高性能mysql（第三版）笔记（二）

た入场券 2022-10-17 10:54 164阅读 0赞

### 查询性能优化 ###

#### 确认应用程序是否在检索大量超过结果行的数据。 ####

> 这通常意味着访问了太多的行，或者太多的列

1.  可以使用limit
2.  关联查询表字段写所需要的字段

#### 确认`mysql`服务器层是否在分析大量超过需要的数据行； ####

1.  使用覆盖索引，把需要查询的列放到索引中
2.  改变库表结构
3.  重写这个复杂的查询

#### 将复杂而大的查询分解多个小的查询 ####

> **以前总认为网络通信，查询解析和优化是一件代价很高的事情，但是这样的想法对于MySQL并不适用，mysql从设计上让连接和断开连接都很轻量级，再返回一个小的查询结果方面很高效，有时候考虑将一个大的查询分解成多个小的查询是很有必要的**

1.  比如分解大的删除语句
2.  分解关联查询

#### 查询执行的基础原理\*\*\* ####

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UX9rD2Gg-1622622158417)(en-resource://database/2664:1)\]

> `mysql`客户端和服务端基于半双工通信协议，这意味着任意时刻，要么由服务器向客户端发送数据，要么就相反；

##### 关联查询 #####

> `MySQL`认为任何一个查询都是一次“关联”，并不仅仅是一个查询需要到两个表匹配才叫关联，所以在`MySQL`中，每一个查询，每一个片段（包括子查询，甚至基于单表查询）都可以是一次关联  
> 关联查询的本质是 “嵌套循环查询” 就是`for`循环里面嵌套`for`循环  
> 一般来说在关联查询中，需要在顺序为第二个表的关联字段中添加索引

**子查询中, 如果子表 大的话 可以使用`exists` 更合适,原因是 `exists`只会遍历外表的长度,然后判断子表的子查询是否为 `true` 而`in` 则会嵌套循环遍历外表和子表,以便找到结果, 所以子查询中子表小的话则可以使用`in`**

##### Union #####

> 在使用`union`查询时, 可以在每个查询子句中使用`limit`以此来减少从数据库查询的数据量  
> 尽量使用UNION ALL, 这也可以在查寻出结果放入临时表时,不会使用 `distinct`过滤数据. 而UNION 则会使用`distinct`

##### 当需要查询某一列不同类型的数量时，可以使用count,或者SUM(IF(expr)) #####

SELECT
    	COUNT(delete_status = 1 OR NULL) AS del,
    	COUNT(delete_status = 0 OR NULL) AS normal
    FROM
    	pms_product

SELECT
    	sum(IF(delete_status= 1 , 1, 0)) AS del,
    	sum(IF(delete_status= 0 , 1, 0)) AS normal
    FROM
    	pms_product;
        
     --- 或者
    SELECT
    	sum(delete_status= 1 ) AS del,
    	sum(delete_status= 0 ) AS normal
    FROM
    	pms_product

##### 5.6以下mysql版本中,使用关联查询比子查询好些 #####

##### 临时表时没有任何索引的 #####

##### GROUP BY优化 #####

> 如果没有通过 `ORDER BY`子句显式的指定排序列, 当查询使用`GROUP BY`子句的时候, 结果集会自动按照分组字段进行排序,如果不关心结果集的顺序,而这种默认排序又导致了文件排序"filesort", 则可以使用 `ORDER BY NULL`

##### 优化limit #####

> 常见的limit查询为:

SELECT film_id, descreption FROM sakila.film ORDER BY title LIMIT 50,5;

如果这个表非常大,那么这个查询最好改写成下面的样子:

SELECT film.film_id, film.description FROM sakila.film INNER JOIN ( SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5 ) AS lim USING(film_id);

上面的语句使用覆盖索引查询id,让`mysql`扫描尽可能少的页面

**优化策略**

1.  如果已经知道`limit`查找访问,可以使用范围查找, 或者可以使用书签获取记录上次取数据的位置,下次就可以直接从该书签记录的位置开始扫描
2.  可以使用缓存,先存储一千行数据,满足查询需求, 之后如果查询大于一千行则另外加载一千行缓存;