MySql（五）：索引原理

前文：

空间局部性：数据和程序都有聚集的倾向

时间局部性：之前被查询的数据很可能再次被查询

磁盘预读：在磁盘和内存进行交互的时候，有一个最小的逻辑单元datapage(数据页)，每页的大小为4k的整数倍大小，操作系统会对磁盘数据读取按照页的整数倍进行。

 

索引是什么： 数据库中的一种存储数据结构。

1、该数据结构存储表中一列或多列的值，所以索引是基于数据表的列。

2、作用：增快数据的查询效率(减少IO次数和减少每次IO量)。

3、查看：show index from tableName;

优点：

1、减少服务器需要扫描的数据量

• 减少IO次数：索引树的查找避免全表扫描
• 减少IO量：索引树只包含索引列相关的数据，不用加载每行完整数据；datapge数据页的设计

2、索引是有序的，将随机IO变成顺序IO

3、排序时(order by)使用索引能避免临时表的创建。

4、索引覆盖无需查询完整数据行

 

缺点：

1、索引占用物理空间(磁盘)，以及数据库存储引擎的优化器会选择合适的索引，所以索引不是越多越好；

2、对表数据进行增删改时，索引也需要动态维护，索引不合理时会增加数据增删改的成本。

 

索引类型：

1、聚簇索引和非聚簇索引(辅助索引)，属于InnoDB

2、主键索引

3、唯一索引

4、普通索引：单列索引和组合索引

5、全文索引

 

 

MySQL三层：

MySql的逻辑架构：

• 连接器：负责跟客户端连接、权限管理、连接管理等。客户端先和连接器建立连接，经过ICP握手后，连接器认证客户端身份，来完成连接。
• 分析器：Sql语句的词法分析、语法分析。
• 优化器：执行计划的生产，决定索引的使用，和多表Join时，各表连接的顺序。
• 执行器：操作引擎，返回结果。
• 存储引擎：负责对表中的数据进行读取和写入，常用的存储引擎有InnoDB、MyISAM、Memory等，不同的存储引擎有自己的特性，InnoDB相对MyISAM有事务操作和行锁(对于表锁能提高并发)，数据在不同的存储引擎中存放格式也是不同的，比如InnoDb数据会和聚簇索引(primary key > unique index > rowid)数据存放在一起、Memory不用磁盘存储数据。

连接器中长连接和短连接的问题？

连接分为长连接、短连接。每次连接需要在内存中分配连接对象，一只使用长连接积累下来，会导致内存占用过大，导致OOM。所以需要定期断开长连接，或执行占用内存大的查询后初始化连接资源(MySql5.7之后版本，可通过执行mysql_reset_connection初始化连接到刚刚创建完的状态，不需要重连和重新权限验证)。

 

 

在InnoDB中数据会存储在磁盘中，数据处理时会先加载到内存，InnoDB数据读取存储的方式：将数据划分成页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB。所以InnoDB数据的读取和写入都是16KB。

 

InnoDB数据页结构：

页是InnoDB管理存储空间的基本单位，一个页的大小默认为16KB；

-- 查看数据页太小
show global status like 'Innodb_page_size';

名称	中文名	占用空间	简单描述
File Header	文件头部	38Byte	页的一些通用信息
Page Header	页面头部	56Byte	数据存储专有的页信息
Infimum+Supremum	最小记录和最大记录	26Byte	两个虚拟的行记录
User Record	用户记录		实际存储的行记录的内容
Free Space	空闲空间		页中尚未使用的空间，当存储数据时，会分配给User Record
Page Directory	页面目录	不确定	页面中某些记录的相对位置
File Trailer	文件尾部	8Byte	校验页是否完整

 

 

 

 

 

 

 

 

 

InnoDB数据行结构：

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

create table 表名 (列信息) row_format=行格式名称；

alter table 表名 row_format=行格式名称；

 

Compact行格式：

为了描述行数据而额外记录的信息：变长字段长度列表、NULL标志位、记录头信息、隐藏列。

变长字段长度列表：

Mysql支持一些变长数据类型，如varchar(M)、varbinary(M)、text、blob，这些数据类型列称为变长字段，变长字段存储的数据是不固定的，所以在真实数据存在中，为了方便变长数据占用空间统计，则把变长字段的占用字节树也存储起来。在Compact行格式中，所有变长字段占用字节长度都存在记录开头部分，就是变长字段长度列表。如果表中的列没有变长字段，则变长字段长度列表不存在。

char是固定长度类型，varchar是可变长度类型；varchar(M)，M代表最大能存M字符，mysql5.0.3指字节，之后值字符。

 

NULL值列表：

Compact格式也会把允许为null的列统一管理，标记哪些列为null，二进制位为1代表为null，为0则不为null。如果没有允许为NULL的列，则NULL值列表也不存在。

 

记录头信息：

用来描述记录的记录头信息，固定5个字节长度。5个字节即40个二进制位，不同的位代表不同的意思。

隐藏列：

列名	说明	是否必须	占用空间	描述
DB_ROW_ID	row_id	否	6字节	行ID，唯一标识一条记录，表没有主键会选择Unique键作为主键，如果Unique键也没有，则添加row_id隐藏列作为主键。所以row_id在没有主键和唯一键时才存在。
DB_TRX_ID	transaction_id	是	6Byte	事务id，当前数据被修改的事务id
DB_ROLL_PTR	roll_pointer	是	7Byte	回滚指针，指向上一个修改的数据

 

 

 

 

 

 

 

行数据溢出：

varchar(M) 类型的列最多可以占用65536个字节，其中M代表最多存储的字符数量，如果我们使用ascii字符集的话，一个字符代表一个字节。

create table varchar_test (
    c varchar(65535)
) charset=ascii row_format=compact;

----
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

会报错：

mysql对一条记录占用的最大存储空间是有限制的，除BLOB和TEXT类型外，其他列(不包含隐藏列和记录头)占用字节长度加起来不能超过65535Byte，这个65536字节除自身信息外还包含可变字段长度列和NULL值列表。因为c字段没定义NOT NULL所以，NOT NULL值需要占用1个字节，可变长度列需要占用2个字节，所以c字段只能存储65532个字节的数据。

但是InnoDB中pagesize大小为16KB=16384Byte，所以当一页存储不下数据会存储在下一个数据页，使用记录头信息的next_record表示下一页的相对位置。

 

Dynamic和Compressed行格式：

• Compact和Reduntant格式中，对于数据太多溢出的处理是在真实数据记录处存储一部分数据，剩下的数据分散在其他页。
• Dynamic和Compressed行格式在数据溢出是不会在数据记录处存储一部分数据，而是把所有数据存储在其他页，只记录数据存储页地址。
• Compressed会使用压缩算法对页面进行压缩。

 

MySql的索引原理和数据结构：B+树/哈希表

为什么是B+数？从数据存储的可能结构分析->

数据存储可能有的数据结构：

 

1、链表：

• 查询时间复杂度高O(n)，范围查找必须遍历所有链表，效率低。

2、Hash表：

• 需要优秀的hash算法，避免哈希碰撞、哈希冲突问题，容易浪费空间；

• 数据是散列存储的，没有排序和没有规则，不支持范围查询，范围查找时间复杂度O(n)；

• 需要大量的内存空间。

3、BST(二叉搜索树)、AVL(二叉平衡树)、red/black(红黑树)：

数据结构模拟网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

 

• 二叉搜索树：数据递增插入或者递减插入时，会退化为一个链表，查询时间复杂度O(n)，则过渡到 -> 平衡二叉树。

• 平衡二叉树：左子树和右子树高度相差不能大于1，所以按递增数据插入会不断通过旋转保证树的平衡，插入数据时树为了平衡的旋转操作比较费性能，插入性能低，则过渡到 -> 红黑树。

• 红黑树：为非严格的二叉平衡树，最长子树的高度不超过最短子树高度的2倍即可，减少插入时的旋转操作。

所有二叉树只有左右节点，当数据量越多时，树的高度/层数也就越高，树的IO就会变多，查询就会变慢，则过渡到 -> 多阶树/B数。

 

4、B数：

• 多阶(degree)树，每个节点可以放degree-1个值，B树中数据存在data里面，每个节点为一个磁盘快16k，假设data中数据库一条数据为1k，则一个节点可以存16个范围的条数据，则3层的B树，总共可以存16*16*16条数据。存储数据较少。

• 每个节点中，data数据信息占用大量数据空间，导致在有限层的树结构中能存储的数据量少。

5、B+树：

• 非叶子节点重复，但只存储key和指针，每个磁盘块可以存储更多的节点，可降低高度，将数据范围变为多个区间，区间越多，数据检索越快，如一个key占10byte，则三层可存16*1024/10 * 16*1024/10 * 16(最后层存数据) = 四千万记录；

• 叶子节点存储key和数据，且两两指针相连(同磁盘预读特性)，顺序查询性能更高

• 全表扫描就是通过聚簇索引的叶子节点的指针进行扫描，索引命中则从B+树根节点开始进行查找。

 

 

B+树跟B树的区别是什么：

• B树中每个节点会存储键值、子节点指针、键值对应的其他数据；B+树非叶子节点只存储键值、子节点指针，每个磁盘块存储的节点key变多，树的高度减少，IO减少，且数据划分的范围区间多，数据检索越快；

• 同样层数，B+树能存储的数据量多；

• B+树的非叶子节点会重复，B+树叶子节点两两指针相连(双向指针，方便范围查询)，顺序查询性能高(磁盘预读)

 

MySql聚簇索引和非聚簇索引的区别：

聚簇索引：不是单独的索引类型，而是一种数据存储方式，即数据和索引键值存放在一起

非聚簇索引：数据文件和索引文件分开，即不在一起的索引是非聚簇索引，非聚簇索引的设计是为了保证数据的一致性和节省存储空间。

 

 

 

回表：

通过非聚簇索引查找数据时，结果列数据不在索引树上，需要通过主键再次去聚簇索引查询，这叫做回表。

 

索引覆盖(Using index)：

通过非聚簇索引查找数据时，查询的结果列都能通过当前索引树的数据满足，则会直接使用索引树的数据返回，通常explain执行计划的extra信息为Using index。

索引覆盖的优点：

1. 索引树上存储的数据小于完整数据行，可以减少数据的访问量，减少IO量
2. 索引是有序的，对于IO密集型的范围 查找，可以减少IO次数

 

索引下推：

是指将数据筛选处理逻辑从mysql server层下放到存储引擎层，在Mysql5.7版本默认开始；如select * from t1 where a = xxx and b = xxx，索引没下推前，会先根据a=xx去存储引擎拉取数据，返回到server层再对b=xxx过滤；有了索引下推后，直接从存储引擎拿取a=xxx and b = xxx的结果。

// 查看索引下推配置 
show variables like '%optimizer_switch%';

 

MySql的组合索引的结构是什么样的

1. 索引树的数据结构包含：key(索引值) 、pointer(子节点指针) 、data（叶子节点主键值）；
2. 单个索引的key只需要对应一个value，组合索引的key对应多个value，如(value1, value2, value3...)；
3. 组合索引的排序是按照索引的顺序从第一个列value值比较，再比较第二个列value，依次比较排序；其中允许NULL值的列中，null值算是最小的，排在最左边。
4. 普通索引中，索引的值也会包含主键的值，因为当普通索引值一样时，为了区分不同的行，普通索引的值也会加上主键的值。

 

索引匹配方式：

1. 全值匹配：和索引中索引所以列进行匹配
2. 匹配最左前缀：组合索引只匹配前面几列
3. 匹配列前缀：匹配索引列的值的开头部门
4. 匹配范围值：查询某索引列的一个范围的数据
5. 精确匹配某一列并范围匹配另外一列