基于MySQL 的 SQL 优化总结

栏目: IT技术 · 发布时间: 4年前

内容简介:在数据库运维过程中,优化 SQL 是 DBA 团队的日常任务。例行 SQL 优化,不仅可以提高程序性能,还能减低线上故障的概率。目前常用的 SQL 优化方式包括但不限于:业务层优化、SQL 逻辑优化、索引优化等。其中索引优化通常通过调整索引或新增索引从而达到 SQL 优化的目的。索引优化往往可以在短时间内产生非常巨大的效果。

基于 <a href='https://www.codercto.com/topics/18746.html'>MySQL</a>  的  <a href='https://www.codercto.com/topics/18630.html'>SQL</a>  优化总结

基于MySQL 的 SQL 优化总结

在数据库运维过程中,优化 SQL 是 DBA 团队的日常任务。例行 SQL 优化,不仅可以提高程序性能,还能减低线上故障的概率。

目前常用的 SQL 优化方式包括但不限于:业务层优化、SQL 逻辑优化、索引优化等。其中索引优化通常通过调整索引或新增索引从而达到 SQL 优化的目的。索引优化往往可以在短时间内产生非常巨大的效果。

--- 来自美团技术团队

SQL 优化是一个复杂的问题,不同版本和种类的数据库、不同数据级的数据需要选择不同的优化策略。

说明:我这里简单总结一下 SQL 优化,很多的大佬写过这方面的细节和用法,甚至还有相关的案例。我只是作为一个阶段性的总结,肯定是不全面的。如有错误和不当之处,欢迎批评指正,不胜感激。

从日常开发写 SQL 的角度看,需要遵循一些规则,但是这些规则只能解决部分问题。因为随着开发和数据量的增长,SQL 还是会变慢,这个时候需要一些针对性的措施,比如针对性地添加索引,通过命令或者 工具 分析变慢的 SQL 等等。

说说 SQL 优化的其中两个大的原则(肯定还有别的):

原则一:尽量避免全表扫描。

原则二:通过索引优化。

这两个涉及的点比较多,他们之间也是有联系的,下面详细说说。

1、避免全表扫描

为啥要避免全表扫描呢?因为全表扫描耗费更多的时间。

那么从哪些方法避免全表扫描呢?

对 where 和 order by 涉及的列建立索引可以提高访问速度。但是要注意,并不是你建立了索引,索引就一定会生效。如果没有生效查询时还是全表扫描,速度还是得不到提升。那如何判断索引没有生效呢?可以借助 explain + SQL 语句 的结果判断。大佬写的 MySQL EXPLAIN 命令: 查看查询执行计划 中总结了用法。简单的说,使用该命令分析的结果中很多字段,其中 type 描述了查询的方式,如果 type 的结果是 ALL ,那么索引肯定没起作用。下面总结一下如何避免索引失效。

  1. 避免在 where 子句中对字段进行 null 判断
select id from user where name is null
  1. 避免在 where 子句使用 != 或者 <>
  2. 避免在 where 子句中对表达式进行操作
select id from user where age/2 = 20

修改为:

select id from user where age = 20 * 2
%
select id from user where username like '%wh'

2、索引优化

适当地添加索引可以提高 SQL 的速度,但也有些注意点。

  1. 使用联合索引时,注意索引列的顺序,一般遵循最左匹配原则

比如一个索引:

KEY `idx_userid_age` (`userId`, `age`) USING BTREE

符合最左匹配原则的写法是把 userid 放在前面

select userid, name from user where userid = 1001 and age = 10

当我们创建的这个联合索引,就相当于创建了 (userid)(userid, age) 两个索引。联合索引不满足最左原则,一般会失效,但是这个还跟 MySQL 优化器有关系。

  1. 在适当的时候,使用 覆盖索引

通常在使用索引检索数据之后,需要访问磁盘上数据表文件读取所需要的列,这种操作成为“ 回表 ”。

若索引中包含查询的所有列,则不需要回表操作,直接从索引文件中读取数据即可,这种索引成为“ 覆盖索引 ”。

在查询时尽量减少 select * ,只查询需要的行,条件允许时尽量建立 覆盖索引

  1. 删除冗余索引

索引并不是越多越好,冗余的索引会影响性能。

比如,索引 (A, B) 相当于创建了索引 (A) 和索引 (A, B)

  1. 注意索引的数量

索引不是越多越好,一般不要超过 5 个。索引虽然提高了查询效率,但是也会降低插入和更新的效率。插入或更新可能会重建索引,索引建立索引也需要慎重考虑。

  1. 索引不适合建立在有大量重复的字段上,如性别这类字段

3、其他

其他原则包括但不限于:

  1. 查询 SQL 尽量不要使用 select * ,而是 select 某字段
  2. 连表查询的时候尽量将数据量少的表驱动数据多的表。
  3. 如果插入的数据较多时,考虑批量插入。
  4. 原则上不要有超过 5 张以上的表连接

阿里巴巴开发手册中规定超过三个表禁止 join的,但是这些规范的适用性还是要考虑环境。当连表数量较少时,连表路径算法选择的是动态规划算法;但是连表太多的情况下,路径算法可能退化成贪心算法,连表的方案可能不是最优的的。

这种情况下,如何写 SQL 呢?答案是通过可以通过冗余实现,细节就不展开了。

4、通过工具分析 SQL

说说几个用到的 SQL 分析工具

4.1 MySQL 自带的慢查询日志

MySQL 的慢查询日志是 MySQL 提供的一种日志,记录,用于记录在 MySQL 中响应时间超过设定的阈值的语句。在 MySQL 的配置文件 my.ini 中开启后,支持将慢查询日志写入文件或者数据库。通过 explain 关键词模拟优化器执行 SQL,分析慢查询 SQL。

分析相关语句使用了哪些表、连接的类型、扫描的行数、使用的索引等。

4.2 日志分析工具 MySQLdumpslow

在生产环境中,手工分析日志、查找 SQL 比较费时间。MySQL 提供的 MySQLdumpslow 工具可以得到一些 SQL 访问的统计数据,比如访问次数最多的 10 条 SQL 等。

4.3 第三方工具:美团技术团队的 SQLAdvisor

由美团技术团队维护的一个开源的分析 SQL,给出索引优化建议的工具。

只是大概做了个总结,细节都没有展开,有兴趣的同学自行学习吧。


以上所述就是小编给大家介绍的《基于MySQL 的 SQL 优化总结》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

精益创业实战

精益创业实战

Ash Maurya / 张玳 / 图灵文化发展有限公司 / 2013-1 / 39.00元

《精益创业实战(第2版)》融合了精益创业法、客户开发、商业模式画布和敏捷/持续集成的精华,讲解精益创业实战法。作者以自己的创业项目为主线,结合大量真实案例,并融入一些伟大创业者的智慧,创建了一套思考、验证和发布产品的系统。那些想要验证自己的创意、解决实际问题和渴望拥有成功事业的人,可以把《精益创业实战(第2版)》当成一套明确的实践计划、一幅清晰的创业路线图、一本实践指南,或者一套反复实践的方法论。一起来看看 《精益创业实战》 这本书的介绍吧!

MD5 加密
MD5 加密

MD5 加密工具

RGB CMYK 转换工具
RGB CMYK 转换工具

RGB CMYK 互转工具