MySQL-索引(1)

当我们使用自增主键的时候 : 每次插入数据都会被顺序添加到索引中,每次插入一条新记录都是追加操作,当页面满的时候,就会自动开辟一个新的页面继续插入,不需要重新移动数据. 插入效率非常高
当我们使用非自增主键(比如UUID,随机ID...) : 由于插入的id没有规律,所以InnoDB就无法做到总是把新数据插入到索引的最后,就需要为新数据寻找合适的位置从而来分配新的空间.会导致以下问题 :
- 写入的目标页(新纪录要写入的页)很可能已经刷新到磁盘上并且从缓存中移除或者还没有被加载到缓存中, InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中,这将导致大量的随机IO
- 因为写入是乱序的,InnoDB不得不频繁的做页分裂操作,以便为新的数据分配空间,页分裂导致移动大量的数据 -- > 可能导致造成大量的内存碎片,导致索引结构不紧凑,从而影响查询效率
  - 页分裂 : 比如要将记录插入到数据页中间的某个位置,就必须移动其他新的数据来满足新的数据插入,甚至可能需要把数据从一个页面复制到另外的一个页面 ---> 可能导致造成大量的内存碎片,导致索引结构不紧凑,从而影响查询效率

所以 : 使用innodb应该尽可能的按主键的自增顺序插入，并且尽可能使用单调的增加的聚簇键的值来插入新行

mysql自增主键用完了怎么办？

索引

什么是索引?

索引就好比我们书中的目录一样,当我们查找某个内容的时候,先会去目录中找到对应的章节,然后再到对应章节中往后找我要查找的内容.这样就极大的提高了我们找资料的效率

书中的目录就对应着数据库(存储的数据)的索引(查找数据的目录).

索引就是一种帮助存储引擎快速查询的一种数据结构.换句话说索引就是数据的目录

什么是存储引擎呢 ?

存储引擎就是如何存储数据,如何为数据创建索引,如何查询和更新数据等技术的实现方法.

MySQL 支持哪些存储引擎？如何查看？默认使用哪个？

MySQL支持多种存储引擎 ,可以使用 show engines 命令来查看 MySQL 支持的所有存储引擎。

InnoDB存储引擎支持事务,外键,行级锁 , 此外所有存储引擎中只有InnoDB存储引擎是支持事务的.

MySQL 5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

可以通过 show variables like '%storage_engine%' 命令直接查看 MySQL 当前默认的存储引擎

如果你只想查看数据库中某个表使用的存储引擎的话，可以使用 show table status from db_name where name='table_name'命令。

存储引擎基于数据库还是表？

存储引擎是MySQL体系结构的核心。

存储引擎是基于表的，而不是数据库。

MySQL 存储引擎架构了解吗？

MySQL 存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

MyISAM 和 InnoDB 的区别是什么？

MySQL 5.5版本之前使用MyISAM作为默认从存储引擎,MySQL 5.5版本之后默认采用的是InnoDB作为存储引擎.

是否支持行级锁

MyISAM不支持行级锁,只支持表锁,所以一锁就锁的是整张表

InnoDB支持表锁和更加细粒度的行级锁,默认采用的是行级锁

是否支持事务

MyISAM不支持事务

InnoDB支持事务,具有提交和回滚事务的能力,并提供了四种隔离级别(读未提交,读已提交,可重复读,串行化) ,默认的隔离级别是REPEATABLE-READ（可重复读）隔离级别.并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）

是否支持外键

MyISAM不支持外键

InnoDB支持外键

是否支持数据库异常崩溃后的安全恢复

MyISAM不支持数据库异常崩溃后的安全恢复

InnoDB支持数据库异常崩溃后的安全恢复,对于InnoDB来说数据库崩溃之后,数据库重新启动之后会恢复回崩溃之前的状态-->redo log

是否支持MVCC

MyISAM不支持MVCC

InnoDB支持MVCC

索引的实现不一样

InnoDB和MyISAM索引的底层数据结构都是B+树,但是InnoDB是数据即索引,索引即数据,索引和数据不分离的对于MyISAM存储引擎数据和索引是分离的,分为两个文件数据文件,索引文件,MyISAM 中建立的索引相当于全部都是二级索引(数据文件-->通过行号快速访问一条记录,索引文件->叶子结点存放的是主键值和行号 ,所以先去叶子结点查到对应的行号在回表查询数据文件,通过行号定位记录).

MyISAM 和 InnoDB 如何选择？

大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点.

一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择 MyISAM 也是一个不错的选择。但是一般情况下，我们都是需要考虑到这些问题的。

Memory存储引擎

将表的数据存放在内存中,如果数据库重启或崩溃,表中数据都将消失.适用于存储临时数据的临时表,以及数据仓库中的维度表.默认使用哈希索引,而不是B+树索引.
速度快,但有限制.如只支持表锁，并发性能差，不支持TEXT和BLOB列类型。存储变长字段（varchar）时是按照定常字段（char）的方式进行的，因此会浪费内存。
MySQL数据库使用Memory存储引擎作为临时表来存放查询的中间结果集。如果中间结果集大于Memory存储引擎表的容量设置，或中间结果含有TEXT/BLOB列类型字段，则MySQL数据库会把其转换到MyISAM存储引擎表而存放到磁盘中。MyISAM不缓存数据文件，因此产生的临时表的性能对于查询会有损失。

索引的分类

所以主要分为4大类 : 按照数据结构分类,按照字段个数分类,按照物理存储分类,按照字段特性分类

按照数据结构分类 : B树索引,B+树索引,Hash索引,FullText(全文索引)

按照字段特性分类 : 主键索引,唯一索引,普通索引,前缀索引.

按照物理存储分类 : 主键索引(聚簇索引), 二级索引(非聚簇索引)

按照字段个数分类 : 单列索引, 联合索引.

创建索引的三种方式

在创建表的时候创建主键索引 :

create table 表名(col1,col2.... ,primary key(...) USEING BTREE);

使用SQL语句创建主键索引

创建表的时候创建唯一索引

create table <表名>(col1,col2,col3..., UNIQUE KEY(col1,col2...));

使用SQL语句创建唯一索引

create UNIQUE INDEX <索引名字> on <表名>(col1,col2....);

在创建表的时候创建普通索引 :

create table <表名>(col1,col2,col3..... ,index(col1,col2....));

使用SQL语句创建普通索引 :

create index <索引名> on <表名>(col1,col2...);

在创建表的时候创建前缀索引 :

create table <表名>(col1,col2,col3.... , index(col1(length)) );

使用SQL语句创建前缀索引 :

create index <索引名> on <表名>(col(length));

索引底层数据结构

MySQL 5.5之后默认的存储引擎是InnoDB,InnoDB存储引擎支持B+Tree索引,5.6版本之后也支持Full_text索引(全文索引); B+Tree索引是MySQL使用最多的索引数据结构.

MySQL 在创建表的时候会帮助我们选择索引

如果表中有主键,那就选择有主键的列为索引键
如果表中没有主键,那就选择,不为null值的唯一列,作为索引键.
如果都没有,InnoDB就会自动生成一个row_id(隐式自增id) 作为索引键.

把主键作为索引键,叫作主键索引,也叫作聚簇索引.非主键作为你索引键,叫做二级索引,也叫作非聚簇索引.

主键索引和二级索引底层数据结构都是B+Tree,但是有些许区别.

我们先来看看主键索引的B+Tree长什么样子

简单找了一张图,如上图就是InnoDB中的一颗B+Tree,是一颗多叉树,每一个节点都是一个数据页(大小是16KB),非叶子结点只存放索引,每一层父节点都会出现在底层子节点上,所有的用户记录都存放在底层叶子结点中,底层叶子结点都是按照主键排好序的形成一个双向链表.

再来讲一讲记录存在哪里(数据页的结构) ?

每一个节点就是一个数据页,数据页存放多条记录,对于叶子结点存放的就是完整的用户记录,为了查找方便,会把多条记录分成一组,这样数据页中就有多组,然后把每一组的最后一条记录的偏移量取出来,也叫作槽,放到页目录中,并且每一条记录都是使用单向链表连接起来的.这样在一个数据页中查找某条记录的时候,先去查找对应的页目录,使用二分法找到记录所在的槽,然后遍历槽中的记录寻找,最终就可以找到目标记录.这样就可以快速查找某条记录

主键索引的B+Tree 是如何查询的呢 ?

由于B+Tree是一颗多叉树,每一个节点都是一个数据页,非叶子结点存放的索引-->目录项记录便于查找目标记录所在的页,叶子结点存放的是所有的用户记录.

查找步骤如下 :

先会从根节点开始,使用二分法定位到符合包含目标记录所在的目录项记录的页
在通过二分法在目录项记录的页找到目标记录所在的页(目标记录与目录项记录比较)
定位到目标记录的页,也就是到达了叶子结点,先去页目录中使用二分法定位到记录所在的槽,找到槽之后在遍历槽中所有的记录,就找到目标记录.如果是范围查询沿着单向链表往后遍历,一直到不符合条件位为止.

二级索引的B+Tree 是如何查询的呢 ?

先说一下主键索引和二级索引的区别 :

主键索引 : B+Tree叶子结点存放的是所有用户记录
二级索引 : B+Tree叶子结点存放的是索引列 + 主键值(索引列相同按照主键排序)而不是所有的用户记录

所以主键索引和二级索引在查询的时候,就有所区别 :

如果使用的是二级索引查询,先会在二级索引的B+Tree查询,找到对应的叶子结点,然后获取叶子结点的主键值,根据主键值去主键索引的B+Tree查询,查到叶子结点得到所有的用户记录(整行数据)---->这个过程叫做回表,也就是说使用二级索引查询的时候,是需要查询两颗B+Tree的.

那有没有一种情况使用二级索引只查询一颗B+Tree呢 ?

当然有,那就是索引优化的一种,也就是覆盖索引,他说的是,如果你查询二级索引的B+Tree,叶子结点就包含了查询列表中的列,那么就不需要回表,只查询一颗B+Tree就可以了--->覆盖索引

主键索引和二级索引的区别什么 ? 都是怎么进行查询的? 查询的时候有什么区别?

主键索引和二级索引都是一颗B+Tree,区别是主键索引的叶子结点存放的是所有的用户记录,而二级索引存放的是索引列 + 主键值.

所有的B+Tree查询都是如下步骤 :

先从根节点开始,使用二分法找到包含目标记录的目录项页
再根据目录项页,使用二分法定位到目标记录具体所在的数据页
先到数据也中的页目录中使用二分法定位到目标记录所在的槽,遍历槽中的记录找到目标记录

只不过主键索引找到叶子结点就能找到所有的用户记录.

对于二级索引来说,找到叶子结点,获取到主键值之后再去主键索引的B+Tree查找-->这一操作称为回表,也就是说二级索引在查询的时候比主键索引多了一步回表操作. 当然也有优化,如果查询列表中的列都能够在二级索引的B+Tree找到的话,就不需要回表操作-->称为覆盖索引.

为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构？

我们期待使用什么样的数据结构作为数据库的索引呢 ?

我们知道数据都是存储到磁盘上的,这样数据才能持久化,但是内存和磁盘的速度差异有很大,同样是读取数据,磁盘要比内存慢上上万倍,甚至几十万倍.

当我们查询数据的时候,就需要先将索引加载到内存,再根据索引查找到某行数据,再把数据加载到内存中,也就是说查询的过程中,需要发生多次磁盘I/O操作,磁盘I/O次数越多,耗费的时间就会越久.

所以,选择的数据库索引结构,要尽量将磁盘I/O次数减少到最低,这样才能高效的查询数据.

除此以外,数据库不仅要支持高效的查找一条记录,还要支持高效的范围查询多条记录,

综上 : 我们要选择的索引数据结构包含以下几点 :

查询的时候,尽量将磁盘I/O次数降到最低.完成查询
选择的索引结构.要支持高效的范围查询多条记录

和线性结构相比较

线性结构也就是使用数组,由于数组是一段连续的内存空间,如果要查询某一个元素,时间复杂度就是O(N),但是可以使用二分查找,时间复杂度为O(logN).

比如你要查找[ID.card.x ,ID.card.y] 之间的元素,那么可以使用二分查找找到ID.card.x(如果没有ID.card.x,那么就选择第一个大于ID.card.x的id.card),然后向后遍历,知道Id.card大于Id.card.y.

但是不能使用线性结构来作为索引结构 :

查询的时候,使用二分查找每次都要计算中间位置
使用线性结构插入元素的时候,需要频繁的移动元素,耗费性能.

线性结构适用于静态存储引擎,比如你要保存某一年的人口信息,由于这一年的人口信息都确定好了,不在需要更改.

和哈希表相比较

哈希表就是用来保存键值对的,知道key,然后通过和哈希函数得到哈希值,然后在与数组长度-1按位与,得到数组下标,把这个元素存放到下标位置即可. 根据key很快就能找到对应的value

当然哈希表也会出现问题就是当多个key通过哈希函数计算出的数组下标是一样的,这样就会导致哈希冲突问题,我们一般采用的是链表法,将冲突的元素,使用链表串起来

比如现在要根据身份证号获取到名字,我们使用哈希表当做索引就可以这么做,先根据身份证号计算出数组下标,然后把对应的用户信息放到数组下标上,冲突的元素,使用链表连起来即可.

查找的时候,先根据身份证号计算出数组下标,然后遍历哈希桶的链表,找到用户信息.

但是哈希表不适合用于索引

当我们进行范围查询的时候,由于查询id值不是有序的,我们只能遍历哈希表所有的元素找到对应的数据.

所以哈希表作为索引,只适用于等值查询的场景.

和二叉搜索树相比较

先说一下二叉搜索树的特点 : 每一个节点都比自己左树节点值大,比自己右树节点值小.

所以二叉搜索树是天然的二分结构,-->后序遍历元素就是有序的,二叉搜索树解决了线性结构插入元素开销很大的问题.

但是二叉搜索树也不能作为索引数据结构 :

二叉搜索树极端情况下,会变成一颗斜树,时间复杂度从O(logN)变为O(N).
随着二叉搜索树的节点数越来越多,树的高度也越来越高,我们又知道每访问一个节点就相当于一次磁盘I/O, 所以树的高度决定访问磁盘I/O的次数.树的高度越高,磁盘I/O数越多,导致查询时间就变得慢.

和AVL,红黑树相比较

AVL树也就是高度平衡的二叉树,以及红黑树,都是在二叉搜索树的基础上增加了一些约束条件,使得树的高度平衡,不让其变成一颗斜树,-->时间复杂度为O(logN)

但是AVL,红黑树依然不当作为索引的数据结构

因为不管是AVL树,还是红黑树,都是一颗二叉树,随着元素越来越多,插入的节点就越多,树的高度就变得越来越高,树的高度就决定了磁盘的I/O次数,高度越高,磁盘I/O次数越多,查询越慢.

所以像这种二叉树,无论怎么加约束条件,随着元素越来越多,都会使得树的高度越来越高,因为索引不止存在内存中，还要写到磁盘上 , 就会导致磁盘I/O次数越来越多,查询效率就变得越慢. 所以我们就要使用多叉树.

和B树相比较

B树的特点

1) : M阶的B树节点有M-1个数据,M个分叉 , 每一个节点存放的是索引和记录

2) 中序遍历,元素是有序

B树的缺点 :

1) : 由于存放的是索引+记录,很有可能导致记录的大小远远超过索引的大小,导致更多的磁盘I/O在查找的时候 , 会读取很多无效的数据(比如我们要找某一个记录的过程中,会读取对我们没用的记录到内存中,我们只想通过读取这些节点的索引比较定位到目标节点,所以就增加额外的磁盘I/O次数

2)范围查询,由于要中序遍历(中序遍历元素有序),会涉及到多个节点的磁盘I/O问题,导致整体速度下降

B+树

先说一下B+树的特点 :

B+树的非叶子结点只存放索引,叶子结点存放所有的用户记录,并且父节点都会出现在下层子节点中,并且是子节点中最小/最大的节点,所以B+树有很多冗余的节点
B+树的叶子结点用一个有序链表连接起来,方便范围查询
B+树非叶子结点中有几个节点就有几个索引

InnoDB中的B+树

数据库读写磁盘是按照行读取的么 ?

由于数据存储在磁盘上的,我们的记录虽然是以行为单位的,但是从数据库读取数据到磁盘是以数据页为单位的,一个页的大小是16KB,否则IO次数会非常多,影响性能,所以,当你要从磁盘中读取一条记录到内存,其实是需要将一整个数据也读取到内存的.

综上 : 数据库是以数据页为单位(IO操作的基本单位是数据页),一个数据页大小是16KB,一次最少从磁盘中读取16KB的内容到内存; 一次最少从内存刷新16KB的内容到磁盘.

再来讲一讲数据页都包含什么 ?

数据页中存放的就是我们一条条的用户记录,但是也有这个数据页的基本信息比如 : fileHead,filetailer......

这里重点说一下我们关心的用户记录, 由于用户记录会非常的多,为了便于查找会有一个页目录的东西,将每一个数据页的多个记录进行分组,把每组最大的记录的偏移量-->也叫作槽放到页目录中,数据页中的记录会形成一个单向链表.这样我们到达数据页先去对页目录中的槽使用二分法,找到对应的槽,然后在去遍历槽中的记录就能找到我们的目标记录了.

在来讲一讲InnoDB中的B+树

InnoDB中的B+树的节点都是一个数据页,叶子结点的数据页存放所有的用户记录(索引+记录),非叶子结点也叫作内节点->存储目录项的记录,只存放索引,每一个非叶子结点的目录项记录都会在下层子节点中并且是子节点中最小的. 叶子结点中的用户记录形成有序的单向链表,叶子结点与叶子结点之间使用双向链表连接,这样方便范围查询

这样我们使用B+树进行查找是如下几个步骤

先从根节点开始,使用二分法让目标记录与目录项的记录比较,找到包含目标记录的目录项记录
根据目录项记录,使用二分法找到目标记录所在的页
找到目标记录所在的数据页之后,先到页目录中对槽进行二分查找,找到目标记录所在的槽,然后沿着单向链表遍历槽中的记录,就能够找到目标记录

所以对于N叉树来说,它在读写上的性能优点以及适配磁盘的访问模式,已经被广泛应用在数据库引擎中了.

因为读写磁盘的速度是非常慢的,我们要想让一个查询尽量少的都写磁盘,就必须让查询的过程中访问尽量少的数据块,我们就需要使用N叉树,

以InnoDB的一个整数字段索引为例，对于N叉树(B+) ,这个N差不多是1200。这棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了。考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

B树和B+树的区别，数据库为什么使用B+树而不是B树？

基本区别 :

B树的节点既要存放记录,也要存放索引. 而B+树的非叶子结点既存放索引,也存放记录,非叶子结点的记录都会存在下层子节点中并且是子节点中最小的,叶子结点存放索引的用户记录(索引+记录),所以相比于B树来说B+树会有很多冗余节点,不想B树那样既存放记录也存放索引,存放的索引就会很少,索引少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低,B+树就可以存放更多的索引值,所以底层就能够存放更多的记录(叶子结点存放所有记录),B+树也会更矮一些.

性能上的区别 :

单点查询: B树单点查询波动比较大,因为B树的节点既存放记录也存放索引,这样有可能还没有遍历到最底层就找到了,对于B+树来说,单点查询很稳定,因为B+树的所有记录都在叶子结点,所以查询的时候都要搜到叶子结点. 但是B+树不想B树那样既要存放记录,也要存放索引,B+树的非叶子结点只存放索引,相同数据量的情况下,B+树能够存放更多的索引,存放的记录也会更多一些,B+树也就更加矮胖,查询时磁盘IO次数会更少
插入和删除效率 :由于B树节点既存放索引也存放记录,导致插入和删除会发生很大的树结构变化, 比如删除根节点,甚至导致所有节点都要动一下,而B+树的所有记录都在叶子结点,插入和删除只涉及到B+树的一条路径,不会导致树的结构变化很大,并且自动平衡,所以B+树的效率更高
范围查询 : B树的做范围查询的时候没有链表连接,就需要对B树做遍历,这样需要访问多个节点,涉及到多个节点的操作,也可能会有很多随机IO,但是对于B+树而言,叶子结点与叶子结点之间是由双向链表连接器起来的,并且数据页里面的记录是形成按主键顺序排列好的用单链表串起来的, 是顺序IO,对范围查询非常有帮助,查询效率更高一些.

Hash索引和B+树区别是什么？你在设计索引是怎么抉择的？

哈希索引（hash index）基于哈希表实现,只需一次哈希算法即可立刻定位到相应的位置，速度非常快。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。对于hash相同的，采用链表的方式解决冲突。类似于hashmap。因为索引的结构是十分紧凑的，所以hash索引的查询很快。

Hash索引不支持范围查询,Hash索引的索引列值是无序的,而B+树是可以做范围查询的,叶子结点形成一个有序的链表
Hash索引不支持部分索引列的匹配查找,因为哈希索引始终是使用索引列的全部值作为哈希值的,无法使用联合索引,而对于B+树它是支持联合索引的最左前缀法则的
Hash索引是不支持orderby排序的,因为Hash索引指向的都是无序的数据,B+树是可以通过orderby语句优化的,因为B+树底层叶子结点本身就是按照主键排好序的 ,同理Hash索引不支持模糊匹配 ,B+树支持
Hash索引在等值查询上比B+树效率更高,通过哈希值可以快速定位到某一条记录
对于Hash索引来说还容易发生哈希冲突,它是与hashmap类似使用拉链法解决哈希冲突.当出现哈希冲突的时候,存储引擎必须遍历链表中所有的行指针,逐行进行比较,直到找到所有符合条件的行.维护操作的代价也会很高。例如，如果在某个哈希冲突较多的列建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。