MySql（39）其他语句优化

天天天天天天天天d

于 2022-10-16 23:42:18 发布

阅读量755

点赞数 1

分类专栏： MySql 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/gtd54789/article/details/127355487

版权

MySql 专栏收录该内容

52 篇文章 4 订阅

订阅专栏

文章目录

子查询优化
排序优化
- filesort算法：双路排序和单路排序
GROUP BY优化
分页查询优化(limit优化)
EXISTS 和 IN 的区分
COUNT(*)与COUNT(具体字段)效率
关于SELECT(*)
多使用COMMIT

子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。
子查询是MySQL的一项重要的功能，可以通过一个SQL语句实现比较复杂的查询。但是，子查询的执行效率不高。原因:

执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询
子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响
对于返回结果集比较大的子查询。其对查询性能的影响也就越大

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好

尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

排序优化

问题:在WHERE条件字段上加索引，但是为什么在ORDER BY字段上还要加索引呢?

在MySQL中，支持两种排序方式，分别是 FileSort 和Index排序。

Using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buiffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSot 排序。
using index: 通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高
Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高
FileSort 排序则一般在内存中进行排序，占用CPU 较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率校低

SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER EY子句说免使用 FileSort排序。当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，还是要避免，以提高查询效率。
尽且使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列;如果不同就使用联合索引
无法使用lndex时，需要对FileSort方式进行调优

CREATE INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

filesort算法：双路排序和单路排序

排序的字段若如果不在索引列上，则filesort会有两种算法:双路排序和单路排序

双路排序（慢）

MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列
，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序

单路排序（快）

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

由于单路是后出的，总体而言好过双路但是用单路有问题

在sort_buffer中,单路比多路要多占用更多空间，因为单路是取出所有字段，而双路值取排序字段。这时数据的总大小有可能大于sort_buffer`的容量
导致需要多次I/O得不偿失

优化策略:

尝试提高 sort_buffer_size

不管使用那种算法，提高这个参数都会提高效率，因为这个是更具每个进程的1M-8M的调整。

  SHOW VARIABLES LIKE '%sort_buffer_size%'

尝试提高 max_length_for_sort_data
- 提高这个参数，会增加改进算法的概率
- ```
  SHOW VARIABLES LIKE `%max_length_for_sort_data%`    # 默认1024字节
```

如果设置太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高磁盘I/O活动和低处理器使用率.

如果返回的列的总数长度大于 max_length_for_sort_data 使用双路算法，否则使用单路。

在 1024-8192字节之间调整

order by 时 select * 是大忌，最好只取需要的字段
太大超了 max_length_for_sort_data 会改用老的双路排序

GROUP BY优化

group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。
group by 先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
where效率高于having，能写在where限定的条件就不要写在having中了
减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢

分页查询优化(limit优化)

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大
在大数据量的分页查询时,limit后的起始位置越靠后，耗时越长

EXPLAIN select * from student limit 2000000,10;

# 优化思路一
# 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id;

# 优化思路二
# 该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询
EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

limit优化思路：通过覆盖索引+子查询的方式来优化

EXISTS 和 IN 的区分

问题：不太理解哪种情况下应该使用 EXISTS，哪种情况应该用 IN。选择的标准是看能否使用表的索引吗？

回答:索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为小表驱动大表。在这种方式下效率是最高的。

比如下面这样:

SELECT * FROM A  WHERE cc IN(SELECT cc FRON B)
SELECT * FROM  WHERE EXISTS (SELECT cc FRON B WHERE B.cc=A.cc)

当A小于B时，用EXISTS。因为EXISTS的实现，相当于外表循环，实现的逻辑类似于:

for i in A:
    for j in B:
        if j.cc == i.cc

当B小于A时用IN，因为实现的逻辑类似于:

for i in B:
    list.append(i)
    
for j in A:
    if j in list

COUNT(*)与COUNT(具体字段)效率

COUNT()和COUNT(1)都是对所有结果进行COUNT,COUNT()和COUNT(1)本质上并没有区别（二者执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的)。如果有WHERE了句，则是对所有符合筛选条件的数据行进行统计;如果没有WHERE子句，则是对数据表的数据行数进行统计
- 如果是MyISAM存储引擎，统计数据表的行数只需要o(1)的复杂度，这是因为每张MyISAM的数据表都有一个meta信息存储了row_count值，而—致性则由表级锁来保证
- 如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像 MyISAM—样，维护一个row_count变量，因此需要采用扫描全表，进行循环＋计数的方式来完成统计
- 在InnoDB 引擎中，如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引)。对于COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。
- 如果有多个二级索引，会使用key_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计

关于SELECT(*)

在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原因：

MySQL 在解析的过程中，会通过查询数据字典将"*"按序转换成所有列名，这会大大的耗费资源和时间。
无法使用覆盖索引

多使用COMMIT

只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。
COMMIT 所释放的资源：

回滚段上用于恢复数据的信息
被程序语句获得的锁
redo / undo log buffer 中的空间
管理上述 3 种资源中的内部花费

天天天天天天天天d

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySql（39）其他语句优化

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像 MyISAM—样，维护一个row_count变量，因此需要采用扫描全表，进行循环＋计数的方式来完成统计。取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。但是，子查询的执行效率不高。
复制链接

扫一扫