1. 索引优化

1.1. 索引类型

MySql 常用索引类型有B+Tree 和 Hash索引。

  1. MySQL中大部分的存储引擎都支持B+Tree 索引，如果没有为数据库表显示的指定索引类型，那么MySQL会默认使用B+Tree索引
  2. Hash索引，比较合适存储Key-Value 类型的数据，查询Key-Value 类型的数据时，会根据Key快速获取数据。Hash索引有一个不可以根据范围查找数据的缺陷

1.2. 使用索引的场景

1.2.1. 全值匹配

全值匹配是指在MySQL的查询条件中包含索引中的所有列，并且针对索引中的每列进行等值判断。
根据goods数据表的主键ID查询数据，按照主键ID查询数据使用了主键索引。
image.png

1.2.2. 范围查询

MySQL 支持对索引的值进行范围查找，通过索引命中分析命令可以看出type为range，说明查询优化器根据主键索引范围进行查询。Extra为Using Where，说明MySQL按照主键确定范围后再回表查询数据。
image.png

1.2.3. 联合索引

联合索引的使用，在goods表中建立一个名称为category_name_index的联合索引，索引中包含的字段为category和name，并按照category和name字段查询数据。

  1. # 连接联合索引
  2. Alter table goods add index category_name_index(category,name);

通过Explain 发现使用了category_name_index索引查询数据
image.png
联合索引的最左匹配原则，在MySQL中使用联合索引查询数据时从联合索引的最左边的列开始查询，并且不能跳过索引中的列。如果跳过索引中的列查询数据时，后续的查询中不在使用索引。
在goods表中新建joint_index联合索引，包含字段id、category、name，那么以下形式会使用到联合索引

  1. # 以下语句会使用联合索引joint_index
  2. explain select * from goods where id='1';
  3. explain select * from goods where id='1' and category='01';
  4. explain select * from goods where id='1' and category='01' and name='Apple';

以下形式不会使用联合索引

  1. # 以下语句不会使用联合索引joint_index
  2. explain select * from goods where category='01';
  3. explain select * from goods where name='01';
  4. explain select * from goods where category='01' and name='Appale';

1.2.4. 查询索引列

MySQL 在查询包含索引的列或者查询的列都在索引中时，查询的效率会比使用SELECT * 或者查询没有索引的列的效率要高很多。
我们在goods表中将字段id字段添加为索引，在查询id字段时MySQL不在使用Using Where回表查询数据，而是使用Using Index覆盖索引扫描
image.png

1.2.5. 匹配字段前缀

如果表中的字段存储的数据比较大，在这个字段添加索引时会影响写入新能，增加MySQL的索引维护负担。我们可以根据业务需求，在字段的开头部分添加索引，规定字段前面为多少个字符添加为索引，这样在索引匹配时只会匹配开头部分。
在goods表中为name字段的前2个字符添加索引

  1. # 为goods表中的name字段前2个字符添加索引
  2. CREATE INDEX name_part ON goods (name(2));

根据name字段查询goods表时，匹配name字段前2个字符使用name_part索引
image.png

1.2.6. 精确与范围匹配索引

在查询数据时，可以同时精确匹配索引并按照另一个索引的范围查询数据。
查找goods表时，category字段为精确值，id为范围查找。通过命令可以知道MySQL使用了category_part索引精确匹配，并且按照主键索引进行范围查找。
image.png

1.2.7. 匹配NULL值

MySQL中，对一个添加了索引的字段判断是否为NULL时会使用索引进行查询。
image.png

1.2.8. LIKE 索引匹配

like 语句中的查询条件不以通配符开始时，MySQL会使用索引匹配。
image.png

1.2.9. 连接查询匹配索引

1.3. 无法使用索引的场景

在MySQL中，不是我们建立了索引，在查询数据时就一定会使用索引匹配，有些情况下是无法使用索引匹配查询的。不能使用索引匹配查询的情况常见有以下几种。

1.3.1. 以通配符开始的LIKE查询

image.png

1.3.2. 数据类型转换

在查询条件中，对查询字段的类型进行转换后，就不会使用索引匹配查询。
image.png

1.3.3. 联合索引未匹配最左列

在数据表中创建联合索引后，查询条件不包含联合索引中最左列或者最左列开始部分，就认为是不满足最左前缀匹配，不会使用索引匹配查询
image.png

1.3.4. OR语句

查询语句中包含OR 来连接多个查询条件时，只要查询条件中存在未创建的索引字段，就不会使用索引匹配查询
image.png

1.3.5. 使用<>或者!=操作符匹配查询条件

在查询语句中使用<>或者!=操作符时不能使用索引匹配查询
image.png

1.3.6. 计算索引列

在查询语句中对添加了索引的列使用计算或者函数时，不会使用索引匹配查询
image.png

1.3.7. 匹配NOT NULL

使用IS NULL 判断某个字段是否为NULL时，会使用该字段的索引。如果使用NOT NULL来验证某个字段不为NULL时，会进行全表扫描

2. SQL 语句优化

在MySQL优化过程中，大部分优化都是针对SQL语句的优化，SQL语句的优化主要在SELECT、INSERT、DELETE、ORDER BY、分页等语句的优化。

2.1. 嵌套查询优化

当SQL语句存在嵌套查询时，MySQL会生成临时表来存储子查询的结果，外层查询会从临时表中读取数据，待整个查询完毕后，会删除临时表，整个过程比较耗时。此时可以使用JOIN 语句替代嵌套查询来提升查询性能。
image.png

使用JOIN 连接语句查询时，MySQL 查询性能提升很多
image.png

2.2. OR 条件语句的优化

在查询语句中有多个查询条件使用OR关键字进行连接时，只要OR连接的条件中有一个查询条件没有使用索引，MySQL就不会使用索引，也就是说使用OR连接多个查询条件时，必须每个查询条件都使用索引才会使用索引查询数据。
image.png

2.3. ORDER BY 语句优化

SQL语句中使用ORDER BY 排序时，要保证ORDER BY 子语句的字段上的字段存在索引。
sort字段没有索引时，会对结果进行filesort排序
image.png
我们对sort添加索引后，再次按sort排序查询，此时MySQL使用索引对数据进行排序操作

2.4. GROUP BY 语句的优化

在MySQL 8.0版本之前，使用GROUP BY 语句对数据进行分组时，默认会根据GROUP BY 子语句的字段进行排序，如果GROUP BY 子语句字段上不存在索引时， 就会很耗性能。此时可以指定ORDER BY NULL 禁止排序来减少ORDER BY 子语句带来的性能消耗。

  1. explain select * from goods where category='02' group by category;

使用GROUP BY NULL 禁止排序后，会明显提升数据查询性能

  1. explain select * from goods where category='02' group by category order by null;

2.5. 分页查询优化

MySQL使用LIMIT m,n 来实现分页查询，LIMIT 语句会默认排序出数据表中前m+n条数据，然后将前m条数据舍弃，只返回m+1到m+n条数据记录，这是非常耗费性能的。
直接使用LIMIT 语句进行分页时，MySQL 会进行全表扫描并对查询的结果数据使用filesort方式进行排序
image.png
按照索引分页并会查数据表
image.png
使用回表查询的方式来优化分页，能够让MySQL 扫描更少的分页数据，达到提升分页查询性能的目的，比直接使用LIMIT m,n查询性能更高