InnoDB 表空间介绍以及管理
1.mysql表空间类型
system tablespace 系统表空间
file-per-table tablespace 独立表空间
temporary tablespace 临时表空间
undo tablespace UNDO表空间
general tablespace 通用表空间
transportable tablespace 传输表空间2.扩展IBDATA系统表空间案例，加多一个1G的ibdata4的系统表空间文件
show global variables like '%innodb_data_file_path%';
修改my.cnf文件 innodb_data_file_path 参数
innodb_data_file_path=ibdata1:1024M;ibdata2:1024M;ibdata3:1024M:autoextend:max:20G修改成：
innodb_data_file_path= ibdata1:1024M;ibdata2:1024M;ibdata3:1024M;ibdata4:1024M:autoextend:max:20G
重启mysql、初始化：
systemctl restart mysql;
3.收缩ibdata系统表空间innodb system tablespace 案例
场景：原来是共享表空间，转换成独立表空间以后，数据和索引迁移出ibdata，原来的ibdata占用500G空间不会自动缩小。
(1)导出所有的数据(逻辑备份)：
mysqldump --all-databases --flush-privileges \
--quick --routines --triggers -uroot -p > mysqldump_all_database.sql(2)查询 mysql 系统数据下所有innodb表、导出内容、删掉表结构 ：
SELECT
CONCAT('ls -lah /mysql/data/3306/data/mysql/',table_name,'.frm') "查看.frm路径",
CONCAT('rm -rf /mysql/data/3306/data/mysql/',table_name,'.frm') "删除.FRM"
FROM information_schema.tables
WHERE table_schema='mysql' AND ENGINE='innodb';(3)停止mysql数据库 ：
systemctl stop mysql; (4)冷备打包mysql datadir目录 ：
cd /mysql/data/3306/
tar zcvf data.tar.gz data (5)删除所有的表空间文件
删除ibdata、ib_log*、undo*、mysql目录下面的*.ibd文件、以及mysql中innodb表的.frm文件。
find /mysql/data/3306/data/ -name "ibdata*" -exec rm -rf {} \;
find /mysql/data/3306/data/ -name "ib_logfile*" -exec rm -rf {} \;
find /mysql/data/3306/data/ -name "undo*" -exec rm -rf {} \;
find /mysql/data/3306/data/ -name "*.ibd" -exec rm -rf {} \;(6)修改my.cnf参数文件
将innodb_data_file_path 参数改小，生产如果是500G大小，改成100M。
innodb_data_file_path=ibdata1:500G;ibdata2:500G;ibdata3:500G:autoextend:max:500G修改成：
innodb_data_file_path=ibdata1:100M;ibdata2:100M;ibdata3:100M;ibdata4:100M:autoextend:max:20G(7)启动mysql、恢复数据库、查询数据
systemctl start mysql;
mysql -uroot -p123456
source /root/mysqldump/mysqldump_all_database.sql;
commit;
select count(*) from scott.emp;
select count(*) from yzjtestdb.yzjtest_m1;
4.临时表空间
临时表空间:主要用于临时排序/非压缩的临时表,默认12M的文件。
mysql 5.7 之前 : 临时表都放在 ibdata 里面
mysql 5.7之后 : 临时表放在临时表空间
相关参数
show variables like '%innodb_temp_data_file_path%';# 临时表空间优化案例
问：临时表空间主要用于临时排序、分组作用，而临时表空间路径不允许修改，只能放在$datadir目录内，如果SQL语句有大量的排序分组操作，然而磁盘IO又跟不上，对数据库造成阻塞，我们改怎么优化数据库?
答：我们可以将临时表空间存放在/dev/shm目录（内存）下。
ln -sf /dev/shm /mysql/data/3306/data/temp
chown -R mysql:mysql /mysql
vi /mysql/data/3306/my.cnf
innodb_temp_data_file_path=ibtmp1:500M;ibtmp2:500M:autoextend:max:20G修改成：
innodb_temp_data_file_path=temp/ibtmp1:500M;temp/ibtmp2:500M:autoextend:max:20G
systemctl restart mysql
ls -l /mysql/data/3306/data/temp/*
*/5.UNDO回滚表空间
mysql5.7 以后,从系统表空间独立出来的 undo 回滚表空间
相关的参数
show variables like '%undo%';
6.mysql常用空间信息查询SQL
(1)查看数据库大小
select SUM( data_length + index_length ) / 1024 / 1024 "Database Size in MB" from information_schema.TABLES;(2)查看各数据库大小
select table_schema "Database Name",
SUM( data_length + index_length ) /1024 / 1024 "Database Size in MB"
from information_schema.TABLES group by table_schema;(3)查看索引大小
select CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB')
as 'Total Index Size' from information_schema.TABLES
where table_schema like 'scott';(4)查看数据库中所有表和索引的空间信息
select CONCAT(table_schema,'.',table_name) as 'Table Name', CONCAT(ROUND(table_rows,2))
as 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),2),'G')
as 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),2),'G')
as 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G')
as 'Total'
from information_schema.TABLES where table_schema like 'scott';(5)查看表空间大小
select CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB')
as data_size, CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB')
as max_data_size, CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB')
as data_free, CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB')
as index_size from information_schema.tables where TABLE_SCHEMA = 'scott';select CONCAT(ROUND(SUM(data_length/1024/1024),2),'MB')
as data_length_MB, CONCAT(ROUND(SUM(index_length/1024/1024),2),'MB')
as index_length_MB from information_schema.tables
where table_schema='scott' and table_name = 'emp';(6)找出占用空间前10的表大小
select CONCAT(table_schema, '.', table_name),
CONCAT(table_rows) rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') data,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,ROUND(index_length / data_length, 2) idxfrac
from information_schema.TABLES order by data_length + index_length desc limit 10;
