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

最新推荐文章于 2024-01-23 11:53:09 发布

weixin_39781143

最新推荐文章于 2024-01-23 11:53:09 发布

阅读量133

点赞数

文章标签： mysql 数据行宽

本文链接：https://blog.csdn.net/weixin_39781143/article/details/113232936

版权

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 |

+----------------------+-------+