mysql索引-1

索引概述

介绍

索引(index)是帮助MySQL**高效获取数据**的**数据结构**(**有序**)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优点

1. 提高数据检索的效率，降低数据库的IO成本
2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势–可以忽略

3. 索引列也要沿用空间的---------现在磁盘空间都大，可以忽略
4. 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行insert,update,delete时，降低效率------表大部分的任务是查询

索引结构

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持B+Tree树索引
Hash索引	底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效不支持范围查询
R-tree(空间索引)	空间索引是MylSAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

索引	InnoDB	MyISAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-tree(空间索引)	不支持	支持	不支持
Full-text(全文索引)	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

二叉树

缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢
顺序插入的缺点，由红黑树解决

红黑树

缺点：大数据量情况下，层级较深，检索速度慢
数据多检索慢，延伸出 B-Tree(多路平衡二叉树)

B-Tree(多路平衡二叉树)

缺点：B-Tree的叶子节点和非叶子节点都会存储数据，导致一页中存储的键值减少，指针跟着减少，要同B+tree一样保存大量数据，只能增加树的高度(根据上一章mysql存储引擎中提到的，页的大小是固定的16k)，导致性能降低
以一颗最大度数（max-degree)为5(5阶)的b-tree为例(每个节点最多存储4个key,5个指针):

B+Tree

相对于B-Tree区别：

1. 所有的数据都会出现再叶子节点
2. 叶子节点形成一个单向链表

B+Tree（MySQL）

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

所有的数据都会出现在叶子节点

Hash

缺点:Hash索引只支持等值匹配，B+Tree支持范围匹配及排序操作
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突(也称为hash碰撞)，可以通过链表来解决。

Hash索引特点
3. Hash索引只能用于对等比较(=，in)，不支持范围查询(between，>,<，…） ------因为是无序的
4. 无法利用索引完成排序操作 ------因为是无序的
5. 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引 -----如果不出现Hash冲突就查一次，否则在对应链表中查找
存储引擎支持
在MsQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

索引分类

分类	含义	提点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建	primary
唯一索引	避免同一个表中某数据列中的值重复 (设置唯一约束)	可以有多个	unique
常规索引	快速定位特定数据	可以有多个
全文索引(使用少)	全文索引查找的是文本中的关键字，而不是比较索引中的值	可以有多个	fulltext

在lnnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种:

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到一起，索引结构的叶子节点保存了行数据	必须有，而且只能有一个
二级索引(Secondary Index)	将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则:

1. 如果存在主键，主键索引就是聚集索引。
2. 如果不存在主键，将使用第一个唯一(UNIQUE）索引作为聚集索引。
3. 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

name 列下存储的并非这一行的数据，而是id的值

例：select *from user where name = ‘Arm’
执行查询后，先去二级索引(b+tree)中定位到’Arm’，取得这一行数据的主键值,再根据主键值去聚集索引(b+tree)中定位到整行的行数据
被称为：回表查询