[Mysql] quickly locate unreasonable indexes-- MySQL index tuning (1)
Original address: http://mp.weixin.qq.com/s?__biz=MjM5MjIxNDA4NA==&mid=401131835&idx=1&sn=37c5fd9d3d8670fb379a1e0565e50eeb&scene=0#wechat_redirect
Creating an index is a technical task, and one of the tasks of developing DBA is to cooperate with the application to create the best index. However, most companies do not develop DBA, and most index creation needs to be done by program developers themselves. As a result, the quality of index creation depends on the temperament of the programmer in most cases.
In general, Inside uses the following SQL statement to view the index created (while drinking coffee and listening to music). In most cases, 90% of the index creation is unreasonable:
Unfortunately, the above SQL statement does not work under MySQL version 5.6 (even with the latest MySQL version 5.6.28), because the statistics about Cardinality in the current STATISTICS table of 5.6 are wrong! For more information, see MySQL bugs # 78066. However, the Cardinality worth statistics in the table innodb_index_stats are still true, so the question arises:
Who knows how to rewrite the above SQL under 5.6?
How to repair Cardinality Bug under 5.6?
Version "= 5.6
Find unused indexes: mysql > select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME from performance_schema.table_io_waits_summary_by_index_usage where INDEX_NAME is not null and COUNT_STAR=0 and OBJECT_SCHEMA='xdq' and OBJECT_NAME='order_reasons_dispute' order by OBJECT_SCHEMA,OBJECT_NAME
+-+
| | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | |
+-+
| | xdq | order_reasons_dispute | PRIMARY | |
| | xdq | order_reasons_dispute | s_uid | |
| | xdq | order_reasons_dispute | b_uid | |
| | xdq | order_reasons_dispute | c_time | |
| | xdq | order_reasons_dispute | r_time | |
+-+
5 rows in set (0.15 sec)
Version = 5.7
Mysql > select * from sys.schema_redundant_indexes redundant index mysql > select * from schema_unused_indexes; unused index-- see mysql5.7 sys schema view for details
Mysql > select * from statements_with_full_table_scans; using full table scan sql statements, etc.
