不属于企业网站建设基本标准,wordpress应用市场模板下载失败,广告页面设计图片,建材招商网MySQL数据库中的表在进行了多次delete、update和insert后#xff0c;表空间会出现碎片。定期进行表空间整理#xff0c;消除碎片可以提高访问表空间的性能。
检查表空间碎片 下面这个实验用于验证进行表空间整理后对性能的影响#xff0c;首先检查这个有100万记录表的大小表空间会出现碎片。定期进行表空间整理消除碎片可以提高访问表空间的性能。
检查表空间碎片 下面这个实验用于验证进行表空间整理后对性能的影响首先检查这个有100万记录表的大小
mysql analyze table sbtest1; ---------------------------------------------------------------- | Table | Op | Msg_type | Msg_text | ---------------------------------------------------------------- | sbtest.sbtest1 | analyze | status | Table is already up to date | ---------------------------------------------------------------- 1 row in set (0.06 sec)
mysql show table status like sbtest1\G *************************** 1. row *************************** Name: sbtest1 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 1000000 Avg_row_length: 729 Data_length: 729000000 Max_data_length: 205195258022068223 Index_length: 20457472 Data_free: 0 Auto_increment: 1000001 Create_time: 2021-05-31 18:54:22 Update_time: 2021-05-31 18:54:43 Check_time: 2021-05-31 18:55:05 Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
mysql system ls -l /var/lib/mysql/sbtest/sbtest1.* -rw-r----- 1 mysql mysql 729000000 May 31 08:24 /var/lib/mysql/sbtest/sbtest1.MYD -rw-r----- 1 mysql mysql 20457472 May 31 08:25 /var/lib/mysql/sbtest/sbtest1.MYI 命令show table status和从OS层看到的数据文件大小一致这时的Data_free为零。
删除这个表三分之二的记录
mysql delete from sbtest1 where id%30; Query OK, 666667 rows affected (51.72 sec) 重新收集这个表的统计信息后再查看表的状态
mysql analyze table sbtest1; --------------------------------------------- | Table | Op | Msg_type | Msg_text | --------------------------------------------- | sbtest.sbtest1 | analyze | status | OK | --------------------------------------------- 1 row in set (0.13 sec)
mysql show table status like sbtest1\G *************************** 1. row *************************** Name: sbtest1 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 333333 Avg_row_length: 729 Data_length: 729000000 Max_data_length: 205195258022068223 Index_length: 20457472 Data_free: 486000243 Auto_increment: 1000001 Create_time: 2021-05-31 18:54:22 Update_time: 2021-05-31 19:03:59 Check_time: 2021-05-31 18:55:05 Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec)
mysql select 486000243/729000000; --------------------- | 486000243/729000000 | --------------------- | 0.6667 | --------------------- 1 row in set (0.00 sec)
mysql system ls -l /var/lib/mysql/sbtest/sbtest1.* -rw-r----- 1 mysql mysql 729000000 May 31 08:33 /var/lib/mysql/sbtest/sbtest1.MYD -rw-r----- 1 mysql mysql 20457472 May 31 08:34 /var/lib/mysql/sbtest/sbtest1.MYI 发现这个表中的三分之二的记录已经被删除但数据文件的大小还和原来一样。因为被删除的记录只是被标记成删除他们占用的存储空间并没有被释放。
进行全表扫描看看性能
mysql select count(*) from sbtest1 where caaa; ---------- | count(*) | ---------- | 333333 | ---------- 1 row in set (0.82 sec) 发现这个全表扫描SQL用时0.82秒查看sys.session视图中的last_statement_latency可以看到一样的用时。
整理表空间与性能提升
进行表空间整理
mysql alter table sbtest1 force; Query OK, 333333 rows affected (10.73 sec) Records: 333333 Duplicates: 0 Warnings: 0
mysql analyze table sbtest1; ---------------------------------------------------------------- | Table | Op | Msg_type | Msg_text | ---------------------------------------------------------------- | sbtest.sbtest1 | analyze | status | Table is already up to date | ---------------------------------------------------------------- 1 row in set (0.04 sec)
mysql show table status like sbtest1\G *************************** 1. row *************************** Name: sbtest1 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 333333 Avg_row_length: 729 Data_length: 242999757 Max_data_length: 205195258022068223 Index_length: 6820864 Data_free: 0 Auto_increment: 1000001 Create_time: 2021-05-31 19:10:35 Update_time: 2021-05-31 19:10:41 Check_time: 2021-05-31 19:10:45 Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.48 sec)
mysql system ls -l /var/lib/mysql/sbtest/sbtest1.* -rw-r----- 1 mysql mysql 242999757 May 31 08:40 /var/lib/mysql/sbtest/sbtest1.MYD -rw-r----- 1 mysql mysql 6820864 May 31 08:40 /var/lib/mysql/sbtest/sbtest1.MYI 经过整理后硬盘空间占用剩下原来的三分之一Data_free又变成零被删除的记录的硬盘空间都释放了。
再次执行全表扫描的SQL语句
mysql select count(*) from sbtest1 where caaa; ---------- | count(*) | ---------- | 333333 | ---------- 1 row in set (0.29 sec) 发现执行速度也提高到大约原来的三倍。这里使用的是MyISAM表进行测试如果用InnoDB表速度的提高没有这么明显因为InnoDB的数据会缓存到InnoDB缓存中MyISAM表的数据MySQL不进行缓存OS可能会缓存因此要得到准确的测试结果在Linux系统上每次测试前要使用下面的命令释放系统的缓存
# echo 3 /proc/sys/vm/drop_caches 使用alter table force进行表空间整理和OPTIMIZE TABLE命令的作用一样这个命令适用于InnoDB , MyISAM和ARCHIVE三种引擎的表。但对于InnoDB的表不支持OPTIMIZE TABLE命令可以用alter table sbtest1 engineinnodb代替例如
mysql OPTIMIZE TABLE sbtest2; ------------------------------------------------------------------------------------------------------- | Table | Op | Msg_type | Msg_text | ------------------------------------------------------------------------------------------------------- | sbtest.sbtest2 | optimize | note | Table does not support optimize, doing recreate analyze instead | | sbtest.sbtest2 | optimize | status | OK | ------------------------------------------------------------------------------------------------------- 2 rows in set (1 min 25.24 sec)
mysql alter table sbtest2 engineinnodb; Query OK, 0 rows affected (1 min 3.06 sec) Records: 0 Duplicates: 0 Warnings: 0 使用mysqlcheck进行批量表空间优化 #列出所有已经产生碎片的表 select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in (sys, mysql, performance_schema, information_schema, test) and data_free 0; 下面的命令可以找出表空间中可释放空间超过10M的最大10个表
mysql select table_name,round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) 10 order by data_free_mb desc limit 10; ------------------------------------------ | TABLE_NAME | data_length_mb | data_free_mb | ------------------------------------------ | sbtest2 | 232 | 174 | ------------------------------------------ 1 row in set (0.02 sec)
查看表占空间和记录数
select table_schema,table_name, concat(round((data_lengthindex_length)/1024/1024/1024,2),‘G’) as tablesize_gb, table_rows from information_schema.tables where table_schema‘admin’ order by tablesize_gb desc limit 5;
可以使用MySQL自带的工具mysqlcheck的-o选项进行表空间优化这个工具适合于在脚本中进行批量处理可以被Linux中的crontab或Windows中的计划任务调用。
对单个表进行表空间优化的例子如下
$ mysqlcheck -o sbtest sbtest1 也可以使用下面的命令对某个数据库中的所有表进行表空间优化
$ mysqlcheck -o sbtest 还可以对整个实例中对所有数据库进行表空间优化
$ mysqlcheck -o --all-databases 与其要考虑怎么删除数据和回收空间不如在设计之初就考虑到表的数据删除策略根据业务需求存储有用的数据。 根据****热计费项目生产环境上ibd文件异常大借机梳理表碎片清理和表空间收缩的知识点 1、碎片清理的好处 降低访问表时的IO,提高mysql性能,释放表空间降低磁盘空间使用率 OPTIMIZE TABLE table_name;对myisam表有用,对innodb也有用系统会自动把它转ALTER TABLE table_name ENGINE Innodb; 这是因为optimize table的本质就是alter table所以不管myisam引擎还是innodb引擎都可以使用OPTIMIZE TABLE回收表空间. 注意: 1)、表空间管理的类型共享表空间ibdata1(5.5默认)和独立表空间*.ibd文件(5.6默认即innodb_file_per_tableON) 2)、每个表的表空间存放的是该表自己的数据和索引 3)、drop table自动回收表空间删除大量数据后可以通过alter table xx engine innodb;回收空间; 2、关于OPTIMIZE的一些用法(标红的为应用场景)和描述 OPTIMIZE TABLE tbl_name [, tbl_name] ... 如果您已经删除了表的一大部分或者如果您已经对含有可变长度行的表含有VARCHAR, BLOB或TEXT列的表进行了很多更改即delete或update,则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间并整理数据文件的碎片。 3、碎片产生的原因 删除时留白, 插入时尝试使用留白空间当删除后并未将所有留空的都插入数据,既可以认为未被立即使用的留空就是碎片 备注 1) MySQL官方建议不要经常(每小时或每天)进行碎片整理一般根据实际情况只需要每周或者每月整理一次即可; 2) OPTIMIZE TABLE只对MyISAMBDB和InnoDB表起作用尤其是MyISAM表的作用最为明显。此外并不是所有表都需要进行碎片整理一般只需要对包含上述可变长度的文本数据类型的表进行整理即可; 3) 在OPTIMIZE TABLE 运行过程中MySQL会锁定表; 4) 默认情况下直接对InnoDB引擎的数据表使用OPTIMIZE TABLE可能会显示「 Table does not support optimize, doing recreate analyze instead」的提示信息。这个时候我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL以便于让其他引擎支持OPTIMIZE TABLE; 4、查相关碎片的命令 -- 列出所有已经产生碎片的表 select table_schema db, table_name, round(data_free/1024/1024, 2) data_free, engine,table_rows, round((data_lengthindex_length)/1024/1024,2) length from information_schema.tables where table_schema not in (information_schema, mysql) and data_free 0; -- 查询并计算碎片率 select table_schema db, table_name, engine,table_rows, round(data_free/1024/1024, 2) data_free_M, round((data_lengthindex_length)/1024/1024,2) length_M , round(data_free/(data_free data_lengthindex_length),2) rate from information_schema.tables where table_schema not in (information_schema, mysql) and data_free 0 order by data_free_M desc ,rate desc; --查看某张表的碎片率 mysql show table status like t_exception_log202005; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | t_exception_log202005 | InnoDB | 10 | Dynamic | 61360 | 18294 | 1122566144 | 0 | 0 | 6291456 | NULL | 2020-07-31 01:56:57 | NULL | NULL | utf8_general_ci | NULL | | 异常信息日志表 | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 查询结果中 Data_length : 数据的大小 Index_length : 代表索引的数量 Data_free : 代表碎片数量指占用page的大小 5、本地模拟*.ibd文件缩小 5.1 创建新表frag_test create table frag_test (id int auto_increment primary key, c1 varchar(64)); 5.2 利用存储过程插入数据 mysql delimiter $$ mysql create procedure insert_frag_test(IN START INT(10),IN max_num INT(10)) - begin - declare i int default 0; - set autocommit 0; - repeat - set i i 1; - insert into frag_test(id,c1) values((STARTi),this is a test i); - until i max_num - end repeat; - commit; - end $$ mysql delimiter ; mysql call insert_frag_test(1, 2000000); mysql call insert_frag_test(2000002, 10000000); ERROR 1534 (HY000): Writing one row to the row-based binary log failed 这是因为内存不足导致的binlog cache size不够不能写入binlog导致语句无法执行 在配置文件中调整binlog_cache_size和max_binlog_cache_size参数的值改大一点 查看参数 mysql show variables like %binlog_cache_size%; ---------------------------------- | Variable_name | Value | ---------------------------------- | binlog_cache_size | 4194304 | | max_binlog_cache_size | 536870912 | ---------------------------------- mysql set global binlog_cache_size157810688; 5.3 查看ibd文件以及碎片量 [mysqldb1 test01]# du -sh * 4.0K db.opt 12K frag_test.frm 308M frag_test.ibd 12K t1.frm 96K t1.ibd mysql show table status like frag_test; -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | frag_test | InnoDB | 10 | Dynamic | 7086834 | 41 | 295469056 | 0 | 0 | 5242880 | 7104392 | 2020-10-16 10:15:43 | 2020-10-16 10:49:22 | NULL | utf8_general_ci | NULL | | | -------------------------------------------------------------------------------------------------------- 5.4 删除数据查看ibd文件大小以及碎片量 mysql delete from frag_test where id 20; mysql delete from frag_test where id 24 and id 30; mysql delete from frag_test where id 200 and id 230; mysql delete from frag_test where id 2220 and id 2560; mysql delete from frag_test where id 30000 and id 50000; mysql show table status like frag_test; -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | frag_test | InnoDB | 10 | Dynamic | 7066457 | 41 | 295469056 | 0 | 0 | 227540992 | 7104392 | 2020-10-16 10:15:43 | 2020-10-16 10:49:22 | NULL | utf8_general_ci | NULL | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) 在删除数据的过程中看到frag_test.ibd文件大小并没有变化 5.5、执行优化 mysql optimize table frag_test; 查看ibd文件明显缩小 [mysqldb1 test01]# du -sh * 4.0K db.opt 12K frag_test.frm 104M frag_test.ibd 12K t1.frm 96K t1.ibd mysql show table status like frag_test; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | frag_test | InnoDB | 10 | Dynamic | 1993875 | 50 | 100253696 | 0 | 0 | 4194304 | 7104392 | 2020-10-16 14:00:30 | NULL | NULL | utf8_general_ci | NULL | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 发现Data_length和Data_free都有变小 5.6 删除剩余所有的数据 目前的数据量 mysql select count(*) from frag_test; ---------- | count(*) | ---------- | 1999002 | ---------- 1 row in set (0.29 sec) 全部删除mysql delete from frag_test; 全部删除后ibd文件依旧是104M 通过mysql show table status like frag_test; ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | frag_test | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 103809024 | 7104392 | 2020-10-16 14:00:30 | 2020-10-16 14:06:54 | NULL | utf8_general_ci | NULL | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) 发现Data_length变小和Data_free都有变大 执行mysql optimize table frag_test; 查看ibd文件 [rootdb1 test01]# du -sh * 4.0K db.opt 12K frag_test.frm 96K frag_test.ibd 12K t1.frm 96K t1.ibd 执行mysql show table status like frag_test; *************************** 1. row *************************** Name: frag_test Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 7104392 结论 1碎片清理可以使用optimize table table_name,手动触发数据页合并 2optimize table执行过程中会锁表会产生临时表占用一定的空间会影响主从延迟 补充 数据页合并有自动触发和手动触发; 手动触发optimize table 自动触发依赖于数据页合并临界值MERGE_THRESHOLD MySQL InnoDB 表数据页或者二级索引页简称数据页或者索引页的合并与分裂对 InnoDB 表整体性能影响很大数据页的这类操作越多对 InnoDB 表数据写入的影响越大。 MySQL 提供了一个数据页合并临界值MERGE_THRESHOLD在某些场景下可以人为介入减少数据页的合并与分裂。在 InnoDB 表里每个数据页默认16K 大小默认 MERGE_THRESHOLD 值为 50取值范围从 1 到 50默认值即是最大值。也就是当页面记录数占比小于 50% 时MySQL 会把这页和相邻的页面进行合并保证数据页的紧凑避免太多浪费。
注意
1、DELETE执行时当达到阈值后page会合并被合并的page会并标记为空闲页留着以后用并不会释放。所以DELETE后ibd是不会变小的(truncate会变小)。使用optimze等于表重构所以ibd会变小。 2、对于Innodb 引擎一般optimze是在开始和结束阶段需要元数据锁中间阶段是可以进行DML操作的。
