MySQL的索引你了解吗

当访问MySQL中的表时，如果没有索引，每次查询时都需要全表扫描，这将导致查询速度变慢。索引可以帮助MySQL更快地定位到数据，减少查询时间。

索引的基础知识

• 索引是什么？

在MySQL中，数据表的索引类似于图书馆的书目索引。索引会存储着字段值和与之关联的行指针，以便于找到数据库表中的数据。

• 索引的类型有哪些？

MySQL中支持多种不同类型的索引，包括B树索引、哈希索引和全文索引等。其中B树索引是最常见的类型，它可以应用于所有的MySQL存储引擎。

• 如何创建索引？

在创建表或者修改表结构时，可以使用CREATE INDEX命令来创建索引。例如：

CREATE INDEX index_name ON table_name (column_name);

这将在表中创建列column_name的B树索引，并将它命名为index_name。

使用索引的技巧

• 索引的选择

虽然索引可以加速查询，但是过多的索引会对数据库的性能产生负面影响。因此，在创建索引时需要谨慎选择哪些列需要添加索引。一些常用的原则包括：

- 对于WHERE或者JOIN条件中经常使用的列，应该添加索引
- 对于经常需要排序、分组或者联合的列，应该添加索引
- 对于区分度高的列，应该添加索引。例如，一个Boolean类型的列只有两个可能的值，添加索引可能并不高效，因为全表扫描的成本并不高

• 索引的优化

除了选择合适的列进行索引之外，还可以通过其他途径来优化索引的使用效率。一些常用的技巧包括：

- 使用复合索引。一个复合索引可以将多个列合并成为一个索引，这样可以减少对表的扫描次数。

```
CREATE INDEX index_name ON table_name (column1, column2);
```

- 避免使用LIKE和%操作符。因为这个操作不会利用索引，会导致全表扫描。

```
SELECT * FROM table_name WHERE column_name LIKE '%xxxx%';
```

- 索引列应该使用数据类型相同的值进行比较。如果是不同的数据类型，则需要进行类型转换，这样也会导致全表扫描。

```
SELECT * FROM table_name WHERE column_name = 1;
```

示例说明

假设我们有一张学生表students，其中包含id、name、age、gender和address列。我们想要查询所有年龄为20岁的男性学生，并且按照名字的字母顺序进行排序。

如果输入的SQL语句如下：

SELECT name, age, address 
FROM students 
WHERE age = 20 AND gender = 'M' 
ORDER BY name;

为了优化查询性能，我们可以为age和gender列创建复合索引，并为name列单独创建一个索引。创建索引的语句如下：

CREATE INDEX age_gender_index ON students (age, gender);
CREATE INDEX name_index ON students (name);

当查询语句中出现WHERE和ORDER BY语句时，MySQL会自动选择合适的索引进行查询。在这个例子中，MySQL可以使用age_gender_index索引进行WHERE条件的检索，并使用name_index索引进行ORDER BY操作。

使用索引可以大幅度提高查询性能，但是需要根据实际情况进行优化。如果添加过多的索引或者选择遗憾的列进行索引，反而会降低数据库的性能。