当前位置: 代码迷 >> MySQL >> mysql ?目录优化
  详细解决方案

mysql ?目录优化

热度:235   发布时间:2016-05-05 16:28:37.0
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 上验证过,可以运行。
意思还是比较难懂,需要深研。
  相关解决方案