sql优化技巧

优化sql
select * from order e where type in (?,?) and (e.phone = ? or (e.mobile = ? and type !=?))
and
DATEDIFF(now(), e.create_time) < 7 order by create_time

优化思路
添加单个索引
CREATE INDEX idx_phone USING BTREE ON order (phone);

CREATE INDEX idx_mobile USING BTREE ON order (mobile);
为什么不加组合索引
组合索引的负担太重 且 索引字段的顺序要一致 才能命中索引，所以建议分开建索引。如果遇到需要指定优化某条sql可以建立组合索引
DATEDIFF(now(), e.create_time) < 7
修改下 改成 e.create_time > DATE_SUB(NOW(), INTERVAL 7 DAY)，尽量不要在字段上做函数操作
select * from order e where type in (?,?) and (e.phone =? or (e.mobile =? and type!=?)) and e.create_time > DATE_SUB(NOW(), INTERVAL 7 DAY) order by create_time