下面都是以MYSQL8.0版本测试。
关闭MYSQL缓存.
目录
上面讲了Explain关键字所有字段意思和基础运用，现在来分析哪种情况导致索引失效。
以上就是本次优化知识，比如group by 的优化呀 ，order by的优化，目前一张表数据不太容易做，下次更新。
MYSQL架构图
第一层是一种通用架构，大多数基于客户端/服务器的应用软件都有，主要是建立连接，授权处理，安全认证。
第二层是核心架构，包括缓存、查询解析，优化，核心函数（日期，数学函数，加密函数），以及所有跨存储引擎的功能都在这里实现：存储过程，触发器，视图等等。
第三层是存储引擎层，存储引擎负责MYSQL中数据的存储和提取。和GUN/LINUX下的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过API与存储引擎进行通信。
存储引擎API包含了几十个底层函数，用于执行诸如“开始一个事务” 或者 “根据主键提取一行记录”等操作，但是存储引擎并不会去解析SQL（INNODB会解析外键，因为只有INNODB才支持外键）
不同存储引擎之前也不会相互通信，而只是简单地响应上层服务器的请求。
Explain关键字使用
直接通过在查询语句前加上explain ，如 explain select * from test ;
Explain 下各个字段作用
id：从id属性可以得知表的执行顺序
举个例子：
查询1 explain select * from test ;
查询2 explain select * from score sc where sc.s_id in (select stu.s_id from student stu);
查询3 explain select * from `test` union select * from `test`;
结果集如图：
所以这个有三种情况：
- id相同，则从上至下执行
- id不同，则id越大优先级越高，执行最早
- id既有相同也有不同的，则相同会认为是一组，从上往下执行，然后根据每组id，id越大优先级越高，越先执行。
select_type：查询的类型
比如上图，有simple-简单查询，有primary-复杂查询。
一共有5种
- Simple：简单的select查询，查询中不包含子查询或者union，即就一个简单的select * from 表名 ,那么就是simple
- Primary：复杂查询最外面一个查询，比如上面查询三中第一个select * from test 就是primary 。
- subquery：在select或者where列表中包含子查询
- derived：在from列表中包含的子查询被标记为DERIVED（衍生表），mysql会递归这些子查询，放在临时表中。
- union：若有union查询，则union后面的表则是union，若union包含在from子句的子查询中,外层select将被标记为:DERIVED;
table：查询的表名
partitions：表所在分区，若操作了分库分表这里就会显示
type：访问类型。显示查询使用了何种类型,从最好到最差依次是:system > const > eq_ref > ref > range > index > all
- system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,这个也可忽略不计;
- const:表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行记录,所以很快. 如果将主键置于where列表中,mysql就能将该查询转换成一个常量;
- eq_ref:唯一性索引扫描,对于每一个索引键,表中只有一条记录与之匹配,常用于主键或唯一索引扫描;
- ref:非唯一性索引扫描,返回匹配某个单独值得所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体;
- range:只检索给定范围的行,使用一个索引来选择行,key列显示使用哪个索引,一般就是在你的where语句中出现了between,<,>,in等的查询；这种范围索引扫描比全表扫描要好,因为它只需要开始于索引的某一个点,结束于另一个点,不用扫描全部索引;
- index:index于all区别为index类型只遍历索引树,这通常比all快,因为索引文件通常比数据文件小;也就是说虽然all和index都是读写表,但index是从索引中读取的,而all是从硬盘中读的;
- all:也就是全表扫描;
备注:一般来说,得保证查询至少达到range级别,最好能达到ref.
举例： 以我这个300w test表 数据为准 , 只有主键有索引
- 直接根据主键查询。（很明显type=const）
所以，结论就是根据主键查询效率是非常高的。 - title列设置为普通索引，根据索引查询。 可以看到非唯一性索引查询是ref
- title列设置为全文索引，根据全文索引查询。（可以看到走的是ALL，并且全文索引失效了），提一个思考题为什么全文索引失效了？
- title列不设置索引，直接查询。 （可以发现与设置了全文索引相同，说明上面的全文索引肯定失效了）
possible_keys:显示可能会被应用到这张表的索引,一个或者多个;查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用到;
key:实际使用到的索引.如果为null,则没有使用索引;查询中若使用了覆盖索引,则该索引仅出现在key列表中;
key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好; key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的;
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值;
rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数;
Extra:包含不适合在其它列中显示但十分重要的额外信息
-
using filesort(出现这个东西不好):说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为"文件排序";
-
using temporary(出现这个东西更不好,使用到了临时表):使用了临时表保存中间结果,Mysql在对查询结果排序时使用临时表,常见于排序order by和分组查询group by。
-
using index:表示相应的select操作中使用了覆盖索引(Covering Index),避免了访问了表的数据行,效率不错！如果同时出现using where ,表明索引被用来执行索引键值的查找;
如果没有同时出现using where,表明索引用来读取数据而非执行查找操作。
解释一下覆盖索引及回表查询
在INNODB引擎中，由于聚簇索引直接保存了行数，叶子节点所在的二级索引则保存了指向一级索引即聚簇索引的指针，若没有设置覆盖索引，则查找了索引后还需要二次查询索引并返回数据。
通过二次查询索引导致二次定位数据行称回表查询。换句话说就是查的字段有没有用到索引
上面讲了Explain关键字所有字段意思和基础运用，现在来分析哪种情况导致索引失效。
- 使用like关键字 ， '%零%' ; sql：explain SELECT options_start FROM test where title like '%零%';
很明显，尽快title加了索引，但是一旦用了like查询，索引失效！
第二种情况，explain SELECT options_start FROM test where title like '零%';
发现走了tilte索引，所以若一定要使用like 关键字，尽可能确认前缀再去匹配 。
第三种情况，explain SELECT options_start FROM test where title like '%零';
很明显也没走索引，和第一种一个结果。
结论：目前没有想到什么原因导致这种情况，我想着应该是给了前缀后，mysql优化器会将词缀分开并解析。 - 使用 in 关键字 ， explain SELECT options_start FROM test where title in ('霏丝佳;PHYSI0GEL','0四七九','降米唐0号');
走了title索引 。
第二种组合，in 中使用子查询 explain SELECT options_start FROM test where title in (select title from test where id > 60 );
很明显，in中套子查询外层查询也不走索引。
结论：很容易发现in后面接任何的查询都不会走索引，not in 很显然也是一样的效果 。
在翻阅资料时发现一个解释， in 中的子查询要放一个小表进去，应该要小表驱动大表进行查询，即外层查询表（数据量小-》小表），子查询（数据量远远大于外层循环-》称为大表）-》 这个原因解释为，我认为是不对的。
SELECT options_start FROM test where title in (select title from test where id > 60 );
SELECT options_start FROM test t1 where exists (select 1 from test t2 where t2.id > 60 and t1.title=t2.title); 这是测试的exists
大表驱动小表 测试300w数据 ， in 查询时间17.098s ， exists 查询时间17.269s。
select t1.options_start from test t1 where t1.id > 60 and t1.title in (select title from test);
select t1.options_start from test t1 where t1.id > 60 and exists (select 1 from test where title=t1.title);
小表驱动大表 测试300w数据 ， in 查询时间22.674s ， exists 查询时间是19.231s
两个测试得出结论，现在很多博客上面写的测试是有问题的，in 接子查询 应该要是大表驱动小表更快 ，而exists 接子查询 则是小表驱动大表更快。 -》用到不可避免用到子查询时用exists 代替 in 。
以上就是本次优化知识，比如group by 的优化呀 ，order by的优化，目前一张表数据不太容易做，下次更新。