mysql索引

后面explain介绍等级参考文档：https://blog.csdn.net/ssspk_/article/details/89422085   大道至简

 

要了解原理，说白了就是要明白他需要做什么，索引的目的是什么，如果是你，你会如何设计？

　　首先，在一个表中，存储数据过大的时候，我们如何才可以快速找到我们需要的数据？索引就像字典的目录一样，根据目录，可以很快找到内容所在的位置，进一步找到我们需要的data。

　　官方一点：索引是帮助MySQL高效获取数据的排好序的数据结构。

　　接下来开始第二步，索引要如何设计，采用哪种算法，哪种数据结构可以进一步提高速度？

　　数据结构：B树，B+树。

首先看下B树：

叶节点具有相同的深度，叶节点的指针为空

•所有索引元素不重复

•节点中的数据索引从左到右递增排列

B树可以横向存在多个索引，但是，存在一个很大的问题：空间是有限的（Mysql规定：每个分支的最大大小为16KB，即第一行最大16KB，第一行的每个分支下的第二行都可以存16KB......），如果数据过多，因为每个节点中都存储了索引和对应数据，就会造成树的深度很深。

因此，在此基础上，做了另外的优化，也就是B+树：

B+Tree(B-Tree变种)

•非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引

•叶子节点包含所有索引字段

•叶子节点用指针连接，提高区间访问的性能

所以叶子结点包含了我们整张表的所有的索引元素及data。

指针：存储子节点地址信息；键值：记录的索引（也就是一般所说的主键）；数据：记录表中除主键外的数据。

mysql指针是双向的(也就是我们所说的，支持顺序查找和倒序查找)。最后一个节点指向头节点。

B+树叶子结点指针的作用：查找范围很快。不用回到根节点重新查找。

目前大多数用的b+ tree，而不用hash算法：因为hash算法精确查找很快，但是范围查找、排序等很慢。

 注意：在B+树上存在两个头指针：一个指向根节点，另一个指向关键字的最小的叶子结点，所有的叶子结点之间是一种链式环的结构，这样做的目的是：可以进行两种查找，一种是根据主键进行的范围查找和分页查找，另一种是从根节点开始进行随机查找。

总结：

hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（innodb默认支持它）

---------------------------------------------------------------------------

数据库中每个表都可以设置存储引擎，不同的表可以使用不同的存储引擎：

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

mysql数据库数据存储在磁盘文件中，存储在data文件夹中。索引存储在MYI文件中。数据存储在MYD文件中。

MyISAM索引文件和数据文件是分离的(非聚集，疏散索引)

先在索引MYI文件中的索引对应的磁盘文件位置，在MYD文件中查找到对应数据。

InnoDB索引实现(聚集)

•表数据文件本身就是按B+Tree组织的一个索引结构文件

•聚集索引（聚簇索引）：叶节点包含了完整的数据记录，即：索引和表的数据及树节点都在一个文件中。

•为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

没有主键没办法使用B+树存储，有一个唯一的标识找数据；查找过程中涉及到大量元素索引的比较，整型比较会快很多（uuid或者其他，需要先挨个将字符转换成ascii码，在一个个比较，且占用存储空间大），整型自增，叶子结点从左到右是自增的，如果不是自增的，新插入数据，如果当前已经存储满了，会分裂，变成两个节点，且还要平衡，因此用自增，出现分裂和平衡几率会很小。

 

•为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

　　叶子结点中存储的是索引所在那行的主键，也就是查找的时候要先遍历非主键索引的B+tree，再遍历主键索引的B+Tree，性能比主键索引会低，目的：一致性（如果既有主键索引，也有非主键索引，涉及到事物）和节省存储空间（不必每个索引文件都存储数据）。

主键索引：

非主键索引存储内容：

注：frm后缀存储的表结构，ibd存储的索引和数据。第一个图最后的叶子结点存储的是索引及其所有数据。

 

联合索引的底层存储结构长什么样？

将多个索引（按照每个索引的排序顺序）一起当作一个索引，只用一张B+tree表即可，叶子结点中存放主键。

从第一个字段开始查找，不能直接从第二个索引开始查找，直接从第二个查找，不会走索引；如果直接用第一个索引和第三个索引作为条件，只会走第一个字段索引。

不要在索引列上面做任何函数计算，否则将不走索引。

核心：就是按照索引字段顺序一个个排序，中间不能断。

覆盖索引：代表当前查找结果的所有字段都在索引中。即sql所有查找的字段都只在索引key中，包括查找条件。

 

实战告诉你索引有多重要？

数据库有30张表，每张表有1亿条数据，怎么才能快速找到符合要求的一条？

　　建立索引，理论上一颗三阶的B+树可以维护10亿条索引，根节点缓存在内存中，只需要两次io就能获得所需的数据主键，在进行一次或两次io回表取得完整数据。

回表：先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树.

-------------------------------------------------------------------------------

首先类型有许多，这里我只给大家介绍企业里面用的最多的类型：

system>const>eq_ref>ref>range>index>ALL

越往左边，性能越高，比如system就比ALL类型性能要高出许多，其中system、const只是理想类型，基本达不到；

我们自己实际能优化到ref>range这两个类型，就是你自己写SQL，没优化基本上就是ALL，优化尽量达到ref>range这两个级别；

左边基本达不到！

要对type优化的前提是，你需要有索引，如果你连索引都没有创建，那你就不用优化了，肯定是ALL.....；

Type级别详解

一.system级别

索引类型能是system的只有两种情况：

1.只有一条数据的系统表

只有一条数据的系统表，就是系统里自带一张表，并且这个表就一条数据，这个基本上就达不到，这个是系统自带的表，而且就一条数据，所以基本达不到；

2.或衍生表只能有一条数据的主查询

这个是可以实现的，但是在实际开发当中，你不可能去写一个这么个玩意儿，不可能公司的业务去让你把SQL索引类型写实system...

const级别

1.仅仅能查出一条的SQL语句并且用于Primary key 或 unique索引；

能查出来一条SQL语句，你的索引还必须是Primary key或unique；

三.eq_ref级别

唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多，不能0）;

解说：比如你select ...from 一张表 where 比方说有一个字段 name = 一个东西，也就是我们以name作为索引，假设我之前给name加了一个索引值，我现在根据name去查，查完后有20条数据，我就必须保证这二十条数据每行都是唯一的，不能重复不能为空！

添加唯一键语法：alter table 表名 add constraint 索引名 unique index(列名)

检查字段是否唯一键：show index form 表名；被展示出来的皆是有唯一约束的；

四 .ref级别

到ref还是问题不大的，只要你上点心，就可以达到；

非唯一性索引：对于每个索引键的查询，返回匹配的所有行（可以是0，或多个）

假设我现在要根据name查询，首先name可能有多个，因为一个公司或学校叫小明的不止一个人，但是你要用name去查，你必须name是索引，我们先给它加个索引，因为要达到ref级别，所以这里我给它加一个单值索引：

单值索引语法：alter table 表名 索引类型 索引名（字段）

五.range级别

检索指定范围的行，查找一个范围内的数据，where后面是一个范围查询 （between,in,> < >=);

注：in 有时会失效，导致为ALL；

六.index级别

查询全部索引中的数据

讲解：假设我有一张表，里面有id name age，这个时候name是一个单值索引，一旦name被设定成索引，它就会成为B树一样，经过各种算法将name里面的值像树一样进行分类，这个时候我where name = **，就相当于把这颗B树查了一个遍，

也就是说，你把name这一列给查了一遍；

SQL语句：select id From student;//我只查被索引声明的列，必然就是index了；

七.ALL级别

查询全部表数据，就是select name From student;

其中 name 不是索引；

如果你查的这一列不是索引，就会导致全表扫描，所以要避免全表扫描；