ITPub博客

首页 > 数据库 > MySQL > 【Mysql】快速定位不合理的索引——MySQL索引调优(一)

【Mysql】快速定位不合理的索引——MySQL索引调优(一)

MySQL 作者:小亮520cl 时间:2016-01-08 09:51:39 0 删除 编辑
原文地址:http://mp.weixin.qq.com/s?__biz=MjM5MjIxNDA4NA==&mid=401131835&idx=1&sn=37c5fd9d3d8670fb379a1e0565e50eeb&scene=0#wechat_redirect

创建索引是门技术活,开发DBA的工作之一就是配合应用创建最优的索引。然大部分公司并没有开发DBA一职,大多数的索引创建需要由程序开发人员自己完成,这导致的一个后果是,索引创建的好与坏大部分情况下需要看这个程序猿的气质


通常,Inside君通过下面这条SQL语句来检视创建的索引(同时喝着咖啡,听着音乐),大部分情况下可以定位出90%的索引创建不合理情况:

  1. SELECT
  2.      t.TABLE_SCHEMA,t.TABLE_NAME,INDEX_NAME, CARDINALITY,
  3.     TABLE_ROWS, CARDINALITY/TABLE_ROWS AS SELECTIVITY
  4. FROM
  5.     information_schema.TABLES t,
  6.  (
  7.   SELECT table_schema,table_name,index_name,cardinality
  8.   FROM information_schema.STATISTICS
  9.   WHERE (table_schema,table_name,index_name,seq_in_index) IN (
  10.   SELECT table_schema,table_name,index_name,MAX(seq_in_index)
  11.   FROM information_schema.STATISTICS
  12.   GROUP BY table_schema , table_name , index_name )
  13.  ) s
  14. WHERE
  15.     t.table_schema = s.table_schema
  16.         AND t.table_name = s.table_name AND t.table_rows != 0
  17.         AND t.table_schema NOT IN ( 'mysql','performance_schema','information_schema')
  18. 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值得统计依然是正确的,那么问题来了:

  1. 有谁知道5.6下上述SQL该如何改写?

  2. 如何修复5.6下的Cardinality Bug?





版本《=5.6


  1. 查找未被使用的索引:
  2. 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
  1. mysql> select * from sys.schema_redundant_indexes   冗余索引
  2. mysql> select * from schema_unused_indexes ;      未使用索引  --详见mysql5.7 sys schema视图详解   
  3. mysql> select * from statements_with_full_table_scans; 使用全表扫描的sql语句 等




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29096438/viewspace-1973622/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
毕业以后专业任职数据库工程师职位,itpub一直作为自己的笔记记录的地方,blog写的不详细,草书笔记,仅供参考!

注册时间:2013-09-12

  • 博文量
    530
  • 访问量
    973069