When the MySQL data table is a many-to-many relationship, will the intermediate table be faster than the primary key if the ' ID ' key is added?

For example, the commodity table and the attribute table, because it is many-to-many relationship, so there is a commodity attribute intermediate table;
CREATE TABLE IF not EXISTS dslxs_goods_attr(
goods_idMediumint (8) unsigned not NULL COMMENT ' Product ID ',
attr_idMediumint (8) unsigned not NULL COMMENT ' property id ',
KEY goods_id( goods_id),
KEY attr_id( attr_id)
Engine=innodb DEFAULT Charset=utf8 comment= ' commodity attribute intermediate table ';
If you add a new one to this table, idMediumint (8) unsigned not NULL auto_increment, and set as primary key primary key ( id)。 This will not be quick, no big data test, which has more experience please tell me.

Reply content:

For example, the commodity table and the attribute table, because it is many-to-many relationship, so there is a commodity attribute intermediate table;
CREATE TABLE IF not EXISTS dslxs_goods_attr (
goods_idMediumint (8) unsigned not NULL COMMENT ' Product ID ',
attr_idMediumint (8) unsigned not NULL COMMENT ' property id ',
KEY goods_id ( goods_id ),
KEY attr_id ( attr_id )
Engine=innodb DEFAULT Charset=utf8 comment= ' commodity attribute intermediate table ';
If you add a id mediumint (8) unsigned not NULL auto_increment in this table, and set the primary key primary key ( id ). This will not be quick, no big data test, which has more experience please tell me.

The so-called key is just an alias of the index, and the index is the key to increasing the speed of your mapping table, and you have set the index of both fields. Now all you have to do is add a foreign key, improve the stability of the table and the entire database, the other is the case, plus the unique key will slightly increase the insertion burden, and certainly there will be no query based on this unique key, perhaps there is a deletion based on this key (the probability is very small, not to speed up, add a combination of the two The insertion slows down and the index file becomes larger).

This is roughly the case, to jump out of all the tables to a unique increment key error.

To add, I hope some help.

Clustered index:
MySQL InnoDB is bound to establish a clustered index, the actual data rows and related key values are saved in a piece, which also determines that a table can only have a clustered index, that is, MySQL will not save the data row in two places at a time.
1) InnoDB is usually clustered according to primary key value (primary key)
2) If no primary key is created, a unique and non-empty index column is used as the primary key, which becomes the clustered index of this table
3) The above two conditions are not satisfied, InnoDB will create a virtual clustered index on its own

Advantages:
The advantage of clustered indexes is to improve data access performance. The clustered index saves both the index and the data in the same B + tree data structure, and simultaneously saves the indexed column with the related data rows. This means that when you access different row records of the same data page, you have loaded the page into buffer and accessed it again in memory, without having to access the disk. Unlike the MyISAM engine, which does not put the index and data in a piece, it is placed in a different physical file, the index file is cached in the Key_buffer, the index corresponds to the disk location, and the disk data is accessed through the disk location.

Disadvantages:
1) It is expensive to maintain indexes, especially when inserting new rows or when the primary key is updated to be paged (page split). It is recommended that after a large number of new rows are inserted, the table is optimized for the low-load time period through optimize tables because the row data that must be moved can be fragmented. Use the exclusive table space to weaken fragmentation

2) Table Because of the use of UUID as the primary key, so that the data storage sparse, this will appear clustered index may be more slow than the full table sweep surface, it is recommended to use the auto_increment of int as the primary key
3) If the primary key is larger, the secondary index will become larger because the leaf of the secondary index stores the primary key value, and the long primary key value causes the non-leaf node to occupy more physical space.

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

