FROM `t_mytest_userinfo`
WHERE (`c_is_delete` = 0
AND `c_company_id` = 'e0b5df9cb47646ee8cd97237b838e35e'
AND (`c_emp_name` LIKE '%张三疯%' )
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
