我们知道MySQL 暂时不支持函数索引。 目前大部分数据库包括PostgreSQL,Oracle等都支持。 什么是函数索引呢？ t_girl=# \d email_list; Table public.email_list Column | Type | Modifiers ----------+-----------------------------+----------- id | intege
我们知道MySQL 暂时不支持函数索引。 目前大部分数据库包括PostgreSQL,Oracle等都支持。 什么是函数索引呢？
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)
