不得不说的数据库索引-京东云开发者社区

### 1 索引概述

利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。索引的关键字一定是排序的。索引本质上是表字段的有序子集，它是提高查询速度最有效的方法。一个没有建立任何索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询效率极低、速度也极慢。如果建立索引，那么就好比一本添加的目录，通过目录的指引，迅速翻阅到指定的章节，提升的查询性能，节约了查询资源。

### 2 索引种类

从索引的定义方式和用途中来看：主键索引，唯一索引，普通索引，全文索引。

无论任何类型，都是通过建立关键字与位置的对应关系来实现的。索引是通过关键字找对应的记录的地址。以上类型的差异：对索引关键字的要求不同。

- 关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）。
- 普通索引,index：对关键字没有要求。
- 唯一索引,unique index：要求关键字不能重复。同时增加唯一约束。
- 主键索引,primary key：要求关键字不能重复，也不能为NULL。同时增加主键约束。
- 全文索引,fulltext key：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。
- 关键字含义：可以是某个字段，也可以是某些字段。如果一个索引通过在多个字段上提取的关键字，称之为复合索引。 命令:alter table exp add index (field1, field2);

PS：这里主键索引和唯一索引的区别在于：主键索引不能为空值，唯一索引允许空值；主键索引在一张表内只能创建一个，唯一索引可以创建多个。主键索引肯定是唯一索引，但唯一索引不一定是主键索引。

### 3 索引操作

#### 3.1 创建主键索引
创建一个无符号整型且自动增长的列，然后设置成主键即可。
//通过EXPLAIN语句查看索引状态

```sql
EXPLAIN SELECT * FROM think_user WHERE id=1;
```

#### 3.2 创建普通或唯一索引

直接进入navicat设计表的第二栏，选择一个字段(比如user字段)，添加一个Nomral(普通索引)或Unique(唯一索引)。
//通过EXPLAIN语句查看索引状态

```sql
EXPLAIN SELECT * FROM think_user WHERE user=&#39;蜡笔老新&#39;;
```
//查看表所有索引情况

```sql
SHOW INDEX FROM think_user;
```

#### 3.3 使用sql语句的方式建立索引----建表时就创建索引

![](//img1.jcloudcs.com/developer.jdcloud.com/7fee0eac-7072-4eee-819b-35c7891589bc20211202145008.png)

注意：索引可以起名字，但是主键索引不能起名字，因为一个表仅仅可以有一个主索引，其他索引可以出现多个。名字可以省略，mysql会默认生成，通常使用字段名来充当。
#### 3.4 使用sql语句的方式建立索引----更新表时创建索引

![](//img1.jcloudcs.com/developer.jdcloud.com/64106a0f-0a2e-4e13-853f-d23aef3b94cd20211202145022.png)

注意：如果表中存在数据，数据符合唯一或主键的约束才可能创建成功。auto_increment属性，依赖于一个KEY。
#### 3.5 使用sql语句的方式删除索引，auto_increment依赖于KEY

![](//img1.jcloudcs.com/developer.jdcloud.com/15336abf-3c02-46bd-9e79-464478f8ac7820211202145045.png)

#### 3.6 Explain 执行计划
可以通过在select语句前使用 explain，来获取该查询语句的执行计划，而不是真正执行该语句。

![](//img1.jcloudcs.com/developer.jdcloud.com/48e4b6d0-e7e3-4264-a995-860aebddcf5a20211202145059.png)

删除索引时，再看执行计划：

![](//img1.jcloudcs.com/developer.jdcloud.com/49d0167c-dc59-4662-af33-25d52ec74f1f20211202145115.png)

从查询的行数可知，有索引时查询会快的多，因为它只需要查找一行，而没有索引时，会造成全表扫描。
注意：select语句才能获取到执行计划。（新版本5.6会扩展其他语句的执行计划的获取）
### 4 索引原则
如果索引不遵循使用原则，则可能导致索引无效。
#### 4.1 列独立
如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧。

![](//img1.jcloudcs.com/developer.jdcloud.com/8a2eacc0-3d4b-488b-9b24-b070bf1c5bbd20211202145131.png)

第三个语句 empno-1就不是列独立：就不能用索引。类似函数等等。（write_time < unix_timestamp()-$gc_maxlifetime）

![](//img1.jcloudcs.com/developer.jdcloud.com/9561824e-8302-4f27-8db3-4161bc0c4ad020211202145145.png)

其他两个列独立可以使用：

![](//img1.jcloudcs.com/developer.jdcloud.com/fcf904db-d2bc-41f1-814b-affb5c0c77ca20211202145159.png)

#### 4.2 左原则
Like：匹配模式必须要左边确定不能以通配符开头。

![](//img1.jcloudcs.com/developer.jdcloud.com/f4da798a-7484-46c6-86b8-ee95389a860620211202145212.png)

假如业务逻辑上出现: field like &lsquo;%keywork%&rsquo;;类似查询，需要使用全文索引。
复合索引：一个索引关联多个字段，仅仅针对左边字段有效果。

![](//img1.jcloudcs.com/developer.jdcloud.com/e69b4114-348a-40ed-a909-21ee839d368120211202145228.png)

对Ename的查询，使用了索引，结果如下：

![](//img1.jcloudcs.com/developer.jdcloud.com/7b00f790-048c-4bd1-8867-59fa8f2c1b8c20211202145240.png)

Empno的查询没有使用索引，结果如下：

![](//img1.jcloudcs.com/developer.jdcloud.com/859856ec-f8ca-47e5-83b3-e94bb3690f9720211202145253.png)

### 5 索引应用
#### 5.1 无索引情况
表结构

```sql
CREATE TABLE `user` (
……
mo bigint NOT NULL DEFAULT '' ,
KEY ind_mo (mo) 
……
) ENGINE=InnoDB;SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1
```

执行计划

```sql
mysql> explain  SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1;
           id: 1
  select_type: SIMPLE
        table: user
         type: ALL
possible_keys: NULL
          key: NULL
         rows: 707250
         Extra: Using where
```

从上面的SQL看到执行计划中ALL，代表了这条SQL执行计划是全表扫描，每次执行需要扫描707250行数据，这是非常消耗性能的，该如何进行优化？添加索引。

**验证mo字段的过滤性**

```sql
mysql> select count(*) from user where mo=13772556391;
|   0    |
```

可以看到mo字段的过滤性是非常高的，进一步验证可以通过select count(*) as all_count,count(distinct mo) as distinct_cnt from user，通对比 all_count和distinct_cnt这两个值进行对比，如果all_cnt和distinct_cnt相差甚多，则在mo字段上添加索引是非常有效的。

#### 5.2添加索引

```sql
mysql> alter table user add index ind_mo(mo);
mysql>SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1;
Empty set (0.05 sec)
```

**执行计划**

```sql
mysql> explain  SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1\G;
*************************** 1. row ***************************               id: 1      select_type: SIMPLE            table: user             type: index    possible_keys: ind_mo              key: ind_mo             rows: 1            Extra: Using where; Using index
```

#### 5.3隐式转换示例一
**表结构**

```sql
  CREATE TABLE `user` (
  ……
  mo char(11) NOT NULL DEFAULT '' ,
  KEY ind_mo (mo)
  ……
  ) ENGINE=InnoDB;
```

**执行计划**

```sql
mysql> explain extended select uid from`user` where mo=13772556391 limit 0,1;
mysql> show warnings;
Warning1：Cannot use  index 'ind_mo' due to type or collation conversion on field 'mo'                                                                        
Note：select `user`.`uid` AS `uid` from `user` where (`user`.`mo` = 13772556391) limit 0,1
```

**如何解决**

```sql
mysql> explain   SELECT uid FROM `user` WHERE mo='13772556391' LIMIT 0,1\G;
*************************** 1. row ***************************              id: 1     select_type: SIMPLE           table: user            type: ref
   possible_keys: ind_mo
             key: ind_mo            rows: 1           Extra: Using where; Using index
```

上述案例中由于表结构定义mo字段后字符串数据类型，而应用传入的则是数字，进而导致了隐式转换，索引无法使用，所以有两种方案：
第一，将表结构mo修改为数字数据类型。
第二，修改应用将应用中传入的字符类型改为数据类型。
#### 5.4隐式转换示例二

**表结构**

```sql
CREATE TABLE `test_date` (
     `id` int(11) DEFAULT NULL,
     `gmt_create` varchar(100) DEFAULT NULL,
     KEY `ind_gmt_create` (`gmt_create`)
) ENGINE=InnoDB AUTO_INCREMENT=524272;
```

**5.5版本执行计划**

**5.6版本执行计划**

|Warning|Cannot use range access on index 'ind_gmt_create' due to type on field 'gmt_create'
```

上述案例是用户在5.5版本升级到5.6版本后出现的隐式转换，导致数据库cpu压力100%，所以我们在定义时间字段的时候一定要采用时间类型的数据类型。
#### 5.5隐式转换示例三

**表结构**

```sql
  CREATE TABLE `t1` (
  `c1` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  KEY `ind_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `t2` (
  `c1` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `c2` varchar(100) DEFAULT NULL,
  KEY `ind_c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
```

执行计划

```sql
mysql> explain     select t1.* from  t2 left  join  t1 on t1.c1=t2.c1 where t2.c2='b';
+----+-------------+-------+------+---------------+--------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys |key| key_len | ref   | rows   | Extra    |
+----+-------------+-------+------+---------------+--------+---------+-------+--------+-------------+
| 1 | SIMPLE | t2 | ref  | ind_c2 | ind_c2 | 303     | const |    258 | Using where |
|1  |SIMPLE |t1  |ALL   | NULL   | NULL   | NULL    | NULL  | 402250 |    |
```

**修改COLLATE**

```sql
mysql> alter table t1 modify column c1 varchar(100) COLLATE utf8_bin ;              
Query OK, 401920 rows affected (2.79 sec)
Records: 401920  Duplicates: 0  Warnings: 0
```

**执行计划**

```sql
mysql> explain   select t1.* from  t2 left  join  t1 on t1.c1=t2.c1 where t2.c2='b';
+----+-------------+-------+------+---------------+--------+---------+------------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref | rows  | Extra       |
+----+-------------+-------+------+---------------+--------+---------+------------+-------+-------------+
|  1 | SIMPLE| t2| ref | ind_c2| ind_c2 | 303     | const      |   258 | Using where |
|  1 |SIMPLE| t1|ref| ind_c1  | ind_c1 | 303     | test.t2.c1 | 33527 |             |
+----+-------------+-------+------+---------------+--------+---------+------------+-------+-------------+
```

可以看到修改了字段的COLLATE后执行计划使用到了索引，所以一定要注意表字段的collate属性的定义保持一致。

#### 5.6 两个索引的常见误区

误区一：对查询条件的每个字段建立单列索引，例如查询条件为：A=？and B=？and C=？。
在表上创建了3个单列查询条件的索引ind_A(A)，ind_B(B)，ind_C(C)，应该根据条件的过滤性，创建适当的单列索引或者组合索引。

误区二：对查询的所有字段建立组合索引，例如查询条件为select A,B,C,D,E,F from T where G=？。
在表上创建了ind_A_B_C_D_E_F_G(A,B,C,D,E,F,G)。

### 6 总结

1. 不要过度索引。索引越多，占用空间越大，反而性能变慢；
1. 只对WHERE子句中频繁使用的建立索引；
1. 尽可能使用唯一索引，重复值越少，索引效果越强；
1. 使用短索引，如果char(255)太大，应该给它指定一个前缀长度，大部分情况下前10位或20位值基本是唯一的，那么就不要对整个列进行索引；
1. 充分利用左前缀，这是针对复合索引，因为WHERE语句如果有AND并列，只能识别一个索引(获取记录最少的那个)，索引需要使用复合索引，那么应该将WHERE最频繁的放置在左边。
1. 索引存在，如果没有满足使用原则，也会导致索引无效。

------------

###### 自猿其说Tech-京东物流技术发展部
###### 作者：王新宇（北斗星团队）