Mysql索引优化实例1

database►MySQL , 评论

目录

前言

PS:本文只讲解了一个实例,中间用到了两个mysql的索引相关概念,覆盖索引和最左前缀索引,需要读者自行学习一下.

这是工作中遇到的一个实际案例,主要的背景如下:

  1. 有一个很复杂的业务接口,主要需要查询一个6000w数据的表6次.
  2. 不想分表,太麻烦了,由此带来的工作量太大了.
  3. 不能做缓存,数据要求较高的实时性,即3-5分钟内的变化也要体现出来.
  4. 但是不会极限的在乎用户体验,比如接口1-2s是能接受的,不会要你优化到10ms(但是目前接口是20s).

所以最后的决定是,不使用缓存或者lucene等花里胡哨的东西,就使用mysql,优化一下索引,争取到做的单个查询300ms内,这样加上一些带代码层面的优化,接口能接近1s左右.

当前情况介绍

数据表的字段较多,主要使用的是一下几个:

1
2
3
4
5
name -- 名字
sex -- 性别,1,0两种取值
birth -- 出生日期 
last_active_time 最后活跃日期
city -- 用户当前城市

上面的所有字段都会出现在where语句中.

当前有的索引如下:

1
2
3
4
primary_key(id)
city(city)
last(last_active_time)
city_sex_last(last_active_time)

当前主要难搞的几个查询如下:

1
2
3
4
5
6
// 根据用户的城市和性别拿到用户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没有使用第二个索引.

所以我们优化的目标就是在不影响第一个语句的情况下,让第二个和第三个语句尽可能的命中索引.

  1. 首先第二个的语句是完全可以拆开的,拆成and sex = 0和and sex= 1 and last_active_time >= 20190422两个语句去执行,那么前面拆出来的那个可以命中索引.
  2. 想要拆分后的第二个语句命中索引,我们需要city,sex,last_active_time的联合索引.
  3. 对第三个语句来说,我们需要一个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 是为了让第一条语句命中索引以完成覆盖索引).

这样优化之后,相应的我们的第三条语句需要改动,需要变化成下面这样:

1
2
3
4


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.


完。



ChangeLog

2019-05-29 完成

**以上皆为个人所思所得，如有错误欢迎评论区指正。**

欢迎转载，烦请署名并保留原文链接。

联系邮箱：huyanshi2580@gmail.com

更多学习笔记见个人博客——>呼延十

呼延十Developer

个人简介。