Chinaunix首页 | 论坛 | 博客
  • 博客访问: 7172655
  • 博文数量: 3857
  • 博客积分: 6409
  • 博客等级: 准将
  • 技术积分: 15948
  • 用 户 组: 普通用户
  • 注册时间: 2008-09-02 16:48
个人简介

迷彩 潜伏 隐蔽 伪装

文章分类

全部博文(3857)

文章存档

2017年(5)

2016年(63)

2015年(927)

2014年(677)

2013年(807)

2012年(1241)

2011年(67)

2010年(7)

2009年(36)

2008年(28)

分类: Mysql/postgreSQL

2014-04-04 10:24:55

我们知道MySQL 暂时不支持函数索引。 目前大部分数据库包括PostgreSQL,Oracle等都支持。 什么是函数索引呢?
函数索引就是说用某固定的函数来对列生成一个基于此函数结果集的索引树。 好处是开发人员写SQL变得随意而且简单了,但是不好的一点也是如此,必须写按照固定条件进行的读取过滤。


在之前呢,如果要实现这样的功能,MySQL 得创建一个新的列,然后用前置触发器来修改此列的值。  现在呢,MariaDB有一个虚拟列的特性可以很方便的来实现这个目的。
先来看下在PostgreSQL中的表结构

点击(此处)折叠或打开

  1. t_girl=# \d email_list;
  2.              Table "public.email_list"
  3.   Column | Type | Modifiers
  4. ----------+-----------------------------+-----------
  5.  id | integer |
  6.  email | character varying(200) |
  7.  log_time | timestamp without time zone |
  8. Indexes:
  9.     "idx_email_suffix" btree (substr(email::text, "position"(email::text, '@'::text) + 1))


  10.   这张表的EMAIL列属性上有一个函数索引,目的是来查找次EMAIL属性是属于哪家提供商,比如163,GMAIL等等。


  11. 我们给张表产生了20W行记录。
  12. t_girl=# select count(*) from email_list;
  13.  count
  14. --------
  15.  200000
  16. (1 row)


  17. Time: 39.851 ms



现在来进行对应的查询。 如果不严格按照这个函数的创建规范,查询就不走索引,所以一定要严格来写SQL。
  

点击(此处)折叠或打开

  1. QUERY PLAN
  2. --------------------------------------------------------------------------------------------------------------------------------------
  3.  Aggregate (cost=1607.19..1607.20 rows=1 width=12) (actual time=5.514..5.514 rows=1 loops=1)
  4.    -> Bitmap Heap Scan on email_list (cost=48.29..1602.08 rows=2047 width=12) (actual time=1.126..4.806 rows=1960 loops=1)
  5.          Recheck Cond: (substr((email)::text, ("position"((email)::text, '@'::text) + 1)) = '56.com'::text)
  6.          -> Bitmap Index Scan on idx_email_suffix (cost=0.00..47.78 rows=2047 width=0) (actual time=0.802..0.802 rows=1960 loops=1)
  7.                Index Cond: (substr((email)::text, ("position"((email)::text, '@'::text) + 1)) = '56.com'::text)
  8.  Total runtime: 5.603 ms
  9. (6 rows)


  10. Time: 6.601 ms


  11. 从查询分析计划中看到,走这个函数索引,扫描了大概2K行记录,生成结果集1960行。


  12. t_girl=# select count(email) as num from email_list where substr(email,position('@' in email)+1)='56.com';
  13.  num
  14. ------
  15.  1960
  16. (1 row)


  17. Time: 5.251 ms
  18. t_girl=


接下来,我们看看在MariaDB中如何来实现对应的功能。
表结构如下:

点击(此处)折叠或打开

  1. MariaDB [t_girl]> show create table email_list;
  2. +------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | email_list | CREATE TABLE `email_list` (
  6.   `id` int(11) DEFAULT NULL,
  7.   `email` varchar(200) DEFAULT NULL,
  8.   `log_time` datetime(6) DEFAULT NULL,
  9.   `email_suffix` varchar(100) AS (substr(email,position('@' in email)+1)) PERSISTENT,
  10.   KEY `idx_email_suffix` (`email_suffix`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
  12. +------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  13. 1 row in set (0.01 sec)


  14. 这里我们用到了MariaDB的虚拟列,并且对虚拟列指定persistent属性,这样就能当成真实的属性来看待了。
  15. 行,下来我们利用这个虚拟列来进行查询,不过这样反而简单点,查询语句不需要那么严格了,直接跟普通的语句一样。
  16. MariaDB [t_girl]> explain select count(email) from email_list where email_suffix = '56.com';
  17. +------+-------------+------------+------+------------------+------------------+---------+-------+------+-----------------------+
  18. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  19. +------+-------------+------------+------+------------------+------------------+---------+-------+------+-----------------------+
  20. | 1 | SIMPLE | email_list | ref | idx_email_suffix | idx_email_suffix | 103 | const | 1959 | Using index condition |
  21. +------+-------------+------------+------+------------------+------------------+---------+-------+------+-----------------------+
  22. 1 row in set (0.02 sec)


  23. 查询速度当然也就非常快了。
  24. MariaDB [t_girl]> select count(email) from email_list where email_suffix = '56.com';
  25. +--------------+
  26. | count(email) |
  27. +--------------+
  28. | 1960 |
  29. +--------------+
  30. 1 row in set (0.02 sec)



阅读(431) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~