mysql索引

Mysql架构图

存储引擎负责对表中的数据进行读取和写入,常用的存储引擎有InnoDB, MyISAM, Memory等,不同的存储引擎有自己的特性, 数据在不同存储引擎中存放的格式也是不同的,比如Memory都不用磁盘来存储数据.

在InnoDB中, 数据会存储到磁盘上, 在真正处理数据是需要先将数据加载到内存, 表中读取某些记录时, InnoDB存储引擎不需要一条一条的把记录从磁盘上读出来, InnoDB采取的方式是 : 将数据划分为若干个页, 以页作为磁盘和内存之间交互的基本单位, InnoDB中页的大小一般为16KB, 也就是说, 当需要从磁盘中读取数据时每一次最少将从磁盘中读取16KB的内容到内存中, 每一次最少也会把内存中的16KB内容写入到磁盘中.

索引分类

主键索引: 主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。
唯一索引: 索引列的所有值都只能出现一次，即必须唯一，值可以为空。
普通索引: 基本的索引类型，值可以为空，没有唯一性的限制。
全文索引: 全文索引的索引类型为FULLTEXT.

InnoDB

查看文件存储位置: show global variables like “%datadir%”;
查询数据,先读磁盘, 再走条件(where).
聚集索引表, 有序.
局部性原理与磁盘预读:
由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。预读长度一般为页的整数倍，主存和磁盘以页作为单位交换数据。(操作系统中一般为4K, mysql中一页为16KB)

当一个数据被用到时，其附近的数据也通常会马上被使用。
程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。
参考: http://blog.codinglabs.org/articles/theory-of-mysql-index.html

InnoDB数据页结构

File Header: 记录 Page 的头信息(页的一些通用信息)，FIL_PAGE_PREV 和 FIL_PAGE_NEXT 字段分别记录该页的上一页和下一页。
Page Header: 记录 Page 的状态信息。
Infimum 和 Supremum: 记录该页存储的上下边界。
User Records 中存放的是实际的数据行记录。
Free Space 中存放的是空闲空间。
Page Directory: 记录着与二叉查找相关的信息, 页中的记录的相对位置 。
File Trailer: 存储用于检测数据完整性的校验和等数据。

User Records, Free Space 都是用于存储用户记录, User Recoreds 是已存储占用的空间.

InnoDB行格式

InnoDB的不同记录格式: Compact, Redundant, Dynamic, Compressed .
MySql单行限制为65535个字节, 定义数据项时, 必须小于65533 .
修改表中的行格式:
CREATE TABLE table_name (vol…) ROW_PORMAT=行格式名称;
ALTER TABLE table_name ROW_PORMAT =行格式名称

COMPACT 行格式:

变长字段长度列表: mysql支持一些变长的数据类型(varchar, varbinary, text … ), 这些数据的长度不固定,mysql将这些字段占用的字节数存起来,形成的变长字段长度列表.

NULL标志位: 记录该行的列信息, 是否为空.

记录头信息: 行相关信息, 固定占五个字节.

行溢出:
一个页的大小一般是16KB, 也就是16384个字节, 而一个varchar类型的列就最多可以存储65533个字节(行长限制为65535), 这样就可能出现一个页存放不下一条记录.

1. Compact和Reduntant行格式中, 记录的真实数据处就存取部分数据加下页地址, 剩余数据存放在其它页中. 真实数据处用20个字节存储指向存储这些页的地址与所占字节数.
2. Dynamic 和 Compressed 行格式中, 不在记录的真实数据处存储一部分数据, 而是把所有数据都存储到其它页面中, 这里只存放数据所在的页面地址. Compressed 还会采用压缩 .

InnoDB数据存储及查询结构

存储机制: 建表时先开一页, 存数据时, 先将数据放入第一页, 当第一页放不下时, 先将第一页copy一份, 再增加第二页存放数据,原第一页该变成为目录页. (这样做表的起始页将不会改变)

查询: 从根节点依次向下查找.

非主键索引(辅助索引):
利用辅助索引查询数据, 最后会返回主键索引中对行进行查找., InnoDB的辅助索引默认不会存储数据.
查询时遵从最左前缀原则, 即比较的索引的左边必须精确. 例如 ‘aa%’.
在MySQL中 null值是最小的.

参考: https://www.bilibili.com/video/av57156557/?p=7