淺談MySql整型索引和字符串索引失效或隱式轉(zhuǎn)換問題
問題概述
今天在上班時，DBA突然找出來一段sql，表示該sql存在隱式轉(zhuǎn)換，不走索引。經(jīng)過我們的查看后，發(fā)現(xiàn)是類型varchar的字段， 我們使用條件傳入了數(shù)值型的值，由于擔(dān)心違反保密協(xié)議，在此就不貼圖了，由我重現(xiàn)一下類似情況給大家看一下。
問題重現(xiàn)
首先我們先創(chuàng)建一張用戶表test_user，其中USER_ID為了效果我們設(shè)置為varchar類型且加上唯一索引。
CREATE TABLE test_user ( ID int(11) NOT NULL AUTO_INCREMENT, USER_ID varchar(11) DEFAULT NULL COMMENT '用戶賬號', USER_NAME varchar(255) DEFAULT NULL COMMENT '用戶名', AGE int(5) DEFAULT NULL COMMENT '年齡', COMMENT varchar(255) DEFAULT NULL COMMENT '簡介', PRIMARY KEY (ID) UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表格數(shù)據(jù)如下（嘻嘻 數(shù)據(jù)依舊使用與上次Mysql的文章MySQL使用UNION連接兩個查詢排序失效相同的數(shù)據(jù)，但是要注意表結(jié)構(gòu)不同。）
| ID | USER_ID | USER_NAME | AGE | COMMENT |
|---|---|---|---|---|
| 1 | 111 | 開心菜鳥 | 18 | 今天很開心 |
| 2 | 222 | 悲傷菜鳥 | 21 | 今天很悲傷 |
| 3 | 333 | 認(rèn)真菜鳥 | 30 | 今天很認(rèn)真 |
| 4 | 444 | 高興菜鳥 | 18 | 今天很高興 |
| 5 | 555 | 嚴(yán)肅菜鳥 | 21 | 今天很嚴(yán)肅 |
接下來我們執(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 |
我們給條件加上引號后再解釋以下：
EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111';
這時候我們發(fā)現(xiàn)varchar類型的字段在作為字符串查詢的時候使用了索引，在以數(shù)值類型進(jìn)行查詢時是不使用索引的。
問題引申
那么問題來了，如果字段是整型的且加上索引，以字符串查詢時會不會也不走索引呢？實(shí)踐出真知，讓我們再接著往下測試一下。
-- 將USER_ID的類型修改為整型 CREATE TABLE test_user ( ID int(11) NOT NULL AUTO_INCREMENT, USER_ID int(11) DEFAULT NULL COMMENT '用戶賬號', USER_NAME varchar(255) DEFAULT NULL COMMENT '用戶名', AGE int(5) DEFAULT NULL COMMENT '年齡', COMMENT varchar(255) DEFAULT NULL COMMENT '簡介', 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í)行了上面兩個語句后我們發(fā)現(xiàn)，int類型的字段無論是以字符串查詢還是以數(shù)值型查詢都會走索引。
結(jié)論
- 當(dāng)我們使用的字段是數(shù)值類型時，加引號或者不加引號（sql中單引號和雙引號實(shí)現(xiàn)相同效果）都不影響索引的使用
- 當(dāng)我們的字段是字符串類型時，不加引號的查詢無法使用索引，加引號的查詢才可正常使用索引
綜上所述，我認(rèn)為以后寫sql的時候注意最好都加上引號，避免這種字符串類型的不走索引的情況發(fā)生，更深層次的原理需要再挖掘一下，如果大家有什么意見可以探討一下。
到此這篇關(guān)于淺談MySql整型索引和字符串索引失效或隱式轉(zhuǎn)換問題的文章就介紹到這了,更多相關(guān)MySql整型索引和字符串索引失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL的慢查詢,全名是慢查詢?nèi)罩?是MySQL提供的一種日志記錄,用來記錄在MySQL中響應(yīng)時間超過閥值的語句,下面這篇文章主要給大家介紹了關(guān)于MySQL慢查詢以及解決方案的相關(guān)資料,需要的朋友可以參考下2023-05-05
mysql xtrabackup 備份恢復(fù)實(shí)現(xiàn)分享
Xtrabackup是由percona提供的mysql數(shù)據(jù)庫備份工具，據(jù)官方介紹，這也是世界上惟一一款開源的能夠?qū)nnodb和xtradb數(shù)據(jù)庫進(jìn)行熱備的工具2012-11-11
這篇文章主要介紹了mysql日期處理函數(shù)實(shí)例解析,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2019-12-12
本文介紹了兩種mysql對自增id重新從1排序的方法，簡少了對于某個項(xiàng)目初始化數(shù)據(jù)的工作量，感興趣的朋友可以參考下2015-07-07
MySQL默認(rèn)的事務(wù)隔離級別是可重復(fù)讀，用Spring開發(fā)程序時，如果不設(shè)置隔離級別默認(rèn)用MySQL設(shè)置的隔離級別，如果Spring設(shè)置了就用已設(shè)置的隔離級別，本文重點(diǎn)給大家介紹Mysql事務(wù)隔離級別與鎖機(jī)制的相關(guān)知識，一起看看吧2021-09-09
使用SQL實(shí)現(xiàn)小計(jì),合計(jì)以及排序
本篇文章是對SQL實(shí)現(xiàn)小計(jì),合計(jì)以及排序進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
剛開始安裝時使用了默認(rèn)目錄，使用一段時間，數(shù)據(jù)慢慢變在，發(fā)現(xiàn)當(dāng)前設(shè)置的目錄空間不夠時，就要搬遷數(shù)據(jù)到另一個目錄了2011-10-10
本文通過一個實(shí)例來給大家講述一下Mysql數(shù)據(jù)庫存儲過程基本語法，希望你能喜歡。2017-11-11
Mysql中order by、group by、having的區(qū)別深入分析
本篇文章是對Mysql中order by、group by、having的區(qū)別進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
在MySQL中有時需要隨機(jī)生成數(shù)字或字符串，隨機(jī)生產(chǎn)數(shù)字可直接使用rand()函數(shù)，但是要隨機(jī)生成字符串就比較麻煩。2010-12-12