varchar int 查詢 到底什麼情況下走索引？

来源:https://www.cnblogs.com/gjc592/archive/2020/04/01/12616781.html
-Advertisement-
Play Games

一個字元類型的、一個int類型的，查詢的時候到底會不會走索引，其實很多工作了幾年的開發人員有時也會暈，下麵就用具體事例來測試一下。 1. 準備工作 先準備2張表，以備後續測試使用。 表1：創建表test1,總共3列，其中id 是主鍵（int）,c_no 為int型，且有索引，c_2為普通欄位 /*創 ...


 

一個字元類型的、一個int類型的，查詢的時候到底會不會走索引，其實很多工作了幾年的開發人員有時也會暈，下麵就用具體事例來測試一下。

1.  準備工作

先準備2張表，以備後續測試使用。

表1：創建表test1,總共3列，其中id 是主鍵（int）,c_no 為int型，且有索引，c_2為普通欄位

/*創建表test1 */
create table  test1(id int primary key,c_no  int ,c_2 varchar(1),key c_no(c_no));

/* 插入一些測試數據 */
insert  into test1 values(1,1,'0'),(2,2,'1'),(3,4,'1'),(4,6,'0'),(5,7,''1),(6,11,'2'),(7,5,'3'),(8,100,'0'),(9,30,'1'),(10,50,'0');

表2： 創建表test1,總共3列，其中id 是主鍵（int）,c_no 為字元型，且有索引，c_2為普通欄位

/* 創建test2 */
create table  test2(id int primary key  auto_increment,c_no  varchar(11) ,c2 varchar(2),key c_no(c_no));

/* 插入一些測試數據 */
 insert  into test2 values(1,'5','1'),(4,'100','0'),(3,'30','1'),(10,'500','0'),(11,'20','0'),(12,'20a','0'),(15,'020b','1');

兩張表的差異是c_no的欄位類型不同。

 

2.    等值查詢測試

2.1  測試test1

test1.c_no欄位為int類型，下麵分別用整型和字元串進行比較，查看是否走索引。對應的執行計劃如下：

mysql> explain  select *  from test1 where c_no='100';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test1 | NULL       | ref  | c_no          | c_no | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain  select *  from test1 where c_no=100;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test1 | NULL       | ref  | c_no          | c_no | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可見，兩種方式均走索引了，且走的是c_no的索引，類型為ref為const（常量的等值查詢），掃行數為1

也就是說當表中的欄位類型為整型時，無論查詢用字元串類型的數字還是int類型的數字均能走索引。其中用int類型的值查詢能走索引可以容易理解，那麼，字元型的為什麼能走？ 其實這裡的字元類型做了隱式轉化，上例中就相當於

mysql> explain  select *  from test1 where c_no=CAST('100' as UNSIGNED);
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test1 | NULL       | ref  | c_no          | c_no | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 

2.2  測試 test2 表

以同樣的方式測試一下test2的查詢情況

先測試正常情況下字元型與字元型比較，結果可想而知，可以走索引，如下：

mysql> explain  select *  from test2 where c_no='100';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test2 | NULL       | ref  | c_no          | c_no | 47      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

而，如果是整型再查呢？結果如下（很遺憾，不能走索引了）

mysql> explain  select *  from test2 where c_no=100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | ALL  | c_no          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

也就是說，表中欄位為字元類型的時候，查詢的值為整型時，無法走索引了。

那這句相當於如下情況：

mysql> explain  select *  from test2 where cast(c_no  as  unsigned)=100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

也就是c_no做了隱式轉化。因為如果是100做了影視轉化，那麼結果應該是可以走索引，例如：

mysql> explain  select *  from test2 where c_no=cast(100 as char);
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test2 | NULL       | ref  | c_no          | c_no | 47      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

由此，我們也應證瞭如果欄位做了函數計算後，該列上即使有索引也無法使用（MySQL8.0之前的版本）。

2.3  進一步測試

其實針對test2表 還可以測試一點，進一步證明是c_no欄位做了隱式轉化，例如：

mysql> select  * from test2 where c_no=20;
+----+------+------+
| id | c_no | c2   |
+----+------+------+
| 11 | 20   | 0    |
| 12 | 20a  | 0    |
| 15 | 020b | 1    |
+----+------+------+
3 rows in set, 2 warnings (0.00 sec)

另外，看到了2個警告，內容如下：

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '20a'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: '020b' |
+---------+------+------------------------------------------+
2 rows in set (0.00 sec)

更加證明瞭轉化為數字型（預轉為double）

 

3.  小結

通過上面的簡單測試，即可發現如下結論：

  • 當表中的欄位類型為整型時，無論查詢用字元串類型的數字還是int類型的數字均能走索引；
  • 表中欄位為字元類型的時候，查詢的值為整型時，無法走索引；
  • 如果欄位做了函數計算後，該列上即使有索引也無法使用（MySQL8.0之前的版本）

因此開發同學在寫SQL的時候要註意SQL的寫法，缺少一個單引號可能導致很大的性能差異。

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • vi編輯器操作
    vi的基本操作 如果只鍵入vi，沒有文件名，也能進入vi,退出命令時輸入文件名才能 被存入磁碟。 進入vi後，游標處於特定某行上，如果去掉“+”號後面數字，游標處於文件最末行 模式識別匹配，在命令中指定一個字元串，進入vi後，游標處於文件中第一個與指定模式串相匹配那行，所有匹配的字元串被高亮。 末行 ...
  • Linux 切換 shell
    查看當前已安裝的shell 查看當前使用的shell 切換Shell ...
  • 運行docker大致流程
    平時部署測試環境使用jenkins將代碼打包成docker鏡像部署在rancher中，閑下來研究了一下docker的大致流程，自己畫了一個流程圖 ...
  • 那些好用到手軟的軟體和網站
    今天推薦一下那些大神平常使用的電腦軟體和工具類網站，讓你秒變大神。 下麵的整理前面部分是基礎的軟體，做不做技術都需要安裝的一些，後面部分是對程式猿推薦的一些開發軟體和插件。 操作系統 1、微軟正版win10系統安裝：bai度搜索 下載Windows10（訪問這裡 "下載windows10" ） 2、 ...
  • 使用grep -o統計文件中某個字元串出現的次數
    昨天，老師課後給出了一道題： 統計/etc/passwd中有多少個sbin 菜雞如我，沒想到怎麼做。只能在今天上課的時候認真聽聽老師講的方法。 grep o grep命令有一個參數 ，可以逐行輸出匹配的內容，有多少個匹配就有多少行。所以這道題可以這麼做： wc l 用來統計行數 另一種思路 老師還講 ...
  • 老闆說，Vim巨集都不會用，你的工作效率太低啦~
    工作中，對於文本文件的編輯我們經常有這樣的需求： 多次重覆輸入一段相同文本； 生成一段序列化的文本； 每行文本插入一句相同的文本。 除此之外，還有很多需要重覆操作的動作。對於這些需求，如果我們人工去操作的話，肯定是非常枯燥乏味，並且要浪費很多時間精力。 這時， Vim 巨集就派上用場了，它可以通過一些 ...
  • Redis系列(一)：小試牛刀
    隨著互聯網的高速發展，傳統的關係資料庫(如MySQL、Microsoft SQL Server等)已不能滿足日益增長的業務需求，如商品秒殺、搶購等及時性非常強的功能，隨著應用高併發的訪問，會造成系統資料庫崩潰，為瞭解決此種情況，需要引用一個緩存中間件，市面上比較常用的緩存中間件有Redis 和 Me... ...
  • hadoop 多次格式化format namenode 導致節點不能啟動解決
    鏈接：https://blog.csdn.net/qq_41059374/article/details/80695581?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.no ...
一周排行
    -Advertisement-
    Play Games
  • .NET 6 Entity Framework Core Db First（1） 創建模型
    .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 實測可以完整運行在 win7sp1/win10/win11. 如果用其他工具打包,還可以運行在mac/linux下, 傳送門BlazorHybrid 發佈為無依賴包方式 安裝 WebView2Runtime 1.57 M ...
  • 資料庫升級PostgreSql+Garnet
    目錄前言PostgreSql安裝測試額外Nuget安裝Person.cs模擬運行Navicate連postgresql解決方案Garnet為什麼要選擇Garnet而不是RedisRedis不再開源Windows版的Redis是由微軟維護的Windows Redis版本老舊，後續可能不再更新Garne ...
  • 如何基於surging架設流媒體視頻推流（視頻講解）
    C#TMS系統代碼-聯表報表學習 領導被裁了之後很快就有人上任了，幾乎是無縫銜接，很難讓我不想到這早就決定好了。我的職責沒有任何變化。感受下來這個系統封裝程度很高，我只要會調用方法就行。這個系統交付之後不會有太多問題，更多應該是做小需求，有大的開發任務應該也是第二期的事，嗯？怎麼感覺我變成運維了？而 ...
  • EAV模型（實體-屬性-值）的設計和低代碼的處理方案（2）--數據的查詢處理
    我在隨筆《EAV模型（實體-屬性-值）的設計和低代碼的處理方案（1）》中介紹了一些基本的EAV模型設計知識和基於Winform場景下低代碼（或者說無代碼）的一些實現思路，在本篇隨筆中，我們來分析一下這種針對通用業務，且只需定義就能構建業務模塊存儲和界面的解決方案，其中的數據查詢處理的操作。 ...
  • C# 程式集、模塊和類型概念及關係
    對某個遠程伺服器啟用和設置NTP服務(Windows系統) 打開註冊表 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\TimeProviders\NtpServer 將 Enabled 的值設置為 1，這將啟用NTP伺服器功 ...
  • Django信號與擴展：深入理解與實踐
    title: Django信號與擴展：深入理解與實踐 date: 2024/5/15 22:40:52 updated: 2024/5/15 22:40:52 categories: 後端開發 tags: Django 信號 松耦合 觀察者 擴展 安全 性能 第一部分：Django信號基礎 Djan ...
  • Django3.2安裝源碼xadmin2步驟以及遇到的問題
    使用xadmin2遇到的問題&解決 環境配置： 使用的模塊版本： 關聯的包 Django 3.2.15 mysqlclient 2.2.4 xadmin 2.0.1 django-crispy-forms >= 1.6.0 django-import-export >= 0.5.1 django-r ...
  • 原創->CommonsCollections1-DefaultMap鏈
    今天我打算整點兒不一樣的內容，通過之前學習的TransformerMap和LazyMap鏈，想搞點不一樣的，所以我關註了另外一條鏈DefaultedMap鏈，主要調用鏈為： 調用鏈詳細描述： ObjectInputStream.readObject() DefaultedMap.readObject ...
  • 05-應用級開發者 AI 時代破局點
    後端應用級開發者該如何擁抱 AI GC？就是在這樣的一個大的浪潮下，我們的傳統的應用級開發者。我們該如何選擇職業或者是如何去快速轉型，跟上這樣的一個行業的一個浪潮? 0 AI金字塔模型 越往上它的整個難度就是職業機會也好，或者說是整個的這個運作也好,它的難度會越大,然後越往下機會就會越多,所以這是一 ...
  • Java面試題：讓依賴註入變得簡單，面對@Autowired和@Resource，該如何選擇？
    @Autowired是Spring框架提供的註解，@Resource是Java EE 5規範提供的註解。 @Autowired預設按照類型自動裝配，而@Resource預設按照名稱自動裝配。 @Autowired支持@Qualifier註解來指定裝配哪一個具有相同類型的bean，而@Resourc... ...
所有分類