MySQL索引失效之隱式轉(zhuǎn)換的問題
更新時間：2022年01月09日 09:18:45 作者：GeaoZhang
本文主要介紹了MySQL索引失效之隱式轉(zhuǎn)換的問題，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下
常見索引失效：
1. 條件索引字段"不干凈"：函數(shù)操作、運算操作
2. 隱式類型轉(zhuǎn)換：字符串轉(zhuǎn)數(shù)值；其他類型轉(zhuǎn)換
3. 隱式字符編碼轉(zhuǎn)換：按字符編碼數(shù)據(jù)長度大的方向轉(zhuǎn)換，避免數(shù)據(jù)截取
一、常見索引失效場景
root@test 10:50 > show create table t_num\G *************************** 1. row *************************** ? ? ? ?Table: t_num Create Table: CREATE TABLE `t_num` ( ? `id` int(11) NOT NULL AUTO_INCREMENT, ? `c1` int(11) NOT NULL, ? `c2` varchar(11) NOT NULL, ? PRIMARY KEY (`id`), ? KEY `ix_c1` (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 ? root@test 10:51 > select * from t_num; +----+----+----+ | id | c1 | c2 | +----+----+----+ | ?1 | -2 | -2 | | ?2 | -1 | -1 | | ?3 | ?0 | ?0 | | ?4 | ?1 | ?1 | | ?5 | ?2 | ?2 | +----+----+----+ ? # 在c1字段上加上索引 root@test 10:52 > alter table t_num add index ix_c1(c1); ? # 標準使用情況下，索引有效 root@test 10:55 > explain select * from t_num where c1 = -1; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key ? | key_len | ref ? | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | ?1 | SIMPLE ? ? ?| t_num | NULL ? ? ? | ref ?| ix_c1 ? ? ? ? | ix_c1 | 4 ? ? ? | const | ? ?1 | ? 100.00 | NULL ?| +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1、條件字段函數(shù)操作
# 在where中c1上加上abs()絕對值函數(shù)，可以看到type=ALL，全表掃描，在Server層進行絕對值處理后進行比較 root@test 10:58 > explain select * from t_num where abs(c1) = 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key ?| key_len | ref ?| rows | filtered | Extra ? ? ? | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | ?1 | SIMPLE ? ? ?| t_num | NULL ? ? ? | ALL ?| NULL ? ? ? ? ?| NULL | NULL ? ?| NULL | ? ?5 | ? 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
如上，對索引字段做函數(shù)操作，即where條件列上不干凈時，可能會破壞索引值的有序性（按照c1的值有序組織索引樹），因此優(yōu)化器就決定放棄走索引樹搜索功能。
但是，條件字段函數(shù)操作下，也并非完全的走全表掃描，優(yōu)化器并非完全的放棄該字段索引。
# 選擇查詢的數(shù)據(jù)，只有id和c1字段，可以看到type=index，使用到了ix_c1索引 root@test 10:59 > explain select id,c1 from t_num where abs(c1) = 1; +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t_num | NULL | index | NULL | ix_c1 | 4 | NULL | 5 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
如上，由于ix_c1索引樹是根節(jié)點c1和葉子節(jié)點id構(gòu)造的，雖然因為c1上的函數(shù)操作導(dǎo)致放棄索引定位，但優(yōu)化器可以選擇遍歷該索引樹，使用覆蓋索引（Using index），無需回表，將所需的id和c1數(shù)據(jù)返回Server層后進行后續(xù)的abs()和where過濾。
2、條件字段運算操作
# where條件里，對c1進行運算操作 root@test 11:03 > explain select * from t_num where c1 + 1 = 2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_num | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
如上，雖然“+1”的操作并沒有破壞c1索引的有序性，但優(yōu)化器仍然沒有使用該索引快速定位。因此，等號左邊，注意優(yōu)化掉索引字段上的運算操作。
3、隱式類型轉(zhuǎn)換
# 在c2字段上加上索引 root@test 12:30 > alter table t_num add index ix_c2(c2); ? # 標準使用情況下（注：c2是varchar類型的），索引有效 root@test 12:30 > explain select * from t_num where c2 = "2"; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_num | NULL | ref | ix_c2 | ix_c2 | 42 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+ ? # 去掉等號右邊值的引號，即字符串和數(shù)值進行比較，索引失效 root@test 12:30 > explain select * from t_num where c2 = 2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_num | NULL | ALL | ix_c2 | NULL | NULL | NULL | 5 | 20.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
如上，c2字段是varchar類型，是字符串和數(shù)值的比較，此時，MySQL是將字符串轉(zhuǎn)換成數(shù)字，即此處的c2被CAST(c2 AS signed int)，這就相當于對條件字段做了函數(shù)操作，優(yōu)化器放棄走樹索引定位。
4、隱式字符編碼轉(zhuǎn)換
# 創(chuàng)建一個t_cou表，表結(jié)構(gòu)基本和前面的t_num相同，唯一不同的設(shè)置是表字符集CHARSET=utf8 root@test 14:02 > show create table t_cou\G *************************** 1. row *************************** ? ? ? ?Table: t_cou Create Table: CREATE TABLE `t_cou` ( ? `id` int(11) NOT NULL AUTO_INCREMENT, ? `c1` int(11) NOT NULL, ? `c2` varchar(10) NOT NULL, ? PRIMARY KEY (`id`), ? KEY `ix_c1` (`c1`), ? KEY `ix_c2` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ? root@test 14:02 > insert into t_cou select * from t_num; ? # join表，t_num和t_cou通過c2字段進行關(guān)聯(lián)查詢 root@test 14:03 > select n.* from t_num n ? ? -> join t_cou c ? ? -> on n.c2 = c.c2 ? ? -> where n.c1 = 1; +----+----+----+ | id | c1 | c2 | +----+----+----+ | ?4 | ?1 | 1 ?| +----+----+----+ ? root@test 14:23 > explain select n.* from t_num n join t_cou c ?on n.c2 = c.c2 where c.c1 = 1; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key ? | key_len | ref ? | rows | filtered | Extra ? ? ? ? ? ? ? ? | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+ | ?1 | SIMPLE ? ? ?| c ? ? | NULL ? ? ? | ref ?| ix_c1 ? ? ? ? | ix_c1 | 4 ? ? ? | const | ? ?1 | ? 100.00 | NULL ? ? ? ? ? ? ? ? ?| | ?1 | SIMPLE ? ? ?| n ? ? | NULL ? ? ? | ref ?| ix_c2 ? ? ? ? | ix_c2 | 42 ? ? ?| func ?| ? ?1 | ? 100.00 | Using index condition | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+ # 執(zhí)行計劃分析: # 1.操作的c表，使用了ix_c1定位到一行數(shù)據(jù) # 2.從c表定位到的行數(shù)據(jù)，拿到c2字段去操作n表，t_cou稱為驅(qū)動表，t_num稱為被驅(qū)動表 # 3.ref=func說明使用了函數(shù)操作，指的是n.c2=CONVERT(c.c2 USING utf8mb4) # 4.同時Using index condition，ix_c2讀取查詢時，使用被下推的條件過濾，滿足條件的才回表 ? root@test 14:23 > explain select n.* from t_num n join t_cou c ?on n.c2 = c.c2 where n.c1 = 1; +----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-----------------------------------------------------------------+ | id | select_type | table | partitions | type ?| possible_keys | key ? | key_len | ref ? | rows | filtered | Extra ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | +----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-----------------------------------------------------------------+ | ?1 | SIMPLE ? ? ?| n ? ? | NULL ? ? ? | ref ? | ix_c1,ix_c2 ? | ix_c1 | 4 ? ? ? | const | ? ?1 | ? 100.00 | NULL ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| | ?1 | SIMPLE ? ? ?| c ? ? | NULL ? ? ? | index | NULL ? ? ? ? ?| ix_c2 | 32 ? ? ?| NULL ?| ? ?5 | ? 100.00 | Using where; Using index; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-----------------------------------------------------------------+ # 執(zhí)行計劃分析: # 1.操作的n表，使用了ix_c1定位到一行數(shù)據(jù) # 2.從n表定位到的行數(shù)據(jù)，拿到c2字段去操作c表，t_num稱為驅(qū)動表，t_cou稱為被驅(qū)動表 # 3.同樣的n.c2=c.c2，會將c.c2的字符集進行轉(zhuǎn)換，即被驅(qū)動表的索引字段上加函數(shù)操作，索引失效 # 4.BNL，表join時，驅(qū)動表數(shù)據(jù)讀入join buffer，被驅(qū)動表連接字段無索引則全表掃，每取一行和join buffer數(shù)據(jù)對比判斷，作為結(jié)果集返回
如上，分別對t_num、 t_cou作為驅(qū)動表和被驅(qū)動表的執(zhí)行計劃分析，總結(jié)：
utf8mb4和utf8兩種不同字符集（編碼）類型的字符串在做比較時，MySQL會先把 utf8 字符串轉(zhuǎn)成 utf8mb4 字符集，再做比較。為什么？字符集 utf8mb4 是 utf8 的超集，再做隱式自動類型轉(zhuǎn)換時，為了避免數(shù)據(jù)在轉(zhuǎn)換過程中由于截斷導(dǎo)致數(shù)據(jù)錯誤，會“按數(shù)據(jù)長度增加的方向”進行轉(zhuǎn)換。
表連接過程中，被驅(qū)動表的索引字段上加函數(shù)操作，會導(dǎo)致對被驅(qū)動表做全表掃描。
優(yōu)化手法：
修改統(tǒng)一join字段的字符集
對驅(qū)動表下手，將連接字段的字符集轉(zhuǎn)換成被驅(qū)動表連接字段的字符集
root@test 18:09 > explain select n.* from t_num n join t_cou c on convert(n.c2 using utf8) = c.c2 where n.c1 = 1; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | n | NULL | ref | ix_c1 | ix_c1 | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | c | NULL | ref | ix_c2 | ix_c2 | 32 | func | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
二、類型轉(zhuǎn)換
1、字符串轉(zhuǎn)整型
# 字符開頭的一律為0
root@test 18:44 > select convert("abc", unsigned integer);
+----------------------------------+
| convert("abc", unsigned integer) |
+----------------------------------+
| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0 |
+----------------------------------+
# 'abc' = 0是成立的，因此查詢時等號右邊使用對應(yīng)的類型很重要，0匹配出字段字符開頭數(shù)據(jù)，'0'只匹配0
root@test 18:44 > select 'abc' = 0;
+-----------+
| 'abc' = 0 |
+-----------+
| ? ? ? ? 1 |
+-----------+
?
# 數(shù)字開頭的，直接截取到第一個不是字符的位置
root@test 18:45 > select convert("123abc", unsigned integer);
+-------------------------------------+
| convert("123abc", unsigned integer) |
+-------------------------------------+
| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 123 |
+-------------------------------------+2、時間類型轉(zhuǎn)換
root@test 19:11 > show create table time_demo\G
*************************** 1. row ***************************
? ? ? ?Table: time_demo
Create Table: CREATE TABLE `time_demo` (
? `id` int(11) NOT NULL AUTO_INCREMENT,
? `c1` datetime DEFAULT NULL,
? `c2` date DEFAULT NULL,
? PRIMARY KEY (`id`),
? KEY `ix_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
?
root@test 19:15 > select count(*) from time_demo;
+----------+
| count(*) |
+----------+
| ? ? ? 11 |
+----------+
?
root@test 19:16 > select * from time_demo limit 4;
+----+---------------------+------------+
| id | c1 ? ? ? ? ? ? ? ? ?| c2 ? ? ? ? |
+----+---------------------+------------+
| ?1 | 2022-01-08 00:01:01 | 2022-01-08 |
| ?2 | 2022-01-06 23:01:01 | 2022-01-06 |
| ?3 | 2022-01-06 00:00:00 | 2022-01-06 |
| ?4 | 2022-01-08 00:00:00 | 2022-01-08 |
+----+---------------------+------------+
?
# 1.date轉(zhuǎn)datetime：末尾追加 00:00:00
root@test 19:11 > select * from time_demo where c1 between "2022-01-06" and "2022-01-08";
+----+---------------------+------------+
| id | c1 ? ? ? ? ? ? ? ? ?| c2 ? ? ? ? |
+----+---------------------+------------+
| ?2 | 2022-01-06 23:01:01 | 2022-01-06 |
| ?3 | 2022-01-06 00:00:00 | 2022-01-06 |
| ?4 | 2022-01-08 00:00:00 | 2022-01-08 |
+----+---------------------+------------+
# 結(jié)果分析：c1是datetime類型，進行比較時，between and中的date類型會轉(zhuǎn)換成datetime
# 即 where c1 between "2022-01-06 00:00:00" and "2022-01-08 00:00:00";
# 同 where c1 >= "2022-01-06 00:00:00" and c1 <= "2022-01-08 00:00:00";
root@test 19:42 > explain select * from time_demo where c1 between "2022-01-06" and "2022-01-08";
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table ? ? | partitions | type ?| possible_keys | key ? | key_len | ref ?| rows | filtered | Extra ? ? ? ? ? ? ? ? |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| ?1 | SIMPLE ? ? ?| time_demo | NULL ? ? ? | range | ix_c1 ? ? ? ? | ix_c1 | 6 ? ? ? | NULL | ? ?3 | ? 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
# 格式化date轉(zhuǎn)datetime
root@test 19:23 > select date_format("2022-01-08","%Y-%m-%d %H:%i:%s");
+-----------------------------------------------+
| date_format("2022-01-08","%Y-%m-%d %H:%i:%s") |
+-----------------------------------------------+
| 2022-01-06 00:00:00 ? ? ? ? ? ? ? ? ? ? ? ? ? |
+-----------------------------------------------+
?
# 2.datetime轉(zhuǎn)date：直接截取date部分
root@test 19:47 > select date(c1) from time_demo limit 1;
+------------+
| date(c1) ? |
+------------+
| 2022-01-06 |
+------------+
?
# 3.date轉(zhuǎn)time，沒有意義，直接變成 00:00:00到此這篇關(guān)于MySQL索引失效之隱式轉(zhuǎn)換的問題的文章就介紹到這了,更多相關(guān)MySQL 隱式轉(zhuǎn)換內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
在IntelliJ IDEA中使用Java連接MySQL數(shù)據(jù)庫的方法詳解
這篇文章主要介紹了在IntelliJ IDEA中使用Java連接MySQL數(shù)據(jù)庫的方法詳解,本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學(xué)習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-10-10
MySQL8.0數(shù)據(jù)庫開窗函數(shù)圖文詳解
開窗函數(shù)為將要被操作的行的集合定義一個窗口,它對一組值進行操作,不需要使用GROUP BY子句對數(shù)據(jù)進行分組,能夠在同一行中同時返回基礎(chǔ)行的列和聚合列,這篇文章主要給大家介紹了關(guān)于MySQL8.0數(shù)據(jù)庫開窗函數(shù)的相關(guān)資料,需要的朋友可以參考下2023-06-06
mysql常用函數(shù)實例總結(jié)【聚集函數(shù)、字符串、數(shù)值、時間日期處理等】
這篇文章主要介紹了mysql常用函數(shù),結(jié)合實例形式總結(jié)分析了mysql聚集函數(shù)、字符串、數(shù)值、時間日期處理等常見函數(shù)與相關(guān)使用技巧,需要的朋友可以參考下2020-04-04
這篇文章主要介紹了銀河麒麟V10安裝MySQL5.7,本文給大家介紹的非常詳細，對大家的學(xué)習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-05-05
探討:sql插入空,默認1900-01-01 00:00:00.000的解決方法詳解
本篇文章是對sql插入空,默認1900-01-01 00:00:00.000的解決方法進行了詳細的分析介紹，需要的朋友參考下2013-06-06
Prometheus 監(jiān)控MySQL使用grafana展示
這篇文章主要介紹prometheus通過mysql exporter+node exporter監(jiān)控mysql，并使用grafana進行圖表展示的相關(guān)內(nèi)容，感興趣的效果版可以參考下文2021-08-08
這篇文章主要介紹了mysql int(3)與int(11)的區(qū)別詳解的相關(guān)資料,需要的朋友可以參考下2016-09-09
這篇文章主要介紹了MySQL 5.7 版本的安裝及簡單使用（圖文教程）的相關(guān)資料,這里對mysql 5.7的安裝及使用和注意事項，需要的朋友可以參考下2016-12-12
默認情況下，MySQL只允許本地登錄，如果要開啟遠程連接，則需要修改/etc/my.cnf文件2013-02-02
MySQL系列之開篇 MySQL關(guān)系型數(shù)據(jù)庫基礎(chǔ)概念
數(shù)據(jù)庫是指長期儲存在計算機中的有組織的、可共享的數(shù)據(jù)集合，數(shù)據(jù)具有三大基本特點，永久存儲，有組織，可共享，是數(shù)據(jù)庫系統(tǒng)的核心，本文給大家分享MySQL關(guān)系型數(shù)據(jù)庫基礎(chǔ)概念，需要的朋友參考下吧2021-07-07