MySQL的查询性能优化——《深究MySQL》

素颜马尾好姑娘i 2022-05-26 20:15 280阅读 0赞

更新中。。。。。。。

## \#写在前面  ##

**mysql查询缓慢的原因有哪些？**  
1.查询语句是否请求了不必要的多余数据  
2.  
**总结以上原因之后，优化数据库性能，就需从以下几个方面着手：**  
1.

说明：下面的知识为零散的记录，后期需要整理

# 一、Mysql优化  #

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

### 1.优化insert和update ###

**1.1 批量插入**：将多条要插入的数据合并成一条

*  合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率；
 *  通过合并SQL语句，减少网络传输的IO；
 *  通过合并SQL语句，减少SQL语句解析的次数；

注意事项：数据库sql长度是有限制，sql长度过大会溢出报错。可通过max\_allowed\_packet配置修改，默认是1M

**1.2 事务插入**：在开始插入前开启事务，插入完的提交事务

*  进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗；

注意事项：事务日志大小也有限制，过大会刷磁盘，导致性能下降。可通过innodb\_log\_buffer\_size配置修改

**1.3 主键顺序插入**：前面插入数据和主键和后插入数据的主键是有序的

*  如果有序，新插入的记录直接定位在索引的最后面，索引的定位效率会很高，也且对索引调整也较小。
 *  如果无序，新插入的记录可能要定位到索引中间，索引定位效率下降，而这时还需要B+tree进行分裂合并等处理，会消耗比较多计算资源，数据量较大时会有频繁的磁盘操作。

参考：[https://www.2cto.com/database/201707/654686.html][https_www.2cto.com_database_201707_654686.html]

### 2. in 和 exsits的区别和效率 ###

参考：[https://www.cnblogs.com/emilyyoucan/p/7833769.html][https_www.cnblogs.com_emilyyoucan_p_7833769.html]  
总结如下：  
select a.\* from A a where exists(select 1 from B b where [a.id=b.id][a.id_b.id])  
exists()适合B表比A表数据大的情况

select \* from A where id in(select id from B)  
in()适合B表比A表数据小的情况

### 2.not in 和not exists ###

如果查询语句使用了not in ,那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

### 3. 要建索引的MySQL字段尽量设置为NOT NULL ###

含有空值的列很难进行查询优化，而且表索引是不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。  
因为它们使得索引、索引的统计信息以及比较运算更加复杂。  
你应该用0、一个特殊的值或者一个空串代替空值。

### 4.负向查询不能使用索引 ###

反例：select name from user where id not in (1,3,5);  
正例：select name from user where id in (2,4,6);

### 5. 前导模糊查询不能使用索引 ###

反例：select name from user where name like '%zhang ’  
正例：select name from user where name like ‘zhang%’

### 6. 不对索引字段进行计算（不然索引会失效） ###

反例：select name from user where FROM\_UNIXTIME(create\_time) < CURDATE();  
正例：select name from user where create\_time < FROM\_UNIXTIME(CURDATE());

### 7.保证参数的数据类型与库一致（不然会作强制转换） ###

库里 phone字段为varchar  
反例：select name from user where phone=18810503732;  
正例：select name from user where telno=‘18810503732’

### 8. 避免复合索引失效 ###

说明：user表的username 和phone 字段创建了复合索引，且username在前面。  
能利用到索引的SQL：  
select username from user where username=‘zhangsan’ and phone =‘18810503732’

select username from user where phone =‘18810503732’ and username=‘zhangsan’

select username from user where username=‘zhangsan’

不能利用到索引的SQL：

select username from user where phone =‘18810503732’

这个类似于电话本，你先以用户名字作了目录，再按电话号作的目录。如果你先查电话的目录的话，肯定是不行的。你只能先安用户名字去搜索。

### 非同步方法会不会受同步方法的影响？ ###

### SQL语句中or的使用方法注意事项 ###

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

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

#  #

###  ###

### 1. 优化count查询 ###

**1.尽量使用count(\*)。当然也可以尝试索引覆盖，此时最好是主键。**

count(columnName) 虽然也可以求出行数，但是它有一个致命的问题，那就是不统计为NULL的列。所以如果用列名代替\*求行数的方式可能会出现统计错误。

###  ###

### 2.优化关联查询 ###

**1.确保在ON或USING的关联顺序中第二个表的关联列有索引**

例如，当A表和B表的关联顺序是A、B，即SQL语句为select \* from A left join B on A.cid = B.cid 时，需要保证在B表的cid列表创建索引，而A表的cid列有无索引对性能没有影响。

**2. 尽量让group by 和order by 的表达式中只涉及到一个表中的列**

分组的字段在一个表，排序的字段又在另外一个表中，这样就很难利用到索引。

###  ###

### 3.优化子查询 ###

**1. 尽量使用关联查询代替子查询**

子查询会创建临时表，而临时表又没有任何索引

**2. 子查询优化的基本思路，就是消除嵌套层次，把子查询与父查询放在同一个层次去执行。**  
实现步骤：

把子查询的FROM子句中的表对象，与上层的父FROM子句中的表对象做JOIN

再把子查询的WHERE子句中的条件表达式，与上层的父WHERE子句中的条件表达式用“AND”操作符连接

###  ###

### 4.优化group by ###

**1. 不关心顺序时，尽量用order by NULL 避免group by的默认排序（从而避免了文件排序）**

group by子句如果没有指定排序列，则会自动根据**分组列**进行默认升序排序，从而导致了文件排序。

有个一般人不会用的方式，那就是直接在 group by 字段A 后面加上ASC或DESC关键字，使得分组结果按分组列（字段A）进行升序或降序排序。  
2. group by要求返回的所有字段，要么出现在聚合函数（avg、sum、count、max、min等）中，要么出现Group By后面作为分组依据，不然高版本的mysql会报错。这是因为分组后，没有通过聚合函数聚合的多行数据不知道怎么存放在一行了，高版本时可通过配置文件设置忽略，从而只选择一行来存。  
**3. 注意：group by是先排序再分组，且是分组后再聚合。**

###  ###

### 4.1优化group by with rollup ###

**可通过explain查看执行计划，决定是否加with rollup**

with rollup表示需要做超级聚合，即可统计分组结果中每组数据分组前的某些值，如我们需要统计各部门员工工资的平均值  
select depId, avg(money) from user group by depId with rollup;

需要注意的是，用了with rollup后不能再使用order by关键字，但可以直接在分组字段后加asc或desc

参考：[https://blog.csdn.net/id19870510/article/details/6254358][https_blog.csdn.net_id19870510_article_details_6254358]

###  ###

### 5.优化limit ###

**1.尽量利用索引覆盖做“延迟关联"**(延迟关联也可以优化关联查询的limit子句)

在做分页查询时，如果遇到像limit 2000,20这样偏移量大的查询时，mysql会实际查询2020条数据，然后抛弃前面2000条数据，只返回最后20条。这样做的代价非常高。

limit 2000,20 这样的分而之所以会慢，是因为offset。offset会导致mysql扫描大量不需要的行然后抛弃掉。所以平时我们看到 limit 1 这种写法是与offset无关的，也就是说这种写法不会很慢。顺便提一下，limit 2000,20 是等价于 limit 20 offset 2000的。

###  ###

### 6.优化union查询 ###

**1.如果不需要对查询结果去重的话，尽量使用union all 而不是union。**

union后没跟all 时，会自动给临时表加上distinct做唯一性检查，从而降低性能。

**2.巧用 limit**

(SELECT \* from sys\_user where id < 2000 order by office\_id ) UNION (SELECT \* from sys\_user WHERE id >2000 order by office\_id ) order by office\_id desc limit 20;

两个括号里的子查询都会将各自的数据全部放在一个临时表中，然后从临时表中取出20条数据，这时可以**将每个子查询都用上limit 20** ：

(SELECT \* from sys\_user where id < 2000 order by office\_id limit 20) UNION (SELECT \* from sys\_user WHERE id >2000 order by office\_id limit 20 ) order by office\_id desc  limit 20;

这样一来，临时表中就只有40条数据了。需要注意的是，从临时表取数据的顺序是不确定的，所以要想按自定义的顺序取，必须在全局加上order by ，如上面SQL上的红色字。

###  ###

### 7.优化 in 子查询 ###

**1.将in子查询用innor join改写成关联查询**

IN的子查询在实际执行的时候会被mysql自动改写成较缓慢的查询（mysql有时候也会自以为是的，哈哈），如下查询

select \* from film where film\_id IN(select film\_id from film\_actor where actor\_id = 1)

会被改成写：

select \* from film where film\_id **exists** (select film\_id from film\_actor where actor\_id = 1 and film\_actor.film\_id = film.film\_id )

从改写后的语句可以看到，子查询里面**涉及到外部表** film 的film\_id字段了，所以mysql会先对外部表film进行全表扫描，通过explain执行计划也可以看到。

优化后的写法为：

select \* from film **inner join** film\_actor ON film.film\_id = film\_actor.film\_id WHERE film\_actor.actor\_id = 1;

###  ###

### 8.优化 MAX()、MIN() ###

**1. 通过order by 和limit来优化**

如下，我们需要获取最大值和最小值（前提是id值有索引）

优化前：  
最小值：select **min(id)** from actor;  
最大值：select **max(id)** from actor;

优化后：  
最小值：select id from actor order by id **asc** limit 1;  
最大值：select id from actor order by id **desc** limit 1;

从explain可以看出，虽然优化前和优化后都用到了索引，但是min和max函数进行了大量的扫描，而优化后只扫描了一行。

###  ###

### 9.优化OR查询 ###

**用UNION替换OR (仅适用于索引列)**

对索引列使用OR将造成全表扫描。注意,该规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。

在下面的例子中, LOC\_ID 和REGION上都建有索引：  
**高效**: select log\_id , log\_desc , region from log where log\_id = 10 **UNION**  
select log\_id , log\_desc , region from log where region= “update”

**低效**: select log\_id , log\_desc , region from log where log\_id = 10 **OR** region = “update”

如果你坚持要用OR, 那就建议你将返回记录最少的索引列写在最前面。

[https_www.2cto.com_database_201707_654686.html]: https://www.2cto.com/database/201707/654686.html
[https_www.cnblogs.com_emilyyoucan_p_7833769.html]: https://www.cnblogs.com/emilyyoucan/p/7833769.html
[a.id_b.id]: http://a.id=b.id
[https_blog.csdn.net_id19870510_article_details_6254358]: https://blog.csdn.net/id19870510/article/details/6254358