MySQL-04-执行计划

本章主要讲解索引的优化

数据库性能参数

	使用SHOW STATUS语句查看MySQL数据库的性能参数
	SHOW STATUS LIKE 'value'
	
	value常用的参数：
	
	-- 1、查询连接mysql服务器次数
	show status like 'Connections';

	-- 2、查询当前MySQL本次启动后的运行统计时间
	show STATUS like 'Uptime';
	 
	-- 3、查询慢查询次数
	show status like 'slow_queries'

	-- 4、 查询自当前MySQL启动后所有连接执行的SELECT语句总数
	show global status like 'com_select';
	 
	-- 5、查看MySQL服务器的线程信息
	show status like 'Thread_%';

	-- 6、--查看insert语句的执行数
	show  status like 'com_insert';
	show GLOBAL status like 'com_insert';

	-- 7、查询本次MySQL启动后执行的SELECT语句的次数
	show status like 'com_select';

EXPLAIN 分析SQL执行计划

EXPLAIN SELECT * FROM table_name

id
每个SELECT语句都会自动分配一个唯一标示符,sql语句中有几个SELECT就有几个id,分为三种情况：

• id相同：执行顺序由上到下
• id不同：如果是子查询，id号会自增，id越大优先级越高；
• id列为null的就表示这是一个结果集，不需要使用它来进行查询。

select_type (重要)

• simple
  ​ 单表，表示不需要union操作或者不包含子查询的简单select查询；有连接查询时，外层的查询为simple，且只有一个;
  

• primary 嵌套(主查询)
  一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。

  含有子查询的场景：
  含有union的场景：
  

• subquery 子查询
  除了from字句中包含的子查询外，其他地方出现的(select后from前及where中)子查询都可能是subquery子语句；

• dependent subquery 关联查询
  与dependent union类似，表示语句中的子查询subquery的查询要受到外部表查询的影响，“where a.id=b.dep”需要与语句外部做关联；
  

• union 联合
  union连接的两个select查询，第一个查询是PRIMARY，其他的select_type都是union，最终会产生一行UNION RESULT，Extra类型是（Using temporary）建立了临时表；

• union result 联合结果集
  包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null；
  

• dependent union 嵌套联合
  与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响；
  

table

• 显示的查询表名，如果查询使用了别名，那么这里显示的是别名；
• 如果不涉及对数据表的操作，那么这显示为null；
• 如果是尖括号括起来的<union M,N>，类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

type(重要)

• 依次从好到差(最少要索引使用到range级别)

system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL(没有用到索引)

• system
  表中只有一行数据或者是空表

• const （重要）
  使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。
  例如：下面demo同时展示了system与const 出现的场景
  

• eq_ref （重要）
  代表有关联，连接字段为主键或者唯一性索引；此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 ‘=’, 查询效率较高.
  注：使用索引关联查询必须“=”两侧都建立索引
  

• ref（重要）非唯一索引
  简单来说就是查询条件是索引，但是非唯一索引，当然也不可能是主键索引
  例如，此时的name为索引，但是非唯一索引，非主键
  

• fulltext
  全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

• range （重要）
  检索范围查询 常见于使用>,<,is null,between ,in ,like右模糊等运算符的查询中。

   例如：最左前缀范围查询
   ![在这里插入图片描述](https://img-blog.csdnimg.cn/97f41825ebf848b69c2b061b87620adf.png)
   例如：普通范围查询
   ![在这里插入图片描述](https://img-blog.csdnimg.cn/34cb91656fef4ce89ecc65181b010ab8.png)
  

• index_merge （MySQL 5.1 开始）
  表示查询使用了两个以上的索引，最后取交集或并集，常见and ，or的条件使用了不同的索引，但是实际上由于要读取多个索引，性能可能大部分时间都不如range，注意：并不是单独使用二个索引就一定会出现
  extra:intersect 交集
  extra:union 并集
  如下图

• index（重要）
  index与all的区别在于，index是遍历索引树从而拿到数据，而all是扫磁盘的全部数据
  例如：此时name为普通索引，查询所有的name则遍历了整棵树，所以type为index
  

• all（重要）
  这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

possible_keys
可能选中的索引
key
查询真正使用到的索引
key_len
用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的 列，这里不会计算进去。 留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。 另外:key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

ref
如果是使用的常数等值查询，这里会显示const 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows
这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原因是InnoDB里面使用了MVCC并发机制）

Extra（重要）
这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十 种，常用的有

• distinct
  在select部分使用了distinct关键字
• no tables used
  不带from字句的查询
• using filesort（重要）
  排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中 说明MySQL会使用一个外部的索引排序，而不是按照索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”
• using index（重要）
  查询时不需要回表查询，直接通过索引就可以获取查询的数据。（索引覆盖）
  
• using where（重要）
  表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。
  例如：此时name虽然遍历一棵索引树能获得全部，但是还是要进行where过滤
  
• using index condition
  索引下推，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了 存储引擎扫描的记录数量。
  例如: 前提 name ,score 都是索引列
  本来score=100的有二条记录，mysql的储存引擎只用到一个索引，过滤回来也是二条记录，但是有了索引下推，可以在存储引擎层进行name的过滤，这样一次就返回正确结果给了server层,
  

索引口诀

1.组合索引，全值匹配我最爱，带头大哥不能死(失效),出现范围就断层。
2.最佳左前缀法则（like）
3.不要在索引上做计算
4.尽量使用覆盖索引
5.索引字段上不要!= <> (主键除外)
6.索引字段上不判null
7.字符串不能不加单引号