How to quickly locate unreasonable Index by MySQL5.6

This article mainly explains "MySQL5.6 how to quickly locate the unreasonable index", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL5.6 how to quickly locate the unreasonable index" bar!

# if the CARDINALITY / TABLE_ROWS is less than 10% (empirical value), it means that the data repetition rate is high, and you usually need to consider whether it is necessary to create the index

# # #

# mysql 5.6

At present, the statistics about Cardinality in the STATISTICS table of 5.6are wrong! For more information, see MySQL bugs # 78066.

However, the statistics about the worth of Cardinality in table innodb_index_stats are still correct.

# # #

SELECT

T.TABLE_SCHEMA

T.TABLE_NAME,INDEX_NAME

S.CARDINALITY

T.TABLE_ROWS

S.CARDINALITY/t.TABLE_ROWS AS SELECTIVITY

FROM

Information_schema.TABLES t

(SELECT database_name,table_name,index_name,stat_value AS CARDINALITY

FROM mysql.innodb_index_stats

WHERE (database_name,table_name,index_name,stat_name) IN

(SELECT table_schema,table_name,index_name,CONCAT ('nasty diffusive pfx0 recording Max (seq_in_index))

FROM information_schema.STATISTICS

-- where table_name='xxxxx'

GROUP BY table_schema, table_name, index_name)) s

WHERE t.table_schema = s.database_name

AND t.table_name = s.table_name

AND t.table_rows! = 0

AND t.table_schema NOT IN ('mysql','performance_schema','information_schema')

ORDER BY SELECTIVITY

The following sections are from: http://mp.weixin.qq.com/s?__biz=MjM5MjIxNDA4NA==&mid=401131835&idx=1&sn=37c5fd9d3d8670fb379a1e0565e50eeb&scene=0#wechat_redirect

# # #

# mysql 5.7Table STATISTICS records the CARDINALITY value of each index

# # #

SELECT

T.TABLE_SCHEMA,t.TABLE_NAME,INDEX_NAME, CARDINALITY

TABLE_ROWS, CARDINALITY/TABLE_ROWS AS SELECTIVITY

FROM

Information_schema.TABLES t

(

SELECT table_schema,table_name,index_name,cardinality

FROM information_schema.STATISTICS

WHERE (table_schema,table_name,index_name,seq_in_index) IN (

SELECT table_schema,table_name,index_name,MAX (seq_in_index)

FROM information_schema.STATISTICS

GROUP BY table_schema, table_name, index_name)

) s

WHERE

T.table_schema = s.table_schema

AND t.table_name = s.table_name AND t.table_rows! = 0

AND t.table_schema NOT IN ('mysql','performance_schema','information_schema')

ORDER BY SELECTIVITY

Thank you for your reading, the above is the content of "how to quickly locate the unreasonable index of MySQL5.6". After the study of this article, I believe you have a deeper understanding of how to quickly locate the unreasonable index of MySQL5.6, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!