After analyzing the index problem in the last article, we know the function and structure of the index. This article will continue to analyze some types of knowledge points of the index

Index is divided into clustered index and non-clustered index, what is the difference between the two

Last B + tree structure as we know, leaf nodes storing a row data, this is an important characteristic of distinguish between the two, as shown in figure, the figure on the left is the InnoDB primary key index and secondary indexes, the right to MyISAM index, we know, for the MyISAM, index of leaf node contains field value and pointer to data page data lines of logic, In other words, the data of a table is separated from the index, and the primary key index is the same as the secondary index. When searching for data, you need to further search according to the row pointer stored in the index. For InnoDB index have a primary key index and average index respectively, clustering index, according to the primary key to build a leaf node is the row data, average index of the primary key and index of fields, that is to say, when searching in the select if it is the primary key index, you do not need to back to the table query, can return to check the data directly, If a common index query is used, you do not need to query back to the table if the queried field is exactly the field itself. If the queried field is not in the index field itself, you need to query other fields back to the table based on the primary key. Mysql (select *) itself will affect the efficiency of the query, it is best to only check the required field, overwrite the index, avoid back to the table

Here’s a look at the common index types

  • Primary key and unique index

For a table, there can be multiple unique indexes, but only one primary key. A primary key is a unique index, but a unique index does not have to be a primary key. A unique index can be empty, but only one value can be empty. A primary key cannot be empty

  • Joint index

We take (a, B, C) as an example to build a joint index, which actually builds (a),(a,b),(a, B, C) three indexes. The joint index has the principle of “left-most prefix”. A range query (>, <, between, like) stops matching. Let’s take a look at some common questions.

For example, create an index for (a,b,c)

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` varchar(15) NOT NULL,
  `b` varchar(15) NOT NULL,
  `c` varchar(15) NOT NULL,
  `d` varchar(15) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Copy the code
  1. Whether the index will be used if the order of A, B, and C is reversed

EXPLAIN SELECT * FROM test WHERE c='test' AND a='test' AND b='test'

You can see that MySQL automatically adjusts the columns in the query without affecting the use of indexes

  1. If you abandon the leftmost rule, that is, do not check a, directly query B, C

EXPLAIN SELECT * FROM test WHERE c='test' AND b='test' Note Indexes cannot be triggered if the first query column is not used

Based on the above results, how should we now build indexes when we backward some queries

  1. SELECT * FROM test WHERE a > 1 and b = 2

If the above query is created for (a, b), only a can be used. If the above query is created for (b, a), the MySQL optimizer will adjust it to use the index

  1. EXPLAIN SELECT * FROM test WHERE a IN (1,7,9) and b > 1

(a,b), (a, B), (a, B), (a, B)

  1. SELECT * FROM test WHERE a > 1 and b = 2 and c > 3;

For the above statement, it does not matter whether to establish (b,c) or (b,a), because we cannot cover all of them, so it depends on the usage

  1. Think of any indexing and sorting problem
EXPLAIN SELECT * FROM test WHERE  a =1 ORDER BY b;
EXPLAIN SELECT * FROM test WHERE  a >1 ORDER BY b;
Copy the code

(a, b) is established on the index, when a = 1, b relatively orderly, and can avoid ordering again, and the second statement is a range query, within the scope of the b value is disorderly, no need to (a, b) index

Make progress together, learn and share

Welcome everyone to pay attention to my public number [write code xiao Yang], related articles, learning materials will be updated in it, sorting out the data will be placed in it.

If you think it’s written well, click a “like” and add a follow! Keep updating!! Pay attention, don’t get lost, Xiao Yang take you to the highway