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

This article introduces the relevant knowledge of "how to understand and master mysql tables". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

one。 Index organization table

If the primary key is not defined when the table is created, the innodb storage engine selects or creates the primary key as follows

-first, determine whether there is a non-empty unique index in the table. If so, the column is the primary key. When there are multiple non-empty unique indexes in the table, the innodb storage engine will select the non-empty unique index first defined when the table is created as the primary key.

-if the above conditions are not met, the innodb storage engine automatically creates a 6-byte pointer.

Click (here) to collapse or open

Because b is the only index allowed to be null, the primary key of the table is d

Create table wwj.z (

An int not null

B int null

C int not null

D int not null

Unique key (b)

Unique key (d)

Unique key (c)

);

Insert into wwj.z select 1,2,3,4

Insert into wwj.z select 5,6,7,8

Insert into wwj.z select 9,10,11,12

Mysql > select a recorder brecronomy crecinct drection rowid from wwj.z

+

| | a | b | c | d | _ rowid |

+

| | 1 | 2 | 3 | 4 | 4 |

| | 5 | 6 | 7 | 8 | 8 |

| | 9 | 10 | 11 | 12 | 12 | |

+

II. Innodb logical storage structure

1. Tablespace

The default is that all data is stored in a shared tablespace. If the parameter innodb_file_per_table is enabled, the data for each table will be placed in a separate tablespace.

If innodb_file_per_table is enabled, only data, indexes, and buffered bitmap pages are stored in the tablespace of each table. Other types of data, such as inserting buffer index pages, system transaction information, and secondary write buffering, are still stored in the original shared tablespace.

two。 Segment

The table space is composed of each segment, and the common segments are data segment, index segment, rollback segment and so on.

Segment management is done by the engine itself, and DBA cannot and does not need to control it.

3. Zone

An extent is a space made up of contiguous pages, and in any case the size of each zone is 1m.

In order to ensure the continuity of the pages in the zone, the innodb storage engine requests 4 zones from the disk at a time. By default, the page size of the innodb storage engine is 16kb, that is, there are 64 consecutive pages in an extent.

When the user enables the parameter innodb_file_er_table, the default size of the table created is 96kb, not the default size of the zone 1m. This is because at the beginning of each segment, 32 page-size fragmented pages are used to store data, and after using these fragmented pages, 64 consecutive pages are applied for. The purpose of this is that for small tables, or segments such as undo, you can apply for less space at the beginning, saving on disk capacity.

4. Page

Minimum unit of disk management for innodb

Common page types are:

Data page

Undo page

System page

Transaction data page

Insert buffer bitmap page

Insert buffered free list page

Uncompressed binary large object page

Compressed binary large object page

5. OK

III. Innodb line record format

1.compact line record format

Introduced after 5.0, it is designed to store data efficiently. To put it simply, the more rows of data stored in a page, the higher its performance.

2.redundant

Row record storage format prior to 5.0

3. Row overflow data

The maximum length of the row to hold data of type varchar is 65532 bytes.

Varchar (N) represents the character length when the table is created.

For example, the following table statement will report an error

Create table 22 (

A varchar (65532)

) charset=utf8 engine=innodb

ERROR 1074 (42000): Column length too big for column 'a' (max = 21845); use BLOB or TEXT instead

The following statement can be executed normally

Create table 22 (

A varchar (1)

) charset=utf8 engine=innodb

Insert into wwj.22 values ('Yang')

Row overflow data storage graph:

So how long varchar is saved in a single data page, and from how long will it be saved in the blob page?

Think of it this way: the innodb storage engine table is indexed, or B+Tree, so that there should be at least two row records in each page, so if there is only one record in the page, the innodb storage engine automatically stores the row data in the overflow page.

Through experiments, it is found that when the length of a row is 8098 bytes, two records can be placed in the page, and it does not need to be extended to the blob page.

The same is true for TEXT and BLOB data types, if at least 2 records can be stored in the data page, the records will not be extended to the blob page.

4. New file format barracuda

There are two new line record formats under this file format: compressed and dynamic

The new record format uses a complete row overflow way for the data stored in blob. Only 20 bytes of pointers are stored in the data page, and the actual data is stored in off page, while the previous compact and redundant formats store 768 prefix bytes.

Figure

Another feature of the compressed row record format is that the row data stored in it is compressed using zlib's algorithm. Therefore, large-length data such as blob,text,varchar can be stored very efficiently.

The parameter innodb_file_format is used to specify the file format

four。 Constraint

Constraint type

Primary key

Unique key

Foreign key

Default

Not null

five。 Partition table

1.mysql supports the following types of partitions:

Range partition

List partition

Hash partition

Key partition

* if there is a primary key or unique index in the table, the partitioned column must be part of the unique index. If you do not specify a primary key and a unique index when you create the table, you can specify any column partition column.

* the indexes in mysql partitioned tables are locally partitioned and global indexes are not supported.

two。 Partition Typ

(1) .range partition

Create table wwj.sales (

Money int

Date1 datetime

) engine=innodb

Partition by range (year (date1)) (

Partition p2008 values less than (year ('2009-01-01'))

Partition p2009 values less than (year ('2010-01-01'))

Partition p2010 values less than (year ('2011-01-01'))

);

The optimizer can only optimize functions such as year (), to_days (), to_seconds () and unix_timestamp ().

(2). List partition

Create table wwj.tt (

An int

B int

) engine=innodb

Partition by list (b) (

Partition p0 values in (1, 3, 5, 7, 9)

Partition p1 values in (0meme 2re4 minus 6p8)

);

(3). Hash partition

The aim is to distribute the data evenly among the predefined partitions to ensure that the amount of data in each partition is roughly the same.

-- ordinary hash partition

Create table wwj.qqq (

An int

B datetime

) engine=innodb

Partition by hash (year (b))

Partitions 4

The algorithm for allocating records is as follows:

Mod (year ('2010-04-01'), 4) = mod (2010) = 2

So the record will be placed in the p2 partition.

-- linear hash partition

Create table wwj.qqq (

An int

B datetime

) engine=innodb

Partition by hash (year (b))

Partitions 4

The algorithm for allocating records is as follows:

Take the power value V V=POWER of the next 2 that is greater than the number of partitions 4. (2) LOG (2)) = 4

N=YEAR ('2010-04-01') & (Vmur1) = 2

So the record will be placed in the p2 partition.

The advantage of linear hash partitions is that adding, deleting, merging, and splitting partitions will be faster and easier to deal with tables that contain a lot of data.

The disadvantage is that the data may not be evenly distributed.

(4) .key partition

Similar to hash partitions, except that hash partitions use user-defined functions, while key partitions use functions provided by the mysql database.

Create table wwj.www (

An int

B datetime

) engine=innodb

Partition by key (b)

Partitions 4

(5). Columns partition

In the previous four partitions, the condition of the partition is that the data must be shaped, and if it is not, it needs to be converted to shaping through a function.

Columns partitions can be partitioned directly using non-shaping data, partitions are directly compared according to the type, do not need to be converted to shaping, and support multiple column values for partitioning.

Columns supports the following data types:

Int,smallint,tinyint,bigint,date,datetime,char,varchar,binary,varbinary

This is the end of the content of "how to understand and master the mysql table". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.