目录

SQL优化

23什么是慢SQL

如何优化呢?

如何利于覆盖索引

如何使用联合索引

如何进行分页优化

Join代替子查询

为什么要小表驱动大表?

为什么避免join太多的表?

如何进行排序优化

什么是filesort

全字段排序和rowid排序

条件下推

索引

索引为什么能提高MySQL的效率呢?

索引的分类

普通索引和唯一索引的区别

创建索引需要注意哪些

索引失效的情况

索引不适合哪些场景

什么适合创建索引

索引优化思路

---

SQL优化

23什么是慢SQL

MySQL 中有一个叫 long_query_time 的参数，原则上执行时间超过该参数值的 SQL 就是慢 SQL，会被记录到慢查询日志中。

如何优化呢?

首先要找到慢sql可以通过慢sql日志进行查询.然后可以用Explain + sql语句 看有没有用索引,大部分慢sql都是因为这个

1.避免不需要的列 避免select *

2.分页优化

3.索引优化

4.Join优化

如何利于覆盖索引

覆盖索引的核心是“查询所需的字段都在同一个索引里”，这样 MySQL 就不需要回表，直接从索引中返回结果。

如何使用联合索引

使用联合索引最重要的一条是遵守最左前缀原则，也就是查询条件需要从索引的左侧字段开始。

如何进行分页优化

分页优化的核心是避免深度偏移带来的全表扫描，可以通过两种方式来优化：延迟关联和添加书签。分页查询的效率问题主要是由于 OFFSET 的存在，OFFSET 会导致 MySQL 必须扫描和跳过 offset + limit 条数据，这个过程是非常耗时的。

Join代替子查询

第一，JOIN 的 ON 条件能更直接地触发索引，而子查询可能因嵌套导致索引失效。

第二，JOIN 的一次连接操作替代了子查询的多次重复执行，尤其在大数据量的情况下性能差异明显。

为什么要小表驱动大表?

第一，如果大表的 JOIN 字段有索引，那么小表的每一行都可以通过索引快速匹配大表。

时间复杂度为小表行数 N 乘以大表索引查找复杂度 log(大表行数 M)，总复杂度为 N*log(M)。

显然小表做驱动表比大表做驱动表的时间复杂度 M*log(N) 更低。

1. 当使用 left join 时，左表是驱动表，右表是被驱动表。
2. 当使用 right join 时，刚好相反。
3. 当使用 join 时，MySQL 会选择数据量比较小的表作为驱动表，大表作为被驱动表。

为什么避免join太多的表?

第一，多表 JOIN 的执行路径会随着表的数量呈现指数级增长，优化器需要估算所有路径的成本，有可能会导致出现大表驱动小表的情况。

第二，多表 JOIN 需要缓存中间结果集，可能超出 join_buffer_size，这种情况下内存临时表就会转为磁盘临时表，性能也会急剧下降。

如何进行排序优化

第一，对 ORDER BY 涉及的字段创建索引，避免 filesort。

如果是多个字段，联合索引需要保证 ORDER BY 的列是索引的最左前缀。

第二，可以适当调整排序参数，如增大 sort_buffer_size、max_length_for_sort_data 等，让排序在内存中完成。

第三，可以通过 where 和 limit 限制待排序的数据量，减少排序的开销。

什么是filesort

当不能使用索引生成排序结果的时候，MySQL 需要自己进行排序，如果数据量比较小，会在内存中进行；如果数据量比较大就需要写临时文件到磁盘再排序，我们将这个过程称为文件排序。

全字段排序和rowid排序

当排序字段是索引字段且满足最左前缀原则时，MySQL 可以直接利用索引的有序性完成排序。

当无法使用索引排序时，MySQL 需要在内存或磁盘中进行排序操作，分为全字段排序和 rowid 排序两种算法。

全字段排序会一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序，排序后直接返回结果，无需回表。

以 SELECT * FROM user WHERE name = "王二" ORDER BY age 为例：

• 从 name 索引中找到第一个满足 name='张三' 的主键 id；
• 根据主键 id 取出整行所有的字段，存入 sort buffer；
• 重复上述过程直到处理完所有满足条件的行
• 对 sort buffer 中的数据按 age 排序，返回结果。

优点是仅需要一次磁盘 IO，缺点是内存占用大，如果数量超过 sort buffer 的话，需要分片读取并借助临时文件合并排序，IO 次数反而会增加。

也无法处理包含 text 和 blob 类型的字段。

rowid 排序分为两个阶段：

• 第一阶段：根据查询条件取出排序字段和主键 ID，存入 sort buffer 进行排序；
• 第二阶段：根据排序后的主键 ID 回表取出其他需要的字段。

同样以 SELECT * FROM user WHERE name = "王二" ORDER BY age 为例：

• 从 name 索引中找到第一个满足 name='张三' 的主键 id；
• 根据主键 id 取出排序字段 age，连同主键 id 一起存入 sort buffer；
• 重复上述过程直到处理完所有满足条件的行
• 对 sort buffer 中的数据按 age 排序；
• 遍历排序后的主键 id，回表取出其他所需字段，返回结果。

优点是内存占用较少，适合字段多或者数据量大的场景，缺点是需要两次磁盘 IO。

条件下推

条件下推的核心思想是将外层的过滤条件，比如说 where、join 等，尽可能地下推到查询计划的更底层，比如说子查询、连接操作之前，从而减少中间结果的数据量。就是尽量早点做过滤动作

索引

索引为什么能提高MySQL的效率呢?

索引就像一本书的目录，能让 MySQL 快速定位数据，避免全表扫描。

除了查得快，索引还能加速排序、分组、连接等操作。

MySQL索引的底层是B+树 他比较矮壮 那么IO的次数就会少,效率就会比较高

索引的分类

从功能上分类的话，有主键索引、唯一索引、全文索引；从数据结构上分类的话，有 B+ 树索引、哈希索引；从存储内容上分类的话，有聚簇索引、非聚簇索引。

主键索引用于唯一标识表中的每条记录，其列值必须唯一且非空。创建主键时，MySQL 会自动生成对应的唯一索引。

主键索引=唯一索引+非空。每个表只能有一个主键索引，但可以有多个唯一索引

主键索引不允许插入 NULL 值，尝试插入 NULL 会报错；唯一索引允许插入多个 NULL 值。\

普通索引和唯一索引的区别

普通索引仅用于加速查询，不限制字段值的唯一性；适用于高频写入的字段、范围查询的字段。

唯一索引强制字段值的唯一性，插入或更新时会触发唯一性检查；适用于业务唯一性约束的字段、防止数据重复插入的字段。

全文索引是 MySQL 一种优化文本数据检索的特殊类型索引，适用于 CHAR、VARCHAR 和 TEXT 等字段

创建索引需要注意哪些

第一，选择合适的字段

第二，要控制索引的数量，避免过度索引，每个索引都要占用存储空间，单表的索引数量不建议超过 5 个。

第三，联合索引的时候要遵循最左前缀原则，即在查询条件中使用联合索引的第一个字段，才能充分利用索引。

索引失效的情况

1. 未遵循最左前缀原则
2. 在索引列上使用函数或运算
3. 使用不等于操作符(!=, <>)
4. 使用NOT IN或NOT EXISTS
5. 使用LIKE以通配符开头
6. 隐式类型转换
7. OR条件使用不当
8. 使用IS NULL或IS NOT NULL
9. 数据分布不均匀
10. 索引列参与计算
11. 使用ORDER BY不当
12. 索引选择性过低
13. 统计信息过时
14. 索引未被维护
15. 查询返回过多数据
16. 使用全表扫描提示
17. 多表连接条件不当
18. 子查询处理不当
19. 使用UNION而非UNION ALL
20. 索引列使用表达式

索引不适合哪些场景

第一，区分度低的列，可以和其他高区分度的列组成联合索引。

第二，频繁更新的列，索引会增加更新的成本。

第三，TEXT、BLOB 等大对象类型的字段，可以使用前缀索引、全文索引替代。

第四，当表的数据量很小的时候，不超过 1000 行，全表扫描可能比使用索引更快。

什么是区分度

区分度是衡量一个字段在 MySQL 表中唯一值的比例。

区分度 = 字段的唯一值数量 / 字段的总记录数；越接近 1，就越适合作为索引。因为索引可以更有效地缩小查询范围。一个表中有 1000 条记录，其中性别字段只有两个值（男、女），那么性别字段的区分度只有 0.002，就不适合建立索引。

什么适合创建索引

主键、唯一键、以及经常作为查询条件的字段最适合加索引。除此之外，字段的区分度要高，这样索引才能起到过滤作用；如果字段经常用于表连接、排序或分组，也建议加索引。同时如果多个字段经常一起出现在查询条件中，也可以建立联合索引来提升性能。

索引优化思路

先通过慢查询日志找出性能瓶颈，然后用 EXPLAIN 分析执行计划，判断是否走了索引、是否回表、是否排序。接着根据字段特性设计合适的索引，如选择区分度高的字段，使用联合索引和覆盖索引，避免索引失效的写法，最后通过实测来验证优化效果。