If a mysql DATA table is in a many-to-many relationship, will it be faster to add a 'id' primary key to the intermediate table than to not add a primary key?

For example, because a commodity table and an Attribute Table have many-to-many relationships, a commodity attribute intermediate table exists. CREATETABLEIFNOTEXISTSdslxs_goods_attr (goods_idmediumint (8) unsignedNOTNULLCOMMENT & #039; item id & #039;, attr_idmediumint (8) unsigne... for example, because a commodity table and an Attribute Table have many-to-many relationships, a commodity attribute intermediate table exists;
CREATE TABLE IF NOT EXISTS dslxs_goods_attr(
goods_idMediumint (8) unsigned not null comment 'item id ',
attr_idMediumint (8) unsigned not null comment 'attribute id ',
KEY goods_id( goods_id),
KEY attr_id( attr_id)
) ENGINE = InnoDB default charset = utf8 COMMENT = 'item attribute intermediate table ';
If you add idMediumint (8) unsigned not null AUTO_INCREMENT, and set it as the primary key ( id). If this is the case, you will not be able to perform big data testing quickly. If you have more experience, please let us know ..

Reply content:

For example, because a commodity table and an Attribute Table have many-to-many relationships, a commodity attribute intermediate table exists;
CREATE TABLE IF NOT EXISTSdslxs_goods_attr(
goods_idMediumint (8) unsigned not null comment 'item id ',
attr_idMediumint (8) unsigned not null comment 'attribute id ',
KEYgoods_id(goods_id),
KEYattr_id(attr_id)
) ENGINE = InnoDB default charset = utf8 COMMENT = 'item attribute intermediate table ';
If you addidMediumint (8) unsigned not null AUTO_INCREMENT, and set it as the primary key (id). If this is the case, you will not be able to perform big data testing quickly. If you have more experience, please let us know ..

The so-called KEY is only the alias of the index, and the index is the KEY to improving the query speed of the ing table. You have already set the indexes for these two fields. Now, you may need to add a foreign key to improve the stability of the table and the entire database. This is also the case for others. Adding a unique key will slightly increase the insertion burden, and certainly there will be no query based on this unique key, and there may be deletion based on this key (the possibility is very small, if you have to accelerate, add a joint index of the two, the insertion will be slow, the index file will become larger ).

This is basically the case. To avoid the misunderstanding that all tables must have a unique incrementing key.

I want some help.

Clustered index:
MySQL InnoDB will certainly create a clustered index to store the actual data rows and related key values in one partition. This also determines that a table can have only one clustered index, that is, MySQL does not store data rows in two places at a time.
1) InnoDB generally performs clustering based on the primary key value (primary key ).
2) If no primary key is created, a unique and not empty index column will be used as the primary key to become the clustered index of the table.
3) If none of the preceding conditions is met, InnoDB creates a virtual clustered index.

Advantages:
The advantage of clustered index is to improve data access performance. Clustered indexes store both indexes and data in the same B + tree data structure, and store the index columns and related data rows together. This means that when you access different rows of records on the same data page, the page has been loaded into the Buffer. When you access the page again, the access will be completed in the memory without accessing the disk. Different from the MyISAM engine, the index and data are not stored in one block and stored in different physical files. The index file is cached in key_buffer, and the index corresponds to the disk location, you have to access disk data through the disk location.

Disadvantages:
1) It is very expensive to maintain indexes, especially when a new row is inserted or the primary key is updated to the page split. We recommend that you use optimize table to OPTIMIZE the TABLE when a large number of new rows are inserted, because the row data that must be moved may cause fragmentation. Use exclusive tablespace to weaken fragments

2) because the table uses UUId as the primary key, data storage is sparse, which may cause the cluster index to be slower than the full table scan. Therefore, we recommend using auto_increment of int as the primary key.
3) if the primary key is large, the secondary index will be larger, because the leaf of the secondary index stores the primary key value; if the primary key value is too long, non-leaf nodes occupy more physical space.

From: http://blog.csdn.net/wyzxg/article/details/8779235