MySQL為什么有時(shí)候會(huì)選錯(cuò)索引-創(chuàng)新互聯(lián)

本篇內(nèi)容介紹了“MySQL為什么有時(shí)候會(huì)選錯(cuò)索引”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

為山陽等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計(jì)制作服務(wù)，及山陽網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為成都做網(wǎng)站、網(wǎng)站設(shè)計(jì)、山陽網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長期合作。這樣，我們也可以走得更遠(yuǎn)！

今天在生產(chǎn)環(huán)境中看到一個(gè)慢SQL，是個(gè)核心業(yè)務(wù)表，數(shù)據(jù)1300萬+

看一下表索引：

mysql>show index from `order`
+-----------------+----------------------+--------------------+------------------------+-----------------------+---------------------+-----------------------+--------------------+------------------+----------------+----------------------+-------------------+-------------------------+
| Table           | Non_unique           | Key_name           | Seq_in_index           | Column_name           | Collation           | Cardinality           | Sub_part           | Packed           | Null           | Index_type           | Comment           | Index_comment           |
+-----------------+----------------------+--------------------+------------------------+-----------------------+---------------------+-----------------------+--------------------+------------------+----------------+----------------------+-------------------+-------------------------+
| order           | 0                    | PRIMARY            | 1                      | id                    | A                   | 10493505              |                    |                  |                | BTREE                |                   |                         |
| order           | 0                    | uidx_order         | 1                      | order_seq             | A                   | 10512924              |                    |                  |                | BTREE                |                   |                         |
| order           | 1                    | idx_user           | 1                      | user_id               | A                   | 1995181               |                    |                  | YES            | BTREE                |                   |                         |
| order           | 1                    | idx_shop           | 1                      | shop_id               | A                   | 53933                 |                    |                  | YES            | BTREE                |                   |                         |
| order           | 1                    | idx_out_channel    | 1                      | out_channel           | A                   | 524                   |                    |                  | YES            | BTREE                |                   |                         |
| order           | 1                    | idx_out_channel    | 2                      | out_order_no          | A                   | 10512924              |                    |                  | YES            | BTREE                |                   |                         |
| order           | 1                    | idx_order_time     | 1                      | order_time            | A                   | 9867734               |                    |                  |                | BTREE                |                   |                         |
| order           | 1                    | idx_update_time    | 1                      | update_time           | A                   | 8305698               |                    |                  |                | BTREE                |                   |                         |
| order           | 1                    | idx_create_time    | 1                      | create_time           | A                   | 9951390               |                    |                  |                | BTREE                |                   |                         |
+-----------------+----------------------+--------------------+------------------------+-----------------------+---------------------+-----------------------+--------------------+------------------+----------------+----------------------+-------------------+-------------------------+
返回行數(shù)：[9]，耗時(shí)：4 ms.
mysql>SELECT id,order_seq,user_id 
FROM
	`ORDER`
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+---------------------+----------------------------------+
| id           | order_seq           | user_id                          |
+--------------+---------------------+----------------------------------+
| 6068129      | 20161128183300861   | d4b0c318b28a46968718dddbaf4775c0 |
| 6118611      | 20161206171509550   | d4b0c318b28a46968718dddbaf4775c0 |
| 6885081      | 20170427104933189   | d4b0c318b28a46968718dddbaf4775c0 |
| 7720299      | 2017101718252243    | d4b0c318b28a46968718dddbaf4775c0 |
| 10319613     | 201905281103186182  | d4b0c318b28a46968718dddbaf4775c0 |
| 505498       | 2019082116584284235 | d4b0c318b28a46968718dddbaf4775c0 |
| 10840144     | 1119082315041792571 | d4b0c318b28a46968718dddbaf4775c0 |
+--------------+---------------------+----------------------------------+
返回行數(shù)：[7]，耗時(shí)：18534 ms.

耗時(shí)18s，這個(gè)查詢速度肯定是不能接受的。

我們看一下執(zhí)行計(jì)劃：

mysql>EXPLAIN SELECT id,order_seq,user_id 
FROM
	`ORDER`
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+----------------+-------------------+---------------+----------------+--------------------+-----------------+
| id           | select_type           | table           | partitions           | type           | possible_keys           | key            | key_len           | ref           | rows           | filtered           | Extra           |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+----------------+-------------------+---------------+----------------+--------------------+-----------------+
| 1            | SIMPLE                | ORDER           |                      | index          | idx_user                | idx_order_time | 5                 |               | 2705           |               0.01 | Using where     |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+----------------+-------------------+---------------+----------------+--------------------+-----------------+

 執(zhí)行計(jì)劃中看到，這個(gè)SQL走索引idx_order_time，根據(jù)經(jīng)驗(yàn)判斷，此索引效率很差。而掃描行數(shù)為2705，慢日志顯示掃描行數(shù)為13,347,074，二者相差甚遠(yuǎn)，那么為什么會(huì)出現(xiàn)如此大的差異呢？

        選擇索引是優(yōu)化器的工作。而優(yōu)化器選擇索引的目的，是找一個(gè)最優(yōu)的執(zhí)行方案，并用最小的代價(jià)去執(zhí)行語句。在數(shù)據(jù)庫里面，掃描行數(shù)是影響執(zhí)行代價(jià)的因素之一。掃描的行數(shù)越少，意味著訪問磁盤數(shù)據(jù)的次數(shù)越少，消耗的CPU資源越少。

        當(dāng)然，掃描行數(shù)并不是唯一的判斷標(biāo)準(zhǔn)，優(yōu)化器還會(huì)結(jié)合是否使用臨時(shí)表，是否排序等因素進(jìn)行綜合判斷。掃描行數(shù)是怎么判斷的？

        MySQL在真正執(zhí)行SQL之前，并不能準(zhǔn)確的判斷滿足這個(gè)條件的數(shù)據(jù)有多少行，只能按統(tǒng)計(jì)信息來估算行數(shù)。

索引的統(tǒng)計(jì)信息就是索引的“區(qū)分度”，一個(gè)索引不同的值越多，這個(gè)索引的區(qū)分度就越好，而一個(gè)索引上不同的值的個(gè)數(shù)，我們稱之為“基數(shù)”，基數(shù)越大，索引的區(qū)分度越好。

若強(qiáng)制使用idx_user索引，看下執(zhí)行情況：

mysql>SELECT id,order_seq,user_id 
FROM
	`ORDER` force index(idx_user)
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+---------------------+----------------------------------+
| id           | order_seq           | user_id                          |
+--------------+---------------------+----------------------------------+
| 6068129      | 20161128183300861   | d4b0c318b28a46968718dddbaf4775c0 |
| 6118611      | 20161206171509550   | d4b0c318b28a46968718dddbaf4775c0 |
| 6885081      | 20170427104933189   | d4b0c318b28a46968718dddbaf4775c0 |
| 7720299      | 2017101718252243    | d4b0c318b28a46968718dddbaf4775c0 |
| 10319613     | 201905281103186182  | d4b0c318b28a46968718dddbaf4775c0 |
| 505498       | 2019082116584284235 | d4b0c318b28a46968718dddbaf4775c0 |
| 10840144     | 1119082315041792571 | d4b0c318b28a46968718dddbaf4775c0 |
+--------------+---------------------+----------------------------------+

查詢速度還是很快的，看一下執(zhí)行計(jì)劃：

mysql>explain SELECT id,order_seq,user_id 
FROM
	`ORDER` force index(idx_user)
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
| id           | select_type           | table           | partitions           | type           | possible_keys           | key           | key_len           | ref           | rows           | filtered           | Extra                                              |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
| 1            | SIMPLE                | ORDER           |                      | ref            | idx_user                | idx_user      | 163               | const         | 77706          |                  1 | Using index condition; Using where; Using filesort |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
返回行數(shù)：[1]，耗時(shí)：4 ms.

如果換成數(shù)據(jù)行數(shù)少一些的user_id

mysql>EXPLAIN SELECT id,order_seq,user_id 
FROM
	`ORDER` 
WHERE
	delete_flag = 0 
	AND user_id = '1e41c833fc6f4f57b490a4627a4170dc' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
| id           | select_type           | table           | partitions           | type           | possible_keys           | key           | key_len           | ref           | rows           | filtered           | Extra                                              |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
| 1            | SIMPLE                | ORDER           |                      | ref            | idx_user                | idx_user      | 163               | const         | 13             |                  1 | Using index condition; Using where; Using filesort |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
返回行數(shù)：[1]，耗時(shí)：4 ms.
mysql>SELECT id,order_seq,user_id 
FROM
	`ORDER` 
WHERE
	delete_flag = 0 
	AND user_id = '1e41c833fc6f4f57b490a4627a4170dc' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+---------------------+----------------------------------+
| id           | order_seq           | user_id                          |
+--------------+---------------------+----------------------------------+
| 10397123     | 2019092523044218361 | 1e41c833fc6f4f57b490a4627a4170dc |
+--------------+---------------------+----------------------------------+
返回行數(shù)：[1]，耗時(shí)：4 ms.

對(duì)比一下兩個(gè)user_id對(duì)應(yīng)的數(shù)據(jù)量：

返回行數(shù)：[1]，耗時(shí)：4 ms.
mysql>select count(*) from order01 where user_id='1e41c833fc6f4f57b490a4627a4170dc'
+--------------------+
| count(*)           |
+--------------------+
| 15                 |
+--------------------+
返回行數(shù)：[1]，耗時(shí)：4 ms.
mysql>select count(*) from order01 where user_id='d4b0c318b28a46968718dddbaf4775c0'
+--------------------+
| count(*)           |
+--------------------+
| 38611              |
+--------------------+
返回行數(shù)：[1]，耗時(shí)：14 ms.

總結(jié)：在此業(yè)務(wù)場景中，MySQL優(yōu)化器認(rèn)為檢索38000行數(shù)據(jù)然后進(jìn)行排序要比檢索15行數(shù)據(jù)排序代價(jià)大得多，所以選擇了有序的索引idx_order_time，但未必是最快的執(zhí)行計(jì)劃。

但是，此處還有一個(gè)疑問，如果對(duì)于user_id：d4b0c318b28a46968718dddbaf4775c0，不使用limit分頁，執(zhí)行計(jì)劃是什么樣呢？

mysql>SELECT * 
FROM
	ORDER01 
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0'
	AND display_status = 2 
ORDER BY
	order_time desc
+--------------+---------------------+----------------------+----------------------+----------------------------------+-----------------------+---------------------+----------------------------------+---------------------+----------------------+---------------------+-------------------------------+-------------------------+---------------------------+-----------------------------------+-----------------------+-------------------------+-------------------+-----------------------+------------------------+--------------------------+----------------------+---------------------+--------------------------+------------------------+------------------------+---------------------+------------------+-----------------------+-----------------------+-----------------------+
| id           | order_seq           | order_type           | order_flag           | user_id                          | user_mobile           | user_nick           | shop_id                          | shop_name           | pay_status           | pay_time            | receiver_address_id           | receiver_name           | receiver_mobile           | receiver_address                  | cancel_time           | cancel_reason           | channel           | out_channel           | out_order_no           | out_store_name           | order_time           | over_time           | display_status           | order_status           | sale_channel           | sale_mode           | remark           | delete_flag           | create_time           | update_time           |
+--------------+---------------------+----------------------+----------------------+----------------------------------+-----------------------+---------------------+----------------------------------+---------------------+----------------------+---------------------+-------------------------------+-------------------------+---------------------------+-----------------------------------+-----------------------+-------------------------+-------------------+-----------------------+------------------------+--------------------------+----------------------+---------------------+--------------------------+------------------------+------------------------+---------------------+------------------+-----------------------+-----------------------+-----------------------+
| 11153421     | 201911091339555506  |                    1 |                    1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545           | 總部-客服-補(bǔ)單      | 29e541d6da9b4aae8957409ca03c6670 | 清悠                |                    1 | 2019-11-09 13:40:10 | 2666265                       | 總部-客服-補(bǔ)單          | 13718903545               | 東城區(qū) 王府井 王府井 總部補(bǔ)單     |                       |                       0 |                   |                     0 | 201911091339555506     |                          | 2019-11-09 13:39:55  |                     |                        2 |                    200 |                      1 |                   1 |                  |                     0 | 2019-11-09 13:39:55   | 2019-11-09 13:40:10   |
| 7720299      | 2017101718252243    |                    1 |                    1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545           | 總部-客服-補(bǔ)單      | ad41dba7bf5c4b69b03e0222878cb2b0 | 蝶舞                |                    1 | 2017-10-17 18:25:26 | 2282099                       | 總部-客服-補(bǔ)單          | 13718903545               | 2號(hào)線; 地鐵7號(hào)線 華強(qiáng)北 總部補(bǔ)單  |                       |                       0 |                   |                     0 | 2017101718252243       |                          | 2017-10-17 18:25:22  |                     |                        2 |                    200 |                      1 |                   1 |                  |                     0 | 2017-10-17 18:25:22   | 2017-10-17 18:25:22   |
| 6885081      | 20170427104933189   |                    1 |                    1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545           | 總部-客服-補(bǔ)單      | c6092260f92643098f7f56e68560d8c0 | 木蘭花              |                    1 | 2017-04-27 10:49:39 | 2264946                       | 總部-客服-補(bǔ)單          | 13718903545               | 天河北商圈                        |                       |                       0 |                   |                     0 | 20170427104933189      |                          | 2017-04-27 10:49:33  |                     |                        2 |                    200 |                      1 |                   1 |                  |                     0 | 2017-04-27 10:49:33   | 2017-04-27 10:49:33   |
| 6118611      | 20161206171509550   |                    1 |                    1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545           | 總部-客服-補(bǔ)單      | 7a0cd4d60f52423fb757b0be1ab55be6 | 娟子                |                    1 | 2016-12-06 17:15:12 | 1904075                       | 總部-客服-補(bǔ)單          | 13718903545               | 廣東省深圳市南山區(qū)深南大道 科技園 |                       |                       0 | helijia           |                     0 | 20161206171509550      |                          | 2016-12-06 17:15:09  |                     |                        2 |                    200 |                      1 |                   1 |                  |                     0 | 2016-12-06 17:15:09   | 2016-12-06 17:15:09   |
| 6068129      | 20161128183300861   |                    1 |                    1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545           | 總部-客服-補(bǔ)單      | f6f4612493654695ac4c6bac6df67672 | 美天                |                    1 | 2016-11-28 18:33:03 | 1544109                       | 總部-客服-補(bǔ)單          | 13718903545               | 青羊區(qū)金河路口寬窄巷子 寬窄巷子   |                       |                       0 | helijia           |                     0 | 20161128183300861      |                          | 2016-11-28 18:33:00  |                     |                        2 |                    200 |                      1 |                   1 |                  |                     0 | 2016-11-28 18:33:00   | 2016-11-28 18:33:00   |
+--------------+---------------------+----------------------+----------------------+----------------------------------+-----------------------+---------------------+----------------------------------+---------------------+----------------------+---------------------+-------------------------------+-------------------------+---------------------------+-----------------------------------+-----------------------+-------------------------+-------------------+-----------------------+------------------------+--------------------------+----------------------+---------------------+--------------------------+------------------------+------------------------+---------------------+------------------+-----------------------+-----------------------+-----------------------+
返回行數(shù)：[5]，耗時(shí)：152 ms.
mysql>explain SELECT * 
FROM
	ORDER01 
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0'
	AND display_status = 2 
ORDER BY
	order_time desc
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
| id           | select_type           | table           | partitions           | type           | possible_keys           | key           | key_len           | ref           | rows           | filtered           | Extra                                              |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
| 1            | SIMPLE                | ORDER01         |                      | ref            | idx_user                | idx_user      | 163               | const         | 75800          |                  1 | Using index condition; Using where; Using filesort |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
返回行數(shù)：[1]，耗時(shí)：4 ms.

查詢速度很快，執(zhí)行計(jì)劃走了user_id字段的索引。為什么會(huì)出現(xiàn)這樣的情況呢？

查閱了相關(guān)資料，對(duì)于order by limit這樣的排序，當(dāng)檢索到的數(shù)據(jù)較多的時(shí)候，排序消耗是很大的，這個(gè)時(shí)候由于優(yōu)化器選擇了有序的idx_order_time而導(dǎo)致執(zhí)行索引選擇錯(cuò)誤。

優(yōu)化辦法：

1、強(qiáng)制使用索引idx_user；

2、創(chuàng)建組合索引idx_uid_ordertime(user_id,order_time)

mysql>alter table `ORDER` add index idx_uid_ordertime(user_id,order_time)
執(zhí)行成功，耗時(shí)：60334 ms.
mysql>SELECT id,order_seq,user_id 
FROM
	`ORDER` 
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+---------------------+----------------------------------+
| id           | order_seq           | user_id                          |
+--------------+---------------------+----------------------------------+
| 6068129      | 20161128183300861   | d4b0c318b28a46968718dddbaf4775c0 |
| 6118611      | 20161206171509550   | d4b0c318b28a46968718dddbaf4775c0 |
| 6885081      | 20170427104933189   | d4b0c318b28a46968718dddbaf4775c0 |
| 7720299      | 2017101718252243    | d4b0c318b28a46968718dddbaf4775c0 |
| 10319613     | 201905281103186182  | d4b0c318b28a46968718dddbaf4775c0 |
| 505498       | 2019082116584284235 | d4b0c318b28a46968718dddbaf4775c0 |
| 10840144     | 1119082315041792571 | d4b0c318b28a46968718dddbaf4775c0 |
+--------------+---------------------+----------------------------------+
返回行數(shù)：[7]，耗時(shí)：86 ms.
mysql>explain SELECT id,order_seq,user_id 
FROM
	`ORDER` 
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+-----------------------+-----------------+----------------------+----------------+----------------------------+-------------------+-------------------+---------------+----------------+--------------------+------------------------------------+
| id           | select_type           | table           | partitions           | type           | possible_keys              | key               | key_len           | ref           | rows           | filtered           | Extra                              |
+--------------+-----------------------+-----------------+----------------------+----------------+----------------------------+-------------------+-------------------+---------------+----------------+--------------------+------------------------------------+
| 1            | SIMPLE                | ORDER           |                      | ref            | idx_user,idx_uid_ordertime | idx_uid_ordertime | 163               | const         | 72772          |                  1 | Using index condition; Using where |
+--------------+-----------------------+-----------------+----------------------+----------------+----------------------------+-------------------+-------------------+---------------+----------------+--------------------+------------------------------------+
返回行數(shù)：[1]，耗時(shí)：4 ms.

“MySQL為什么有時(shí)候會(huì)選錯(cuò)索引”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)-成都網(wǎng)站建設(shè)公司網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！

名稱欄目：MySQL為什么有時(shí)候會(huì)選錯(cuò)索引-創(chuàng)新互聯(lián)
轉(zhuǎn)載源于：http://muchs.cn/article44/pgjee.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供標(biāo)簽優(yōu)化、手機(jī)網(wǎng)站建設(shè)、網(wǎng)站營銷、全網(wǎng)營銷推廣、域名注冊(cè)、響應(yīng)式網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

成都做網(wǎng)站