現(xiàn)象

成都創(chuàng)新互聯(lián)是一家專注于網(wǎng)站設(shè)計制作、成都網(wǎng)站設(shè)計與策劃設(shè)計,射陽網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)十余年,網(wǎng)設(shè)計領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:射陽等地區(qū)。射陽做網(wǎng)站價格咨詢:13518219792

今天遇到一個慢查詢，查詢?nèi)罩菊业铰樵冋Z句是這樣的：

 
 
 
 

  
  
  
  
select * from convert_test where areacode=0001 and period>='20170511' and period<='20170511'; 
 
 
 
 

convert_test表結(jié)構(gòu)如下：

 
 
 
 

  
  
  
  
CREATE TABLE `convert_test` ( 
  
  
  
  
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
  
  
  
  
    `areacode` char(12) NOT NULL DEFAULT '', 
  
  
  
  
    `period` int(6) unsigned NOT NULL DEFAULT 0, 
  
  
  
  
    `mid_price` int(10) unsigned NOT NULL DEFAULT 0, 
  
  
  
  
    `mid_change` float NOT NULL DEFAULT 0, 
  
  
  
  
    `updated_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  
  
  
  
    PRIMARY KEY (`id`), 
  
  
  
  
    UNIQUE KEY `idx_areacode_period` (`areacode`,`period`) 
  
  
  
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='隱式轉(zhuǎn)換測試表'; 
 
 
 
 

表中數(shù)據(jù)42W以上。

乍一看，明明創(chuàng)建了一個唯一索引，正常來說，上面的查詢語句應(yīng)該正好***idx_areacode_period這個索引的，不應(yīng)該是慢查詢的。

為了查看這個語句是怎么查詢的，我們在測試庫中explain一下：

 
 
 
 

  
  
  
  
mysql> explain select * from convert_test where areacode=0001 and period>='20170511' and period<='20170511'; 
 
 
 
 

結(jié)果如下：

可以看到，這里是沒有用到索引的。

原因

定義表的時候，areacode字段是字符串類型的，查詢的時候傳入的是0001，這里0001被Mysql當做了整數(shù)處理為1，Mysql檢測到areacode這個字段的查詢類型是整型，就會全表掃描，將所有行的areacode轉(zhuǎn)換成整型，然后在做查詢處理。

找原因了，就很好解決了，上面的sql語句修改如下：

 
 
 
 

  
  
  
  
mysql> explain select * from convert_test where areacode='0001' and period>='20170511' and period<='20170511'; 
 
 
 
 

結(jié)果如下： 

可以看到完全***了idx_areacode_period 這個索引。

擴展

上面的period定義的時候是整型，但是查詢傳入的是字符串類型，那為什么會***索引的呢？

看一下官方的隱試轉(zhuǎn)換說明：

1. 兩個參數(shù)至少有一個是 NULL 時，比較的結(jié)果也是 NULL，例外是使用 <=> 對兩個 NULL 做比較時會返回 1，這兩種情況都不需要做類型轉(zhuǎn)換

2. 兩個參數(shù)都是字符串，會按照字符串來比較，不做類型轉(zhuǎn)換

3. 兩個參數(shù)都是整數(shù)，按照整數(shù)來比較，不做類型轉(zhuǎn)換

4. 十六進制的值和非數(shù)字做比較時，會被當做二進制串

5. 有一個參數(shù)是 TIMESTAMP 或 DATETIME，并且另外一個參數(shù)是常量，常量會被轉(zhuǎn)換為 timestamp

6. 有一個參數(shù)是 decimal 類型，如果另外一個參數(shù)是 decimal 或者整數(shù)，會將整數(shù)轉(zhuǎn)換為 decimal 后進行比較，如果另外一個參數(shù)是浮點數(shù)，則會把 decimal 轉(zhuǎn)換為浮點數(shù)進行比較

7. 所有其他情況下，兩個參數(shù)都會被轉(zhuǎn)換為浮點數(shù)再進行比較

所以,下面的幾個sql語句有相同的效果：

 
 
 
 

  
  
  
  
select * from convert_test where areacode=0001 and period>='20170511' and period<='20170511'; 
  
  
  
  
select * from convert_test where areacode=1 and period>='20170511' and period<='20170511'; 
  
  
  
  
select * from convert_test where areacode=0001.0 and period>='20170511' and period<='20170511'; 
  
  
  
  
select * from convert_test where areacode=1.0 and period>='20170511' and period<='20170511'; 
 
 
 
 

mysql 在查詢的時候，會將areacode轉(zhuǎn)換成浮點型進行比較 

分享文章：MySQL數(shù)據(jù)類型隱式轉(zhuǎn)換規(guī)則
本文路徑：http://uogjgqi.cn/article/dpgooei.html