實際案例：“隐式轉換”導緻的索引失效

一、現象

1.1、表結構（資料庫tidb）

CREATE TABLE `daily_customer_warn_data` (
  `id` bigint(15) NOT NULL AUTO_INCREMENT,
  `biz_uid` bigint(20) NOT NULL COMMENT '商業uid',
  `zz_uid` bigint(20) NOT NULL COMMENT '轉轉uid',
  `warn_type` int(10) NOT NULL DEFAULT '0' COMMENT '',
  `dt` varchar(10) NOT NULL COMMENT '資料歸屬日期，yyyy-MM-dd'
      
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_dt_pv1_pvr_keyword` (`dt`,`biz_uid`,`warn_type`)
)  ；
           

1.2、SQL查詢

mysql> select count(1) from daily_customer_warn_data where dt=date_sub(‘2021-04-30’,interval 8 day);

±---------+

| count(1) |

±---------+

| 39232 |

±---------+

1 row in set (15.16 sec)

沒什麼沒有走uniq_dt_pv1_pvr_keyword來查詢呢？

二、分析：

2.1、SQL的執行計劃

mysql> explain

-> select

-> count(1)

-> from daily_customer_warn_data where dt=date_sub(‘2021-04-30’,interval 8 day);

±-----------------------±------------±-----±--------------------------------------------------------------------+

| id | count | task | operator info |

±-----------------------±------------±-----±--------------------------------------------------------------------+

| StreamAgg_10 | 1.00 | root | funcs:count(1) |

| └─Selection_14 | 13893928.80 | root | eq(cast(tdb_adstat.daily_customer_warn_data.dt), 2021-04-22) |

| └─TableReader_16 | 17367411.00 | root | data:TableScan_15 |

| └─TableScan_15 | 17367411.00 | cop | table:daily_customer_warn_data, range:[-inf,+inf], keep order:false |

2.2、原因：

eq(cast(tdb_adstat.daily_customer_warn_data.dt), 2021-04-22)，cast(tdb_adstat.daily_customer_warn_data.dt) 是将dt轉換成時間類型，這樣索引就失效了

2.3、tidb文檔：

值得注意的是，TiDB 目前隻支援比較符一端是列，另一端是常量，或可以計算成某一常量的情況，類似 year(birth_day) < 1992 的查詢條件是不能利用索引的。還要注意應盡可能使用同一類型進行比較，以避免引入額外的 cast 操作而導緻不能利用索引，如 user_id = 123456，如果 user_id 是字元串，需要将 123456 也寫成字元串常量的形式。

4、優化：

explain
select
count(1)
from daily_customer_warn_data   where dt=date_sub('2021-04-30',interval 8 day);
           

±-----------------------±------------±-----±--------------------------------------------------------------------+

| id | count | task | operator info |

±-----------------------±------------±-----±--------------------------------------------------------------------+

| StreamAgg_10 | 1.00 | root | funcs:count(1) |

| └─Selection_14 | 13893928.80 | root | eq(cast(tdb_adstat.daily_customer_warn_data.dt), 2021-04-22) |

| └─TableReader_16 | 17367411.00 | root | data:TableScan_15 |

| └─TableScan_15 | 17367411.00 | cop | table:daily_customer_warn_data, range:[-inf,+inf], keep order:false |

三、思考

3.1 什麼是隐式轉換

當運算符與不同類型的操作數一起使用時，将進行類型轉換以使操作數相容。某些轉換是隐式發生的。例如，MySQL會根據需要自動将字元串轉換為數字，反之亦然。例如

mysql> SELECT 1+'1';
+-------+
| 1+'1' |
+-------+
|     2 |
+-------+
           

SELECT CONCAT(2,' test');
+-------------------+
| CONCAT(2,' test') |
+-------------------+
| 2 test            |
+-------------------+
           

3.2 産生的條件

1. 兩個參數至少有一個是 NULL 時，比較的結果也是 NULL，例外是使用 <=> 對兩個 NULL 做比較時會傳回1，這兩種情況都不需要做類型轉換
2. 兩個參數都是字元串，會按照字元串來比較，不做類型轉換
3. 兩個參數都是整數，按照整數來比較，不做類型轉換
4. 十六進制的值和非數字做比較時，會被當做二進制串
5. 有一個參數是 TIMESTAMP 或DATETIME，并且另外一個參數是常量，常量會被轉換為 timestamp 有一個參數是 decimal 類型，

6. 如果另外一個參數是decimal 或者整數，會将整數轉換為 decimal 後進行比較，如果另外一個參數是浮點數，則會把 decimal

   轉換為浮點數進行比較 所有其他情況下，兩個參數都會被轉換為浮點數再進行比較

3.3 解決

MySQL 的類型轉換函數 cast 和 convert，來明确的進行轉換。

關注下，一起學習～～