一文弄懂MySQL索引创建原则

一、MySQL索引简介

MySQL的索引是查询优化的关键，索引可以大大加快数据的检索速度。索引可以看作是目录，它们可以在查询中快速地定位到满足条件的数据。MySQL支持以下类型的索引：

• B-tree索引：B-tree是平衡树，并且是一种多路搜索树，这个树的每个节点最多包含k个孩子。
• B+tree索引：B+tree是B-tree树的一种变形。相对于B-tree它有更高的磁盘和内存访问效率，且能够保持稳定的性能。
• 哈希索引: 基于哈希表实现的，只有精确匹配索引所有列时才有效。对于任何需要进行范围查找的情况，哈希索引都无法正常使用。

本文以B-tree索引为例讲解MySQL索引的创建原则。

二、索引创建原则

1. 最左前缀原则

在MySQL中，如果一个查询中出现了索引的前缀顺序，则查询可以通过索引完成，否则无法使用。所以使用索引时，应该将访问最高、最具限制性的条件定义在索引的前面。

例如：

SELECT * FROM emp WHERE job='Manager' AND deptno=20;

建立（job，deptno）索引，能够满足该查询条件。因为表中只有job有过过多数据，所以通过job来过滤数据是最具效率的。

而下面这个建立（deptno，job）索引的是错误的，因为在 WHERE 子句中没有出现了索引的第一个字段。如果这么建索引，将无法使用（deptno，job）索引进行查询。

1. 索引列的选择性原则

选择性是指不重复的索引值（也称为基数）和数据表记录数（中的总行数）的比值。选择性越高的索引，查询效率越高。

例如：

SELECT * FROM emp WHERE job='Manager';

如果根据job字段建立索引，非常适合此查询场景，因为只有极少数记录的job等于'Manager'。

但这条规则也有例外，例如：当需要查询女性用户时，性别字段是比较合适的索引字段。然而，当查询新生儿的信息时，性别就不是可选择性良好的字段。

1. 单列索引与复合索引的选择

当一个查询涉及到多个查询条件时，一般建议建立复合索引，但并非所有的查询都适合建立复合索引，这需要根据场景进行判断。

例如：

SELECT * FROM emp WHERE name='Jack' AND age=20;

建立（name, age）索引是比较合适的。

而下面这个建立两个单列索引的写法也没问题：

ALTER TABLE emp ADD INDEX name_index(name);
ALTER TABLE emp ADD INDEX age_index(age);

总之，选择单列索引还是复合索引，需要根据具体的使用场景进行判断。

三、示例说明

1. 示例一

假设有一个学生表，需要查询学生名字为“张三”、性别为“女”、出生日期早于“2000-01-01”的所有数据，并且表中student_id字段为主键，因此必须建立索引优化查询性能。我们可以建立复合索引（name, sex, birth_date, student_id）来完成该查询。

CREATE INDEX name_sex_birth_date_index 
ON student (name, sex, birth_date, student_id);

1. 示例二

假设有一个订单表，需要查询订单状态码在1和4之间、订单金额在100到300之间、订单日期在2020年4月之后的所有数据。由于该查询没有单个字段能满足，因此需要建立复合索引（order_date, order_status，order_amount）来优化查询效率。

CREATE INDEX order_date_status_amount_index 
ON orders (order_date, order_status, order_amount);

以上是“一文弄懂MySQL索引创建原则”的完整攻略及两条示例说明，建立合理的索引可以大幅提升数据库的查询性能，但是过多或者不合理的索引也会导致性能下降，因此需要仔细衡量索引的选择。