MySQL查询性能优化

引用：https://www.cnblogs.com/boothsun/p/8480909.html

执行一条 SQL 语句时，要想知道这个 SQL 先后查询了哪些表，是否使用了索引，这些数据从哪里获取到，获取到数据遍历了多少行数据等等，

我们可以通过 EXPLAIN 命令来查看这些执行信息，这些执行信息被统称为执行计划。

• type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。

SQL优化原则：

1. 严禁select * 查询语句，必须明确查询字段，insert必须明确插入的字段；
2. 严禁单条SQL关联超过3张表，而且关联字段必须有索引且数据类型一致；
3. 严禁单条SQL子查询超过2层；
4. 严禁SQL中进行计算或嵌套逻辑的计算；
5. 严禁查询条件无索引，表的索引不要超过6个;
6. 严禁在where条件查询的字段中使用函数或表达式；
7. 严禁负向查询、模糊查询；
8. 严禁传入变量类型与查询条件类型不匹配；
9. 严禁表无主键或复合索引做主键，严禁使用无序数据做主键内容；
10. 严禁使用外键、视图、触发器、存储过程、自定义函数和分区表；
11. 使用子查询优化分页查询（覆盖索引）

1、索引原则：

• 最左匹配前缀原则，联合索引
• 如果确定有多少条数据，使用 limit 限制一下，MySQL在查找到对应条数的数据的时候，会停止继续查找
• join 语法，尽量将小的表放在前面，在需要on的字段上，数据类型保持一致，并设置对应的索引，否则MySQL无法使用索引来join查询
• 尽量选择区分度高的列作为索引
• 索引列不能参与计算，保持列“干净”
• 尽量的扩展索引，不要新建索引
• 用or分割条件，若or前后只要有一个列没有索引，就都不会用索引

2、为什么是最左匹配前缀原则？

索引是有序的，多列索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了。

3、在最左匹配原则中，有如下说明：

• 最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

4、MySQL执行过程、执行计划和优化

 

(1) 客户端发送一条查询给服务器

(2) 服务器会先检查查询缓存，如果命中了缓存，则立即返回。否则进入下一阶段

(3) 服务器端进行SQL解析、预处理，然后再有查询优化器生成对应的执行计划。MySQL解析是对mysql的语法规则进行验证，预处理器是根据mysql的一些规则进行进一步的检查解析树是否合法，主要是检查数据库表和数据列是否存在等。MySQl是基于成本的的优化器，它将尝试预测一个查询使用某种执行计划的成本，并选择其中成本最小的一个。

(4) Mysql根据查询优化器生成的执行计划，调用存储引擎来执行查询，一般是innodb

(5) 将查询结果返回给客户端