MySQL（高级性能篇）10章——索引优化与查询优化

数据库调优维度

1. 索引：解决索引失效或利用不充分问题，合理建立索引
2. SQL：对包含大量 JOIN 操作的 SQL 语句进行调整和改进
3. 服务器：调整 my.cnf 中的参数（如缓冲、线程数）
4. 数据量：数据过多时采用分库分表策略

查询优化分类

1. 物理优化：借助索引和表连接方式优化，重点掌握索引使用
2. 逻辑优化：通过 SQL 等价变换，换查询写法提升效率 

一、索引失效案例

1. 索引对性能的重要性：在 MySQL 中，合理设计索引可提高性能。索引能高效访问数据、快速定位记录、加快查询，若查询不用索引，数据量大时全量扫描会使查询变慢
2. 索引类型：大多数情况下采用 B + 树构建索引，空间列类型的索引使用 R - 树，MEMORY 表还支持 hash 索引

3. 索引使用决策：查询是否使用索引由优化器决定。MySQL 优化器是基于成本（Cost - Base Optimizer）的，而非基于规则（Rule - Based Optimizer）或语义，会选择开销小的执行方式。此外，SQL 语句是否使用索引还与数据库版本、数据量、数据选择度有关

（1）全值匹配

1. 系统中经常出现的SQL语句如下：

   EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
   
   EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
   
   EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

2. 建立索引前执行：（关注执行时间）

   SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

3. 分别为student表创建三个索引：

   CREATE INDEX idx_age ON student(age);#索引一
   CREATE INDEX idx_age_classid ON student(age,classId);#索引二
   CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);#索引三

4. 全值匹配：全值匹配是指查询语句的条件列与联合索引列从左到右一一对应、完全匹配，覆盖所有索引列且顺序一致
5. 结论：
   1. 索引选择原则：当存在多个索引时，查询优化器通常会基于成本估算来选择最合适的索引，而不是单纯看与查询字段的匹配度。不过一般来说，和查询条件匹配度越高、能更精准筛选数据的索引，被选中的可能性越大，因为这样通常可以减少数据扫描量，提高查询效率
   2. 其他索引状态：除被选中的索引外，其他索引并非失效。只是在当前查询中未被优化器选用，但在其他查询场景下仍可能被使用
6. 补充：SQL语句中SQL_NO_CACHE的使用保证不存在查询缓存，使各语句的比较不受“是否缓存”的影响

（2）最佳左前缀法则

1. 最左前缀原则：在使用联合索引进行查询时，查询条件需要从联合索引的最左边的列开始依次使用，并且中间不能跳过列，这样才能有效利用该联合索引来加速查询。如果查询条件不满足最左前缀要求，可能导致索引部分失效或完全失效
2. 示例：
3. 结论：MySQL 可以为多个字段创建联合索引，不同存储引擎对一个索引最多包含的字段数有不同限制，如 InnoDB 默认一个索引最多含 16 个字段且部分情况可调整该限制。使用多列索引时，过滤条件需按索引建立顺序依次满足，（1）若跳过某个字段，其后的字段无法利用该索引；（2）若查询条件未使用索引中的第一个字段，多列（联合）索引将不会被使用

（3）主键插入顺序

1. InnoDB 存储引擎表未显式创建索引时，数据存于聚簇索引叶子节点，记录存于数据页，数据页和记录按主键值升序排序。插入记录时，主键值依次增大，插满一页换页插入；主键值忽大忽小则处理较麻烦
2. 假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间：此时如果再插入一条主键值为9的记录，那它插入的位置就如下图：
3. 数据页满处理问题：InnoDB 表中，若数据页已满，插入记录需进行页面分裂和记录移位，会造成性能损耗
4. 主键值插入建议：为避免上述损耗，建议插入记录的主键值依次递增，可让主键具有 AUTO_INCREMENT 属性
5. 自增主键优势：创建表时给主键添加 AUTO_INCREMENT 约束，存储引擎会自动填入自增主键值，这种主键占用空间小，支持顺序写入，能减少页分裂

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

（5）类型转换导致索引失效

1. 举例：

   EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

2. 理解