1.为什么要有SQL优化
问题：
1.计算机系统的资源，包括 CPU、内存、磁盘 I/O 和网络带宽，都是有限的。
2.一个糟糕SQL不仅仅自己执行慢，还影响整个系统，如果有多个缓慢SQL，还可能导致数据库负载飙升，服务不可用。
目的：
SQL 优化策略存在的根本原因在于：在有限的硬件资源下，通过最优化的方式执行数据库操作，以应对海量数据、高并发的挑战，从而保障系统的稳定性，提升用户体验，并最终实现业务价值。
2.插入优化
插入数据优化的核心目标是：减少不必要的开销，将随机 I/O 转化为顺序 I/O，并尽可能地利用批量操作的优势，从而提高写入吞吐量。
1. 批量插入
这是最基础，也是最有效的插入优化策略
问题：当我们要插入多行数据时，如果每行都执行一个单独的 INSERT 语句，会产生以下开销：
网络开销：每一次
INSERT都是一次网络往返，发送数据、等待服务器确认。事务开销：MySQL 默认是自动提交（
autocommit=1）。这意味着每执行一个INSERT语句，都会立即提交一个事务。事务提交需要将 Redo Log 刷写到磁盘，这个过程会产生大量的磁盘 I/O。
优化策略：
- 使用单条
INSERT语句插入多行数据： - 减少网络通信，检查事务开销
INSERT INTO table_name (col1, col2, col3)
VALUES
(value1_1, value1_2, value1_3),
(value2_1, value2_2, value2_3),
(value3_1, value3_2, value3_3);应用层面：
在应用中，不要一次性批量插入过多的数据（如上万条），因为这可能会导致单次请求过大，服务器处理时间过长。建议分批次、小批量地进行插入，例如每次插入 1000 行。
2. 有序插入
这个主要针对 InnoDB 存储引擎，它的原理与聚簇索引的特性紧密相关
关于InnoDB的存储机制和实现原理，大家可以去详细了解一下，这里就不做过多讲解了
问题：InnoDB 的数据是按主键顺序物理存储的（聚簇索引）。如果插入的数据主键值是随机的（比如 UUID），那么新的数据行可能需要被插入到 B+ Tree 索引的任意位置。
- 如果目标页面已满，就会触发页面分裂（Page Split）。页面分裂是一个昂贵的操作，它需要分配一个新的页面，并将旧页面的一半数据移动到新页面，这个过程会产生大量的随机磁盘 I/O。
- 随机插入还会导致索引树的结构变得不平衡，影响查询性能。
优化策略：
使用自增主键（AUTO_INCREMENT）。当使用自增主键时，新的主键值总是大于当前的最大值，因此新的数据行总是被插入到表的末尾。
优点：
- 顺序 I/O：数据总是被追加到最后一个页面，这是最快的写入方式。
- 避免页面分裂：除非最后一个页面满了，否则不会发生页面分裂。
- 保持索引结构平衡：自增主键的插入方式保持了 B+ Tree 索引的紧凑和平衡。
反例：使用 UUID 作为主键。虽然 UUID 保证了唯一性，但它的无序性会使得插入操作变成随机 I/O，引发频繁的页面分裂，严重影响写入性能。
3.主键优化
在InnoDB引擎中主键是非常重要的
聚簇索引：这是 InnoDB 的核心特性。表数据是根据主键的顺序在磁盘上物理存储的。这意味着，主键索引的叶子节点就是数据行本身。这种设计使得通过主键查找数据非常快，因为索引和数据存储在一起。
二级索引：所有非主键索引都是二级索引。二级索引的叶子节点存储的不是数据行的物理地址，而是主键值。当通过二级索引查询时，InnoDB 会先找到主键值，然后利用主键值再到聚簇索引中去查找完整的行数据，这个过程称为回表（Lookup）。
主键优化的核心原则
主键优化的核心原则可以概括为三点：小、整型、单调递增。
主键要小
原理：
二级索引开销：由于每个二级索引的叶子节点都需要存储主键值，主键越小，二级索引占用的空间就越少。
页面存储能力：每个 B+ Tree 页面（Page，默认 16KB）能存储的索引项数量是有限的。主键越小，一个页面就能存储更多的索引项，从而减少 B+ Tree 的高度，减少查询时的磁盘 I/O。
主键要整型（Integer）
原理：
比较和排序效率：整型数据在计算机内部的比较和排序速度比字符串快得多。字符串的比较需要逐个字符进行，而整型只需要一次 CPU 运算。
策略：
使用
INT、BIGINT等整型作为主键，而非VARCHAR。
主键要单调递增（Monotonically Increasing）
原理：
顺序插入：InnoDB 的数据是按主键顺序物理存储的。如果主键是自增的，新的数据总是被追加到表的末尾。这个过程是顺序写入，是最快的写入方式。
页面分裂：如果主键是随机的（例如 UUID），新数据可能需要插入到 B+ Tree 索引的任何位置。如果目标页面已经满了，就会触发页面分裂（Page Split）。页面分裂是一个昂贵的操作，它需要分配一个新的页面，并将旧页面的一半数据移动到新页面，这个过程会产生大量的随机磁盘 I/O。
策略：
优先使用自增主键
在分布式系统中，可以采用类似 Twitter Snowflake 的算法生成有序 UUID，以兼顾全球唯一性和单调递增性。
4.ORDER BY优化
1.Using flesor:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort bufer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
2.Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
1. ORDER BY 的工作原理
当 MySQL 收到一个带有 ORDER BY 子句的查询时，它会首先尝试使用索引来获取有序的结果。但如果无法使用索引，MySQL 就必须自己进行排序。
2. ORDER BY 的优化策略
优化 ORDER BY 的核心就是如何引导 MySQL 避免 Using filesort，转而利用索引。
a. 利用索引进行排序
这是最理想的情况。如果 ORDER BY 的列恰好是索引的一部分，MySQL 就可以直接按照索引的顺序来读取数据，无需额外排序。
条件：
ORDER BY的列是索引的最左前缀。ORDER BY的列与WHERE子句中的等值查询条件一起，构成了索引的最左前缀。ORDER BY的列的排序方向（ASC 或 DESC）与索引的排序方向一致。
b. 导致索引失效的常见情况
了解什么情况下 ORDER BY 无法利用索引，对于优化至关重要。
1.排序方向不一致
-- 索引是 (col1 ASC, col2 ASC)，但 ORDER BY 的方向不一致
SELECT * FROM my_table ORDER BY col1 ASC, col2 DESC;这种情况下，MySQL 无法直接利用索引，因为它需要对 col2 进行逆序排序。
2.排序的列不连续：
-- 索引是 (col1, col2, col3)，但跳过了中间列
SELECT * FROM my_table ORDER BY col1, col3;这违反了最左前缀法则。MySQL 只能利用 col1 的有序性，但对 col3 无法直接排序，仍会触发 Using filesort。
3.在 ORDER BY 列上使用函数或表达式：
-- 对索引列使用了函数
SELECT * FROM my_table ORDER BY YEAR(hire_date);索引存储的是 hire_date 的原始值，而不是函数处理后的结果，所以索引失效。
4.WHERE 条件中的索引列为范围查询：
-- 索引是 (col1, col2)，但 WHERE 中 col1 是范围查询
SELECT * FROM my_table WHERE col1 > 100 ORDER BY col2;当 WHERE 子句中包含范围查询（>、<、BETWEEN）时，这个范围之后的索引列将无法用于排序，因为在这个范围内，col2 的值是无序的。
3.如何判断ORDER BY优化
使用 EXPLAIN 命令是分析 ORDER BY 优化的关键。
Extra字段：Using filesort：表示需要进行文件排序，这是需要优化的信号。Using index：表示使用了索引覆盖，同时避免了排序，这是最优的情况。Using index; Using where：表示使用了索引，但还需要额外的条件过滤，且没有进行文件排序。
key字段：检查是否使用了正确的索引。
即使
key字段显示使用了索引，Extra字段也可能出现Using filesort，这通常是上面提到的范围查询等原因导致的。
总结：
ORDER BY优化的核心思想是通过索引来替代排序。
5.GROUP BY优化
GROUP BY是 SQL 中用于对数据进行聚合分组的操作，其性能开销主要来自于对数据的排序和临时表的创建。
1. GROUP BY 的工作原理
- 有索引按照索引的有序性进行分组，无序额外的排序和创建临时表
- 无法使用索引则会在内存或磁盘创建一个临时表
2.GROUP BY 的优化策略
优化
GROUP BY的核心就是如何引导 MySQL 避免Using temporary，转而利用索引。
a. 利用索引进行分组
i. 松散索引扫描
MySQL 只需要扫描索引的一部分即可完成分组，因为它可以直接跳过不符合条件的索引值。
条件：
GROUP BY的列是索引的最左前缀。WHERE子句中没有对GROUP BY的列之外的列进行条件限制。查询中没有使用
COUNT(DISTINCT...)等复杂的聚合函数。
假设有一个复合索引 (col1, col2, col3)。
EXPLAIN SELECT col1, COUNT(col2) FROM my_table GROUP BY col1;执行计划：MySQL 会直接遍历
col1的索引，每遇到一个不同的col1值，就进行一次聚合。它不需要扫描所有行，只需扫描索引即可。EXPLAIN分析：Extra字段会显示Using index for group-by，这代表了松散索引扫描。
ii. 紧凑索引扫描
当
GROUP BY的列不是索引的最左前缀，但仍然是索引的一部分，或者有WHERE条件限制时，MySQL 仍然可以利用索引，但需要扫描整个索引范围来完成分组。
条件：
GROUP BY的列和WHERE子句的条件一起，构成了索引的最左前缀。GROUP BY的列是连续的。
EXPLAIN SELECT col2, COUNT(col3) FROM my_table WHERE col1 > 10 GROUP BY col2;执行计划：MySQL 会先利用
col1 > 10定位到索引的起始位置，然后在这个范围内，对col2进行分组。这个过程需要扫描索引的整个范围。EXPLAIN分析：Extra字段会显示Using where; Using index，它没有明确的Using temporary，意味着分组操作是在索引扫描过程中完成的。
b. 导致索引失效的常见情况
分组的列不连续或跳过最左前缀：
-- 索引是 (col1, col2, col3)，但跳过了中间列
SELECT col1, col3 FROM my_table GROUP BY col1, col3;对 GROUP BY 列使用函数或表达式：
-- 对索引列使用了函数
SELECT YEAR(create_time) FROM my_table GROUP BY YEAR(create_time);ORDER BY 排序与 GROUP BY 不一致：
MySQL 默认会对
GROUP BY的结果进行排序。如果
GROUP BY和ORDER BY的列不一致，MySQL 可能需要创建临时表来完成排序。
GROUP BY优化的核心思想是通过索引来替代临时表和排序。在设计表结构和编写 SQL 语句时，我们应该充分考虑查询中GROUP BY的需求，并为之创建合适的索引。
6.limit优化
LIMIT是 SQL 中用于限制查询结果返回行数的子句，它在分页查询中尤为常见。
1. LIMIT 的工作原理
当执行一个类似于 SELECT * FROM table ORDER BY col LIMIT offset, count 的查询时，MySQL 的执行过程是：
扫描：MySQL 会首先扫描
offset + count条记录。排序：如果查询中有
ORDER BY子句，MySQL 会对这offset + count条记录进行排序。丢弃：MySQL 会丢弃掉前
offset条记录。返回：最后，它将剩余的
count条记录返回给客户端。
2. LIMIT 优化的核心策略
LIMIT优化的核心目标是避免扫描和排序不必要的行。我们的目的是直接跳到需要返回的第一条记录，然后只返回count条。
a:利用索引覆盖优化子查询
先通过索引快速定位到需要返回的行的主键或唯一索引，然后再根据这些主键回表查询完整的行数据。
实现方式： 假设我们要查询第 m 页，每页 n 条记录，即 LIMIT (m-1)*n, n。
-- 原始的慢查询
SELECT * FROM products ORDER BY price LIMIT 1000000, 10;
-- 优化后的查询
SELECT p.* FROM products AS p
JOIN (
SELECT id FROM products ORDER BY price LIMIT 1000000, 10
) AS temp ON p.id = temp.id;b：利用索引的连续性
如果你的查询是基于一个单调递增的主键或索引（例如自增 ID 或时间戳），你可以通过记录上次查询的最大值来避免使用 offset。
实现方式： 假设我们已经获取了第一页的最后一条记录的 ID 为 100。要查询第二页，我们就可以利用这个 ID。
-- 原始的慢查询 (假设 id 是自增主键)
SELECT * FROM products ORDER BY id LIMIT 100, 10;
-- 优化后的查询 (假设上次查询的最大 id 是 100)
SELECT * FROM products WHERE id > 100 ORDER BY id LIMIT 10;7.count优化
COUNT是一个非常常用的聚合函数，用于统计符合条件的行数
1.COUNT 的不同用法和原理
COUNT(*)：
直接统计行数，并不会去获取具体的列值。
COUNT(column)：
统计指定列的非 NULL 行数。
COUNT(1)：
统计非 NULL 的常量行数。
在 InnoDB 中，
COUNT(*)、COUNT(1)、COUNT(主键)这三者的性能基本没有区别，因为优化器会选择一个最快的索引（通常是主键或某个非空索引）来完成计数。
2. COUNT 的优化策略
a：利用索引覆盖
问题：
COUNT(*)在有WHERE条件时，如果条件列没有索引，就需要进行全表扫描。优化策略：为
WHERE子句中的列创建索引，并确保COUNT操作可以使用到该索引。
-- 假设 status 列没有索引
SELECT COUNT(*) FROM orders WHERE status = 'paid';
-- EXPLAIN: type=ALL, Extra=Using where
-- 为 status 列创建索引
ALTER TABLE orders ADD INDEX idx_status (status);
-- 再次查询
SELECT COUNT(*) FROM orders WHERE status = 'paid';
-- EXPLAIN: type=ref, Extra=Using indexb:使用近似值或缓存
在某些场景下，对行数的需求并不需要绝对精确，这时我们可以使用一些非实时的计数方法。
使用
SHOW TABLE STATUS：SHOW TABLE STATUS LIKE 'table_name';这条命令会返回表的元数据，其中的
Rows字段就是行数的估算值。优点：速度极快，时间复杂度为 O(1)。
缺点：在 InnoDB 中，这个值是一个估算值，不保证精确。
使用缓存：
将
COUNT的结果缓存到 Redis 等外部缓存系统中。在每次插入、删除、更新操作时，同步更新缓存中的计数值。
优点：速度极快，可以应对高并发的计数查询。
缺点：实现逻辑相对复杂，需要保证缓存和数据库数据的一致性。
总结：在有where条件下：利用索引覆盖
无where条件下：效率已经足够快