MySQL优化相关知识点总结

一、慢查询

MySQL默认10秒内执行的SQL语句没有响应及为慢查询

查看当前数据库设置的慢查询时间
	show variables like 'long_query_time';

修改慢查询判定的时间，用于查找慢查询
	SET long_query_time=1;
注：重启mysql之后，long_query_time依然是my.ini中设置的默认值。

二、Explain 语法

MYSQL8.0之前使用Explain要排除缓存干扰

2.1 、Explain 用法

1. 查看表结构，是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。

EXPLAIN tbl_name

2. 如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。

如 ：EXPLAIN SELECT * FROM USER WHERE NAME =‘lisi’ OR bird =‘1994-01-01’ AND age = 15

2.2 、Explain Select 各个字段的含义

MYSQL官方文档

1. id: 选择标识符。id值越大，越先执行。
2. select_type ：查询的类型

类型	含义
SIMPLE	简单SELECT(不使用UNION或子查询)
PRIMARY	最外面的SELECT
UNION	UNION中的第二个或后面的SELECT语句
DEPENDENT UNION	UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT	UNION的结果。
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT，取决于外面的查询
DERIVED	导出表的SELECT(FROM子句的子查询)

3. table：输出行所用的表
4. type：联接类型

类型	含义
system	表仅有一行(=系统表)。这是const联接类型的一个特例。
const	const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时
eq_ref	用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。
ref	如果键不是UNIQUE或PRIMARY KEY,则使用ref
ref_or_null	该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化
index_merge	该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单
unique_subquery	该联接类型类似于unique_subquery。可以替换IN子查询
index_subquery	该联接类型类似于unique_subquery。可以替换IN子查询，只适合子查询中的非唯一索引
range	只检索给定范围的行，使用一个索引来选择行
index	只全扫描索引树
ALL	全表扫描

5. possible_keys：表示MySQL能使用哪个索引在该表中找到行。
6. key： key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
7. key_len：key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。
8. ref：ref列显示使用哪个列或常数与key一起从表中选择行。
9. rows： 估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。
10. Extra：该列包含MySQL解决查询的详细信息

类型	含义
Distinct	MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。
Not exists	MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。
Using filesort	。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。
Using index	从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。
Using temporary	为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
Using where	WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误
Using sort_union(…), Using union(…), Using intersect(…)	这些函数说明如何为index_merge联接类型合并索引扫描
Using index for group-by	类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表

三、修复索引基数

SHOW INDEX FROM 表名：查询cardinality字段值，该字段的含义是索引列的基数，显示了存储引擎估算索引索引列有多少个不通的取值。
ANALYZE TABLE 表名 ：更新表的索引基数

四、count（）查询优化

4.1 、count()函数作用

count 可以统计某个列的数量，或者统计行数。

1. 统计列值的数量：比如 SELECT COUNT(name) FROM user ，则统计出user表内name不为NULL的列值。
2. 统计行数 ： 如：SELECT COUNT(*) FROM user

4.2 、count()函数优化

1. 有条件的查找数据优化

优化前： SELECT COUNT() FROM user WHERE id >10（需要扫描大于10后面全部的行数据）
优化后：SELECT (SELECT COUNT() FROM user ) - COUNT(*) FROM user WHERE id <=10(逆向计算，使用总数量- 相反条件的数量)

2. 如果要统计一个列不同的值的数量，可以用SUM 或者COUNT,如下统计性别是男或女的人数

SELECT SUM(IF(gender =‘f’,1,0)) AS F, SUM(IF(gender =‘m’,1,0)) AS m FROM USER
SELECT COUNT(gender =‘f’ OR NULL) AS F, COUNT(gender =‘m’ OR NULL) AS m FROM USER

五、优化关联查询

1. 只有当索引的列顺序和ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时，MYSQL才能够使用索引来对结果做排序。
2. 关联多表时，只有当ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序。
3. 尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。
4. 如果查询产生分页性能的解决方法： 通过延迟关联的方式，通过使用覆盖索引查询返回需要的主键，在根据这些主键关联原表获得需要的行即可。

六、SQL优化分类

6.1、查询优化

1. 分解关联查询：可以让缓存的效率更高，可以减少锁的竞争，可以减少冗余记录的查询。
   
2. 当只要一行数据时使用LIMIT 1 ，因为使用了limit 之后MySQL数据库引擎会找到一条记录之后停止搜索，而不是继续往后查找下一个符号记录的数据。
   比如当你要判断某一个值是否存在于数据库中，例如：

SELECT id FROM student WHERE  NAME = 'huge'  
SELECT 1 FROM student WHERE  NAME = 'huge' limit 1 

3. 为经常搜索的字段添加索引
4. 在使用Join进行关联连接时使用一样类型的例并且添加索引
5. 避免使用Select * 进行查询，从数据库里读取越多的数据，查询就会越慢，应当需要什么信息就使用什么信息。
6. 遵循最左匹配原则

6.2、索引优化

1. 在写多读少的时候使用唯一索引（因为唯一索引的更新不能使用change buffer）
2. 为较长的字段添加前缀索引
3. 对较小的数据列使用索引，这样会使索引文件更小，同时内存中也可以装载更多的索引键。
4. 不要过多创建索引，除了增加额外的磁盘空间外，对于DML操作的速度影响很大，因为其每增删改一次就得从新建立索引。
5. 使用组合索引，可以减少文件索引大小，在使用时速度要优于多个单列索引。

6.3、库表结构优化

1. 尽量为每一张表都设置一个ID自增的主键。因为MySQL是按照顺序进行存储在B+树上。
2. 字段用来做一些选项列表使用ENUM类型而不是VARCHAR。ENUM类型是非常快和紧凑的。
3. 尽可能让字段保持NOT NULL
4. 固定长度的表会提高性能，因为MySQL搜索的更快一些，固定长度容易计算下一个数据的偏移量。
5. 垂直分表：将不经常使用的字段拆成副表，可以减低表的复杂度和字段的数目，从而达到
   优化的目的。
6. 越小的列会越快
   比如：表用于配置信息，则可以使用tinyint来做主键。在不需要记录具体时间时使用Date比datetime好。