MySQL数据库SQL语句优化

最新推荐文章于 2024-04-18 03:29:48 发布

Mekeater

最新推荐文章于 2024-04-18 03:29:48 发布

阅读量300

点赞数

分类专栏：数据库文章标签：数据库 sql mysql 优化

本文链接：https://blog.csdn.net/qq_34720818/article/details/117885722

版权

数据库专栏收录该内容

20 篇文章 2 订阅

订阅专栏

MySQL数据库SQL语句优化

前言

数据库语句有很多常见的优化策略，本文讲解这些常见的优化方法或者要遵循的规则。

1. 大批量插入数据提高效率的方法

主键顺序插入
因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。
关闭唯一性校验
在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
手动提交事务
建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

2. 优化insert语句

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
在事务中进行数据插入（即开启事务，然后手动提交事务，这样可以多条插入语句一次性提交）。
数据有序插入。

3. 优化order by语句

order by 有两种排序方式。第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。
要想使用using index这种排序方式，有3点注意：1.where 条件和Order by 使用相同的索引；2. Order By 的顺序和复合索引顺序相同；3.Order by 的字段都是升序，或者都是降序。
Filesort 的优化：对于Filesort，MySQL有两种排序算法，1. 两次扫描算法：首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。2.一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。
对于两种Filesort算法，MySQL数据库如何选择？MySQL 通过比较系统变量max_length_for_sort_data 的大小和Query语句取出的字段总大小，来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。因此，可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效
率。

4. 优化group by 语句

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。
因此，提高group by语句的效率有两种方法：

如果查询包含 group by 但是用户想要避免排序结果的消耗，则可以执行order by null 禁止排序。
为待分组字段创建索引。

5. 优化嵌套查询

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

连接(Join)查询之所以更有效率一些，是因为MySQL 不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

6. 优化OR条件

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。
建议使用 union 替换 or

7.优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性。
一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。
优化方法一：在索引上完成排序分页操，最后根据主键关联回原表查询所需要的其他列内容。
优化方法二：该方案适用于主键自增的表，并且主键自增顺序不能出现断裂（即有删除操作，导致索引某些位置不是连续自增），==可以把Limit 查询转换成某个位置的查询 ==。

8. SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。（指定或建议数据库优先去干嘛，而不用数据库自己去选择干嘛）

use index：在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

上图中name字段是创建了索引的字段
ignore index：和use index作用相反，它是建议数据库不用哪个索引。
force index： use index和 ignore index都是对数据库的建议，如果数据库觉得建议不合适，它会执行自己的选择。但是force index是强制数据库必须用这个字段作为索引。