Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQL table and area example analysis", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "MySQL table and area example analysis"!

Districts in MySQL

In any case, the size of the region is always 1M, but the newly created table allocation is relatively small.

1. create a database

mysql> create database vastdata;

Query OK, 1 row affected (0.01 sec)

2. use this database

mysql> use vastdata

Database changed

3. create a table

mysql> create table t1 (col1 int not null auto_increment,col2 varchar(7000),primary key(col1))engine=InnoDB;

Query OK, 0 rows affected (0.01 sec)

4. Insert two pieces of data where repeat ('a ', 7000) is a 7000 consecutive times, that is, 7000 a. So we consider a record to be 7K in size.

In MySQL, the size of a data page is 16KB, so two records occupy one page.

mysql> insert t1 select null,repeat('a',7000);

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert t1 select null,repeat('a',7000);

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

5. View the data file size corresponding to t1 table

mysql> system ls -lh /data/mydb/vastdata/t1.ibd

-rw-rw---- 1 mysql mysql 96K Oct 17 01:09 /data/mydb/vastdata/t1.ibd

6. Use the py_innodb_page_info.py tool to view the distribution of pages in the data file

[root@vast python]# python py_innodb_page_info.py -v /data/mydb/vastdata/t1.ibd

page offset 0000000, page type #system page

page offset 0000001, page type #system page

page offset 0000002, page type #system page

page offset 0000003, page type , page level #The page where the data is stored

page offset 0000000, page type #Free page

page offset 0000000, page type #Free page

Total number of pages: 6: #Number of pages allocated

Freshly Allocated Pages: 2 #Free Pages

Insert Buffer Bitmap: 1 #Number of pages corresponding to Insert Buffer Bitmap in the system

File Space Header: 1 #Number of pages corresponding to File Space Header in the system

B-tree Node: 1 #Number of pages storing data

File Segment inode: 1 Number of pages corresponding to File Segment inode in the system

7. View the current data file size corresponding to t1

-rw-rw---- 1 mysql mysql 96K Oct 17 01:09 /data/mydb/vastdata/t1.ibd

Here you can see that the t1 table size is only 96K, and there is no direct allocation of 1M.

8. Insert a row again, approximately 7KB in size

mysql> insert t1 select null,repeat('a',7000);

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

9. Looking at the data file size again, it is still 96K

mysql> system ls -lh /data/mydb/vastdata/t1.ibd

-rw-rw---- 1 mysql mysql 96K Oct 17 01:15 /data/mydb/vastdata/t1.ibd

10. Take a look at the current internal page distribution of this data file

[root@vast python]# python py_innodb_page_info.py -v /data/mydb/vastdata/t1.ibd

page offset 00000000, page type

page offset 00000001, page type

page offset 00000002, page type

page offset 00000003, page type , page level

page offset 00000004, page type , page level

page offset 00000005, page type , page level

Total number of page: 6:

Insert Buffer Bitmap: 1

File Space Header: 1

B-tree Node: 3

File Segment inode: 1

As we can see, the free page is gone. Where 0000004, 0000005 are the blocks corresponding to the root of the stored data block 000003.

11. Create a stored procedure to simplify insertion.

mysql> delimiter //

mysql> create procedure load_t1(count INT UNSIGNED)

-> begin

-> declare s int unsigned default 1;

-> declare c varchar(7000) default repeat('a',7000);

-> while s insert into t1 select NULL,c;

-> set s = s+1;

-> end while;

-> end;

-> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

12. To execute this stored procedure, 60 means inserting 60 rows into the table, each row containing 7000 a's.

mysql> call load_t1(60);

Query OK, 1 row affected (0.06 sec)

13. See how many rows there are in the table. It can be seen that there are 63 rows

mysql> select count(*) from t1;

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

| count(*) |

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

| 63 |

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

1 row in set (0.00 sec)

14. View data file size

mysql> system ls -lh /data/mydb/vastdata/t1.ibd

-rw-rw---- 1 mysql mysql 576K Oct 17 01:39 /data/mydb/vastdata/t1.ibd

63 * 7KB = 441KB Two rows of records require one data page, so these 63 rows require 32 data pages.

15. Check the distribution of pages in the data file to verify the guess

[root@vast python]# python py_innodb_page_info.py -v /data/mydb/vastdata/t1.ibd

page offset 00000000, page type

page offset 00000001, page type

page offset 00000002, page type

page offset 00000003, page type , page level

page offset 00000004, page type , page level

page offset 00000005, page type , page level

page offset 00000006, page type , page level

page offset 00000007, page type , page level

page offset 00000008, page type , page level

page offset 00000009, page type , page level

page offset 0000000a, page type , page level

page offset 0000000b, page type , page level

page offset 0000000c, page type , page level

page offset 0000000d, page type , page level

page offset 0000000e, page type , page level

page offset 0000000f, page type , page level

page offset 00000010, page type , page level

page offset 00000011, page type , page level

page offset 00000012, page type , page level

page offset 00000013, page type , page level

page offset 00000014, page type , page level

page offset 00000015, page type , page level

page offset 00000016, page type , page level

page offset 00000017, page type , page level

page offset 00000018, page type , page level

page offset 00000019, page type , page level

page offset 0000001a, page type , page level

page offset 0000001b, page type , page level

page offset 0000001c, page type , page level

page offset 0000001d, page type , page level

page offset 0000001e, page type , page level

page offset 0000001f, page type , page level

page offset 00000020, page type , page level

page offset 00000021, page type , page level

page offset 00000022, page type , page level

page offset 00000023, page type , page level

Total number of page: 36:

Insert Buffer Bitmap: 1

File Space Header: 1

B-tree Node: 33

File Segment inode: 1

Here you see 33 data pages, one of which is the root. 32+1=33

16. Insert a line of records

mysql> call load_t1(1);

Query OK, 1 row affected (0.01 sec)

17. Check the size of the data file and find that at this time, the data file is 2M

mysql> system ls -lh /data/mydb/vastdata/t1.ibd

-rw-rw---- 1 mysql mysql 2.0M Oct 17 01:50 /data/mydb/vastdata/t1.ibd

mysql>

18. View the distribution of pages in a data file.

[root@vast python]# python py_innodb_page_info.py /data/mydb/vastdata/t1.ibd

Total number of page: 128:

Freshly Allocated Page: 91

Insert Buffer Bitmap: 1

File Space Header: 1

B-tree Node: 34

File Segment inode: 1

There are 91 free pages left.

At this point, I believe that everyone has a deeper understanding of "MySQL table and area example analysis", may wish to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.