在程序员的技能树中，数据库是绕不开的核心能力。初级程序员能写出CRUD，中级程序员会建索引、写复杂查询，而进阶程序员则要面对千万级甚至亿级数据量下的高性能、高可用、高扩展挑战。
本文将围绕“高性能数据库实战”这一核心主题，从索引底层原理与优化、SQL语句深度调优、数据库架构演进、分库分表实战、读写分离与主从复制、数据库连接池调优、事务隔离级别与锁机制、以及NoSQL与NewSQL选型八个维度，带你全面掌握数据库性能优化的全栈能力。
一、索引的底层原理与优化策略
索引是数据库性能的第一道防线。不了解索引本质的优化，都是盲人摸象。
1.1 InnoDB的B+树索引深度解析
InnoDB使用B+树作为索引结构，每个节点对应一个磁盘页（默认16KB）。B+树的核心特性：
所有数据都在叶子节点，非叶子节点只存键值和指针
叶子节点之间通过双向链表连接，支持范围查询
聚簇索引：叶子节点存储完整行数据
二级索引：叶子节点存储主键值（回表）
B+树高度计算
假设：
单行数据约1KB（含所有列）
主键为BIGINT（8字节）
页指针（6字节）
每页可存储键值对数量：16KB / (8+6) ≈ 1142个
结论：对于千万级数据，B+树高度通常为3-4层，索引查找需要3-4次磁盘I/O。
1.2 索引失效场景与优化
-- 假设表结构
CREATE TABLE `orders` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint NOT NULL,
`order_no` varchar(32) NOT NULL,
`amount` decimal(10,2) NOT NULL,
`status` tinyint NOT NULL,
`create_time` datetime NOT NULL,
`update_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_order_no` (`order_no`),
KEY `idx_create_time` (`create_time`),
KEY `idx_user_status` (`user_id`, `status`), -- 复合索引
KEY `idx_amount` (`amount`)
);
常见索引失效场景及原理
-- 1. 对索引列使用函数或计算（失效）
-- 错误写法
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';
-- 原理：函数破坏了索引列的原值，无法使用B+树的有序性
-- 正确写法（使用范围查询）
SELECT * FROM orders
WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';
-- 2. 隐式类型转换（失效）
-- order_no是varchar类型
-- 错误写法：传入数字，发生隐式转换
SELECT * FROM orders WHERE order_no = 123456;
-- 原理：MySQL会将order_no转换为数字，相当于对列使用了CAST函数
-- 正确写法
SELECT * FROM orders WHERE order_no = '123456';
-- 3. 使用LIKE以通配符开头（失效）
SELECT * FROM orders WHERE order_no LIKE '%123%';
-- 原理：通配符开头无法利用B+树的前缀匹配特性
-- 正确写法（前缀匹配可用索引）
SELECT * FROM orders WHERE order_no LIKE 'ORD123%';
-- 4. OR条件中有一侧无索引（可能失效）
SELECT * FROM orders WHERE user_id = 1001 OR status = 1;
-- 原理：只要有一个条件无法使用索引，就可能全表扫描
-- 优化：使用UNION
SELECT * FROM orders WHERE user_id = 1001
UNION
SELECT * FROM orders WHERE status = 1;
-- 5. 复合索引不满足最左前缀原则（失效）
-- 索引 (user_id, status)
SELECT * FROM orders WHERE status = 1; -- 无法使用该索引
-- 原理：B+树先按user_id排序，再按status排序。缺少user_id时无法定位
SELECT * FROM orders WHERE user_id = 1001; -- 可以使用
SELECT * FROM orders WHERE user_id = 1001 AND status = 1; -- 完全使用
-- 6. NOT IN / <> 操作（通常失效）
SELECT * FROM orders WHERE status <> 1;
-- 原理：不等操作无法利用索引的有序二分查找
1.3 索引设计的黄金法则
三星索引标准（《Relational Database Index Design》）
实际案例：订单查询优化
-- 慢查询：用户查看订单列表，按时间倒序
-- 耗时：800ms（1000万数据）
SELECT id, order_no, amount, status, create_time
FROM orders
WHERE user_id = 1001 AND status = 1
ORDER BY create_time DESC
LIMIT 10;
-- 分析执行计划
EXPLAIN SELECT ...
-- type: ref, Extra: Using where; Using filesort
-- 优化方案1：创建复合索引 (user_id, status, create_time)
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
-- 再次分析
-- Extra: Using index (覆盖索引，无需回表，无filesort)
-- 耗时：15ms
-- 优化方案2：针对分页场景的延迟关联（适用于LIMIT offset很大时）
-- 原始慢查询（offset大时）
SELECT * FROM orders
WHERE user_id = 1001
ORDER BY id
LIMIT 100000, 10; -- 需要跳过10万条
-- 优化写法
SELECT * FROM orders
INNER JOIN (
SELECT id FROM orders
WHERE user_id = 1001
ORDER BY id
LIMIT 100000, 10
) AS tmp ON orders.id = tmp.id;
-- 原理：子查询只查主键，走索引覆盖；再通过主键回表取完整数据
1.4 索引监控与维护
-- 查看索引使用情况（MySQL）
SELECT
index_name,
cardinality, -- 基数：唯一值数量
seq_in_index,
collation
FROM information_schema.statistics
WHERE table_name = 'orders';
-- 查看未使用的索引（通过sys库）
SELECT * FROM sys.schema_unused_indexes;
-- 查看重复/冗余索引
SELECT * FROM sys.schema_redundant_indexes;
-- 索引碎片整理（当删除大量数据后）
ALTER TABLE orders ENGINE=InnoDB;
-- 或使用OPTIMIZE TABLE（会锁表，建议在低峰期）
OPTIMIZE TABLE orders;
