第10章索引优化实战：提升查询性能的利器

邝煜云

326人浏览 · 2026-03-05 18:52:05

邝煜云 · 2026-03-05 18:52:05 发布

.第10章索引优化实战：提升查询性能的利器

10.1 索引核心概念与商业价值

10.1.1 索引的含义：从书本目录到数据库加速

想象一下，你要在一本几百页的技术书里找到“MySQL索引”这个知识点。如果没有目录，你得一页一页翻，耗时又费力。有了目录，你就能直接翻到对应页码。数据库索引的作用就是如此——它是帮助MySQL高效获取数据的数据结构。

在MySQL中，索引由存储引擎实现，最常用的是InnoDB的B+树索引。索引虽然能极大提升查询速度，但也会占用额外存储空间，并在插入、更新、删除时增加开销。因此，索引设计是数据库性能调优的核心。

10.1.2 索引的特点：双刃剑

优点：

大大提高数据检索效率，减少磁盘IO。
通过索引列排序，可以避免文件排序，提升ORDER BY性能。
对于唯一索引，可以保证数据唯一性。
在使用分组和连接查询时，也能显著提升效率。

缺点：

索引会占用磁盘空间，尤其是组合索引和多个索引。
数据增删改时，索引也需要动态维护，降低写入性能。
过多索引会导致优化器选择困难，可能选错索引。

商业项目中的权衡：在交易系统（如订单表）中，写入频繁，不宜建太多索引；在查询频繁的表（如商品表）中，可以适当多建索引。

10.1.3 索引的分类

MySQL的索引从不同角度有多种分类：

10.1.3.1 按数据结构分

B+Tree索引：最常用，支持范围查询和排序。InnoDB的默认索引类型。
哈希索引：基于哈希表，只支持等值查询，速度极快，但无法排序和范围查询。Memory引擎默认使用，InnoDB的自适应哈希索引也是基于此。
全文索引：用于全文搜索，只有MyISAM和InnoDB（5.6+）支持。
空间索引：用于地理空间数据，MyISAM和InnoDB支持。

10.1.3.2 按应用逻辑分

普通索引（INDEX）：最基本的索引，没有任何限制。
唯一索引（UNIQUE）：索引列的值必须唯一，允许NULL（可以有多个NULL）。
主键索引（PRIMARY KEY）：特殊的唯一索引，不允许NULL，每个表只能有一个。
组合索引（复合索引）：多个字段组合成一个索引，遵循最左前缀原则。
全文索引（FULLTEXT）：用于全文搜索。
空间索引（SPATIAL）：用于空间数据类型。

10.1.3.3 按存储方式分

聚簇索引：数据行和索引存储在一起，InnoDB的主键就是聚簇索引，叶子节点包含完整数据。
二级索引（辅助索引）：叶子节点存储主键值，查询时需要回表。

10.1.4 索引的设计原则：从业务出发

在商业项目中，索引设计不能盲目，必须遵循一些原则：

明确查询模式：分析业务中主要的查询SQL，找出WHERE条件、JOIN字段、ORDER BY字段、GROUP BY字段。
选择性高的列优先：列中不同值占比越高，索引效果越好。比如性别字段选择性很低，不适合单独建索引。
频繁更新的列慎建索引：因为更新索引代价高。
尽量使用短索引：对字符串列可以指定前缀长度，节省空间。
组合索引优于多个单列索引：组合索引可以覆盖多个查询条件，且最左前缀原则使得它能被多个查询利用。
合理利用覆盖索引：如果查询的字段都在索引中，就不需要回表，效率极高。
考虑数据量：小表不需要索引，大表必须索引。
索引数量控制：单表一般不超过5-6个索引，过多影响写入。

下面我们通过一个真实的电商项目案例，逐步演示如何创建和使用索引。

10.2 索引创建实战

10.2.1 创建表时创建索引

在CREATE TABLE语句中，可以在定义字段的同时定义索引。我们以电商数据库中的几张表为例。

10.2.1.1 用户表（users）的索引设计

CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password CHAR(60) NOT NULL COMMENT '密码哈希',
    phone VARCHAR(20) COMMENT '手机号',
    email VARCHAR(100) COMMENT '邮箱',
    reg_time DATETIME NOT NULL COMMENT '注册时间',
    last_login DATETIME COMMENT '最后登录时间',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态 1正常 0禁用',
    PRIMARY KEY (user_id),  -- 主键索引（聚簇索引）
    UNIQUE KEY uk_username (username),  -- 唯一索引
    UNIQUE KEY uk_phone (phone),        -- 唯一索引
    INDEX idx_email (email),            -- 普通索引
    INDEX idx_reg_time (reg_time)       -- 普通索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

说明：

PRIMARY KEY：主键索引，InnoDB自动将其作为聚簇索引。
UNIQUE KEY：唯一索引，确保用户名和手机号唯一。
INDEX：普通索引，用于邮箱和注册时间，加速按邮箱查询和按时间范围查询。

10.2.1.2 商品表（products）的索引

商品表经常需要按名称模糊查询、按分类查询、按价格排序等。

CREATE TABLE products (
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    brand_id INT UNSIGNED,
    price DECIMAL(10,2) NOT NULL,
    stock INT UNSIGNED NOT NULL DEFAULT 0,
    status TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    INDEX idx_category (category_id),
    INDEX idx_brand (brand_id),
    INDEX idx_price (price),
    INDEX idx_name (product_name)  -- 注意：对长字符串建索引，可能性能不佳，可考虑前缀索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

10.2.1.3 订单表（orders）的索引

订单表查询条件多样：按用户查询、按时间查询、按状态查询。

CREATE TABLE orders (
    order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    order_amount DECIMAL(10,2) NOT NULL,
    pay_status TINYINT NOT NULL DEFAULT 0 COMMENT '0未支付 1已支付 2已取消',
    pay_time DATETIME,
    create_time DATETIME NOT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_pay_status (pay_status),
    INDEX idx_create_time (create_time),
    INDEX idx_user_status (user_id, pay_status)  -- 组合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

组合索引 idx_user_status 可以加速类似“查询某个用户的已支付订单”这样的查询。

10.2.1.4 创建全文索引

对于需要全文搜索的场景，比如商品描述、文章内容，可以创建全文索引。MySQL 5.7中InnoDB支持中文全文索引，但需要设置ngram分词器。

例如在商品表增加description字段，并创建全文索引：

ALTER TABLE products ADD description TEXT;
CREATE FULLTEXT INDEX ft_description ON products (description) WITH PARSER ngram;

注意：全文索引的维护成本较高，适合内容不频繁变更的表。

10.2.1.5 创建空间索引

如果需要存储地理位置，可以使用空间数据类型和空间索引。例如存储店铺坐标：

CREATE TABLE shops (
    shop_id INT PRIMARY KEY,
    name VARCHAR(100),
    location POINT NOT NULL SRID 4326,
    SPATIAL INDEX sp_location (location)
) ENGINE=InnoDB;

10.2.2 在已存在的表上创建索引

很多情况下，表已经存在，我们需要根据实际查询需求添加索引。可以使用CREATE INDEX或ALTER TABLE语句。

10.2.2.1 使用CREATE INDEX

-- 创建普通索引
CREATE INDEX idx_email ON users(email);

-- 创建唯一索引
CREATE UNIQUE INDEX uk_phone ON users(phone);

-- 创建组合索引
CREATE INDEX idx_user_status ON orders(user_id, pay_status);

-- 创建全文索引
CREATE FULLTEXT INDEX ft_description ON products(description) WITH PARSER ngram;

-- 创建空间索引
CREATE SPATIAL INDEX sp_location ON shops(location);

10.2.2.2 使用ALTER TABLE

-- 添加普通索引
ALTER TABLE users ADD INDEX idx_email (email);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX uk_phone (phone);

-- 添加主键索引（如果之前没有）
ALTER TABLE users ADD PRIMARY KEY (user_id);

-- 添加组合索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, pay_status);

10.2.2.3 前缀索引

对于很长的字符串列（如VARCHAR(255)），可以只索引前几个字符，节省空间，但可能降低选择性。例如，对product_name创建前缀索引：

CREATE INDEX idx_name_prefix ON products (product_name(20));

选择合适的前缀长度需要计算选择性：不同前缀长度下，不同值比例与原字段的比例接近即可。

10.3 索引维护与删除

10.3.1 查看索引

使用SHOW INDEX FROM 表名查看表的索引信息：

SHOW INDEX FROM users\G

输出包括索引名称、字段、是否唯一、索引类型等。

10.3.2 删除索引

当索引不再需要，或者发现索引影响写入性能时，应及时删除。

-- 使用DROP INDEX
DROP INDEX idx_email ON users;

-- 使用ALTER TABLE
ALTER TABLE users DROP INDEX idx_email;

-- 删除主键索引（如果有自增，需先取消自增）
ALTER TABLE users DROP PRIMARY KEY;

10.3.3 索引的修改

MySQL没有直接修改索引的语句，通常需要先删除旧的，再创建新的。

例如，将普通索引改为唯一索引：

ALTER TABLE users DROP INDEX idx_phone,
ADD UNIQUE INDEX uk_phone (phone);

10.3.4 索引的可见性（MySQL 8.0支持，5.7不支持）

MySQL 5.7没有不可见索引功能，但我们可以通过删除/添加来控制。

10.4 综合案例：电商平台索引设计与优化

10.4.1 业务场景描述

假设我们运营一个中型电商平台，核心数据库表包括：用户表、商品表、订单表、订单明细表、商品分类表等。业务需求如下：

用户登录：根据用户名或手机号查询用户信息。
商品列表：按分类、品牌、价格范围筛选，按销量、价格排序。
商品搜索：根据商品名称模糊查询。
订单查询：用户查看自己的订单，按时间倒序；后台管理员按状态、时间查询订单。
销售统计：按天统计每个商品的销量。

10.4.2 分析查询并设计索引

10.4.2.1 用户登录查询

常用SQL：

SELECT * FROM users WHERE username = 'zhangsan';
SELECT * FROM users WHERE phone = '13800138000';

我们已经对username和phone建立了唯一索引，这些查询会非常快。

10.4.2.2 商品列表筛选

假设分类筛选：

SELECT * FROM products WHERE category_id = 10 ORDER BY price DESC LIMIT 20;

我们已有 idx_category 和 idx_price，但这里需要按分类过滤再排序。单独使用category_id过滤出大量数据，然后排序，可能仍然低效。最佳方式是建立组合索引 (category_id, price)，让排序也用到索引。

CREATE INDEX idx_category_price ON products(category_id, price);

这样，WHERE category_id=10 可以走索引，并且ORDER BY price可以直接利用索引顺序，避免文件排序。

10.4.2.3 商品名称模糊查询

如果用户输入“手机”，我们可能需要：

SELECT * FROM products WHERE product_name LIKE '%手机%';

这种查询无法使用普通索引，因为%在前。解决方案：考虑使用全文索引，或者应用层引入Elasticsearch。这里我们创建全文索引：

ALTER TABLE products ADD FULLTEXT INDEX ft_name (product_name) WITH PARSER ngram;

然后使用全文搜索：

SELECT * FROM products WHERE MATCH(product_name) AGAINST('手机' IN NATURAL LANGUAGE MODE);

10.4.2.4 用户订单查询

用户查看自己的订单，通常带状态：

SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC;

我们已经创建了 idx_user_id 和 idx_create_time。如果查询需要同时按user_id过滤并按时间排序，可以建立组合索引 (user_id, create_time) 来优化排序。

CREATE INDEX idx_user_create ON orders(user_id, create_time DESC);

注意：MySQL 5.7不支持索引中指定DESC，但索引默认是升序，排序时反向扫描也可以，效果稍差。如果必须严格按降序，可以考虑5.7的虚拟列或8.0的降序索引。这里我们暂时用升序，查询时ORDER BY create_time DESC也能利用索引（反向扫描）。

10.4.2.5 后台订单查询

后台可能按状态和时间过滤：

SELECT * FROM orders WHERE pay_status = 1 AND create_time BETWEEN '2024-01-01' AND '2024-01-31';

我们可以建立组合索引 (pay_status, create_time)。

CREATE INDEX idx_status_time ON orders(pay_status, create_time);

10.4.2.6 销售统计查询

统计每天每个商品的销量，需要从订单明细表和订单表关联：

SELECT oi.product_id, DATE(o.create_time) AS day, SUM(oi.quantity) AS total
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE o.pay_status = 1
GROUP BY oi.product_id, day;

这个查询涉及多表，索引建议：

orders表的pay_status和create_time索引。
order_items表的order_id和product_id索引。

我们在order_items上建立组合索引 (order_id, product_id) 来加速JOIN。

10.4.3 验证索引效果

使用EXPLAIN分析查询，看是否使用了预期索引。

EXPLAIN SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC;

结果中看到 possible_keys 有 idx_user_id, idx_create_time, idx_user_create，实际使用的 key 可能是 idx_user_create，说明索引生效。

10.4.4 索引的维护

随着业务发展，查询模式可能变化，我们需要定期监控慢查询日志，找出未使用索引的查询，并调整索引。同时，对频繁更新的表，要关注索引碎片，必要时重建索引。

10.4.5 索引设计的常见误区

过多索引：每个索引都占用空间，且拖慢写入。应当只为最常用的查询建立索引。
滥用组合索引：组合索引的字段顺序很重要，应把选择性高的字段放在前面。
忽略覆盖索引：如果能将查询的字段全部包含在索引中，可以避免回表，大幅提升性能。
对很小的表建索引：表只有几十行，全表扫描比索引更快，索引反而浪费空间。

10.5 专家解惑：常见索引问题与解决方案

10.5.1 为什么我建了索引，查询还是慢？

可能原因：

索引未被使用：查询条件不符合最左前缀原则，或对索引列使用了函数、计算，导致索引失效。
数据量过大，即使走索引也需要扫描大量行（如范围查询），此时可能优化器选择全表扫描。
索引选择不当，比如选择性太低（如性别字段）。
回表次数过多，导致大量随机IO。

解决方案：用EXPLAIN分析，调整索引或改写查询。

10.5.2 哪些操作会导致索引失效？

对索引列使用函数，如 WHERE DATE(create_time) = ‘2024-01-01’。
隐式类型转换，如 phone = 13800138000（phone是字符串）。
使用不等于（<>或!=）可能不走索引（但有时也会，取决于数据分布）。
LIKE以通配符开头，如 ‘%abc’。
OR条件中只要有一个字段没有索引，整个查询可能不走索引。
组合索引不满足最左前缀原则。

10.5.3 索引的存储空间有多大？

可以通过查询information_schema查看索引大小。例如：

SELECT 
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS '总大小(MB)',
    ROUND(index_length / 1024 / 1024, 2) AS '索引大小(MB)'
FROM information_schema.tables
WHERE table_schema = 'ecommerce' AND table_name = 'orders';

如果索引过大，可以考虑压缩索引（InnoDB支持），但会消耗CPU。

10.5.4 如何选择合适的索引列？

优先选择WHERE条件中的列。
其次选择ORDER BY和GROUP BY的列，可以避免文件排序。
选择选择性高的列（不同值比例高）。
对于组合索引，将最常用的列放在前面，选择性高的列也尽量靠前。

10.5.5 索引会不会拖慢插入速度？

会。每张表每多一个索引，插入一条记录就要多维护一个B+树，写入速度会下降。但在读写混合的场景中，查询性能的提升通常远大于写入的损耗。需要权衡。

10.5.6 什么时候应该重建索引？

当索引碎片较多时，重建可以回收空间并提高效率。查看碎片：

SELECT 
    table_name,
    ROUND(data_free / 1024 / 1024, 2) AS 碎片大小(MB)
FROM information_schema.tables
WHERE table_schema = 'ecommerce';

对于InnoDB表，可以使用OPTIMIZE TABLE重建表和索引：

OPTIMIZE TABLE orders;

注意：OPTIMIZE会锁表，建议在低峰期执行。

10.5.7 全文索引和普通索引能同时存在吗？

可以，全文索引用于全文搜索，普通索引用于精确查找，互不干扰。

10.5.8 索引覆盖扫描是什么？

如果查询的字段全部包含在索引中（不需要回表），称为覆盖索引。例如：

SELECT user_id, username FROM users WHERE username = 'zhangsan';

如果索引是 (username, user_id)，则可以直接从索引中获取数据，无需访问聚簇索引。这能极大提升性能。

10.6 经典习题与实战思考

简述聚簇索引和非聚簇索引的区别，InnoDB的主键为什么推荐使用自增整数？
什么是组合索引的最左前缀原则？举例说明。
给定一个订单表 orders(order_id, user_id, order_amount, status, create_time)，列出你认为需要创建的索引，并说明理由。
解释为什么在性别字段上建索引通常没有意义。
使用EXPLAIN分析一个查询，判断其是否使用了索引，以及使用了哪个索引。
如何查看一张表上已有的索引？如何删除一个索引？
创建前缀索引的语法是什么？如何选择前缀长度？
假设有一个查询 SELECT * FROM products WHERE price BETWEEN 100 AND 200 ORDER BY sales DESC; 如何设计索引以优化该查询？
什么情况下MySQL会选择全表扫描而不是走索引？
写一个SQL，查询订单表orders中最近7天每个用户的订单总数，并为该查询设计合适的索引。

本章从索引的基本概念出发，深入讲解了索引的分类、设计原则、创建方法、维护技巧，并通过电商案例展示了索引在实际项目中的设计与优化。索引是数据库性能调优的核心，掌握好索引，就能让MySQL查询如虎添翼。记住，索引不是越多越好，而是要恰到好处。不断分析查询、监控性能、调整索引，是每个DBA和开发者的必修课。