`

一个MySQL order by优化案例

 
阅读更多

 

今天一个用户反馈SQL执行比较慢记录下分析过程:

 1.用户的SQL 

    SELECT * FROM ie_article_25 WHERE status = 3  AND addtime < 1284078469 order by addtime desc LIMIT 0, 1 \G

 

 2. 查看执行计划

   mysql> explain SELECT * FROM ie_article_25    WHERE status = 3  AND addtime < 1284078469  LIMIT 0, 1 \G

   *************************** 1. row ***************************

           id: 1

     select_type: SIMPLE

         table: ie_article_25

         type: ref

   possible_keys: addtime,IDX_STATUS_ADDTIME,IDX_Status,IDX_STATUS_CATID

          key: IDX_STATUS_ADDTIME

       key_len: 1

          ref: const

         rows: 7980

        Extra: Using index condition

  发现走了索引IDX_STATUS_ADDTIME,走了这个索引应该可以过滤掉数据同时可以避免排序了,按理说不应该有性能问题了。

 

  3.测试

  手动执行了一下"SELECT * FROM ie_article_25 WHERE status = 3  AND addtime < 1284078469 order by addtime desc LIMIT 0, 1"  执行时间为1.5s

然后又执行了下"SELECT * FROM ie_article_25  force index(addtime)  WHERE status = 3  AND addtime < 1284078469 order by addtime desc  LIMIT 0, 1 \G" 

时间竟然为0.04s。到这里猜测IDX_STATUS_ADDTIME只走了status索引,其实通过执行计划还是可以看出来的key_len: 1这个是因为status的定义为tinyint(1)。

 

  4.猜测

  其实还是不能理解为何没有使用addtime字段,这里where使用的是小于号,而且order by是desc只能猜测是否是索引信息没有更新的问题。

执行analyse table ie_article_25;更新表信息。

 

 再重新执行explain 原来的SQL发现执行计划果然变了,现在采用的是addtime索引了,执行的速度顺便变成了0.04s

*************************** 1. row ***************************

           id: 1

     select_type: SIMPLE

         table: ie_article_25

         type: range

   possible_keys: addtime,IDX_STATUS_ADDTIME,IDX_Status,IDX_STATUS_CATID

          key: addtime

       key_len: 4

          ref: NULL

         rows: 275648

        Extra: Using index condition; Using where

1 row in set (0.04 sec)

 

  5.结论

  这个执行计划还可以更优一点,走这个IDX_STATUS_ADDTIME索引,用force index(IDX_STATUS_ADDTIME)发现执行时间变了0.03s了。

 SELECT * FROM ie_article_25 force index(IDX_STATUS_ADDTIME)   WHERE status = 3  AND addtime < 1284078469  order by addtime desc LIMIT 0, 1

 

用户的部分表结构:

CREATE TABLE `ie_article_25` (

  `itemid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `addtime` int(10) unsigned NOT NULL DEFAULT '0',

  `status` tinyint(1) NOT NULL DEFAULT '0',

  PRIMARY KEY (`itemid`),

  KEY `addtime` (`addtime`),

  KEY `IDX_STATUS_ADDTIME` (`status`,`addtime`),

  KEY `IDX_CA_ST_AD` (`catid`,`status`,`addtime`),

  KEY `IDX_Status` (`status`),

  KEY `IDX_STATUS_CATID` (`status`,`catid`),

) ENGINE=MyISAM

 

分享到:
评论

相关推荐

    Mysql索引优化案例.pdf

    通过这些案例,我们可以得出一个结论:一个良好的索引策略对于提高数据库查询性能至关重要。在设计和维护数据库时,合理设计索引并持续进行性能监控和优化,可以显著提升数据库的运行效率和稳定性。

    mysql优化方案

    在无法使用索引的场景中,我们需要注意通过索引扫描的记录数超过 30%,变成全表扫描的情况,例如联合索引中,第一个索引列使用范围查询,或者模糊查询条件列最左以通配符% 开始等。此外,我们还需要注意内存表使用 ...

    mysql性能优化教程.pdf (by caoz)

    SELECT * FROM user WHERE area = '$area' AND sex = '$sex' ORDER BY lastlogin DESC LIMIT 0, 30; ``` - **解决方案**:创建`area + sex + lastlogin`的复合索引,这样可以同时利用地区和性别筛选结果,并按...

    mysql 优化 pdf

    MySQL是世界上最受欢迎的开源关系型数据库管理系统之一,其性能优化对于任何依赖于数据库的企业或项目都至关重要。在《mysql 优化 pdf》这份资料中,可能会涵盖以下关键知识点: 1. **索引优化**: - 索引类型:B-...

    MySQL索引优化的实际案例分析

    Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景,其优化原理也非常的简单,就是利用索引的有序性,优化器沿着索引的顺序扫描,在扫描到符合条件的M行数据后,停止扫描;看起来非常的简单,但是...

    mysql基础操作及案例分析

    分组查询**:使用`GROUP BY`子句按一个或多个列进行分组。 ```sql SELECT gender, COUNT(*) FROM student GROUP BY gender; ``` **4. 分页查询LIMIT**:限制返回的结果数量。 ```sql SELECT * FROM student ...

    Mysql性能优化教程.doc

    - 将索引视为一个排序序列,以便更好地理解查询过程。 #### 二、影响结果集 - **概念介绍**: - 影响结果集是指查询执行后返回的行数或记录数。 - 正确理解影响结果集对于优化查询至关重要。 - **获取影响结果集...

    mysql高级视频教程百度云(2019).txt

    48.MySQL高级_为排序使用索引OrderBy优化.avi 47.MySQL高级_in和exists.avi 46.MySQL高级_小表驱动大表.avi 45.MySQL高级_索引优化答疑补充和总结口诀.avi 44.MySQL高级_索引面试题分析.avi 43.MySQL高级...

    MySQL优化大揭秘.rar

    MySQL优化是数据库管理中至关重要的一个环节,它旨在提高数据查询效率、降低系统资源消耗,以确保服务的稳定性和响应速度。"MySQL优化大揭秘"这个压缩包中的"SQL.pdf"很可能包含了关于如何优化MySQL数据库的详细教程...

    MySQL 百万级分页优化(Mysql千万级快速分页)

    ### MySQL 百万级分页优化(Mysql千万级快速分页) #### 背景与挑战 在处理大规模数据集时,例如拥有数百万乃至数千万条记录的数据库表,传统的分页查询方法可能会遇到性能瓶颈。特别是使用`LIMIT`进行分页时,随着...

    北大青鸟mysql数据库学习资料 案例MySchool数据库代码等

    "MySchool"数据库案例是这些资料中的一个重要部分,它是一个实际的数据库应用实例,适合初学者实践操作和学习。 《使用DQL命令查询数据》学习资料,顾名思义,重点讲解了如何使用Data Query Language(DQL)来查询...

    MySQL优化案例系列-mysql分页优化

    这种查询方式是通过给定一个起始位置(start)和偏移量(offset),来获取一定范围内的数据。 示例SQL语句如下: ```sql SELECT * FROM t1 WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10; ``` 这条SQL语句将从表`...

    传智播客mysql的sql优化

    MySQL是世界上最受欢迎的关系型数据库管理系统之一,SQL优化是提高数据库性能的关键步骤,尤其对于有大量数据和高并发访问的应用来说更是如此。传智播客的MySQL SQL优化课程针对已有数据库开发经验的开发者,旨在...

    尚硅谷Java视频教程_MySQL高级视频

    尚硅谷_MySQL高级_为排序使用索引OrderBy优化 · 49.尚硅谷_MySQL高级_慢查询日志 · 50.尚硅谷_MySQL高级_批量插入数据脚本 · 51.尚硅谷_MySQL高级_用Show Profile进行sql分析 · 52.尚硅谷_MySQL高级_全局...

    一次MYSQL死锁分析案例1

    MySQL在检测到死锁时,会自动选择一个事务进行回滚以解除死锁。在这个案例中,MySQL选择了Transaction 1进行回滚。 **预防死锁的策略**: 1. **事务排序**: 确保事务的操作顺序是固定的,避免不同事务对同一资源的...

    Mysql开发教程及项目案例

    还会深入讲解SQL查询,包括SELECT语句的使用,如何联接表,以及使用WHERE、GROUP BY、HAVING和ORDER BY子句进行数据过滤和排序。此外,教程中还会涵盖视图、存储过程、触发器和函数等高级特性,这些都是提升数据库...

    分析一个MySQL的异常查询的案例

    5. 对于包含GROUP BY和ORDER BY的复杂查询,优化器可能需要使用临时表和文件排序,这会增加查询成本。针对这类查询,优化索引结构和查询语句可以显著提高性能。 通过案例的分析,我们可以看到理解和掌握MySQL查询...

Global site tag (gtag.js) - Google Analytics