MySQL5.7 虛擬列實現表示式索引

航空母艦發表於2017-01-13
MySql索引

MySQL自古以來就不提供函式索引這麼複雜的功能。那怎麼在MySQL裡面實現這樣的功能呢？ 我們先來看看函式索引的概念。函式索引，也可稱為表示式索引，也就是基於欄位以特定函式（表示式）建立索引來提升查詢效能之需。函式索引的優勢在於更加精確的獲取所需要的資料。
MySQL 5.7提供了一個新的特性，虛擬列，可以很完美的解決這個問題。
在介紹虛擬列之前，我們來看看在MySQL裡面普通索引的範例。

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `rank` int(11) DEFAULT NULL,
  `log_time` datetime DEFAULT NULL,
  `nickname` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_rank` (`rank`),
  KEY `idx_log_time` (`log_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 假設我們來檢索2015年4月9號的資料。（結果是有兩條記錄，id 分別為95和3423。）

SELECT * FROM t1 WHERE DATE(log_time) = '2015-04-09'

下來我們看看這條語句的查詢計劃。

mysql> explain SELECT * FROM t1 WHERE DATE(log_time) = '2015-04-09'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5000
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

 我們發現TYPE是ALL，掃描的函式是5000，也就是說這條語句進行了一個全表掃描。 雖然給欄位log_time 加了索引，但是沒有用到，那這個時候怎麼辦？
在MySQL裡面一般這樣修改

SELECT * FROM t1 WHERE log_time >= '2015-04-09 00:00:00' AND log_time <='2015-04-10 00:00:00'

 通過查詢結果，發現結果集一致，那再來看看查詢計劃

mysql> explain SELECT * FROM t1 WHERE log_time >= '2015-04-09 00:00:00' AND log_time <= '2015-04-10 00:00:00'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: idx_log_time
          key: idx_log_time
      key_len: 6
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

可以看到這條修改過的語句很好的利用到了idx_log_time這條索引。
那好，這個是之前在MySQL 5.6以及之前的舊版本解決方法，隨著MySQL 5.7的釋出，虛擬列的出現讓這個問題更加簡單。


現在修改下之前的表結構：

ALTER TABLE t1 ADD COLUMN log_date DATE AS (DATE(log_Time)) stored, ADD KEY idx_log_date (log_date);

 這樣，增加了一新列，用來存放date(log_time)這個表示式，並且給他加了一列索引。

mysql> explain  SELECT * FROM t1 WHERE log_date = '2015-04-09'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_log_date
          key: idx_log_date
      key_len: 4
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

通過以上介紹，我們看到虛擬列實現起來相對之前的方法來的容易的多。但是這裡筆者還是得說上幾句。
函式索引的用法以及SQL語句雖然寫起來簡單，但是在大部分場合下，只能說不得已而為之，是一種設計上的缺陷，後期增加了運維人員的運維難度以及繁瑣度。這也就是為什麼MySQL 直到5.7才推出了這項類似的功能的原因。

相關文章