About how to automatically add a primary key in the index of MySQL InnoDB

(I) principle:

As long as the index field defined by the user contains the field in the primary key, this field will not be automatically added to the index by InnoDB.
However, if the index field does not fully contain the primary key field, InnoDB adds the remaining primary key fields to the end of the index.


(Ii) Example

Example 1:

CREATE TABLE t (  a char(32) not null primary key,  b char(32) not null,  KEY idx1 (a,b),  KEY idx2 (b,a)) Engine=InnoDB;

Idx1 and idx2 indexes have the same internal size and no difference.


Example 2:

CREATE TABLE t (  a char(32) not null,  b char(32) not null,  c char(32) not null,  d char(32) not null,  PRIMARY KEY (a,b)  KEY idx1 (c,a),  KEY idx2 (d,b)) Engine=InnoDB;

In this table, InnoDB will automatically complete the primary key dictionary. idx1 actually stores (C, A, B) internally, and idx2 actually stores (D, B, A) internally)
However, the automatically added field and server layer are unknown, so the MySQL optimizer does not know the existence of this field. If you have a query:

   SELECT * FROM t WHERE d=x1 AND b=x2 ORDER BY a;

In fact, the idx2 (D, B, A) in the internal storage can make this query completely indexed, but the server layer does not know,
Therefore, the MySQL Optimizer may select idx2 (D, B) to filter and sort field a, or directly use PK scan to avoid sorting.

If we define the table structure as key idx2 (D, B, A), MySQL will know that all the three fields (D, B, A) are indexed,
In addition, InnoDB found that the user-defined index contains all primary key fields, and no more storage space is added.


(Iii) Suggestions

Therefore, we sincerely recommend that you add the primary key field and,
There is no loss, but it may surprise you.