MySQL为数据表建立索引的原则详解

索引简介

索引是一种能够快速访问存储在数据表中数据的数据结构，类似于书籍的目录，它能够缩短数据的查找时间及提高数据库的查询速度。MySQL支持多种索引类型，包括B-Tree索引、Hash索引、Full-Text索引等。

为数据表建立索引的原则

在为数据表建立索引时，应遵循以下原则：

1. 选择合适的索引类型

MySQL支持多种索引类型，不同的索引类型具有不同的优缺点，应根据需要选择合适的索引类型。如需快速查询，应该使用B-Tree索引；如需快速匹配对应键值，应该使用Hash索引；如需进行全文搜索，应该使用Full-Text索引。

2. 建立多列索引

建立联合索引可以提高多列条件查询的效率，可以减少MySQL执行器扫描数据表的次数。如：

CREATE INDEX idx_name_age ON users (name, age);

3. 不要超过5个索引

建立太多的索引将会影响MySQL的性能，因为每个索引占用磁盘空间，并且每个索引都需要被维护。一般来说，不要超过5个索引。

4. 使用前缀索引

若某一属性的数据非常长，建立前缀索引可以节省空间并提高查询性能。如：

CREATE INDEX idx_title ON news (title(20));

5. 使用覆盖索引

在查询语句中只使用了索引列，且被查询的列和索引完全匹配时，MySQL可以使用覆盖索引避免回表查询。如：

SELECT name, age FROM users WHERE name = 'Tom';

6. 不要在查询语句中使用函数

在where子句中使用函数会导致MySQL无法使用索引，因为MySQL在对数据进行索引时，只认识值而不认识函数。如：

SELECT name, age FROM users WHERE YEAR(birthday) = 2000;

7. 避免在索引列上进行大量的计算

如果对索引列进行了大量计算，将会大幅降低MySQL的查询效率，应当避免这种情况。如：

SELECT name, age FROM users WHERE age % 2 = 0;

示例

示例1：建立联合索引

假设有一个用户表users，包含name、age、gender等字段，现需要查询所有女性用户的姓名和年龄，并按年龄排序。则可以建立联合索引：

CREATE INDEX idx_gender_age ON users (gender, age);

查询时可以直接使用索引：

SELECT name, age FROM users WHERE gender = 'F' ORDER BY age;

示例2：使用前缀索引

假设有一张新闻表news，包含title、content等字段，其中title字段非常长。如果需要查询所有带有“MySQL”关键字的新闻标题，可以使用前缀索引：

CREATE INDEX idx_title ON news (title(20));

查询时可以直接使用索引：

SELECT title FROM news WHERE title LIKE '%MySQL%';