I believe that no one will deliberately create duplicate redundant index, many duplicate and redundant index is inadvertently created, today songo and everyone to clarify this problem.

Because we use MySQL in the daily process, basically use InnoDB engine, so the following discussion is mainly based on InnoDB engine B+Tree index to discuss, other hash index and full-text index are not discussed.

1. Duplicate with the federated index

In the previous article, Songo shared several articles about federated indexes, including the coverage indexes, prefix matching, and so on. Federated indexes are useful, but if you don’t understand federated indexes properly, you can create duplicate indexes like the following:

CREATE TABLE `user2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `user_index1` (`username`,`address`),
  KEY `user_index2` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

As you can see, there are two indexes created:

  • User_index1: This index contains two fields, username first and address second.
  • User_index2: This index contains one field username.

In MySQL, the like keyword can also be used as an index! In this article, Songo talks about the left-most matching principle of indexes, namely:

The (username,address) index can be used either as a federated index or as a single (username) index using the leftmost matching rule.

Therefore, it is unnecessary to create a separate index for the username field, which will slow down the process of adding, deleting, and editing.

For example, if you want to create a joint index between (username) and a particular long field, the search efficiency may be lower if you use username alone. See if a duplicate index needs to be created.

2. Add the primary key to the joint index

Take a look at this index:

CREATE TABLE `user2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `user_index` (`username`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

An index named user_Index contains two fields username and ID, where ID is the primary key.

What is a “back table” in MySQL? In this article, Songo tells us that indexes can be divided into clustered indexes and non-clustered indexes according to the physical storage mode.

What we call primary key indexes are Clustered indexes; All indexes except the primary key Index are called non-primary key indexes. Non-primary key indexes are also called Secondary indexes or Secondary indexes.

For both primary and non-primary key indexes, the data structure is B+Tree. The only difference is that the contents stored in the leaf nodes are different:

  • The leaf node of the primary key index stores a complete row of data.
  • Leaf nodes that are not primary key indexes store primary key values and index column values.

That’s the big difference between the two.

Since the primary key already exists in the leaf node, there is no primary key in the joint index.

Ok, a few small attention points, I hope to give friends inspiration.

References:

  • High Performance MySQL