MySQL编程优化篇（四） SQL优化（ⅳ）常用SQL优化

灰太狼 2023-06-23 10:56 105阅读 0赞

**目录**

大批量插入数据

优化order by语句

MySQL中有两种排序方式

Filesort的优化

优化group by语句

优化嵌套查询

优化分页查询

巧用 Rand() 提取随机行

数据库名、表名大小写问题

--------------------

# 大批量插入数据 #

1.  因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
2.  在导入前关闭唯一性校验，导入后开启唯一性校验。
3.  导入前关闭自动提交，导入后开启自动提交。

# 优化order by语句 #

## MySQL中有两种排序方式 ##

1.  通过**有序索引顺序扫描直接返回有序数据**，这种方式在使用 explain 分析查询的时候显示为Using Index，不需要额外的排序，操作效率较高
2.  通过对返回数据进行排序，也就是通常说的 **Filesort 排序**，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。

**FileSort排序**

*  通过相应的排序算法，将取得的数据在 sort\_buffer\_size 系统变量设置的 **内存排序区** 中进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。
 *  sort\_buffer\_size 设置的排序区是每个线程独占的，所以同一个时刻，MySQL中存在多个 sort buffer排序区。

优化目标：**尽量减少额外的排序，通过索引直接返回有序数据**。

WHERE条件和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者都是降序。否则肯定需要额外的排序操作，这样就会出现Filesort。

总结，下列SQL可以使用索引:

SELECT * FROM tabname ORDER BY key_part1,key_part2...
    
    SELECT * FROM tabname WHERE key_part1=1 ORDER BY key_part1 DESC,key_ part2 DESC;
    
    SELECT * FROM tabname ORDER BY key_part1 DESC,key_part2 DESC;

但是在以下几种情况下则不使用索引:

-- order by的字段混合ASC和DESC
    SELECT * FROM tabname ORDER BY key_part1 DESC,key_part2 ASC; 
    
    -- 用于查询行的关键字与ORDER BY中所使用的不相同
    SELECT * FROM tabname WHERE key2=constant ORDER BY key1;
    
    -- 对不同的关键字使用ORDER BY（？）
    SELECT * FROM tabname ORDER BY key1, key2;

## Filesort的优化 ##

通过创建合适的索引能够减少Filesort的出现，但是在某些情况下，条件限制不能让Filesort消失，那就想办法加快Filesort的操作。对于Filesort，MySQL有两种排序算法。

*  **两次扫描算法（Two Passes）**：首先根据条件取出排序字段和行指针信息，之后在排序区 sort buffer中排序。如果排序区 sort buffer 不够，则在临时表 Temporay Table中存储排序结果。完成排序后根据行指针回表读取记录。
    
    需要两次访问数据，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，尤其是第二次读取操作可能会导致大量随机IO操作；优点是排序的时候内存开销少。
 *  **一次扫描算法（Single Pass）**：一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法要高。

MySQL通过比较系统变量 max\_length\_for\_sort\_data 的大小和 Query 语句取出的字段总大小来判断使用哪种排序。如果 max\_length\_for\_sort\_data 更大，那么使用第二种优化之后的算法，否则使用第一种算法。

**结论**：

> 尽量只使用必要的字段，即：SELECT具体的字段名称，而不是 SELECT \* 选择所有字段，这样可以减少排序区的使用，提高SQL性能。

# 优化group by语句 #

默认情况下，MySQL对所有GROUP BY col1,col2,…的字段进行排序。这与在查询中指定ORDER BY col1,col2,…类似。因此，如果显示包括一个相同列的order by子句，则对MySQL的实际执行性能没有什么影响。

如果查询包括GROUP BY但用户想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。

案例：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTQxNjE5_size_16_color_FFFFFF_t_70][]

# 优化嵌套查询 #

使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。

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

# 优化分页查询 #

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头痛的分页场景是"limit 1000, 20"，此时MySQL排序出前1020条记录后仅仅需要返回第1001到1020条记录，前1000条记录都会被抛弃，查询和排序的代价非常高。

**第一种优化思路**

在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。

**第二种优化思路**

把 limit 查询转换成某个位置的查询

`select ... where id < ? limit 10`

注意，这样把 LIMIT m,n 转换成 LIMIT n 的查询，只适合在排序字段不会出现重复值的特定环境，能够减轻分页翻页的压力；如果排序字段出现大量重复值，而仍进行这种优化，那么分页结果可能会丢失部分记录，不适用这种方式进行优化。

# 巧用 Rand() 提取随机行 #

大多数数据库都会提供产生随机数的包或者函数，通过这些包或者函数可以产生用户需要的随机数，也可以用来从数据表中抽取随机产生的记录，这对一些抽样分析统计是非常有用的。

如：`SELECT * FROM crm_user ORDER BY RAND() LIMIT 11`

# 数据库名、表名大小写问题 #

在大多数UNIX环境中，由于操作系统对大小写的敏感性导致了数据库名和表名对大小写敏感性，而在 Windows中，由于操作系统本身对大小写不敏感，因此在Windows下的MySQL数据库名和表名对大小写也不敏感。

列、索引、存储子程序和触发器名在任何平台上对大小写不敏感。默认情况下，表别名在UNIX中对大小写敏感，但在Windows或Mac OS X中对大小写不敏感。如，在Linux中执行以下sql会报错。

mysql> select * from t as test where Test.a = 1;
    ERROR 1054 (42S22): Unknown column 'Test.a' in 'where clause'

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTQxNjE5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191218164916207.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTQxNjE5,size_16,color_FFFFFF,t_70