About the differences between MySQL database primary key and index

I. What is an index?
Indexes are used to quickly look for records with specific values, and all MySQL indexes are saved as B-trees. If there is no index, MySQL must start scanning all records of the entire table from the first record until it finds a record that meets the requirements. The higher the number of records in the table, the higher the cost of this operation. If an index has been created on a column that is a search condition, MySQL can quickly get to where the target record is without scanning any records. If a table has 1000 records, finding records by index is at least 100 times times faster than sequential scan records.

Ii. Types of indexes
MySQL offers a variety of index types to choose from:

Normal index
This is the most basic type of index, and it has no limitations such as uniqueness. Normal indexes can be created in the following ways:
Create indexes, such as the name of the CREATE INDEX < index > on tablename (List of columns);
Modify the table, such as ALTER TABLE TableName ADD index [name of index] (list of columns);
Specify an index when creating a table, such as CREATE TABLE TableName ([...], index [name of indexed] (List of columns));

Uniqueness Index
This index is basically the same as the previous "normal index", but there is one difference: all the values of an indexed column can only occur once, that is, they must be unique. A unique index can be created in the following ways:
Create indexes such as the name of the Create UNIQUE Index < index > on tablename (List of columns);
Modify the table, such as ALTER TABLE TableName ADD UNIQUE [index name] (List of columns);
Specify indexes when creating tables, such as CREATE TABLE TableName ([...], UNIQUE [index name] (List of columns));

Primary key
The primary key is a unique index, but it must be specified as "PRIMARY key". If you've ever used a auto_increment type column, you're probably already familiar with concepts like the primary key. The primary key is typically specified when creating the table, such as "CREATE TABLE TableName ([...], PRIMARY KEY (List of columns)"; ”。 However, we can also add the primary key by modifying the table, such as "ALTER table tablename Add PRIMARY key (List of columns); ”。 There can be only one primary key per table.

Full-Text Indexing
MySQL supports full-text indexing and full-text retrieval starting from version 3.23.23. In MySQL, the index type of the full-text index is fulltext. A full-text index can be created on a varchar or text-type column. It can be created by the CREATE TABLE command or by the ALTER TABLE or CREATE INDEX command. For large datasets, it is faster to create a full-text index by using the ALTER TABLE (or CREATE INDEX) command than to insert the record into an empty table with a full-text index. The following discussion in this article no longer involves full-text indexing, see MySQL documentation for more information.

Disadvantages of the Index
So far, we've been talking about the advantages of indexes. In fact, indexes also have drawbacks.

First, the index takes up disk space. Usually, this problem is not very prominent. However, if you create an index of each possible combination of columns, the index file volume will grow much faster than the data file. If you have a large table, the size of the index file may reach the maximum file limit allowed by the operating system.

Second, indexes can slow down operations that require writing data, such as delete, update, and insert operations. This is because MySQL not only writes the change data to the data file, but it also writes the changes to the index file.

the main differences between indexes and primary keys are :
A primary key is a constraint, and a unique index is an index, and the two are essentially different.

The primary key must be created with a unique index, and the uniqueness index is not necessarily the primary key.

The uniqueness index column allows null values, while primary key columns are not allowed to be null.

When the primary key column is created, it is already default to a null value + unique index.

A primary key can be referenced by another table as a foreign key, and a unique index cannot.

A table can create a maximum of one primary key, but multiple unique indexes may be created.

Primary keys are more suitable for unique identities that are not easily changed, such as auto-increment columns, Social Security numbers, and so on.

In RBO mode, the primary key's execution plan priority is higher than the unique index. Both can improve the speed of queries.

--Create a table that contains only primary keys and unique indexes

CREATE TABLE Test

(PrimaryKey VARCHAR2 (20),

UniqueKey VARCHAR2 (20)

);

--Create a primary key and a unique index, respectively, with different syntax

ALTER TABLE test ADD CONSTRAINT test_primarykey PRIMARY KEY (PrimaryKey);

CREATE UNIQUE INDEX test_uniquekey on test (UniqueKey);

--Two index names can be seen in user_indexes

SELECT table_name,table_type,index_name,index_type,uniqueness

From User_indexes

WHERE table_name= ' TEST ';

--You can see two index field names in User_ind_columns

SELECT table_name,index_name,column_name,column_position

From User_ind_columns

WHERE table_name= ' TEST ';

--Only the PRIMARY KEY constraint name can be seen in user_constraints

SELECT Table_name,constraint_name,constraint_type

From User_constraints

WHERE table_name= ' TEST ';

---only the PRIMARY KEY constraint field name can be seen in User_cons_columns

SELECT table_name,constraint_name,column_name,position

From User_cons_columns

WHERE constraint_name in (SELECT constraint_name

From User_constraints

WHERE table_name= ' TEST ');

--Adds a non-null constraint to a unique index

ALTER TABLE test MODIFY uniquekey not NULL;

---only the PRIMARY KEY constraint name and the non-null constraint name can be seen in user_constraints

SELECT Table_name,constraint_name,constraint_type

From User_constraints

WHERE table_name= ' TEST '

---only the PRIMARY KEY constraint field name and the non-null constraint field name can be seen in User_cons_columns

SELECT table_name,constraint_name,column_name,position

From User_cons_columns

WHERE constraint_name in (SELECT constraint_name

From User_constraints

WHERE table_name= ' TEST ')

About the differences between MySQL database primary key and index