MySQL的id关联和索引使用的实际优化案例
作者:罗龙九 发布时间:2024-01-26 03:43:30
昨晚收到客服MM电话,一用户反馈数据库响应非常慢,手机收到load异常报警,登上主机后发现大量sql执行非常慢,有的执行时间超过了10s
优化点一:
SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10;
表结构为:
CREATE TABLE `game_shares_buy_list` (
`tran_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`………..'
PRIMARY KEY (`tran_id`),
KEY `ind_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=3144200 DEFAULT CHARSET=utf8;
执行计划:
root@127.0.0.1 : sitevipdb 09:10:22> explain SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10;
+—-+————-+———————-+——-+—————+———+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————-+——-+—————+———+———+——+——+————-+
| 1 | SIMPLE | game_shares_buy_list | index | NULL | PRIMARY | 4 | NULL | 10 | Using where |
+—-+————-+———————-+——-+—————+———+———+——+——+————-+
1 row in set (0.00 sec)
分析该sql的执行计划,由于tran_id是表的主键,所以查询根据主键降序顺序扫描,这样就可以不用排序,
然后在过滤条件price>2.00的记录,看上去这个执行计划貌似非常好,如果查询扫描到了满足条件的10条记录,就会停止扫描;
但是这里有个问题,如果表中有大量的记录是不符合2.00的,意味查询就需要扫描非常多的记录,才能找到符合条件的10条:
root@127.0.0.1 : sitevipdb 09:17:23> select price,count(*) as cnt from `game_shares_buy_list` group by price order by cnt desc limit 10;
+——-+——-+
| price | cnt |
+——-+——-+
| 1.75 | 39101 |
| 1.68 | 38477 |
| 1.71 | 34869 |
| 1.66 | 34849 |
| 1.72 | 34718 |
| 1.70 | 33996 |
| 1.76 | 32527 |
| 1.69 | 27189 |
| 1.61 | 25694 |
| 1.25 | 25450 |
可以看到表中有大量的记录不是2.00的,所以这个时候不能在根据主键顺序扫描,在过滤记录;
那么是否需要在price建立一个索引:
root@127.0.0.1 : sitevipdb 09:09:01> select count(*) from `game_shares_buy_list` where price>'2′;
+———-+
| count(*) |
+———-+
| 4087 |
+———-+
root@127.0.0.1 : sitevipdb 09:17:31> select count(*) from `game_shares_buy_list` ;
+———-+
| count(*) |
+———-+
| 1572100 |
从上面price的数据分布可以看出,price的分布相对还是比较集中的,如果在price建立索引,mysql也有可能认为由于需要回表的记录过多,
同时需要额外的排序,而不选择在price上的索引:
root@127.0.0.1 : sitevipdb 09:24:53> alter table game_shares_buy_list add index ind_game_shares_buy_list_price(price);
Query OK, 0 rows affected (5.79 sec)
可以看到优化器虽然注意到了我们新加的索引,但是最终还是选择了primary来扫描;
所以这个时候我们加上去的索引没有产生效果,数据库负载依然很高,如果强制走price上的索引,效果会这样:
root@127.0.0.1 : sitevipdb 09:35:38> SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10;
。。。。。
10 rows in set (7.06 sec)
root@127.0.0.1 : sitevipdb 09:36:00> SELECT * FROM `sitevipdb`.`game_shares_buy_list` force index(ind_game_shares_buy_list_price) WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10;
。。。。
10 rows in set (1.01 sec)
可以看到如果强制走索引,时间已经明显下降了,但是还是有些慢,能不能在快一点?其实我们需要扫描的记录只有10条,但查询在取得这10条记录的时候需要扫描大量无效的记录
怎么降低这个数据:其实只要改写一下sql就可以,我们先从索引中得到满足条件的10个id,在回表进行关联:
root@127.0.0.1 : sitevipdb 09:44:45> select * from game_shares_buy_list t1,
-> ( SELECT tran_id FROM sitevipdb.game_shares_buy_list WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10) t2
-> where t1.tran_id=t2.tran_id;
10 rows in set (0.00 sec)
可以看到执行时间已经不在秒级别了,和客户电话沟通后,很愿意这样改写sql。
—这里看到是order by tran_id是要额外排序的,索引也可以这样来建立消除排序(tran_id,price)这样可以消除排序,同时可以利用order by desc/asc +limit M,N的优化。
优化点二:
CREATE TABLE `game_session` (
`session_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ,
`session_expires` int(10) unsigned NOT NULL DEFAULT '0′,
`client_ip` varchar(16) DEFAULT NULL,
`session_data` text,
…………………….
PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
查询为select `session_data`, `session_expires` from `game_session` where session_id='xxx'出现大量等待情况
同时该表的insert,也有等待的现象;
可以看到这个表结构设计是有些问题的,咨询了客户后,可以改为下面结构:
CREATE TABLE `game_session` (
id int auto_increment,
`session_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ,
`session_expires` int(10) unsigned NOT NULL DEFAULT '0′,
`client_ip` varchar(16) DEFAULT NULL,
`session_data` varchar(200),
PRIMARY KEY (id),
key ind_session_id(session_id,session_data, session_expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
小结:
新增自增主键id作为表的主键,这样对插入的性能提升是很好的,同时也降低了表主键的大小;
将session_data由text改为了varchar(200),咨询了客户后,这个字段可以不用大字段存储,同时有text改为了varchar,就可以冗余到索引中;
由于查询可以使用覆盖索引来完成,所以将查询的3个字段冗余到索引中,查询通过索引完成,不用回表
猜你喜欢
- 简述Matplotlib是一个基于python的2D画图库,能够用python脚本方便的画出折线图,直方图,功率谱图,散点图等常用图表,而且
- 开发客户端客户是持有TPCoins并从网络上的其他供应商处交换商品/服务的客户,包括他自己的.我们应该为此目的定义 Client&
- 本地一个长期更新的项目,git log突然报错:xxx@yyy:~/android/project/kernel/.git$ git log
- Laravel 中间件提供了一种方便的机制来过滤进入应用的 HTTP 请求。例如,Laravel 内置了一个中间件来验证用户的身份认证。如果
- 自己用python写了一个签到脚本,经过测试已经可以成功打卡,于是研究了一下windows定时运行程序1. 创建定时任务1.1 计划任务打开
- 本文主要给大家介绍了关于python实现循环购物车功能的相关内容,分享出来供大家参考学习,下面来一起看看详细的介绍:示例代码# -*- co
- 本文实例讲述了Python实现的圆形绘制。分享给大家供大家参考,具体如下:# -*- coding:utf-8 -*-#! python3i
- 一.打包Flask项目1.1自己写个Flask2.2 下载pyinstallerpip install pyinstaller可选参数示例说
- 基本设置class Map3D( # 初始化配置项,参考 `global_options.InitOpts` &n
- Pandas的apply函数概念(图解)实例1:怎样对数值按分组的归一化实例2:怎样取每个分组的TOPN数据来源:https://blog.
- 近期在做爬虫时有时会遇到网站只提供pdf的情况,这样就不能使用scrapy直接抓取页面内容了,只能通过解析PDF的方式处理,目前的解决方案大
- # -*- coding: UTF-8 -*-from __future__ import unicode_literalsimport I
- 一、绪论在使用python开发过程中经常会使用到第三方库。因此就涉及到了如何安装、复制移动。二、安装方式第三方库的安装方式1、python自
- 1、何为ansible-playbookplaybook是ansible用于配置,部署,和管理被控节点的剧本,通过playbook的详细描述
- mktime()方法是localtime()反函数。它的参数是struct_time或全9元组,它返回一个浮点数,为了兼容时ti
- 完整的示例代码如下: 代码如下:<%@LANGUAGE="JAVASCRIPT" CODEPAGE="6
- 在Python中使用json的时候,主要也就是使用json模块,json是以一种良好的格式来进行数据的交互,从而在很多时候,可以使用json
- 1. glob文件名模式匹配尽管glob API很小,但这个模块的功能却很强大。只要程序需要查找文件系统中名字与某个模式匹配的一组文件,就可
- QWidget基本介绍基础窗口控件QWidget类是所有用户界面对象的基类,所有的窗口或者控件都直接或者间接的继承自QWidget类。窗口坐
- 前言之前做了一个校园交友的APP,其中一个逻辑是通过用户的教务系统来确认用户是一名在校大学生,基本的想法是通过用户的账号和密码,用爬虫的方法