一、索引概述

1.1、索引的概念

索引是存储引擎用于快速找到记录的一种数据结构。

假设一本书就是一张表，那么目录就是对应就是表的索引。

1.2、索引的优点

• 加快查询效率：通过有序的数据结构（B+树）快速定位数据
• 优化排序和分组操作：如果ORDER BY或者GROUP BY字段建立了索引，MySQL可以利用索引已有的有序特性，避免昂贵的临时表或文件排序（FileSort）
• 保证数据的完整性：主键索引和唯一索引能保证指定列的数据唯一性，防止重复数据的插入

1.3、索引的使用

• 快速过滤数据：快速查找匹配的WHERE字句的数据
• 多索引选择：如果查询存在多个可用的索引，MySQL一般使用匹配最少行的索引
• 最左匹配原则：对于组合索引（col1、col2、col3），MySQL优化器可以利用最左匹配原则进行查询， 如col1、col1 + col2
• 表连接：在执行连接查询时从其他表检索行（To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size）
• 优化MIN/MAX聚合函数：在查询索引的最小值和最大值，会被优化器优化成成常量查询
• 优化排序和分组操作：如果ORDER BY或者GROUP BY字段建立了索引，MySQL可以利用索引已有的有序特性，避免昂贵的临时表或文件排序（FileSort）
• 覆盖索引：如果一个查询只需要访问索引中包含的列（普通的索引包含主键），MySQL可以直接返回数据，无需回表。

二、索引类型

2.1、主键索引(Primary Key Index)

每张表只能有一个主键索引（可以包含多个列），主键索引 = 唯一索引 + 非空。

特点：

• 唯一性：保证主键列中的每一行都是唯一的
• 非空：主键不为包含null值
• 聚簇索引：在InnoDB引擎中，表数据本身都是按照主键索引来生成的

ALTER TABLE table_name ADD PRIMARY KEY (id);
-- 主键可以包含多个列
ALTER TABLE table_name ADD PRIMARY KEY (id, name);

2.2、唯一索引(Unique Index)

索引列的所有值都只能出现一次，可以为空，一张表可以有多个唯一索引

特点：

• 唯一性：与主键类型，保证列中所有的值都是唯一（除了NULL）
• 允许多个唯一索引：一张表可以有多个唯一索引
• 允许NULL值：可以包含多个NULL值

-- 唯一索引的名称一般都是：uk_ + 字段名称
ALTER TABLE table_name ADD UNIQUE KEY uk_index_name (column_name);
CREATE UNIQUE INDEX uk_index_name ON table_name (column_name);

2.3、普通索引 (Normal Index)

值可以为空，没有唯一性的限制

特点：

• 无约束：不强制数据的唯一性，仅用于提高查询效率

-- 普通索引的名称一般都是：idx_ + 列名称 
ALTER TABLE table_name ADD INDEX idx_name (column_name);
CREATE INDEX idx_name ON table_name (column_name);

2.4、全文索引(Full-text Index)

专门用于全文搜索的索引，仅适用与CHAR、VARCHAR和TEXT列，不提议使用（提议使用专门的搜索引擎如：Elasticsearch或者Solr）

2.5、组合索引(Composite Index)

多个列组成的一个索引（最左匹配原则），也叫复合索引或者多列索引。一个索引最多可包含16列。

特点：

• 唯一性：唯一索引也可以是包含多个列
• 最左匹配原则：对于组合索引（col1、col2、col3），MySQL优化器可以利用最左匹配原则进行查询， 如col1、col1 + col2
• 覆盖索引：如果一个查询只需要访问索引中包含的列（普通的索引包含主键），MySQL可以直接返回数据，无需回表。

-- 普通的组合索引
ALTER TABLE table_name ADD INDEX idx_name (col1, col2, col3);
-- 唯一的组合索引
ALTER TABLE table_name ADD UNIQUE INDEX uk_name (col1, col2, col3);

不同索引对比：

索引类型	唯一性	是否允许为空	数量限制	主要用途
主键索引	唯一	不允许	1个	唯一标识，是聚簇索引的组织依据
唯一索引	唯一	不允许	多个	确保数据的唯一性，加快查询速度
普通索引	不限制	允许	多个	提高查询速度
全文索引	不限制	允许	多个	对文本内容的关键字搜索
组合索引	取决于定义（定义多列唯一索引）	取决于定义（多列索引中的列都有非空约束）	多个	优化多列查询，实现覆盖索引

三、案例

案例1：一张表包含一个主键，一个组合索引，一个普通字段。

创建表和索引：

-- 创建test表，并创建组合索引
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(30) NOT NULL,
  `first_name` varchar(30) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_last_name_first_name` (`last_name`,`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 插入测试数据
insert into test(last_name,first_name,age) values('san','zhang',18),('si','li',19),('wu','wang',20),
('san','zhang',18),('si','li',19),('wu','wang',20),
('san','zhang',18),('si','li',19),('wu','wang',20),
('san','zhang',18),('si','li',19),('wu','wang',20),
('san','zhang',18),('si','li',19),('wu','wang',20),
('san','zhang',18),('si','li',19),('wu','wang',20),
('san','zhang',18),('si','li',19),('wu','wang',20),
('san','zhang',18),('si','li',19),('wu','wang',20),
('san','zhang',18),('si','li',19),('wu','wang',20),
('san','zhang',18),('si','li',19),('wu','wang',20);

索引生效的场景：

mysql> EXPLAIN SELECT * FROM test WHERE last_name='fengtong' AND first_name='wang';
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_last_name_first_name | idx_last_name_first_name | 184     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+

mysql> EXPLAIN SELECT * FROM test WHERE last_name='fengtong';
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_last_name_first_name | idx_last_name_first_name | 92      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+

mysql> EXPLAIN SELECT * FROM test WHERE last_name='fengtong' AND (first_name='wang' OR first_name='w');
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | idx_last_name_first_name | idx_last_name_first_name | 184     | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+

mysql> EXPLAIN SELECT * FROM test WHERE last_name='fengtong' AND first_name >='w' AND first_name < 'x';
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | idx_last_name_first_name | idx_last_name_first_name | 184     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+

索引不生效的场景：

mysql> EXPLAIN SELECT * FROM test WHERE first_name='wang';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   30 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

mysql> EXPLAIN SELECT * FROM test WHERE last_name='fengtong' OR first_name='wang';
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys            | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | idx_last_name_first_name | NULL | NULL    | NULL |   30 |    19.00 | Using where |
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+-------------+

通过以上执行计划的结果可得到结论组合索引支持最左匹配原则。

注意：要确保test表中存在必定的数据，如果没有数据MySQL优化器可能会选择全表扫描，从而导致得不到我们想要的结果。

案例2：一张表包含一个主键，其他字段都是组合索引的一部分。

创建表和索引：

CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(30) NOT NULL,
  `first_name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_last_name_first_name` (`last_name`,`first_name`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

测试索引使用情况：

mysql> EXPLAIN SELECT * FROM test2 WHERE last_name='fengtong' AND first_name='wang';
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | ref  | idx_last_name_first_name | idx_last_name_first_name | 184     | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------------+

mysql> EXPLAIN SELECT * FROM test2 WHERE last_name='fengtong' AND first_name='wang';
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | ref  | idx_last_name_first_name | idx_last_name_first_name | 184     | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------------+

mysql> EXPLAIN SELECT * FROM test2 WHERE last_name='fengtong' AND (first_name='wang' OR first_name='w');
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test2 | NULL       | range | idx_last_name_first_name | idx_last_name_first_name | 184     | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+--------------------------+

mysql> EXPLAIN SELECT * FROM test2 WHERE last_name='fengtong' AND first_name >='w' AND first_name < 'x';
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test2 | NULL       | range | idx_last_name_first_name | idx_last_name_first_name | 184     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+--------------------------+

mysql> EXPLAIN SELECT * FROM test2 WHERE first_name='wang';
+----+-------------+-------+------------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key                      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test2 | NULL       | index | NULL          | idx_last_name_first_name | 184     | NULL |   30 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+

mysql> EXPLAIN SELECT * FROM test2 WHERE last_name='fengtong' OR first_name='wang';
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test2 | NULL       | index | idx_last_name_first_name | idx_last_name_first_name | 184     | NULL |   30 |    19.00 | Using where; Using index |
+----+-------------+-------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+--------------------------+

通过案例2的测试，所有的测试场景索引都生效了。

跟案例1测试结果的对比，主要差异存在两点：

• 在案例1中索引不生效的情况下，在案例2中索引生效了
• 在案例2的执行计划中，Extra字段都存在Using index关键字，这就是大名鼎鼎的“索引覆盖”。

案例3：表有name和age字段，查询有以下3种情况，索引该如何创建？

• 只根据name查询数据
• 只根据age查询数据
• 根据name和age一起查询数据

组合索引name_age + age 和 age_name + name哪个比较合适？

方案1：创建组合索引idx_name_age + 普通索引idx_age

-- 创建表
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据
insert into t_user (name,age,email) VALUES('zhangsan',20,'zhangshan@163.com'),
('lisi',21,'lisi@163.com'),('wangwu',22,'wangwu@163.com');

查看执行计划

mysql> EXPLAIN select * from t_user where name = 'zhangsan';
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_name_age  | idx_name_age | 1023    | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

mysql> EXPLAIN select * from t_user where age = 20;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_age       | idx_age | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+

mysql> EXPLAIN select * from t_user where name = 'zhangsan' and age = 20;
+----+-------------+--------+------------+------+----------------------+--------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys        | key          | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------------+--------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_name_age,idx_age | idx_name_age | 1028    | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+----------------------+--------------+---------+-------------+------+----------+-------+

方案2：创建组合索引idx_age_name + 普通索引idx_name

-- 创建表
CREATE TABLE `t_user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据
insert into t_user2 (name,age,email) VALUES('zhangsan',20,'zhangshan@163.com'),
('lisi',21,'lisi@163.com'),('wangwu',22,'wangwu@163.com');

查看执行计划

mysql> EXPLAIN select * from t_user2 where name = 'zhangsan';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user2 | NULL       | ref  | idx_name      | idx_name | 1023    | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+

mysql> EXPLAIN select * from t_user2 where age = 20;
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user2 | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

mysql> EXPLAIN select * from t_user2 where name = 'zhangsan' and age = 20;
+----+-------------+---------+------------+------+-----------------------+--------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key          | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+--------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_user2 | NULL       | ref  | idx_age_name,idx_name | idx_age_name | 1028    | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+--------------+---------+-------------+------+----------+-------+

从两种方案的测试结果来看，所有的场景索引都生效了，并且执行计划中的索引长度也都一样。

但是我们再从索引的存储成本和读取成本来思考一下两种方案的差异：

• 存储成本：表t_user2的索引大小明显比t_user的要大

mysql> SELECT TABLE_NAME, TABLE_ROWS,DATA_LENGTH, INDEX_LENGTH,
    ->     (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS Total_MB
    -> FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA = 'wft' AND TABLE_NAME IN ('t_user', 't_user2');
+------------+------------+-------------+--------------+------------+
| TABLE_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | Total_MB   |
+------------+------------+-------------+--------------+------------+
| t_user     |      10668 |     1589248 |       524288 | 2.01562500 |
| t_user2    |      10668 |     1589248 |       573440 | 2.06250000 |
+------------+------------+-------------+--------------+------------+

• 读取成本：因整型字段在索引中占用空间小，一样数据页可存储更多索引项，降低I/O成本。

最佳实践总结

索引设计原则

1. 选择性原则：为高选择性的列创建索引
2. 最左前缀原则：合理设计组合索引的列顺序
3. 覆盖索引原则：尽量让查询只需访问索引
4. 精简索引原则：避免创建过多冗余索引

性能优化提议

1. 定期分析查询性能：使用EXPLAIN分析执行计划
2. 监控索引使用情况：定期检查未使用的索引
3. 思考数据分布：根据实际数据特点设计索引
4. 测试不同方案：在真实数据量下测试索引效果

四、附录

参考文献：
https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html