【MySQL】再說order by 優化

楊奇龍發表於2017-08-04
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. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  12. | 1  | SIMPLE      | tx    | index | NULL          | uniq_shid_gid | 8       | NULL | 24   | Using index |
  13. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  14. 1 row in set (0.00 sec)
  15. test [RW] 06:04:54 >desc select id,shid,gid from tx order by shid,gid;
  16. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  17. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra |
  18. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  19. | 1  | SIMPLE      | tx    | index | NULL          | uniq_shid_gid | 8       | NULL | 24   | Using index |
  20. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  21. 1 row in set (0.00 sec)
分析
    從type=index，extra=Using index 可以看出當select 的欄位包含在索引中時，能利用到索引排序功能，進行覆蓋索引掃描。
    使用select * 則不能利用覆蓋索引掃描且由於where語句沒有具體條件MySQL選擇了全表掃描且進行了排序操作。
案例二
  SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
使用組合索引中的一部分做等值查詢 ，另一部分作為排序欄位。更嚴謹的說法是where條件使用組合索引的左字首等值查詢，使用剩餘欄位進行order by排序。
  1. test [RW] 06:05:41 >desc select * from tx where shid= 2 order by gid;
  2. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref     | rows  | Extra|
  4. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  5. | 1  | SIMPLE      | tx    | ref  | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | Using where |
  6. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  7. 1 row in set (0.00 sec)
  8. test [RW] 11:30:13 >desc select * from tx where shid= 2 order by gid desc;
  9. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  10. | id | select_type | table | type | possible_keys | key | key_len | ref     | rows  | Extra|
  11. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  12. | 1  | SIMPLE      | tx    | ref  | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | Using where |
  13. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  14. 1 row in set (0.00 sec)
分析:
  where 條件字句可以基於 shid 進行索引查詢 並且利用(shid,gid)中gid的有序性避免額外的排序工作. 我們基於本例解釋"即使ORDER BY語句不能精確匹配(組合)索引列也能使用索引,只要WHERE條件中的所有未使用的索引部分和所有額外的ORDER BY列為常數就行。"
該語句的order by  gid 並未精確匹配到組合索引(shid,gid)，where條件 shid利用了組合索引的最左字首且為等值常量查詢，對order by 而言shid就是額外的欄位，沒有出現在order by子句中卻是組合索引的一部分。這樣的條件既可以使用索引來排序。

案例三
SELECT * FROM t1  ORDER BY key_part1 DESC, key_part2 DESC;
其實和案例一 類似，只是選擇了倒序。該sql不能利用索引的有序性，需要server層進行排序。
  1. test [RW] 06:06:30 >desc select * from tx order by shid desc,gid desc;
  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)
  8. 如果select 中選擇索引欄位，可以利用覆蓋索引掃描則可以利用索引進行排序。
  9. test [RW] 06:06:31 >desc select shid,gid from tx order by shid desc,gid desc;
  10. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  11. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
  12. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  13. | 1  | SIMPLE      | tx    | index | NULL          | uniq_shid_gid | 8       | NULL | 24   | Using index |
  14. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  15. 1 row in set (0.00 sec)
案例四
SELECT * FROM t1 WHERE key_part1 = 1  ORDER BY key_part1 DESC, key_part2 DESC;
本例和案例二類似，只是order by 字句中包含所有的組合索引列。
  1. test [RW] 06:06:55 >desc select * from tx where shid=4 order by shid desc ,gid desc;
  2. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  3. | id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
  4. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  5. | 1  | SIMPLE      | tx    | ref  | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | Using where |
  6. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  7. 1 row in set (0.00 sec)
分析:
   where shid=4 可以利用shid的索引定位資料記錄，select *  有不在索引裡面的欄位，所以回表訪問組合索引列之外的資料，利用了gid索引的有序性避免了排序工作。
案例五
SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC;
  1. test [RW] 11:40:48 >desc select * from tx where shid>5 order by shid desc ;
  2. +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
  4. +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
  5. | 1  | SIMPLE      | tx    | ALL  | uniq_shid_gid | NULL | NULL    | NULL | 24   | Using where; Using filesort |
  6. +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
  7. 1 row in set (0.00 sec)

  9. test [RW] 11:47:25 >desc select * from tx where shid>13 order by shid desc ;
  10. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
  11. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                 |
  12. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
  13. | 1  | SIMPLE      | tx    | range | uniq_shid_gid | uniq_shid_gid | 4       | NULL | 2    | Using index condition |
  14. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
  15. 1 row in set (0.00 sec)
分析
  表總共24行,其中大於5的有16行，大於13的2行，導致MySQL優化器選擇了不同的執行計劃。這個測試說明和shid的區分度有關。
案例六
SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;
利用組合索引字首索引進行ref等值查詢，其他欄位進行範圍查詢，order by 非等值的欄位
  1. test [RW] 06:10:41 >desc select * from tx where shid=6 and gid>1 order by gid;
  2. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
  3. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                 |
  4. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
  5. | 1  | SIMPLE      | tx    | range | uniq_shid_gid | uniq_shid_gid | 8       | NULL | 3    | Using index condition |
  6. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
  7. 1 row in set (0.02 sec)
分析:
    利用shid=6的進行索引查詢記錄到了MySQL的ICP特性，無排序操作。為啥使用ICP 這個待確認。

2.4 不能利用索引排序的分析
案例一
order by語句使用了多個不同的索引
SELECT * FROM t1 ORDER BY key1, key2;
  1. test [RW] 09:44:03 >desc select * from tx order by price, type;
  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使用了不同的索引列，在儲存上順序存在不一致的可能性，MySQL會選擇排序操作。
特例 因為所有的輔助索引裡面都包含主鍵id，當where 欄位加上order by欄位溝通完整的索引時 ，可以避免filesort的
  1. test [RW] 11:20:10 >desc select * from tx where type=1 order by id;
  2. +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
  3. | id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra       |
  4. +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
  5. | 1  | SIMPLE      | tx    | ref  | idx_type      | idx_type | 1       | const | 6    | Using where |
  6. +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
  7. 1 row in set (0.00 sec)
案例二
當查詢條件使用了與order by不同的其他的索引，且值為常量，但排序欄位是另一個聯合索引的非連續部分時
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part1, key_part3;

  1. test [RW] 11:19:17 >desc select * from tx where type=1 order by gid;
  2. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  3. | id | select_type | table | type | possible_keys | key      | key_len | ref | rows   | Extra                       |
  4. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  5. | 1  | SIMPLE      | tx    | ref  | idx_type      | idx_type | 1       | const | 6    | Using where; Using filesort |
  6. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  7. 1 row in set (0.00 sec)
  8. test [RW] 11:21:08 >desc select * from tx where type=1 order by shid;
  9. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  10. | id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                       |
  11. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  12. | 1  | SIMPLE      | tx    | ref  | idx_type      | idx_type | 1       | const | 6    | Using where; Using filesort |
  13. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  14. 1 row in set (0.00 sec)
分析 
  與案例一一致，key2 的順序語句key1(key_part1)儲存排序不一樣的情況下，MySQL 都會選擇filesort 。
案例三
order by 語句使用了和組合索引預設不同的排序規則
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
官方文件中提示使用混合索引排序規則會導致額外排序，其實我們建立索引的時候可以做 (key_part1 DESC, key_part2 ASC)
案例四
當where 條件中利用的索引與order by 索引不同時，與案例二有相似性。
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
  1. test [RW] 11:19:44 >desc select * from tx where type=1 order by shid;
  2. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  3. | id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                       |
  4. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  5. | 1  | SIMPLE      | tx    | ref  | idx_type      | idx_type | 1       | const | 6    | Using where; Using filesort |
  6. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  7. 1 row in set (0.00 sec)
  8. test [RW] 11:20:07 >desc select * from tx where type=1 order by shid,gid;
  9. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  10. | id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                       |
  11. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  12. | 1  | SIMPLE      | tx    | ref  | idx_type      | idx_type | 1       | const | 6    | Using where; Using filesort |
  13. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  14. 1 row in set (0.00 sec)
案例五
order by 欄位使用了表示式
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

  1. test [RW] 11:53:39 >desc select * from tx where shid=3 order by -shid;
  2. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------------+
  3. | id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra                       |
  4. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------------+
  5. | 1  | SIMPLE      | tx    | ref  | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | Using where; Using filesort |
  6. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------------+
  7. 1 row in set (0.00 sec)
  1. test [RW] 11:56:26 >desc select * from tx where shid=3 order by shid;
  2. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
  3. | id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra |
  4. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
  5. | 1  | SIMPLE      | tx    | ref  | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | NULL |
  6. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
分析
    order by 的欄位使用函式，和在where條件中使用函式索引一樣 ，MySQL都無法利用到索引。
案例六
The query joins many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)
當查詢語句是多表連線，並且ORDER BY中的列並不是全部來自第1個用於搜尋行的非常量表.(這是EXPLAIN輸出中的沒有使用const聯接型別的第1個表)

  1. test [RW] 12:32:43 >explain select shid,gid from tx a left join goods_type b on a.shid=b.id where a.shid=2 order by a.gid,b.id;
  2. +----+-------------+-------+-------+---------------+---------------+---------+-------+------+-------------------------------+
  3. | id | select_type | table | type  | possible_keys | key           | key_len | ref   | rows | Extra                         |
  4. +----+-------------+-------+-------+---------------+---------------+---------+-------+------+-------------------------------+
  5. | 1  | SIMPLE      | a     | ref   | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | Using index; Using temporary;                                                                                                Using filesort                |
  6. | 1  | SIMPLE      | b     | const | PRIMARY       | PRIMARY       | 4       | const | 1    | Using index                   |
  7. +----+-------------+-------+-------+---------------+---------------+---------+-------+------+----------------------------------------------+
  8. 2 rows in set (0.00 sec)
  9. test [RW] 12:32:44 >explain select shid,gid from tx a left join goods_type b on a.shid=b.id where a.shid=2 order by a.gid;
  10. +----+-------------+-------+-------+---------------+---------------+---------+-------+------+--------------------------+
  11. | id | select_type | table | type  | possible_keys | key           | key_len | ref   | rows | Extra |
  12. +----+-------------+-------+-------+---------------+---------------+---------+-------+------+--------------------------+
  13. | 1  | SIMPLE      | a     | ref   | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | Using where; Using index |
  14. | 1  | SIMPLE      | b     | const | PRIMARY       | PRIMARY       | 4       | const | 1    | Using index |
  15. +----+-------------+-------+-------+---------------+---------------+---------+-------+------+--------------------------+
  16. 2 rows in set (0.00 sec)
分析
  出現join的情況下不能利用索引其實有很多種，只要對a的訪問不滿足上面說的可以利用索引排序的情況都會導致額外的排序動作。但是當where + order 複合要求，order by 有包含了其他表的列就會導致額外的排序動作。
案例七
sql中包含的order by 列與group by 列不一致 
  1. test [RW] 11:26:54 >desc select * from tx group by shid order by gid;
  2. +----+-------------+-------+-------+---------------+---------------+---------+------+------+---------------------------------+
  3. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                          |
  4. +----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------------+
  5. | 1  | SIMPLE      | tx    | index | uniq_shid_gid | uniq_shid_gid | 8       | NULL | 24   | Using temporary; Using filesor |
  6. +----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------------+
  7. 1 row in set (0.00 sec)
group by 本身會進行排序的操作,我們可以顯示的注讓group by不進行額外的排序動作。
  1. test [RW] 12:09:52 >desc select * from tx group by shid order by null;
  2. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
  3. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra |
  4. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
  5. | 1  | SIMPLE      | tx    | index | uniq_shid_gid | uniq_shid_gid | 8       | NULL | 24   | NULL |
  6. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
  7. 1 row in set (0.00 sec)
案例八
索引本身不支援排序儲存 比如，hash索引。
  1. CREATE TABLE `hash_test` (
  2.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT ,
  3.   `name` varchar(20) NOT NULL COMMENT '名稱',
  4.   PRIMARY KEY (`id`),
  5.   KEY `name` (`name`)
  6. ) ENGINE=MEMORY ;
  7. INSERT INTO `hash_test` (`id`, `name`) VALUES
  8. (1, '張三'),
  9. (2, '李四');
  10. test [RW] 12:07:27 >explain select * from hash_test force index(name) order by name;
  11. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  12. | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  13. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  14. | 1  | SIMPLE      | hash_test | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using filesort |
  15. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  16. 1 row in set (0.00 sec)
  17. test [RW] 12:07:48 >explain select * from hash_test order by name;
  18. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  19. | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  20. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  21. | 1  | SIMPLE      | hash_test | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using filesort |
  22. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  23. 1 row in set (0.00 sec)
  24. test [RW] 12:07:53 >alter table hash_test ENGINE=innodb;
  25. Query OK, 2 rows affected (0.45 sec)
  26. Records: 2 Duplicates: 0 Warnings: 0
  27. test [RW] 12:08:33 >explain select * from hash_test order by name;
  28. +----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
  29. | id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra |
  30. +----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
  31. | 1  | SIMPLE      | hash_test | index | NULL          | name | 82      | NULL | 1    | Using index |
  32. +----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
  33. 1 row in set (0.00 sec)
分析 
   hash 索引本身不支援排序儲存，故不能利用到排序特性，將錶轉化為innodb再次查詢，避免了filesort
案例九
order by的索引使用部分字串 比如 key idx_name(name(2))

  1. test [RW] 12:08:37 >alter table hash_test drop key name ,add key idx_name(name(2));
  2. Query OK, 0 rows affected (0.03 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0
  4. test [RW] 12:09:50 >explain select * from hash_test order by name;
  5. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  6. | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  7. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  8. | 1  | SIMPLE      | hash_test | ALL  | NULL          | NULL | NULL    | NULL | 1    | Using filesort |
  9. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  10. 1 row in set (0.00 sec)
三 老生常談的優化策略
 為了提高order by 查詢的速度，儘可能的利用索引的有序性進行排序，如果不能利用索引排序的功能，那麼我們只能退而求其次優化order by相關的快取引數
1 增加 sort_buffer_size 大小，建議sort_buffer_size要足夠大能夠避免磁碟排序和合並排序次數。
2 增加 read_rnd_buffer_size 大小。
3 使用合適的列大小儲存具體的內容，比如對於city欄位 varchar(20)比varchar(200)能獲取更好的效能。
4 將tmpdir 目錄指定到os上面有足夠空間的具有比較高iops能力的儲存上。

四 推薦文章
[1] MySQL order by 優化的那些事兒
[2] 官方文件 
[3] order by 結果不準確的問題及解決  
[4] MySQL排序原理與案例分析 
[5] order by 原理以及優化 
看完本文 如果您覺得有所收穫 ，可以請 北在南方 一瓶飲料 ^_^




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-2143086/，如需轉載，請註明出處，否則將追究法律責任。

相關文章