数据库索引详解

什么是索引

介绍索引前先介绍三个概念：

• 二分查找（折半法）：快速查找元素位置的方法，详见我的另一篇博客使用python实现二分查找

• 哈希存储 —> 根据对象的哈希码（摘要）来决定对象存储在什么位置
  百度云的快速秒传功能就是应用的是哈希存贮的原理
  python底层数据的存贮也是hash存贮（字典和元组也是）
  

• B+树 是一种层次结构
  根节点相当于是目录 —> 第二层按范围给数据分块 —> 第三层就是表中的数据
  同时每一层中数据内容又是排好序的，应用了二分查找方法

本文章所描述的索引即可等价于是B+树，主要应用到数据库表内容的查询上
索引在数据库表中主要分为两类：

• 聚集索引（聚合索引）：自动根据主键建立的索引（B+Tree的叶子节点就是表中数据）

• 辅助索引（非聚集索引）：用户根据需要自己创建的索引，其中叶子节点保存的是数据的主键，通过这个主键再查聚集索引就可以获取到数据，如果你的索引已经覆盖了查询的全部列，那么就不需要再回表查询

为什么要创建索引

创建索引主要是改善select执行状况，提升查询效率

如何生成执行计划，查看执行效率

MysQL —>explain select …
oracle —> explain plan for select …
举例如下：

explain select * from tb_emp where ename='张三丰';

运行的结果如下：（主要关注三个参数：type、key、rows）

如何判断执行状况的好坏

1. 判断查看key处内容，判断是否使用了索引
2. 查看type类型内容：
   类型值判断标准如下：
   const>ref_eq>ref>range>index>all
   其中all是最糟糕的一种查询情况，const最好
3. 查看查询的行数：行数越少，执行情况越好

如何创建索引和删除索引

详见下面的sql语句

-- 创建独一无二的索引（前提是员工名字独一无二可以建立唯一索引）
create unique index uk_emp_ename on tb_emp (ename);

-- 删除索引
drop index uk_emp_ename on tb_emp;

-- 不排除重复的
create index idx_emp_ename on tb_emp (ename);

-- 前缀索引(只给员工名字第一个字建立索引)
create index idx_emp_ename on tb_emp (ename(1));

drop index idx_emp_ename on tb_emp;


-- 复合索引（从左往右，最左匹配原则）
create index idx_emp_ename_job on tb_emp (ename(1),job);

重新执行以下语句

explain select * from tb_emp where ename='张三丰';

执行结果变为：

从最新结果可看，添加索引后，type变成了const，查询的行数也只有1行，执行情况得到很好的改善，查询效率得到了极大的提高。

使用索引需要注意的地方

1. 模糊查询在某些情况下不能使用索引
这种模糊查询可以使用索引

explain select * from tb_emp where ename like '张%';

前面加%号的模糊查询绝对使用索引，索引会失效

explain select * from tb_emp where ename like '%张%';

2. 以下情况不能使用复合索引（符合索引的最左匹配以及or语句要相当注意）
符合索引的创建方式：

-- 复合索引（从左往右，最左匹配原则）
create index idx_emp_ename_job on tb_emp (ename(1),job);

以下查询方式原则上复合索引都会失效：

-- 下面的查询无法使用复合索引，因为索引的匹配要遵循最左匹配原则
explain select * from tb_emp where job='程序员';
-- 此处原则上也是不能使用索引的，SQL内部解释器做了优化，把张三丰的条件放在前面的
explain select * from tb_emp where job='程序员' and ename='张三丰'; 
-- 下面的查询使用or后也无法使用符合查询
explain select * from tb_emp where ename='张三丰' or job='程序员';
-- 此条语句使用不了符合索引，应为复合索引中名字在前面，所以必须要包含名字才行，否则索引失效，因为必须符合最左匹配原则，这条语句是全表查询
explain select * from tb_emp where  job='程序员';