数据库索引及空间优化
安装pt-query-digest,检查重复索引pt-duplicate-key-checker
安装pt-query-digest--- 源码包安装
[ ! -d /tools ] && mkdir /tools cd /tools wget percona.com/get/percona-toolkit.tar.gz tar zxf percona-toolkit.tar.gz cd percona-toolkit-3.3.1 yum -y install perl-DBD-MySQL yum -y install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker yum -y install perl-Digest-MD5 perl Makefile.PL PREFIX=/usr/local/percona-toolkit make && make install ln -s /usr/local/percona-toolkit/bin/pt-duplicate-key-checker /usr/local/bin/
使用pt-duplicate_key-checher工具检查重复冗余索引
PTDEBUG=1 pt-duplicate-key-checker -uroot -p 'password' -h xxx.xxx.xxx.xxx -P 3306 -d xuehua_order -t order_discount > dev_xuehua_order_index_check.sql 2>&1 PTDEBUG=1 pt-duplicate-key-checker -uroot -p 'password' -h xxx.xxx.xxx.xxx -P 3306 > csyx_index_check.sql cat dev_xuehua_order_index_check.sql
![](https://img2022.cnblogs.com/blog/2139106/202203/2139106-20220323165126859-377396713.png)
分析慢SQL
pt-query-digest /usr/local/mysql/data/slow.log
![](https://img2022.cnblogs.com/blog/2139106/202203/2139106-20220323165126895-1613596004.png)
看哪些索引是不使用的,在mysql中通过慢查日志配合pt-index-usage 工具来进行索引使用情况的分析。
pt-index-usage -uroot -p'123456' mysql-slow.log
对于阿里云 mysql-slow.log 直接在rds控制台日志管理》慢日志明细查看即可,高可用版本mysql.slow_log 表会清理的。
最新的慢日志可以查看这个表,执行sql例如查看今天慢sql执行
select `start_time` ,`user_host` ,`query_time` ,`lock_time` ,`rows_sent` ,`rows_examined` ,`db` ,`last_insert_id` ,`insert_id` ,`server_id` ,cast( `sql_text` as char) ,`thread_id` from mysql.slow_log where date_format(start_time,'%Y%m%d')='20220323';
问题: 删除重复索引后,索引空间是变小了,但表空间反而还增大了,碎片率也增大了
![](https://img2022.cnblogs.com/blog/2139106/202203/2139106-20220323165032428-1076411517.png)
解决反馈的信息碎片率增加了
1、先执行SQL查看数据大小统计。
SELECT file_name,concat(TOTAL_EXTENTS,'MB') as 'FileSize' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESC limit 20;
2、然后对于大表执行 回收一下表空间(碎片整理)!!!注意会锁表
optimize table <表名>;
OPTIMIZE TABLE语句可以重新组织表、索引的物理存储,减少存储空间,提高访问的I/O效率。类似于碎片整理功能
3、然后执行SQL 重新统计表空间。
analyze table <表名>;
MySQL中analyze table的作用是?生产上操作会有什么风险?
一、analyze table的作用
1、analyze table 会统计索引分布信息。
2、对于 MyISAM 表,相当于执行了一次 myisamchk --analyze
3、支持 InnoDB、NDB、MyISAM 等存储引擎,但不支持视图(view)
4、执行 analyze table 时,会对表加上读锁(read lock)
5、该操作会记录binlog
二、生产上操作的风险
1、analyze table的需要扫描的page代价粗略估算公式:sample_pages * 索引数 * 表分区数。
2、因此,索引数量较多,或者表分区数量较多时,执行analyze table可能会比较费时,要自己评估代价,并默认只在负载低谷时执行。
3、特别提醒,如果某个表上当前有慢SQL,此时该表又执行analyze table,则该表后续的查询均会处于waiting for table flush的状态,严重的话会影响业务,因此执行前必须先检查有无慢查询。
4、在重新执行1的命令看下是否有下降
SELECT file_name,concat(TOTAL_EXTENTS,'MB') as 'FileSize' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESC limit 20;
原因
个人是这样理解的,当你删除数据时,mysql并不会回收已删除的数据所占据的存储空间,以及索引位。
而是空在那里,而是等待新的数据来弥补这个空缺,这样就有一个缺少,如果一时半会,没有数据来填补这个空缺,那这样就太浪费资源了。
所以对于写比较频繁的表,要定期进行optimize,一个月一次,看实际情况而定了
1、查询所有数据库占用磁盘空间大小的SQL语句
SELECT table_schema as '数据库', sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)', sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)', sum(truncate(data_free/1024/1024, 2)) as '碎片空间(MB)', sum(table_rows) as '表总行数' FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') GROUP BY table_schema ORDER BY sum(data_length) DESC, sum(index_length) DESC;
2、查询所有库中所有表磁盘占用大小的SQL语句
SELECT table_schema as '数据库', table_name as '表名', truncate(data_length/1024/1024, 2) as '数据空间(MB)', truncate(index_length/1024/1024, 2) as '索引空间(MB)', truncate(data_free/1024/1024, 2) as '碎片空间(MB)', table_rows as '表行数' FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') ORDER BY data_length DESC, index_length DESC;
清除碎片操作会暂时锁表,数据量越大,耗费的时间越长,可以做个脚本,定期检查MySQL中 information_schema.tables字段,查看 data_free 字段,大于0话,就表示有碎片,当大于自己认为的警戒值的话,就清理一次。
SELECT table_schema as 'table_schema', table_name as 'table_name', truncate(data_length/1024/1024, 2) as 'data_length(MB)', truncate(index_length/1024/1024, 2) as 'index_length(MB)', truncate(data_free/1024/1024, 2) as 'data_free(MB)', truncate((data_length+index_length+data_free)/1024/1024, 2) as 'total_size(MB)', table_rows as 'table_rows', truncate(data_free/(data_length+index_length+data_free), 2) as 'frag_percent' FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') and data_free/(data_length+index_length+data_free) > 0.2 ORDER BY data_length+index_length+data_free DESC;