mysql 数据行宽_innodb行宽几百字节，千万行记录，索引深度多少？

怼烎@ 2023-01-11 13:28 76阅读 0赞

root@localhost:mysql3307.sock \[db1\] 11:30:05> show create table page\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Table: page

Create Table: CREATE TABLE \`page\` (

\`id\` int(11) NOT NULL AUTO\_INCREMENT,

\`name\` varchar(100) DEFAULT NULL,

\`text\` varchar(100) DEFAULT NULL,

PRIMARY KEY (\`id\`)

) ENGINE=InnoDB AUTO\_INCREMENT=11831127 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

root@localhost:mysql3307.sock \[db1\] 11:28:46> select count(\*) from page;

\+----------+

| count(\*) |

\+----------+

| 11831126 |

\+----------+

1 row in set (1.95 sec)

查看平均行大小

root@localhost:mysql3307.sock \[db1\] 11:27:35> show table status like 'page'\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Name: page

Engine: InnoDB

Version: 10

Row\_format: Dynamic

Rows: 11822697

Avg\_row\_length:394

Data\_length: 4661968896

Max\_data\_length: 0

Index\_length: 0

Data\_free: 5242880

Auto\_increment: 11831127

Create\_time: 2018-08-25 11:23:11

Update\_time: 2018-08-25 11:27:12

Check\_time: NULL

Collation: utf8\_general\_ci

Checksum: NULL

Create\_options:

Comment:

1 row in set (0.00 sec)

root@localhost:mysql3307.sock \[db1\] 11:28:38> select \* from mysql.innodb\_index\_stats where table\_name='page';

\+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

\+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

| db1 | page | PRIMARY | 2018-08-25 11:28:12 | n\_diff\_pfx01 | 12172354 | 20 | id |

\+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

3 rows in set (0.00 sec)

root@localhost:mysql3307.sock \[db1\] 11:31:23> select \* from mysql.innodb\_table\_stats where table\_name='page';

\+---------------+------------+---------------------+----------+----------------------+--------------------------+

\+---------------+------------+---------------------+----------+----------------------+--------------------------+

| db1 | page | 2018-08-25 11:28:12 | 12172354 | 288128| 0 |

\+---------------+------------+---------------------+----------+----------------------+--------------------------+

平均一页多少记录？

root@localhost:mysql3307.sock \[db1\] 11:32:33> select 11831126/288128;

\+-----------------+

| 11831126/288128 |

\+-----------------+

| 41.0620 |

\+-----------------+

1 row in set (0.00 sec)

查看索引高度

InnoDB是索引组织表，每个页都包含一个PAGE\_LEVEL的信息，用于表示当前页所在索引中的高度。默认叶子节点的高度为0，那么root页的PAGE\_LEVEL + 1就是这棵索引的高度。在第三个页FIL\_PAGE\_INODE 索引页，这个索引页是聚集索引的root，Root页的位置通常是不会更改的，接下去的问题就是怎样得到一张表所有索引的Root页所在的位置呢？

root@localhost:mysql3307.sock \[db1\] 11:51:29> SELECT b.name, a.name, index\_id, type, a.space, a.PAGE\_NO FROM information\_schema.INNODB\_SYS\_INDEXES a, information\_schema.INNODB\_SYS\_TABLES b WHERE a.table\_id = b.table\_id and b.name='db1/page';

\+----------+---------+----------+------+-------+---------+

\+----------+---------+----------+------+-------+---------+

| db1/page | PRIMARY | 133| 3 | 100 | 3|

\+----------+---------+----------+------+-------+---------+

1 row in set (0.00 sec)

root@localhost:mysql3307.sock \[db1\] 11:55:26> show variables like '%innodb\_page%';

\+----------------------+-------+

| Variable\_name | Value |

\+----------------------+-------+

| innodb\_page\_cleaners | 4 |

| innodb\_page\_size | 16384 |

\+----------------------+-------+