MySQL优化

关于MySQL优化，我们可以根据一条SQL的执行流程需要经过哪些环节，通过这些环节思考对应的优化策略。
SQL优化中最主要的就是查询优化。其中关联查询太多可能是由于数据库设计不合理，可以根据数据库设计的范式准则，适当增加冗余，减少需要关联的表。

一、客户端与数据库服务器的连接

1.从数据库服务器的角度
（1）连接数的设置，目前单台数据库服务器可以支持的连接数是多少，可以依据服务器的硬件配置来设置
（2）连接的超时设置，避免客户端长时间占用连接，未及时释放连接
2.从客户端的角度
（1）采用池化技术管理连接，目前市面上常见的池化技术
老牌的C3P0,阿里的druid,hikari等。
设置连接数的时候并不是越多越好，主要是看当前主机的CPU数量，减少上下文切换带来的IO开销。
（2）分页查询
用户体验上：通过在客户端进行分页，可以减少每次查询返回的数据量，从而减少客户端数据的加载时间，提高用户对数据的访问速度和响应速度。
服务器负载上：数据库服务器处理大量数据的分页查询可能会导致服务器的负载增加，降低系统的性能。通过在客户端进行分页，可以将部分计算负载转移到客户端，减轻数据库服务器的压力。

二、索引及其SQL的优化

当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。
整个流程可以分为观察和行动，首先使用对应的分析工具找到需要进行优化的地方，然后针对性地进行优化。

注意：对于数据库的优化，越接近硬件成本越高，效果反而不如对SQL和索引的优化

1.慢查询定位和分析

首先开启MySQL的慢查询，使用慢查询日志对慢查询问题进行定位，然后可以使用EXPLAIN分析查询语句。除了EXPLAIN也可以使用其他分析工具进行分析。

通过查看执行计划，检查SQL的执行性能

explain SQL id select_type table type possible_keys key key_len
ref rows Extra

涉及字段含义：

id：执行顺序号，值越大，越先执行

select_type：查询类型，普通，联合，子查询等；

simple：简单查询

primary：主查询

subquery：子查询

drived（衍生）：from 列表中包含的查询

union：联合查询，union之后的

union：联合查询的结果查询

table：涉及的表

type：访问类型，即数据是怎么获取到的

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

const：只需匹配一行数据，如：where primary_key=x

ref：非唯一性索引扫描，匹配多行

range：范围匹配，如between、in

index：取索引列，从索引文件读取

all：全表扫描

possible_keys：查询字段包含的索引

key：使用的索引

key_len：索引中使用的字节数

rows：找到所需的记录所需要读取的行数

extra：额外信息

Using temporary：使用临时表保存中间结果，group by

Using filesort：对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作称为“文件排序”

Using index：使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高 

Using where ： 表示服务器将存储引擎返回行后再应用where过滤条件

Impossible WHERE：不可达的查询

小结：
（1）type参数：结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL
其中比较重要的几个提取出来（见上图中的粗体字）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）
只有All类型没有用到索引，进行了全表扫描，导致性能很差。

2.索引的优化

由于合理的索引可以有效提高性能，所以我们应该避免索引失效的发生，充分利用索引。
下面讨论常见的索引失效的情况：
1.不满足最佳左前缀法则，联合索引就会失效，只会使用索引的部分来查询
例如，如果有一个联合索引 (A, B, C)，按照最左前缀法则，查询中只使用了 A，C 列进行条件筛选，那么索引中只能利用A来查询。但如果查询中只使用了 B 列或 C 列进行条件筛选，索引可能就不会被使用，从而导致性能问题。

2.主键插入时应该顺序插入，否则会导致页分裂，造成性能消耗。
3.计算、函数、类型转换（自动或手动）导致索引失效

第一种情况
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = ‘abc’;
第二种失效的情况
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

4.类型转换导致索引失效

NAME是字符串类型
未使用索引：
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
索引生效了：
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

5.范围条件右边的列索引失效

create index idx_age_classid_name on student(age,classid,name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

上面的范围查询会导致右边的name列索引失效，定义索引时应该将范围查询的列索引放在最右侧

create index idx_age_name_classid on student(age,name,classid);

6.不等于（!=或者<>)导致索引失效
7.is null可以使用索引，is not null不可以使用索引

is null可以看作是= null,is not null可以看作是！=null,故不可以使用索引
如果确实需要使用NULL，可以将NULL字符串设置未‘ ’。
8.like以通配符%开头索引失效

EXPLAIN SELECT NO_SQL_CACHE * FROM student WHERE NAME LIKE ‘ab%’;

ab%的开头是确定的，所以索引并不会失效

EXPLAIN SELECT NO_SQL_CACHE * FROM student WHERE NAME LIKE ‘%ab%’;

%ab%由于开头不确定，所以会失效

9.OR前后存在非索引的列，索引失效

未使用到索引
CREATE INDEX index_age ON student(age);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid =100;

10.数据库和表的字符集统一使用utf8mb4
统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不
同的 字符集 进行比较前需要进行 转换 会造成索引失效。

3.SQL的优化

（1）查询字段尽量不要用select * ，而是具体字段
（2）尽量使用数值代替字符串类型

• 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；
• 而对于数字型而言只需要比较一次就够了；
• 字符会降低查询和连接的性能，并会增加存储开销。

（3）使用varchar代替char

• varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；

• char按声明大小存储，不足补空格；

• 其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

(4)关联查询优化
LEFT JOIN和RIGHT JOIN都应该是小表驱动大表，小表作为驱动表。
INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。

JOIN的底层原理：
对于左外连接，是以左表为基础，将左表中的所有行与右表进行连接，保留左表中的所有行，而右表中匹配的行将会根据连接条件进行返回。左表进行全表扫描，右表可以利用索引加快查询。所以左外连接中左表是驱动表，右表是被驱动表，数据量大的表可以利用索引查找，提高查询效率。

(5)不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
子查询效率不高的原因：
① 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表 ，然后外层查询语句从临时表
中查询记录。查询完毕后，再 撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会
受到一定的影响。
③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
(6)ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序
(7)GROUP BY尽量使用索引

• 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。
• 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
• where效率高于having，能写在where限定的条件就不要写在having中了

(8)优先覆盖索引，避免索引的二次查询（回表）
(9)UNION查询
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录。而且如果表数据量大的话还可能会导致用磁盘进行排序。
推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。
(10)批量插入性能提升
默认新增SQL有事务控制，导致每条都需要事务开启和事务提交，而批量处理是一次事务开启和提交，效率提升明显，达到一定量级，效果显著，平时看不出来。
(11)操作delete或者update语句，加个limit或者循环分批次删除

• 降低写错SQL的代价
• 一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作。
• 如果你删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢。
• delete执行时，如果字段加了索引，会导致所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。

4.其他优化策略

如果由于数据太多，索引优化、sql优化、服务器调优设置参数都不能解决问题，这时就要增加服务器，使用主从同步、读写分离、分库分表等策略，从物理上进行优化。

技术延申：
1.char与varchar的区别
（1）char的长度是固定的，varchar的长度是可变的
（2）在大多数情况下，varchar的查询效率会比char高
由于Varchar使用可变存储，存储空间小，索引占用空间也小，故查询时效率更高。但是需要注意的是，如果某些情况下你的数据确实具有固定长度的特性，并且长度较短，CHAR 可能会更有效率，因为它不需要额外的字节来记录字符串的长度，并且对于固定长度的数据存储会更加紧凑。
（3）char类型由于使用的是定长存储，所以当字符串长度不够时会自动补齐空格，因此char类型查询的时候一定要记得使用trim()，但查询中使用函数可能导致索引失效
(4）where中使用默认值代替null

转载于https://blog.51cto.com/u_13521/7523100
https://www.sohu.com/a/727441376_121124376