Heim > Datenbank > MySQL-Tutorial > 【原创】MySQL5.7虚拟列实现表达式索引

【原创】MySQL5.7虚拟列实现表达式索引

WBOY
Freigeben: 2016-06-07 14:54:37
Original
1536 Leute haben es durchsucht

MySQL自古以来就不提供函数索引这么复杂的功能。那怎么在MySQL里面实现这样的功能呢? 我们先来看看函数索引的概念。函数索引,也可称为表达式索引,也就是基于字段以特定函数(表达式)建立索引来提升查询性能之需。函数索引的优势在于更加精确的获取所需要

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);
Nach dem Login kopieren


示例表数据量,这里我增加了5000条记录:

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


假设我们来检索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)
Nach dem Login kopieren

下来我们看看这条语句的查询计划。

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)
Nach dem Login kopieren


我们发现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)
Nach dem Login kopieren


通过查询结果,发现结果集一致,那再来看看查询计划


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)
Nach dem Login kopieren


可以看到这条修改过的语句很好的利用到了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);
Nach dem Login kopieren

这样,增加了一新列,用来存放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)
Nach dem Login kopieren

执行后结果集和之前的一致。


我们来看看查询计划,发现很好的利用了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)
Nach dem Login kopieren


通过以上介绍,我们看到虚拟列实现起来相对之前的方法来的容易的多。但是这里笔者还是得说上几句。

函数索引的用法以及SQL语句虽然写起来简单,但是在大部分场合下,只能说不得已而为之,是一种设计上的缺陷,后期增加了运维人员的运维难度以及繁琐度。这也就是为什么MySQL 直到5.7才推出了这项类似的功能的原因。


Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage