【MySQL】二十八、索引

1. 索引，作用

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。

在数据库方面，查询一张表的时候有两种检索方式：

• 全表扫描
• 根据索引检索（效率很高）

索引为什么可以提高检索效率：其实最根本的原理是缩小了扫描的范围。

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

explain关键字

注意：添加索引是给某一个字段，或者说某些字段添加索引。

select ename, sal from emp where ename = 'SMITH';

当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。

可以使用explain关键字查询mysql的执行计划，该关键字是mysql数据库独有的！

explain select sal from emp where sal = 5000;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

type为all：表示全表扫描；

rows为14：表示扫描了14行记录；

2. 创建索引对象

语法：

create index 索引名 on 表名(字段名)

给薪资字段sal添加索引

create index emp_sal_index  on emp(sal);

可以理解为在表emp的sal字段上创建一个名为emp_sal_index的索引。

当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

再次执行expalin语句：

 explain select sal from emp where sal = 5000;

+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+

type的值为ref，rows为1表示仅扫描了1行记录

3. 删除索引对象

语法：

drop index 索引名 on 表名;

删除上述在emp表中创建的emp_sal_index索引“

drop index emp_sal_index on emp;

4.什么时候考虑给字段添加索引（满足什么条件）

• 数据量庞大。（根据客户的需求，根据线上的环境）
• 该字段很少的DML操作，也就是很少对字段值进行修改（因为字段进行修改操作，索引也需要维护）
• 该字段经常出现在where子句中。（经常根据哪个字段查询）

注意： 主键和具有unique约束的字段（带有唯一索引和主键索引）会自动添加索引，所以根据主键查询效率较高！尽量根据主键检索！

5. 索引实现原理

索引底层采用的数据结构是：B+ Tree；

通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

select ename from emp where ename = 'SMITH';

通过索引转换为：

select ename from emp where 物理地址 = 0x3;

当ename字段上没有索引时，会进行全表扫描，效率较低；

索引的工作流程：

（1）给ename字段添加索引时：

create index emp_ename_index on emp(ename);

只要这句一执行，会在硬盘或是在内存中生成索引，具体是硬盘还是内存，是要根据不同的存储引擎而定。如果是memory的引擎，会存储在内存中，如果是MyISAM，则会存在硬盘中。

（2）查询

select ename from emp where ename = 'SMITH';

（3）在内存或硬盘中，生成了一个索引对象（emp_ename_index），如下图右面的图；

（4）索引进行分区，根据首字母分为了M区，S区…；

（5）首先条件是ename字段，这时会查看ename字段有没有对应的索引，结果找到了ename字段对应的索引对象是：emp_ename_index，然后通过索引检索。首先是ename=‘SMITH’，先定位S区，继续定位M区…缩短了扫描的数量，很快定位到SMITH。

（6）数据库表中的任意一行记录都会有一个物理地址。查询语句的原sql语句将变为：

select ename from emp where ename = 0x3;

这条sql语句不再通过表查了，而是通过内存或者硬盘进行查询。

6. 索引的分类

• 单一索引：给单个字段添加索引；
• 复合索引： 给多个字段联合起来添加1个索引；
• 主键索引：主键上会自动添加索引；
• 唯一索引：有unique约束的字段上会自动添加索引；
  …

7. 索引什么时候失效？

select ename from emp where ename like '%A%';

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

即使ename加了索引，索引也是失效，仍然要全表扫描。