北在南方

每天进步一点点

  • 博客访问: 6783134
  • 博文数量: 1031
  • 用 户 组: 普通用户
  • 注册时间: 2009-10-07 13:14
个人简介

MySQL DBA NoSQL DEVOPS

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(1031)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
【MySQL】再说order by 优化 2017-08-04 13:45:22

分类: MySQL

一 前言 
   为什么是再说呢?因为前面已经写过一篇blog,介绍order by 的基本原理以及优化。如果觉得对order by原理了解不透彻可以参考其他同行的文章《MySQL排序内部原理探秘》.本文是基于官网文档的二刷(基本翻译+测试验证),看完本文可以了解到什么样的select + order by 语句可以使用索引,什么样的不能利用到索引排序。
二 分析  
2.1 官方标准介绍
对于select  order by语句如何能够利用到索引,官方表述如下:
  1. "The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause."
翻译一下就是
即使ORDER BY语句不能精确匹配(组合)索引列也能使用索引,只要WHERE条件中的所有未使用的索引部分和所有额外的ORDER BY列为常数就行。
如何理解这句话呢?我们通过具体用例来解释。
2.2 准备工作
  1. CREATE TABLE `tx` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  3.   `shid` int(11) NOT NULL COMMENT '商店ID',
  4.   `gid` int(11) NOT NULL COMMENT '物品ID',
  5.   `type` tinyint(1) NOT NULL COMMENT '支付方式',
  6.   `price` int(10) NOT NULL COMMENT '物品价格',
  7.   `comment` varchar(200) NOT NULL COMMENT '备注',
  8.   PRIMARY KEY (`id`),
  9.   UNIQUE KEY `uniq_shid_gid` (`shid`,`gid`),
  10.   KEY `idx_price` (`price`),
  11.   KEY `idx_type` (`type`)
  12. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;
  13. INSERT INTO `tx` (`shid`, `gid`, `type`, `price`, `comment`) VALUES (6, 2, 0, '399', '2'),(6, 5, 0, '288', '2'),(6, 11, 0, '10', '2');
  14. (1, 1, 0, '10', 'sd'),
  15. (2, 55, 0, '210', 'sa'),
  16. (2, 33, 1, '999', 'a'),
  17. (3, 17, 0, '198', 'b'),
  18. (3, 22, 1, '800', 'e'),
  19. (4, 12, 0, '120', 'f'),
  20. (4, 73, 0, '250', 'd'),
  21. (5, 61, 0, '10', 'c'),
  22. (6, 1, 0, '210', '2'),
  23. (7, 9, 1, '999', '44'),
  24. (7, 2, 0, '198', '45'),
  25. (8, 3, 1, '800', 'rt'),
  26. (9, 4, 0, '120', 'pr'),
  27. (9, 6, 0, '250', 'x'),
  28. (10, 8, 0, '10', 'w'),
  29. (12, 9, 0, '210', 'w'),
  30. (12, 10, 1, '999', 'q'),
  31. (13, 11, 0, '198', ''),
  32. (13, 12, 1, '800', ''),
  33. (14, 13, 0, '120', ''),
  34. (14, 19, 0, '250', '');
  35. CREATE TABLE `goods_type` (
  36.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  37.   `type` int NOT NULL COMMENT '类型',
  38.   `name` varchar(20) NOT NULL COMMENT '名称',
  39.   PRIMARY KEY (`id`)
  40. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  41. INSERT INTO `goods_type` (`id`, `type`, `name`) VALUES
  42. (1, 1, 'hw手机'),
  43. (2, 0, 'xiaomi'),
  44. (3, 1, 'apple')

2.3 能够利用索引的例子分析
官方的文档 中介绍有7个例子可以使用索引进行排序。如果使用explain/desc工具查看执行计划中的extra中出现了Using filesort则说明sql没有用到排序优化。
案例一
文档: SELECT * FROM t1 ORDER BY key_part1,key_part2,...; 
  1. test [RW] 06:03:52 >desc select * from tx order by shid,gid;
  2. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  4. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  5. | 1  | SIMPLE      | tx    | ALL  | NULL          | NULL | NULL    | NULL | 24   | Using filesort |
  6. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  7. 1 row in set (0.00 sec)
分析:
  显然上述sql没有利用到索引排序. type=ALL Extra=Using filesort,因为where字句没有条件,优化器选择全表扫描和内存排序。
  1. test [RW] 06:04:39 >desc select gid from tx order by shid,gid;
  2. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  3. | id | select_type | table | type  | possible_keys | key         | key_len   | ref  | rows | Extra       |
  4. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  5. | 1  | SIMPLE      | tx    | index | NULL          | uniq_shid_gid | 8       | NULL | 24   | Using index |
  6. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  7. 1 row in set (0.00 sec)
  8. test [RW] 06:04:47 >desc select shid,gid from tx order by shid,gid;
  9. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  10. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra |
  11. +----+-------------+-------