数据库索引：原理、设计与优化

避免在索引列上使用函数或表达式。
- 错误示例：SELECT * FROM users WHERE YEAR(create_time) = 2023;
- 正确示例：SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
避免使用 OR 连接多个条件。

4. 使用 EXPLAIN 分析查询

使用 EXPLAIN 命令分析查询执行计划，检查是否使用了索引。
示例：

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

索引的优缺点

优点

显著提升查询性能。
加速排序和分组操作。
保证数据的唯一性。

缺点

占用额外的存储空间。
影响写操作的性能（插入、更新、删除）。
需要定期维护。

实际案例

假设我们有一个电商平台的订单表 orders，包含以下字段：

order_id：订单ID（主键）
user_id：用户ID
product_id：商品ID
order_date：订单日期
amount：订单金额

表结构如下：

CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
product_id INT,
order_date DATE,
amount DECIMAL(10, 2)
);

数据量

表中包含 100 万条订单数据。

查询需求

根据 user_id 查询某个用户的所有订单。
根据 order_date 查询某一天的订单。
根据 user_id 和 order_date 查询某个用户在某一天的订单。
根据 amount 查询金额大于某个值的订单。

索引设计与实现

1. 创建表并插入数据

首先创建 orders 表，并插入 100 万条测试数据。

-- 创建表
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
product_id INT,
order_date DATE,
amount DECIMAL(10, 2)
);

-- 插入测试数据（示例）
DELIMITER //
CREATE PROCEDURE InsertOrders()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 1000000 DO
INSERT INTO orders (user_id, product_id, order_date, amount)
VALUES (
FLOOR(RAND() * 1000), -- user_id: 1-1000
FLOOR(RAND() * 100), -- product_id: 1-100
DATE('2023-01-01') + INTERVAL FLOOR(RAND() * 365) DAY, -- order_date: 2023年内随机日期
ROUND(RAND() * 1000, 2) -- amount: 0-1000随机金额
);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;

-- 调用存储过程插入数据
CALL InsertOrders();

2. 创建索引

2.1 单列索引

为 user_id 和 order_date 创建单列索引：

CREATE INDEX idx_user_id ON orders (user_id);
CREATE INDEX idx_order_date ON orders (order_date);

2.2 复合索引

为 user_id 和 order_date 创建复合索引：

CREATE INDEX idx_user_id_order_date ON orders (user_id, order_date);

2.3 金额索引

为 amount 创建索引：

CREATE INDEX idx_amount ON orders (amount);

查询性能测试

1. 根据 `user_id` 查询

-- 查询 user_id = 100 的所有订单
SELECT * FROM orders WHERE user_id = 100;

性能分析：

无索引时：需要全表扫描，查询时间较长。
有索引时：通过 idx_user_id 索引快速定位数据，查询时间显著减少。

具体流程对比

不加索引的流程

从磁盘读取整个 orders 表的数据。
逐行检查 user_id 是否等于 100。
返回满足条件的行。

加索引的流程

从磁盘读取索引文件（如 idx_user_id）。
在索引中查找 user_id = 100 的键值对。
获取对应的行指针（如 order_id）。
根据行指针从磁盘读取对应的行数据。
返回满足条件的行。

2. 根据 `order_date` 查询

-- 查询 order_date = '2023-05-01' 的所有订单
SELECT * FROM orders WHERE order_date = '2023-05-01';

性能分析：

无索引时：需要全表扫描，查询时间较长。
有索引时：通过 idx_order_date 索引快速定位数据，查询时间显著减少。

3. 根据 `user_id` 和 `order_date` 查询

-- 查询 user_id = 100 且 order_date = '2023-05-01' 的订单
SELECT * FROM orders WHERE user_id = 100 AND order_date = '2023-05-01';

性能分析：

无索引时：需要全表扫描，查询时间较长。
有复合索引时：通过 idx_user_id_order_date 索引快速定位数据，查询时间显著减少。

4. 根据 `amount` 查询

-- 查询 amount > 500 的所有订单
SELECT * FROM orders WHERE amount > 500;

性能分析：

无索引时：需要全表扫描，查询时间较长。
有索引时：通过 idx_amount 索引快速定位数据，查询时间显著减少。

使用 EXPLAIN 分析查询

通过 EXPLAIN 命令可以查看查询的执行计划，判断是否使用了索引。

示例

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND order_date = '2023-05-01';

输出结果：

key：显示使用的索引（如 idx_user_id_order_date）。
rows：显示扫描的行数。
type：显示查询类型（如 ref 表示使用了索引）。

索引的维护

1. 删除不必要的索引

DROP INDEX idx_user_id ON orders;

2. 重建索引

如果索引碎片化严重，可以重建索引：

ALTER TABLE orders DROP INDEX idx_user_id;
CREATE INDEX idx_user_id ON orders (user_id);

通过本案例，我们演示了如何在 MySQL 中创建和使用索引，并分析了索引对查询性能的影响。以下是关键点：

索引可以显著提升查询性能，尤其是在数据量较大的情况下。
合理设计索引，避免过度索引。
使用 EXPLAIN 分析查询，确保索引被正确使用。
定期维护索引，删除不必要的索引并重建碎片化的索引。

、

总结

索引：是一个排序的列表，包含索引字段的值和其相对应的行数据所在的物理地址

作用：加快表的查询速度，还可以对字段排序

如何实现的？
没有索引的情况下，要查询某行数据，需要先扫描全表来定位某行数据
有索引后会通过查找条件的字段值找到其索引对应的行数据的物理地址，然后根据物理地址访问相应的数据

副作用：会额外占用磁盘空间，更新包含索引的表效率会更慢

创建索引的依据：
1）表中的记录行数较多时，一般超过300行的表建议要有索引
2）建议在表中的主键字段、外键字段、多表连接使用的字段、唯一性较好的字段，不经常更新的字段、经常出现在where、group by、order by 子语句的字段、小字段创建索引
3）不建议在唯一性较差的字段、更新太频繁的字段、大文本字段上创建索引

索引类型：
普通索引 create index 索引名 on 表名 (字段); alter table 表名 add index 索引名 (字段);
唯一索引 create unique index 索引名 on 表名 (字段); alter table 表名 add unique 索引名 (字段);
主键索引 alter table 表名 add primary key (字段);
组合索引 create index 索引名 on 表名 (字段1, 字段2, 字段3); lter table 表名 add index 索引名 (字段1, 字段2, 字段3);
select ... from 表名 where 字段1=XX and 字段2=xx and 字段3=XX 用 and 做条件逻辑运算符时，要创建组合索引且要满足最左原则
用 or 做条件逻辑运算符时，所有字段都要创建索引
全文索引 create fulltext index 索引名 on 表名 (字段); alter table 表名 add fulltext 索引名 (字段);
模糊查询： select ... from 表名 where match(字段) against('查询内容');

查看索引
show create table 表名;
show index from 表名;
show keys from 表名;

删除索引
drop index 索引名 on 表名;
alter table 表名 drop 索引名;
alter table 表名 drop primary key;

explain select ... 可以用于分析select查询语句，看key字段，确定是否使用了索引或索引使用是否正确