`
骑猪逛街666
  • 浏览: 141517 次
  • 性别: Icon_minigender_2
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

[MySQL 5.6优化] --order by limit x,x 优化

mysql算法工作csql 
阅读更多
阅读原文请点击：http://click.aliyun.com/m/23632/
摘要： 简介： order by limit x ,x 在实际工作中有各种业务需求要有order by的排序，有时候处理不好则会造成系统宕机！ 原理： a.通过索引来获取排序 b.通过内部算法获取排序： 案例 具体SQL： SELECT c.

简介： order by limit x ,x 在实际工作中有各种业务需求要有order by的排序，有时候处理不好则会造成系统宕机！
原理：
a.通过索引来获取排序
b.通过内部算法获取排序：

案例

具体SQL：

SELECT c.order_price orderPrice,
       c.preferential_amount preferentialAmount,
       c.order_sumprice orderSumprice,
       cast(c.mode as SIGNED) rechargeType,
       cast(c.pay_type as SIGNED) payType,
       cast(c.type as SIGNED) appType,
       c.order_sn orderSn,
       c.create_time payTime,
       u.nickname nickName,
       u.headimgurl headImg,
       u.real_name memberName,
       cast(c.pay_status as SIGNED) payStatus
  FROM t_order c
  LEFT JOIN t_user u ON c.user_id= u.id
WHERE c.token= '1392044'
   and c.pay_status in (1, 3)
   and c.refund_status= 0
   and c.store_id= 36574
order by c.create_time desc
limit 0,15
表结构：

CREATE TABLE `t_order ` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_sn` varchar(30) DEFAULT NULL COMMENT ',
  `preferential_amount` decimal(10,2) DEFAULT '0.00' COMMENT,
  `order_sumprice` decimal(10,2) DEFAULT '0.00' COMMENT ,
  `mode` tinyint(3) unsigned DEFAULT '1' COMMENT '',
  `pay_type` tinyint(1) DEFAULT '1' COMMENT '',
  `type` tinyint(4) DEFAULT '1' COMMENT '',
  `create_time` int(10) unsigned DEFAULT '0' COMMENT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_sn` (`order_sn`),
  KEY `IDX_CR_MO_TO` (`create_time`,`token`,`user_id`),
  KEY `idx_store_token_createtime` (`store_id`,`token`,`create_time`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=53925518 DEFAULT CHARSET=utf8

CREATE TABLE `t_user ` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nickname` varchar(20) DEFAULT NULL COMMENT '',
  `headimgurl` varchar(255) DEFAULT NULL,
  `real_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `openid` (`openid`),
  KEY `IDX_NICKNAME` (`nickname')
) ENGINE=InnoDB AUTO_INCREMENT=13974852 DEFAULT CHARSET=utf8
1、SQL优化器默认选择索引执行计划为：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys: idx_tscc,IDX_CR_MO_TO
          key: idx_tscp
      key_len: 68
          ref: const,const
         rows: 26980
        Extra: Using index condition; Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
       table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: youdian_life_sewsq.c.user_id
         rows: 1
        Extra: Using where

共返回 2 行记录,花费 5 ms.
执行时间：共返回 15 行记录,花费 128 ms.

2、当使用IDX_CR_MO_TO (create_time,token,user_id)索引时，避免Using filesortl临时表，减少rows
执行计划为：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: index
possible_keys:
          key: IDX_CR_MO_TO
      key_len: 73
          ref:
         rows: 15
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: youdian_life_sewsq.c.user_id
         rows: 1
        Extra: Using where
执行时间：共返回 15 行记录,花费 234 ms

3、当使用limit 100时强制索引效果：

mysql>explain SELECT c.order_price orderPrice,
c.preferential_amount preferentialAmount,
c.order_sumprice orderSumprice,
cast(c.mode as SIGNED) rechargeType,
cast(c.pay_type as SIGNED) payType,
cast(c.type as SIGNED) appType,
c.order_sn orderSn,
c.create_time payTime,
u.nickname nickName,
u.headimgurl headImg,
u.real_name memberName,
cast(c.pay_status as SIGNED) payStatus
  FROM tp_order c force index(IDX_CR_MO_TO)
  LEFT JOIN tp_user u ON c.user_id= u.id
WHERE c.token= '1392044'
   and c.pay_status in (1, 3)
   and c.refund_status= 0
   and c.store_id= 36574
order by c.create_time desc
limit 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: index
possible_keys:
          key: IDX_CR_MO_TO
      key_len: 73
          ref:
         rows: 100
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: youdian_life_sewsq.c.user_id
         rows: 1
        Extra: Using where
3、当limit 为1000,10时候的效果：

强制索引：
mysql>explain SELECT c.order_price orderPrice,
c.preferential_amount preferentialAmount,
c.order_sumprice orderSumprice,
cast(c.mode as SIGNED) rechargeType,
cast(c.pay_type as SIGNED) payType,
cast(c.type as SIGNED) appType,
c.order_sn orderSn,
c.create_time payTime,
u.nickname nickName,
u.headimgurl headImg,
u.real_name memberName,
cast(c.pay_status as SIGNED) payStatus
  FROM tp_order c force index(IDX_CR_MO_TO)
  LEFT JOIN tp_user u ON c.user_id= u.id
WHERE c.token= '1392044'
   and c.pay_status in (1, 3)
   and c.refund_status= 0
   and c.store_id= 36574
order by c.create_time desc
limit 1000,10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: index
possible_keys:
          key: IDX_CR_MO_TO
      key_len: 73
          ref:
         rows: 1010
        Extra: Using where
*************************** 2. row ***************************
          id: 1
  select_type: SIMPLE
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: youdian_life_sewsq.c.user_id
         rows: 1
        Extra: Using where
默认执行计划：
************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys:  idx_tscc,IDX_CR_MO_TO
          key: idx_tscp
      key_len: 68
          ref: const,const
         rows: 27002
        Extra: Using index condition; Using where; Using filesort
*************************** 2. row ***************************
          id: 1
  select_type: SIMPLE
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: youdian_life_sewsq.c.user_id
         rows: 1
        Extra: Using where
4、limit 1000,10执行时间对比

使用idx_tscc索引执行时间：
mysql>SELECT c.order_price orderPrice,
c.preferential_amount preferentialAmount,
c.order_sumprice orderSumprice,
cast(c.mode as SIGNED) rechargeType,
cast(c.pay_type as SIGNED) payType,
cast(c.type as SIGNED) appType,
c.order_sn orderSn,
c.create_time payTime,
u.nickname nickName,
u.headimgurl headImg,
u.real_name memberName,
cast(c.pay_status as SIGNED) payStatus
  FROM tp_order c
  LEFT JOIN tp_user u ON c.user_id= u.id
WHERE c.token= '1392044'
   and c.pay_status in (1, 3)
   and c.refund_status= 0
   and c.store_id= 36574
order by c.create_time desc
limit 1000,10\G
共返回 10 行记录,花费 220 ms.

使用强制索引执行时间：
mysql>SELECT c.order_price orderPrice,
c.preferential_amount preferentialAmount,
c.order_sumprice orderSumprice,
cast(c.mode as SIGNED) rechargeType,
cast(c.pay_type as SIGNED) payType,
cast(c.type as SIGNED) appType,
c.order_sn orderSn,
c.create_time payTime,
u.nickname nickName,
u.headimgurl headImg,
u.real_name memberName,
cast(c.pay_status as SIGNED) payStatus
  FROM tp_order c  force index(IDX_CR_MO_TO)
阅读原文请点击：http://click.aliyun.com/m/23632/
分享到：
| 云数据库Redis版的开源之路
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    【DISTINCT】优化之MySQL官方文档翻译

    因此，对于`DISTINCT`查询的更多优化细节，可以参考[MySQL官方文档](http://dev.mysql.com/doc/refman/5.6/en/group-by-optimization.html)中关于`GROUP BY`优化的部分。 #### 四、DISTINCT与LIMIT的结合使用 当`...

    MySQL 5.1参考手册

    7.2.12. MySQL如何优化ORDER BY 7.2.13. MySQL如何优化GROUP BY 7.2.14. MySQL如何优化LIMIT 7.2.15. 如何避免表扫描 7.2.16. INSERT语句的速度 7.2.17. UPDATE语句的速度 7.2.18. DELETE语句的速度 7.2.19. 其它...

    专题23：Mysql 面试题（卷王专供+ 史上最全 + 2023面试必备）-V106-from-尼恩Java面试宝典.pdf

    - **ORDER BY优化**: - 对大量数据进行排序时，可以通过预排序、使用索引覆盖等方法进行优化。 ### 8. MySQL日志管理 - **WAL (Write-Ahead Logging)**: - 一种日志先写机制，确保事务提交之前先将日志写入磁盘。...

    Mysql子查询IN中使用LIMIT应用示例

    WHERE mapply.aid IN (SELECT aid FROM `mapply` WHERE state = $state ORDER BY `atime`, `uid` DESC LIMIT 0,10) ``` 这里的意图是只选取`mapply`表中状态为`$state`的前10条记录的`aid`值，然后在主查询中匹配...

    MySQL 5.1官方简体中文参考手册

    7.2.12. MySQL如何优化ORDER BY 7.2.13. MySQL如何优化GROUP BY 7.2.14. MySQL如何优化LIMIT 7.2.15. 如何避免表扫描 7.2.16. INSERT语句的速度 7.2.17. UPDATE语句的速度 7.2.18. DELETE语句的速度 7.2.19. 其它...

    MySql 5.1 参考手册.chm

    7.2.12. MySQL如何优化ORDER BY 7.2.13. MySQL如何优化GROUP BY 7.2.14. MySQL如何优化LIMIT 7.2.15. 如何避免表扫描 7.2.16. INSERT语句的速度 7.2.17. UPDATE语句的速度 7.2.18. DELETE语句的速度 7.2.19. 其它...

    MySQL5.1参考手册官方简体中文版

    7.2.12. MySQL如何优化ORDER BY 7.2.13. MySQL如何优化GROUP BY 7.2.14. MySQL如何优化LIMIT 7.2.15. 如何避免表扫描 7.2.16. INSERT语句的速度 7.2.17. UPDATE语句的速度 7.2.18. DELETE语句的速度 7.2.19. 其它...

    MySQL 5.1参考手册 （中文版）

    7.2.12. MySQL如何优化ORDER BY 7.2.13. MySQL如何优化GROUP BY 7.2.14. MySQL如何优化LIMIT 7.2.15. 如何避免表扫描 7.2.16. INSERT语句的速度 7.2.17. UPDATE语句的速度 7.2.18. DELETE语句的速度 7.2.19. 其它...

    MySQL 5.1参考手册中文版

    7.2.12. MySQL如何优化ORDER BY 7.2.13. MySQL如何优化GROUP BY 7.2.14. MySQL如何优化LIMIT 7.2.15. 如何避免表扫描 7.2.16. INSERT语句的速度 7.2.17. UPDATE语句的速度 7.2.18. DELETE语句的速度 7.2.19. ...

    MySQL 5.1中文手冊

    7.2.12. MySQL如何优化ORDER BY 7.2.13. MySQL如何优化GROUP BY 7.2.14. MySQL如何优化LIMIT 7.2.15. 如何避免表扫描 7.2.16. INSERT语句的速度 7.2.17. UPDATE语句的速度 7.2.18. DELETE语句的速度 7.2.19. 其它...

    老司机总结的12条 SQL 优化方案（非常实用）.docx

    在 MariaDB 10/Mysql 5.6 版本中，采用 join 关联方式对其进行了优化，自动转换为 INNER JOIN 语句。但请注意的是：优化只针对 SELECT 有效，对 UPDATE/DELETE 子查询无效，固生产环境应避免使用子查询。 2. 用 IN ...

    MYSQL中文手册

    7.2.12. MySQL如何优化ORDER BY 7.2.13. MySQL如何优化GROUP BY 7.2.14. MySQL如何优化LIMIT 7.2.15. 如何避免表扫描 7.2.16. INSERT语句的速度 7.2.17. UPDATE语句的速度 7.2.18. DELETE语句的速度 7.2.19. ...

    程序员必备：SQL优化技巧

    - 使用`INDEX Condition Pushdown (ICP)`，MySQL 5.6后引入的新特性，允许在存储引擎层进行部分条件过滤，减少回表操作。 5. **性能分析工具**： - `SHOW PROFILE`可以查看SQL执行的详细时间分布，帮助找出性能...

    mysql官方中文参考手册

    7.2.12. MySQL如何优化ORDER BY 7.2.13. MySQL如何优化GROUP BY 7.2.14. MySQL如何优化LIMIT 7.2.15. 如何避免表扫描 7.2.16. INSERT语句的速度 7.2.17. UPDATE语句的速度 7.2.18. DELETE语句的速度 7.2.19. 其它...

    mysql5.1中文手册

    MySQL如何优化ORDER BY 7.2.13. MySQL如何优化GROUP BY 7.2.14. MySQL如何优化LIMIT 7.2.15. 如何避免表扫描 7.2.16. INSERT语句的速度 7.2.17. UPDATE语句的速度 7.2.18. DELETE语句的速度 ...

    c# MySql与SQL sever的区别.docx

    MySQL则使用`LIMIT`，如`SELECT * FROM table ORDER BY id DESC LIMIT n`。 - `GROUP BY`：SQL Server要求`GROUP BY`部分与`SELECT`部分一致，除非是统计类字段。MySQL允许更多的灵活性，只要求聚合函数与`GROUP BY...

    如何修改Mysql中group_concat的长度限制

    SELECT GROUP_CONCAT(f_a ORDER BY f_a SEPARATOR '_') FROM t_one GROUP BY f_b; ``` - **自定义分隔符**： 通过`SEPARATOR`关键字可以设置自定义分隔符，如上所示。 - **与`CONCAT`结合使用**： 当`...

    My SQL group by取同组第一条

    在MySQL 8.0中，为了解决这个问题，你需要确保`ORDER BY`和`LIMIT`子句结合使用，因为`GROUP BY`不再保证按排序顺序返回结果。正确的查询可能是这样的： ```sql SELECT SwipeID, MemberID, AddTime FROM ( SELECT ...

Magicbox
Global site tag (gtag.js) - Google Analytics