亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

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

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

  不少同學私信我說,用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è)置多少合適

    很多朋友都會問mysql read_buffer_size 設(shè)置多少合適,其實這個都是根據(jù)自己的內(nèi)存大小等來設(shè)置的
    2016-05-05
  • mysql中in條件使用字符串方式

    mysql中in條件使用字符串方式

    這篇文章主要介紹了mysql中in條件使用字符串方式,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教
    2022-08-08
  • MySQL中create table as 與like的區(qū)別分析

    MySQL中create table as 與like的區(qū)別分析

    這篇文章主要介紹了MySQL中create table as 與like的區(qū)別,結(jié)合實例分析了二者在使用中的具體區(qū)別與主要用途,需要的朋友可以參考下
    2016-01-01
  • MySQL全面瓦解之查詢的過濾條件詳解

    MySQL全面瓦解之查詢的過濾條件詳解

    這篇文章主要給打大家介紹了關(guān)于MySQL全面瓦解之查詢的過濾條件的相關(guān)資料,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
    2020-11-11
  • mysql如何將查詢結(jié)果插入到另一張表中

    mysql如何將查詢結(jié)果插入到另一張表中

    這篇文章主要介紹了mysql如何將查詢結(jié)果插入到另一張表中問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
    2023-10-10
  • mysql如何才能保證數(shù)據(jù)的一致性

    mysql如何才能保證數(shù)據(jù)的一致性

    這篇文章主要介紹了mysql如何才能保證數(shù)據(jù)的一致性問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教<BR>
    2024-03-03
  • 如何開啟mysql中的嚴格模式

    如何開啟mysql中的嚴格模式

    這篇文章介紹了如何開啟mysql中的嚴格模式,有需要的朋友可以參考一下
    2013-09-09
  • MyCAT上新增一個庫及MyCAT報錯1184的問題及解決

    MyCAT上新增一個庫及MyCAT報錯1184的問題及解決

    這篇文章主要介紹了MyCAT上新增一個庫及MyCAT報錯1184的問題及解決方案,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
    2023-11-11
  • MySQL通過binlog實現(xiàn)恢復數(shù)據(jù)

    MySQL通過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)的錯誤

    這篇文章主要介紹了關(guān)于MySQL中savepoint語句使用時所出現(xiàn)的錯誤,字符串出現(xiàn)e時所產(chǎn)生的問題也被作為MySQL的bug進行過提交,需要的朋友可以參考下
    2015-05-05

最新評論