Shulou(Shulou.com)06/01 Report--

To answer questions from water lovers on the planet:

Mr. Shen, I heard on the Internet that when the amount of data in the MySQL data sheet is relatively large, the primary key should not be too long. Is that the case? And why is that? This problem can not be generalized: (1) if it is an InnoDB storage engine, the primary key should not be too long; (2) if it is a MyISAM storage engine, the impact is not great; first give a simple chestnut to explain the pre-order knowledge. Suppose there is a data table:

T (id Competition, name KEY, sex, flag)

Where: (1) id is the primary key; (2) name builds a general index; assume that there are four records in the table:

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

If the storage engine is MyISAM, the structure of its indexes and records is as follows:

(1) there is a separate area storage record (record); (2) the primary key index has the same structure as the ordinary index, which stores the pointer of the record (temporarily understood as the pointer); voiceover: (1) the primary key index and the record are not stored together, so it is a nonclustered index (Unclustered Index); (2) the MyISAM can be free from competition. When MyISAM uses an index for retrieval, it first locates from the index tree to the record pointer, and then locates to the specific record through the record pointer. Voiceover: regardless of primary key index or general index, the process is the same. Unlike InnoDB, the structure of its indexes and records is as follows:

(1) the primary key index is stored with the record; (2) the general index stores the primary key (which is no longer a pointer); voiceover: (1) the primary key index and the record are stored together, so it is called clustered index (Clustered Index); (2) InnoDB must have a clustered index; InnoDB can directly locate to the row records when querying through the primary key index.

However, if you query through the ordinary index, the primary key will be queried first, and then the index tree will be traversed twice from the primary key index. Back to the point, why should the primary key of InnoDB not be too long? Suppose there is a user center scenario that contains business attributes such as ID card number, ID card MD5, name, date of birth, and so on.

The easiest way to think of design is:

ID card as the primary key

Index on other attributes

User (id_code competition

Id_md5 (index)

Name (index)

Birthday (index))

The index tree and row record structure at this time is as follows:

Id_code clustered index, associated row record

Other indexes, storing id_code attribute values

ID number id_code is a long string, and each index stores this value. In the case of large amount of data and precious memory, the limited buffer of MySQL will reduce the index and data stored, and the probability of disk IO will increase. Voiceover: at the same time, the disk space occupied by the index will also increase. At this point, a new id self-incrementing column with no business meaning should be added:

List as a clustered index with id self-increment, associate row records

Other indexes, storing id values

User (id competes for auto inc

Id_code (index)

Id_md5 (index)

Name (index)

Birthday (index))

In this way, the limited buffer can buffer more index and row data, the frequency of disk IO will be reduced, and the overall performance will increase. Summary (1) MyISAM indexes are stored separately from data, index leaves store pointers, and primary key indexes are not much different from ordinary indexes; (2) InnoDB clustered indexes and data rows are stored uniformly, clustered indexes store data rows themselves, and ordinary indexes store primary keys; (3) InnoDB does not recommend using too long fields as competition (at this time, you can join a self-key competition), MyISAM does not care.

I hope to answer the water friend's question.

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.