like语句(全/右)模糊查询优化_4699096的技术博客_

2、优化前

有个姓名列，原来查询是下面这样得：
`c_emp_name` LIKE '%张三疯%' 
1)在表里新增一列c_emp_name_re，存放姓名反转之后得值，比如 '张三' 反转后 '三张';
2)对历史数据生成反转值
UPDATE t_mytest_userinfo SET c_emp_name_re=REVERSE(c_emp_name) 
备注: reverse()函数可以反转字符串
3)然后分别在2个姓名列上建立索引
ALTER TABLE `t_mytest_userinfo`
ADD INDEX idx_ename_com (c_emp_name,c_company_id),
ADD INDEX idx_enamer_com (c_emp_name_re,c_company_id)
4)改写SQL条件
`c_emp_name` LIKE '张三疯%' OR `c_emp_name_re` LIKE '疯三张%'
1)优化前
SELECT COUNT('*') AS `__count` 
FROM `t_mytest_userinfo` 
WHERE (`c_is_delete` = 0 
AND `c_company_id` = 'e0b5df9cb47646ee8cd97237b838e35e' 
AND (`c_emp_name` LIKE '%张三疯%' )
执行计划:
    id  select_type  table              type    possible_keys      key                key_len  ref            rows  Extra                               
------  -----------  -----------------  ------  -----------------  -----------------  -------  -----------  ------  ------------------------------------
     1  SIMPLE       t_mytest_userinfo  ref     ix_delete_company  ix_delete_company  99       const,const  517170  Using index condition; Using where  
执行耗时   : 14.776 sec
传送时间   : 0.001 sec
总耗时     : 14.777 sec
2)优化后:
SELECT COUNT('*') AS `__count` 
FROM `t_mytest_userinfo` 
WHERE (`c_is_delete` = 0 
AND `c_company_id` = 'e0b5df9cb47646ee8cd97237b838e35e' 
AND (`c_emp_name` LIKE '张三疯%' OR `c_emp_name_re` LIKE '疯三张%') )
执行计划:
    id  select_type  table              type         possible_keys                                   key                           key_len  ref       rows  Extra                                                        
------  -----------  -----------------  -----------  ----------------------------------------------  ----------------------------  -------  ------  ------  -------------------------------------------------------------
     1  SIMPLE       t_mytest_userinfo  index_merge  ix_delete_company,idx_ename_com,idx_enamer_com  idx_ename_com,idx_enamer_com  138,138  (NULL)       8  Using sort_union(idx_ename_com,idx_enamer_com); Using where  
执行耗时   : 0.007 sec
传送时间   : 0.003 sec
总耗时     : 0.011 sec