溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“類型轉換對MySQL選擇索引有什么影響”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“類型轉換對MySQL選擇索引有什么影響”吧！
遇到了幾例 MySQL 沒用使用預期索引的問題，讀了些文檔之后，發現 MySQL 的類型轉換對索引選擇的影響還真是一個不大不小的坑。
比如有這樣一張 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 是一個有索引的 varchar 字段，表內數據是這樣的：
+—-+——–+—–+———————+
| 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′ 作為參數對 name 字段查詢，Execution Plan 如預期的一樣，會使用 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)
而使用數字作為參數對 name 字段做查詢時，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)
究其原因，是當文本字段與數字進行比較時，由于類型不同，MySQL 需要做隱式類型轉換才能進行比較，結果就如上面的例子所提到的一樣。
MySQL 的文檔 (Type Conversion in Expression Evaluation) 中提到，在做比較時，會按這樣的規則進行必要的類型轉換：
兩個參數至少有一個是 NULL 時，比較的結果也是 NULL，例外是使用 <=> 對兩個 NULL 做比較時會返回 1，這兩種情況都不需要做類型轉換
兩個參數都是字符串，會按照字符串來比較，不做類型轉換
兩個參數都是整數，按照整數來比較，不做類型轉換
十六進制的值和非數字做比較時，會被當做二進制串，和數字做比較時會按下面的規則處理
有一個參數是 TIMESTAMP 或 DATETIME，并且另外一個參數是常量，常量會被轉換為 timestamp
有一個參數是 decimal 類型，如果另外一個參數是 decimal 或者整數，會將整數轉換為 decimal 后進行比較，如果另外一個參數是浮點數，則會把 decimal 轉換為浮點數進行比較
所有其他情況下，兩個參數都會被轉換為浮點數再進行比較
比如：
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)
因為浮點數精度(53 bits)問題，并且 MySQL 將字符串轉換為浮點數和將整數轉換為浮點數使用不同的方法，字符串 ’18015376320243459′ 和整數 18015376320243459 相比較就不相等，如果要避免隱式浮點數轉換帶來的精度問題，可以顯式地使用 cast 做類型轉換，將字符串轉換為整數。
按照這些規則，對于上面的例子來說，name 字段的值和查詢參數 ’111222′ 都會被轉換為浮點數才會做比較，而很多文本都能轉換為和 111222 相等的數值，比如 ’111222′, ’111222aabb’, ‘ 111222′ 和 ’11122.2e1′，所以 MySQL 不能有效使用索引，就退化為索引掃描甚至是全表掃描。
而反過來，如果使用一個字符串作為查詢參數，對一個數字字段做比較查詢，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 可以將查詢參數 ’30′ 轉換為確定的數值 30，之后可以快速地在索引中找到與之相等的數值。
除此之外，使用函數對索引字段做顯式類型轉換或者計算也會使 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 函數對 age 做顯式的類型轉換，會使索引失效，當然了，在實際的代碼中很少會有這樣的寫法，但類似下面這樣對時間字段做運算的用法就比較多了：
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 這一天的記錄，用變通的方法，避免在索引字段上做運算就可以有效使用索引了：
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)
感謝各位的閱讀，以上就是“類型轉換對MySQL選擇索引有什么影響”的內容了，經過本文的學習后，相信大家對類型轉換對MySQL選擇索引有什么影響這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
