“This is the 18th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021”

Usually when the design of a lot of times will use the joint index, generally rarely use a single field as an index, so that the index can be as little as possible to avoid disk occupation too much, add, delete and change the performance is poor.

Create a secondary index KEY (class_name, student_name, subject_nane) for the student in the table where the student’s class, name, subject, and grade are queried. The data structure of the index is as follows:

Contour matching

SELECT * FROM student_scope where class_name= ‘1 ‘AND student_name =’ xiaomin’ AND subject_nane =’ English ‘; Select * from table WHERE id=100; select * from table WHERE id=100; select * from table WHERE id=100; select * from table WHERE id=100;

Leftmost column matches

For example, if a joint index is a KEY (class_name, student_name, subject_nane), all columns in a WHERE condition will be indexed. If a joint index is a KEY (class_name, student_name, subject_nane), all columns will be indexed.

SELECT * FROM student_scope where class_name= '1' AND student_name ='Ming' AND subject_nane  ='English';
SELECT * FROM student_scope where class_name= '1' AND student_name ='Ming';
SELECT * FROM student_scope where class_name= '1' ;
Copy the code

The leftmost index will be used for matching. When executing the following SQL, the class_name field will be indexed and the Subject_Name will not be indexed.

SELECT * FROM student_scope where class_name= '1' AND subject_nane  ='English';
Copy the code

And the following statement will not go to the index at all

SELECT * FROM student_scope where student_name ='Ming' AND subject_nane  ='English';
SELECT * FROM student_scope where subject_nane  ='English';
Copy the code

Left-most prefix matching rule

SQL > select * from class (‘ 1 ‘, ‘1’, ‘1’); SQL > select * from class (‘ 1 ‘, ‘1’, ‘1’); And then you give a fuzzy match symbol at the end, which can also be looked up based on the index.

SELECT * FROM student_scope where class_name like '1%';
Copy the code

But executing data ending in classes does not go to the index because you do not know what the left prefix is and cannot look it up based on sort.

SELECT * FROM student_scope where class_name like Class '%';
Copy the code

Range lookup rule

If the fields of an SQL query take a range query based on an index column, as in the following SQL, the index will also be moved.

SELECT * FROM student_scope where class_name > = '1' AND class_name <'10 class';
Copy the code

But when the first field is the scope of the query, the index of the second field is can't walk, such as the following SQL, class_name index will go, student_name won't go behind the index.Copy the code

SELECT * FROM student_scope where class_name > = '1' AND class_name <'10 class' AND student_name ='Ming';
SELECT * FROM student_scope where class_name > = '1' AND class_name <'10 class' AND student_name > = 'Ming';
Copy the code

Equivalent matching + range matching rule

Student_name >= ‘Ming’; student_name >= ‘Ming’; class_name >= ‘Ming’; Student_name <‘ king ‘is not an index.

SELECT * FROM student_scope where class_name = '1' AND student_name > = 'Ming' AND student_name <'Cathy';
Copy the code

Generally write SQL statements, are held in joint indexes of the multiple fields on the left to contour matching + range search, or based on the most on the left side of the part of the field to the left prefix of the fuzzy matching, or based on the scope of the left-most fields for search, this accords with the rules of SQL statements to write, can be used to establish a good joint index.

How are indexes used for sorting

The SQL when we need to use the ORDER BY statement for sorting, seems to be BY index screening out a wave data, and then put data into the memory, or put in a temporary disk file, and then through the sorting algorithm according to the ORDER BY the back of the field to sort, then return the sort good data, When a large amount of data is sorted, it is not enough to use memory for sorting, so it is based on disk files (filesort), which is slow. SQL > select select * from table where order by (select * from table where order by (select * from table where order by (select * from table where order by (select * from table where order by))

SELECT * FROM student_scope ORDER BY class_name ASC,student_name ASC,subject_nane  ASC LIMIT 10;
SELECT * FROM student_scope ORDER BY class_name DESC,student_name DESC,subject_nane DESC LIMIT 10;
Copy the code

When sorting, try to sort the order by according to the joint index of the query, so that the data order in the joint index tree can be directly used to obtain the required data according to the order of the fields in the index number. But also to have certain restriction, because joint field values in the index in the index tree is raised to lined up, so in ORDER BY or nothing behind each field, is directly ORDER BY xx1, xx2, xx3, otherwise it is added DESC descending ORDER, ORDER BY xx1 DESC,xx2 DESC,xx3 DESC. If the ORDER BY column has both ascending and descending ORDER, it cannot be sorted BY index, as in the following SQL statement

SELECT * FROM student_scope ORDER BY class_name DESC,student_name ASC,subject_nane DESC LIMIT 10;
Copy the code

How do groups use indexes

Select * from GROUP BY; select * from GROUP BY; select * from GROUP BY; select * from GROUP BY; select * from GROUP BY; select * from GROUP BY You can then perform an aggregate function for each set of data.