MySQL 定位不合理的索引(转载)
2021/9/5 2:06:01
本文主要是介绍MySQL 定位不合理的索引(转载),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
转载至:https://www.yuque.com/yinjianwei/vyrvkf/yh2y71
通过下面这条 SQL 语句来检视创建的索引,大部分情况下可以定位出 90% 的索引创建不合理情况:
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 ;
上述的 SQL 语句利用了 information_schema 数据库下的元数据表 TABLES、STATISTICS。
- 表 TABLES 记录了表的基本信息,例如库名,表名,行数等。
- 表 STATISTICS 记录了各个索引的 CARDINALITY 值。
- 那么 CARDINALITY / TABLE_ROWS 表示的就是索引的选择性。
在 Inside 君的《MySQL技术内幕:InnoDB存储引擎》一书中明确指出的是,在 OLTP 的应用场景下,创建的索引是要求高选择性的。
若 CARDINALITY / TABLE_ROWS 小于10%(经验值),那么表示数据重复率较高,通常需要考虑是否有必要创建该索引。该语句运行的结果如下所示,列 SELECTIVITY 表示的就是选择性:
上述 SQL 语句并不能工作在 MySQL 5.6 版本下(即使最新的 MySQL 5.6.28 版本),因为目前 5.6 的 STATISTICS 表中关于 Cardinality 的统计是错误的。具体可见 MySQL bugs #78066。但是,表 innodb_index_stats 中关于 Cardinality 值得统计依然是正确的。
这篇关于MySQL 定位不合理的索引(转载)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-12-24MySQL集群部署资料:新手入门教程
- 2024-12-24MySQL集群资料详解:新手入门教程
- 2024-12-24MySQL集群部署入门教程
- 2024-12-24部署MySQL集群学习:新手入门教程
- 2024-12-24部署MySQL集群入门:一步一步搭建指南
- 2024-12-07MySQL读写分离入门:轻松掌握数据库读写分离技术
- 2024-12-07MySQL读写分离入门教程
- 2024-12-07MySQL分库分表入门详解
- 2024-12-07MySQL分库分表入门指南
- 2024-12-07MySQL慢查询入门:快速掌握性能优化技巧