MySQL优化索引-----定位需要优化的索引( 我的SQL支持5.6,5.7,mariadb。另下面附了姜总原文中的SQL支持5.6外的环境)

前些日子在姜承尧姜总那里看到他公众号分享了个索引优化,他给了个SQL说这个SQL5.6不好使,让大家来写一个5.6能使用的,然后给了一些信息。然后我就写了下面的sql,姜总说这个SQL在SELECTIVITY是0.1左右的都需要关注了。

下面这个SQL后来请吴总帮忙看了下,说是没有问题,给了个意见,然后我按照吴总的建议修改了下现在只输出SELECTIVITY<=0.4的行。

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 t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY,'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name - 1
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY'
        AND t2.index_name = 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs NOT REGEXP '^PRIMARY'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_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')
HAVING SELECTIVITY <=0.4
ORDER BY SELECTIVITY ;

里面的3个子查询分别是1)表有主键 当前匹配的索引不是主键 2)表有主键 当前匹配索引是主键 3)当前表没有主键
 
这里再贴下姜总的SQL吧,姜总说这个SQL在5.6中因为information_schema.STATISTICS统计的信息有BUG索引无法得出准确的结果。但是支持其他的版本。
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;

----------------------------------------------------------------
关于会有SELECTIVITY列大于1的情况
我是这么理解的 information_schema.TABLES这个表不是实时跟新,这个表相对更新慢,如果行数少,比如查出在某一个时刻基数是5对应当时的行数是5, 但是如果information_schema.TABLES收集的那个时间点,有4条还未插入,就可能会导致计算的结果是5。我做过尝试,当看到超过1的行的时候,稍后再执行,那一行一般都会变成另一个数值。
如果理解有误请勘正,或者还有其他的理解希望大家也能分享讨论下。共同提高。

17 个评论

很常有用的一个SQL。 保留备用。
不错,留下备用
不错,谢谢
看不懂 解释一下!
huzilin

huzilin 回复 Yum

我编辑了下,下面新补充了写注释
mark
收藏了
非常好
收藏了
用心
为什么有SELECTIVITY大于1的情况?
我重新编辑了一下,希望有帮助,如有问题请指正~
支持5.5不?我的是在5.5试的
我的语句不支持,下面我附上的姜总的是5.5支持
我的语句不支持,下面我附上的姜总的是5.5支持
嗯,多谢!在5.5试的时候存在SELECTIVITY大于1的情况

要回复文章请先登录注册