淺談MySql整型索引和字符串索引失效或隱式轉(zhuǎn)換問(wèn)題

更新時(shí)間：2021年11月18日 11:41:53 作者：試著奔跑的菜鳥(niǎo)

本文主要介紹了MySql整型索引和字符串索引失效或隱式轉(zhuǎn)換問(wèn)題，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

問(wèn)題概述

今天在上班時(shí)，DBA突然找出來(lái)一段sql，表示該sql存在隱式轉(zhuǎn)換，不走索引。經(jīng)過(guò)我們的查看后，發(fā)現(xiàn)是類型varchar的字段，我們使用條件傳入了數(shù)值型的值，由于擔(dān)心違反保密協(xié)議，在此就不貼圖了，由我重現(xiàn)一下類似情況給大家看一下。

問(wèn)題重現(xiàn)

首先我們先創(chuàng)建一張用戶表test_user，其中USER_ID為了效果我們?cè)O(shè)置為varchar類型且加上唯一索引。

CREATE TABLE test_user (
  ID int(11) NOT NULL AUTO_INCREMENT,
  USER_ID varchar(11) DEFAULT NULL COMMENT '用戶賬號(hào)',
  USER_NAME varchar(255) DEFAULT NULL COMMENT '用戶名',
  AGE int(5) DEFAULT NULL COMMENT '年齡',
  COMMENT varchar(255) DEFAULT NULL COMMENT '簡(jiǎn)介',
  PRIMARY KEY (ID)
  UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表格數(shù)據(jù)如下（嘻嘻數(shù)據(jù)依舊使用與上次Mysql的文章MySQL使用UNION連接兩個(gè)查詢排序失效相同的數(shù)據(jù)，但是要注意表結(jié)構(gòu)不同。）

ID	USER_ID	USER_NAME	AGE	COMMENT
1	111	開(kāi)心菜鳥(niǎo)	18	今天很開(kāi)心
2	222	悲傷菜鳥(niǎo)	21	今天很悲傷
3	333	認(rèn)真菜鳥(niǎo)	30	今天很認(rèn)真
4	444	高興菜鳥(niǎo)	18	今天很高興
5	555	嚴(yán)肅菜鳥(niǎo)	21	今天很嚴(yán)肅

接下來(lái)我們執(zhí)行以下sql

EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;

發(fā)現(xiàn)給出的解釋結(jié)果如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	test_user		ALL					5		Using where

我們給條件加上引號(hào)后再解釋以下：

EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111';

這時(shí)候我們發(fā)現(xiàn)varchar類型的字段在作為字符串查詢的時(shí)候使用了索引，在以數(shù)值類型進(jìn)行查詢時(shí)是不使用索引的。

問(wèn)題引申

那么問(wèn)題來(lái)了，如果字段是整型的且加上索引，以字符串查詢時(shí)會(huì)不會(huì)也不走索引呢？實(shí)踐出真知，讓我們?cè)俳又聹y(cè)試一下。

-- 將USER_ID的類型修改為整型
CREATE TABLE test_user (
  ID int(11) NOT NULL AUTO_INCREMENT,
  USER_ID int(11) DEFAULT NULL COMMENT '用戶賬號(hào)',
  USER_NAME varchar(255) DEFAULT NULL COMMENT '用戶名',
  AGE int(5) DEFAULT NULL COMMENT '年齡',
  COMMENT varchar(255) DEFAULT NULL COMMENT '簡(jiǎn)介',
  PRIMARY KEY (ID),
  UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;

EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111';

在執(zhí)行了上面兩個(gè)語(yǔ)句后我們發(fā)現(xiàn)，int類型的字段無(wú)論是以字符串查詢還是以數(shù)值型查詢都會(huì)走索引。

結(jié)論

當(dāng)我們使用的字段是數(shù)值類型時(shí)，加引號(hào)或者不加引號(hào)（sql中單引號(hào)和雙引號(hào)實(shí)現(xiàn)相同效果）都不影響索引的使用
當(dāng)我們的字段是字符串類型時(shí)，不加引號(hào)的查詢無(wú)法使用索引，加引號(hào)的查詢才可正常使用索引

綜上所述，我認(rèn)為以后寫(xiě)sql的時(shí)候注意最好都加上引號(hào)，避免這種字符串類型的不走索引的情況發(fā)生，更深層次的原理需要再挖掘一下，如果大家有什么意見(jiàn)可以探討一下。

到此這篇關(guān)于淺談MySql整型索引和字符串索引失效或隱式轉(zhuǎn)換問(wèn)題的文章就介紹到這了,更多相關(guān)MySql整型索引和字符串索引失效內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: