关于mysql:数据库索引的知识点你所需要了解的都在这儿了

数据库索引，置信大家都不生疏吧。

索引是对数据库表中一列或多列的值进行排序的一种构造，应用索引可快速访问数据库表中的特定信息。作为辅助查问的工具，正当的设计索引能很大水平上加重 db 的查问压力，db 咱们都晓得，是我的项目最外围也是最单薄的中央，如果压力太大很容易产生故障，造成难以预计的影响。所以，不论是日常开发还是面试，索引这一块常识体系都是必须把握的。

当然，虽说是必须把握，但索引的知识点很多，很多初学者常常会脱漏，这也是我为什么想写这篇知识点总结的起因，既是给读者的分享，也是给本人一次全面的温习，心愿对你们有所帮忙。

好了，废话不多说，进入正题。

首先申明一下，本文索引的知识点全副是基于 MySQL 数据库

索引的优缺点

长处：

1. 大大放慢数据的查问速度

2. 惟一索引能够保障数据库表每一行的唯一性

3. 减速表连接时间

毛病：

1. 创立、保护索引要消耗工夫，所以，索引数量不能过多。

2. 索引是一种数据结构，会占据磁盘空间。

3. 对表进行更新操作时，索引也要动静保护，升高了保护速度

索引的类型

索引的呈现是为了进步查问效率，然而实现索引的形式却有很多种，所以这里也就引入了索引模型的概念。这里介绍三种罕用于索引的数据结构，别离是哈希表、有序数组和搜寻树。

哈希索引

哈希表，也称散列表，次要设计思维是通过一个哈希函数，把关键码映射的地位去寻找寄存值的中央，读取的时候也是间接通过关键码来找到地位并存进去，这种数据结构的均匀查找复杂度为 O(1)。

比方咱们保护一张身份证信息和用户姓名的表，须要依据身份证号查问姓名，哈希索引大略是这样的：

这种索引构造长处在于随机增加或删除单个元素的效率高，毛病在于哈希表中的元素并不一定按顺序排列，所以如果想做区间查问的话是很慢的，

假如我想查找图中身份证号在 [ID_card_n1, ID_card_n3] 这个区间的所有用户的话，就必须全副扫描一遍了。

所以，哈希表这种构造实用于只有等值查问的场景

有序数组索引

有序数组索引在等值查问和区间查问场景中的效率都很高，还是拿下面的图做例子，用有序数组实现的话是这样子的：

数组的元素按身份证号有序排列，要查问数据的时候，应用二分法就能够疾速失去，工夫复杂度为 O(logN)，而且，因为是有序排列，查问某个区间内的数据也是十分的快。

当然，有序数组的毛病也很显著，就跟 ArrayList 一样，尽管搜寻快，但增加删除元素都有可能要挪动前面所有的元素，这是数组的人造缺点。所以，有序数组索引只实用于动态存储引擎，比方你要保留的是 2017 年某个城市的所有人口信息，这类不会再批改的数据。

搜寻树索引

说到搜寻树，咱们最相熟的应该就是二叉搜寻树了，二叉搜寻树的特点是每个结点的左儿子小于父结点，父结点又小于右儿子，并且左右子树也别离为二叉搜寻树，均匀工夫复杂度是 O(log2(n))。

它既有链表的疾速插入与删除操作的特点，又有数组疾速查找的劣势，同时，因为自身二叉搜寻树是有序的，所以也反对范畴查找

这么说起来，其实二叉搜寻树来做索引如同也是个不错的抉择，其实不然

首先咱们要明确的一点是，这棵树是存在于磁盘中，每次咱们都要从磁盘中读取出相应的结点，然而二叉搜寻树的结点在文件中是随机寄存的，所以可能读取一个结点就须要一个磁盘 IO，恰好二叉搜寻树都会比拟高，如一棵一百万个元素的均衡二叉树就有十几层高度了，也就是大部分状况下检索一次数据就须要十几次磁盘 IO，这个代价太高了，所以个别二叉搜寻树也不会被用来作索引。

为了让一个查问尽量少地读磁盘，就必须让查问过程拜访尽量少的数据块，也就是说，尽可能的让树的高度变低，也就是用多路搜寻树，而 InnoDB 存储引擎应用的就是这种多路搜寻树，也就是咱们常说的 B + 树。

InnoDB 的索引构造

InnoDB 是 MySQL 中最罕用的搜索引擎，它的索引底层构造用的就是 B + 树，所有的数据都是存储在 B + 树中的。每一个索引在 InnoDB 中对应一颗 B + 树。

B+ 树的特点是：

• 所有的叶子结点中蕴含了全副元素的信息，及指向含这些元素记录的指针，且叶子结点自身依关键字的大小自小而大程序链接。
• 所有的两头结点元素都同时存在于子结点，在子结点元素中是最大（或最小）元素。

这种构造有两个长处：

• 能够使得繁多结点存储更多的元素，除了叶子结点，其余的结点只是蕴含了键，没有保留值，这样的话，树的高度就能无效升高，从而缩小查问的 IO 次数；
• 同时，因为叶子结点蕴含了下个叶子结点的指针，所以范畴查问的时候如果搜寻到第一个叶子结点的话，就能依据指针指向查问前面的数据，不必再从根结点遍历了。这也是为什么很多大神倡议表的主键设计成自增长的好，因为这样范畴查问能提高效率

索引的分类

依照构造来分的话，数据库索引能够分为聚簇索引和非聚簇索引。

聚簇索引，也叫汇集索引，就是依照每张表的主键结构一颗 B + 树，同时叶子结点中寄存的就是整张表的行记录数据，简略点说，就是咱们常说的主键索引。在聚簇索引之上创立的索引称之为辅助索引，辅助索引拜访数据总是须要二次查找。

非聚簇索引，也叫非汇集索引，二级索引。这种索引是将数据与索引离开存储，索引构造的叶子结点指向了数据对应的地位。

聚簇索引

InnoDB 应用的是聚簇索引，将主键组织到一棵 B + 树中，而行数据就贮存在叶子节点上，咱们先假如一张用户表，这张表蕴含了 id，name，company 几个字段，

用图片示意 InnoDB 的索引构造大略是这样：

从图中就能够看出，如果咱们应用 ”where id = 14″ 这样的条件查找主键，则依照 B + 树的检索算法即可查找到对应的叶结点，之后取得行数据。

若对 Name 列进行条件搜寻，则须要两个步骤：第一步在辅助索引 B + 树中检索 Name，达到其叶子节点获取对应的主键。第二步应用主键在主索引 B + 树种再执行一次 B + 树检索操作，最终达到叶子节点即可获取整行数据 。（ 重点在于通过其余键须要建设辅助索引）

这是聚簇索引的构造，而非聚簇索引的代表是 MyISM，这也是 MySQL 中常见的搜索引擎。

非聚簇索引

非聚簇索引的两棵 B + 树看上去没什么不同，结点的构造完全一致只是存储的内容不同而已，主键索引 B + 树的节点存储了主键，辅助键索引 B + 树存储了辅助键。索引自身不存储数据，数据存储在独立的中央，这两颗 B + 树的叶子节点都应用一个地址指向真正的表数据。

看上去，如同非聚簇索引的效率要高于聚簇索引，因为不必查两次 B + 树，那为什么最罕用的 InnoDB 引擎还要用这种存储构造呢？它自身的劣势在哪？

1、聚簇索引中，因为行数据和叶子结点存储在一起，同一页中会有多条行数据，拜访同一数据页不同行记录时，曾经把页加载到了 Buffer 中，再次拜访的时候，会在内存中实现拜访，不用拜访磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就能够立即将行数据返回了，所以，如果依照主键 Id 来组织数据，取得数据更快。

2、辅助索引应用主键作为 ” 指针 ” 而不是应用地址值作为指针的益处是，缩小了当呈现行挪动或者数据页决裂时辅助索引的保护工作 ， 应用主键值当作指针会让辅助索引占用更多的空间，换来的益处是 InnoDB 在挪动行时毋庸更新辅助索引中的这个 ” 指针 ”。也就是说行的地位（实现中通过 16K 的 Page 来定位）会随着数据库里数据的批改而发生变化（后面的 B + 树节点决裂以及 Page 的决裂），应用聚簇索引就能够保障不论这个主键 B + 树的节点如何变动，辅助索引树都不受影响。

3、聚簇索引适宜用在排序、范畴查问，非聚簇索引不适宜。

笼罩索引

说到辅助索引，咱们还能够延长出另一种特地的索引，就是 笼罩索引。

下面说了，聚簇索引中拜访数据要通过二次查找，就是先找到辅助键的叶子结点，失去主键对应的结点后再用主键索引查问数据，这样还是比较慢的，其实，如果咱们所需的字段第一次查找就能获取到的话，就不必再二次查找主键了，也就是不必“回表”。

就还是下面那张表有三个字段 id，name，company 的表来说，我给 name 加了索引，在查问数据的时候，我就这么写语句：

select name from user where name like '张 %';

因为咱们的语句走了索引，并且返回的字段在叶子结点都存在，查问的时候就不会回表了，多好啊~~

所以，如果所需的字段刚好是索引列的话，尽量用这种查问形式，不要用 select * 这种语句。

索引品种

后面说的索引分类是依照构造来分，如果按作用范畴来分的话，索引还能够分为以下几种：

一般索引：这是最根本的索引类型，没唯一性之类的限度。

CREATE INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)

唯一性索引：和一般索引基本相同，但所有的索引列只能呈现一次，放弃唯一性。

CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)

主键：跟惟一索引一样，不能有反复的列，但实质上，主键不能算是索引，而是一种束缚，必须指定为 ”PRIMARY KEY”。它跟惟一索引的区别在于：

• 主键创立后肯定蕴含一个唯一性索引，唯一性索引并不一定就是主键。
• 唯一性索引列容许空值，而主键列不容许为空值。
• 主键列在创立时，曾经默认为空值 + 惟一索引了。
• 主键能够被其余表援用为外键，而惟一索引不能。
• 一个表最多只能创立一个主键，但能够创立多个惟一索引。
• 主键更适宜那些不容易更改的惟一标识，如主动递增列、身份证号等。

全文索引：全文索引的索引类型为 FULLTEXT，能够在 VARCHAR 或者 TEXT 类型的列上创立。在 MySQL5.6 以前的版本，只有 MyISAM 存储引擎反对全文索引，5.6 及之后的版本，MyISAM 和 InnoDB 存储引擎均反对全文索引。

CREATE FULLTEXT INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)

联结索引：联结索引其实不是一种索引分类，就是蕴含多个字段的一般索引，比方有个联结索引为 index(a，b)，查找的时候能够用 a and b 作为条件，

最左匹配准则

联结索引中，最左优先，以最右边的为终点任何间断的索引都能匹配上。同时遇到范畴查问 (>、<、between、like) 就会进行匹配。

就像下面说的 index(a，b)或者是 a 独自作为查问条件都会走索引，然而如果是独自用 b 做查问条件就不会走索引了

或者是如果建设 (a,b,c,d) 程序的索引的话，用 a = 1 and b = 2 and c > 3 and d = 4 这样的语句搜寻，d 是用不到索引的，因为 c 字段是一个范畴查问，它之后的字段会进行匹配。

索引什么时候会生效

1、索引列用函数或表达式，比方这种

select * from test where  num  +  1 = 5

MySQL 无奈解析这种方程，这齐全是用户的行为，应该把索引列当成独立的列，这样索引才会失效。

2、存在 NULL 值条件

select * from user where user_id is not null;

咱们在设计数据库表时，应该尽力防止 NULL 值呈现，如果数据有为空的状况能够给一个默认值，比方数值型的能够给 0、-1，字符类型的能够给空字符串。

3、用 or 表达式作为条件，有一个列没有索引，那么其它列的索引将不起作用

select * from user where user_id = 700 or user_name = "老薛";

像这种，如果 user_id 有加索引，而 user_name 没有的话，那么执行的时候 user_id 的索引也是生效的，这也是为什么开发中尽量少用 or 的起因，除非是两个字段都加了索引。

4、列与列比照，某个表中，有两列（id 和 c_id）都建了独自索引，上面这种查问条件不会走索引

select * from test where id = c_id;

5、数据类型的转换。如果列类型是字符串，那肯定要在条件中将数据应用引号援用起来，否则不应用索引

create index `idx_user_name` ON user(user_name)
select * from user where user_name = 123;

像下面这种，尽管给 user_name 建设了索引，但查问的时候条件没有当成字符串，这样的话就不会走索引。

6、NOT 条件

当查问条件为非时，索引定位就艰难了，执行打算此时可能更偏向于全表扫描，这类的查问条件有：<>、NOT、in、not exists

select * from user where user_id<>500;
select * from user where user_id in (1,2,3,4,5);
select * from user where user_id not in (6,7,8,9,0);
select * from user where user_id exists (select 1 from user_record where user_record.user_id = user.user_id);

7、like 查问是以 % 结尾

当应用含糊搜寻时，尽量采纳后置的通配符，例如要查姓张的人，能够用user_name like‘张 %’，这样走索引时，能够从后面开始匹配索引列，但如果是这样user_name like‘% 张’，那么就会走全表扫描的形式

8、多列索引，遵循最左匹配准则，这个下面说了

什么时候该用索引

后面说了，索引尽管能放慢查问速度，但自身也会占用空间，所以，索引的创立并不是越多越好，为了使索引能无效利用，咱们要把索引留给最有用的查问字段，一般来说，应该在这些字段上创立索引：

• 主键字段，这不必多说了吧；
• 常常须要搜寻的列，比方 where 条件常常用到的字段；
• 其余表的外键字段，作为连贯表的条件字段，能够无效放慢连表查问速度；
• 查问中作为排序、统计或者是分组的字段；

同样，对于有些字段不应该创立索引，这些列包含

• 频繁更新的字段不适宜创立索引，因为每次更新不单单是更新记录，还会更新索引，保留索引文件
• where 条件里用不到的字段，不创立索引；
• 表记录太少，不须要创立索引；
• 对于那些定义为 text，image 类型的列不应该减少索引。这是因为，这些列的数据量要么相当大，要么取值很少，不利于应用索引；
• 数据反复且散布均匀的字段，因而为常常查问的和常常排序的字段建设索引。留神某些数据蕴含大量反复数据，这种字段建设索引就没有太大的成果，例如性别字段，只有男女，不适宜建设索引。

explain 关键字

explain 是 MySQL 的关键字，通过该关键字咱们能够查看搜寻语句的性能。

这是查问表的数量，一共有三千多万行，这么多的数据，咱们搜寻的时候必定要用到索引才行，至于索引是否会失效，咱们也能够通过该关键字来看下

看，搜寻的条数霎时降到了 16 条，走的索引是 index_user_id，证实咱们的索引是失效的。

对于 explain 的几个重要参数，咱们有必要理解一些：

id：查问的序列号

select_type：查问的类型，次要是区别一般查问和联结查问、子查问之类的简单查问。

type：

type 显示的是拜访类型，是较为重要的一个指标，后果值从好到坏顺次是：

system > const > eq_ref > ref >fulltext > ref_or_null > index_merge > unique_subquery >index_subquery > range > index > ALL

System 效率最高，ALL 的话曾经是全表扫描了，一般来说，查问至多要达到 range 级别。

key：

显示 MySQL 理论决定应用的键。如果没有索引被抉择，键是 NULL。

key=primary 的话，示意应用了主键；key=null 示意没用到索引。

possible_keys：

指出 MySQL 能应用哪个索引在该表中找到行。如果是空的，没有相干的索引。这时要查看语句中是不是有什么状况导致索引生效。

rows：

示意执行打算中预计扫描的行数，是个估计值。

Extra：

• 如果是 Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。
• 如果是 where used，就是应用上了 where 限度。
• 如果是 impossible where 示意用不着 where，个别就是没查出来啥。
• 呈现 using index 就阐明咱们的索引是失效的。

总结

好了，索引的知识点就介绍到这了，最初总结一下索引的注意事项吧。

1、索引要依据表数据的应用状况来创立，不能创立太多，个别一张表不倡议超过 6 个索引字段

2、好刀要用在刀刃上，常常用于查问，没多少反复数据，搜寻行数不超过表数据量 4% 的字段用索引的成果比拟好

3、创立联结索引要留神最左匹配准则，切记，最右边的字段是必传字段，这点我他妈就吃过大亏

4、查问语句要用 explain 执行打算来查看性能。

参考：

https://www.jianshu.com/p/fa8…

MySQL 实战 45 讲

最初

尽管都是基础知识，但也花了我一天的工夫来整顿了，洋洋洒洒五千多字，也算是一篇干货了，各位看官感觉有所播种的话，还望能给鄙人来个转发或点赞之类的，不求四连，能双连或者是一连我都很称心了，你们的举手之劳就是我一直创作的能源！

作者：鄙人薛某，一个不拘于技术的互联网人，欢送关注我的公众号，每周不定期更新干货文章，这里不仅有技术，还有吹水~~~