User Statistic是個(gè)能統(tǒng)計(jì)MySQL中線程凄诞、用戶雹有、表、索引等信息的插件,在Percona和MariaDB直接集成了,但官方文檔沒(méi)有多少實(shí)用例子,以下是我日常用的一些例子(測(cè)試跑在MariaDB 10.0上):
1.找出沒(méi)被訪問(wèn)過(guò)的表
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語(yǔ)句:
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.找出沒(méi)被訪問(wèn)過(guò)的索引
主鍵必須排除,唯一索引可能用作唯一約束所以也要排除掉
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語(yǔ)句:
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.數(shù)據(jù)庫(kù)中哪個(gè)表查詢最頻繁
但數(shù)據(jù)庫(kù)撐不住日益增長(zhǎng)的壓力時(shí)就要考慮將一些表垂直拆分出去了,如下是找出查詢?cè)L問(wèn)最頻繁的10張表:
use INFORMATION_SCHEMA;
select * from TABLE_STATISTICS order by rows_read desc limit 10;
4.數(shù)據(jù)庫(kù)中哪個(gè)表更新最頻繁
use INFORMATION_SCHEMA;
select * from TABLE_STATISTICS order by rows_changed_x_indexes desc limit 10;
5.兼顧主機(jī)和備機(jī)
上面的1失球、2例子只是單機(jī)情況下找出無(wú)用的表或索引,但一個(gè)索引可能主機(jī)沒(méi)有用它,而備機(jī)有查詢能夠使用到,這種情況下索引其實(shí)是有用的,所以就要兼顧主機(jī)和所有備機(jī),Jay Janseen在這篇文章提出了一個(gè)方法,而我常用方法如下:
a.在一臺(tái)測(cè)試機(jī)創(chuàng)建如下數(shù)據(jù)庫(kù):
create database if not exists user_statistics;
b.將遠(yuǎn)程主機(jī)的statistics岖是、tablestatistics、indexstatistics復(fù)制到測(cè)試機(jī):
mysqldump ... -h M_IP information_schema statistics table_statistics index_statistics > M.sql
c.將遠(yuǎn)程備機(jī)的tablestatistics实苞、indexstatistics數(shù)據(jù)導(dǎo)出成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;)
