创建索引是门技术活，开发DBA的工作之一就是配合应用创建最优的索引。然大部分公司并没有开发DBA一职，大多数的索引创建需要由程序开发人员自己完成，这导致的一个后果是，索引创建的好与坏大部分情况下需要看这个程序猿的气质。
通常，Inside君通过下面这条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值得统计依然是正确的，那么问题来了：
-
有谁知道5.6下上述SQL该如何改写？
-
如何修复5.6下的Cardinality Bug？
版本《=5.6
- 查找未被使用的索引：
-
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)
版本=5.7
-
mysql> select * from sys.schema_redundant_indexes 冗余索引
-
mysql> select * from schema_unused_indexes ; 未使用索引 --详见mysql5.7 sys schema视图详解
-
mysql> select * from statements_with_full_table_scans; 使用全表扫描的sql语句 等
- mysql> select * from sys.schema_redundant_indexes 冗余索引
-
mysql> select * from schema_unused_indexes ; 未使用索引 --详见mysql5.7 sys schema视图详解
- mysql> select * from statements_with_full_table_scans; 使用全表扫描的sql语句 等
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29096438/viewspace-1973622/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [Mysql]Mysql5.7并行复制
- 【Redis】错误：failed: Hostname must not be empty or null
- 【Python】python map()函数和lambda表达式
- 【Mysql】MySQL中interactive_timeout和wait_timeout的区别
- 【Mysql】修改mysql时区
- MySQL(Innodb)索引的原理
- 【Redis】修改redis-trib.rb使其import支持密码
- 【Redis】redis-cluster需要注意的几个地方
- 【Mongodb】mongo复制集只剩一个secondery节点的解决办法
- 【Redis】redis-cluster到redis-cluster的快速复制
