mysql ?目录优化

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 SELECTIVITYFROM 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    ) sWHERE 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.4ORDER BY SELECTIVITY ;

在5.6 上验证过，可以运行。
意思还是比较难懂，需要深研。