User Statistic是个能统计MySQL中线程、用户、表、索引等信息的插件,在Percona和MariaDB直接集成了,但官方文档没有多少实用例子,以下是我日常用的一些例子(测试跑在MariaDB 10.0上):
1.找出没被访问过的表
use INFORMATION_SCHEMA;
select table_schema,table_name from STATISTICS where (table_schema,table_name) not in (select table_schema,table_name from TABLE_STATISTICS ) and table_schema not in ('information_schema','performance_schema','mysql');
生成drop语句:
use INFORMATION_SCHEMA;
select concat('drop table ',table_schema,'.',table_name,';') from STATISTICS where (table_schema,table_name) not in (select table_schema,table_name from TABLE_STATISTICS ) and table_schema not in ('information_schema','performance_schema','mysql');
2.找出没被访问过的索引
主键必须排除,唯一索引可能用作唯一约束所以也要排除掉
use INFORMATION_SCHEMA;
select table_schema,table_name,index_name from STATISTICS where (table_schema,table_name,index_name) not in (select table_schema,table_name,index_name from INDEX_STATISTICS ) and table_schema not in ('information_schema','performance_schema','mysql') and index_name != 'PRIMARY' and NON_UNIQUE = 1;
生成drop语句:
use INFORMATION_SCHEMA;
select concat('alter table ', table_schema,'.',table_name,' drop index ',index_name,';') from STATISTICS where (table_schema,table_name,index_name) not in (select table_schema,table_name,index_name from INDEX_STATISTICS ) and table_schema not in ('information_schema','performance_schema','mysql') and index_name != 'PRIMARY' and NON_UNIQUE = 1;
3.数据库中哪个表查询最频繁
但数据库撑不住日益增长的压力时就要考虑将一些表垂直拆分出去了,如下是找出查询访问最频繁的10张表:
use INFORMATION_SCHEMA;
select * from TABLE_STATISTICS order by rows_read desc limit 10;
4.数据库中哪个表更新最频繁
use INFORMATION_SCHEMA;
select * from TABLE_STATISTICS order by rows_changed_x_indexes desc limit 10;
5.兼顾主机和备机
上面的1、2例子只是单机情况下找出无用的表或索引,但一个索引可能主机没有用它,而备机有查询能够使用到,这种情况下索引其实是有用的,所以就要兼顾主机和所有备机,Jay Janseen在这篇文章提出了一个方法,而我常用方法如下:
a.在一台测试机创建如下数据库:
create database if not exists user_statistics;
b.将远程主机的statistics、tablestatistics、indexstatistics复制到测试机:
mysqldump ... -h M_IP information_schema statistics table_statistics index_statistics > M.sql
c.将远程备机的tablestatistics、indexstatistics数据导出成insert ignore形式:
mysqldump ... -h S_IP --add-drop-table=false --no-create-info --add-lock=false -t --insert-ignore --complete-insert information_schema table_statistics index_statistics > S.sql
mysql> use user_statistics;
mysql> source M.sql;
mysql> source S.sql;
这样就能用例子1,2的SQL了(要将use INFORMATION_SCHEMA;换成use user_statistics;)
