MySQL 5.7 索引原理和存储引擎及 NULL 和空值问题

最新推荐文章于 2025-03-16 04:30:00 发布

guaoran

最新推荐文章于 2025-03-16 04:30:00 发布

阅读量1.2k

点赞数 1

分类专栏：数据库文章标签：索引 mysql B+ 树存储引擎

本文链接：https://blog.youkuaiyun.com/guaoran/article/details/108293526

版权

数据库专栏收录该内容

12 篇文章

订阅专栏

存储引擎是第三方提供的，可插拔式的插件。索引机制也便是有插拔式的存储引擎进行实现。

索引是什么

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构
在这里插入图片描述

为什么选择的是B+

二叉查找树 Binary Search Tree
在这里插入图片描述

可以看出当我们插入一个有序的数值时，树的深度就比较长（右子节点），时间复杂度比较差O(n)

平衡二叉查找树 AVL Trees

平衡二叉树又叫相对平衡树，高度差不会超过1

在这里插入图片描述
该方式在一定程度上解决了顺序插入时，树的深度的问题，但是当数据量比较大的时候，这种问题也是会存在的。
数据处的（高）深度决定着他的IO操作次数，IO操作耗时大。
每一个磁盘块（节点/页）保存的数据量又太小

多路平衡查找树 B-Tree

在这里插入图片描述
B 树，从根本上解决了由于数据量大导致的树深度的问题。

不过由于每个节点都存储了磁盘的数据

加强版多路平衡查找树plus B+ Tree

在这里插入图片描述

B+TREE与B-TREE的区别

B+节点关键字搜索采用闭合区间
- 这样就可以知道最小值和最大值是多少，避免空查询不存在的值
B+非叶节点不保存数据相关信息，只保存关键字和子节点的引用
- 相同的空间大小，B+树能存储更多的索引
B+关键字对应的数据保存在叶子节点中
B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系
- 提高 order 、范围查询的速度

MySQL中 B+Tree 索引的体现形式

Innodb VS Myisam
在这里插入图片描述

myisam:
- .frm:表定义文件
- .myd:数据
- .myi:索引
innodb:
- .frm:表定义文件
- .ibd:数据和索引

Innodb	Myisam
.IBD文件	.MYI 和 .MYD 文件
辅助索引中存放的是主键索引，流程是根据辅助索引查询到主键索引，在主键索引中查询到对应的数据。	主键索引和辅助索引是通过在叶子节点存放数据的引用地址

myisam 主键索引和辅助索引
在这里插入图片描述

innodb 主键索引和辅助索引
在这里插入图片描述

myisam 和 innodb 主键和非主键

myisam 的主键索引和辅助索引

是通过在叶子节点存放数据的引用地址

innodb 的主键索引和辅助索引，

辅助索引中，存放的是主键索引，流程是根据辅助索引查询到主键索引，在在主键索引中查询到对应的数据。

为什么要多次一举呢？因为在innodb中，认为主键索引是最经常、频繁用到的。而且当数据进行迁移的时候，只要保证主键不变，不需要在重新维护辅助索引。

索引补充

离散性：越高，选择性越好，越适合建在第一位索引
在这里插入图片描述

联合索引列选择原则

经常用的列优先【最左匹配原则】
选择性（离散度）高的列优先【离散度高原则】
宽度小的列优先【最少空间原则】

MySQL 存储引擎

CSV存储引擎

数据存储以CSV文件

不能定义索引，列定义必须为 NOT NULL、不能设置主键和自增
- 不适合大表或数据的在线处理
CSV 数据的存储用,隔开，可直接编译CSV文件进行数据的修改
- 数据安全性低
- 编辑之后要生效需要执行 flush table tablename; 命令

应用场景：

数据的快速导入
表格直接转换成CSV

Archive 存储引擎

压缩协议进行数据的存储,数据存储为ARZ文件格式

只支持 insert 和 select 两种操作
只允许自增ID列建立索引
行级锁
不支持事务
数据占用磁盘少（同innodb 比较)

应用场景：

日志系统
大量的设备数据采集

Memory 存储引擎

数据都是存储在内存中，IO效率要比其他引擎高很多服务重启数据丢失，内存数据表默认只有16M

支持hash 索引，B tree 索引，默认hash索引
字段长度是固定长度 varchar(32)=char(32)
不支持大数据存储类型字段，如：blog 、text
表级锁

应用场景：

等值查找热度较高数据
查询结果内存中的计算，大多数都是采用这种存储引擎作为临时表，用来存储计算的数据

Myisam 存储引擎

MySQL 5.5版本之前的默认存储引擎，较多的系统表也还是使用这个存储引擎，系统临时表也会用到Myisam存储引擎,(如果数据较少的话，小于16M，默认先用Memory 引擎)

select count(*) from table 无需进行数据的扫描
数据（MYD）和索引（MYI）分开存储
表级锁
不支持事务
通过先禁用索引、载入数据，重新启用索引，来实现高效的导入数据

Innodb 存储引擎

MySQL 5.5 及以后的默认存储引擎

支持事务
行级锁
使用主键索引（聚集索引）存储数据
支持外键关系保证数据完整性

NULL 值和空值的问题

MySQL NULL 值问题官网

You can add an index on a column that can have NULL values if you are using the MyISAM, InnoDB, or MEMORY storage engine. Otherwise, you must declare an indexed column NOT NULL, and you cannot insert NULL into the column.
如果使用的是MyISAM，InnoDB或MEMORY存储引擎，则可以在具有NULL值的列上添加索引。 否则，您必须声明一个索引列NOT NULL，并且您不能在该列中插入NULL。

SELECT VERSION(); -- 5.7.22

创建表

CREATE TABLE `null_demo_innodb` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `test_null` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_test_null` (`test_null`)
) ENGINE=INNODB CHARSET=utf8;

插入数据

insert into `null_demo_innodb` (`id`, `test_null`) values
('1','one'),('2','two'),
('3',NULL),('4',NULL),
('5',''),('6',''),
('7','senven');

统计的时候，不会统计出为 NULL 的记录

SELECT COUNT(id) FROM null_demo_innodb; -- 7
SELECT COUNT(test_null) FROM null_demo_innodb; -- 5

查询的不同

SELECT * FROM null_demo_innodb WHERE test_null IS NULL; -- 2 只有 NULL 值
SELECT * FROM null_demo_innodb WHERE test_null IS NOT NULL; -- 5 包含有值的和空值，非 NULL 的

SELECT * FROM null_demo_innodb WHERE test_null =''; -- 2 只有 空值
SELECT * FROM null_demo_innodb WHERE test_null !=''; -- 3 只有有值的，不包含 NULL 值和空值

另外发现一个有趣的现象，如果我们一个表只有两个字段，即主键和非主键字段。当我们对非主键字段建立索引之后，使用 != 或 <> 或 not NULL 是会走索引的。