【MySQL】- 1.mysql索引

淡淡的烟草味﹌ 2022-12-04 09:29 157阅读 0赞

### 文章目录 ###

*   *  1.Mysql架构
     *   *  a. mysql的架构图
         *  b. InnoDB数据页的结构
         *   *  页结构
         *  c. InnoDB行格式
         *   *  COMPACT行格式
             *   *  记录的额外信息
                 *  字节字段长度列表
                 *  null值列表
                 *  记录头信息
                 *  记录真实数据
                 *  行溢出数据
                 *  记录数据太多产生的溢出
             *  Dynamic和Compresses行格式
     *  2.索引
     *   *  a. 聚簇索引
         *  b. 二级索引（复制索引）
         *  c. 联合索引
         *   *  联合索引中的特殊存在--覆盖索引
             *  目录项记录的唯一性
         *  d.以数据结构区分的索引
         *   *  哈希索引
             *  Btree索引--MYISAM与InnoDB索引的区别
             *   *  b+树的结构
         *  e.索引中存在的问题
         *   *  使用索引空间上的代价
             *  时间上的代价
         *  f. 索引的最左匹配原则
         *   *  避免冗余索引
         *  g. 创建索引需要注意的事项
         *   *  考虑索引的选择性
             *  考虑前缀索引
             *  总结
             *  不可以使用索引的情况 ——> ASC，DESC混用
         *  h.索引使用的注意事项

## 1.Mysql架构 ##

### a. mysql的架构图 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5aGR4d2FuZw_size_16_color_FFFFFF_t_70_pic_center]  
其中存储引擎是负责数据的读取和写入，常用的存储引擎有myISAM，innoDB，Momory等，每个存储引擎都有自己的特性，而且其存放数据的格式也不同，比如memory就不用磁盘存储数据。

InnoDB，数据会存储到磁盘上，在真正处理数据的时候会将数据先加载的内存，表读取这些记录时，InnoDB存储引擎不是一条条把数据从磁盘读取，而是将数据划分为若干个页，以页作为磁盘和内存之间数据交换的单位，InnoDB的页大小一般是16k，及使用InnoDB从磁盘读取数据时一次至少要读取16k数据到内存中，每次也会把16k数据写回磁盘。

### b. InnoDB数据页的结构 ###

页是InnoDB管理数据的基本单位，一个页默认大小为16k

#### 页结构 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5aGR4d2FuZw_size_16_color_FFFFFF_t_70_pic_center 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5aGR4d2FuZw_size_16_color_FFFFFF_t_70_pic_center 2]

### c. InnoDB行格式 ###

一行数据可以以不同的格式存储在InnoDB中，行格式分别是Compact，Redundant，Dynamic和Compressed行格式。

我们可以在创建或修改表时指定行格式：

creat table 表名 （列信息） ROW_FORMAT=行格式名称
    
    alter table 表名 ROW_FOEMAT=行格式名称

#### COMPACT行格式 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5aGR4d2FuZw_size_16_color_FFFFFF_t_70_pic_center 3]

##### 记录的额外信息 #####

这部分是服务器为了描述这条记录而不得不添加一些额外信息，这些信息分为三类：变长字段长度列表，NULL值列，记录头信息。

##### 字节字段长度列表 #####

MySQL支持一些变长的数据类型，比如VARCHAR(M)、 VARBINARY(M)、 TEXT类型， BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

> CHAR是一种固定长度的类型， VARCHAR则是一种可变长度的类型。  
> VARCHAR(M)， M代表最大能存多少个字符。 ( MySQL5.0.3以前是字节，以后就是字符)

##### null值列表 #####

ComPact会把null的列统一管理，存放在一个NULL值列表中，如果表中不存在NULL列，那么NULL值列表也就不存在。

*  使用二进制存放，如果对应比特位为1说明该列为null
 *  二进制为0，表示该列不为null

##### 记录头信息 #####

记录头信息由5个字节组成，既是40个二进制位，不同二进制位表示不同意思如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5aGR4d2FuZw_size_16_color_FFFFFF_t_70_pic_center 4]

##### 记录真实数据 #####

记录真实数据除了我们自定义的数据外，还会有三个隐藏列：  
![在这里插入图片描述][20200907234453111.png_pic_center]  
实际上这几个列的真正名称其实是： DB\_ROW\_ID、 DB\_TRX\_ID、 DB\_ROLL\_PTR  
**一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row\_id的隐藏列作为主键**。所以 **row\_id是在没有自定义主键以及Unique键的情况下才会存在的**

##### 行溢出数据 #####

VARCHAR(M)类型的列最多可以占用65535个字节。其中的M代表该类型最多存储的字符数量，如果我们使用ascii字符集的话，一个字符就代表一个字节，我们看看VARCHAR(65535)是否可用：

CREATE TABLE vachar_deom(
    c VARCHAR(65535)
    ) CHARSET=ascii ROW_FORMAT=Compact;
    
    CREATE TABLE vachar_deom(
    c VARCHAR(65535)
    ) CHARSET=ascii ROW_FORMAT=Compact
    > 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
    > 时间: 0s

报错信息表达的意思是： MySQL对一条记录占用的最大存储空间是有限制的，除BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。这个65535个字节除了列本身的数据之外，还包括一些其他的数据，比如说我们为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：

1.  真实数据
2.  变长字段真实数据的长度
3.  NULL值标识

如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为变长字段的长度占用2个字节， NULL值标识需要占用1个字节

CREATE TABLE vachar_deom(
    c VARCHAR(65532)
    ) CHARSET=ascii ROW_FORMAT=Compact;
    
    CREATE TABLE vachar_deom(
    c VARCHAR(65532)
    ) CHARSET=ascii ROW_FORMAT=Compact
    > OK
    > 时间: 0.005s

##### 记录数据太多产生的溢出 #####

一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录。

在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。

#### Dynamic和Compresses行格式 ####

这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，**它们不会在记录的真实数据处存储一部分数据，而是把所有的数据都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。另外，Compressed行格式会采用压缩算法对页面进行压缩**。

## 2.索引 ##

> Mysql中的utf8是3字节和我们在平时代码中使用的不同（平时的utf8为4字节），mysql的utf8不能存表情，如果存储表情使用utf8mb4字符集

### a. 聚簇索引 ###

聚簇索引的特点：

1.  按主键值的大小进行记录和页的排序：
    
     *  数据页(叶子节点)里的记录是按照主键值从小到大排序的一个单向链表。
     *  数据页(叶子节点)之间也是是按照主键值从小到大排序的一个双向链表。
     *  B+树中同一个层的页目录也是按照主键值从小到大排序的一个双向链表。
2.  B+树的叶子节点存储的是完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种**聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建。 InnoDB存储引擎会自动的为我们创建聚簇索引。**

在**InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引** 。

### b. 二级索引（复制索引） ###

聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。当我们想以别的列作为搜索条件时我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。

二级索引与聚簇索引有几处不同：

1.  按指定的索引列的值来进行排序
2.  叶子节点存储的不是完整的用户记录，而只是索引列+主键。
3.  目录项记录中不是主键+页号，变成了索引列+页号。
4.  在对二级索引进行查找数据时，需要根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程叫做回表。

即二级索引最终还是要回到主键索引中查询数据。

### c. 联合索引 ###

以多个列的大小作为排序规则，建立的B+树称为联合索引，本质上还是一个二级索引。

#### 联合索引中的特殊存在–覆盖索引 ####

通过前叙内容，可以知道，如果不是聚簇索引那么子节点存储的是：主键+列值，且最终还要回表去查询数据，这样会比较慢。

**覆盖索引**：就是要查询的列和索引是对应的，就不做回表操作。

例如：  
现在我创建了索引\*(username,age)\*，在查询数据的时候：*select username , age from user where username = ‘Java3y’ and age = 20。*  
很明显地知道，我们上边的查询是走索引的，并且，要查询出的列在叶子节点都存在！所以，就不用回表了~  
**所以，能使用覆盖索引就尽量使用吧**

#### 目录项记录的唯一性 ####

我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

*  索引列的值
 *  主键值
 *  页号

### d.以数据结构区分的索引 ###

MySQL索引使⽤的数据结构主要有BTree索引 和 哈希索引 。

#### 哈希索引 ####

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝⼤多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余⼤部分场景，建议选择BTree索引。

#### Btree索引–MYISAM与InnoDB索引的区别 ####

MySQL的BTree索引使⽤的是B树中的B+Tree，但对于主要的两种存储引擎的实现⽅式是不同的。

*  MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，⾸先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“⾮聚簇索引”。
 *  InnoDB: 其数据⽂件本身就是索引⽂件。相⽐MyISAM，索引⽂件和数据⽂件是分离的，其表数据⽂件本身就是按B+Tree组织的⼀个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据⽂件本身就是主索引。这被称为“聚簇索引（或聚集索引） ”。⽽其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值⽽不是地址，这也是和MyISAM不同的地⽅。 在根据主索引搜索时，直接找到key所在的节点即可取出数据；**在根据辅助索引查找时，则需要先取出主键的值，再⾛⼀遍主索引。 因此，在设计表的时候，不建议使⽤过⻓的字段作为主键，也不建议使⽤⾮单调的字段作为主键，这样会造成主索引频繁分裂。**

##### b+树的结构 #####

索引就是把无序的数据变成了相对有序的数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5aGR4d2FuZw_size_16_color_FFFFFF_t_70_pic_center 5]

### e.索引中存在的问题 ###

#### 使用索引空间上的代价 ####

一个索引对应一个B+数，这就意味着索引需要占用一定的物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

#### 时间上的代价 ####

每当对表中的数据进行增删改时，索引需要动态的维护，而花费时间去维护索引就会降低维护数据的时间。

### f. 索引的最左匹配原则 ###

最左匹配原则：使用联合索引时（如：city和name组成的联合索引name,city），如果查询条件精确匹配到最左边的一列或几列，则使用就可以被使用。

select * from user where name=xx and city=xx ; ／／可以命中索引
    select * from user where name=xx ; // 可以命中索引
    select * from user where city=xx ; // 无法命中索引

注意：如果两个条件都用上了，但是顺序不同，如：*city= xx and name ＝xx*，那么在查询时会自动优化为匹配联合索引的顺序，这样是可以命中索引的。

**由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。**

#### 避免冗余索引 ####

冗余索引指的是索引的功能相同，能够命中 就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引 **，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。**

MySQL 5.7 版本后，可以通过查询 sys 库的 schema\_redundant\_indexes 表来查看冗余索引

### g. 创建索引需要注意的事项 ###

#### 考虑索引的选择性 ####

索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数的比值：

选择性=基数/记录数（就是不重复记录数与总记录数的比值）

选择性的取值范围为(0, 1\]，选择性越高的索引价值越大。如果选择性等于1，就代表这个列的不重复值和表记录数是一样的，那么对这个列建立索引是非常合适的，如果选择性非常小，那么就代表这个列的重复值是很多的，不适合建立索引。

#### 考虑前缀索引 ####

用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。（覆盖索引）

#### 总结 ####

*  索引列的类型尽量小
 *  利用索引字符串值的前缀
 *  主键自增
 *  定位并删除表中的重复和冗余索引
 *  尽量使用覆盖索引进行查询，避免回表带来的性能损耗。

#### 不可以使用索引的情况 ——> ASC，DESC混用 ####

对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序

> ORDER BY子句后的列如果不加ASC或者DESC默认是按照ASC排序规则排序的，也就是升序排序的。

select * from t1 order by b ASC, c DESC;

是无法使用索引的。

### h.索引使用的注意事项 ###

1.  在经常需要搜索的列上，可以加快搜索的速度；
2.  在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
3.  在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
4.  对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
5.  在经常用在连接的列上，这 些列主要是一些外键，可以加快连接的速度；
6.  避免 where 子句中对宇段施加函数，这会造成无法命中索引。
7.  在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
8.  将打算加索引的列设置为 NOT NULL ，否则将导致引擎放弃使用索引而进行全表扫描。
    
     *  订正，来自issue758 。将某一列设置为default null，where 是可以走索引，另外索引列是否设置 null  
        是不影响性能的。 但是，还是不建议列上允许为空。最好限制not null，因为null需要更多的存储空间并且null值无法参与某些运算。
     *  《高性能MySQL》第四章如是说：And, in case you’re wondering, allowing NULL values  
        in the index really doesn’t impact performance 。NULL  
        值的索引查找流程参考：https://juejin.im/post/5d5defc2518825591523a1db  
        ,相关阅读：MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！ 。
9.  删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys  
    库的chema\_unused\_indexes 视图来查询哪些索引从未被使用
10. 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

文章部分引用自：  
Java Guide：[https://snailclimb.gitee.io/javaguide/\#/docs/database/MySQL%20Index][https_snailclimb.gitee.io_javaguide_docs_database_MySQL_20Index]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5aGR4d2FuZw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/f3da47d405774d5cbbaecae7afcaf93f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5aGR4d2FuZw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/f4d899cbe8154bc89d8a331ab516a665.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5aGR4d2FuZw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/927632353fff40a990326d4e87cc7b75.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5aGR4d2FuZw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/4e577f90a93848a280cf7f3cf0d9b17e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5aGR4d2FuZw_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/5a731b18ae3e4e9193b4111e6e05e409.png
[20200907234453111.png_pic_center]: /images/20221123/72dc71965fff49c09ccb0c88ffaf0ffb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5aGR4d2FuZw_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221123/6715a2849747445b867f4458839b0493.png
[https_snailclimb.gitee.io_javaguide_docs_database_MySQL_20Index]: https://snailclimb.gitee.io/javaguide/#/docs/database/MySQL%20Index