類型轉(zhuǎn)換對MySQL選擇索引有什么影響

這篇文章主要講解了“類型轉(zhuǎn)換對MySQL選擇索引有什么影響”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“類型轉(zhuǎn)換對MySQL選擇索引有什么影響”吧！

成都創(chuàng)新互聯(lián)2013年至今，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢想脫穎而出為使命，1280元龍鳳做網(wǎng)站,已為上家服務(wù),為龍鳳各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:028-86922220

遇到了幾例 MySQL 沒用使用預(yù)期索引的問題，讀了些文檔之后，發(fā)現(xiàn) MySQL 的類型轉(zhuǎn)換對索引選擇的影響還真是一個(gè)不大不小的坑。

比如有這樣一張 MySQL 表：

CREATE TABLE `indextest` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` tinyint(3) unsigned NOT NULL DEFAULT ’0′,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_age` (`age`),
KEY `idx_create` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

name 是一個(gè)有索引的 varchar 字段，表內(nèi)數(shù)據(jù)是這樣的：

+—-+——–+—–+———————+
| id | name | age | create_time |
+—-+——–+—–+———————+
| 1 | hello | 10 | 2012-02-01 20:00:00 |
| 2 | world | 20 | 2012-02-02 20:00:00 |
| 3 | 111222 | 30 | 2012-02-03 20:00:00 |
| 4 | wow | 40 | 2012-02-04 20:00:00 |
+—-+——–+—–+———————+

使用字符串 ’111222′ 作為參數(shù)對 name 字段查詢，Execution Plan 如預(yù)期的一樣，會使用 name 字段上的索引 idx_name：

mysql [localhost] {msandbox} (test) > explain select age from
-> indextest where name=’111222′\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ref
possible_keys: idx_name
key: idx_name
key_len: 13
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

而使用數(shù)字作為參數(shù)對 name 字段做查詢時(shí)，explain 表明這將是全表掃描：

mysql [localhost] {msandbox} (test) > explain select age from
-> indextest where name=111222\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ALL
possible_keys: idx_name
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)

究其原因，是當(dāng)文本字段與數(shù)字進(jìn)行比較時(shí)，由于類型不同，MySQL 需要做隱式類型轉(zhuǎn)換才能進(jìn)行比較，結(jié)果就如上面的例子所提到的一樣。

MySQL 的文檔 (Type Conversion in Expression Evaluation) 中提到，在做比較時(shí)，會按這樣的規(guī)則進(jìn)行必要的類型轉(zhuǎn)換：

兩個(gè)參數(shù)至少有一個(gè)是 NULL 時(shí)，比較的結(jié)果也是 NULL，例外是使用 <=> 對兩個(gè) NULL 做比較時(shí)會返回 1，這兩種情況都不需要做類型轉(zhuǎn)換
兩個(gè)參數(shù)都是字符串，會按照字符串來比較，不做類型轉(zhuǎn)換
兩個(gè)參數(shù)都是整數(shù)，按照整數(shù)來比較，不做類型轉(zhuǎn)換
十六進(jìn)制的值和非數(shù)字做比較時(shí)，會被當(dāng)做二進(jìn)制串，和數(shù)字做比較時(shí)會按下面的規(guī)則處理
有一個(gè)參數(shù)是 TIMESTAMP 或 DATETIME，并且另外一個(gè)參數(shù)是常量，常量會被轉(zhuǎn)換為 timestamp
有一個(gè)參數(shù)是 decimal 類型，如果另外一個(gè)參數(shù)是 decimal 或者整數(shù)，會將整數(shù)轉(zhuǎn)換為 decimal 后進(jìn)行比較，如果另外一個(gè)參數(shù)是浮點(diǎn)數(shù)，則會把 decimal 轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較
所有其他情況下，兩個(gè)參數(shù)都會被轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較
比如：

mysql [localhost] {msandbox} (test) > SELECT ’18015376320243459′ =
-> 18015376320243459;
+—————————————–+
| ’18015376320243459′ = 18015376320243459 |
+—————————————–+
| 0 |
+—————————————–+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT ’18015376320243459′ + 0;
+————————-+
| ’18015376320243459′ + 0 |
+————————-+
| 1.80153763202435e+16 |
+————————-+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT
-> cast(’18015376320243459′ as unsigned) = 18015376320243459;
+———————————————————–+
| cast(’18015376320243459′ as unsigned) = 18015376320243459 |
+———————————————————–+
| 1 |
+———————————————————–+
1 row in set (0.00 sec)

因?yàn)楦↑c(diǎn)數(shù)精度(53 bits)問題，并且 MySQL 將字符串轉(zhuǎn)換為浮點(diǎn)數(shù)和將整數(shù)轉(zhuǎn)換為浮點(diǎn)數(shù)使用不同的方法，字符串 ’18015376320243459′ 和整數(shù) 18015376320243459 相比較就不相等，如果要避免隱式浮點(diǎn)數(shù)轉(zhuǎn)換帶來的精度問題，可以顯式地使用 cast 做類型轉(zhuǎn)換，將字符串轉(zhuǎn)換為整數(shù)。

按照這些規(guī)則，對于上面的例子來說，name 字段的值和查詢參數(shù) ’111222′ 都會被轉(zhuǎn)換為浮點(diǎn)數(shù)才會做比較，而很多文本都能轉(zhuǎn)換為和 111222 相等的數(shù)值，比如 ’111222′, ’111222aabb’, ‘ 111222′ 和 ’11122.2e1′，所以 MySQL 不能有效使用索引，就退化為索引掃描甚至是全表掃描。

而反過來，如果使用一個(gè)字符串作為查詢參數(shù)，對一個(gè)數(shù)字字段做比較查詢，MySQL 則是可以有效利用索引的：

mysql [localhost] {msandbox} (test) > explain select name from
-> indextest where age=’30′\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ref
possible_keys: idx_age
key: idx_age
key_len: 1
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)

原因則是，MySQL 可以將查詢參數(shù) ’30′ 轉(zhuǎn)換為確定的數(shù)值 30，之后可以快速地在索引中找到與之相等的數(shù)值。

除此之外，使用函數(shù)對索引字段做顯式類型轉(zhuǎn)換或者計(jì)算也會使 MySQL 無法使用索引：

mysql [localhost] {msandbox} (test) > explain select name from
-> indextest where cast(age as unsigned)=30\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)

如上，使用 cast 函數(shù)對 age 做顯式的類型轉(zhuǎn)換，會使索引失效，當(dāng)然了，在實(shí)際的代碼中很少會有這樣的寫法，但類似下面這樣對時(shí)間字段做運(yùn)算的用法就比較多了：

mysql [localhost] {msandbox} (test) > explain select * from
-> indextest where date(create_time)=’2012-02-02′\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)

對于本例的需求，是想查找 create_time 是 2012-02-02 這一天的記錄，用變通的方法，避免在索引字段上做運(yùn)算就可以有效使用索引了：

mysql [localhost] {msandbox} (test) > explain select * from
-> indextest where create_time between ’2012-02-02′ and ’2012-02-03′\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: range
possible_keys: idx_create
key: idx_create
key_len: 4
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)

感謝各位的閱讀，以上就是“類型轉(zhuǎn)換對MySQL選擇索引有什么影響”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對類型轉(zhuǎn)換對MySQL選擇索引有什么影響這一問題有了更深刻的體會，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！

網(wǎng)站標(biāo)題：類型轉(zhuǎn)換對MySQL選擇索引有什么影響
網(wǎng)頁網(wǎng)址：http://chinadenli.net/article8/jiieop.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站營銷、App開發(fā)、ChatGPT、自適應(yīng)網(wǎng)站、搜索引擎優(yōu)化、網(wǎng)站改版

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

猜你還喜歡下面的內(nèi)容

欧美一区二区三区老妇人-欧美做爰猛烈大尺度电-99久久夜色精品国产亚洲a-亚洲福利视频一区二区

類型轉(zhuǎn)換對MySQL選擇索引有什么影響