mysql  索引优化

转自:http://fireflyclub.org/?/article/14
前些日子在姜承尧姜总那里看到他公众号分享了个索引优化,他给了个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 ;


在5.6 上验证过,可以运行。
意思还是比较难懂,需要深研。
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值