没内鬼,来点干货!SQL优化和诊断
SQL优化与诊断
Explain诊断
Explain各参数的含义如下:
列名 | 说明 |
---|---|
id | 执行编号,标识select所属的行。如果在语句中没有子查询或关联查询,只有唯一的select,每行都将显示1.否则,内层的select语句一般会顺序编号,对应于其在原始语句中的位置 |
select_type | 显示本行是简单或复杂select,如果查询有任何复杂的子查询,则最外层标记为PRIMARY(DERIVED、UNION、UNION RESUIT) |
table | 访问引用哪个表(引用某个查询,如“derived3”) |
type | 数据访问/读取操作类型(All、index、range、ref、eq_ref、const/system、NULL) |
possible_key | 揭示哪一些索引可能有利于高效的查找 |
key | 显示mysql实际决定采用哪个索引来优化查询 |
key_len | 显示mysql在索引里使用的字节数 |
ref | 显示了之前的表在key列记录的索引中查找值所用的列或常量 |
rows | 为了找到所需要的行而需要读取的行数,估算值 |
Extra | 额外信息,如using index、filesort等 |
select_type 常见类型及其含义
- SIMPLE:不包含子查询或者 UNION 操作的查询
- PRIMARY:查询中如果包含任何子查询,那么最外层的查询则被标记为 PRIMARY
- SUBQUERY:子查询中第一个 SELECT
- DEPENDENT SUBQUERY:子查询中的第一个 SELECT,取决于外部查询
- UNION:UNION 操作的第二个或者之后的查询
- DEPENDENT UNION:UNION 操作的第二个或者之后的查询,取决于外部查询
- UNION RESULT:UNION 产生的结果集
- DERIVED:出现在 FROM 字句中的子查询
type常见类型及其含义
- system:这是 const 类型的一个特例,只会出现在待查询的表只有一行数据的情况下
consts
:常出现在主键或唯一索引与常量值进行比较的场景下,此时查询性能是最优的- eq_ref:当连接使用的是完整的索引并且是 PRIMARY KEY 或 UNIQUE NOT NULL INDEX 时使用它
ref
:当连接使用的是前缀索引或连接条件不是 PRIMARY KEY 或 UNIQUE INDEX 时则使用它- ref_or_null:类似于 ref 类型的查询,但是附加了对 NULL 值列的查询
- index_merge:该联接类型表示使用了索引进行合并优化
range
:使用索引进行范围扫描,常见于 between、> 、< 这样的查询条件index
:索引连接类型与 ALL 相同,只是扫描的是索引树,通常出现在索引是该查询的覆盖索引的情况- ALL:全表扫描,效率最差的查找方式
阿里编码规范要求:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好
key列
实际在查询中是否使用到索引的标志字段
如何查看Mysql优化器优化之后的SQL
# 仅在服务器环境下或通过Navicat进入命令列界面 explain extended SELECT * FROM `student` where `name` = 1 and `age` = 1; # 再执行 show warnings; # 结果如下: /* select#1 */ select `mytest`.`student`.`age` AS `age`,`mytest`.`student`.`name` AS `name`,`mytest`.`student`.`year` AS `year` from `mytest`.`student` where ((`mytest`.`student`.`age` = 1) and (`mytest`.`student`.`name` = 1)) 复制代码
为什么要做这个事呢?我们知道Mysql有一个最左匹配原则,那么如果我的索引建的是age,name,那我以name,age这样的顺序去查询能否使用到索引呢?实际上是可以的,就是因为Mysql查询优化器可以帮助我们自动对SQL的执行顺序等进行优化,以选取代价最低的方式进行查询(注意是代价最低,不是时间最短)
SQL优化
超大分页场景解决方案
如表中数据需要进行深度分页,如何提高效率?在阿里出品的Java编程规范中写道:
利用延迟关联或者子查询优化超多分页场景
说明:MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写
# 反例(耗时129.570s) select * from task_result LIMIT 20000000, 10; # 正例(耗时5.114s) SELECT a.* FROM task_result a, (select id from task_result LIMIT 20000000, 10) b where a.id = b.id; # 说明 task_result表为生产环境的一个表,总数据量为3400万,id为主键,偏移量达到2000万 复制代码
获取一条数据时的Limit 1
如果数据表的情况已知,某个业务需要获取符合某个Where条件下的一条数据,注意使用Limit
说明:在很多情况下我们已知数据仅存在一条,此时我们应该告知数据库只用查一条,否则将会转化为全表扫描
# 反例(耗时2424.612s) select * from task_result where unique_key = 'ebbf420b65d95573db7669f21fa3be3e_861414030800727_48'; # 正例(耗时1.036s) select * from task_result where unique_key = 'ebbf420b65d95573db7669f21fa3be3e_861414030800727_48' LIMIT 1; # 说明 task_result表为生产环境的一个表,总数据量为3400万,where条件非索引字段,数据所在行为第19486条记录 复制代码
批量插入
# 反例 INSERT into person(name,age) values('A',24) INSERT into person(name,age) values('B',24) INSERT into person(name,age) values('C',24) # 正例 INSERT into person(name,age) values('A',24),('B',24),('C',24); # 说明 比较常规,就不多做说明了 复制代码
like语句的优化
like语句一般业务要求都是 '%关键字%'
这种形式,但是依然要思考能否考虑使用右模糊的方式去替代产品的要求,其中阿里的编码规范提到:
页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决
# 反例(耗时78.843s) EXPLAIN select * from task_result where taskid LIKE '%tt600e6b601677b5cbfe516a013b8e46%' LIMIT 1; # 正例(耗时0.986s) select * from task_result where taskid LIKE 'tt600e6b601677b5cbfe516a013b8e46%' LIMIT 1 ########################################################################## # 对正例的Explain 1 SIMPLE task_result range adapt_id adapt_id 98 99 100.00 Using index condition # 对反例的Explain 1 SIMPLE task_result ALL 33628554 11.11 Using where # 说明 task_result表为生产环境的一个表,总数据量为3400万,taskid是一个普通索引列,可见%%这种匹配方式完全无法使用索引,从而进行全表扫描导致效率极低,而正例通过索引查找数据只需要扫描99条数据即可 复制代码
避免SQL中对where字段进行函数转换或表达式计算
# 反例 select * from task_result where id + 1 = 15551; # 正例 select * from task_result where id = 15550; ########################################################################## # 对正例的Explain 1 SIMPLE task_result const PRIMARY PRIMARY 8 const 1 100.00 # 对反例的Explain 1 SIMPLE task_result ALL 33631512 100.00 Using where # 说明 其实在知道了有SQL优化器之后,我个人感觉这种普通的表达式转换应该可以提前进行处理再进行查询,这样一来就可以用到索引了,但是问题又来了,如果mysql优化器可以提前计算出结果,那么写sql语句的人也一定可以提前计算出结果,所以矛盾点在这个地方,导致5.7版本以前的此种情况都无法使用索引吧,未来可能会对其进行优化 复制代码
使用 ISNULL()来判断是否为 NULL 值
说明:NULL 与任何值的直接比较都为 NULL
# 1) NULL<>NULL 的返回结果是 NULL,而不是 false。 # 2) NULL=NULL 的返回结果是 NULL,而不是 true。 # 3) NULL<>1 的返回结果是 NULL,而不是 true。 复制代码
多表查询
我所在的公司基本禁止了多表查询,那如果必须使用到的话,我们可以一起参考一下阿里的编码规范
Eg:超过三个表禁止 join。需要 join 的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引
明明有索引为什么还走全表扫描
之前回答一些面试问题的时候,对某一个点的理解出现了偏差,即我认为只要查询的列有索引则一定会使用索引去Push数据
然而实际上不仅仅是这样,真正应该是:针对查询的数据行占总数据量过多时会转化成全表查询
那么这个过多指代的是多少呢?
我的测试结果是50%,但个人认为MySQL优化器不会完全纠结于行数区分是否全表,而是有很多其他因素综合考虑发现全表扫描的效率更高等等,所以充分认识到该问题即可
count(*) 还是 count(id)
阿里的Java编码规范中有以下内容:
【强制】不要使用 count(列名) 或 count(常量) 来替代 count(*)
count(*) 是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。
说明:count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行
字段类型不同导致索引失效
阿里的Java编码规范中有以下内容:
【推荐】防止因字段类型不同造成的隐式转换,导致索引失效
实际上数据库在查询的时候会作一层隐式的转换,比如 varchar 类型字段通过 数字去查询
# 正例 EXPLAIN SELECT * FROM `user_coll` where pid = '1'; type:ref ref:const rows:1 Extra:Using index condition # 反例 EXPLAIN SELECT * FROM `user_coll` where pid = 1; type:index ref:NULL rows:3(总记录数) Extra:Using where; Using index # 说明 pid字段有相应索引,且格式为varchar 复制代码
关于
感谢以下博文及其作者:
Tips
自建数据表进行测试
CREATE TABLE `student` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(255) NOT NULL, `class` varchar(255) DEFAULT NULL, `page` bigint(20) DEFAULT NULL, `status` tinyint(3) unsigned NOT NULL COMMENT '状态:0 正常,1 冻结,2 删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 复制代码
插入数据
DELIMITER ;; CREATE PROCEDURE insertData() BEGIN declare i int; set i = 1 ; WHILE (i < 1000000) DO INSERT INTO student(`name`,class,`page`,`status`) VALUES(CONCAT('class_', i), CONCAT('class_', i), i, (SELECT FLOOR(RAND() * 2))); set i = i + 1; END WHILE; commit; END;; CALL insertData();
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
Python 编程语言的核心是什么?
云栖号资讯:【点击查看更多行业资讯】在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来! 为什么要问这个问题? 我想要用Python实现WebAssembly,这并不是什么秘密。这不仅可以让Python进入浏览器,而且由于iOS和Android都支持将JavaScript作为应用的一部分运行,因此Python也可以进入移动开发。想到这些我就觉得兴奋。 但是每当想到创建一个新Python实现的艰巨任务时,我就会不断地问自己: “Python究竟是什么?” 我们使用CPython已经很长时间了,以至于我怀疑我们大多数人都认为“ Python == CPython”。PyPy试图将兼容做到极致,所以他们打算实现CPython的实现细节。基本上,我所知道的大多数Python实现都会为通过CPython的测试套件而努力,并尽可能与CPython兼容。这就有点可怕了。 CPython实现的Python非常动态,它公开了许多东西,只有当你以某种方式使用解释器实现Python才有意义。例如,PyPy有一个基本的解释器使用JIT,但是你可以通过Python中的很多东西来迫使PyPy关闭JIT并...
- 下一篇
Ignite数据加载入门:功能介绍
本文是Ignite数据加载入门系列文章的第一篇,会介绍开发人员、分析人员和运维人员可以使用的Ignite数据加载功能,在后面的两篇文章中,会介绍Ignite进行数据加载的两种主要技术:CacheStore和DateStreamer。 数据加载功能和Ignite的部署模式强相关,每个加载方式都有其优点和成本,这也使得用户在不同的场景会做出不同的选择。 Ignite的数据加载功能 Ignite提供了若干种功能,用于从外部数据源加载数据,下面会逐一介绍。 CacheStore CacheStore是数据网格(IMDG)方案中用于与外部或第三方持久化存储进行同步的主要工具。Ignite的CacheStore接口实现了缓存的通读和通写功能,另外CacheStore接口还具有缓存加载方法,可用于在初始化时热加载缓存(不仅限于在启动时执行)。API-Cache-CacheStore-DataStore关系的图形表示如下所示,突出显示了通读、通写和缓存加载功能: Ignite自带了很多CacheStore接口的实现,可将Ignite缓存与外部数据源(如RDBMS、Hibernate、NoSQL系统(如...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- Linux系统CentOS6、CentOS7手动修改IP地址
- 2048小游戏-低调大师作品
- CentOS8安装Docker,最新的服务器搭配容器使用
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- Windows10,CentOS7,CentOS8安装Nodejs环境
- CentOS8安装MyCat,轻松搞定数据库的读写分离、垂直分库、水平分库
- SpringBoot2更换Tomcat为Jetty,小型站点的福音
- 设置Eclipse缩进为4个空格,增强代码规范
- CentOS7,8上快速安装Gitea,搭建Git服务器
- Windows10,CentOS7,CentOS8安装MongoDB4.0.16