数据库进阶

CREATE TABLE users (  id BIGINT PRIMARY KEY,       -- 聚簇索引  name VARCHAR(50),  age INT,  INDEX idx_name (name)        -- 二级索引);

-- 有索引 idx_name(name)SELECT id, name FROM users WHERE name = 'Alice';--   ✅ 索引里已有 name 和 id → 不需要回表（覆盖索引）SELECT id, name, age FROM users WHERE name = 'Alice';--   ❌ 索引里没有 age → 需要回表查整行

-- 优化：把常用查询字段加到索引里，避免回表ALTER TABLE users ADD INDEX idx_name_age (name, age);SELECT id, name, age FROM users WHERE name = 'Alice';   -- 现在可覆盖

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);

WHERE user_id = 1                                    -- ✅ 用到第 1 列WHERE user_id = 1 AND status = 2                     -- ✅ 用到前 2 列WHERE user_id = 1 AND status = 2 AND created_at > '2026-01-01'   -- ✅ 全用WHERE user_id = 1 AND created_at > '2026-01-01'      -- ⚠️ 只用到第 1 列（跳过了 status）

WHERE status = 2                                     -- ❌ 跳过最左列WHERE created_at > '2026-01-01'                      -- ❌ 跳过前两列

EXPLAIN SELECT id, name FROM users WHERE name = 'Alice' ORDER BY created_at DESC LIMIT 20;

# my.cnfslow_query_log = 1long_query_time = 1log_queries_not_using_indexes = 0     # 数据量大时开启会产生大量日志

# 按总耗时排序（t=总时间, c=次数, l=锁时间, r=返回行数）mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

-- ❌ 需要扫描并丢弃前 100000 行SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;-- ✅ 游标分页：始终走主键索引SELECT * FROM orders WHERE id < 100020 ORDER BY id DESC LIMIT 20;-- ✅ 延迟关联：先只查主键，再回表取整行SELECT o.* FROM orders oJOIN (SELECT id FROM orders ORDER BY id DESC LIMIT 100000, 20) t ON o.id = t.id;

-- ❌ status 无索引 → 全表扫描并锁住所有扫描行（甚至升级为表锁）UPDATE orders SET status = 'cancelled' WHERE status = 'pending';-- ✅ 有索引 → 只锁匹配的行ALTER TABLE orders ADD INDEX idx_status (status);

SHOW ENGINE INNODB STATUS;      -- 查看 LATEST DETECTED DEADLOCK 段

-- ✅ 乐观锁：避免长事务与锁竞争UPDATE stock SET count = count - 1, version = version + 1WHERE id = ? AND version = ? AND count >= 1;-- 影响 0 行 → 版本冲突，由业务重试

// 写后强制读主：用 Redis 标记async function createOrder(userId, data) {  const order = await db.master.insert('orders', data)  await redis.set(`force-master:${userId}`, '1', 'EX', 2)   // 2 秒内读主  return order}function pickConnection(userId) {  return forceMaster.has(userId) ? db.master : db.replica}

SHOW SLAVE STATUS\G-- 关注 Seconds_Behind_Master：持续增大说明从库跟不上

-- JSON 字段：适合结构不固定的扩展信息ALTER TABLE products ADD COLUMN attrs JSON;-- 可以针对 JSON 内部字段建索引（虚拟列）ALTER TABLE products ADD COLUMN brand VARCHAR(50)  GENERATED ALWAYS AS (JSON_UNQUOTE(attrs->'$.brand')) STORED;CREATE INDEX idx_brand ON products(brand);

-- ✅ 用数据库约束兜住并发ALTER TABLE user_coupons ADD UNIQUE KEY uk_user_coupon (user_id, coupon_id);

-- 表大小与行数SELECT table_name,       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,       table_rowsFROM information_schema.tablesWHERE table_schema = 'your_db'ORDER BY data_length DESCLIMIT 20;-- 索引使用情况（找出从未使用的索引）SELECT * FROM sys.schema_unused_indexes;-- 冗余/重复索引SELECT * FROM sys.schema_redundant_indexes;

-- ✅ 批量插入（单次 500–1000 条）INSERT INTO logs (user_id, action, created_at) VALUES  (1, 'login', NOW()), (2, 'view', NOW()), ...;-- ✅ 大范围更新分批，避免长事务与主从延迟UPDATE orders SET archived = 1 WHERE id BETWEEN 1 AND 1000;-- 循环推进，每批 sleep 几十毫秒，降低对线上影响

-- 归档策略：冷热分离-- 1. 将 1 年前数据迁到归档表INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);-- 2. 分批删除（避免大事务锁表）DELETE FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR) LIMIT 1000;-- 循环执行直到影响行数为 0

# 逻辑备份（可读、跨版本，但慢）mysqldump --single-transaction --routines --triggers db_name | gzip > db_$(date +%F).sql.gz# 恢复gunzip < db_2026-01-01.sql.gz | mysql db_name