MariaDB 实现函数索引

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))

t_girl=# select count(*) from email_list;
 count  
--------
 200000
(1 row)


Time: 39.851 ms

                                                              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

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 [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 [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)

MariaDB 实现函数索引,布布扣,bubuko.com

MariaDB 实现函数索引

原文：http://blog.csdn.net/yueliangdao0608/article/details/22884515