《高性能MySQL》读书笔记（下）

小咪咪 2023-09-24 20:23 52阅读 0赞

**目录**

Mysql查询性能的优化

慢查询基础

优化数据访问

是否向数据库请求了不需要的数据

查询了不需要的记录

多表联查中返回全部列

MySQL是否在扫描额外的记录

重写查询的方式

切分查询（重点）

分解连接查询（重点）

MySQL如何执行联接查询

查询优化器

排序优化（重点）

MySQL查询优化器的局限性

优化特定类型的查询

优化count()查询（重点）

优化联接查询

优化大量数据的limit分页（重点）

优化union查询（重点）

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

## Mysql查询性能的优化 ##

首先要清楚为什么查询速度会慢？如果我们把一个查询看作一个任务，那么这个任务它就是由一系列子任务组成，每个子任务在执行的 时候都会消耗一定的时间，如果要优化查询的执行效率，实际上就是要优化其子任务，**要么是消除一些子任务，要么减少子任务的执行次数，要么让子任务执行更快。**

**简单的来说**，一个查询的生命周期如下：从客户端到服务器，然后在服务器上进行语法解析，生成执行计划，**执行**，并给客户端返回结果。其中**执行**可以被认为是整个生命周期中最重要的阶段，其中**包含了大量**为了检索数据对存储引擎的**调用**以及**调用后的数据处理**，**包含排序，分组**等。

在每一次消耗大量时间的查询案例中，我们都可以看到一些不必要的操作，比如**某些操作被额外地执行重复了很多次，某些操作执行得太慢**等，优化查询的目的就是消除这些操作花费的时间。

### 慢查询基础 ###

#### 优化数据访问 ####

一条查询语句，如果性能很差，最常见的原因就是访问的数据太多，对于低效的查询，我们通常可以从下面两个步骤来进行分析：

*  确认应用程序**是否在检索大量且不必要的数据**（后面会讲怎么判断）。这通常意味着访问了太多行，但有时候也可能是访问了太多的列。
 *  确认MySQL服务器是否在**分析（比如：排序，分组）大量不需要的数据行**。

#### 是否向数据库请求了不需要的数据 ####

一些查询会请求超过实际需要的数据，然后**这些多余的数据会被应用程序丢弃，并且这个过程会增加网络开销**，同时这也会消耗应用服务器的CPU和内存资源。

#### 查询了不需要的记录 ####

MySQL查询是会先返回全部的结果集然后再进行计算，比如，一条查询语句查询出100条数据，但是只需要在页面上显示前10条数据，实际情况来说，**MySQL会先查询出这100条数据，然后把这100条数据全部返回给客户端**，然后再抛弃其中的大部分数据。

#### 多表联查中返回全部列 ####

能不要select \* 就不要select \* 。需要什么就查询什么列。如果**取出全部列，会让优化器无法完成索引覆盖扫描**这类优化，还会为服务器带来额外的IO，内存和CPU消耗。

#### MySQL是否在扫描额外的记录 ####

在确定查询只返回需要的数据后，接下来就需要看看查询为了返回我们需要的结果**是否扫描了过多的数据**，对于MySQL来说，最简单衡量查询开销的三个指标如下：

*  响应时间（具体的响应时间难以估计，只能通过一些经验方法大概估计，这里不再赘述）
 *  **扫描的行数**
 *  **返回的行数**

这三个指标会被记录到**MySQL的慢日志**中。需要注意的是：扫描的行数与返回的行数的比率通常很低，**一般**是在1:1或者是1:10。

![999e195204784f21b04c00458d42ec9b.png][]

![b316f13197e740f7a4839eec179da21f.png][]

这个type的值，除了All和const之外的其他种类都是走索引的，all表示全表扫描，const表示的是常量查询。

一般来说，MySQL能够使用如下三种方式应用where条件，从好到坏如下：

1.  在**索引中**使用where条件来过滤不匹配的记录。这是在存储引擎中完成的。
2.  使用**索引覆盖**（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，不需要再进行回表操作。
3.  **从数据表中**返回数据，然后过滤不满足条件的记录（在Extra列中出现了Using where）。这在MySQL服务器层完成，MySQL需要从表中读取数据然后再进行过滤。

### 重写查询的方式 ###

设计查询的时候，一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。MySQL从设计上让连接和断开连接都是非常轻量的，返回一个小的查询结果是很高效的，而且现代的网络速度比以前要快很多，能在很大程度上降低延迟，所以运行多个小的查询现在已经不是大问题了。在其他条件都相同的情况下，使用尽可能少的查询当然是更好的。但是有时候，将一个大的查询分解为多个小的查询是很有必要的。

#### 切分查询（重点） ####

有时候对于一个大查询，我们需要将大查询切分成小查询，每个查询的功能完全一样，只完成一小部分，每次只返回一小部分查询结果。最常见的案例就是：删除旧的数据，比如需要定期清楚大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小的但重要的查询。**此时我们可以将一个大的delete 语句切分成多个较小的查询（通过使用limit来限制每一次处理的数据量，然后通过程序中的循环来进行控制）**，可以尽可能小的影响MySQL的性能。

-- 比如需要每个月运行下面的SQL语句 data_sub()是一个函数,可以通过时间偏移量来进行运算，now()返回配置的时区的当前日期和时间，Interval用于添加和减去日期和时间值
    delete from TableA where created < data_sub(now(),3 month);
    
    -- 可以把上面的逻辑改写成如下
    rows_affected = 0;
    do{
     -- do_query是一个执行SQL语句的方法 
     rows_affected = do_query(
     "delete from TableA where created < data_sub(now(),3 month) limit 10000")
    }while rows_affected > 0;

这样删除数据还有一个好处：将服务器上原本一次性的压力分散到一个很长的时间段中，可以大大减低对服务器的影响，还可以大大的减少删除时锁的持有时间。

#### 分解连接查询（重点） ####

如果多张表的连接查询速度非常的慢，我们**可以对每一个表进行一次单表查询，然后将结果在应用程序（比如在java代码中进行多次的单表查询，自己在实习的时候就这样干过）中进行连接。**使用多个单表查询的优势如下：

*  可以让缓存的效率更加高
 *  将查询分解后，执行单个查询可以减少锁的竞争。
 *  可以减少对冗余记录的访问。因为在应用程序中（代码中）做连接查询，意味着对某条记录的访问只需要查询一次，而在数据库进行连接查询的话，则可能需要重复发访问一些数据。
 *  而且在对多个值进行查询的时候，我们可以控制其访问顺序，比如**在in()中使用顺序读来访问MySQL中的数据**，这比随机读的效率要高很多。
 *  在应用程序中进行联接处理可以更加容易的对数据进行拆分，可以更加容易的扩展程序。

注意：并不是所有的联接查询都需要进行拆分的，不要为了拆分而进行拆分，下面的一些场景使用在应用程序中进行联接查询可能速度更加快：

*  可以利用缓存和重用之前的查询结果时
 *  当能够使用in（）列表代替联接查询大型表时
 *  当一次查询中多次引用同一张表时
 *  在多台服务器分发数据的时候

注意：即便在使用联接查询，如果**联接查询是同名字段作为联接条件**，那么使用using（）函数进行联接**效果比on的效果更加**好。比如Using(id) <=> on A.id = B.id。（因为在**同样的联接查询语句中**，使用on来进行连接比使用using进行连接要多出几次联接字段的扫描，比如使用on联接id，可能id会在执行计划中出现两次，但是如果使用using来进行id的联接，那么id在执行计划中只会出现一次）

### MySQL如何执行联接查询 ###

（这里涉及到一个重要的概念：临时表）

对于union查询，MySQL会先将一系列的单个查询结果放到一个**临时表**中，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。最后根据各个表匹配的行，返回查询中需要的各个列。

![1ada816afb66483b8ad929d366025823.png][]

查询执行的基础（大概）：

1.  客户端给服务器发生一条SQL语句。
2.  服务器端进行SQL语句的解析，预处理，然后再由优化器生成对应的执行计划。
3.  MySQL根据优化器生成的执行计划，调用**储存引擎的API来执行查询**。
4.  将查询的结果返回给客户端。

MySQL的客户端和服务器之间的**通信协议是“半双工”的**，这意味着，任何时刻，要么是由服务器向客户端发送数据，要么是客户端向服务器发送数据，**这两个动作是不能同时发生的**。这种模式的通信也意味着，**一旦一端开始发送消息了，另一端要接收完整个消息才会响应它**，同时也意味着MySQL是没法进行流量控制的。

所以在查询的时候，**如果我们只是想获取查询结果中的前几条数据或者是后几条数据，那么此时最好的做法就是使用【limit】来进行限制**，否则我们可能只是需要10条数据，但是由于没有使用limit进行限制，那么MySQL服务器是会把所有的查询结果都返回给客户端的。

## 查询优化器 ##

下面这个语句可以用来查询当前会话的一个大概成本 查询结果中的value值表示的时候执行上面的SQL语句大概需要做value个数据页的随机查找才能完成上面需要的查询，这个得到的value中只是一个简单的参考值，因为优化器不会考虑缓存带来的影响，而且此时的MySQL也不知道哪些数据在内存，哪些数据在磁盘等。

show status like 'Last_query_cost';

下面是MySQL优化器能够处理的一些优化类型：

1.  重新定义联接表的顺序
2.  将外联接转换成内联接
3.  用代数等价变化规则进行替换
4.  **优化count（），Min（），MAX（），因为最值一般是索引中的最右边或者是最左边一列的值，所以MySQL在优化器的时候会把这个最值当做常数来进行处理。**
5.  预估并且转化为常数表达式，比如在对主键列进行where条件的访问，那么优化器就会知道这个值已经是确定的，那么访问的类型就会转化为const（在explain解析中的type列也可以看到其值为const）。
6.  **覆盖索引扫描**，当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行（就是不需要进行回表了）；
7.  子查询优化
8.  **提前终止查询。比如，早就已经查询到需要的结果了，那么MySQL会立刻终止查询，一个典型的案例就是limit查询。**
9.  等值传播
10. **列表in()的比较。在其他数据库中in()和or几乎是等效的，但是在MySQL这里不一样，因为MySQL将in（）列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个o(logn)的复杂度，等价转化为OR查询的话复杂度变成了O(n)，所以在对于列表中有大量取值的时候，MySQL使用in()的效率会更加高。**

### 排序优化（重点） ###

排序本身就是一个成本很高的操作，所以从性能角度考虑，应该尽可能的避免排序或者是尽可能避免对大量数据进行排序。

当**不能使用索引**生成排序结果的时候，MySQL需要自己进行排序，**如果数据小于‘’排序缓冲区“则在内存中进行快速排序**，如果数据量大于‘’排序缓冲区“，那么MySQL会先对数据进行分块，对每个独立的块使用”快速排序“进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并，最后返回排序结果，这一个过程统称为文件排序。

在联接查询的时候如果需要进行排序，MySQL会分两种情况来处理：

*  如果order by子句中的所有列都是来自联接的第一个表，那么MySQL在联接处理第一个表的时候就会进行文件排序，这时就可以在explain中看到extra列中有using filesort；
 *  除此之外的所有情况，MySQL都会先将联接的结果存储到一个临时表中，然后在所有联接都结束后，再进行文件排序，这时就可以在explain中看到extra列中有“using temporary；using filesort"；如果查询语句中有limit的话，limit也会在文件排序之后进行应用。

### MySQL查询优化器的局限性 ###

union的限制：MySQL无法将限制条件从union外层下推到内层。

如果你希望union的各个子句能够根据limit只取部分结果集，或者是希望先排好序再合并结果集的话，就**需要在union的各个子句分别使用这些子句。**案例：你想要将两个子查询的结果联合起来，然后再取前20条记录。（这个需求：MySQL会将两个表存放到同一个临时表中，然后再取出前20行的记录），下面将展示两种写法：

(select first_name,last_name from actor order by last_name)
    union all
    (select first_name,last_name from customer order by last_name)
    limit 20;
    这种写法会先把actor表中的记录和customer表中的记录存放到一个临时表中，然后再从临时表中取出前20条数据。假设两张连接表中都有1000条数据，那么此时这个临时表就会有2000条左右的数据。

(select first_name,last_name from actor order by last_name limit 20)
    union all
    (select first_name,last_name from customer order by last_name limit 20)
    limit 20;
    如果是按照这种写法，那么临时表中只会存在40条数据，大大的减少了不必要的数据的扫描，这里需要注意一下，从临时表中取出的数据并不是一定的，如果想要获取到正确的顺序，那么还需要在limit之前加一个全局的排序操作。

## 优化特定类型的查询 ##

### 优化count()查询（重点） ###

首先我们要先了解一下count（）这个函数的作用：

*  统计**某列的值的数量**，也可以统计行数；**count函数中可以是列名也可以是列的表达式。**

在统计列值时不统计null，比如 select count(od.setmeal\_id) from order\_detail od 输出的结果就是2；也就是说如果在count（）的括号中指定了列或者是列的表达式，那么统计的就是这个表达式有值的结果数。

![698d173c477a45dfa8282938b4c0301e.png][]

*  count（）还可以统计**【结果集】的【行】数**；当MySQL确认括号内的表达式不为空时，实际上就是在统计行数。比如当我们使用
    
    count（\*）时，这种情况下通配符 \* 不会去统计所有的列，它会**忽略所有列而直接去统计满足结果集的行数。**

-- 统计输出结果是11 为这张表的所有数据
    select count(*) from order_detail od 
    
    -- 统计输出结果为3 为结果集的行数
    select count(*) from order_detail od  where od.order_id  ='1522581871770824706'

简单优化count（）：

案例：**如何在一个查询中统计同一列的不同值的数量。**（这个是比较常见的案例），比如统计价格大于500的数量和价格小于100的数量：

**下面两条 SQL语句的结果是一样的；**

select sum(if(od.amount > 500,1,0)) as expensive_goods ,sum(if(od.amount < 100,1,0)) as fair_goods
    from order_detail od 
    
    select count(od.amount > 500 or null) as expensive_goods, count(od.amount < 100 or null) as fair_goods
    from order_detail od

需要注意的是，在进行统计的时候，如果使用的是sum函数，那么条件为真就是进行加一，条件为加就是进行加0，而使用count函数来进行这样的统计的话，条件为真的不需要进行处理，条件为假需要对其设置为null，**所以后面的or null是一定要加的。**

### 优化联接查询 ###

注意的是，MySQL的查询优化器会帮我们调整联接查询的表的顺序；通常在进行多表联查的时候，可以有多种不同的联接顺序来获得相同的执行结果，MySQL的联查优化器通过评估不同顺序时的联查成本来选择一个成本最低的联接顺序。一句话概括就是：小表驱动大表（**小表作为驱动表，表中的每条数据只查询一次，而被驱动表中的数据会被多次重复查询**）。这样可以让查询进行更少的回溯和重读操作。如果你不想使用MySQL优化器提供的顺序，那么可以使用straight\_join关键字来重写查询。

*  确保on或者是using子句中的列有索引。在创建索引的时候就要考虑到连接的顺序。当A表和B表通过c列来进行联接的时候，如果优化器的联接顺序是B,A ，那么就不需要在B表的对应列创建索引。没用用到的索引只会带来额外的负担。一般来说，只需要在联接顺序（这里的联接顺序指的是优化器的联接顺序）中的第二个表的相应列创建索引。
 *  确保任何group by和order by中的表达式只涉及到一个表中的列，这样MySQL才可能使用索引来优化这一个过程。

#### **优化大量数据的limit分页**（重点） ####

在偏移量很大的时候，比如limit 10000，20，这是MySQL需要查询10020条数据最后只返回20条数据，前面的10000条记录都将被抛弃，这样代价非常高。如果这个表的数据非常大，那么我们可以尽可能的使用索引覆盖来进行扫描，而不是查询全部的行。

-- 一般的查询
    select film.film_id,film.description from sakila.film order by title limit 50,5;
    
    -- 优化后的查询  通过延迟连接来进行优化
    select film.film_id,film.description from sakila.film
    inner join (
        select film.film_id from sakila.film order by title limit 50,5;
    )as lim using(film_id);

优化之后的SQL查询之所以有效，是因为它**允许服务器在不访问行的情况下检查索引中尽可能少的数据。（里面的子查询只是查询了一个film\_id，这样可以直接走索引进行查找数据）**然后，**一旦找到需要的行，就将它们与整个表联接，以从该行中检索其他我们需要的列**。

#### 优化union查询（重点） ####

MySQL总是通过创建并填充临时表的方式来执行union查询，如果你不需要MySQL帮我们消除重复的行，那么一定要使用union all来进行连接查询。如果没有all关键字，MySQL会给临时表加上distinct选型，这会导致对整个临时表的数据进行去重检查。虽然即便有all关键字，MySQL仍然会使用到临时表来存储结果。

**UNION 语句**：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

**UNION ALL 语句**：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

[999e195204784f21b04c00458d42ec9b.png]: https://img-blog.csdnimg.cn/999e195204784f21b04c00458d42ec9b.png
[b316f13197e740f7a4839eec179da21f.png]: https://img-blog.csdnimg.cn/b316f13197e740f7a4839eec179da21f.png
[1ada816afb66483b8ad929d366025823.png]: https://img-blog.csdnimg.cn/1ada816afb66483b8ad929d366025823.png
[698d173c477a45dfa8282938b4c0301e.png]: https://img-blog.csdnimg.cn/698d173c477a45dfa8282938b4c0301e.png