Chinaunix首页 | 论坛 | 博客
  • 博客访问: 4149683
  • 博文数量: 240
  • 博客积分: 11504
  • 博客等级: 上将
  • 技术积分: 4277
  • 用 户 组: 普通用户
  • 注册时间: 2006-12-28 14:24
文章分类

全部博文(240)

分类: Mysql/postgreSQL

2015-11-13 11:35:46

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


MySQL 5.7提供了一个新的特性,虚拟列,可以很完美的解决这个问题。
在介绍虚拟列之前,我们来看看在MySQL里面普通索引的范例。
示例表结构:

点击(此处)折叠或打开

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



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

点击(此处)折叠或打开

  1. mysql> select count(*) from t1;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. | 5000 |
  6. +----------+
  7. 1 row in set (0.00 sec)




假设我们来检索2015年4月9号的数据。(结果是有两条记录,id 分别为95和3423。)



点击(此处)折叠或打开

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




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



点击(此处)折叠或打开

  1. mysql> explain SELECT * FROM t1 WHERE DATE(log_time) = '2015-04-09'\G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t1
  6.    partitions: NULL
  7.          type: ALL
  8. possible_keys: NULL
  9.           key: NULL
  10.       key_len: NULL
  11.           ref: NULL
  12.          rows: 5000
  13.      filtered: 100.00
  14.         Extra: Using where
  15. 1 row in set, 1 warning (0.00 sec)




我们发现TYPE是ALL,扫描的函数是5000,也就是说这条语句进行了一个全表扫描。 虽然给字段log_time 加了索引,但是没有用到,那这个时候怎么办?
在MySQL里面一般这样修改:

点击(此处)折叠或打开

  1. mysql> SELECT * FROM t1 WHERE log_time >= '2015-04-09 00:00:00' AND log_time <='2015-04-10 00:00:00'\G
  2. *************************** 1. row ***************************
  3.       id: 3423
  4.     rank: 42
  5. log_time: 2015-04-09 02:55:38
  6. nickname: test
  7. *************************** 2. row ***************************
  8.       id: 95
  9.     rank: 24
  10. log_time: 2015-04-09 05:53:13
  11. nickname: test
  12. 2 rows in set (0.00 sec)




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





点击(此处)折叠或打开

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




可以看到这条修改过的语句很好的利用到了idx_log_time这条索引。


那好,这个是之前在MySQL 5.6以及之前的旧版本解决方法,随着MySQL 5.7的发布,虚拟列的出现让这个问题更加简单。
 现在修改下之前的表结构:



点击(此处)折叠或打开

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




这样,增加了一新列,用来存放date(log_time)这个表达式,并且给他加了一列索引。


那么,之前的语句就变成如下:

点击(此处)折叠或打开

  1. mysql> SELECT * FROM t1 WHERE log_date = '2015-04-09'\G
  2. *************************** 1. row ***************************
  3.       id: 95
  4.     rank: 24
  5. log_time: 2015-04-09 05:53:13
  6. nickname: test
  7. log_date: 2015-04-09
  8. *************************** 2. row ***************************
  9.       id: 3423
  10.     rank: 42
  11. log_time: 2015-04-09 02:55:38
  12. nickname: test
  13. log_date: 2015-04-09
  14. 2 rows in set (0.00 sec)




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


我们来看看查询计划,发现很好的利用了idx_log_date索引列。

点击(此处)折叠或打开

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



通过以上介绍,我们看到虚拟列实现起来相对之前的方法来的容易的多。但是这里我还是得说上几句。
函数索引的用法以及SQL语句虽然写起来简单,但是在大部分场合下,只能说不得已而为之,是一种设计上的缺陷,后期增加了运维人员的运维难度以及繁琐度。这也就是为什么MySQL 直到5.7才推出了这项类似的功能的原因。
阅读(11333) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~