MySQL5.7 虛擬列實現運算式索引

來源:互聯網
上載者:User

MySQL5.7 虛擬列實現運算式索引

MySQL自古以來就不提供函數索引這麼複雜的功能。那怎麼在MySQL裡面實現這樣的功能呢? 我們先來看看函數索引的概念。函數索引,也可稱為運算式索引,也就是基於欄位以特定函數(運算式)建立索引來提升查詢效能之需。函數索引的優勢在於更加精確的擷取所需要的資料。

MySQL 5.7提供了一個新的特性,虛擬列,可以很完美的解決這個問題。

在介紹虛擬列之前,我們來看看在MySQL裡面普通索引的範例。

樣本表結構:

CREATE TABLE t1 (id INT ,rank INT, log_time DATETIME, nickname VARCHAR(64)) ENGINE INNODB;
ALTER TABLE t1 ADD PRIMARY KEY (id), ADD KEY idx_rank (rank),ADD KEY idx_log_time (log_time);

樣本表資料量,這裡我增加了5000條記錄:

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|    5000 |
+----------+
1 row in set (0.00 sec)

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

mysql> SELECT * FROM t1 WHERE DATE(log_time) = '2015-04-09'\G
*************************** 1. row ***************************
      id: 95
    rank: 24
log_time: 2015-04-09 05:53:13
nickname: test
*************************** 2. row ***************************
      id: 3423
    rank: 42
log_time: 2015-04-09 02:55:38
nickname: test
2 rows in set (0.01 sec)

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

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裡面一般這樣修改:

mysql> 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: 3423
    rank: 42
log_time: 2015-04-09 02:55:38
nickname: test
*************************** 2. row ***************************
      id: 95
    rank: 24
log_time: 2015-04-09 05:53:13
nickname: test
2 rows in set (0.00 sec)

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

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> SELECT * FROM t1 WHERE log_date = '2015-04-09'\G
*************************** 1. row ***************************
      id: 95
    rank: 24
log_time: 2015-04-09 05:53:13
nickname: test
log_date: 2015-04-09
*************************** 2. row ***************************
      id: 3423
    rank: 42
log_time: 2015-04-09 02:55:38
nickname: test
log_date: 2015-04-09
2 rows in set (0.00 sec)

執行後結果集和之前的一致。

 

我們來看看查詢計劃,發現很好的利用了idx_log_date索引列。

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才推出了這項類似的功能的原因。

本文永久更新連結地址:

相關文章

Alibaba Cloud 10 Year Anniversary

With You, We are Shaping a Digital World, 2009-2019

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。