MySQL技巧(二):百万级数据 MySQL 查询优化宝典

核心思想让每一个SQL都发挥出极致性能榨干硬件每一分潜力一、 索引优化核武器1.函数处理 - 索引的终结者sql-- 死亡写法让索引彻底失效 SELECT * FROM orders WHERE DATE(created_at) 2024-01-15; -- ✅ 重生写法性能提升1000倍 SELECT * FROM orders WHERE created_at 2024-01-15 AND created_at 2024-01-16;WHERE DATE(created_at) 2024-01-15;WHERE created_at 2024-01-15 AND created_at 2024-01-16; 原理深度剖析textBTree 索引本质是排序好的二叉树存储的是原始值。 【死亡写法执行过程】 1. MySQL 无法直接使用索引因为值是处理后的 2. 被迫全索引扫描读取 1000 万条记录 3. 每条记录都执行 DATE() 函数 4. 比较结果是否相等 5. 时间复杂度O(1000万) 函数计算开销 【重生写法执行过程】 1. 二分查找定位到 2024-01-15 00:00:00约20次比较 2. 顺序扫描到 2024-01-16 00:00:00假设1000条 3. 直接返回结果 4. 时间复杂度O(log 1000万 1000) ≈ O(20 1000) 性能提升从 10秒 → 0.01秒提升 1000 倍2.覆盖索引 - 不回表的艺术sql-- 普通索引每次都要回表 CREATE INDEX idx_user_id ON orders(user_id); SELECT * FROM orders WHERE user_id 123; -- 回表1000次 -- ✅ 覆盖索引数据全在索引中 CREATE INDEX idx_user_cover ON orders(user_id, order_no, amount); SELECT user_id, order_no, amount FROM orders WHERE user_id 123; -- 不回表原理深度剖析textInnoDB 索引结构揭秘 【聚簇索引主键索引】 叶子节点存储完整行数据 ┌─────────────────────┐ │ id1 │ 完整行数据 │ │ id2 │ 完整行数据 │ └─────────────────────┘ 【二级索引普通索引】 叶子节点只存储索引列 主键值 ┌─────────────────────┐ │ user_id123 │ id1 │ │ user_id123 │ id2 │ └─────────────────────┘ 【普通索引查询过程】 步骤1在二级索引找到 user_id123 → 得到 id1 步骤2回表用 id1 去聚簇索引找完整数据 磁盘I/O2次索引页 数据页 【覆盖索引查询过程】 步骤1在覆盖索引找到 user_id123 步骤2索引中已经有 user_id, order_no, amount直接返回 磁盘I/O1次仅索引页 性能提升减少50%的随机I/O对于百万级数据这是质的飞跃3.最左前缀 - 复合索引的黄金法则sql 原理深度剖析text复合索引存储结构类似电话簿-- 索引(status, created_at, user_id) -- ✅ 能用索引的查询 WHERE status 1 -- 完美命中 WHERE status 1 AND created_at 2024-01-01 -- 完美命中 WHERE status 1 AND created_at 2024-01-01 AND user_id 123 -- 完美命中 -- 能启用索引但是需要索引全扫描效率不佳 WHERE created_at 2024-01-01 -- 跳过 status WHERE user_id 123 -- 跳过前两列 WHERE status 1 AND user_id 123 -- 跳过了 created_atwhere status1 启用了索引同时完美命中where status1 and create_at2024-01-01 启用索引范围查找where created_at2024-01-01 走了索引但是索引全扫描有优化空间索引数据按第一列排序第一列相同按第二列排序... 存储示例 (status1, created_at2024-01-01, user_id100) (status1, created_at2024-01-01, user_id200) (status1, created_at2024-01-02, user_id100) (status2, created_at2024-01-01, user_id100) 【为什么跳列就不能用】 假设你要在电话簿中找名字是张三的人 - 电话簿按姓氏-名字排序 - 不知道姓氏无法定位到张字开头的位置 - 只能从头翻到尾全索引扫描 性能影响 - 能用索引O(log n) - 不能用索引O(n)n1000万 - 相差1000万倍二、 分页查询的降维打击4.大偏移量 - 最致命的性能杀手sql-- 死亡写法扫描1000万行取20行 SELECT * FROM orders ORDER BY id LIMIT 10000000, 20; -- 执行时间10-30秒甚至超时 -- ✅ 重生写法1延迟关联 SELECT * FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 10000000, 20 ) t ON o.id t.id; -- 执行时间0.3-0.8秒 -- ✅ 重生写法2游标分页 SELECT * FROM orders WHERE id 10000000 ORDER BY id LIMIT 20; -- 执行时间0.01-0.05秒 原理深度剖析text【普通 LIMIT 的执行噩梦】 步骤1从索引第一行开始扫描 步骤2读取前10000020条记录的主键 步骤3回表10000020次读取完整行 步骤4丢弃前10000000条只保留最后20条 磁盘I/O10000020次随机读 10000020次回表 数据量 │ 扫描行数 │ 回表次数 │ 时间 10万 │ 10万 │ 10万 │ 0.5秒 100万 │ 100万 │ 100万 │ 5秒 1000万 │ 1000万 │ 1000万 │ 50秒超时 【延迟关联的执行奇迹】 步骤1子查询只扫描索引只读取主键 - 磁盘I/O10000020次索引读顺序I/O快 - 内存占用只存储20个主键 步骤2主查询根据20个主键回表 - 磁盘I/O20次随机读 总I/O10000020次顺序读 20次随机读 【游标分页的极致性能】 步骤1直接定位到 id 10000000 的位置BTree二分查找 步骤2顺序扫描20条记录 磁盘I/O1次定位 20次读取 性能提升从50秒 → 0.05秒提升1000倍三、 JOIN 优化的核心机密5.小表驱动大表 - 嵌套循环的智慧sql-- ✅ 正确姿势小表驱动大表 SELECT * FROM users u -- 10万行过滤后1万 INNER JOIN orders o -- 1000万行 ON u.id o.user_id WHERE u.status 1; -- 错误姿势大表驱动小表虽然优化器会调但要养成习惯 SELECT * FROM orders o -- 1000万行 INNER JOIN users u -- 10万行 ON o.user_id u.id WHERE u.status 1; 原理深度剖析textNested Loop Join 执行机制 【小表驱动大表 - 高效】 for each row in users (1万行) -- 外层循环1万次 for each row in orders where user_idrow.id -- 内层用索引查找 匹配后输出 时间复杂度O(1万 × log 1000万) ≈ O(1万 × 23) ≈ 23万次操作 【大表驱动小表 - 低效】 for each row in orders (1000万行) -- 外层循环1000万次 for each row in users where idrow.user_id -- 内层用主键查找 匹配后输出 时间复杂度O(1000万 × log 10万) ≈ O(1000万 × 17) ≈ 1.7亿次操作 性能差距1.7亿 vs 23万 739倍6.JOIN前过滤 - 减少中间结果集sql-- 先JOIN后过滤产生巨大中间结果 SELECT u.name, o.order_no FROM users u INNER JOIN orders o ON u.id o.user_id WHERE u.status 1 AND o.amount 1000; -- ✅ 先过滤再JOIN中间结果极小 SELECT u.name, o.order_no FROM (SELECT id, name FROM users WHERE status 1) u INNER JOIN (SELECT * FROM orders WHERE amount 1000) o ON u.id o.user_id; 原理深度剖析text【先JOIN后过滤】 步骤1JOIN users × orders - 假设 users 10万行orders 1000万行 - 产生10万 × 1000万 1万亿理论笛卡尔积 - 实际优化器会优化但仍产生巨大临时表 步骤2过滤 WHERE 条件 - 扫描临时表过滤出符合条件的行 内存占用可能几百MB甚至GB 磁盘I/O可能产生磁盘临时文件 【先过滤再JOIN】 步骤1过滤 users10万 → 1万status1 步骤2过滤 orders1000万 → 100万amount1000 步骤3JOIN 1万 × 100万 → 实际匹配可能只有几千行 内存占用几MB 磁盘I/O无临时文件 性能提升从可能超时 → 毫秒级响应四、 子查询的生死抉择7.IN vs EXISTS - 生死攸关的选择sql-- 场景1子查询结果集小 → 用 IN SELECT * FROM orders WHERE user_id IN (1,2,3,4,5); -- 场景2子查询结果集大 → 用 EXISTS SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.amount 1000 ); 原理深度剖析text【IN 的执行机制 - 适合小结果集】 步骤1执行子查询将结果集物化到内存临时表 步骤2对外层查询的每一行检查是否在物化结果中 步骤3如果子查询结果集小物化开销小查询快 场景子查询返回5个ID物化5条记录很快 【EXISTS 的执行机制 - 适合大结果集】 步骤1对外层查询的每一行执行子查询 步骤2子查询一旦找到匹配立即返回TRUE 步骤3如果外层结果集小即使子查询结果集大也很快 场景外层100万用户内层1亿订单 - EXISTS对每个用户查订单有索引平均查找100次 - IN先查1亿订单物化1亿条记录内存爆炸 性能对比100万用户每人1000订单 - IN1亿订单物化 → 内存不足磁盘临时表 → 几分钟 - EXISTS100万次索引查找 → 0.5-1秒8.相关子查询 - 逐行执行的噩梦sql 原理深度剖析-- 相关子查询每行都执行一次 SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id u.id) as order_count FROM users u; -- 10万用户 -- ✅ JOIN优化一次搞定 SELECT u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id;text【相关子查询执行过程】 for each row in users (10万行) 执行 SELECT COUNT(*) FROM orders WHERE user_id row.id - 每次都要扫描 orders 索引 - 假设平均每人10条订单 - 总操作次数10万 × log 1000万 ≈ 10万 × 23 230万次 【JOIN方式执行过程】 步骤1一次 LEFT JOIN - 产生中间结果用户×订单每人10条共100万行 步骤2一次 GROUP BY - 分组聚合扫描100万行 总操作次数100万次 性能对比 - 相关子查询230万次索引查找 10万次函数调用 - JOIN方式100万次扫描 1次分组 提升约2-3倍且数据量越大优势越明显五、 聚合查询的极致优化9.GROUP BY 排序陷阱sql-- 默认会排序 SELECT user_id, COUNT(*) FROM orders GROUP BY user_id; -- Extra: Using temporary; Using filesort -- ✅ 不需要排序时取消排序 SELECT user_id, COUNT(*) FROM orders GROUP BY user_id ORDER BY NULL; -- Extra: Using temporary无 filesort 原理深度剖析text【默认 GROUP BY 执行过程】 步骤1扫描表使用哈希表分组内存临时表 步骤2对分组结果按 user_id 排序filesort 步骤3返回排序结果 为什么要排序 - MySQL 历史原因GROUP BY 默认会排序 - 为了兼容其他数据库的行为 【ORDER BY NULL 执行过程】 步骤1扫描表使用哈希表分组 步骤2直接返回结果无序 性能影响100万分组 - 有排序O(n log n) ≈ 100万 × 20 2000万次比较 - 无排序O(n) ≈ 100万次操作 提升30-50% 的查询时间10.COUNT 优化 - 索引的选择sql-- ✅ 使用最小的二级索引 CREATE INDEX idx_status ON orders(status); SELECT COUNT(*) FROM orders WHERE status 1; -- 全表扫描 SELECT COUNT(*) FROM orders; 原理深度剖析textInnoDB COUNT(*) 执行策略 【无 WHERE 条件】 - InnoDB 会扫描最小的二级索引 - 因为二级索引叶子节点只存主键更小 - 1000万行数据 * 主键索引约 500MB * 二级索引TINYINT约 80MB * 扫描二级索引I/O 减少 6 倍 【有 WHERE 条件】 - 尽量使用覆盖索引 - 索引列越少越好 - 索引类型越小越好 性能对比 - 无索引全表扫描10秒 - 有索引索引扫描0.5秒 - 覆盖索引只读索引0.1秒六、⚡ 排序优化的终极秘密11.ORDER BY 索引利用sql-- ✅ 利用索引排序无 filesort CREATE INDEX idx_created ON orders(created_at); SELECT * FROM orders ORDER BY created_at LIMIT 100; -- 文件排序有 filesort SELECT * FROM orders WHERE status 1 ORDER BY created_at; -- 需要复合索引 (status, created_at) 原理深度剖析text【索引排序 vs 文件排序】 索引排序 1. BTree 已经按 created_at 排序 2. 直接遍历索引叶子节点 3. 时间复杂度O(n) 文件排序filesort 1. 读取所有需要排序的数据到 sort_buffer 2. 如果数据量大使用快速排序内存 3. 如果内存不够使用归并排序磁盘临时文件 4. 时间复杂度O(n log n) 可能的磁盘I/O 性能对比100万行排序 - 索引排序0.1秒直接读取 - 文件排序2-5秒内存排序 - 磁盘文件排序10-30秒临时文件 复合索引 (status, created_at) 的原理 索引已经按 status 分组组内按 created_at 排序 WHERE status1 直接定位到 status1 的区域 该区域内 created_at 已经有序无需再排序七、 数据类型优化的隐藏收益12.选择最小数据类型sql-- ✅ 最优选择 status TINYINT(1) -- 1字节 user_id INT UNSIGNED -- 4字节 created_at INT UNSIGNED -- 4字节Unix时间戳 -- 最差选择 status VARCHAR(10) -- 至少11字节 user_id BIGINT -- 8字节 created_at DATETIME -- 8字节 原理深度剖析text存储空间的连锁反应 【1000万行数据对比】 TINYINT vs VARCHAR(10) - TINYINT10MB1字节 × 1000万 - VARCHAR至少110MB11字节 × 1000万 节省100MB 索引大小影响 - 索引页固定 16KB - 每行越小每页存储的行数越多 - 索引高度3层 vs 4层 内存影响 - InnoDB 缓冲池大小固定 - 小数据类型可以缓存更多数据 - 减少磁盘 I/O CPU 影响 - 整数比较1个CPU指令 - 字符串比较逐字节比较多个指令 综合收益 - 存储节省 100MB-1GB - 内存缓存命中率提升 30-50% - CPU查询速度提升 2-5倍八、 实战案例深度解析案例1订单统计从10秒到0.01秒sql-- 原始查询10秒 SELECT DATE(created_at) as date, COUNT(*) as order_count, SUM(amount) as total_amount FROM orders WHERE created_at BETWEEN 2024-01-01 AND 2024-12-31 GROUP BY DATE(created_at); -- ✅ 优化方案1汇总表0.01秒 CREATE TABLE daily_stats ( stat_date DATE PRIMARY KEY, order_count INT, total_amount DECIMAL(12,2), avg_amount DECIMAL(10,2) ); -- 每天凌晨计算 INSERT INTO daily_stats SELECT DATE(created_at), COUNT(*), SUM(amount), AVG(amount) FROM orders WHERE created_at CURDATE() - INTERVAL 1 DAY AND created_at CURDATE() GROUP BY DATE(created_at); -- 查询直接读汇总表 SELECT * FROM daily_stats WHERE stat_date BETWEEN 2024-01-01 AND 2024-12-31; 原理深度剖析text原始查询代价 - 扫描 orders 表1000万行 - 过滤条件BETWEEN 范围假设365天每天3万行共1095万行 - 分组聚合1095万行分组 - 每次查询都重复这个计算 汇总表原理空间换时间 - 预先计算每天只计算一次3万行 - 存储结果365行/年 - 查询时直接读取365行 计算量对比 - 原始1000万行 × 365天 36.5亿行扫描/年 - 汇总3万行 × 365天 1095万行扫描/年 - 每次查询1000万行 vs 365行 性能提升1000倍以上案例2分页优化从50秒到0.05秒sql-- 原始分页50秒 SELECT * FROM articles ORDER BY id LIMIT 10000000, 20; -- ✅ 游标分页0.05秒 SELECT * FROM articles WHERE id 10000000 ORDER BY id LIMIT 20; 原理深度剖析text【原始分页为什么慢】 LIMIT 10000000, 20 的执行 1. 从 id1 开始扫描索引 2. 读取 10000020 条记录的主键 3. 回表 10000020 次读取完整文章内容 4. 丢弃前 10000000 条 5. 返回最后 20 条 I/O 次数10000020 次随机读 时间50秒 【游标分页为什么快】 WHERE id 10000000 的执行 1. 在 BTree 中二分查找 id10000001约23次比较 2. 顺序读取 20 条记录 3. 直接返回 I/O 次数1次定位 20次读取 时间0.05秒 核心差异O(n) vs O(log n)九、 执行计划破译指南13.type 类型性能排名system const eq_ref ref range index ALL14.Extra 字段解密textUsing index ✅ 覆盖索引性能最优 Using where ⚠️ 需要过滤结合索引看 Using filesort ❌ 文件排序必须优化 Using temporary ❌ 临时表必须优化 Using index condition ✅ 索引下推好 Using join buffer ⚠️ JOIN缓冲区可能需要优化十、 优化效果对比表优化技巧优化前优化后提升倍数避免函数处理10秒0.01秒1000倍覆盖索引5秒0.1秒50倍延迟关联分页50秒0.5秒100倍游标分页50秒0.05秒1000倍小表驱动大表30秒0.3秒100倍相关子查询优化10秒0.5秒20倍汇总表统计10秒0.01秒1000倍合适数据类型5秒1秒5倍 核心要点总结三大黄金法则索引为王让查询走索引让索引覆盖查询减少扫描避免全表/全索引扫描只扫必要数据提前过滤WHERE 条件越早执行越好优化优先级索引优化性价比最高提升10-1000倍SQL 改写无需改表结构提升2-100倍表结构优化需要改表提升1.5-10倍4️⃣配置优化提升1.2-3倍5️⃣架构优化提升3-10倍记住最好的查询是不查询其次是少查询再次是快速查询现在去优化你的 SQL 吧让百万级数据飞起来