欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL的order?by如何避免"未命中索引"(推薦)

 更新時(shí)間：2024年11月06日 10:42:15   作者：_陳哈哈  
本文詳細(xì)解析了在使用MySQL的OrderBy語(yǔ)句時(shí),如何通過(guò)Explain查看執(zhí)行計(jì)劃以及如何有效利用索引,介紹了常見(jiàn)的索引未命中情況,提供了多個(gè)示例來(lái)解釋如何根據(jù)索引的不同使用情況調(diào)整SQL語(yǔ)句,以確保最優(yōu)的查詢(xún)性能

  不少同學(xué)私信我說(shuō)，用Explain查看Order By語(yǔ)句執(zhí)行計(jì)劃時(shí)經(jīng)常發(fā)現(xiàn)用不上索引，難道花好多時(shí)間和資源創(chuàng)建的聯(lián)合索引都擺爛了？今天我把幾個(gè)同學(xué)遇到的情況整理出來(lái)，做一個(gè)Order By使用索引的坑點(diǎn)分享。希望對(duì)你有用。

  要學(xué)會(huì)如何使用，你先要搞清楚：1、怎么看SQL是否用上了索引；2、怎么寫(xiě)SQL能避開(kāi)出錯(cuò)點(diǎn)。

  對(duì)了，如果對(duì)Explain查看索引命中情況比較了解的同學(xué)可以直接跳轉(zhuǎn)第三部分。

一、測(cè)試數(shù)據(jù)導(dǎo)入

-- ----------------------------
-- 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, '無(wú)極劍圣', '易', 450, NULL);
INSERT INTO `t_lol` VALUES (7, '疾風(fēng)劍豪', '亞索', 6300, NULL);
INSERT INTO `t_lol` VALUES (8, '女槍', '好運(yùn)', 1350, NULL);

二、Explain查看索引使用情況

  查看Explain執(zhí)行計(jì)劃是我們開(kāi)發(fā)人員必須掌握的一個(gè)技能，下一篇我會(huì)整理Explain執(zhí)行計(jì)劃的詳細(xì)查看方法。

  本篇文章是查看索引使用情況，我們通過(guò)key列、Extra列判斷足矣。key列即展示使用到的索引，下面重點(diǎn)看一下當(dāng)使用到索引即key列有值時(shí)，Extra列展示的相關(guān)信息都代表啥。

2-1、Using index

  構(gòu)成了覆蓋索引，where篩選條件也符合索引的最左前綴原則。

2-2、Using where,Using index

  • 查詢(xún)的列被索引覆蓋，并且where篩選條件是索引列之一但是不是索引的前導(dǎo)列，無(wú)法直接通過(guò)索引查找來(lái)查詢(xún)到符合條件的數(shù)據(jù)。
  • 查詢(xún)的列被索引覆蓋，并且where篩選條件是索引列前導(dǎo)列的一個(gè)范圍，同樣意味著無(wú)法直接通過(guò)索引查找查詢(xún)到符合條件的數(shù)據(jù)。

2-3、NULL

  既沒(méi)有Using index，也沒(méi)有Using where,Using index，也沒(méi)有using where。

  查詢(xún)的列未被索引覆蓋，并且where篩選條件是索引的前導(dǎo)列。意味著可能用到了索引（我們可以根據(jù)key列判斷是否用上索引），但是部分字段未被索引覆蓋，必須通過(guò)回表來(lái)實(shí)現(xiàn)。

2-4、Using where

  • 查詢(xún)的列未被索引覆蓋，where篩選條件非索引的前導(dǎo)列；
  • 查詢(xún)的列未被索引覆蓋，where篩選條件非索引列；

  using where 意味著通過(guò)表掃描的方式進(jìn)行where條件的過(guò)濾，也就是沒(méi)找到可用的索引。

  當(dāng)然也有特例，如果優(yōu)化器判斷索引掃描+回表的代價(jià)相比全表掃描的代價(jià)更大，則主動(dòng)放棄索引的使用。

如果explain中type列值為all，說(shuō)明MySQL認(rèn)為全表掃描是一種比較低的代價(jià)。

2-5、Using index condition

  • 查詢(xún)的列不全在索引中，where條件中是一個(gè)前導(dǎo)列的范圍查詢(xún)；
  • 查詢(xún)列不完全被索引覆蓋，但查詢(xún)條件可以使用到索引；

三、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)

  該表中有一個(gè)主鍵索引PRIMARY和一個(gè)聯(lián)合索引idx_title_name_price(hero_title, hero_name, price)

3-2、不含where語(yǔ)句的示例

示例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完全相同，因?yàn)锽Tree索引有序，省去了自左向右各索引列的排序工作。

mysql> -- 同上，使用了覆蓋索引（由于B樹(shù)索引類(lèi)型有序，省去了排序）
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è)兩列未使用，違反了最左原則，無(wú)法通過(guò)索引進(jìn)行檢索，但由于查詢(xún)的各列構(gòu)成覆蓋索引，所以不用回表，可以直接拿索引文件中的數(shù)據(jù)進(jìn)行二次重排序 → 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索引中所以無(wú)法使用該索引，當(dāng)然，如果是select * 就更容易出現(xiàn)該情況。因此會(huì)走全表掃描+臨時(shí)表排序（Using filesort），即Extra: Using filesort。

  這里我們很容易誤解。因?yàn)槲乙哺杏X(jué)如果僅通過(guò)索引排序，即使select cols中使用到索引以外的列，僅用索引來(lái)排序再回表查也當(dāng)是沒(méi)問(wèn)題才對(duì)，但使用時(shí)發(fā)現(xiàn)并不行。當(dāng)舔狗的機(jī)會(huì)都沒(méi)有？

  但！需要注意的是，如果where中有hero_title條件，便可以使用到索引了！那么說(shuō)來(lái)，如果場(chǎng)景允許的話，我們是否可以構(gòu)造一個(gè)如hero_title is not null的條件或force index強(qiáng)制使用索引等方式，來(lái)讓我們的SQL硬用到索引的排序功能呢？emmm，好一個(gè)硬用方式。`

mysql> -- 未用到索引；因?yàn)槎嗖榱艘涣衊sex`，當(dāng)然，如果是select * 就更不用說(shuō)了，無(wú)法構(gòu)成覆蓋索引，因此回表進(jìn)行全表掃描+臨 時(shí)表排序（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：

  當(dāng)出現(xiàn)where和order by的條件為聯(lián)合索引(a,b,c)中的(a,c)；

  根據(jù)最左原則，只使用到了聯(lián)合索引的hero_title列索引，后面兩列被中斷了，ORDER BY price無(wú)法使用到索引，故后面的排序只能通過(guò)后建臨時(shí)表的方式來(lái)排序，即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：

  當(dāng)出現(xiàn)where和order by的條件為聯(lián)合索引(a,b,c)中的(a,b)；能否使用索引？

  可以，實(shí)現(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：

  當(dāng)出現(xiàn)where和order by的條件為聯(lián)合索引(a,b,c)中的(a,b)；但where條件a查詢(xún)使用了范圍查詢(xún)，b能否使用索引？

  我們根據(jù)最左原則知道，如果查詢(xún)條件出現(xiàn)范圍查詢(xún)（如between、<、>等），索引使用即中斷，后續(xù)條件無(wú)法再使用索引。這里同樣，ORDER BY hero_name由于被中斷無(wú)法使用索引，索引下推也無(wú)法使用。因此需要 Using filesort自行排序。

mysql> -- 未構(gòu)成覆蓋索引，這里無(wú)法觸發(fā)索引下推特性，因?yàn)? > '將索引使用截?cái)嗔?。因此需?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：

  當(dāng)select [cols…]查詢(xún)了聯(lián)合索引(a,b,c)外的列（常見(jiàn)的select *）會(huì)如何？

  如下，用上了索引idx_title_name_price，但由于多了sex字段，在索引查詢(xún)后需要再回表查詢(xún)。

mysql> -- 用上了索引，由于多了`sex`字段，在索引查詢(xún)后需要再回表查詢(xún)。
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無(wú)法通過(guò)索引進(jìn)行排序的情況

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; -- 對(duì)于排序來(lái)說(shuō)，多個(gè)相等條件也是范圍查詢(xún)

到此這篇關(guān)于MySQL的order by該如何避免“未命中索引“的文章就介紹到這了,更多相關(guān)mysql order by未命中索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

相關(guān)文章

最新評(píng)論