溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

如何進行MySQL中的order by 優(yōu)化

發(fā)布時間：2021-11-16 09:42:02 來源：億速云 閱讀：148 作者：柒染 欄目：MySQL數(shù)據(jù)庫

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

一 前言 
   介紹order by 的基本原理以及優(yōu)化。如果覺得對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列為常數(shù)就行。
如何理解這句話呢？我們通過具體用例來解釋。
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工具查看執(zhí)行計劃中的extra中出現(xiàn)了Using filesort則說明sql沒有用到排序優(yōu)化。
案例一
文檔: 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字句沒有條件，優(yōu)化器選擇全表掃描和內(nèi)存排序。

  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 可以看出當(dāng)select 的字段包含在索引中時，能利用到索引排序功能，進行覆蓋索引掃描。
    使用select * 則不能利用覆蓋索引掃描且由于where語句沒有具體條件MySQL選擇了全表掃描且進行了排序操作。
案例二
  SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
使用組合索引中的一部分做等值查詢 ，另一部分作為排序字段。更嚴謹?shù)恼f法是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)中g(shù)id的有序性避免額外的排序工作. 我們基于本例解釋"即使ORDER BY語句不能精確匹配(組合)索引列也能使用索引,只要WHERE條件中的所有未使用的索引部分和所有額外的ORDER BY列為常數(shù)就行。"
該語句的order by  gid 并未精確匹配到組合索引(shid,gid)，where條件 shid利用了組合索引的最左前綴且為等值常量查詢，對order by 而言shid就是額外的字段，沒有出現(xiàn)在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的索引定位數(shù)據(jù)記錄，select *  有不在索引里面的字段，所以回表訪問組合索引列之外的數(shù)據(jù)，利用了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行，導(dǎo)致MySQL優(yōu)化器選擇了不同的執(zhí)行計劃。這個測試說明和shid的區(qū)分度有關(guān)。
案例六
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，當(dāng)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)

案例二
當(dāng)查詢條件使用了與order by不同的其他的索引，且值為常量，但排序字段是另一個聯(lián)合索引的非連續(xù)部分時
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 語句使用了和組合索引默認不同的排序規(guī)則
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
官方文檔中提示使用混合索引排序規(guī)則會導(dǎo)致額外排序，其實我們創(chuàng)建索引的時候可以做 (key_part1 DESC, key_part2 ASC)
案例四
當(dāng)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 的字段使用函數(shù)，和在where條件中使用函數(shù)索引一樣 ，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.)
當(dāng)查詢語句是多表連接，并且ORDER BY中的列并不是全部來自第1個用于搜索行的非常量表.(這是EXPLAIN輸出中的沒有使用const聯(lián)接類型的第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)

分析
  出現(xiàn)join的情況下不能利用索引其實有很多種，只要對a的訪問不滿足上面說的可以利用索引排序的情況都會導(dǎo)致額外的排序動作。但是當(dāng)where + order 復(fù)合要求，order by 有包含了其他表的列就會導(dǎo)致額外的排序動作。
案例七
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 索引本身不支持排序存儲，故不能利用到排序特性，將表轉(zhuǎn)化為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)

三 老生常談的優(yōu)化策略
 為了提高order by 查詢的速度，盡可能的利用索引的有序性進行排序，如果不能利用索引排序的功能，那么我們只能退而求其次優(yōu)化order by相關(guān)的緩存參數(shù)
1 增加 sort_buffer_size 大小，建議sort_buffer_size要足夠大能夠避免磁盤排序和合并排序次數(shù)。
2 增加 read_rnd_buffer_size 大小。
3 使用合適的列大小存儲具體的內(nèi)容，比如對于city字段 varchar(20)比varchar(200)能獲取更好的性能。
4 將tmpdir 目錄指定到os上面有足夠空間的具有比較高iops能力的存儲上。

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

向AI問一下細節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手