MySQL索引那些事儿

关注

原创最新推荐文章于 2025-11-06 18:45:00 发布 478 阅读 AI 写同款· GEO 优化 ›

mysql索引的那些事儿 mysql索引的那些事儿mysql索引如何定位并优化慢查询Sql如何定位并优化慢查询Sql mysql索引索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。索引的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UP 阅读详情

1.B+树

一个节点有多个元素
所有元素都在叶子节点冗余
叶子节点间有指针且有序

推荐一个B+树演示网站 https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

2.InnoDB数据引擎中的页

默认页大小为16kb

mysql> show global status like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

mysql> select 16384/1024;
+------------+
| 16384/1024 |
+------------+
|    16.0000 |
+------------+
1 row in set (0.01 sec)

3.主键索引

创建表，并插入数据

CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t1` 
VALUES 
('6', '6', '4', '4', 'f'), 
('1', '1', '1', '1', 'a'), 
('2', '2', '2', '2', 'b'), 
('7', '4', '5', '5', 'g'), 
('4', '3', '1', '1', 'd'), 
('5', '2', '3', '6', 'e'), 
('3', '3', '2', '2', 'c'), 
('8', '8', '8', '8', 'h');
COMMIT;

以主键数据创建的B+树，即主键索引

查询索引

mysql> show index from t1;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | a           | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

如果建表时，
- 有主键，建立主键索引
- 无主键
  - 先判断是否有唯一索引
    - 如果有唯一索引，创建唯一索引
    - 如果没有唯一索引，创建一个隐藏字段作为唯一索引，即rowid

了解了主键索引，就可以顺理成章的分析出，使用uuid作为主键的劣势了：

uuid占用空间大，在存储相同数据量的情况下，需要使用更多的页来存储，影响了B+树的高度，影响查询效率。
uuid无需，相较于自增主键，每次插入都要重新排序。

4.联合索引

mysql> create index idx_t1_bcd on t1(b,c,d);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t1;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY    |            1 | a           | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | idx_t1_bcd |            1 | b           | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
| t1    |          1 | idx_t1_bcd |            2 | c           | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| t1    |          1 | idx_t1_bcd |            3 | d           | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

按照bcd的值创建B+树，在叶子节点上，把这条数据对应的主键a的值也关联上，对应起来。

当select后字段不在abcd字段里时，需要根据查到的主键值二次查询，就是平时我们说的回表。

在这里就可以看到，为什么要有“最左前缀原则”了，因为如果查询是最左侧的不能确定，没办法从在B+树上确定位置，就没有办法走索引了。

5.几个具体实例

5.1 key_len值

mysql> explain select * from t1 where a = 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where b = 1 and c = 1 and d = 1;
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_t1_bcd    | idx_t1_bcd | 15      | const,const,const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where b = 1 and d = 1;
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_t1_bcd    | idx_t1_bcd | 5       | const |    2 |    14.29 | Using index condition |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

3条SQL语句

第1条走主键索引
- 其中key_len长度为4，int类型在MySQL中占4字节
第2条走bcd联合索引
- 其中key_len长度为15，int类型在MySQL中占4字节，再加上可能为null的情况（MySQL需要1个字节来标识NULL），而且此次查询使用到了bcd3个字段，所以(4+1)*3=15
第3条走bcd联合索引
- 其中Extra，Using index condition，即没有全部使用到bcd3个字段，只用到了b字段
- key_len长度为15，int类型在MySQL中占4字节，再加上可能为null的情况（MySQL需要1个字节来标识NULL），4+1=5

5.2 回表

mysql> explain select * from t1 where b > 1; 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_t1_bcd    | NULL | NULL    | NULL |    7 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where b > 7;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_t1_bcd    | idx_t1_bcd | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

两条SQL语句，差异只在b大于的常量值，但是第1条没走索引，第2条走索引了。

这里是索引优化器，在走索引或全表扫描二选一的结果。

如果走索引，回表次数过多时，性能可能还不如直接全表扫描。

5.3 覆盖索引

mysql> explain select * from t1 where b > 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_t1_bcd    | NULL | NULL    | NULL |    7 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select b,c,d from t1 where b > 1;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | idx_t1_bcd    | idx_t1_bcd | 15      | NULL |    7 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select b,c,d,a from t1 where b > 1;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | idx_t1_bcd    | idx_t1_bcd | 15      | NULL |    7 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

第1条SQL查询*所有字段，查询出来后需要回表，索引优化器对比后，发现全表扫描比先索引再回表性能更优，直接全表扫描。

第2条走bcd联合索引，比较好理解。

第3条，也走bcd联合索引，因为在bcd联合索引的B+树上，关联了主键索引，而且查询的是bcda字段，所以不需要回表，直接走索引就可以查询出来，也就是平时所说的“覆盖索引”。

覆盖索引（Covering Index）：包含所有满足查询需要数据的索引。

5.4 类型转换

在MySQL中，会默认把字符转换为数字，如果字符不是数字，则转换为0。

mysql> select 0='0';
+-------+
| 0='0' |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

mysql> select 0='1';
+-------+
| 0='1' |
+-------+
|     0 |
+-------+
1 row in set (0.01 sec)

mysql> select 0='a';
+-------+
| 0='a' |
+-------+
|     1 |
+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select 0='ab';
+--------+
| 0='ab' |
+--------+
|      1 |
+--------+
1 row in set, 1 warning (0.00 sec)

插入1条数据

INSERT INTO `t1` VALUES ('0', '9', '9', '9', 'i');

mysql> select * from t1 where a = 'abcd';
+---+------+------+------+------+
| a | b    | c    | d    | e    |
+---+------+------+------+------+
| 0 |    9 |    9 |    9 | i    |
+---+------+------+------+------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from t1 where a = 0;
+---+------+------+------+------+
| a | b    | c    | d    | e    |
+---+------+------+------+------+
| 0 |    9 |    9 |    9 | i    |
+---+------+------+------+------+
1 row in set (0.00 sec)

MySQL会自动把'abcd'转换为0，所以以上2条SQL等价，都把a=0的数据查询出来了。

5.5 索引及类型转换

给e字段增加索引

create index idx_t1_e on t1(e);

explain select * from t1 where a = 1; 	-- 走索引
explain select * from t1 where a = '1';	-- 走索引
explain select * from t1 where e = 1;	-- 不走索引
explain select * from t1 where e = '1';	-- 走索引

对于1、2SQL来讲，a字段为int类型
- 等号右侧为数字时，不需要类型转换，直接走索引
- 等号右侧为字符时，会自动转换为数字，类型一致，走索引
对于3、4SQL来讲，e字段为varchar类型
- 等号右侧为数字时，没办法从数字转换为字符，等号左侧为字符，右侧为数字，只能将e字段转换为数字，因为B+树中存储的是字符，类型不一致导致无法比对，只能全表扫描
- 等号右侧为字符时，不需要类型转换，直接走索引

mysql> explain select * from t1 where a = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where a = '1';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where e = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_t1_e      | NULL | NULL    | NULL |    9 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql> explain select * from t1 where e = '1';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_t1_e      | idx_t1_e | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

在where子句中，等号左侧，不要对字段进行运算或操作，否则无法走索引，导致全表扫描，性能下降。

收录于