MySQL索引知识总结

发表于   |   分类于   |     |   阅读次数

对MySQL索引知识的总结笔记。

普通索引

索引是一种数据结构，主要用于性能的提高。

比如我们有一个表t_users，有4个字段：

1
2
3
4
5
6
7
create table t_users (
	id bigint(20) not null auto_increment,
	name varchar(255) not null,
	age bigint(20) not null,
	num bigint(20) not null,
	primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

往这个表中插入100w条数据，name字段是format-1到100w，age字段也是1-100w，num字段是个随机的10以内的数字。然后执行sql语句进行查询：

1
2
3
4
5
6
7
mysql> select * from t_users where name = 'format-500000';
+--------+---------------+--------+-----+
| id     | name          | age    | num |
+--------+---------------+--------+-----+
| 500000 | format-500000 | 500000 |  38 |
+--------+---------------+--------+-----+
1 row in set (0.47 sec)

explain一下这条语句：

1
2
3
4
5
6
mysql> explain select * from t_users where name = 'format-500000';
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t_users | ALL  | NULL          | NULL | NULL    | NULL | 996677 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

发现查询了996677条数据，进行了全表的扫描。

我们给name字段加上索引：

1
mysql> create index IDX_FOR_NAME on t_users(name);

再次执行sql语句：

1
2
3
4
5
6
7
mysql> select * from t_users where name = 'format-500000';
+--------+---------------+--------+-----+
| id     | name          | age    | num |
+--------+---------------+--------+-----+
| 500000 | format-500000 | 500000 |  38 |
+--------+---------------+--------+-----+
1 row in set (0.00 sec)

explain一下：

1
2
3
4
5
6
7
mysql> explain select * from t_users where name = 'format-500000';
+----+-------------+---------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+---------------+--------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_users | ref  | IDX_FOR_NAME  | IDX_FOR_NAME | 767     | const |    1 | Using index condition |
+----+-------------+---------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

只查询了1条数据，因为我们加了btree索引，可以快速定位到具体的值。

接下来我们查询num字段：

1
2
3
4
5
6
7
8
9
10
mysql> select * from t_users where num = 66;
+--------+---------------+--------+-----+
....
| 965109 | format-965109 | 965109 |  66 |
| 965172 | format-965172 | 965172 |  66 |
| 965182 | format-965182 | 965182 |  66 |
| 965213 | format-965213 | 965213 |  66 |
....
+--------+---------------+--------+-----+
10029 rows in set (0.30 sec)

由于num字段也是没有加索引的，查询的时候也进行全表的扫描，查询耗时。接下来我们给num字段加上索引。

1
mysql> create index IDX_FOR_NUM on t_users(num);

然后进行查询：

1
2
3
4
5
6
7
8
9
10
mysql> select * from t_users where num = 5;
+--------+---------------+--------+-----+
....
| 965109 | format-965109 | 965109 |  5 |
| 965172 | format-965172 | 965172 |  5 |
| 965182 | format-965182 | 965182 |  5 |
| 965213 | format-965213 | 965213 |  5 |
....
+--------+---------------+--------+-----+
10029 rows in set (0.04 sec)

explain一下：

1
2
3
4
5
6
mysql> explain select * from t_users where num = 5;
+----+-------------+---------+------+---------------+-------------+---------+-------+-------+-------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref   | rows  | Extra |
+----+-------------+---------+------+---------------+-------------+---------+-------+-------+-------+
|  1 | SIMPLE      | t_users | ref  | IDX_FOR_NUM   | IDX_FOR_NUM | 8       | const | 206712 | NULL  |
+----+-------------+---------+------+---------------+-------------+---------+-------+-------+-------+

虽然我们在num字段上加了索引，但是由于num值在这100w条数据中有很多重复的数据，这个时候索引对查询速度的提高就没有那么明显了。

因为不论是hash类型的索引还是btree类型的索引，他们对于重复的数据的查询并没有提高多少。相反，由于添加了索引，导致数据写入性能变差，而查询性能又没有增强多少。所以说不能盲目地添加索引。

复合索引

复合索引也叫联合索引，表示索引建立在多个列上。

我们删除t_users上的索引，然后创建一个复合索引。

1
2
3
4
mysql> drop index IDX_FOR_NUM on t_users;
mysql> drop index IDX_FOR_NAME on t_users;

mysql> create index INDEX_FOR_NAME_AGE on t_users(name, age);

复合索引支持最左原则，也就是说INDEX_FOR_NAME_AGE索引支持name字段的查找，支持name，age字段的查找，但是不支持age，name字段的查找以及age字段的查找。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> explain select * from t_users where name = 'format-100000';
+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys      | key                | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_users | ref  | INDEX_FOR_NAME_AGE | INDEX_FOR_NAME_AGE | 767     | const |    1 | Using index condition |
+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+

mysql> explain select * from t_users where name = 'format-100000' and age = 100000;
+----+-------------+---------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
| id | select_type | table   | type | possible_keys      | key                | key_len | ref         | rows | Extra                 |
+----+-------------+---------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | t_users | ref  | INDEX_FOR_NAME_AGE | INDEX_FOR_NAME_AGE | 775     | const,const |    1 | Using index condition |
+----+-------------+---------+------+--------------------+--------------------+---------+-------------+------+-----------------------+

mysql> explain select * from t_users where age = 100000;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t_users | ALL  | NULL          | NULL | NULL    | NULL | 996677 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

使用age，name字段查找：

1
2
3
4
5
6
mysql> explain select * from t_users where age = 100000 and name = 'format-100000';
+----+-------------+---------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
| id | select_type | table   | type | possible_keys      | key                | key_len | ref         | rows | Extra                 |
+----+-------------+---------+------+--------------------+--------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | t_users | ref  | INDEX_FOR_NAME_AGE | INDEX_FOR_NAME_AGE | 775     | const,const |    1 | Using index condition |
+----+-------------+---------+------+--------------------+--------------------+---------+-------------+------+-----------------------+

我们发现使用age，name字段的查找使用了复合索引，这是因为MySQL内部有个查询优化器帮我们进行了优化。

索引的生效

索引创建之后，查询的过程并不一定会使用索引。整理如下(t_users表中name和num字段都有单独的索引)：

1.当使用索引查询比全表扫描更慢。比如下面这句sql中num字段的值分布在1-10之间

1
2
3
4
5
6
mysql> explain select * from t_users where num > 1 and num < 8;
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t_users  | ALL  | IDX_ON_NUM    | NULL | NULL    | NULL | 996504 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+

2.使用or进行查询，并且or左右两边的列有不存在索引的列

1
2
3
4
5
6
mysql> explain select * from t_users where name = 'format-4000' or age = 50;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t_users | ALL  | IDX_FOR_NAME  | NULL | NULL    | NULL | 996677 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

or两边的列都有索引：

1
2
3
4
5
6
mysql> explain select * from t_users where name = 'format-4000' or num = 50;
+----+-------------+----------+-------------+---------------------------------+---------------------------------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table    | type        | possible_keys                   | key                             | key_len | ref  | rows | Extra                                                     |
+----+-------------+----------+-------------+---------------------------------+---------------------------------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | t_users  | index_merge | IDX_ON_NUM,IDX_FOR_NAME         | IDX_FOR_NAME,IDX_ON_NUM         | 767,8   | NULL |    2 | Using union(IDX_FOR_NAME,IDX_ON_NUM); Using where         |
+----+-------------+----------+-------------+---------------------------------+---------------------------------+---------+------+------+-----------------------------------------------------------+

3.使用like，并以 % 开头

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from t_users where name like "%format-200";
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t_users | ALL  | NULL          | NULL | NULL    | NULL | 996677 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

mysql> explain select * from t_users where name like "%format-200%";
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t_users | ALL  | NULL          | NULL | NULL    | NULL | 996677 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

以 % 结尾的查询会使用索引：

1
2
3
4
5
6
mysql> explain select * from t_users where name like "format-200%";
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table   | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t_users | range | IDX_FOR_NAME  | IDX_FOR_NAME | 767     | NULL | 1110 | Using index condition |
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------+

4.复合索引

不是复合索引的最左字段(t_users表有(name,age)复合索引)。

1
2
3
4
5
6
mysql> explain select * from t_users where age = 100000;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t_users | ALL  | NULL          | NULL | NULL    | NULL | 996677 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

5.对varchar类型的字段进行查询的时候，没有加上引号

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from t_users where name = 111;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t_users | ALL  | IDX_FOR_NAME  | NULL | NULL    | NULL | 996677 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

mysql> explain select * from t_users where name = "111";
+----+-------------+---------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+---------------+--------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_users | ref  | IDX_FOR_NAME  | IDX_FOR_NAME | 767     | const |    1 | Using index condition |
+----+-------------+---------+------+---------------+--------------+---------+-------+------+-----------------------+

hash索引和btree索引的区别

  1. hash索引不能使用范围查询，只能使用一些比如 “=”, “<>”, “in”查询。因为hash索引会计算索引列的hash值，计算出后的hash值经过了hash算法与原先的值完全不一样，只能进行等值的过滤，不能基于范围的过滤
  2. hash索引遇到大量hash值相同的情况下，性能比btree要差
  3. hash索引并不一定一次可以定位到数据。因为基于索引列计算出的hash值会有重复，重复的话需要扫描hash表进行比较
  4. 由于hash索引中存放的是经过hash计算之后的hash值，而且hash值的大小关系并不一定和hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算
  5. 对于组合索引，hash索引在计算hash值的时候是组合索引键合并后再一起计算hash值，而不是单独计算hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，hash索引也无法被利用
  6. InnoDB和MyISAM引擎不支持hash索引
如果觉得我的文章对您有用，请随意打赏。您的支持将鼓励我继续创作！
本文作者：Format
原文链接： http://fangjian0423.github.io/2017/07/05/mysql-index-summary/
版权归作者所有，转载请注明出处