MYSQL【二、SQL 优化】

最新推荐文章于 2024-10-31 23:41:03 发布

纯纯的小白

最新推荐文章于 2024-10-31 23:41:03 发布

阅读量826

点赞数 7

分类专栏： MySql 简单回顾文章标签： mysql sql优化

本文链接：https://blog.csdn.net/qq_30769437/article/details/140301656

版权

MySql 简单回顾专栏收录该内容

22 篇文章 1 订阅

订阅专栏

文章目录

1、慢SQL定位
2、优化慢SQL
3、查询深分页
4、执行计划 explain

1、慢SQL定位

慢查询日志：开启MySQL的慢查询日志，再通过一些工具比如 mysqldumpslow 去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。
服务监控：可以在业务的基建中加入对慢SQL的监控，常见的方案有字节码插桩、连接池扩展、ORM框架过程，对服务运行中的慢SQL进行监控和告警。

2、优化慢SQL

主要从两个方面考虑，SQL语句本身的优化，以及数据库设计的优化。

在这里插入图片描述

● 避免不必要的列

SQL查询的时候，应该只查询需要的列，而不要包含额外的列，像 slect * 这种写法应该尽量避免。

● 分页优化

在数据量比较大、分页深度较深的情况下，可以考虑以下分页优化方案：

● 延迟关联（Deferred Join）

先通过 WHERE 条件提取出主键，然后再将该表与原数据表进行关联，通过主键ID提取数据行，而不是使用原有的二级索引提取数据行。这样可以减少关联操作的数据量，提高查询效率。

SELECT 
	a.* 
FROM
	table a,
	(
        SELECT 
        	id 
        FROM 
        	table 
        WHERE 
        	type = 2 AND level = 9
        ORDER BY 
        	id ASC 
        LIMIT 190289, 10
    ) b
WHERE a.id = b.id

● 书签方式（Bookmark）

书签方式是根据 LIMIT 语句中的第一个参数对应的主键值，通过该主键值进行过滤和限制。先找到满足条件的第一个主键值，然后再根据该主键值进行过滤并进行LIMIT操作。

SELECT 
	* 
FROM 
	table 
WHERE 
	id > (
        SELECT 
        	id 
        FROM
        	table 
        WHERE 
        	type = 2 AND level = 9 
        ORDER BY 
        	id ASC
    )
LIMIT 190, 10

● 索引优化

◎ 利用覆盖索引

InnoDB使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引例如对于如下查询：

select name from test where city='上海'

将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取。

alter table test add index idx_city_name (city, name);

◎ 低版本避免使用or查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。

◎ 避免使用 != 或者 <> 操作符

不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引。

解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描。

例如，把 column<>’aaa’，改成 column>’aaa’ or column<’aaa’，就可以使用索引了。

◎ 适当使用前缀索引

适当地使用前缀索引，可以降低索引的空间占用，提高索引的查询效率。

比如，邮箱的后缀都是固定的 “ @xxx.com ”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引。

 alter table test add index index2(email(6));

需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做 order by 和 group by 操作，也无法作为覆盖索引。

◎ 避免列上函数运算

避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率。

select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;

● 正确使用联合索引

使用联合索引的时候，注意最左匹配原则。

● JOIN优化

◎ 优化子查询

尽量使用 join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大。

◎ 小表驱动大表

关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL内部会遍历驱动表，再去连接被驱动表。

比如 left join，左表就是驱动表，A表小于B表，建立连接的次数就少，查询速度就被加快了。

select name from A left join B ;

◎ 适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略。

◎ 避免使用 JOIN 关联太多的表

《阿里巴巴Java开发手册》规定不要 join 超过三张表。

join 太多降低查询的速度
join 的 buffer 会占用更多的内存

如果不可避免要 join 多张表，可以考虑使用数据异构的方式异构到ES中查询。

● 排序优化

◎ 利用索引扫描做排序

MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的。

但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢；因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行。

-- 建立索引（date,staff_id,customer_id）
select 
	staff_id, 
	customer_id 
from 
	test 
where 
	date = '2010-01-01'
order by 
	staff_id, customer_id;

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序。

● UNION优化

◎ 条件下推

MySQL处理 union 的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在 union 查询中都会失效，因为它无法利用索引。

最好手工将 where、limit 等子句下推到 union 的各个子查询中，以便优化器可以充分利用这些条件进行优化。

此外，除非确实需要服务器去重，一定要使用 union all，如果不加 all 关键字， MySQL会给临时表加上 distinct 选项，这会导致对整个临时表做唯一性检查，代价很高。

3、查询深分页

3.1、什么是MySQL深分页

MySQL查询深分页是指在处理大量数据时，需要查询指定页码的数据，但是由于数据量过大，每次查询一页数据的性能较低。

假设有一个账户表 account，包含 id、name、age 等字段，需要查询第 100000 条到第 100010 条数据，下面这个sql就是深分页：

SELECT 
	id, name, balance
FROM 
	account
WHERE 
	update_time > '2020-09-19'
LIMIT 
	100000, 10;

3.2、深分页查询很慢的原因

深分页查询会很慢的原因主要有两个：

limit 语句会先扫描 offset+n 行，然后再丢弃掉前 offset 行，返回后 n 行数据。
1. 也就是说 limit 100000, 10，就会扫描 100010 行，而 limit 0, 10，只扫描 10 行。因此，随着 offset 的增大，查询的数据量也会增大，导致查询效率下降。
limit 语句会导致回表操作的次数增加。
1. 回表是指在使用索引查询时，需要根据索引查找到对应的主键值，然后再根据主键值查询具体的数据。
2. 在深分页查询中，由于需要查询的数据量较大，因此回表操作的次数也会增加，导致查询效率下降。

3.3、优化深分页

● 子查询优化

将查询条件转移到主键索引树，减少回表次数。可以通过子查询的方式，先查询满足条件的主键ID，然后再与原表进行关联查询。这样可以减少回表操作，提高查询效率。

SELECT 
	id, name, balance
FROM 
	account
WHERE 
	id >= (
        SELECT 
        	a.id 
        FROM 
        	account a 
        WHERE
        	a.update_time >= '2020-09-19'
        LIMIT 
        	100000, 1
    )
LIMIT 10;

● INNER JOIN 延迟关联

使用 INNER JOIN 代替子查询，将条件转移到主键索引树。先通过二级索引树查询满足条件的主键ID，然后与原表通过主键ID进行内连接，减少回表操作。

SELECT 
	acct1.id, 
	acct1.name, 
	acct1.balance
FROM 
	account acct1
	INNER JOIN (
		SELECT 
        	a.id
		FROM 
        	account a
		WHERE 
        	a.update_time >= '2020-09-19'
		ORDER BY 
        	a.update_time
		LIMIT 
        	100000, 10
) AS acct2
ON acct1.id = acct2.id;

● 标签记录法

使用标签记录上次查询的位置，下次查询时从该位置开始扫描。这种方法适用于有类似连续自增的字段，例如主键ID。通过记录上次查询的ID，下次查询时使用大于该ID的条件进行查询。

SELECT 
	id, name, balance
FROM 
	account
WHERE 
	id > 100000
ORDER BY 
	id
LIMIT 
	10;

● 使用 between and

将 limit 查询转换为已知位置的查询，通过范围扫描准确获取对应的结果。可以根据已知的边界值进行优化。

SELECT 
	id, name, balance
FROM 
	account
WHERE 
	id BETWEEN 100000 AND 100010
ORDER BY 
	id DESC;

4、执行计划 explain

explain 是sql优化的利器，除了优化慢sql，平时的sql编写，也应该先 explain，查看一下执行计划，看看是否还有优化的空间。

直接在 select 语句之前增加 explain 关键字，就会返回执行计划的信息。

在这里插入图片描述

id：MySQL会为每个 select 语句分配一个唯一的 id 值
select_type：查询的类型，根据关联、union、子查询等等分类，常见的查询类型有SIMPLE、PRIMARY
table：表示 explain 的一行正在访问哪个表
type：最重要的列之一。表示关联类型或访问类型，即 MySQL 决定如何查找表中的行。
1. system：当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快
2. const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量( const )值。这类扫描效率极高，返回数据量少，速度非常快
3. eq_ref：查询时命中主键 primary key 或者 unique key 索引， type 就是 eq_ref
4. ref_or_null：这种连接类型类似于 ref，区别在于 MySQL 会额外搜索包含 NULL 值的行
5. index_merge：使用了索引合并优化方法，查询使用了两个以上的索引
6. unique_subquery：替换下面的 IN 子查询，子查询返回不重复的集合
7. index_subquery：区别于 unique_subquery ，用于非唯一索引，可以返回重复值
8. range：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在 where 语句中使用 bettween…and 、 < 、 > 、 <= 、 in 等条件查询 type 都是 range
9. index： index 与 ALL 其实都是读全表，区别在于 index 是遍历索引树读取，而 ALL 是从硬盘中读取
10. ALL：全表扫描
possible_keys：显示查询可能使用哪些索引来查找，使用索引优化sql的时候比较重要
key：这一列显示 mysql 实际采用哪个索引来优化对该表的访问，判断索引是否失效的时候常用
key_len：显示了 MySQL使用索引的最大可能长度；这个长度是基于索引中使用的字段的数据类型和长度来计算的
ref：ref 列展示的就是与索引列作等值匹配的值，常见的有：const（常量）， func，NULL，字段名
rows：这也是一个重要的字段，MySQL查询优化器根据统计信息，估算SQL要查到结果集需要扫描读取的数据行数，这个值非常直观显示SQL的效率好坏，原则上 rows 越少越好
extra：显示不适合在其它列的额外信息，虽然叫额外，但是也有一些重要的信息：
1. Using index：表示MySQL将使用覆盖索引，以避免回表
2. Using where：表示会在存储引擎检索之后再进行过滤
3. Using temporary：表示对查询结果排序时会使用一个临时表