Mysql索引的一些理解

た入场券 2022-10-26 12:58 179阅读 0赞

# 什么是索引？ #

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。

# 索引的作用？ #

（1）快速取数据；

（2）保证数据记录的唯一性；

（3）实现表与表之间的参照完整性；

（4）在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

# 索引有哪些类型？ #

**1、普通索引**

即不应用任何限制条件的索引，该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。

**2、唯一索引**

使用UNIQUE参数可以设置唯一索引。创建该索引时，索引的值必须唯一。主键是一种特殊唯一索引。

**3、全文索引**

使用FULLTEXT参数可以设置全文索引。全文索引只能创建在CHAR、VARCHAR、TEXT类型的字段上。查询数据量较大的字符串类型字段时，使用全文索引可以提高查询速度。注意：全文索引在默认情况下是对大小写字母不敏感的，可以通过使用二进制对索引的列进行排序以执行大小写敏感的全文索引。

**4、组合索引**

组合索引是在表的多个字段上创建一个索引。该索引只想创建时对应的多个字段，可以通过这几个字段进行查询。要想应用该索引，用户必须使用这些字段中的第一个字段。

mysql索引跟组合索引的区别，组合索引如果有A、B、C where条件后面用了B跟C或者A跟B或者A跟C，会命中索引吗？

组合索引（a,b,c）相当于建立了（a）,（a，b），（a，b，c）三个索引。

场景：

where  条件A   and  条件B  and  条件C        三个索引都命中

where  条件B   and  条件A  and  条件C        三个索引都命中（mysql会自动优化，效果同上）

where  条件A   and  条件B        A，B索引都命中

where  条件A   and  条件C        A索引命中

where  条件B  and  条件C        B，C索引都不命中

where a=3 order by b;    都命中索引

where a=3 order by c;    a命中索引

where a=3 and b>7 and c=3;(范围值就算是断点)

总结：必须有最前面的索引（A索引），如果断点了后面的索引会失效

** 5、覆盖索引（扩展知识）**

在了解覆盖索引之前我们先大概了解一下什么是聚集索引(主键索引)和辅助索引(二级索引)

聚集索引（主键索引）：

聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据。

聚集索引的叶子节点称为数据页，聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

辅助索引（二级索引）：

非主键索引，叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值。

**   再来看看什么是覆盖索引，有下面三种理解：**

*  解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取（不用回表），换句话说查询列要被所使用的索引覆盖。
 *  解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
 *  解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句\[select子句\]与查询条件\[Where子句\]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引

**优点：**

*  索引项通常比记录要小，所以MySQL访问更少的数据。
 *   索引都按值得大小存储，相对于随机访问记录，需要更少的I/O。
 *   数据引擎能更好的缓存索引，比如MyISAM只缓存索引。
 *   覆盖索引对InnoDB尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引包含查询所需的数据，就不再需要在聚集索引中查找了。

** 限制：**

*   覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。
 *   Hash和full-text索引不存储值，因此MySQL只能使用BTree。
 *   不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持覆盖索引。
 *   如果要使用覆盖索引，一定要注意SELECT列表值取出需要的列，不可以SELECT \* ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

# 索引方法？ #

**HASH**

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

**BTREE（最常用的）**

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

# B+tree 跟B-tree的区别？ #

**先上图直观看一下**

B-tree：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMDM3NDc4_size_16_color_FFFFFF_t_70][]

可以看到，B+树与B树最大的区别就是每一个叶子结点都包含指向下一个叶子结点的指针，并且叶子结点的指针指向的是被索引的数据，而非其他的节点页，非叶节点仅具有索引作用，跟数据有关的信息均存储在叶节点中，查找时存储引擎通过根节点一层层的进行二分搜索即可。由于B+树在内部节点上不包含数据信息(每个非叶子节点存放更多的key键值，大大的降低树的高度)，所以它占用空间更小；叶子节点之间形成链表，从而方便了叶子结点的遍历与范围查找。

### 总结 ###

**B-tree**

每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null，叶子结点不包含任何关键字信息

**B+tree**

1、所有的非叶子节点只存储关键字信息。不存储具体数据，这意味着一个节点可以存放更多的key键。  
2、所有卫星数据（具体数据）都存在叶子结点中。  
3、所有的叶子结点中包含了全部元素的信息。  
4、所有叶子节点之间都有一个链指针。

5、降低树的高度，树高度越小，I/O次数越少。效率越高。

我们发现，b+tree有以下特性：

*  对一个范围内的查询特别有效快速（通过叶子的链指针）；
 *  对具体的key值查询仅仅比b-tree低效一点（因为要到叶子一级），但也可以忽略；

# 为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？ #

**1.B+的磁盘读写代价更低**

B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

**2.B+tree的查询效率更加稳定**

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

# 聚集索引和非聚集索引区别? #

**聚集索引(clustered index)**:

聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。  
聚集索引类似于新华字典中用拼音去查找汉字，拼音检索表于书记顺序都是按照a~z排列的，就像相同的逻辑顺序于物理顺序一样，当你需要查找a，ai两个读音的字，或是想一次寻找多个傻(sha)的同音字时，也许向后翻几页，或紧接着下一行就得到结果了。

**非聚合索引(nonclustered index):**

非聚集索引指定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。  
非聚集索引类似在新华字典上通过偏旁部首来查询汉字，检索表也许是按照横、竖、撇来排列的，但是由于正文中是a~z的拼音顺序，所以就类似于逻辑地址于物理地址的不对应。同时适用的情况就在于分组，大数目的不同值，频繁更新的列中，这些情况即不适合聚集索引。

根本区别：

聚集索引和非聚集索引的**根本区别是表记录的排列顺序和与索引的排列顺序是否一致。**

## 另附带两种存储引擎的区别： ##

1、MyISAM是非事务安全的，而InnoDB是事务安全的

2、MyISAM锁的粒度是表级的，而InnoDB支持行级锁

3、MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM

4、MyISAM表保存成文件形式，跨平台使用更加方便

5、MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量select操作可选择

6、InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，可选择。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMDM3NDc4_size_16_color_FFFFFF_t_70]: /images/20221024/ada44c78d13e4cdb9e8de639b03ce632.png