文章目录
一、索引概述
1.1 什么是索引
索引(index)是帮助MySQL高效获取数据、高效排序、高效分组的数据结构(有序)。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
1.2 体会索引的作用
现在有一张表：
假设我们需要执行的SQL语句为：
select * from user where age = 45;
- 没有索引的情况下：就需要我们从第一行开始扫描，一直到最后一行，称之为 全表扫描，性能很低。
- 有索引的情况：假设索引就是二叉树，对年龄age这个字段建立一个二叉树的索引结构。
根据二叉树左小右大的规则，我们只需要扫描三次就能找到数据了，极大的提高了查询的效率。
1.3 使用索引的优劣
| 优势 | 劣势 |
|---|---|
| 提高数据检索的效率，降低数据库的IO成本 | 索引列也是要占用空间的 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗 | 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低 |
认识：所以索引并不是建越多越好，更多的索引需要占用更多的空间和CPU，消耗更多的资源。
二、索引结构
2.1 概述
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种:
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+树索引 |
| Hash索引 | 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 |
| R-tree（空间索引） | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| Full-test（全文索引） | 是一种通过建立倒排索引,快速匹配文档的方式。类似于 Lucene,Solr，ES |
注意：我们大部分时候讨论的索引，除非特别指明，一般都是指B+树结构组织的索引
2.2 索引的数据结构
-
假设以二叉树作为索引
- 主键是乱序插入的，情况如下：
- 如果主键是按顺序插入的，则会形成一个单向链表，结构如下：
显然，二叉树作为索引数据结构不现实，存在以下缺点：
-
顺序插入时，会形成一个链表，查询性能大大降低。
-
大数据量情况下，层级较深，检索速度慢。
- 假设以红黑树作为索引
红黑树本质也是一颗二叉树，所以在大量数据的情况下，层级也较深，检索速度也会很慢。
- B-Tree树
B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。
以一颗最大度数(max-degree)为5 (5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针，如下图：
注意：树的度数是指每一个节点的子节点个数。
特点：
- 5阶的B树，每一个节点最多存储4个key，对应5个指针
- 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂
- 在B树中，非叶子节点和叶子节点都会存放数据
- B+Tree树
B+Tree是B-Tree的变种，我们以一颗最大度数(max-degree)为4 (4阶）的b+tree为例，来看一下其结构示意图:
我们可以看到，两部分:
- 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据
- 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据
我们可以在这个网站中，来模拟B+Tree树的工作过程。
二、索引分类
2.1 索引分类
在MySQL数据库，将索引的具体类型主要分为以下几类:主键索引、唯一索引、常规索引、全文索引。
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
2.2 聚集索引&二级索引
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引(Clustered Index) | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 |
| 二级索引(Secondary Index) | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
2.2.2 聚集索引选取规则：
- 如果存在主键，主键索引就是聚集索引
- 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
2.2.2 聚集索引和二级索引的具体结构
- 聚集索引的叶子节点下挂的是这一行的数据。
- 二级索引的叶子节点下挂的是该字段值对应的主键值。
2.2.3 回表查询
由上图可知，在user表中：我们在name字段建立了一个二级索引，那当我们执行如下SQL时，系统就会先根据二级索引查找到叶子节点中存储的id，再根据id按照聚集索引查找到整行数据，这个过程就是回标查询
select * from user where name = 'Arm';
三、索引语法
查看所有索引
SHOW INDEX FROM table_name;
创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;
创建联合索引
CREATE INDEX idx_pro_age_sta on tb_user(profession，age， status);
删除索引
DROP INDEX index_name ON table_name ;
四、最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果
跳跃某一列，索引将会部分失效(后面的字段索引失效)。
我们来看一个案例，比如，我们为tb_user表创建了一个二级联合索引：
create table tb_user(
id int primary key auto_increment comment '主键',
name varchar(50) not null comment '用户名',
phone varchar(11) not null comment '手机号',
email varchar(100) comment '邮箱',
profession varchar(11) comment '专业',
age tinyint unsigned comment '年龄',
gender char(1) comment '性别 , 1: 男, 2: 女',
status char(1) comment '状态',
createtime datetime comment '创建时间'
) comment '系统用户表';
CREATE INDEX idx_pro_age_sta on tb_user(profession，age， status);
现在使用下面三条SQL语句进行测试，他们的共同点就是条件都包含profession
explain select * from tb_user where profession = '软件工程' and age = 31 and status= '0';
explain select * from tb_user where profession = '软件工程' and age = 31;
explain select * from tb_user where profession = '软件工程';
经过测试我们发现，以上三种情况都能成功执行，返回的结果也是一致的。我们再来看另外两种情况：
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';
这轮测试我们发现，在缺少了profession条件之后，SQL语句返回的结果与之前的三条含有profession条件的SQL语句返回的结果不一致，这两条SQL返回的结果为空，这就表明，通过联合索引查询时，查询条件不能跳过最左边的那个索引，否则就会查询失败。这就是最左前缀法则。
接下来，我们将查询条件的顺序打乱
explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程'；
上面这条SQL还是可以成功执行，与刚开始那三条SQL语句返回的结果一致 。这就表明：
最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。
五、索引失效
我们再使用phone字段为tb_user表创建一个唯一索引。
CREATE UNIQUE INDEX id_user_phone ON tb_user(phone);
- 字符串不加引号，索引失效
phone为varchar类型，当我们使用
explain select * from tb_user where phone = 17799990015;
如果字符串不加单引号，对于查询结果可能没有什么影响，但是数据库存在隐式类型转换，索引将失效。
- 使用函数运算操作后，索引失效
explain select * from tb_user where substring(phone,10,2) = '15';
- 使用模糊查询，当占位符放在字符串前面，索引失效
explain select * from tb_user where profession like '%工%'
- 使用OR查询，如果后面的字段没有索引，则前面字段的索引失效
explain select * from tb_user where phone = '17799990017' or age = 23;
- mysql会判断要不要使用索引，如果全表查询比使用索引更快，索引失效
- 违背最左前缀法则，索引失效
228
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
