"Go" MySQL index leftmost matching principle understanding

Shen Jie

Links: https://www.zhihu.com/question/36996520/answer/93256153
Source: Know
Copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please specify the source.

This is your table structure, there are three fields, each of which is id,name,cid

CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  `cid` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `name_cid_INX` (`name`,`cid`),) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

Index facet: ID is a primary key, (NAME,CID) is a multicolumn index.
-----------------------------------------------------------------------------
Below is two queries that you have questions about:

EXPLAIN SELECT * FROM student WHERE   cid=1;

<img src= "Https://pic1.zhimg.com/d3086a6c81bb2c77796cfc2249b610bc_b.png" data-rawwidth= "1033" data-rawheight= "class=" Origin_image zh-lightbox-thumb "width=" 1033 "data-original=" https://pic1.zhimg.com/ D3086a6c81bb2c77796cfc2249b610bc_r.png ">

EXPLAIN SELECT * FROM student WHERE   cid=1 AND name=‘小红‘;

<img src= "Https://pic3.zhimg.com/53ab2cdea64b7e58e66c4ef86aa6b06a_b.png" data-rawwidth= "1033" data-rawheight= "class=" Origin_image zh-lightbox-thumb "width=" 1033 "data-original=" https://pic3.zhimg.com/ 53ab2cdea64b7e58e66c4ef86aa6b06a_r.png ">

your question is : The criteria for using an index for SQL queries is that you must adhere to the leftmost prefix principle, and why do the above two queries work with indexes?
--------------------------------------------------------------------------------------------------------------- ------------

Before I tell you the above question, I would like to add some knowledge, because I think your understanding of the index is narrow:
The explain result of your two queries above shows that the type of case used in the index is different. , you can observe the Type field in the explain results. Your query is:
1. Type:index
2. Type:ref

Explain:
Index: This type means that MySQL will scan the entire index. To use this type of index, there is no specific requirement for this index, as long as it is an index, or part of a composite index, MySQL may be scanned by the index type. However, the disadvantage is that the efficiency is not high, MySQL will be from the first data in the index to find the last data, until you find an index that matches the criteria.

so:For your first statement:

EXPLAIN SELECT * FROM student WHERE   cid=1;

The condition is cid=1, and the CID is part of the (name,cid) compound index, there is no problem, index scan mode can be done. Explain displays the result used by the index, which is the way of the index type.

--------------------------------------------------------------------------------------------------------------- ------------

Ref: This type means that MySQL will quickly find a qualifying index based on a particular algorithm, rather than a one by one scan of every data in the index, which is the so-called use of index queries that you normally understand to fetch data faster. To achieve this kind of search, the index is required, in order to achieve this algorithm can be quickly found, the index will meet the specific data structure. simply put, the data in the indexed field must be ordered in order for this type of lookup to take advantage of the index.

Some people who know it may ask that the index is not an ordered data structure. But the answer is not perfect, it is only for a single index, and the composite index of the situation some students may not understand.

The following is the composite index:
Taking the table's (NAME,CID) composite index as an example, its internal structure is simply said to be arranged as follows:
<img src= "https://pic2.zhimg.com/8c45fe417afbe97127e8c55fe1cd9395_b.png" data-rawwidth= "149" data-rawheight= "205" class= "content_image" width= "149" >mysql the rule to create a composite index is first to the leftmost of the composite index, That is, the data for the first Name field is sorted, based on the sort of the first field, and then the second CID field in the following order. In fact, it is equivalent to implementing a sort rule like order by name CID.

MySQL the rule for creating a composite index is to first sort the data on the leftmost side of the composite index, which is the first Name field, on the first field's sort basis, and then on the next Second CID field. In fact, it is equivalent to implementing a sort rule like order by name CID.

So: The first Name field is absolutely ordered, and the second field is unordered. So normally, using the second CID field for conditional judgment is not indexed, and of course, the index using index type may appear above. This is why MySQL emphasizes the most left-hand prefix principle.

So when do we get to use it?
Of course, the index data of the CID field is also ordered in order to use the situation, when is the order? The observation indicates that, of course, the CID is ordered in the case where the Name field is equivalent. Found no, observe the two name of the CID field named C is not ordered it? From the top down is 4 5.
This is the reason why the first index must first be used in the MySQL index rule to require a composite index to use the second index. (and the first index must be an equivalent match).
--------------------------------------------------------------------------------------------------------------- ------------
So for your SQL query:

EXPLAIN SELECT * FROM student WHERE   cid=1 AND name=‘小红‘;

There is no mistake, and the two indexed fields in the composite index can be used very well! Because the leftmost name field in the statement matches the equivalent, the CID is ordered and can be used to index.

you may ask : The index I built is (NAME,CID). And I query the statement is cid=1 and name= ' Little Red '; I first query the CID, then query name, not first from the left to check it?

Well, let me explain the question again: first of all, it is certain that the condition judgment turns into such name= ' Little Red ' and cid=1; The result of the last query is the same.
So the problem arises? Since the results are the same, in what order is the best way to query?

so, and that's where our MySQL query optimizer comes in, and the MySQL query optimizer will determine the order in which the SQL statement is to be executed in the most efficient way, and finally generate a real execution plan. So, of course, we can make the best use of the index when the query order is the most efficient, so the MySQL query optimizer will eventually query execution in this order.

"Go" MySQL index leftmost matching principle understanding