这是我参与「第五届青训营 」伴学笔记创作活动的第 17 天
索引失效原因
- 创建了组合索引，但查询条件未准守最左匹配原则;
- 最左匹配原则:
-
- 最左优先，在检索数据时从联合索引的最左边开始匹配
- 对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用
- 如果查询条件中没有使用这些字段中第 1 个字段时，多列（或联合）索引将会失效。
- 在索引列上进行计算、函数、类型转换等操作;
- 模糊查询,以 % 开头的 LIKE 查询比如 like '%abc';
- 查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
- 发生隐式转换(当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低, 如: where str = 100(str为字符类型))
关联查询优化
外连接查询:
连接字段有索引就走索引
内连接查询:
-
- 如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现
- 如果两个表都存在索引，会选择小表作为驱动表，即“小表驱动大表”。即两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表
Join原理简介
MySQL5.5之前:嵌套循环
假设有A,B两张表，无索引，A为驱动表，B为非驱动表，从表A中取出一条数据a1，遍历B表，将匹配的数据放到result，以此类推，驱动表A中每一条记录与表B的记录进行匹配，
MySQL8.0之前:Block Nested-Loop Join(BNLJ算法:块嵌套循环连接)
Block Nested-Loop Join方式不再是逐条获取驱动表的数据，而是一块一块的获取，引入了 join buffer 缓冲区，将驱动表 join 相关的部分数据列(大小受 join buffer 的限制)缓冲到 join buffer 中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和 join buffer 中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。
MySQL8.0之后:Hash Join
Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小的表利用 Join Key 在内存中建立 散列表 ，然后扫描较大的表探测散列表，找出与 Hash表匹配的行。
这种方式适用于较小的表完全可以放在内存中的情况，这样总成本就是访问两个表的成本之和。
在表很大的情况下并不能完全放在内存，这时优化器会将它分割成 若干个不同的分区 ，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高 I/O 的性能。
它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。
Hash Join 只能应用于等值连接，这是由Hash的特点决定的。
