[toc] primary key index, non-primary key index, clustered index, non-clustered index, secondary index, secondary index, secondary index, secondary index

1. Divide by function

According to the function, there are four main indexes:

  • Normal index
  • Unique index
  • The primary key index
  • The full text indexing

Common index is the most basic index, this index does not have any constraints, its main meaning is to improve the efficiency of query.

Common indexes can be created as follows:

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
Copy the code

The name field is a plain index (the name of the index outside the parentheses and the field inside the index).

Unique indexes add constraints on data uniqueness on the basis of common indexes. Multiple unique indexes can exist in a table at the same time. Unique indexes are created in the following ways:

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL.PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
Copy the code

The name field is a unique index.

NOT NULL+UNIQUE; NOT NULL+UNIQUE; NOT NULL+UNIQUE; NOT NULL+UNIQUE;

There are two examples of how to create a primary key index, but I won’t list them here.

Elasticsearch is one of the most popular ways to create a full text index for your MySQL database. If you want to create a full text index for your MySQL database, you can create a full text index for Elasticsearch or Solr. Public account background reply es to get the tutorial link.

Full text indexing is also supported in MySQL:

  • Prior to MySQL 5.6, only the MyISAM storage engine supported full-text indexing.
  • MySQL 5.6 and later, MyISAM and InnoDB storage engines support full-text indexing.

Creating a full-text index also has requirements on the field type. A full-text index can be created only when the data type of a field is CHAR, VARCHAR, or TEXT.

MySQL full-text index initially only supports English, because English word segmentation is convenient; Chinese word segmentation is more troublesome, so the earliest MySQL full-text indexes do not support Chinese. Starting with MySQL5.7.6, ngram full-text parser was introduced to solve the word segmentation problem, and this word segmentation is valid for both MyISAM and InnoDB engines.

MySQL full-text index is not easy to use, so if you need it, you should go directly to Es.

Full-text indexes are created as follows:

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL.PRIMARY KEY (`id`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
Copy the code

The name field is the full-text index.

2. Partition by physical implementation

According to the physical implementation, indexes can be divided into two main categories:

  • Clustered indexes (also called “clustered indexes” by some)
  • Non-clustered index (also called “non-clustered index” by some)

2.1 Clustered Indexes

A clustered index can be sorted by the primary key (not required, depending on the situation). A clustered index can be sorted by the primary key (not required, depending on the situation). A clustered index can be sorted by the primary key (not required, depending on the situation).

In the clustered index shown below, the leaf node holds the data for each row.

In a clustered index, rows in a table are stored in order of the index, which is useful for finding rows. Only if the table contains a clustered index, the rows in the table are physically sorted and stored on disk by the value of the index column. There can only be one clustered index per table, simply because the rows themselves can only be stored in one order.

When we create a table based on the InnoDB engine, we create a clustered index, and each table has a unique clustered index:

  1. If the table has a primary key index defined, the primary key index is used as a clustered index.
  2. If the table has no primary key index defined, the first unique non-empty index of the table is used as the clustered index.
  3. If the table also does not have a unique non-empty index, then InnoDB internally generates a hidden primary key as the clustered index. The hidden primary key is a 6-byte column whose value increases as data is inserted.

As you can see from the above description, primary key index and clustered index are not the same thing, do not confuse!

The main advantage of clustered indexes is that they can be queried quickly. If you want to query a full row of data, using a non-clustered index often requires a return to the table, whereas using a clustered index can be done in one step.

Clustered indexes do have some disadvantages, though:

  1. Clustered indexes can reduce disk I/O times, which is a great advantage on traditional mechanical hard drives, but not on solid state drives or memory (sometimes database servers have large memory blocks to improve operation efficiency).
  2. When the clustered index is inserted, it is best to increase the primary key by itself, which is relatively fast. It can be inserted directly without involving leaf node splitting and other problems (there is no need to move other records). However, when inserting other non-autoincrement primary keys, it may be inserted between two existing data, which may lead to problems such as leaf node splitting and low insertion efficiency (moving other records). If the clustered index does not increment the primary key at the time of insertion, the insertion efficiency will be low.

2.2 Non-clustered indexes

Non-clustered indexes are also called secondary or secondary indexes. For non-clustered indexes, the database has a separate storage space for them. Select * from user where username=’javaboy’ (assuming username is not a clustered index) The leaf node of this B+Tree is not the complete row of data, but the primary key value. When the search is complete, we get the primary key value, and then search the B+Tree of the primary key index. Then we can get a complete row of data.

So if we use a non-clustered index in a query, we will search two B+ trees. The first time we search B+Tree and get the primary key value, we will search the clustered index B+Tree. This process is called table back.

A table can have only one clustered index, but can have multiple non-clustered indexes. When a clustered index is used, the data query efficiency is high, but when data is inserted, deleted, or updated, the efficiency is lower than that of a non-clustered index.

3. Summary

In general, database indexes can be classified in two ways: by function and by storage.

According to functions, it can be divided into four types:

  • Normal index
  • Unique index
  • The primary key index
  • The full text indexing

According to storage mode, it can be divided into two types:

  • Clustered index
  • Nonclustered index

There is a difference between each and there is a connection, I hope that the above can solve your doubts, welcome to leave a message to discuss any questions.