MySQL的order?by如何避免"未命中索引"(推薦)
不少同學私信我說,用Explain查看Order By語句執(zhí)行計劃時經(jīng)常發(fā)現(xiàn)用不上索引,難道花好多時間和資源創(chuàng)建的聯(lián)合索引都擺爛了?今天我把幾個同學遇到的情況整理出來,做一個Order By使用索引的坑點分享。希望對你有用。
要學會如何使用,你先要搞清楚:1、怎么看SQL是否用上了索引;2、怎么寫SQL能避開出錯點。
對了,如果對Explain查看索引命中情況比較了解的同學可以直接跳轉(zhuǎn)第三部分。
一、測試數(shù)據(jù)導入
-- ---------------------------- -- Table structure for t_lol -- ---------------------------- DROP TABLE IF EXISTS `t_lol`; CREATE TABLE `t_lol` ( `id` int(0) NOT NULL AUTO_INCREMENT, `hero_title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `hero_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `price` int(0) NULL DEFAULT NULL, `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_title_name_price`(`hero_title`, `hero_name`, `price`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_lol -- ---------------------------- INSERT INTO `t_lol` VALUES (1, '刀鋒之影', '泰隆', 6300, NULL); INSERT INTO `t_lol` VALUES (2, '迅捷斥候', '提莫', 6300, NULL); INSERT INTO `t_lol` VALUES (3, '光輝女郎', '拉克絲', 1350, NULL); INSERT INTO `t_lol` VALUES (4, '發(fā)條魔靈', '奧莉安娜', 6300, NULL); INSERT INTO `t_lol` VALUES (5, '至高之拳', '李青', 6300, NULL); INSERT INTO `t_lol` VALUES (6, '無極劍圣', '易', 450, NULL); INSERT INTO `t_lol` VALUES (7, '疾風劍豪', '亞索', 6300, NULL); INSERT INTO `t_lol` VALUES (8, '女槍', '好運', 1350, NULL);
二、Explain查看索引使用情況
查看Explain執(zhí)行計劃是我們開發(fā)人員必須掌握的一個技能,下一篇我會整理Explain執(zhí)行計劃的詳細查看方法。
本篇文章是查看索引使用情況,我們通過key
列、Extra
列判斷足矣。key
列即展示使用到的索引,下面重點看一下當使用到索引
即key列
有值時,Extra列展示的相關(guān)信息都代表啥。
2-1、Using index
構(gòu)成了覆蓋索引,where篩選條件也符合索引的最左前綴原則。
2-2、Using where,Using index
- 查詢的列被索引覆蓋,并且where篩選條件是索引列之一但是不是索引的前導列,無法直接通過索引查找來查詢到符合條件的數(shù)據(jù)。
- 查詢的列被索引覆蓋,并且where篩選條件是索引列前導列的一個范圍,同樣意味著無法直接通過索引查找查詢到符合條件的數(shù)據(jù)。
2-3、NULL
既沒有Using index,也沒有Using where,Using index,也沒有using where。
查詢的列未被索引覆蓋,并且where篩選條件是索引的前導列。意味著可能用到了索引(我們可以根據(jù)key列判斷是否用上索引)
,但是部分字段未被索引覆蓋,必須通過回表
來實現(xiàn)。
2-4、Using where
- 查詢的列未被索引覆蓋,where篩選條件非索引的前導列;
- 查詢的列未被索引覆蓋,where篩選條件非索引列;
using where 意味著通過表掃描的方式進行where條件的過濾,也就是沒找到可用的索引。
當然也有特例,如果優(yōu)化器判斷索引掃描+回表的代價
相比全表掃描
的代價更大,則主動放棄索引的使用。
如果explain中type列值為all,說明MySQL認為全表掃描是一種比較低的代價。
2-5、Using index condition
- 查詢的列不全在索引中,where條件中是一個前導列的范圍查詢;
- 查詢列不完全被索引覆蓋,但查詢條件可以使用到索引;
三、Order By的使用示例
3-1、原表索引數(shù)據(jù)
mysql> show index from t_lol; +-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t_lol | 0 | PRIMARY | 1 | id | A | 8 | NULL | NULL | | BTREE | | | YES | NULL | | t_lol | 1 | idx_title_name_price | 1 | hero_title | A | 8 | NULL | NULL | YES | BTREE | | | YES | NULL | | t_lol | 1 | idx_title_name_price | 2 | hero_name | A | 8 | NULL | NULL | YES | BTREE | | | YES | NULL | | t_lol | 1 | idx_title_name_price | 3 | price | A | 8 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 4 rows in set (0.00 sec)
該表中有一個主鍵索引PRIMARY
和一個聯(lián)合索引idx_title_name_price(hero_title, hero_name, price)
3-2、不含where語句的示例
示例1:
直接select聯(lián)合索引三列,如下,可構(gòu)造覆蓋索引,不回表直接返回索引文件中的數(shù)據(jù)。
mysql> -- 使用了覆蓋索引 mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol; +----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_lol | NULL | index | NULL | idx_title_name_price | 267 | NULL | 8 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
示例2:
加上ORDER BY hero_title
,功能和示例1完全相同,因為BTree索引有序,省去了自左向右各索引列的排序工作。
mysql> -- 同上,使用了覆蓋索引(由于B樹索引類型有序,省去了排序) mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol ORDER BY hero_title; +----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_lol | NULL | index | NULL | idx_title_name_price | 267 | NULL | 8 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
示例3:
使用了覆蓋索引,MySQL 8.0新特性-倒敘索引 desc index。
mysql> -- 使用了覆蓋索引,MySQL 8.0新特性-倒敘索引 desc index mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol ORDER BY hero_title desc; +----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+----------------------------------+ | 1 | SIMPLE | t_lol | NULL | index | NULL | idx_title_name_price | 267 | NULL | 8 | 100.00 | Backward index scan; Using index | +----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec)
示例4:
僅使用了ORDER BY price,聯(lián)合索引左側(cè)兩列未使用,違反了最左原則,無法通過索引進行檢索,但由于查詢的各列構(gòu)成覆蓋索引,所以不用回表,可以直接拿索引文件中的數(shù)據(jù)進行二次重排序 → Using index; Using filesort
mysql> -- 違反了最左原則,直接ORDER BY col3; mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol ORDER BY price; +----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | t_lol | NULL | index | NULL | idx_title_name_price | 267 | NULL | 8 | 100.00 | Using index; Using filesort | +----+-------------+-------+------------+-------+---------------+----------------------+---------+------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
示例5:
多查了一列sex
,由于sex
字段是不包含在idx_title_name_price
索引中所以無法使用該索引,當然,如果是select * 就更容易出現(xiàn)該情況。因此會走全表掃描+臨時表排序(Using filesort),即Extra: Using filesort
。
這里我們很容易誤解。因為我也感覺如果僅通過索引排序,即使select cols中使用到索引以外的列,僅用索引來排序再回表查也當是沒問題才對,但使用時發(fā)現(xiàn)并不行。當舔狗的機會都沒有?
但!需要注意的是,如果where中有hero_title條件,便可以使用到索引了!
那么說來,如果場景允許的話,我們是否可以構(gòu)造一個如hero_title is not null的條件或force index強制使用索引等方式,來讓我們的SQL硬用
到索引的排序功能呢?emmm,好一個硬用
方式。`
mysql> -- 未用到索引;因為多查了一列`sex`,當然,如果是select * 就更不用說了,無法構(gòu)成覆蓋索引,因此回表進行全表掃描+臨 時表排序(Using filesort),最慢 mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price`,`sex` from t_lol ORDER BY hero_title; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | t_lol | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec)
3-3、包含where條件的
示例6:
當出現(xiàn)where和order by的條件為聯(lián)合索引(a,b,c)中的(a,c);
根據(jù)最左原則,只使用到了聯(lián)合索引的hero_title列索引,后面兩列被中斷了,ORDER BY price無法使用到索引,故后面的排序只能通過后建臨時表的方式來排序,即Extra:Using index; Using filesort
mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol where `hero_title` = '女槍' ORDER BY price; +----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------------+ | 1 | SIMPLE | t_lol | NULL | ref | idx_title_name_price | idx_title_name_price | 131 | const | 1 | 100.00 | Using index; Using filesort | +----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
示例7:
當出現(xiàn)where和order by的條件為聯(lián)合索引(a,b,c)中的(a,b);能否使用索引?
可以,實現(xiàn)了Using index覆蓋索引,這里是觸發(fā)了5.6推出的索引下推的特性,又根據(jù)最左原則使用到了聯(lián)合索引(hero_title,hero_name)。
mysql> -- Using index覆蓋索引,這里是觸發(fā)了索引下推的特性 mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol where `hero_title` = '女槍' ORDER BY `hero_name`; +----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | t_lol | NULL | ref | idx_title_name_price | idx_title_name_price | 131 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
示例8:
當出現(xiàn)where和order by的條件為聯(lián)合索引(a,b,c)中的(a,b);但where條件a查詢使用了范圍查詢,b能否使用索引?
我們根據(jù)最左原則知道,如果查詢條件出現(xiàn)范圍查詢(如between、<、>等),索引使用即中斷,后續(xù)條件無法再使用索引。這里同樣,ORDER BY hero_name
由于被中斷無法使用索引,索引下推也無法使用。因此需要 Using filesort自行排序。
mysql> -- 未構(gòu)成覆蓋索引,這里無法觸發(fā)索引下推特性,因為' > '將索引使用截斷了。因此需要 Using filesort自行排序 mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price` from t_lol where `hero_title` > '女槍' ORDER BY `hero_name`; +----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------------+ | 1 | SIMPLE | t_lol | NULL | range | idx_title_name_price | idx_title_name_price | 131 | NULL | 4 | 100.00 | Using where; Using index; Using filesort | +----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
特性9:
當select [cols…]查詢了聯(lián)合索引(a,b,c)外的列(常見的select *)會如何?
如下,用上了索引idx_title_name_price,但由于多了sex
字段,在索引查詢后需要再回表查詢。
mysql> -- 用上了索引,由于多了`sex`字段,在索引查詢后需要再回表查詢。 mysql> EXPLAIN SELECT `hero_title`, `hero_name`, `price`,`sex` from t_lol where `hero_title` = '女槍' ORDER BY `hero_name`; +----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_lol | NULL | ref | idx_title_name_price | idx_title_name_price | 131 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
小結(jié)
假設(shè)聯(lián)合索引index(a,b,c)
,總結(jié)一些條件命中索引的情況;
1、僅有 order by 條件,使用索引,基于最左前綴原則
order by a; order by a,b; order by a,b,c; order by a asc,b asc,c asc; order by a desc,b desc,c desc;
2、條件包含where和order by,使用索引
where a= 'chenhh' order by b,c; where a= 'chenhh' and b= 'chenhh' order by c; where a= 'chenhh' and b> 'chenhh' order by b,c;
3、order by無法通過索引進行排序的情況
order by a asc,b desc, c desc; where g=const order by b,c; where a=const order by c; where a=const order by a,d; -- d不是索引一部分 where a in (....) order by b,c; -- 對于排序來說,多個相等條件也是范圍查詢
到此這篇關(guān)于MySQL的order by該如何避免“未命中索引“的文章就介紹到這了,更多相關(guān)mysql order by未命中索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql read_buffer_size 設(shè)置多少合適
很多朋友都會問mysql read_buffer_size 設(shè)置多少合適,其實這個都是根據(jù)自己的內(nèi)存大小等來設(shè)置的2016-05-05MySQL中create table as 與like的區(qū)別分析
這篇文章主要介紹了MySQL中create table as 與like的區(qū)別,結(jié)合實例分析了二者在使用中的具體區(qū)別與主要用途,需要的朋友可以參考下2016-01-01MySQL通過binlog實現(xiàn)恢復數(shù)據(jù)
在MySQL中,如果不小心刪除了數(shù)據(jù),可以利用二進制日志(binlog)來恢復數(shù)據(jù),本文將通過幾個示例為大家介紹一下具體實現(xiàn)方法,希望對大家有所幫助2025-01-01關(guān)于MySQL中savepoint語句使用時所出現(xiàn)的錯誤
這篇文章主要介紹了關(guān)于MySQL中savepoint語句使用時所出現(xiàn)的錯誤,字符串出現(xiàn)e時所產(chǎn)生的問題也被作為MySQL的bug進行過提交,需要的朋友可以參考下2015-05-05