MySQL 字串(char/varchar) 欄位搜尋數字(integer)

2022-04-14 約 653 字預計閱讀 2 分鐘

某次的 Table Query 下了一個基本的數字 id 條件做查詢，結果異樣的慢

帶一下 profiling 的過程找問題 ..

情境

該條 SQL 需要耗費將近 2 分鐘

db> SELECT * FROM order_task_table WHERE orderId = 2913339

分析

檢查索引 show index 是有的

db> show indexes from order_task_table\G
8 rows in set
Time: 0.167s
***************************[ 4. row ]***************************
Table         | order_task_table
Non_unique    | 1
Key_name      | orderid_index
Seq_in_index  | 2
Column_name   | orderId
Collation     | A
Cardinality   | 606842
Sub_part      | <null>
Packed        | <null>
Null          | YES
Index_type    | BTREE
Comment       |
Index_comment |

只好 Explain 一下

db> explain SELECT * FROM order_task_table WHERE orderId = 2913339

+----+-------------+---------------------+------+----------------+--------+---------+--------+---------+-------------+
| id | select_type | table               | type | possible_keys  | key    | key_len | ref    | rows    | Extra       |
+----+-------------+---------------------+------+----------------+--------+---------+--------+---------+-------------+
| 1  | SIMPLE      | order_task_table    | ALL  | customid_index | <null> | <null>  | <null> | 2427382 | Using where |
+----+-------------+---------------------+------+----------------+--------+---------+--------+---------+-------------+

怪哉，吃不到 index，只好再用 describe 看一下欄位狀況

db> describe order_task_table
+----------------------+---------------------+------+-----+---------------------+----------------+
| Field                | Type                | Null | Key | Default             | Extra          |
+----------------------+---------------------+------+-----+---------------------+----------------+
| ..........           | ............        | YES  | MUL | <null>              |                |
| orderId              | varchar(30)         | YES  | MUL | <null>              |                |
| ..........           | ............        | YES  | MUL | <null>              |                |

傻眼貓咪

結果號稱 Id 的欄位開的 Type 是 varchar…

這故事告訴我們，不要覺得欄位名稱跟型別會有關係，原本以為是數字型態 (integer/biginteger)

修正

修正方法，一律加上 Quote 引號把數字視為字串

上述情境無法使用指定索引來改善，只要你搜尋的條件是在字串欄位給數字，那就無法利用 Btree 索引

db> SELECT * FROM order_task_table WHERE orderId = '2913339'

探討

直接查到 DK 大寫的文章: MySQL 裡搜尋 CHAR/VARCHAR (String) 欄位時要注意的事情

數字被當成文字處理很常被誤用

欄位型態與查詢數字的內容做比較

欄位是 integer: 查詢內容是數字，不管有沒有帶引號，也就是內容有可能是數字或字串
- MySQL 會自己幫你轉成數字再比較，可以吃索引
欄位是 char/varchar: 查詢內容是數字，一定要使用引號將其視為字串
- MySQL 不會幫你轉，所以會吃不到索引
- 原因在 DK 大大文章內寫了

結論

建議

儲存內容如果確定是數字的，不要用 char/varchar 存
- 不要荼毒隊友
- 數字的儲存空間，在大多情況都比字串來得小
看到欄位名稱時，要記得檢查型別(type)
真的要在字串欄位裡搜尋數字，記得轉成字串(加上引號)

雷踩完了，就更強了

目錄

MySQL 字串(char/varchar) 欄位搜尋數字(integer)

情境

分析

修正

探討

欄位型態與查詢數字的內容做比較

結論