小表驱动大表 + in/exists关键字 + 索引失效原因 + 执行计划参数 + 隐式转换导致索引失效

sql优化方向-小表驱动大表总结：

1. in后放小表：因为SQL执行时先走子查询
2. exists后放大表：因为exist前表示要查询的数据，要查的数据当然越小越好，所以逆推一下，大表就是放在exist之后的
3. 表连接时小表驱动大表，
   例: user表10000条数据，class表20条数据
   select * from user u left join class c u.userid=c.userid
   这样则需要用user表循环10000次才能查询出来，而如果用class表驱动user表则只需要循环20次就能查询出来
   例：
   select * from class c left join user u c.userid=u.userid

小表驱动大表

in和exists的区别

IN：
 
select * from A where A.id in （select B.id from B）

in后的括号的表达式结果要求先输出一列字段。与之前的搜索字段匹配，匹配到相同则返回对应行。
mysql的执行顺序是先执行子查询，然后执行主查询，用子查询的结果按条匹配主查询。

EXIST：
 
select * from A where exists（select * from B where B.id= A.id）

exist后的括号里则无输出要求，exist判断后面的结果集中有没有行，有行则返回外层查询对应的行。

ps所以exist还可以这样写： 用常量替换 * ，反正是判断有没有行，不需要实际传回的数据。
select * from A where exist（select 1 from B where B.id= A.id）
mysql的执行顺序是先执行主查询，将主查询的数据放在子查询中做条件验证。

大体看来貌似exist的执行效率比in低，但其实exists子查询在底层做了优化，会忽略select清单,也并不会对每条数据进行对比。

---

小表驱动大表的作用，联表时索引最好加在哪个表

MySQL优化-小表驱动大表原则

索引失效原因汇总

索引失效原因

容易忘记的失效原因：
表达式，函数的使用，隐式转换 会引起索引失效。
因为b+数上直接存的是索引值，而不是转化后的值。
二级索引不一定会触发回表，当前仅当索引字段已全部覆盖了被查询字段的时候

---

explain参数定义+ 隐式转换demo

先了解执行计划的参数含义：
extra（using index，using where，using index condition 分析）：

using index：使用覆盖索引，不需要回表就直接从二级索引返回数据
using where: 需要在服务层过滤数据（mysql分为服务层和存储引擎层）
using index condition：需要回表查询数据，但是有部分数据是在二级索引过滤后，再回表查询数据，减少了回表查询的数据行数

type：

【推荐】SQL 性能优化的目标:至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
说明:
1)consts 单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。
2)ref 指的是使用普通的索引(normal index)。
3)range 对索引进行范围检索。
反例:explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级 别比较 range 还低，与全表扫描是小巫见大巫。

id: 表示执行的顺序，id越大越先执行，id一样的从上往下执行。

type：

表示找到所查询数据的方法，也是本文重点介绍的属性。
该属性的常见值如下，性能从好到差：
NULL：无需访问表或者索引，比如获取一个索引列的最大值或最小值。
system/const：当查询最多匹配一行时，常出现于where条件是＝的情况。system是const的一种特殊情况，既表本身只有一行数据的情况。
eq_ref：多表关联查询时，根据唯一非空索引进行查询的情况。
ref：多表查询时，根据非唯一非空索引进行查询的情况。（军规要求一般要到ref级别！！）
range：在一个索引上进行范围查找。（军规要求至少type要到该级别！！）
index：遍历索引树查询，通常发生在查询结果只包含索引字段时。
ALL：全表扫描，没有任何索引可以使用时。这是最差的情况，应该避免。

possible_keys 表示mysql此次查询中可能使用的索引。
key 表示mysql实际在此次查询中使用的索引。
key_len 表示mysql使用的索引的长度。该值越小越好。
ref 表示连接查询的连接条件。
rows 表示mysql估计此次查询所需读取的行数。该值越小越好。
extra 表示mysql解决查询的其他信息，有几十种不同的值，该信息也是我们优化sql可以专注的一个值。

explain参数详解

rows：
在查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表该表的估计行数。如果使用索引来执行查询，执行计划的rows列就代表预计扫描的索引记录行数。

---

案例：隐式转换导致索引失效

以下案例中，数值类型的值被隐式转换成了字符串类型，再进行比对。会导致索引失效。
通过执行计划可知，进行隐式转换的SQL扫描了10 rows，未隐式转换的只扫描了一行

 -- name字段为字符串类型，以下两句均可查出正确结果
 
 -- 法一：会发生隐式转换，int将转成字符串类型再比对
 EXPLAIN SELECT name from t_innodb where name = 22
 
 -- 法二：
 EXPLAIN SELECT name from t_innodb where name = '22'