mysql count star_mysql 索引的统计

查看一个库里面没有使用过的索引

select object_type,object_schema,object_name,index_name,count_star,count_read,COUNT_FETCH from performance_schema.table_io_waits_summary_by_index_usage where object_schema='bocat' and count_star=0 and count_read=0

and object_name not in ('transactions_2019Q4','projects_2019Q1','transactions_2019Q1','projects_2018Q4','transactions_2018Q4','projects_2018Q3','transactions_2018Q3','withdrawals_history_0607',

'projects_2018Q2','transactions_2018Q2','transactions_2018Q1','projects_2018Q1','push_records_history','passive_records_history','transactions_2017Q4','projects_2017Q4','withdrawals_history','

traces_history','user_profits_sport_history','user_profits_history','user_profits_20160131','user_bank_cards_copy','transactions_history','transaction_types_copy','trace_details_history','projects_history','projects_bk1',

'jc_matches_info_copy','issues_history','user_login_logs_7','user_login_logs_5','user_login_logs_4','user_login_logs_0','user_login_logs_6','user_login_logs_3','user_login_logs_9','user_login_logs_8',

'user_login_logs_2','user_login_logs_1','transactions_2019Q3','transactions_2019Q2') and index_name !='PRIMARY';

查看一个表的索引的使用情况。

select distinct b.TABLE_SCHEMA,b.TABLE_NAME , b.INDEX_NAME , a.count_star

from performance_schema.table_io_waits_summary_by_index_usage a ,

INFORMATION_SCHEMA.STATISTICS b

where a.OBJECT_SCHEMA = b.TABLE_SCHEMA and a.INDEX_NAME = b.INDEX_NAME

and a.OBJECT_NAME=b.TABLE_NAME and a.OBJECT_SCHEMA =b.INDEX_SCHEMA

and b.CARDINALITY > 0

and b.TABLE_NAME = 'table_name';