MySQL 8.0 版本 SQL查询优化
--------
1. **对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引**
---
2. **应尽量避免在 where 子句中对字段进行表达式操作，应尽量避免在where子句中对字段进行函数操作**
>例如：下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢：
```sql
SELECT * FROM record WHERE substrINg(card_no,1,4)=’5378’ (13秒)
SELECT * FROM record WHERE amount/30< 1000 （11秒）
SELECT * FROM record WHERE convert(char(10),date,112)=’19991201’ （10秒）
```
>**分析：**
>WHERE子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：
```sql
SELECT * FROM record WHERE card_no like ‘5378%’ （< 1秒）
SELECT * FROM record WHERE amount< 1000*30 （< 1秒）
SELECT * FROM record WHERE date= ‘1999/12/01’ （< 1秒）
```
---
3. **尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销；
尽可能的使用 varchar/nvarchar 代替 char/nchar ， 因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些**
4. **使用表的别名(Alias)：当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误**
---
5. **在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数**
---
6. **当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新!**
---
7. **提高GROUP BY语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果，但第二个明显就快了许多**
- 低效:
```sql
SELECT JOB , AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING JOB =’PRESIDENT’
OR JOB =’MANAGER’
```
- 高效:
```sql
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB =’PRESIDENT’
OR JOB =’MANAGER’
GROUP BY JOB
```
---
8. **选择表合适存储引擎：**
- myisam: 应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。
- Innodb： 事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。（Innodb有效地降低删除和更新导致的锁定）。对于支持事务的InnoDB类型的表来说，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打开也可以），将大大提高性能。
---
9. **优化表的数据类型,选择合适的数据类型：**
- **原则：** 更小通常更好，简单就好，所有字段都得有默认值,尽量避免null。
- 例如：数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型.(mediumint就比int更合适)
- 比如时间字段：datetime和timestamp, datetime占用8个字节，而timestamp占用4个字节，只用了一半，而timestamp表示的范围是1970—2037适合做更新时间
- MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。
- 因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如， 在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间， 甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话， 我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。 应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理， 而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
---
10. **尽量避免使用子查询**
有以下子查询示例：
```sql
SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
```
- 你肯定认为这个 SQL 会这样执行：
```sql
1、SELECT t2.b FROM t2 WHERE id < 10;
2、得到结果: 1,2,3,4,5,6,7,8,9
3、select * from t1 where t1.a in(1,2,3,4,5,6,7,8,9);
```
但实际上 MySQL 有可能并不是这样做的，可能会根据表的不同大小进行优化。例如MySQL 会将相关的外层表压到子查询中，优化器认为这样效率更高。也就是说，优化器会将上面的 SQL 改写成这样：
```sql
select * from t1 where exists(select b from t2 where id < 10 and t1.a=t2.b);
```
因此，当外表的数据很大时，查询速度会非常慢。
>**MySQL子查询优化的技术或优化策略，包括三种**，分别为：
>1. **semi-join**：半连接优化技术，本质上是把子查询上拉到父查询中，与父查询的表做 **semi-join** 的操作。关键词是“**上拉**”。
- 注：所谓的semi-join是指semi-join子查询。 当一张表在另一张表找到匹配的记录之后，半连接（semi-jion）返回第一张表中的记录。与条件连接相反，即使在右节点中找到几条匹配的记录，左节点的表也只会返回一条记录。另外，右节点的表一条记录也不会返回。Hive中专门有这种连接。
>
>```
>MySQL提供5种优化策略，来进一步优化semi-join操作，分别是：
>- DUPS_WEEDOUT/重复剔除
>- LOOSE_SCAN/松散扫描
>- FIRST_MATCH/首次匹配
>- MATERIALIZE_LOOKUP/索引式物化
>- MATERIALIZE_SCAN/扫描式物化
>这5种子优化策略，需要通过代价估算完成最优选择。
>```
>2. **Materialization**：物化子查询，子查询的结果通常缓存在内存或临时表中
>3. **EXISTS strategy**：把半连接转换为EXISTS操作。本质上是把父表的条件下推到子查询中关键词是“**下推**”。(如上例)
| 子查询格式 | 可选的优化策略 |
| ---- | ---- |
| IN / = / ANY | Semi-join， Materialization，EXISTS strategy |
| NOT IN / <> / ALL | Materialization， EXISTS strategy |
注意：update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略，会以 exists 方式执行，**优化的方法也很简单，改成 join 即可**（这里是 delete，不用担心重复行问题）：
```sql
DELETE biz_customer_incoming_path
FROM biz_customer_incoming_path a
JOIN biz_customer_incoming b
WHERE a.bizCustomerIncoming_id=b.id and b.cid='7Ex46Dz22Fqq6iuPCLPlzQ';
```
>延伸：**为什么子查询比连接查询（LEFT JOIN）效率低**
>示例：
>```sql
>SELECT goods_id,goods_name
>FROM goods
>WHERE goods_id = (select max( goods_id ) from goods );
>```
>执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。
>
>**优化方式：**
可以使用连接查询（JOIN）代替子查询，连接查询不需要建立临时表，因此其速度比子查询快。
[MySQL 子查询优化](https://www.jianshu.com/p/3989222f7084)【他建的表有主键索引、a索引】
[Semi-join 之 Materialization 子查询优化策略](https://www.jianshu.com/p/04e896155ef6)
[Semi-join 之 FirstMatch 子查询优化策略](https://www.jianshu.com/p/c81b4ca29402)
[Semi-join 之 DuplicateWeedout 子查询优化策略](https://www.jianshu.com/p/5c4a28aa7f7c)
[Semi-join 之 LooseScan 子查询优化策略](https://www.jianshu.com/p/431db51f367b)
----
11. **用IN来替换OR**
>- 低效查询
>```sql
>SELECT * FROMt WHERE LOC_ID= 10ORLOC_ID= 20 ORLOC_ID= 30;
>```
>- 高效查询
>```sql
>SELECT * FROM t WHERE LOC_IN IN(10,20,30);
>```
对于许多数据库服务器而言，IN( )列表不过是多个OR语句的同义词而已，因为IN和OR在逻辑上是等同的。不仅是在MySQL数据库服务器，对于许多其他的数据库服务器使用到IN查询时，都是按照如下方式处理的:
1. 对IN列表中的数值进行排序。
2. 对于查询的匹配，每次使用二分查找去匹配IN列表的数值。
所以对于第2步，每次比较的算法复杂度大概为O(log n)。相反，对于同样逻辑的OR列表，每次都要遍历，所以OR相应的算法复杂度为O(n)(因此对于遍历非常大的OR列表，会很缓慢！)。
>因此，在了解了IN和OR的区别之后，每次优化，我们可以采用如下方式：
>- 尽量将能使用IN来代替OR查询。
>- 对IN列表中的数据，写SQL的时候就排好序，避免MySQL来做这个工作。
延伸：同理，对于连续的数字能用between就不要用in了，between只需要比对两个数字，而in全都要比对。
---
12. **读取适当的记录LIMIT M,N，而不要读多余的记录**
```sql
SELECT * FROM t WHERE 1;
----->
SELECT * FROM t WHERE 1 LIMIT 10;
```
以及快速定位范围：
```sql
select id,name
from table_name limit 866613, 20
```
使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。
优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：
```sql
select id,name from table_name
where id> 866612 limit 20
```
据数据库这种查找的特性，就有了一种想当然的方法，利用自增索引（假设为id）：
由于普通搜索是全表搜索，适当的添加 WHERE 条件就能把搜索从全表搜索转化为范围搜索，大大缩小搜索的范围，从而提高搜索效率。
这个优化思路就是告诉数据库：「你别数了，我告诉你，第10001条数据是这样的，你直接去拿吧。」
-----
13. **避免数据类型不一致**
```sql
SELECT COUNT(*)
FROM p_video_circle_relation a
LEFT JOIN p_video_info b
ON a.video_id = b.work_id
WHERE a.circle_id = 212307047 ;
```
SQL关联查询消耗662ms
仔细观察发现关联字段video_id和work_id的数据类型并不一致，video_id是bigint类型，work_id是varchar类型，关联查询时必须将关联字段转换成相同的类型才能进行比较，数据越多，转换需要的时间越长
将work_id修改为bigint类型后，查询仅需13ms
-----
14. **总和查询可以禁止排重用union all**
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，**union all的前提条件是两个结果集没有重复数据**（业务上需要不重复）。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all 提高速度。
**另外，如果排序字段没有用到索引，就尽量少排序。**
----
15. **批量INSERT插入**
常用的插入语句如：
```sql
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('1', 'userid_1', 'content_1', 1);
```
修改成：
```sql
INSERT INTO`insert_table`(`datetime`,`uid`,`content`,`type`)
VALUES('0','userid_0','content_0',0),('1','userid_1','content_1',1);
```
修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因是合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。
---
16. **尽量不用select**
SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前者也需要经常更新。所以要求直接在select后面接上字段名。
----
17. **区分in和exists**
```sql
select * from 表A
where id in ( select id from 表B )
```
上面sql语句相当于
```sql
select * from 表A
where exists ( select * from 表B where 表B.id=表A.id )
```
区分in和exists主要是造成了**驱动顺序的改变**（这是性能变化的关键）
如果是exists，那么以外层表为驱动表，先被访问，然后根据外表的数据去和内表进行比较和判断；
而如果是IN，那么先执行子查询，再将内表的数据拿去和外表进行比较与判断。
因此，如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
>延伸：如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
---
# **② 数据库表结构的优化：使得数据库结构符合[三大范式](https://blog.csdn.net/qq_35642036/article/details/82809974)与BCNF**
# **③ 系统配置的优化**
# **④ 硬件的优化**