The difference between the mysql index, primary key, specific index, and standard index

Indexes are a special type of file (the index on the InnoDB data table is an integral part of the table space), they contain reference pointers to all records in the data table.

The only task of an ordinary index (an index defined by the keyword KEY or INDEX) is to speed up data access.

Ordinary indexes allow the indexed data column to contain duplicate values. If you can determine that a data column will only contain values ​​that are different from each other, you should use the keyword UNIQUE to define it as a unique index when creating an index for this data column. In other words, the unique index can guarantee the uniqueness of data records.

The primary key is a special unique index. Only one primary key index can be defined in a table. The primary key is used to uniquely identify a record and is created using the keyword PRIMARY KEY.
Indexes can cover multiple data columns, such as index like INDEX (columnA, columnB), which is a joint index.

Primary keys and combined into a composite primary key primary
composite primary key refers to your primary key table contains more than one field.
For example;
create table test
(
name varchar(19),
id number,
value varchar(10),
primary key (id,name)
)
The combination of the id and name fields above is the composite primary key of your test table (if one is single When indexing the field, the id on the left will have an index)
It appears because your name field may have the same name, so add the ID field to ensure the uniqueness of your record
Under normal circumstances, the field length and number of fields of the primary key should be as small as possible

.As the name implies, multiple primary keys are combined to form a primary key combination, which is reflected in the union.
(The primary key is unique in principle. Don’t be troubled by the unique value.)

Indexes can greatly improve the speed of data query, but it will reduce the speed of inserting, deleting, and updating tables, because when performing these write operations, you must also operate Index file.

Simple example
Primary key A and primary key B form a joint primary key
The data of primary key A and primary key B can be exactly the same (difficult, it doesn’t matter), the joint is that the joint primary key formed by primary key A and primary key B is unique.
In the following example, the data of primary key A is 1, and the data of primary key B is also 1. The combined primary key is actually 11. This 11 is the unique value, and the unique value of 11 is definitely not allowed. (This is a many-to-many relationship)
Primary key A data Primary key B data
1 1
2 2
3 3
The combined primary key value of primary key A and primary key B is at most
11
12
13
21
22
23
31
32
33

I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.