MySQL 优化篇

  • 一、查询性能优化
  • 1. 引言
  • 0.1 为什么查询会慢？
  • 0.2 慢查询基础：优化数据访问
  • 0.3 查询执行的基础
  • 0.3.1 MySQL 如何执行关联查询
  • 0.3.2 优化 LIMIT 分页
  • 0.3.3 优化 UNION 查询
  • 2. 索引
  • 3. 查询条件优化
  • 二、增删改DML 语句优化
  • 三、SQL 优化实战


MySQL 性能优化包括查询速度优化、数据库结构优化、MySQL 服务器优化等。

MySQL优化篇

MySQL 性能优化是指通过合理安排资源，调整系统参数使 MySQL 运行更快、更节省资源。MySQL 性能优化包括查询速度优化、数据库结构优化、MySQL 服务器优化等。

一、查询性能优化

1. 引言

在大刀阔斧讲优化前，我们先思考下，查询为什么会慢，如何优化，又如何针对 MySQL 的优化器写出合理的查询呢？这就要求我们必须了解一点查询的基本原理了。

0.1 为什么查询会慢？

真正重要的是响应时间，如果把查询看作是一个任务，那么它由一系列的子任务完成，每个子任务都会消耗一定的时间。如果要优化，就要优化其子任务，要么消除一些子任务，要么减少子任务的执行次数（如 limit），要么让子任务运行更快。

那哪些任务运行慢呢？我们可以通过 EXPLAIN 等方法获得分析结果。

date mysql 优化 查询 mysql的查询优化_MySQL

查询的生命周期分为：客户端到服务器，在服务器上进行解析，生成执行计划，执行，返回客户端。

执行是生命周期中最重要的阶段，包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

0.2 慢查询基础：优化数据访问

查询性能低下的原因是访问的数据量太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们有以下分析步骤：

  • 确认应用程序是否存在检索大量超过需要的数据，太多的行或太多列；
  • 确认 MySQL 服务器层是否存在分析大量超过需要的数据行。
  • 是否在多表关联时返回了全部列；是否总是取出全部列；
  • 是否扫描到了额外记录（不需要的记录）

一般 MySQL 能使用三种方式应用 WHERE 条件，从好到坏依次为：①在索引中使用 WHERE 条件过滤，这是在存储引擎层完成的（Extra 为空）；②使用覆盖索引扫描（Extra 列中出现了 Using Index）来返回记录；③从数据表中返回数据，然后过滤不满足条件的记录（Extra 为 Using WHERE），这是在服务器层完成的，MySQL 需要从数据表读取数据然后过滤。

✅所以对应的优化技巧如下：①使用覆盖索引扫描，把所有需要用到的列都放到索引中，这样存储引擎无需回表获取对应行；②改变库表结构③重写复杂查询

对于 MySQL 而言，网络通信、查询解析和优化都很轻量级，MySQL 内部每秒能扫描内存中上百万行数据，但 MySQL 响应数据就慢多了。

0.3 查询执行的基础

MySQL 客户端和服务器之间是“半双工”的，客户端用一个单独的数据包将查询发给服务器，而服务器响应的数据可能很多，即使不需要全部数据，客户端也必须等待服务端全部发完。这就是一定要在查询中添加限制条件的原因。

查询是数据库中最频繁的工作，提高查询速度可以有效提高 MySQL 数据库的性能。

0.3.1 MySQL 如何执行关联查询

MySQL 中 JOIN 一词所包含的意义比一般意义上理解的更要广泛，总的来说，**MySQL 认为任何一个查询都是一次 JOIN **——并不仅仅是一个查询有两个表匹配才叫关联。所以在 MySQL 中，每一个查询，每一个片段（包括子查询，甚至基于单表的 SELECT）都可能是 JOIN。

UNION 会创建临时表

JOIN 执行的策略：MySQL 对任何关联查询都执行嵌套查询，即MySQL先在一个表中循环取出单条数据，然后嵌套循环到下一个表中寻找匹配的行，以此循环下去直到匹配所有行，然后再根据各个表匹配的行，返回查询中需要的列。

从本质上说，MySQL 对所有的类型查询都以同样的方式运行。如：MySQL 在 FROM 子句中遇到子查询时，先对子查询执行查询并将结果放到一个临时表中，MySQL 在执行 UNION 时也会使用类似的临时表。在遇到右外连接的时候，MySQL 将其转化为等价的左外连接。

date mysql 优化 查询 mysql的查询优化_mysql_02

MySQL 的执行计划总是像上图所示，是一棵左侧深度优先的树。

优化 JOIN 查询：①确保 ON 子句的列上有索引；②确保任何的 GROUP BY 和 ORDER BY 中的表达式中只涉及到一个表中的列，这样 MySQL 才有可能使用索引来优化；-③尽可能使用关联查询而不是子查询（5.6后修复不需要了）-。

0.3.2 优化 LIMIT 分页

在系统中进行分页的时候，我们通常会使用 LIMIT 加上偏移量同时加上合适的 ORDER BY 子句的办法实现，如果有对应的索引，效果会非常不错。而一个常见又让人头疼的问题是当偏移量非常大的时候，例如 limit 100000，10 的时候，这很浪费资源，要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

优化此类分页查询的最简单的办法就是尽可能的使用覆盖索引，然后根据需要做一次关联操作再返回所需的列。

# 如
SELECT f_id,description FROM film ORDER BY title LIMIT 500000,50;
# 最好优化成下面的语句： 
SELECT film.f_id,film.description FROM film 
INNER JOIN (
		SELECT f_id FROM film ORDER BY title LIMIT 500000,50
) lim on film.f_id = lim.f_id;
# 这里的延迟关联将大大提升查询效率
0.3.3 优化 UNION 查询

MySQL 总是通过创建并填充临时表的方式来执行 UNION 查询。因此很多优化策略都没有办法在 UNION 查询中很好的使用。经常需要手工将WHERE、LIMIT、ORDER BY等子句“下推”到 UNION 的各个子查询中，以便更好的优化。

所以除非确实需要消除重复的行，否则就一定要用 UNION ALL，这一点很重要。如果没有 ALL 关键字，MySQL 将会对整个临时表做唯一性检查，这个代价很大。

一、SELECT 查询语句优化

  1. 分析查询语句：EXPLAIN SELECT 语句

date mysql 优化 查询 mysql的查询优化_数据库_03

查询结果解释：

select_type：表示 select 语句的类型。它可以是以下几种取值：

  • SIMPLE：表示简单查询，没有 join 和子查询；
  • PRIMARY：表示主查询，或是最外层的查询语句；
  • UNION：表示连接查询的第二个或后面的查询语句；
  • DEPENDENT UNION：同上；
  • SUBQUERY：子查询中的第一个语句；

type：表示表的查询类型，有以下几种取值：

  • ALL：全表扫描，这种扫描类型最差；
  • index：全表扫描，只不过扫描的索引树；
  • range：有范围的索引扫描；
  • ref：对于来自前面任意行组合的表中读取匹配的行；使用的索引不是唯一和主键索引。

posssible_keys：指出 MySQL 能使用表中的哪个索引找到行

key：实际使用的索引

key_len：表示MySQL选择的索引字段按字节计算的长度，如果键是NULL，则长度为NULL。注意通过key_len值可以确定MySQL将实际使用一个多列索引中的几个字段。
ref：表示使用哪个列或常数与索引一起来查询记录。
rows：表示MySQL在表中进行查询时必须检查的行数。
Extra：表示MySQL在处理查询时使用索引的详细信息

2. 索引

MySQL 中提高性能最有效的方式就是索引。如果没有索引将会全表扫描，使用索引可以快速的定位表中的某条记录，MySQL 默认使用 InnoDB 存储引擎，也就是说索引数据结构都是 B+ 树。

但是并不是所有带有索引字段的查询都会优先查询索引，以下几种情况会使索引失效：

  1. 使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，则索引不会生效，优化方法是，尽量在字段后使用模糊查询
  2. 当 where 条件中使用 IN 时，当 IN 中的数据较少时，会走索引，当数据匹配到数据库中较多数据时就会全表扫描；
  3. 使用 OR 时也最好前后字段均有索引才会走索引，当多表查询时，不同的表中的字段分别有索引时，使用 UNION ALL；
  4. 优化子查询，将子查询改写为连接查询，因为子查询会创建一个临时表
  5. 为指定字段设置了非空（not null），在使用 * ，is null 和 is not null 是不走索引的，这时可以将这个字段换为默认值更好，而列定义为允许为空，则查询中是可以用到索引的。
  6. 少用 != <>，也会造成索引失效，但是业务需要也可以用；
  7. 连接查询中，优先使用 INNER JOIN ；使用 LEFT JOIN，左边表要小；
  8. 尽量避免在 where 条件中的等号左边含有表达式、函数，应将表达式、函数操作移到右边：score/10 = 2 => score = 2*10 和 date_add(birthday, interval 7 day)>=now() => birthday >= date_add(now(),interval 7 day);
  9. 使用聚簇索引时，保持最左前缀原则；

3. 查询条件优化

  1. 优化 group by 语句：MySQL 会对 group by 分组的所有值进行排序，即使你没有写 order by。所以你不想要排序 group by 之后就 order by null。
  2. 子查询使用 JOIN 替代总是不错的。
  3. MySQL 通过创建并填充临时表的方式来执行 UNION 查询，所以除非有必要消除重复的行，否则建议使用 UNION ALL。原因是如果没有 all 关键字 MySQL 会给临时表加上 distinct 选项，会做唯一性校验，消耗很高。
  4. 清空表时优先使用 truncate 而不是 delete

二、增删改DML 语句优化

  1. 插入语句使用这种方式：INSERT INTO X VALUES(1,2),(1,3),(2,3)
  2. 查询优先还是更新（insert、update、delete）优先？MySQL 允许改变语句调度的优先级，MySQL 默认的调度策略如下：①写入操作优先于读取操作②对某张数据表的写入操作某一时刻只能发生一次③多个读取操作可以同时进行

三、SQL 优化实战

最近项目一直有一个问题，就是一个查询等待的时间很长，之前是别人写的 SQL，因为刚刚学了优化自告奋勇来做SQL的优化，嘻嘻😁。没有优化前，SQL 的查询速度在几十万条数据的多表查询（六个join，七张表）达到了将近70秒，优化后正式环境查询只用了5秒左右，对于一个初级的我来说真的很自豪。

刚开始的思路和上面查询优化总结的一致，①尽量在去掉子查询（⚠️注意：这不一定是对的！） ，这个花费了很多时间；②将关联的字段以及表的筛选条件进行添加索引。索引单表的时候还有效果，但是 JOIN 的表一多，速度立马就降了下来。走投无路之时在网上看到了这篇文章查询优化，也是多表查询，文章的最后总结一下点拨了我：这里学习的不是这种操作，而是怎样去减少数据量的思想，运行效率不会无故变慢，一定是查询过程中操作的数据量过大，只要能减少查询过程中对数据量的操作都是优化！

根据上面的思路进行数据量削减，首当其冲的不是其他就是第一张左表，在 FROM 时添加条件将表的数据量直接削减N倍（使用了子查询），后面的表也类似，速度直接0.3秒左右（测试环境）。