我们知道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 msquery 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 mst_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)