索引优化与查询优化

2. 索引失效案例

2.1 全值匹配我最爱

2.2 最佳左前缀法则

2.3 主键插入顺序

建议：插入的记录的按  主键值依次递增 ，减少 页面分裂 发生 性能损耗。

2.4 计算、函数、类型转换 ( 自动或手动 ) 导致索引失效

2.5 类型转换导致索引失效

2.6 范围条件右边的列索引失效

ALTER TABLE student DROP INDEX idx_name; ALTER TABLE student DROP INDEX idx_age; ALTER TABLE student DROP INDEX idx_age_classid; 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

2.7 不等于 (!= 或者 <>) 索引失效

2.8 is null 可以使用索引， is not null 无法使用索引

2.9 like 以通配符 % 开头索引失效

2.10 OR 前后存在非索引的列，索引失效

2.11 数据库和表的字符集统一使用 utf8mb4

不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

3. 关联查询优化

保证被驱动表的 JOIN 字段已经创建了索引

需要 JOIN 的字段，数据类型保持绝对一致。

LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。

INNER JOIN 时， MySQL 会自动将 小结果集的表选为驱动表 。选择相信 MySQL 优化策略。

能够直接多表关联的尽量直接关联，不用子查询。 ( 减少查询的趟数 )

不建议使用子查询，建议将子查询 SQL 拆开结合程序多次查询，或使用 JOIN 来代替子查询。

衍生表建不了索引

4. 子查询优化

在 MySQL 中，可以使用连接（ JOIN ）查询来替代子查询。 连接查询 不需要建立临时表 ，其 速度比子查询 要快 ，如果查询中使用索引的话，性能就会更好。

结论：尽量不要使用 NOT IN 或者 NOT EXISTS ，用 LEFT JOIN xxx ON xx WHERE xx IS NULL 替代

5. 排序优化

优化建议：

1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排 序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列； 如果不同就使用联合索引。

3. 无法使用 Index 时，需要对 FileSort 方式进行调优。

当【范围条件】和【 group by 或者 order by 】的字段出现二选一时，优先观察条件字段的过

滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段

上。反之，亦然。

6. GROUP BY 优化

group by 使用索引的原则几乎跟 order by 一致 ， group by 即使没有过滤条件用到索引，也可以直接 使用索引。

where 效率高于 having ，能写在 where 限定的条件就不要写在 having 中了

7. 优化分页查询

优化思路一 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

优化思路二 该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

8. 优先考虑覆盖索引

一个索引包含了满足查询结果的数据就叫做覆盖索引。

简单说就是， 索引列 + 主键 包含 SELECT 到 FROM 之间查询的列 。