Primary key index of MYSQL index _mysql

In MySQL, what is the difference between a primary key index and a secondary index, respectively?
Last share we introduced the difference between a clustered index and a nonclustered index, and this time we continue to introduce the difference between primary key indexes and secondary indexes.

1, PRIMARY key index

Primary key index, referred to as primary key, the original is primary key, consisting of one or more columns, used for uniqueness to identify a record in the datasheet. A table can have no primary key, but only one primary key, and the primary key value cannot contain null.

In MySQL, the primary key design of the InnoDB datasheet we usually follow several principles:

1, the use of a no business use of the self-added attribute column as the primary key;
2, the primary key field value is always not updated, only add or delete two operations;
3, do not select the type that will be dynamically updated, such as the current time stamp.

There are several advantages to doing this:

1, the new data, as the primary key value is sequential growth, InnoDB page split probability reduced; You can refer to the previous sharing "[MySQL FAQ] Series-why InnoDB table to recommend the use of self-added key";
2, business data changes, do not modify the primary key value, physical storage location changes in the probability of reducing the InnoDB page in the probability of fragmentation is also reduced.
MyISAM table because it is the heap organization table, the primary key type design can not be so exquisite.

2. Secondary index

The secondary index, which is the index of our regular reference, is the original secondary KEY. Secondary indexes can also be subdivided into unique indexes, not unique indexes.

A unique index should actually be called a uniqueness constraint, and its role is to avoid duplication of one or more column values, which is a binding index.

3. Difference between primary key index and secondary index

In the MyISAM engine, a unique index has no intrinsic difference to the primary key index except that the key value allows null. That is, in the MyISAM engine, a unique index of null values is not allowed, which is essentially the same as the primary key index.

In the InnoDB engine, the difference between primary key index and secondary index is great. The primary key index is selected as the clustered index, and there is no essential difference in storage between the unique index and the normal secondary index, except for uniqueness constraints.

From query performance, the query performance of the primary key index in the MyISAM table and the unique index that does not allow Null is equivalent, and the conversion process from the secondary index to the primary key index is required more than once in the InnoDB table through a unique index query. InnoDB tables are more expensive to find based on common indexes because each time you retrieve a result, you need to retrieve at least one more time to confirm that there are more qualifying results, and that primary key indexes and unique indexes do not need to be done.

After testing, 1 million rows of data MyISAM do random retrieval (integer type), primary key and the efficiency of the unique index is basically the same, the retrieval efficiency of common index is slower than 30%. With the InnoDB table, the unique index is about 9% slower than the primary key index, and the normal index is about 50% slower than the primary key index.

About MySQL in all aspects of what you want to know, you can leave a message back, I will choose some hot topics to share. At the same time, I hope you can forward a lot of reading volume is the old leaf to continue to share the best help, thank you:)