如何進行MySQL中的order by 優化

這篇文章將為大家詳細講解有關如何進行MySQL中的order by 優化，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

一 前言 
   介紹order by 的基本原理以及優化。如果覺得對order by原理了解不透徹，看完之后你可以了解到什么樣的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)
   

8. 
   

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能力的存儲上。

關于如何進行MySQL中的order by 優化就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。