MySQL索引 (index) 实现原理总结

什么是索引

  通俗得讲索引就相当于一本书的目录，通过目录可以快速的找到对应的资源，索引提高检索效率其实最根本的原理是缩小了扫描的范围。
在数据库方面，查询一张表的时候有两种检索方式：

1. 全表扫描
2. 根据索引检索(效率很高)

PS：索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。

比如：表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

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

select ename,sal from emp where ename = ‘SMITH’;
当ename字段没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
当ename字段添加索引的时候，以上sql语句会根据索引扫描，快速定位。

---

创建索引对象&& 删除索引对象

创建索引对象：

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

删除索引对象：

MySQL：drop index [ 索引名称 ] on 表名;
Orcal：drop index [ 索引名称 ];

---

什么时候加索引

1. 数据量庞大。(根据客户的需求，根据线上的环境)
2. 该字段很少的DML操作。(因为字段进行修改操作，索引也需要维护)
3. 该字段经常出现在where子句中。(经常根据哪个字段维护)

---

注意：主键具有unique约束的字段会自动添加索引。

根据主键查询效率较高，尽量根据主键检索。

---

查看sql索引的执行计划：

(explain 语句只有MySQL才有，其他数据库没有)

mysql> explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

给薪资sal字段添加索引：

mysql> create index emp_sal_index on emp(sal);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

再查看执行计划：

mysql> explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

对比两次查询的rows检索次数减少了

---

索引的实现原理

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

以查询emp.ename字段为例：

mysql> select ename from emp;
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
14 rows in set (0.00 sec)

当 ename 字段上没有索引的时候，会进行全表扫描，效率较低
当给 ename 字段加上索引：

mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

emp_ename_index 对象索引会自动排序：

mysql> select ename from emp order by ename;
+--------+
| ename  |
+--------+
| ADAMS  |
| ALLEN  |
| BLAKE  |
| CLARK  |
| FORD   |
| JAMES  |
| JONES  |
| KING   |
| MARTIN |
| MILLER |
| SCOTT  |
| SMITH  |
| TURNER |
| WARD   |
+--------+
14 rows in set (0.00 sec)

排序后的索引会存到硬盘文件或者是内存中，具体存到哪里依据建表的存储引擎
接着索引对象会对数据进行分区：

mysql> select ename from emp order by ename;
+--------+
| ename  |
+--------+
| ADAMS  |——-——-——A区+D区+A区+M区+S区···
| ALLEN  |——-——-——A区+L区+L区+E区···
| BLAKE  |——-——-——B区+L区+A区···
| CLARK  |——-——-——C区+L区···
| FORD   |——-——-——F区···
| JAMES  |
| JONES  |
| KING   |
| MARTIN |
| MILLER |
| SCOTT  |
| SMITH  |
| TURNER |
| WARD   |
+--------+
14 rows in set (0.00 sec)

所以当查询某个字段时例如：

select ename from emp where ename = 'SMITH';

首先条件是 ename 字段，这个时候会查看 ename 字段有没有对应的索引

结果找到了 ename 字段对应的索引是：emp_ename_index

然后通过索引检索，首先ename = ‘SMITH’，先定位S区，继续定位M区…缩短了扫描的数量，最后很快就定位到了SMITH

拿到SMITH的物理地址，最后DQL语句就会变成：

select ename from emp where （SMITH的物理地址）;

总结：

索引通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的"物理地址"，最终通过索引检索到数据之后，获取到关联的物理地址
 
通过物理索引检索到数据之后，数据库表中任何一条记录都有一个物理地址，物理地址在 orcal 中叫 rowid，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
 
select ename from emp where ename = ‘SMITH’;
select ename from emp where 物理地址 = 0x123;（不再走表查询，直接通过物理地址定位硬盘上的数据）

---

索引的分类

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

---

索引失效

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

模糊查询的时候，第一个通配符使用的是%，这个时候索引是是失效的，仍然走全盘扫描，定位不到是哪个区