在 MySQL 中，有很多看上去邏輯相同，但性能卻差異巨大的 SQL 語句。對這些語句使用不當(dāng)?shù)脑挘蜁唤?jīng)意間導(dǎo)致整個數(shù)據(jù)庫的壓力變大。

今天精選真實案例與你分享，希望再遇到相似的問題時，你可以做到舉一反三、快速解決問題。

1、問題開篇

一張用戶表 `users` ，其中字段 `phone` 添加了普通索引。

CREATETABLEusers (
idbigint(20) unsignedNOTNULL AUTO_INCREMENT COMMENT'主鍵ID',
namevarchar(32) COLLATE utf8mb4_unicode_ci NOTNULLDEFAULT''COMMENT'名稱',
  phone varchar(16) COLLATE utf8mb4_unicode_ci NOTNULLDEFAULT''COMMENT'手機',
  created_at timestampNOTNULLDEFAULT'1970-01-01 16:00:00'COMMENT'創(chuàng)建時間',
  updated_at timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新時間',
  PRIMARY KEY (id),
KEY idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用戶表';

分別執(zhí)行以下SQL：

1）字符串類型查詢

EXPLAINSELECT * FROMusersWHERE phone = '2';

執(zhí)行計劃如下：

圖片

2）數(shù)值型查詢

EXPLAINSELECT * FROMusersWHERE phone = 2;

執(zhí)行計劃如下：

圖片

發(fā)現(xiàn)問題：

當(dāng)索引字段 `phone` 為字符串類型時，字符串查詢時候使用了索引`idx_phone`，而數(shù)值類型查詢時候竟無法使用索引`idx_phone`。

2、問題引申

假如索引字段為整型的話，那用字符串查詢時會不會走索引呢？

實踐出真知，我們來驗證一下。

同樣如上表，修改字段 `phone` 類型由 varchar 變更為 bigint：

ALTERTABLEusersMODIFYCOLUMN phone bigint(16) NOTNULLCOMMENT'手機';

然后，分別執(zhí)行以下SQL：

1）字符串類型查詢

EXPLAINSELECT * FROMusersWHERE phone = '2';

執(zhí)行計劃如下：

圖片

2）數(shù)值型查詢

EXPLAINSELECT * FROMusersWHERE phone = 2;

執(zhí)行計劃如下：

圖片

執(zhí)行后發(fā)現(xiàn)，無論是以字符串查詢還是以數(shù)值型查詢都會用到索引。

小結(jié)：

• 當(dāng)索引字段是數(shù)值類型時，數(shù)值型或者字符型查詢都不影響索引的使用。
• 當(dāng)索引字段是字符類型時，數(shù)值型查詢無法使用索引，字符型查詢可正常使用索引。

3、跟進(jìn)探究

為什么會是這樣呢？其根源就是MySQL的隱式類型轉(zhuǎn)換。

3.1 什么是隱式類型轉(zhuǎn)換？

在MySQL中，當(dāng)操作符與不同類型的操作數(shù)一起使用時，會發(fā)生類型轉(zhuǎn)換以使操作數(shù)兼容，則會發(fā)生隱式類型轉(zhuǎn)換。

即 MySQL會根據(jù)需要自動將數(shù)字轉(zhuǎn)換為字符串，或者將字符串轉(zhuǎn)換為數(shù)字。

mysql> SELECT 1+'1';
-> 2
mysql> SELECT CONCAT(2,' test');
->'2 test'

很明顯，上面的SQL語句的執(zhí)行過程中就出現(xiàn)了隱式轉(zhuǎn)化。

從結(jié)果我們可以判定，SQL1中將字符串的“1”轉(zhuǎn)換為數(shù)字1，而在SQL2 中，將數(shù)字2轉(zhuǎn)換為字符串“2”。

3.2 如何避免隱式類型轉(zhuǎn)換？

3.2.1 清楚轉(zhuǎn)換規(guī)則

只有當(dāng)清楚的知道隱式類型轉(zhuǎn)換的規(guī)則，才能從根本上避免產(chǎn)生隱式類型轉(zhuǎn)換。

參考MySQL文檔相關(guān)描述，確定隱式類型轉(zhuǎn)換規(guī)則：

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

驗證示例：

mysql> SELECT 'aa' + 1;
-> '1'
mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning |1292| Truncated incorrect DOUBLE value: 'aa' |
+---------+------+----------------------------------------+

上述示例中，將字符串 'aa' 和1進(jìn)行求和，因為 'aa' 和數(shù)字1的類型不同，通過上述轉(zhuǎn)換規(guī)則并且經(jīng)查看warnings可以確認(rèn)：隱式類型轉(zhuǎn)化將字符串轉(zhuǎn)為了 double 類型。

由于字符串是非數(shù)字型的，所以就會被轉(zhuǎn)換為0，因此計算結(jié)果：0+1=1

3.2.2 使用內(nèi)置函數(shù)顯示轉(zhuǎn)換

MySQL對數(shù)據(jù)進(jìn)行類型轉(zhuǎn)換，提供了cast() 和 convert()。

相同點：兩者都是進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換，實現(xiàn)的功能基本等同

不同點：兩者的語法不同：cast(value as type) 、 convert(value,type)

將數(shù)值型轉(zhuǎn)換為字符串型，應(yīng)用示例如下：

mysql> SELECT CAST(123aschar);
-> '123'
mysql> SELECT CONVERT(123, char);
-> '123'

假如應(yīng)用在開篇描述問題的查詢中，則如下所示：

EXPLAINSELECT * FROMusersWHERE phone = CAST(123ASCHAR);

執(zhí)行計劃所示：

圖片

結(jié)果顯示同應(yīng)用字符串類型參數(shù)一樣，可使用索引`idx_phone`。

3.2.3 類型保持一致

最簡單的一種，保證查詢應(yīng)用規(guī)范，SQL參數(shù)類型與數(shù)據(jù)庫中字段類型保持一致即可。

3.3 字符類型轉(zhuǎn)換

另外，關(guān)于字符串類型轉(zhuǎn)換的一些補充：

mysql> select '1a2b3c' = 1;
-> 1
mysql> select 'a1b2c3' = 0;
-> 1

從上面的例子可以得出：

• 如果字符串的第一個字符就是非數(shù)字的字符，那么轉(zhuǎn)換為數(shù)字就是0；
• 如果字符串以數(shù)字開頭，那轉(zhuǎn)換的數(shù)字就是開頭的那些數(shù)字對應(yīng)的值，直到遇到非數(shù)字字符才結(jié)束。

4、總結(jié)

本文主要從問題入手，繼而進(jìn)行問題引申，最終挖掘出問題根源：MySQL隱式類型轉(zhuǎn)換。

同時也告誡我們?nèi)粘Ｔ趯慡QL時一定要檢查參數(shù)類型與數(shù)據(jù)庫字段類型是否一致，否則可能造成隱式類型轉(zhuǎn)換，不能正常應(yīng)用索引，造成慢查詢，甚至拖垮整個數(shù)據(jù)庫服務(wù)集群。

如果參數(shù)不一致，也可以考慮使用CAST函數(shù)顯性轉(zhuǎn)換成一致類型。