`

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 上验证过,可以运行。
意思还是比较难懂,需要深研。
分享到:
评论

相关推荐

    MySQL中索引优化distinct语句及distinct的多字段操作

    在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个不能利用索引完成DISTINCT操作的实例.   实例1 使用索引优化DISTINCT操作 create table m11 (a int, b int, c int, d int, primary ...

    大型门户网站核心技术-Mysql优化

    Mysql优化 课程目录:【】Mysql优化 资料【】Mysql优化01关键技术【】Mysql优化02表的设计【】Mysql优化03慢查询(一)【】Mysql优化04慢查询(二)【】Mysql优化05慢查询(三)【】Mysql优化06索引(一)【】Mysql优化07...

    MySql索引使用策略分析

    MySql索引 索引优点 1.可以通过建立唯一索引或者主键索引,保证数据的唯一性. 2.提高检索的数据性能 3.在表连接的连接条件 可以加速表与表直接的相连  4.建立索引,在查询中使用索引 可以提高性能 索引缺点 1.在...

    MySQL查询优化

    《MySQL索引优化》 2 分页查询优化 很多时候我们业务系统实现分页功能可能会用如下sql实现: select * from employees limit 10000,10; 表示从表employees中取出从10001行开始的10行记录。看似只查询了10条记录,...

    详解mysql索引总结—-mysql索引类型以及创建

    关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能...

    简单介绍下MYSQL的索引类型

    Mysql常见索引有:主键索引、唯一索引、普通索引、全文索引、组合索引 PRIMARY KEY(主键索引) ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`...

    源码时代软件测试干货分享_史上最全的mysql数据库优化方法,软件测试人手必备!.doc

    Ø 3.Mysql索引的使用 Ø 4.分表技术 2. mysql优化概述 概述: 前面我们学习了页面静态化和redis,它们是通过不操作mysql数据库达到提速目的。但是某些功能是一定要操作数据库的,这就要求我们必须对mysql本身进行...

    MySQL 5.1参考手册

    7.2.9. MySQL如何优化LEFT JOIN和RIGHT JOIN 7.2.10. MySQL如何优化嵌套Join 7.2.11. MySQL如何简化外部联合 7.2.12. MySQL如何优化ORDER BY 7.2.13. MySQL如何优化GROUP BY 7.2.14. MySQL如何优化LIMIT ...

    MySQL 5.7 中文文档

    2. **性能优化**:MySQL 5.7引入了诸多性能优化措施,如改进的查询缓存、更高效的索引使用、更少的磁盘I/O以及更快的数据读取。其中,优化的线程池管理允许更有效地处理并发连接,提高服务器的并发性能。 3. **JSON...

    mysql 全文搜索 技巧

    &lt;&lt;...FULLTEXT 索引用于 MyISAM 表,可以在 CREATE TABLE 时或之后使用 ALTER TABLE 或 CREATE INDEX 在 CHAR、VARCHAR 或 TEXT 列上创建。对于大的数据库,将数据装载到一个

    MySQL索引类型总结和使用技巧以及注意事项

    在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表: ...时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描所有记录,即要查询

    Mysql之组合索引方法详解

    对于任何DBMS,索引都是进行优化的最主要的因素。对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧下降。  如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引...

    mysql-5.5.61-winx64 \mysql-5.5.36-win32\mysql-essential-5.1.32-win32

    - MySQL的性能可以通过调整各种系统变量、优化查询语句、合理设计索引等方式提升。例如,使用EXPLAIN分析查询计划,优化JOIN操作,合理使用索引等。 8. **最新版本与社区支持**: - 虽然提供的版本较旧,但MySQL...

    mysql索引使用技巧及注意事项

    但是当数据量和访问量剧增的时候,就会发现mysql变慢,甚至down掉,这就必须要考虑优化sql了,给数据库建立正确合理的索引,是mysql优化的一个重要手段。  索引的目的在于提高查询效率,可以类比字典,如果要查...

    mysql5.5 linux版

    此外,MySQL 5.5在性能方面有所提升,包括InnoDB引擎的改进,支持更多的索引类型,如全文索引和空间索引,以及更高效的查询优化器。同时,它还支持分区表,可以更好地处理大数据量的场景。 在Linux环境中,你可以...

Global site tag (gtag.js) - Google Analytics