问十：如何优化SQL查询速度？

最新推荐文章于 2024-08-28 18:18:01 发布

问而知

最新推荐文章于 2024-08-28 18:18:01 发布

阅读量545

点赞数

分类专栏： MySQL数据库

本文链接：https://blog.csdn.net/qq_18657175/article/details/89465245

版权

MySQL数据库专栏收录该内容

16 篇文章 4 订阅

订阅专栏

通过ANALYSE语句进行表的分析：SELECT * FROM USER PROCEDURE ANALYSE()；

使用此方法通知数据库进行键值分析，会返回每列属性的最大最小长度，多少行为空，所有行的平均值或者平均长度，同时会给出一些长度设置的建议

使用EXPLAIN验证优化程序的操作是否生效：EXPLAIN SELECT NAME FROM USER ORDER BY NAME DESC

使用EXPLAIN语句的话，可以了解到各索引是否会被使用到。也会告诉一些其他有用的信息，比如type项中，如果是index或者all的话，就是全表扫描，可能需要修改SQL。在Extra项中如果是Useing filesort或者Using temporary，就说明MySQL未能够使用索引，是使用的文件排序或者使用到了临时表。

1.保证不查询多余的列与行，热点数据实现缓存。

* 只返回必要的列：最好不要使用 SELECT * 语句。

* 只返回必要的行：使用 LIMIT 语句来限制返回的数据。

* 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

2. 减少服务器端扫描的行数。

最有效的方式是使用索引来覆盖查询。主要是减少IO操作，如果是覆盖索引，可以不访问硬盘，查询效率更高。

3.重构查询方式

1). 切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

方式1：DELET FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

方式2：

rows_affected = 0

do {

rows_affected = do_query(

"DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")

} while rows_affected > 0

2). 分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。

优点：1.让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。

2.分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。

3.减少锁竞争；

4.在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。

5.查询本身效率也可能会有所提升。

例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

SELECT * FROM tab

JOIN tag_post ON tag_post.tag_id=tag.id

JOIN post ON tag_post.post_id=post.id

WHERE tag.tag='mysql';

SELECT * FROM tag WHERE tag='mysql';

SELECT * FROM tag_post WHERE tag_id=1234;

SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

4.其他方式优化

1）索引优化查询(大部分性能问题都能通过索引来解决)

1）检查索引是否被使用(就是检查有没有建立索引，explain语句可以进行检测)。

2）检查索引是否有必要使用(判断该列的选择性，选择性太小不用)。

3）检查优化器是否使用了索引(使用explain来分析)。

4）索引是否可以被进一步优化(比如说使用索引覆盖以及调整索引顺序)。

2）SPJ的查询优化

所谓SPJ是指：基于选择、投影、连接三种基本操作相结合的查询

其基本原则是对于选择、投影操作来说尽量将操作下推，所谓下推是指将操作下移到子查询中或者基表查询中，这样做的好处是减少连接操作前的结果的行数或列数(也就是说减少连接操作前的数据量或者说减少中间数据量)，进而减少连接操作所需的IO操作数以及数据操作量；对于连接操作来说就是要在不影响功能实现的前提下，通过调整表的连接顺序来提高查询效率。

3）子查询优化

所谓子查询是指一个查询是另一个查询一部分(也就是说查询语句中嵌套查询语句)

1）子查询合并，就是说对同一表的多个子查询合并在一次子查询中

这样可以把多次表扫描、多次连接减少为单次表扫描和单次连接。

2)子查询上拉，就是将一些子查询置于外层查询中，作为连接关系与外层查询连接(就是将子查询尽量优化为关联查询)。

5.查询条件优化

SQL查询语句中，对元组进行过滤和连接的表达式，形式上是出现在WHERE/JOIN-ON/HAVING的子句中的表达式称为条件。

1）条件下推，把与单个表相关的条件，放到对单表进行扫描的过程中执行。

例子：SELECT * FROM A, B WHERE A.a=1 and A.b=B.b;

执行顺序：1.扫描A表，并带有条件A.a=1，把A表作为嵌套循环的外表

2.扫描B表，执行连接操作，并带有过滤条件A.b=B.b

说明：数据库系统都支持条件下推，且无论条件对应的列对象有无索引，系统自动进行优化，不用人工介入。

2）条件化简，去除表达式中冗余的括号。

优点：可以减少语法分析时产生的AND和OR树的层次。---减少CPU的消耗

示例：((a AND b) AND (c AND d))

化简为a AND b AND c AND d

6.连接优化

连接消除,去掉不必要的连接中间表对象，则减少了连接操作。

例： SELECT a.*, c.* FROM a JOIN b ON (a1=b1) JOIN c ON (b1=c1);

相当于：SELECT A.*, C.* FROM A JOIN C ON (a1= c1);

非SPJ的查询优化

1）group by分组的下移：所谓下移指的是如果GROUPBY 操作可能较大幅度减小关系元组的个数，则把分组操作提前到子表中执行。

2 ) order by优化：

a.利用索引避免排序

b.排序下推。排序操作尽量下推到基表中，有序的基表进行连接后的结果符合排序的语义，这样能避免在最终的大的连接结果集上执行排序操作(在大表中进行排序会占用更多的内存)

3) COUNT()优化：

a. 简单优化：即逆向思维，正难则反

b. 使用近似值：有时候一些业务场景并不需要完全精确的count值，因此可以用近似值来代替，而explain估算出的行数就是一个不错的选择。

4）limit分页查询的优化

在分页查询时，在偏移量非常大的时候，例如可能是LIMIT 10000,20这样的查询，这时MySQL需要查询10020条记录后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，可以有如下方法，当然关键还是使用索引。

a.限制分页数量，即超过多少页之后就不准查了。

b.延迟关联，所谓延迟关联是指将语句改写成先查询分页数据所对应的id，然后再做关联查询查出所需要的列，这样可以使用索引覆盖来扫描而不是查询所有列。

c. 记录上一次获取数据的位置或者直接计算分页的起点(要求主键递增的且没有空的)，将LIMIT查询转换为已知的位置的查询，让MySQL通过范围扫描获得到对应的结果。

注：count(*)会使用索引来全表扫描。

3、必要的时候强制提示优化程序使用指定的索引或者按指定顺序访问某个表：FORCE INDEX/USE INDEX/IGNORE INDEX/STRAIGHT_JOIN

FORCE INDEX/USE INDEX/IGNORE INDEX 可以告知服务器使用或者不是用哪些索引

STRAIGHT_JOIN 强制要求优化程序按特定的顺序使用各个表（因为MySQL优化程序会自行确定各个表的扫描顺序，但有时候并不是最优的操作），可以配合EXPLAIN来使用

4、尽量比较拥有相同数据类型的列，同时避免过多使用自动类型转换

一般来说对带索引的列进行比较的时候，如果他们的数据类型相同，那么与类型不等的情况相比，查询性能会提升很多。比如 INT/INT 比较就会比 INT/BIGINT 比较更快。

SELECT *FROM user WHERE user_age = '26';

SELECT *FROM user WHERE user_age = 26;

这样的话，第一条就会比第二条慢。