MySQ的索引Innodb

编程还未

已于 2022-06-28 15:29:49 修改

阅读量279

点赞数

分类专栏： MySQL 文章标签： mysql

于 2022-06-17 17:25:25 首次发布

本文链接：https://blog.csdn.net/program_highway/article/details/125337433

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

本文深入探讨了MySQL InnoDB存储引擎的索引结构，包括页大小、行格式、聚集索引和非聚集索引。讨论了如何计算索引能容纳的数据量，以及如何处理超过一页大小的行数据。还提到了冗余和紧凑行格式的存储特性，以及行级锁定和页外列的概念。

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

文章目录

1.查看索引默认容量

执行命令，查看索引页的默认大小，单位为B。（1KB=1024B）

SHOW GLOBAL STATUS LIKE 'innodb_page_size';
+----------------+-----+
|Variable_name   |Value|
+----------------+-----+
|Innodb_page_size|16384|
+----------------+-----+

查看表的默认信息

select * from information_schema.TABLES where TABLE_SCHEMA = 'zoo' and TABLE_NAME = 'cat';

zoo：zoo为库名

catcat为表名

精简一下结果

select ENGINE,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH,MAX_DATA_LENGTH,INDEX_LENGTH 
from information_schema.TABLES
where TABLE_SCHEMA = 'zoo'
and TABLE_NAME = 'cat';

+------+----------+--------------+-----------+---------------+------------+
|ENGINE|TABLE_ROWS|AVG_ROW_LENGTH|DATA_LENGTH|MAX_DATA_LENGTH|INDEX_LENGTH|
+------+----------+--------------+-----------+---------------+------------+
|InnoDB|129       |889           |114688     |0              |32768       |
+------+----------+--------------+-----------+---------------+------------+

AVG_ROW_LENGTH：平均行长度

DATA_LENGTH：数据文件的长度，字节为单位。

INDEX_LENGTH：分配给非聚集索引的近似空间量（以字节为单位）。具体而言，它是非聚集索引大小（以页为单位）乘以页面大小的总和

2.计算索引容纳的数据

三层树。

请添加图片描述

第一层：一个page大小16K（可以配置，默认16K），主键bigint为例子，一个主键大小8B(字节)，对象头6B（存储下一层索引地址），一个page的索引数量为16*1024/(8+6)=1170个。

第二层：每个元素都延申出一个page：1170*1170

第三层：放数据了 16K/每条的size，假设行数据是1K的话。三层相乘 1170*1170*16=21,902,400

行数据大小最大为page的一半，64K为16K，因为有页外列。最小为 1170*1170*2=2,737,800

主键大小m，数据大小n，page大小为16K，通用公式为：

row=16*1024/(m+6)

row=row*row*(16/n)

***注意：***该数据为默认配置下的估值，跟行数据大小关联比较紧密。

冗余行格式存储特性

行格式具有以下存储特征：REDUNDANT

每个索引记录都包含一个 6 字节的标头。标头用于将连续记录链接在一起，并用于行级锁定。

紧凑行格式存储特性

行格式具有以下存储特征：COMPACT

每个索引记录都包含一个 5 字节的标头，其前面可能有一个长度可变的标头。标头用于将连续记录链接在一起，并用于行级锁定。

MySQL ：： MySQL 8.0 参考手册：： 15.10 InnoDB 行格式

查看索引高度

MySQL查看 InnoDB表中每个索引的高度 - waterystone - 博客园 (cnblogs.com)

查看MySQL InnoDB 表索引的高度_衣舞晨风-CSDN博客

3. 聚集索引

每个表都有一个称为聚集索引的特殊索引，用于存储数据。通常，聚集索引域主键同义。

原文如下。

Each InnoDB table has a special index called the clustered index that stores row data. Typically, the clustered index is synonymous with the primary key.

聚集索引外的索引称为二级索引。二级索引中的每条记录都包含行的主键列，以及二级索引指定的列。如果主键很长，则二级索引会占用更多空间，短主键是有利的。