脚本宝典收集整理的这篇文章主要介绍了SQL优化技巧,脚本宝典觉得挺不错的,现在分享给大家,也给大家做个参考。
原文链接:https://mp.weixin.QQ.COM/s/iZ07hCLRh_-4rBuoqEdaBw
一定要记得创建索引,创建索引,创建索引。
重要的事说三遍!
执行没有索引的 SQL 语句,肯定要走全表扫描,慢是肯定的。
这种查询毫无疑问是一个慢 SQL 查询。
那么问题来了,是不是要收集所有的 where 查询条件,然后针对所有的组合都创建索引呢?
答案肯定是否定的。
MySQL 为了提升数据查询速率,采用 B+ 树结构,通过空间换时间
设计思想。另外每次对表数据做更新操作时,都要调整对应的 索引树
,执行效率肯定会受影响。
本着二八原则
,互联网请求读多写少
的特点,我们一定要找到一个平衡点。
阿里巴巴的开发者手册建议,单表索引数量控制在 5 个以内,组合索引字段数不允许超过 5 个。
其他建议:
不要以为有了索引,就万事大吉。
殊不知,索引失效
也是慢查询的主要原因之一。
常见的索引失效的场景有哪些?
MySQL 的存储引擎分为两大类:MyISam 和 InnoDB。
MyISAM 支持表锁;InnoDB 支持行锁和表锁。
更新操作时,为了保证表数据的准确性,通常会加锁,为了提高系统的高并发能力,我们通常建议采用 行锁
,减少锁冲突
、锁等待
的时间。所以,存储引擎通常会选择 InnoDB
。
行锁可能会升级为表锁,有哪些场景呢?
如果一个表批量更新,大量使用行锁,可能导致其他事务长时间等待,严重影响事务的执行效率。此时,MySQL 会将 行锁
升级为 表锁
。
行锁是针对索引加的锁,如果 条件索引失效
,那么 行锁
也会升级为 表锁
。
注意:行锁将锁的粒度缩小了,进而提高了系统的并发能力。但是也有个弊端,可能会产生死锁,需要特别关注。
如果要开发一个列表展示页面并支持翻页时,我们通常会这样写 SQL:
select * From 表 limIT #{start}, #{pageSize};
随着翻页的深度加大, start
值越来越大,比如:limit 10000 , 10
。
看似只返回了 10 条数据,但数据库引擎需要查询 10010 条记录,然后将前面的 10000 条丢弃,最终只返回最后的 10 条记录,性能可想而知.
针对这个问题,我们通常有另外两种解决方案。
方案一:
先定位到上一次分页的最大 id,然后对 id 做条件索引查询。由于数据库的索引采用 B+ 树结构,这样可以一步到位。
select * from 表 where id > #{id} limit #{pageSize};
任何事情,有利就有弊。
这种翻页方式只支持 上一页
、下一页
,不支持跨越式直梯翻页。
方案二:
采用子查询。
select * from 表 where id > ( select id from 表 order by id limit 10000 1) limit 20;
将原来的单 SQL 查询拆成两步:
最小 id 的索引树节点位置
,通过 偏移量
读取后面的 20 条数据。反面案例:
select * from 表 where buyer_id = #{buyer_id}
我们知道,MySQL 创建表后,具体的行数据存储在主键索引(属于聚簇索引)的叶子节点。
二级索引属于非聚簇索引,其叶子节点存储的是主键值
。
select * 的查询过程:
buyer_id
的二级索引 B+ 树,查出对应的主键 id 列表。回表
操作,在主键索引中查询 id 对应的行数据。所以,我们需要罗列清楚必须的查询字段,且字段尽量在 覆盖索引
中,从而减少 回表
操作。
授人以鱼不如授人以渔。
除了知晓常见的不规范 SQL 写法,在开发过程中避免踩坑。我们还应知道,出现了慢 SQL 该如何排查、优化。
比如下面的语句,我们看是否使用了索引,可以通过 explain
分析相应的执行计划。
EXPLAIN SELECT * FROM t_PRomotion_activity WHERE id = 24
接下来,我们来逐一说明每个字段的含义。
Show Profile 与 EXPLAIN 的区别是,前者主要是在外围分析;后者则是深入到 MySQL 内核,从执行线程的状态和时间来分析。
MySQL 是在 5.0.37 版本之后才支持 Show Profile ,select @@have_profiling
返回 YES
表示功能已开启。
mysql> show profiles;
Empty set, 1 warning (0.00 sec)
显示为空,说明 profiles 功能是关闭的。
通过下面命令开启。
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
获取 Query_ID
之后,通过 show profile for query ID
,查看 SQL 语句在执行过程中线程的每个状态所消耗的时间。
以上是脚本宝典为你收集整理的SQL优化技巧全部内容,希望文章能够帮你解决SQL优化技巧所遇到的问题。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。