Charon's Blog

当前主题

MySQL索引设计原则与最佳实践

2024年7月5日·
MySQL数据库索引设计性能优化

MySQL索引设计原则与最佳实践 ​

索引设计是数据库性能优化的基础，本文详细介绍MySQL索引设计的核心原则和实践方法，帮助开发者构建高效的数据库结构。

索引基础知识 ​

索引数据结构 ​

MySQL支持多种索引数据结构，其中最常用的是B+树索引：

                    [15, 30]
                   /   |    \
                  /    |     \
         [5, 10]       |      [35, 40, 45]
        /  |   \       |      /    |    \   \
     [1,2] [6,7] [11,12,13] [16,20,25] [31,32] [36,37] [41,42] [46,48,49]

B+树索引具有以下特点：

  • 所有数据记录都存储在叶子节点
  • 非叶子节点只存储键值和指针
  • 叶子节点通过链表连接，支持范围查询
  • 树的高度通常为2-4层，查询复杂度为O(log n)

主要索引类型对比 ​

索引类型底层结构优点缺点适用场景
聚簇索引B+树数据访问快速插入可能导致页分裂主键查询
二级索引B+树灵活创建多个需要回表查询非主键列查询
哈希索引哈希表等值查询O(1)不支持范围查询精确匹配场景
全文索引倒排索引支持文本搜索维护开销大文本检索

索引设计核心原则 ​

选择合适的索引列 ​

索引列的选择决定了索引的效率，应考虑以下因素：

1. 高选择性原则 ​

选择性(Selectivity)是指不同值的个数与表记录总数的比值，越接近1越好：

sql
-- 计算列的选择性
SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

索引选择性比较：

列名选择性是否适合做索引
性别0.0002 (2/10000)不适合
手机号0.9998 (9998/10000)非常适合
城市0.02 (200/10000)一般

2. 查询频率原则 ​

频繁出现在以下语句中的列应优先考虑：

  • WHERE子句条件列
  • JOIN连接条件列
  • ORDER BY排序列
  • GROUP BY分组列

联合索引设计策略 ​

联合索引的设计是提高复杂查询性能的关键：

最左前缀原则示例 ​

对于联合索引(A, B, C)：

有效的索引使用:
WHERE A = ?                 (使用索引)
WHERE A = ? AND B = ?       (使用索引)
WHERE A = ? AND B = ? AND C = ? (完全使用索引)

部分有效:
WHERE A = ? AND C = ?       (只使用A部分)

无效的索引使用:
WHERE B = ?                 (不使用索引)
WHERE C = ?                 (不使用索引)
WHERE B = ? AND C = ?       (不使用索引)

列顺序设计原则 ​

联合索引列顺序的决定因素：

  1. 等值条件优先：将等值查询(=)的列放在前面
  2. 选择性优先：高选择性的列放在前面
  3. 范围条件最后：范围查询列放在最后

示例:

sql
-- 查询模式
SELECT * FROM orders 
WHERE customer_id = ? AND status = ? AND order_date BETWEEN ? AND ?;

-- 最佳索引设计 (根据上述规则)
CREATE INDEX idx_orders_customer_status_date ON orders(customer_id, status, order_date);

特殊索引设计技术 ​

前缀索引设计 ​

对于长字符串字段，可以只索引开头的部分字符：

sql
-- 创建前缀索引
CREATE INDEX idx_email ON customers(email(8));

-- 确定最佳前缀长度
SELECT 
  COUNT(DISTINCT LEFT(email, 6)) / COUNT(*) AS prefix_6,
  COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS prefix_8,
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_10,
  COUNT(DISTINCT email) / COUNT(*) AS full_column
FROM customers;

前缀长度选择标准：在索引大小和选择性之间找到平衡点。

覆盖索引设计 ​

覆盖索引是包含查询所需所有字段的索引，可以避免回表操作：

sql
-- 创建覆盖索引
CREATE INDEX idx_products_cat_brand_price ON products(category_id, brand_id, price);

-- 使用覆盖索引的查询
SELECT category_id, brand_id, price FROM products
WHERE category_id = 5 AND brand_id = 10;

函数索引设计(MySQL 8.0+) ​

针对函数查询条件设计的索引：

sql
-- 创建函数索引
CREATE INDEX idx_year_created ON orders((YEAR(created_at)));

-- 使用函数索引
SELECT * FROM orders WHERE YEAR(created_at) = 2023;

实际场景索引设计案例 ​

用户系统索引设计 ​

用户表通常是访问频率最高的表之一，其索引设计至关重要：

sql
CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  phone VARCHAR(20),
  password VARCHAR(64) NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  last_login DATETIME
);

-- 索引设计
CREATE UNIQUE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_phone ON users(phone);
CREATE INDEX idx_status_created ON users(status, created_at);

设计说明：

  • 用户名和邮箱需要唯一索引确保唯一性
  • 电话号码通常用于登录和查询，需要单独索引
  • 状态和创建时间的组合索引用于筛选特定状态的用户并按时间排序

订单系统索引设计 ​

订单表涉及复杂的查询和分析操作，索引设计尤为重要：

sql
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id INT NOT NULL,
  order_no VARCHAR(30) NOT NULL,
  status TINYINT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  payment_method TINYINT,
  created_at DATETIME NOT NULL,
  paid_at DATETIME,
  completed_at DATETIME
);

-- 索引设计
CREATE UNIQUE INDEX idx_order_no ON orders(order_no);
CREATE INDEX idx_user_created ON orders(user_id, created_at);
CREATE INDEX idx_status_created ON orders(status, created_at);
CREATE INDEX idx_created_at ON orders(created_at);
CREATE INDEX idx_payment_status_created ON orders(payment_method, status, created_at);

设计说明：

  • 订单号需要唯一索引
  • 用户ID和创建时间的组合索引用于查询用户订单历史
  • 状态和创建时间的组合索引用于查询特定状态的订单
  • 创建时间单独索引用于按时间范围查询和报表统计
  • 支付方式、状态和创建时间的组合索引用于财务统计和分析

索引设计决策流程 ​

索引设计流程图 ​

开始
 ↓
确定查询模式
 ↓
分析数据分布 → 计算列选择性
 ↓
确定可能的索引列
 ↓
按等值条件、选择性和访问频率排序
 ↓
确定联合索引中的列顺序
 ↓
测试索引效果 → 分析EXPLAIN计划
 ↓
评估索引维护成本
 ↓
监控索引使用情况
 ↓
定期优化现有索引
 ↓
结束

索引设计检查清单 ​

✅ 是否考虑了所有WHERE、JOIN、ORDER BY和GROUP BY子句中的列？
✅ 索引列的选择性是否足够高？
✅ 联合索引中的列顺序是否符合最左前缀原则？
✅ 是否创建了不必要的冗余索引？
✅ 索引总数是否在合理范围内？
✅ 是否考虑了索引对写入性能的影响？
✅ 是否为大字符串列使用了前缀索引？
✅ 是否利用了覆盖索引避免回表？

常见索引设计误区 ​

常见错误及解决方案 ​

常见错误问题解决方案
为每列都创建索引增加维护开销，不一定提高性能仅为高频查询和高选择性列创建索引
索引顺序不当无法充分利用联合索引遵循"等值、高选择性、高频率"原则
忽略索引选择性低选择性索引效果差使用COUNT(DISTINCT)/COUNT(*)评估选择性
重复索引浪费存储空间，增加维护成本定期检查并合并重复索引
过度依赖索引忽略查询本身的优化结合SQL优化和索引设计

索引设计工具与方法 ​

MySQL索引分析工具 ​

sql
-- 查看表的索引信息
SHOW INDEX FROM table_name;

-- 分析表索引使用情况
SELECT 
  object_schema, object_name, index_name,
  count_star, count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY count_fetch DESC;

-- 查看未使用的索引
SELECT 
  object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL 
  AND count_star = 0;

第三方索引优化工具 ​

  • Percona Toolkit: pt-index-usage, pt-duplicate-key-checker
  • MySQL Workbench: 索引分析和优化建议
  • SolarWinds Database Performance Analyzer: 索引影响分析

总结 ​

高效的MySQL索引设计需要同时考虑查询模式、数据分布和硬件资源约束。遵循本文介绍的索引设计原则，可以显著提升数据库查询性能，减少资源消耗，支持更高的并发访问。

对于复杂系统，索引设计不是一次性工作，而是需要随着业务发展和数据增长不断调整和优化的过程。通过定期监控和分析索引使用情况，及时调整索引策略，才能确保数据库性能始终处于最佳状态。

Charon
展开

Charon

后端开发工程师

热爱技术，专注于前后端开发、数据库优化和软件架构设计

📚 MySQL优化技术

关注MySQL数据库性能优化的各个方面，包括索引设计、查询优化等关键技术。

1 / 3 · 完成 33%
1MySQL索引设计原则与最佳实践 当前
2Mysql Index Optimization
3Database Optimization
← 已是第一篇Mysql Index Optimization →