MariaDB 教程 / 创建单列与复合索引并选择列顺序

索引是帮助定位和排序记录的结构,复合索引的列顺序会影响它适合哪些查询。设计时应一起检查筛选、排序、输出列与已有索引,再衡量读取收益、写入维护和存储成本。

把索引视为需要维护的数据结构

有序键可以缩小查找范围,但插入、更新与删除也需要维护相应索引。索引不是越多越好,应结合查询频率、筛选能力、连接与排序需求判断。先写清楚要支持哪条查询,再决定列和顺序,不要看到一个字段就单独给它建索引。

查询或规则 候选键顺序 要核对什么
某客户的近期订单 customer_id、ordered_at、order_id 等值条件、时间范围与相同时间的次级顺序
全体订单按时间筛选 可能需要时间开头的设计 需求不同于客户开头的查找
商品编码唯一 UNIQUE(sku) 除了读取,还承担完整性规则
外键支持 先看 SHOW INDEX 避免在已有结构旁重复创建

设计思路可参阅官方 按 SELECT 设计索引的说明。其中经验性建议需要结合版本与实际工作负载判断,不应把固定索引数量或比例当成普遍规则。

先查看可能已经存在的索引

以 course_user 连接学习服务器,核对 SELECT VERSION(), DATABASE(); 与 SHOW TABLES;。本节独立使用 c39_customers、c39_products、c39_orders、c39_items;如果同名对象存在,先查看定义与记录,不要覆盖未知用途的数据。

以下是完整准备代码,在 MariaDB 客户端中执行。也可按 SQL 文件与 SOURCE 教程保存 UTF-8 文件,用客户端电脑上的实际路径加载,不要直接输入操作系统命令行。

USE shopcourse;
CREATE TABLE c39_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c39_order_customer FOREIGN KEY (customer_id)
  REFERENCES c39_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c39_item_order FOREIGN KEY (order_id)
  REFERENCES c39_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c39_item_product FOREIGN KEY (product_id)
  REFERENCES c39_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c39_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c39_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c39_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c39_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

姓名、城市与商品字符串保留原始韩文。商品 1~4 分别是笔记本、圆珠笔、马克杯和台灯;客户 1、3 位于首尔,2 位于釜山,4 位于大田。请按编号核对结果,保持 UTF-8 字面量一致。

SHOW INDEX FROM c39_orders;

主键与外键可能已经带来所需的索引。检查实际 Column_name 与 Seq_in_index,而不只比较名称:Seq_in_index 表示一项索引内部第几列,不是该列在表中的位置。不同名称可能对应相同列顺序,外键也可能自动得到支持键。

应同时保存 SHOW CREATE TABLE,以核对索引和约束的关系。外键设计教程说明了为什么有些键不仅服务某条查询,还支持引用完整性。

创建客户、时间与编号的复合索引

CREATE INDEX idx_c39_customer_date ON c39_orders(customer_id,ordered_at,order_id);
EXPLAIN SELECT order_id,ordered_at,status FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SELECT order_id FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SHOW INDEX FROM c39_orders;

查询应返回订单 101、102。customer_id 先限定客户,ordered_at 表达一月份的范围,order_id 规定相同时间的次级顺序。列顺序对应这条查询的要求,而不是数据库中所有日期查询都因此得到同样的帮助。

创建后再次看 SHOW INDEX,确认新索引实际顺序是 customer_id、ordered_at、order_id,且是普通非唯一索引。再比较创建前后的查询结果,编号与顺序应不变;索引优化不能以偷偷改变目标人群为代价。

EXPLAIN 显示优化器实际选择,不必为了让演示“使用新索引”而强制键。这里仅有四个订单,优化器可能合理地选择全表扫描;这份小样本用于验证结构和含义,不能证明已经加速。

谨慎理解前导列、范围与覆盖访问

复合键的作用通常与前导列有关。只按日期筛选所有客户时,不应假定一个 customer_id 开头的键能提供与日期开头键相同的高效范围访问。但这也不等于断言缺少前导等值条件时,任何形式的索引读取都绝不可能发生。

范围条件、排序方向、字符串前缀与选择的列,都会影响可用路径。对于本例,客户固定后再观察时间顺序;换成跨客户时间报表,应重新分析查询,不能只搬用同一索引名称。

覆盖索引包含完成查询所需的信息时,可以减少部分回表读取,但盲目把所有列塞进索引会增加体积与维护成本。本例 SELECT 还读取 status,不能仅因为筛选与排序列都在键里,就直接宣称全部输出已经覆盖。

应核对实际 chosen key、访问方式与 Extra,再用代表性数据评估。先确认索引支持什么,再讨论成本,避免把一条经验口诀解释成没有例外的执行保证。

检查重叠索引与约束依赖

判断是否重复,应比较列顺序、唯一性、前缀长度及约束用途。一个复合索引可能与较短键重叠,但删除前还要查看其他查询和真实外键依赖,不能仅根据名字不同或某次计划未使用就移除。

创建新的客户开头复合键后,重新检查外键支持结构如何表现。若试图删除仍支撑外键的唯一索引,数据库可能拒绝;需要先确定可用的替代结构与约束,而不是关闭检查强行删除。

PRIMARY KEY 与 UNIQUE 还承担数据规则。某条查询暂时不用它,不表示唯一性或标识约束已经没有作用。主键和唯一键教程解释了这部分职责,应与纯读取优化分开评估。

在保持结果含义的前提下比较成本

使用真实分布和有代表性的参数,比较计划、实际读取、延迟与写入开销,并记录环境与方法。第二次执行缓存更热,不能把全部差异都归因于新索引;一次小样本查询也不能预测所有客户的数据规模。

有意加入相同时间的订单时,应由明确的 order_id 次级键保持顺序。只改变客户条件时,输入人群不同也应有不同结果;不要为了得到相同输出而放宽 WHERE。结果正确性与访问成本需要各自验证。

这些查询和索引创建不应改变源记录。清理前检查四表定义与数据,保存需要的内容,再仅删除确认可丢弃的本节表。下面按子表到父表顺序执行,DROP TABLE 会删除结构和全部行,普通 ROLLBACK 无法恢复,应与事务实验分开。

DROP TABLE shopcourse.c39_items;
DROP TABLE shopcourse.c39_orders;
DROP TABLE shopcourse.c39_products;
DROP TABLE shopcourse.c39_customers;