mysql数据库索引知识一网打尽

问题：

1数据库中索引的原理，种类，使用索引的好处和问题是什么？

2.解释mysql索引、b树，为啥不用平衡二叉树、红黑树(磁盘和内存的存储方式不同)--滴滴

3.Hash索引和B+树索引的区别？为什么不用Hash索引

4.索引场景的实现方式有哪些，有哪些区别？

5.MySQL的存储引擎有哪些，有哪些区别？

6.Innodb使用的是什么方式实现索引，怎么实现的？

7.聚簇索引和非聚簇索引的区别？

8.为什么用B+树而不用B树？

9.主键索引和非主键索引的区别？

 

问题1:数据库中索引的原理，种类，使用索引的好处和问题是什么？

1.索引主要分为Hash索引和BTree索引，

2.使用索引能够加快查找速度，但是会从时间和空间两个方面，降低性能

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 

说说Mysql索引，看到一个很少比如：索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有500也是目录，它当然效率低，目录是要占纸张的,而索引是要占磁盘空间的。
Mysql索引主要有两种结构：B+树和hash.

hash:hsah索引在mysql比较少用,他以把数据的索引以hash形式组织起来,因此当查找某一条记录的时候,速度非常快.当时因为是hash结构,每个键只对应一个值,而且是散列的方式分布，所以他并不支持范围查找和排序等功能.

B+树:b+tree是mysql使用最频繁的一个索引数据结构,数据结构以平衡树的形式来组织,因为是树型结构,所以更适合用来处理排序,范围查找等功能.相对hash索引,B+树在查找单条记录的速度虽然比不上hash索引,

但是因为更适合排序等操作,所以他更受用户的欢迎.毕竟不可能只对数据库进行单条记录的操作. 
 

使用索引的好处和坏处：

Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

PRIMARY KEY（主键索引）  ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) UNIQUE(唯一索引)     ALTER TABLE `table_name` ADD UNIQUE (`column`)
INDEX(普通索引)     ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) FULLTEXT(全文索引)      ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
组合索引   ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` ) 

Mysql各种索引区别：
普通索引：最基本的索引，没有任何限制
唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值
主键索引：它是一种特殊的唯一索引，不允许有空值 
全文索引：针对文本文件text起作用

 

 

问题2:解释mysql索引、b树，为啥不用平衡二叉树、红黑树(磁盘和内存的存储方式不同）

AVL 平衡二叉树是一种能够自动保持平衡的，二叉查找树（二叉搜索树）。

红黑树有自动平衡的功能（会通过旋转将），本质也是二叉树

为什么红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构

数据库的数据是存储在磁盘上的，而不是存储在内存重的

原因：

AVL 树和红黑树基本都是存储在内存中才会使用的数据结构，那磁盘中会有什么不同呢？

这就要牵扯到磁盘的存储原理了

操作系统读写磁盘的基本单位是扇区，而文件系统的基本单位是簇(Cluster)。

也就是说，磁盘读写有一个最少内容的限制，即使我们只需要这个簇上的一个字节的内容，我们也要含着泪把一整个簇上的内容读完。

那么，现在问题就来了

一个父节点只有 2 个子节点，并不能填满一个簇上的所有内容啊？那多余的内容岂不是要浪费了？我们怎么才能把浪费的这部分内容利用起来呢？哈哈，答案就是 B+ 树。

由于 B+ 树分支比二叉树更多，所以相同数量的内容，B+ 树的深度更浅，深度代表什么？代表磁盘 io 次数啊！数据库设计的时候 B+ 树有多少个分支都是按照磁盘一个簇上最多能放多少节点设计的啊！

所以，涉及到磁盘上查询的数据结构，一般都用 B+ 树啦。

一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

 

 

问题3:Hash索引和B+索引的区别？为什么不用Hash索引？

hash:hsah索引在mysql比较少用,他以把数据的索引以hash形式组织起来,

 1.因此当查找某一条记录的时候,速度非常快.

 2.当时因为是hash结构,每个键只对应一个值,而且是散列的方式分布.所以他并不支持范围查找和排序等功能.

(1)hash表只能匹配是否相等，不能实现范围查找

select * from xx where id > 23; 这时就没办法索引了

(2)当需要按照索引进行order by时，hash值没办法支持排序

select * from xx order by score desc;如果score为建立索引的字段，hash值没办法辅助排序。

3.当数据量很大时，hash冲突的概率也会非常大

 

 

1.如果采取二叉树做为索引可以把查询的次数控制在log2N

 

2.采用Hash索引的原理：

 通过hash算法将值转化成对应的hash值，通过hash值去找对应的内存地址

例如：

     中-国-人-民

要查找这四个字，首先通过hash算法找出每个字对应的Hash值

比如说：

   中-->080

   国-->777

   人-->568

   民-->518

然后申请一大段的内存空间

[000][001].........[999]

然后根据对应的汉字的后面算出的hash值，找到内存空间中的位置

 

缺点：

  1.算出来的散列值（hash值）不连续

  2.碰撞

 

使用索引的好处和坏处：

散列非常浪费内存空间

3.Mysql中实际采用BTree和Hash两种方式来实现索引

5.MySQL的存储引擎有哪些，有哪些区别？

MySQL默认使用的是表级锁

区别：

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务； 

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败； 

3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

4. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

       InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

 

5.1MyISAM引擎

1.不支持事务

2.支持表级锁（MySql支持两种表级锁，表共享读锁和表独占写锁），但不支持行级锁

3.存储表的总行数

4.一个MyISAM表有三个文件：索引文件（.MYI），表结构文件(.frm)，数据文件(.MYD)

5.采用非聚集索引：即索引文件和数据文件是分开的，索引文件的数据域存储指向数据文件的指针

 

5.2InnoDb引擎

1.支持事务

2.支持行级锁（仅在条件语句中包括主键索引时）

3.内存使用率低

4.查询效率和写的效率更低

5.采用聚集索引，索引和数据存在一起，叶子结点直接存的是数据。

6.支持外键

注：MyISAM在查询时的性能比InnoDB高，因为它采用的辅索引和主键索引类似，所以通过辅索引查找数据时只需要通过辅索引树就可以查找到，而InnoDB需要先通过辅索引查找到主索引，再通过主索引树查找到数据。

 

 

 

 

7.说下聚簇索引和非聚簇索引的区别？

MYISM: 非聚集索引，索引和存储的数据不在一起，要先找到索引再经过一次磁盘IO，才能拿到数据。即索引和数据是分开存储的

 

InnoDB：聚集索引，索引和存储的数据在一起，找到了索引也就找到了数据，这样效率高耗时少，减少了一次磁盘IO。即索引和数据是存储在一起

 

 

8.为什么使用B树而不是使用B+树？

都是多叉树，对比二叉树可以降低树的高度

8.1 B树（B-树）

 

 

 

对上图的说明:
1) B 树的阶:节点的最多子节点个数。比如 2-3 树的阶是 3，2-3-4 树的阶是 4

2)  B-树的搜索，从根结点开始，对结点内的关键字(有序)序列进行二分查找，如果命中则结束，否则进入查询  关键字所属范围的儿子结点;重复，直到所对应的儿子指针为空，或已经是叶子结点

3)  关键字集合分布在整颗树中, 即叶子节点和非叶子节点都存放数据.

4)  搜索有可能在非叶子结点结束

5)  其搜索性能等价于在关键字全集内做一次二分查找

8.2 B+ 树

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

 

 

对上图的说明:

1)  B+树的搜索与 B 树也基本相同，区别是 B+树只有达到叶子结点才命中(B 树可以在非叶子结点命中)，其性 能也等价于在关键字全集做一次二分查找

2)  所有关键字都出现在叶子结点的链表中(即数据只能在叶子节点【也叫稠密索引】)，且链表中的关键字(数据)恰好是有序的。

3)  不可能在非叶子结点命中

4)  非叶子结点相当于是叶子结点的索引(稀疏索引)，叶子结点相当于是存储(关键字)数据的数据层

5) 更适合文件索引系统

B+树的优势：

B+树比B-树的搜索效率更高，

1.B+树的所有数据都在叶子节点上，叶子节点是一个连续的链表，便于范围查询。。b+树的数据都集中在叶子节点。分支节点 只负责索引。  b树的分支节点也有数据 。 b+树的层高 会小于 B树 平均的Io次数会远大于 B+树

2.B+树单一节点存储更多的元素，使得查询的IO次数更少。

3.所有查询都要查找到叶子节点，查询性能稳定。

 

9.主键索引和非主键索引的区别

主键索引和非主键索引的示意图如下：

 

 

其中R代表一整行的值。

从图中不难看出，主键索引和非主键索引的区别是：非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。

根据这两种结构我们来进行下查询，看看他们在查询上有什么区别。

1、如果查询语句是 select * from table where ID = 100,即主键查询的方式，则只需要搜索 ID 这棵 B+树。

2、如果查询语句是 select * from table where k = 1，即非主键的查询方式，则先搜索k索引树，得到ID=100,再到ID索引树搜索一次，这个过程也被称为回表。

现在，知道他们的区别了吧？

 为什么建议使用主键自增的索引？

对于这颗主键索引的树

 

如果我们插入 ID = 650 的一行数据，那么直接在最右边插入就可以了

 

但是如果插入的是 ID = 350 的一行数据，由于 B+ 树是有序的，那么需要将下面的叶子节点进行移动，腾出位置来插入 ID = 350 的数据，这样就会比较消耗时间，如果刚好 R4 所在的数据页已经满了，需要进行页分裂操作，这样会更加糟糕。

但是，如果我们的主键是自增的，每次插入的 ID 都会比前面的大，那么我们每次只需要在后面插入就行， 不需要移动位置、分裂等操作，这样可以提高性能。也就是为什么建议使用主键自增的索引。