MySQL索引设计原则：明明建了索引为什么还是慢？7条实战原则帮你避坑

MySQL索引系列文章：

• 📖 基础篇：图解MySQL索引：从二叉树到B+树的演进之路
• 📖 进阶篇：MySQL索引：SQL性能分析工具详解
• 📖 实战篇：MySQL索引优化实战：原则速查与踩坑案例
• 📖 设计原则篇：MySQL索引设计原则：从理论到实践的完整指南
  （本文）

掌握了索引的创建和使用技巧后，如何设计出高效、合理的索引？本文将从表级别、字段级别、索引级别三个维度，系统梳理7条核心设计原则，并结合真实业务场景，帮你避免"
索引建了但效果不佳"的常见陷阱。

记得刚工作那会儿，我负责优化一个订单查询接口。明明已经给user_id
建了索引，但查询还是慢得要命。我百思不得其解，直到DBA老哥看了一眼执行计划，淡淡地说：“你这索引建得不对，字段顺序有问题。”

那一刻我才明白，索引不是建了就完事了，设计才是关键。

你是否也遇到过这样的困惑：

• 明明创建了索引，查询依然很慢？
• 索引建得太多，写入性能反而下降？
• 不知道什么时候该建索引，什么时候不该建？

索引设计就像盖房子，不是砖头越多越好，而是要在合适的地方用合适的材料。本文将从表→字段→索引
三个层次，分享7条实战中总结的设计原则，帮你建立完整的索引设计思路。

---

一、表级别：何时建立索引？

原则1：针对于数据量较大，且查询比较频繁的表建立索引

索引的收益与表的数据量和查询频率成正比。这个道理很简单：数据量越大，索引的价值越明显；查询越频繁，索引的回报越高。

为什么数据量要"较大"？

索引不是免费的午餐，它需要占用存储空间，每次写入还要维护索引结构。对于小表（比如只有几条记录的配置表、字典表），全表扫描的成本可能比索引查找还低：

-- 示例：用户状态字典表（只有5条记录）
CREATE TABLE user_status_dict (
    id INT PRIMARY KEY,
    status_name VARCHAR(20)
);

-- 这种场景下，建索引反而增加开销
CREATE INDEX idx_status_name ON user_status_dict(status_name); -- ❌ 不推荐

-- 对于小表，直接全表扫描即可
SELECT * FROM user_status_dict WHERE status_name = 'ACTIVE';

经验值：一般表数据量超过1000行，才考虑建立索引。但这个数字不是绝对的，还要看查询频率和业务特点。比如一个只有500行的表，如果每秒查询100次，那建索引也是值得的。

查询频率的重要性

即使是大表，如果某个字段极少被查询，建立索引也不划算。我见过有人给一个千万级大表的internal_remark
字段建索引，结果这个字段一个月才查一次，完全是浪费：

-- 订单表（1000万条记录）
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    created_at DATETIME,
    -- 某个业务字段，但只在后台统计时偶尔查询
    internal_remark TEXT
);

-- ❌ 不推荐：查询频率低，但维护成本高
CREATE INDEX idx_internal_remark ON orders(internal_remark);

-- ✅ 推荐：高频查询字段建立索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);

建议：

• 通过慢查询日志统计SQL执行频次，优先为高频SQL涉及的字段建立索引
• 对于低频查询场景，可以考虑用LIMIT限制查询范围，或者走异步查询+缓存

---

二、字段级别：哪些字段适合建索引？

原则2：针对于常作为查询条件（WHERE）、排序（ORDER BY）、分组（GROUP BY）操作的字段建立索引

索引主要有三大应用场景：过滤（WHERE）、排序（ORDER BY）、分组（GROUP BY）。记住这三个场景，基本就能判断哪些字段需要建索引了。

WHERE 条件字段

-- 用户表
CREATE TABLE user_profile (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    age INT,
    city VARCHAR(50),
    created_at DATETIME
);

-- ✅ 高频WHERE条件字段建立索引
CREATE INDEX idx_email ON user_profile(email);
CREATE INDEX idx_phone ON user_profile(phone);

-- 查询示例
SELECT * FROM user_profile WHERE email = 'user@example.com'; -- 命中索引

ORDER BY 排序字段

-- 商品表
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(200),
    price DECIMAL(10,2),
    sales_count INT,
    created_at DATETIME
);

-- ✅ 为排序字段建立索引，避免filesort
CREATE INDEX idx_price ON products(price);
CREATE INDEX idx_sales_created ON products(sales_count DESC, created_at DESC);

-- 查询示例：避免临时排序
SELECT * FROM products ORDER BY price ASC LIMIT 20; -- 可以利用索引顺序

注意：如果ORDER BY和WHERE条件结合，需要设计联合索引：

-- ✅ 联合索引设计：WHERE在前，ORDER BY在后
CREATE INDEX idx_category_price ON products(category_id, price);

-- 这个查询可以利用索引同时完成过滤和排序
SELECT * FROM products 
WHERE category_id = 100 
ORDER BY price ASC 
LIMIT 20;

GROUP BY 分组字段

-- 订单表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status VARCHAR(20),
    amount DECIMAL(10,2),
    created_at DATETIME
);

-- ✅ 为GROUP BY字段建立索引，避免临时表排序
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 查询示例：按状态分组统计每日订单数
SELECT status, DATE(created_at) as order_date, COUNT(*) 
FROM orders 
GROUP BY status, DATE(created_at); 
-- 如果设计合理，可以利用索引避免filesort

踩坑案例：ORDER BY和GROUP BY混合使用时，需要仔细设计索引顺序：

-- 订单表索引设计
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- ❌ 问题查询：GROUP BY和ORDER BY顺序与索引不完全匹配
SELECT user_id, status, COUNT(*) as order_count
FROM orders
WHERE user_id = 1001
GROUP BY status
ORDER BY created_at DESC; -- created_at在GROUP BY之后，可能导致filesort

-- ✅ 优化方案1：调整索引顺序或SQL写法
SELECT user_id, status, COUNT(*) as order_count
FROM orders
WHERE user_id = 1001
GROUP BY status
ORDER BY status; -- 与GROUP BY一致，可以利用索引

-- ✅ 优化方案2：使用子查询分离逻辑
SELECT t.* FROM (
    SELECT user_id, status, COUNT(*) as order_count
    FROM orders
    WHERE user_id = 1001
    GROUP BY status
) t
ORDER BY order_count DESC; -- 在子查询结果上排序

---

原则3：尽量选择区分度高的列建立索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

索引的选择性（Cardinality）决定了索引的查找效率。简单说，区分度越高，索引效果越好。

什么是区分度？

区分度 = 不重复的索引值数量 / 表中的总记录数

• 区分度越高（接近1）：索引效果越好，如主键、唯一字段
• 区分度越低（接近0）：索引效果越差，如性别字段、状态字段

-- 示例：用户表
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,  -- 区分度：100%
    email VARCHAR(100) UNIQUE,    -- 区分度：100%
    gender TINYINT,               -- 区分度：~2%（只有男/女）
    status TINYINT                -- 区分度：~5%（只有几个状态值）
);

-- ✅ 高区分度字段：建立唯一索引
CREATE UNIQUE INDEX uk_username ON users(username);
CREATE UNIQUE INDEX uk_email ON users(email);

-- ❌ 低区分度字段：不建议单独建立索引
CREATE INDEX idx_gender ON users(gender); -- 不推荐
CREATE INDEX idx_status ON users(status); -- 不推荐

-- ✅ 但如果需要与其他字段组合查询，可以建立联合索引
CREATE INDEX idx_status_created ON users(status, created_at);

如何计算区分度？

-- 计算某个字段的区分度
SELECT 
    COUNT(DISTINCT status) as distinct_count,  -- 不重复值数量
    COUNT(*) as total_count,                    -- 总记录数
    COUNT(DISTINCT status) * 1.0 / COUNT(*) as selectivity  -- 区分度
FROM orders;

-- 输出示例：
-- distinct_count: 5
-- total_count: 1000000
-- selectivity: 0.000005  (区分度极低，不建议单独建索引)

经验值（仅供参考）：

• 区分度 > 30%：适合建立单列索引
• 区分度 10% - 30%：建议建立联合索引
• 区分度 < 10%：不建议单独建立索引，除非与其他字段组合

唯一索引的优势

唯一索引不仅能保证数据唯一性，还能带来额外的性能优化：

-- ✅ 唯一索引：查找时可以立即停止（找到一条即停止）
CREATE UNIQUE INDEX uk_order_no ON orders(order_no);
SELECT * FROM orders WHERE order_no = 'ORDER20251109001'; -- 找到即停止

-- 普通索引：需要继续查找是否有重复值（虽然已经找到目标）
CREATE INDEX idx_user_id ON orders(user_id);
SELECT * FROM orders WHERE user_id = 1001; -- 需要扫描所有匹配行

---

原则4：如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引

长字符串字段如果建立完整索引，占用空间会很大。比如一个VARCHAR(1000)的URL字段，如果完整索引，每个索引项可能占用1000字节。这时候可以用前缀索引，
只索引前N个字符，在查询性能和存储成本之间找平衡。

为什么需要前缀索引？

-- 示例：文章表
CREATE TABLE articles (
    id BIGINT PRIMARY KEY,
    title VARCHAR(500),      -- 标题可能很长
    content TEXT,            -- 内容很长，不适合建索引
    url VARCHAR(1000)        -- URL很长
);

-- ❌ 完整索引：占用空间大
CREATE INDEX idx_url ON articles(url); -- 每个索引项可能占用1000字节

-- ✅ 前缀索引：节省空间
CREATE INDEX idx_url_prefix ON articles(url(50)); -- 只索引前50个字符

如何确定前缀长度？

通过计算不同前缀长度的区分度，选择最合适的长度：

-- 方法1：逐步测试不同前缀长度的区分度
SELECT 
    COUNT(DISTINCT LEFT(url, 10)) / COUNT(*) as prefix_10_selectivity,
    COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) as prefix_20_selectivity,
    COUNT(DISTINCT LEFT(url, 50)) / COUNT(*) as prefix_50_selectivity,
    COUNT(DISTINCT LEFT(url, 100)) / COUNT(*) as prefix_100_selectivity
FROM articles;

-- 输出示例：
-- prefix_10_selectivity: 0.8500  (85%的区分度)
-- prefix_20_selectivity: 0.9500  (95%的区分度)
-- prefix_50_selectivity: 0.9950  (99.5%的区分度)
-- prefix_100_selectivity: 0.9990 (99.9%的区分度，但空间开销大)

-- 根据业务需求选择：如果95%的区分度已足够，选择前缀长度20
CREATE INDEX idx_url_prefix ON articles(url(20));

前缀索引的局限性

-- ❌ 前缀索引无法用于ORDER BY和GROUP BY
SELECT * FROM articles ORDER BY url; -- 无法利用前缀索引完成排序

-- ❌ 前缀索引无法用于覆盖索引
-- 如果查询需要完整的url值，仍然需要回表
SELECT url FROM articles WHERE url LIKE 'https://example.com%';
-- 即使命中前缀索引，由于索引只存储了前N个字符，仍需回表获取完整url

建议：

• 前缀长度选择：区分度达到90%以上即可，不用追求100%
• 常见场景：邮箱、URL、身份证号等长字符串字段
• 注意权衡：查询性能 vs 存储空间 vs 索引维护成本，没有标准答案，看业务需求

---

三、索引级别：如何设计索引结构？

原则5：尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

联合索引可以同时满足多个查询条件，还能实现覆盖索引优化。一个联合索引往往能顶多个单列索引，既省空间又提性能。

联合索引 vs 多个单列索引

-- 订单表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status VARCHAR(20),
    created_at DATETIME,
    amount DECIMAL(10,2)
);

-- ❌ 方案1：多个单列索引（不推荐）
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);

-- 问题1：存储空间大（每个索引都是独立的B+树）
-- 问题2：查询多条件时，可能只使用其中一个索引
SELECT * FROM orders 
WHERE user_id = 1001 AND status = 'PAID' AND created_at > '2025-01-01';
-- 优化器可能只选择其中一个索引，其他条件需要回表过滤

-- ✅ 方案2：联合索引（推荐）
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 优势1：一次索引查找可以满足多个条件
-- 优势2：如果查询字段都在索引中，可以实现覆盖索引
SELECT user_id, status, created_at 
FROM orders 
WHERE user_id = 1001 AND status = 'PAID';
-- Extra: Using index（覆盖索引，无需回表）

联合索引的覆盖索引优势

-- 用户行为表
CREATE TABLE user_actions (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    action_type VARCHAR(20),
    created_at DATETIME,
    action_detail TEXT  -- 大字段，存储在数据页
);

-- 联合索引设计
CREATE INDEX idx_user_action_created ON user_actions(user_id, action_type, created_at);

-- ✅ 覆盖索引查询：无需回表
SELECT user_id, action_type, created_at 
FROM user_actions 
WHERE user_id = 1001 AND action_type = 'LOGIN';
-- Explain结果：Extra = "Using index"

-- ❌ 非覆盖索引查询：需要回表
SELECT user_id, action_type, created_at, action_detail 
FROM user_actions 
WHERE user_id = 1001 AND action_type = 'LOGIN';
-- Explain结果：Extra = "Using index condition"（需要回表读取action_detail）

性能对比：

场景	单列索引	联合索引（覆盖）	性能提升
存储空间	3个独立索引	1个联合索引	节省约60%空间
查询耗时	回表读取	无需回表	减少50-80%IO
索引维护	3次维护	1次维护	写入性能提升

联合索引的设计顺序

联合索引的列顺序非常重要，遵循最左前缀原则：

-- 索引：idx_user_status_created (user_id, status, created_at)

-- ✅ 可以利用索引
SELECT * FROM orders WHERE user_id = 1001;
SELECT * FROM orders WHERE user_id = 1001 AND status = 'PAID';
SELECT * FROM orders WHERE user_id = 1001 AND status = 'PAID' AND created_at > '2025-01-01';

-- ❌ 无法利用索引（跳过最左列）
SELECT * FROM orders WHERE status = 'PAID';
SELECT * FROM orders WHERE created_at > '2025-01-01';

-- ⚠️ 部分利用索引（只能用到user_id）
SELECT * FROM orders WHERE user_id = 1001 AND created_at > '2025-01-01';

设计原则：

1. WHERE条件频率：将最常作为查询条件的列放在最前面
2. 选择性高低：将区分度高的列放在前面
3. 等值 vs 范围：等值查询列在前，范围查询列在后
4. 排序需求：如果需要ORDER BY，将排序字段放在索引末尾

-- 实战案例：设计一个电商订单查询索引

-- 查询场景1：根据用户ID查询（90%的查询）
SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at DESC;

-- 查询场景2：根据用户ID和状态查询（8%的查询）
SELECT * FROM orders WHERE user_id = 1001 AND status = 'PAID';

-- 查询场景3：根据用户ID和日期范围查询（2%的查询）
SELECT * FROM orders WHERE user_id = 1001 AND created_at BETWEEN '2025-01-01' AND '2025-01-31';

-- ✅ 推荐索引设计
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at DESC);

-- 理由：
-- 1. user_id 放在最前面（最高频查询条件）
-- 2. status 放在中间（选择性较高，且常与user_id组合查询）
-- 3. created_at 放在最后（支持范围查询和排序，DESC匹配业务需求）

---

原则6：要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率

索引是一把双刃剑。建多了，查询快但写入慢；建少了，写入快但查询慢。关键是要找到平衡点。

索引对写入性能的影响

每次INSERT、UPDATE、DELETE操作，都需要维护相关索引：

-- 用户表
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    age INT,
    city VARCHAR(50),
    status TINYINT,
    created_at DATETIME,
    updated_at DATETIME
);

-- ❌ 过度索引：为每个字段都建立索引
CREATE UNIQUE INDEX uk_username ON users(username);
CREATE UNIQUE INDEX uk_email ON users(email);
CREATE INDEX idx_phone ON users(phone);
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_city ON users(city);
CREATE INDEX idx_status ON users(status);
CREATE INDEX idx_created ON users(created_at);
CREATE INDEX idx_updated ON users(updated_at);
-- 总共8个索引（包括主键）

-- 写入性能测试
INSERT INTO users (username, email, phone, age, city, status) 
VALUES ('user1', 'user1@example.com', '13800138000', 25, 'Beijing', 1);
-- 需要维护：主键索引 + 8个二级索引 = 9次索引更新
-- 写入耗时：~15ms

性能影响分析：

索引数量	INSERT耗时	UPDATE耗时	DELETE耗时	索引维护成本
1个（主键）	2ms	2ms	2ms	低
3个索引	5ms	6ms	5ms	中
5个索引	10ms	12ms	10ms	中高
8个索引	15ms	18ms	15ms	高

如何控制索引数量？

策略1：合并单列索引为联合索引

-- ❌ 不推荐：多个单列索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);

-- ✅ 推荐：合并为联合索引（如果经常组合查询）
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

策略2：删除低频使用的索引

-- 查询索引使用情况（MySQL 5.7+）
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
  AND OBJECT_NAME = 'orders'
ORDER BY COUNT_FETCH DESC;

-- 如果某个索引的COUNT_FETCH为0或很小，考虑删除
-- 注意：需要观察一段时间，避免删除季节性查询使用的索引

策略3：为不同业务场景设计不同索引

-- 订单表：同时支持C端用户查询和B端运营查询

-- C端查询场景（高频）：用户查看自己的订单
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at DESC);

-- B端查询场景（低频）：运营按状态查询所有订单
-- 如果频率不高，可以不建索引，或使用其他优化手段（如分区表、ES等）
-- 如果必须建，考虑建立单独的索引，但需要评估写入性能影响
CREATE INDEX idx_status_created ON orders(status, created_at DESC);

经验值（仅供参考）：

• 单表索引数量控制在5-7个以内，更容易管理和维护
• 核心业务表：可以适当放宽到8-10个，但需要严格监控写入性能
• 配置表/日志表：建议不超过3个索引，毕竟写入频率高

索引维护成本的量化

-- 监控索引维护成本
-- 1. 查看表的总索引大小
SELECT 
    table_name,
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS index_size_mb,
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS data_size_mb,
    ROUND(SUM(index_length) / SUM(data_length) * 100, 2) AS index_ratio_percent
FROM information_schema.tables
WHERE table_schema = 'your_database'
  AND table_name = 'orders'
GROUP BY table_name;

-- 如果index_ratio_percent > 50%，说明索引占用空间过大，需要优化

-- 2. 监控写入性能
-- 通过慢查询日志或performance_schema监控INSERT/UPDATE/DELETE的耗时

---

原则7：如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

NOT NULL约束不仅能保证数据完整性，还能帮助优化器做出更好的索引选择决策。**优化器知道字段不会有NULL值，就能更准确地估算查询成本，选择最优索引
**。

NULL值对索引的影响

-- ❌ 允许NULL值的索引
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20)  -- 允许NULL
);

CREATE INDEX idx_phone ON users(phone);

-- 问题1：NULL值在索引中的存储
-- B+树索引中，NULL值通常会被特殊处理，可能影响索引的紧凑性

-- 问题2：查询时需要额外判断NULL
SELECT * FROM users WHERE phone = '13800138000';
-- 如果phone字段允许NULL，查询计划可能需要考虑NULL值的情况

-- 问题3：统计信息可能不够准确
-- NULL值的存在可能影响索引的选择性统计，导致优化器选择错误的索引

NOT NULL的优化优势

-- ✅ 使用NOT NULL约束
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,  -- 明确不允许NULL
    age INT NOT NULL DEFAULT 0,
    status TINYINT NOT NULL DEFAULT 1
);

CREATE INDEX idx_phone ON users(phone);
CREATE INDEX idx_status_age ON users(status, age);

-- 优势1：优化器可以更准确地估算索引选择性
SELECT * FROM users WHERE phone = '13800138000';
-- 优化器知道phone字段不会有NULL值，可以更准确地计算查询成本

-- 优势2：索引结构更紧凑
-- 不需要为NULL值预留空间或特殊处理

-- 优势3：查询可以更简单
SELECT COUNT(*) FROM users WHERE phone IS NOT NULL; -- 如果phone是NOT NULL，这个条件永远为真

实战案例：优化器选择索引

-- 订单表设计对比

-- 方案1：允许NULL（不推荐）
CREATE TABLE orders_v1 (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,           -- 允许NULL
    status VARCHAR(20),       -- 允许NULL
    created_at DATETIME,      -- 允许NULL
    INDEX idx_user_status (user_id, status)
);

-- 查询：优化器需要判断NULL值的影响
SELECT * FROM orders_v1 
WHERE user_id = 1001 AND status = 'PAID';
-- 优化器可能认为：如果user_id或status可能为NULL，索引效果可能不佳

-- 方案2：使用NOT NULL（推荐）
CREATE TABLE orders_v2 (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,      -- 明确NOT NULL
    status VARCHAR(20) NOT NULL,  -- 明确NOT NULL
    created_at DATETIME NOT NULL, -- 明确NOT NULL
    INDEX idx_user_status (user_id, status)
);

-- 查询：优化器可以更自信地使用索引
SELECT * FROM orders_v2 
WHERE user_id = 1001 AND status = 'PAID';
-- 优化器知道所有行都有user_id和status值，可以更准确地选择索引

如何为现有表添加NOT NULL约束？

-- 步骤1：检查现有数据是否包含NULL值
SELECT 
    COUNT(*) as total_rows,
    SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) as null_count
FROM users;

-- 步骤2：如果存在NULL值，先更新为默认值
UPDATE users SET phone = '' WHERE phone IS NULL;

-- 步骤3：添加NOT NULL约束
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';

-- 步骤4：验证约束生效
SHOW CREATE TABLE users;

建议：

1. 建表时就定义NOT NULL：避免后续修改的麻烦（改表结构可能锁表）
2. 为NOT NULL字段设置合理的默认值：如DEFAULT ''、DEFAULT 0、DEFAULT CURRENT_TIMESTAMP
3. 业务层面保证数据完整性：应用层校验 + 数据库约束双重保障，更稳妥

---

四、索引设计实战：完整案例

案例：电商订单系统的索引设计

假设我们需要设计一个电商订单表的索引，以下是业务场景和SQL查询模式：

业务场景：

• 表数据量：1000万+订单
• 写入频率：每秒1000+订单
• 查询场景：
  1. 用户查看自己的订单列表（按创建时间倒序） - 80%查询
  2. 用户按状态筛选订单 - 15%查询
  3. 运营按状态统计订单 - 5%查询

表结构设计：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL COMMENT '订单号',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    status VARCHAR(20) NOT NULL COMMENT '订单状态',
    total_amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',
    created_at DATETIME NOT NULL COMMENT '创建时间',
    updated_at DATETIME NOT NULL COMMENT '更新时间',
    INDEX idx_order_no (order_no),
    INDEX idx_user_created (user_id, created_at DESC),
    INDEX idx_user_status_created (user_id, status, created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

索引设计分析：

1. 主键索引：id - 必须的，用于唯一标识和聚簇索引
2. 唯一索引：idx_order_no - 订单号查询，区分度高（100%）
3. 联合索引1：idx_user_created - 覆盖场景1（用户查看订单列表）
4. 联合索引2：idx_user_status_created - 覆盖场景2（用户按状态筛选）

查询场景验证：

-- 场景1：用户查看订单列表（命中idx_user_created）
EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 
ORDER BY created_at DESC 
LIMIT 20;
-- type: ref, key: idx_user_created, Extra: Using index condition

-- 场景2：用户按状态筛选订单（命中idx_user_status_created）
EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 AND status = 'PAID' 
ORDER BY created_at DESC;
-- type: ref, key: idx_user_status_created, Extra: Using index condition

-- 场景3：运营统计（考虑是否需要单独索引）
EXPLAIN SELECT status, COUNT(*) FROM orders 
WHERE created_at >= '2025-01-01' 
GROUP BY status;
-- 如果频率不高，可以不建索引；如果频率高，考虑建立idx_status_created

优化建议：

1. 索引数量控制：当前4个索引（含主键），符合"5-7个"原则
2. 索引合并优化：idx_user_created和idx_user_status_created有重叠，但为了覆盖不同查询场景，保留两个索引是合理的
3. 覆盖索引优化：如果查询只需要部分字段，可以调整索引包含的列，实现覆盖索引：

-- 如果查询只需要id、user_id、status、created_at
-- 可以设计覆盖索引
CREATE INDEX idx_user_status_created_covering 
ON orders(user_id, status, created_at DESC, id);

-- 查询时可以实现覆盖索引
EXPLAIN SELECT id, user_id, status, created_at 
FROM orders 
WHERE user_id = 1001 AND status = 'PAID';
-- Extra: Using index（无需回表）

---

五、索引设计检查清单

在实际项目中，建议使用以下检查清单来评估索引设计是否合理：

✅ 表级别检查

• 表数据量是否足够大（>1000行）？
• 表是否高频查询？
• 是否评估过索引对写入性能的影响？

✅ 字段级别检查

• 是否为WHERE条件的字段建立了索引？
• 是否为ORDER BY的字段建立了索引？
• 是否为GROUP BY的字段建立了索引？
• 字段区分度是否足够高（>30%）？
• 字符串字段是否考虑使用前缀索引？
• 索引字段是否设置为NOT NULL？

✅ 索引级别检查

• 是否优先使用联合索引而非多个单列索引？
• 联合索引的列顺序是否遵循最左前缀原则？
• 是否考虑了覆盖索引优化？
• 索引数量是否控制在合理范围（5-7个）？
• 是否定期清理低频使用的索引？

✅ 性能验证

• 是否使用EXPLAIN验证索引使用情况？
• 是否监控索引的查询命中率？
• 是否评估索引对写入性能的影响？
• 是否定期更新表的统计信息（ANALYZE TABLE）？

---

六、常见误区与避坑指南

误区1：为所有字段都建立索引

错误做法：

-- ❌ 为每个字段都建立索引
CREATE TABLE users (...);
CREATE INDEX idx_field1 ON users(field1);
CREATE INDEX idx_field2 ON users(field2);
CREATE INDEX idx_field3 ON users(field3);
-- ... 10多个索引

正确做法：

• 只为核心查询字段建立索引
• 通过慢查询日志分析真实查询模式
• 定期审计索引使用情况，删除无用索引

误区2：忽略索引维护成本

错误做法：

-- ❌ 只看查询性能，忽略写入性能
-- 某个表有15个索引，查询很快，但写入慢如蜗牛

正确做法：

• 建立索引前评估写入频率
• 对于写多读少的表，严格控制索引数量
• 监控写入性能指标（TPS、延迟）

误区3：联合索引顺序随意设计

错误做法：

-- ❌ 不考虑查询模式，随意设计索引顺序
CREATE INDEX idx_created_user_status ON orders(created_at, user_id, status);
-- 但实际查询是 WHERE user_id = ? AND status = ?

正确做法：

• 分析所有查询SQL，找出最常用的查询模式
• 按照查询频率和选择性设计索引顺序
• 使用EXPLAIN验证索引是否被正确使用

误区4：过度依赖覆盖索引

错误做法：

-- ❌ 为了覆盖索引，把很多字段都加到索引中
CREATE INDEX idx_user_covering ON orders(user_id, status, created_at, amount, ...);
-- 索引变得很大，维护成本高

正确做法：

• 覆盖索引只包含高频查询的字段
• 权衡索引大小和维护成本
• 对于低频查询，可以接受回表操作

---

七、总结

索引设计是一个需要权衡的过程，没有银弹，只有最适合的方案。本文从表→字段→索引三个层次，系统梳理了7条核心设计原则：

核心原则回顾

1. 表级别：数据量大且查询频繁的表才建索引
2. 字段级别：为WHERE/ORDER BY/GROUP BY字段建索引
3. 区分度优先：选择区分度高的字段，尽量建唯一索引
4. 前缀索引：长字符串字段使用前缀索引平衡性能和存储
5. 联合索引：优先使用联合索引，实现覆盖索引优化
6. 控制数量：索引数量控制在5-7个，平衡查询和写入性能
7. NOT NULL约束：索引字段使用NOT NULL，帮助优化器做出更好决策

设计流程建议

1. 需求分析：收集所有查询SQL，分析查询模式
2. 优先级排序：按查询频率和重要性排序
3. 索引设计：根据7条原则设计索引
4. 性能验证：使用EXPLAIN和性能测试验证效果
5. 持续优化：定期审计索引使用情况，优化调整

---

记住：索引是优化数据库性能的重要手段，但不是唯一手段。在设计索引时，要结合业务场景、数据量、查询模式、写入频率等多个因素综合考虑，才能设计出真正高效的索引方案。

---

相关文章

想要系统学习MySQL索引，可以阅读以下文章：

• 索引基础：图解MySQL索引：从二叉树到B+树的演进之路（基础篇）
• 性能分析：MySQL索引：SQL性能分析工具详解（进阶篇）
• 使用技巧：MySQL索引优化实战：原则速查与踩坑案例（实战篇）
• 设计原则：MySQL索引设计原则：从理论到实践的完整指南
  （本文）

扩展阅读

• 存储引擎：MySQL存储引擎详解：老王教你如何选择合适的"发动机"