PS:本文只讲解了一个实例,中间用到了两个mysql的索引相关概念,覆盖索引和最左前缀索引,需要读者自行学习一下.
这是工作中遇到的一个实际案例,主要的背景如下:
所以最后的决定是,不使用缓存或者lucene等花里胡哨的东西,就使用mysql,优化一下索引,争取到做的单个查询300ms内,这样加上一些带代码层面的优化,接口能接近1s左右.
数据表的字段较多,主要使用的是一下几个:
name -- 名字
sex -- 性别,1,0两种取值
birth -- 出生日期
last_active_time 最后活跃日期
city -- 用户当前城市
上面的所有字段都会出现在where语句中.
当前有的索引如下:
primary_key(id)
city(city)
last(last_active_time)
city_sex_last(last_active_time)
当前主要难搞的几个查询如下:
// 根据用户的城市和性别拿到用户id和名字
SELECT id, name FROM user_table WHERE city IN ('北京','上海') AND sex = 0
// 根据城市和性别计数,计数条件为,男生,或者女生且在某个时间之后活跃过
select COUNT(1) FROM user_table WHERE city IN ('北京') AND (sex = 0 OR (sex = 1 AND last_active_time >= 20190422))
// 根据用户的城市和出声日期计数
SELECT COUNT(1) FROM user_table WHERE city IN ('北京') AND birth >= 20190422
粗略统计,上述三个语句的执行时间分别在0.5s,2s,1.5s
.
使用explain
命令分析执行计划,第一个sql的索引使用已经OK,where语句全部命中了索引,且查询字段是完全覆盖了索引的.
分析第二个语句,使用了索引,但是仅使用了联合索引的第一个,因为不半部分是个表达式,无法使用索引.
第三个语句,虽然存在两个对应字段的独立索引,但是根据执行计划显示,mysql没有使用第二个索引.
所以我们优化的目标就是在不影响第一个语句的情况下,让第二个和第三个语句尽可能的命中索引.
and sex = 0
和and sex= 1 and last_active_time >= 20190422
两个语句去执行,那么前面拆出来的那个可以命中索引.city,sex,last_active_time
的联合索引.city,birth
的联合索引.到此我们可以完成第一阶段的优化,将上面分析的两个联合索引建如就OK.
但是索引也是有代价的,我们的city存了三份了,这台冗余了想办法搞一下.
可以发现,在数据中,sex只有两种,因此我们可以用sex in (0,1)
来替代无条件.
同时last_active_time
是永远大于birth
的,因此在查询第三个语句时,我们加入一个last_active_time > birth
的条件是完全不影响查询结果,甚至会起到提前删选的作用的.
所以我们可以将索引再优化成city,sex,last_active_time,birth,name
.(name 是为了让第一条语句命中索引以完成覆盖索引).
这样优化之后,相应的我们的第三条语句需要改动,需要变化成下面这样:
SELECT COUNT(1) FROM user_table WHERE city IN ('北京') and sex in (0,1) and last_active_time > 20190422 AND birth >= 20190422
这条语句与原来的语句在查询结果上没有任何区别,却可以完全的命中索引,查询速度大大提升.
完成优化之后,三条语句的执行平均时间为:0.5s,0.2s,0.1s
.
完。
2019-05-29 完成 以上皆为个人所思所得,如有错误欢迎评论区指正。
欢迎转载,烦请署名并保留原文链接。
联系邮箱:huyanshi2580@gmail.com
更多学习笔记见个人博客——>呼延十
var gitment = new Gitment({ id: 'Mysql索引优化实例1', // 可选。默认为 location.href owner: 'hublanker', repo: 'blog', oauth: { client_id: '2297651c181f632a31db', client_secret: 'a62f60d8da404586acc965a2ba6a6da9f053703b', }, }) gitment.render('container')