首页 / Mysql / MySQL的id关联和索引使用的实际优化案例

MySQL的id关联和索引使用的实际优化案例

昨晚收到客服MM电话,一用户反馈数据库响应非常慢,手机收到load异常报警,登上主机后发现大量sql执行非常慢,有的执行时间超过了10s
优化点一:

昨晚收到客服MM电话,一用户反馈数据库响应非常慢,手机收到load异常报警,登上主机后发现大量sql执行非常慢,有的执行时间超过了10s
优化点一:

表结构为:

执行计划:

分析该sql的执行计划,由于tran_id是表的主键,所以查询根据主键降序顺序扫描,这样就可以不用排序,
然后在过滤条件price>2.00的记录,看上去这个执行计划貌似非常好,如果查询扫描到了满足条件的10条记录,就会停止扫描;
但是这里有个问题,如果表中有大量的记录是不符合2.00的,意味查询就需要扫描非常多的记录,才能找到符合条件的10条:

可以看到表中有大量的记录不是2.00的,所以这个时候不能在根据主键顺序扫描,在过滤记录;
那么是否需要在price建立一个索引:

从上面price的数据分布可以看出,price的分布相对还是比较集中的,如果在price建立索引,mysql也有可能认为由于需要回表的记录过多,
同时需要额外的排序,而不选择在price上的索引:

MySQL的id关联和索引使用的实际优化案例

可以看到优化器虽然注意到了我们新加的索引,但是最终还是选择了primary来扫描;
所以这个时候我们加上去的索引没有产生效果,数据库负载依然很高,如果强制走price上的索引,效果会这样:

可以看到如果强制走索引,时间已经明显下降了,但是还是有些慢,能不能在快一点?其实我们需要扫描的记录只有10条,但查询在取得这10条记录的时候需要扫描大量无效的记录

MySQL的id关联和索引使用的实际优化案例

怎么降低这个数据:其实只要改写一下sql就可以,我们先从索引中得到满足条件的10个id,在回表进行关联:

可以看到执行时间已经不在秒级别了,和客户电话沟通后,很愿意这样改写sql。

―这里看到是order by tran_id是要额外排序的,索引也可以这样来建立消除排序(tran_id,price)这样可以消除排序,同时可以利用order by desc/asc +limit M,N的优化。

优化点二:

查询为select `session_data`, `session_expires` from `game_session` where session_id=’xxx’出现大量等待情况
同时该表的insert,也有等待的现象;
可以看到这个表结构设计是有些问题的,咨询了客户后,可以改为下面结构:

小结:

  1. 新增自增主键id作为表的主键,这样对插入的性能提升是很好的,同时也降低了表主键的大小;
  2. 将session_data由text改为了varchar(200),咨询了客户后,这个字段可以不用大字段存储,同时有text改为了varchar,就可以冗余到索引中;
  3. 由于查询可以使用覆盖索引来完成,所以将查询的3个字段冗余到索引中,查询通过索引完成,不用回表
本文来自网络,不代表我是攻城狮立场,转载请注明出处:https://www.54gcs.cn/sql/mysql/2200.html

helwo作者

上一篇
下一篇

为您推荐

发表评论

联系我们

联系我们

在线咨询: QQ交谈

邮箱: web@5vo.cn

工作时间:周一至周五,9:00-17:30,节假日休息
关注微信
微信扫一扫关注我们

微信扫一扫关注我们

手机访问
手机扫一扫打开网站

手机扫一扫打开网站

返回顶部
跳至工具栏