【原创】MariaDB 实现函数索引

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


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

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


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


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


Time: 39.851 ms

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

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=1607.19..1607.20 rows=1 width=12) (actual time=5.514..5.514 rows=1 loops=1)
   -> 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)
         Recheck Cond: (substr((email)::text, ("position"((email)::text, '@'::text) + 1)) = '56.com'::text)
         -> 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)
               Index Cond: (substr((email)::text, ("position"((email)::text, '@'::text) + 1)) = '56.com'::text)
 Total runtime: 5.603 ms
(6 rows)


Time: 6.601 ms


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


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


Time: 5.251 ms
t_girl=

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

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


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


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