数据库MySql索引

索引是一种数据结构，帮助我们快速查找满足条件的数据记录，加快查找速度是，数据库操作涉及增删改查操作，这四个操作的速度一般是：查询>修改>新增>删除

其中，查询一般有查询操作不涉及增删改操作

修改操作，一般有where条件，如

update  userInfo set age=30 where id=1000087;

所以一般是先查询再执行删除操作，所以一般修改涉及到查询操作

新增，

删除操作与修改操作一样，一般都有where条件，所以删除操作也涉及到查询。从增删改查这几个操作的分析可以看出，查询操作在数据库操作中比重很大

 

1、 查询
查询是最快的，不涉及修改、新增和删除

2、修改
一般修改都有where条件，先查询一下，找到这个元素再删，所以一般修改包含了查询

3、新增
一遍表都有索引，新增都需要修改索引，所以新增包含了修改

4、删除
删除跟修改类似，都需要先通过where找到指定数据，再删除，同时还要更新表的索引，所以删除包含了查询和修改，是最慢的
————————————————
版权声明：本文为CSDN博主「Star李猛」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/starlemon2016/article/details/90406268

 

首先介绍下常用的用于查询的数据结构

一、 二叉树 - 平衡二叉树 - B树（B-树）- B+树 - 红黑树

1. 二叉树(Binary Search Tree / 二叉查找树 / 二叉排序树 / 二叉搜索树）

一颗m阶二叉查找树应具备如下特征：
1. 若左子树不为空，那么左子树的关键字应比根节点小
2. 若右子树不为空，那么右子树的关键字应比根节点大
3. 左子树和右子树都为二叉查找树

一个二叉树实例如下：

 

二叉树的性能：

在最好的情况下，二叉排序树的查找效率比较高，是 O(logn)，其访问性能近似于折半查找；

但最差时候会是 O(n)，比如插入的元素是有序的，生成的二叉排序树就是一个链表，这种情况下，需要遍历全部元素才行（见下图 ）。

如果我们可以保证二叉排序树不出现上面提到的极端情况（插入的元素是有序的，导致变成一个链表），就可以保证很高的效率了。但这在插入有序的元素时不太好控制，按二叉排序树的定义，我们无法判断当前的树是否需要调整。因此就要用到平衡二叉树（AVL 树）了。

2. 平衡二叉树(又：AVL 树)

平衡二叉树的提出就是为了保证树不至于太倾斜，尽量保证两边平衡。因此它的定义如下：

1. 平衡二叉树要么是一棵空树

2. 要么保证左右子树的高度之差不大于 1

3. 子树也必须是一颗平衡二叉树

也就是说，树的两个左子树的高度差别不会太大。

在创建平衡二叉树的过程中，一般通过左旋、右旋等操作使二叉树变为平衡二叉树

3. B-树

B-树的关键是：指针+关键字+地址
一颗 m 阶的 B- 树具备如下特征：
1.所有节点最多 m棵子树
2.若根节点不为空，则至少2颗子树
3.除根节点之外的所有非终端节点最少  ⌈m/2⌉  颗子树.（⌈m/2⌉  向上取整）
4. 所有非 终端节点包含一下信息数据：（n, A0, K1, A1, K2, ..., Kn, An）
    其中：a. Ki  (i=1,2,3,.., n)为关键码，且Ki < K(i+1)

               b.  Ai (i=1,2,3,.., n) 为指向子树根节点指针，且指针Ai所指子树中所有节点的关键码均大于

                     Ki,小于K(i+1),( i: 1~n-1) 

               c. ⌈m/2⌉  -1 <=n <=m-1 ,其中，n为关键码个数。

5. 所有叶子节点都出现在同一层次上，且不带信息

 

4. B+树

B+树是B-树的变体，也是一种多路搜索树：

       1.其定义基本与B-树同，除了：

       1.非叶子结点的子树指针与关键字个数相同；

       2.非叶子结点的子树指针P[i]，指向关键字值属于 [ K[i], K[i+1] ) 的子树（B-树是开区间）；

       3.为所有叶子结点增加一个链指针；

       4.所有关键字都在叶子结点出现；

二、索引的优缺点

优点：

1. 大大加快数据的检索速度

2. 创建唯一性索引，保证数据库表中每一行数据的唯一性

3. 加速表和表之间的连接

4. 在使用分组和排序子句及进行数据检索，可以显著减少查询中分组和排序的时间

缺点：

1. 索引占用物理空间

2. 对表进行增删改时，索引也需动态维护，降低了数据的维护速度

三、索引种类

唯一索引 / 非唯一索引、主键索引、聚集索引 / 非聚集索引、组合索引

1. 唯一索引 / 唯一索引

唯一索引：是在表上一个或多个字段组合建立的索引，这个或这些字段组合起来的值在表中不可以重复。

非唯一索引：是在表上一个或多个字段组合建立的索引，这个或这些字段组合起来的值在表中可以重复、不要求唯一。

2. 主键索引（主索引）

主键索引是唯一索引的特定类型。表中创建主键时自动创建的索引，一个表只能建立一个主索引。

3. 聚集索引（聚簇索引）/非聚集索引（非聚簇索引）

聚集索引：表中记录的物理索引和键值的索引顺序相同。一个表只能有一个聚集索引。

聚集索引和非聚集索引的根本区别是：表中记录的物理顺序和索引的排列顺序是否一致。

	聚集索引	非聚集索引
	聚集索引和非聚集索引均采用B+ 树的结构
表中记录的物理顺序和索引的排列顺序	一致	不一致
特点	查询速度快、对表的修改速度慢	查询慢，对表的修改快
使用场合	某列包含了小数目的不同值、排序和范围查找	此列包含了大数目的不同值、频繁更新的列

4. 组合索引（联合索引）

基于多个字段而创建的索引称为组合索引

create index index1 on table1(col1, col2, col3)

详情见：MySql的单列索引和组合索引

四、数据库索引的原理（实现）

目前，大部分数据库系统及文件系统都采用B-树(B树)或其变种B+树作为索引结构，B+树是数据库系统实现索引的首选数据结构。

存储引擎：MySql中的数据用不同的技术存储在文件中（或内存），技术使用不同的存储机制、索引技巧、锁定水平，并且最终提供广泛的、不同的功能和能力，这些不同的功能以及配套的相关功能在MySql中称为存储引擎。

在MySql中，索引属于存储引擎级别的概念，不用存储引擎对索引的实现方式是不同的，这里讨论MyISAM和InnoDA两个存储引擎的索引实现方式。

1. MyISAM

1.1 主索引

使用B+树作为索引结构，叶节点的data域存放的是数据记录的地址。非聚集索引。MyISAM存储原理图如下：

 

1.2 辅助索引

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

        辅助索引，同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

2. InnoDB

2.1 主索引

InnoDB的数据文件本身就是索引文件。叶节点的data域保存了完整的数据记录，聚集索引。这个索引的key是数据表的主键，因此，InnoDB表数据文件本身就是主索引。InnoDB存储原理图如下：

1. InnoDB要求表必须有主键（MyISAM可以没有），如果没有显示指定，则MySql系统会自动选择一个可以唯一表示数据记录的列作为主键，如果不存在这种键，MySql自动为InnoDB表生成一个隐式字段作为主键，类型为长整形，同时尽量2在InnoDb上采用自增字段作为主键。因为InnoDB数据文件本身是一棵B+树，非单调的主键会造成插入新数据时，数据文件为了维持B+树的特性而频繁的分裂调整，十分低效，而使用自增字段是一个很好的选择。如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点后续位置，当一页写满后，会自动开辟一个新的页。

 

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时不需要移动已有的数据，因此效率很高，也不会增加很多开销在维护索引上。

2.1 辅助索引

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。也即，InnoDB的所有辅助索引都引用主键作为data域。定义在col3上的辅助索引。

聚集索引这种实现方式使得按照主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引。首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

3. InnoDB和MyISAM的区别与联系

	InnoDB	MyISAM
	B+树
索引	聚集索引-叶节点data域存放完整的数据记录	非聚集索引- 叶节点的data域存储数记录的地址
辅助索引	辅助索引的data域存储相应记录的主键的值	辅助索引的data域存储相应记录的地址
事务	支持事务	不支持事务
外键	支持外键	不支持外键
锁	表级锁和行级锁，默认行级锁	只支持表级锁
是否允许没有主键	若没有主键，会自动生成一个6字节的主键（用户不可见）	允许没有主键
表的行数	select count(*) from table,MyISAM只要简单的读出保存好的行数，注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的	InnoDB 中不 保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行
使用场景	用于事务处理应用程序，支持外键，对事务的完整性/并发性要求高，有很多更新和删除操作	应用程序以读操作和插入操作为主，更新和删除较少，对事务的完整性和并发性要求不高