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

What are the primary keys and field types in MySQL, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Primary key problem:

The index file of Innodb is itself a data file, that is, the data field of B+Tree stores the actual data, and this kind of index is the clustered index. The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index.

The secondary index data field of InnoDB also stores the value of the corresponding record primary key rather than the address, so when searching with the secondary index, it will first find the primary key according to the secondary index, and then find the actual data according to the primary key index. Therefore, Innodb does not recommend using overly long primary keys, otherwise the secondary index will become too large. It is recommended to use self-increasing fields as the primary key, so that every node of the B+Tree will be sequentially filled without frequent split adjustment, which will effectively improve the efficiency of data insertion.

Field type question:

Mysql is a row-based database, while data reading is based on page. There are rows stored in each page. If the amount of data per row decreases, then there are more rows stored in each page. Each time io can occasionally withdraw more rows.

Numeric type: as a last resort, do not use the double type. In addition to taking up a large amount of space, there is also a problem of accuracy. Similarly, decimals with fixed precision do not use decimal. It is recommended that you multiply them by fixed multiples and convert them to integers for storage. Storage space can be saved without any additional maintenance costs. For the storage of integers, it is recommended to separate tinyint/int/bigint, their storage data occupies a certain gap.

Character type: preferred char type, followed by varchar, as a last resort, do not use the text type. Its processing efficiency is lower than that of char and varchar. Varchar cut can not be given a large length at will.

Type of time: try to use timestamp. The storage footprint is only half that of the datetime type. For types that need to be accurate to a certain day, the date type is recommended. Because it takes three bytes to store. Less than timestamp. It is not recommended to use int to store a unix timestamp, which is not intuitive and will not bring any benefits.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.