InnoDB索引允许null对性能有影响吗

最新推荐文章于 2023-01-16 14:44:59 发布

程序员麦冬

最新推荐文章于 2023-01-16 14:44:59 发布

阅读量308

点赞数

分类专栏：技术文文章标签： java redis mysql 数据库大数据

本文链接：https://blog.csdn.net/m0_49051691/article/details/108579752

版权

本文探讨了InnoDB索引中允许NULL值对性能的影响，包括辅助索引的存储和查询效率。通过实例分析，发现大量NULL值会导致扫描代价增加，而且非叶子节点数据不实时更新，查找数据必须读取叶子节点。同时，允许NULL值不会增加物理存储代价，但可能影响索引效率。最后提出了关于辅助索引的几点建议。

摘要由CSDN通过智能技术生成

1. 初始化测试表、数据

测试表结构如下：

[root@yejr.run]> CREATE TABLE `t_sk` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(10) unsigned NOT NULL,
  `c2` int(10) unsigned NOT NULL,
  `c3` int(10) unsigned NOT NULL,
  `c4` int(10) unsigned NOT NULL,
  `c5` datetime NOT NULL,
  `c6` char(20) NOT NULL,
  `c7` varchar(30) NOT NULL,
  `c8` varchar(30) NOT NULL,
  `c9` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `k1` (`c1`)
) ENGINE=InnoDB;

除了主键索引外，还有个 c1 列上的辅助索引。

用 mysql_random_data_load灌入50万测试数据。

[root@yejr.run]# mysql_random_data_load -hXX -uXX -pXX test t_sk 500000

2. 问题1：索引列允许为NULL，对性能影响有多少

把辅助索引列 c1 修改为允许NULL，并且随机更新5万条数据，将 c1 列设置为NULL

[root@yejr.run]> alter table t_sk modify c1 int unsigned;

[root@yejr.run]> update t_sk set c1 = NULL order by rand() limit 50000;
Query OK, 50000 rows affected (2.83 sec)
Rows matched: 50000  Changed: 50000  Warnings: 0

#随机1/10为null
[root@yejr.run]> select count(*) from t_sk where c1 is null;
+----------+
| count(*) |
+----------+
|    50000 |
+----------+

好，现在观察辅助索引的索引数据页结构。

[root@yejr.run]# innblock test/t_sk.ibd scan 16
...
Datafile Total Size:100663296
===INDEX_ID:46   --聚集索引(主键索引)
level2 total block is (1)  --根节点,层高2(共3层),共1个page
block_no:         3,level:   2|*|
level1 total block is (5)  --中间节点,层高1,共5个page
block_no:       261,level:   1|*|block_no:       262,level:   1|*|block_no:       263,level:   1|*|
block_no:       264,level:   1|*|block_no:       265,level:   1|*|
level0 total block is (5020)  --叶子节点,层高0,共5020个page
block_no:         5,level:   0|*|block_no:         6,level:   0|*|block_no:         7,level:   0|*|
...
===INDEX_ID:47   --辅助索引
level1 total block is (1)  --根节点,层高1(共2层),共1个page
block_no