字符類型數(shù)據(jù)缺失引號(hào)索引被抑制

發(fā)布時(shí)間：2020-08-11 01:42:30 來(lái)源：ITPUB博客閱讀：144 作者：StevenBeijing 欄目：MySQL數(shù)據(jù)庫(kù)

字符類型的數(shù)據(jù)沒(méi)有使用引號(hào)，索引將被抑制，下邊看一個(gè)案例：
查看表結(jié)構(gòu)：

點(diǎn)擊(此處)折疊或打開(kāi)

username字段不加引號(hào)：

點(diǎn)擊(此處)折疊或打開(kāi)

mysql> select * from test06 where username=13499770088;
Empty set, 8208 warnings (5.77 sec)
mysql> explain select * from test06 where username=13499770088;
+----+-------------+--------+------+-----------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-----------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | test06 | ALL | idx_test03_name | NULL | NULL | NULL | 2009559 | Using where |
+----+-------------+--------+------+-----------------+------+---------+------+---------+-------------+

username字段加引號(hào)：

點(diǎn)擊(此處)折疊或打開(kāi)

mysql> select * from test06 where username='13499770088';
Empty set (0.07 sec)
mysql> explain select * from test06 where username='13499770088';
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | test06 | ref | idx_test03_name | idx_test03_name | 302 | const | 1 | Using index condition |
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+

查詢速度明顯變快，執(zhí)行計(jì)劃走了索引，這樣是正常的寫法。

向AI問(wèn)一下細(xì)節(jié)

猜你喜歡