终于有人把MYSQL索引讲清楚了

一什么是索引

索引在MYSQL中也可以称为键，其是存储引擎用于快速查找记录的一种数据结构；这样听起来有点生涩，你可能难以理解；如果给你一本书，你如何能够精确的查找到书中某个章节的具体位置呢？我们肯定是先看目录，再找内容。你可以理解索引就像书的目录一样；当数据库的数据量大的时候，索引的性能对数据库非常重要，索引分为很多种，所以要学习好索引的相关知识，甚至比查询优化更重要。

二B-Tree与 B+Tree2.1B-tree树

学习B-树之前读者肯定要有二叉树的基础知识，（没学过的看这篇

blog.csdn.net/youku1327/article/details/105159762）

MYSQL中的数据结构实际上是B+treemysql索引表，而非Btree；所以我们先要了解一下什么是Btree，再了解下一下什么是B+tree; 要得出的结论是为什么MYSQL要使用B+tree, 而非 Btree；

M阶B-tree的特征如下

如果没学过数据结构的读者看到这边肯定一头雾水，知识追寻者还是做个简单的说明；如下图3阶B-树所示；

树的高度决定了磁盘的IO能力，一棵3阶的B-磁盘IO能力为3，与二叉树IO能力相同；数据库加载索引的时候是加载磁盘页（默认4K大小），而非整个索引，每个磁盘页都对应索引的记录，故B-树并不能带来高效磁盘IO；

从树的形态上B-树比二叉树更加的胖，原因也很简单，B-树的节点可能包含多个元素；

注：B树就是B-树，面试的时候别说B减树；2.2B+tree树

B+树是B-树的基础上进行升级，B+树的特征如下

对B+树也做个简单说明：

2.3B+比B-优势

B+比B-的优势在哪里，面试经常问道；

三 索引3.1 非聚集索引和聚集索引

MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚集索引（普通索引）和聚集索引；

聚集索引:聚集索引的顺序就决定了数据行的物理存储顺序；所以我们创建的主键索引其实就是聚集索引，如果未定义主键，MYSQL会默认选择非空的唯一索引当作主键，否则会默认生成一个主键

非聚集索引：索引顺序与数据行物理排列顺序无关；

看下普通索引如何创建，其作用就是加快查询速度；

语法格式如下

alter table 表名 add index 索引名称(索引字段)

如果创建表的时候语法格式如下

CREATE INDEX 索引名称 ON 表名 (索引字段)

知识追寻者手头有一张用户表，模拟10万数据；

未创建索引查询速度

select * from sys_user where first_name = 'ijklmnopqrs'
> OK
> 时间: 0.059s

创建索引

alter table sys_user add index select_username(first_name)

创建索引后查询速度

select * from sys_user where first_name = 'ijklmnopqrs'
> OK
> 时间: 0.049s

删除索引

DROP INDEX [索引名称] ON 表名; 

查看索引

SHOW INDEX FROM 表名;

3.2索引的分类

Mysql中索引的种类也不是很多，不同类型的索引有不同的作用，索引的作用相互之间也存在交叉关系，Mysql中索引主要分为以下几类：

主键索引（PRIMARY KEY）：主键索引一般都是在创建表的时候进行指定，一个表只有一个主键索引，特点是唯一、非空。MYSQL常用就是 自增主键；唯一索引（UNIQUE）：唯一索引具有的特点就是唯一性，即指定列不能出现重复数据；前缀索引（prefix INDEX）：前缀索引建立的基础就指定列数据有很多的共同前缀；联合索引：联合索引又称符合索引，是在表中两个或者两个列以上的基础上创建索引；覆盖索引：当一个索引包含(或者说是覆盖)需要查询的所有字段的值时,我们称之为覆盖索引；3.3 主键索引和唯一索引

主键索引我们通常不默认，经常使用，一张表中仅允许有一个主键，可以由一个或者多字段组成；主键索引满足如下特征：

创建主键语法格式

alter table 表名 add primary key (字段名称)

创建唯一索引语法格式：

alter table 表名 add unique (字段名称)

如果是创建表时添加约束语法格式

CREATE UNIQUE INDEX 索引名称 ON 表名(字段(字段长度));

3.4 前缀索引

前缀索引: 当对字符串进行索引时,如果数据库中该字段有许多的前缀重复就可以使用前缀索引，,这样可以大大的节约索引空间,从而提高索引效率；但其缺点也很明显，不能在 order by 和 group by 中使用；

前缀索引经常使用在地名，比如 xx省xx市xx县这种情形，有一个统一的前缀 xx省xx市；

创建语法

alter table 表名 add key (字段名称(前缀长度)) 

示例

alter table sys_user add key (first_name(8)) 

查询的时候使用指定前缀的长度性能更加

select * from sys_user where first_name = 'ijklmnop'

3.5 覆盖索引

回表查询：

MYSQL 如果只通过索引就可以返回查询所需要的数据，就是不是回表查询，否则查到索引数据后还需要回到表中查询数据就是回表查询；

我们来看个简单的示例

先去除前缀索引

drop index first_name on sys_user

然后加上普通索引

alter table sys_user add index select_username(first_name)

实行MYSQL执行计划

（没学过MYSQL执行计划看这篇 blog.csdn.net/youku1327/article/details/107336500）

explain select id from sys_user where first_name = 'ijklmnop'

输出结果表示 使用using index ， 由于 id 和 first_name 都是索引；所以不需要回表查询就是覆盖索引；

如果我们使用如下语句则需要回表查询，原因是查询到字段id, first_name后还需要回表查询其它字段，这就是为什么 select *如此慢的原因；

explain select * from sys_user where first_name = 'ijklmnop'

输出结果如下

3.6 联合索引

联合索引是在表中用2个或者2个以上的字段创建索引，其创建索引方式与普通索引相同；其能减小检索范围；

语法格式

alter table 表名 add index 索引名称(字段1，字段2...)

最左前缀匹配原则

使用联合索引有一个非常重要的因素就是所有的索引列只可以进行最左前缀匹配原则；

比如

联合索引 first_name和 last_name

alter table sys_user add index select_username(first_name,last_name)

根据最左匹配原则情形如下会命中索引

转换为查询语句命中索引示例如下

select * from sys_user where first_name = 'ijklmnop';
select * from sys_user where first_name ='ijklmnop' and last_name ='ijklmnop';
select * from sys_user where first_name ='ijklmnop' and last_name in (ijklmnop');
select * from sys_user order by first_name,last_name
select * from sys_userwhere first_name ='ijklmnop'order by last_name

如下情形不会命中索引

select * from sys_user where last_name = 'ijklmnop';
select * from sys_userwhere last_name ='ijklmnop'order by first_name

索引下推

Mysql5.6版本发布了索引下推的原则，主要用于like关键字的查询优化 ；

比如联合索引（last_name,age）

select * from sys_user where last_name = 'ijklmnop' and age>'20';

命中可能性如下

第二种方式的磁盘IO会更少，查询效率会更高，这就是下推索引；

除此之外还有全文索引和hash索引，简单了解一下即可；四 索引总结4.1索引的优点4.2创建索引的策略4.3 使用索引时的注意事项

听说关注公众号 知识追寻者 的男孩子都找到了漂亮的女朋友，女孩子都找到了白马王子；当然索引并非查询优化的最佳原则，但在大多数情况下就已经足够使用；在大数据情况下通常要考虑分库分表；

本套教程

- [MYSQL介绍(1)](知识追寻者)

- [MYSQL检索(2)](知识追寻者)

- [MYSQL检索(3)](知识追寻者)

- [MYSQL插入更新删除(4)](知识追寻者)

- [MYSQL事物(5)](知识追寻者)

- [MYSQL数据类型(6)](知识追寻者)

- [MYSQL表结构操作(7)](知识追寻者)

- [MYSQL视图(8)](知识追寻者)

- [MYSQL储存过程(9)](知识追寻者)

- [MYSQL游标与触发器(10)](知识追寻者)

- [MYSQL用户权限管理(11)](知识追寻者)

- [MYSQLl架构入门(12)](知识追寻者)

- [MYSQL锁等待与死锁(13)](知识追寻者)

- [MYSQLl操作json(14)](知识追寻者)

- [MYSQL执行计划(15)](知识追寻者)

- [MYSQL索引(16)](知识追寻者)

（编辑：广州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!