MySQL函数索引优化

SQL示例1

原始SQL使用了函数lower导致索引失效，花费时间2s以上，优化之后可以达到0.001s

SELECT
	t0.id,
	t0.email,
	t0.mobile,
	t0.state,
	t0.bw_password,
	t0.bw_salt,
	t0.bwplus_password,
	t0.bwplus_salt,
	t0.app_id,
	t0.last_login_time,
	t0.bind_email_time,
	t0.bind_mobile_time,
	t0.activate_time,
	t0.create_time,
	t0.update_time,
	t1.id 
FROM
	newsso.tb_user t0
	LEFT JOIN newsso.bw_user_level t1 ON t1.user_id = t0.id 
WHERE
	( lower( t0.email ) LIKE 'xxxxx@gmail.com%' ESCAPE '' 
     OR 
     lower( t0.mobile ) LIKE 'xxxxx@gmail.com' ESCAPE '' ) 
	LIMIT 10

优化之后

可以建立函数索引，首先将运算之后的结果变成虚拟列插入到tb_user中，然后对虚拟列建立索引。

 alter table tb_user add column lower_email varchar(256) GENERATED ALWAYS AS (lower(email));
 alter table tb_user add column lower_mobile varchar(32) GENERATED ALWAYS AS (lower(mobile));

 alter table tb_user add index tb_user_function_of_email(lower_email);
 
 alter table tb_user add index tb_user_function_of_mobile(lower_mobile);


explain SELECT
 t0.id,
 t0.email,
 t0.mobile,
 t0.state,
 t0.bw_password,
 t0.bw_salt,
 t0.bwplus_password,
 t0.bwplus_salt,
 t0.app_id,
 t0.last_login_time,
 t0.bind_email_time,
 t0.bind_mobile_time,
 t0.activate_time,
 t0.create_time,
 t0.update_time,
 t1.id 
FROM
 tb_user t0
 LEFT JOIN bw_user_level t1 ON t1.user_id = t0.id 
WHERE
 ( t0.lower_email LIKE 'xxxxx@gmail.com%' ESCAPE '' ) 
 OR ( t0.lower_mobile LIKE 'xxxxx@gmail.com' ESCAPE '' ) 
 LIMIT 10
从原来的2.26s降低到0.001s

SQL示例2

select count(*) from newsso.tb_user t0 where (lower(t0.email) like '0978341138%' escape''  or lower(t0.mobile) like '0978341138' escape'' )

优化之后

EXPLAIN SELECT
 count(*) 
FROM
 tb_user t0 
WHERE
 (
   t0.lower_email  LIKE '0978341138%' ESCAPE '' 
  OR t0.lower_mobile LIKE '0978341138' ESCAPE '' 
 )
 
 alter table tb_user add column lower_email varchar(256) GENERATED ALWAYS AS (lower(email));
 alter table tb_user add column lower_mobile varchar(32) GENERATED ALWAYS AS (lower(mobile));

 alter table tb_user add index tb_user_function_of_email(lower_email);
 
 alter table tb_user add index tb_user_function_of_mobile(lower_mobile);

本地优化之后从原来的1.5s降到0.005s