mysql里索引的创建原则

在 MySQL 中创建索引的原则主要是 优化查询性能，避免 索引失效，减少 不必要的索引开销。以下是 索引创建的最佳实践：

1. 选择合适的列创建索引

适合创建索引的列：

• 作为查询条件的列 (WHERE)
• 作为排序的列 (ORDER BY)
• 作为分组的列 (GROUP BY)
• 作为关联键的列 (JOIN)

示例

CREATE INDEX idx_trade_date ON trades(trade_date);

适用场景：

• SELECT * FROM trades WHERE trade_date = '2024-03-01'
• SELECT * FROM trades ORDER BY trade_date DESC

2. 使用合适的索引类型

• B+树索引（默认，适用于大多数查询）
• 哈希索引（只适用于等值查询，如 id = 123）
• 全文索引（适用于文本搜索，如 MATCH(content) AGAINST ('关键字')）
• 空间索引（适用于 GIS 地理信息查询）

示例

CREATE FULLTEXT INDEX idx_content ON articles(content);

3. 选择合适的索引列顺序

（1）联合索引：遵循“最左前缀”原则

索引 (A, B, C)，可以用于查询 A，A, B，A, B, C，但不能直接用于 B 或 C

CREATE INDEX idx_trade ON trades(user_id, trade_date, amount);

查询： ✅ SELECT * FROM trades WHERE user_id = 1001;
✅ SELECT * FROM trades WHERE user_id = 1001 AND trade_date > '2024-01-01';
❌ SELECT * FROM trades WHERE trade_date > '2024-01-01';（索引失效）

4. 避免索引失效

（1）数据类型必须匹配

如果 user_id 是 VARCHAR 类型，但查询用的是 INT，索引会失效：

SELECT * FROM trades WHERE user_id = 1001;  -- 错误
SELECT * FROM trades WHERE user_id = '1001'; -- 正确

（2）避免在索引列上使用函数

索引列如果使用 DATE_FORMAT、LOWER、UPPER，索引失效：

SELECT * FROM trades WHERE DATE_FORMAT(trade_date, '%Y-%m-%d') = '2024-03-01'; -- 索引失效

✅ 改进：

SELECT * FROM trades WHERE trade_date >= '2024-03-01' AND trade_date < '2024-03-02';

（3）避免前导通配符

LIKE '%证券' 无法利用索引，必须是 LIKE '证券%'：

SELECT * FROM stocks WHERE name LIKE '腾讯%';  -- 可以使用索引
SELECT * FROM stocks WHERE name LIKE '%腾讯';  -- 索引失效

5. 控制索引数量，避免过多索引

• 每个表的索引不宜过多，一般3~5个。
• 删除冗余索引，如 idx_trade_date(trade_date) 和 idx_trade(user_id, trade_date, amount)，idx_trade_date 可能是多余的。

6. 使用覆盖索引

如果查询的列都在索引中，MySQL 只访问索引，不访问数据页，提高查询速度。

CREATE INDEX idx_user_trade ON trades(user_id, trade_date, amount);

SELECT user_id, trade_date, amount FROM trades WHERE user_id = 1001;

适用场景：只查询索引包含的字段，不涉及 SELECT *。

7. 对高并发表考虑索引分区

• 分区索引：对于大表（如10年证券交易流水），可以按日期分区：

ALTER TABLE trades PARTITION BY RANGE (YEAR(trade_date)) (
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    ...
);

总结

你们的系统查询证券交易流水时，是否遇到索引失效或查询性能下降的问题？如果有的话，可以一起优化看看。