BATJ解决千万级别数据之MySQL 的 SQL 优化大总结
2020-05-22 16:08:18来源:博客园 阅读 ()
BATJ解决千万级别数据之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
2.避免在 where 子句使用 != 或者 <>
3.避免在 where 子句中对表达式进行操作
select id from user where age/2 = 20
4.避免在 where 子句中对字段进行函数操作
5.避免在 like 查询中将 %放在开头
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 优化器有关系。
2.在适当的时候,使用覆盖索引
通常在使用索引检索数据之后,需要访问磁盘上数据表文件读取所需要的列,这种操作成为“回表”。
若索引中包含查询的所有列,则不需要回表操作,直接从索引文件中读取数据即可,这种索引成为“覆盖索引”。
在查询时尽量减少select *,只查询需要的行,条件允许时尽量建立覆盖索引。
3.删除冗余索引
索引并不是越多越好,冗余的索引会影响性能。
比如,索引(A, B)相当于创建了索引(A)和索引(A, B)。
4.注意索引的数量
索引不是越多越好,一般不要超过 5 个。索引虽然提高了查询效率,但是也会降低插入和更新的效率。插入或更新可能会重建索引,索引建立索引也需要慎重考虑。
5.索引不适合建立在有大量重复的字段上,如性别这类字段
> 其他
其他原则包括但不限于:
- 查询 SQL 尽量不要使用 select *,而是 select 某字段。
- 连表查询的时候尽量将数据量少的表驱动数据多的表。
- 如果插入的数据较多时,考虑批量插入。
- 原则上不要有超过 5 张以上的表连接
阿里巴巴开发手册中规定超过三个表禁止 join的,但是这些规范的适用性还是要考虑环境。当连表数量较少时,连表路径算法选择的是动态规划算法;但是连表太多的情况下,路径算法可能退化成贪心算法,连表的方案可能不是最优的的。
这种情况下,如何写 SQL 呢?答案是通过可以通过冗余实现,细节就不展开了。
通过工具分析 SQL
说说几个用到的 SQL 分析工具
** 1.MySQL 自带的慢查询日志**
MySQL 的慢查询日志是 MySQL 提供的一种日志,记录,用于记录在 MySQL 中响应时间超过设定的阈值的语句。在 MySQL 的配置文件 my.ini中开启后,支持将慢查询日志写入文件或者数据库。通过explain关键词模拟优化器执行 SQL,分析慢查询 SQL。
分析相关语句使用了哪些表、连接的类型、扫描的行数、使用的索引等。
2.日志分析工具 MySQLdumpslow
在生产环境中,手工分析日志、查找 SQL 比较费时间。MySQL 提供的 MySQLdumpslow 工具可以得到一些 SQL 访问的统计数据,比如访问次数最多的 10 条 SQL 等。\
3.第三方工具:美团技术团队的 SQLAdvisor
由美团技术团队维护的一个开源的分析 SQL,给出索引优化建议的工具。
只是大概做了个总结,细节都没有展开,有兴趣的同学自行学习吧。
本文由博客一文多发平台 OpenWrite 发布!
原文链接:https://www.cnblogs.com/MonsterJ/p/12937852.html
如有疑问请与原作者联系
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
- 老板让你抗住千万级流量,如何做架构设计? 2020-06-11
- 聊聊微服务架构及分布式事务解决方案! 2020-06-10
- 解决IDEA Maven下载依赖包速度慢问题 2020-06-05
- 如何快速安全的插入千万条数据? 2020-06-03
- Java生鲜电商平台-生鲜电商中配送订单解决方案(小程序/APP) 2020-05-31
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash