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

Optimization of MySQL performance, mainly from the index optimization, this article mainly explains the compound index with all and the most left prefix rule to optimize MySQL.

Compound indexes are all used

MySQL > select * from table where multiple columns are indexed; Compound index all-use means that the compound index contains several fields, and it is best to use all of them in the query, and strictly follow the index order, so that the query efficiency is the highest. Of course, the actual use should be analyzed according to the specific situation, the above is just an ideal situation.

Let’s actually do it:

SQL script:

CREATE TABLE `people` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(4) DEFAULT NULL,
  `class` varchar(10) DEFAULT NULL,
  `birthday` date DEFAULT NULL.PRIMARY KEY (`Id`),
  KEY `idx_name_age_sex_class` (`name`, `age`, `sex`, `class`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1026 DEFAULT CHARSET=utf8;
Copy the code

Then 1024 were inserted

INSERT INTO `test`.`people` (`name`, `age`, `sex`, `class`, `birthday`) VALUES(' Zhang SAN ', '17', 'MALE ', '1', '2021-03-02');
Copy the code

And the article 1.

INSERT INTO `test`.`people` ( `name`, `age`, `sex`, `class`, `birthday`) VALUES(' Li Si ', '25', 'female ', '2', '2021-02-24');

Copy the code

Comparison of different query conditions:

EXPLAIN SELECT * from people where name="Bill"
Copy the code

=> Query time: 0.027s Key_len: 33

EXPLAIN SELECT * from people where name="Bill" and age="25"
Copy the code

=> Query time: 0.026s Key_len: 38

EXPLAIN SELECT * from people where name="Bill" and age="25" and sex="Female" 
Copy the code

=> Query time: 0.024s Key_len: 51

EXPLAIN SELECT * from people where name="Bill" and age="25" and sex="Female" and class="2"
Copy the code

=> Query time: 0.023s KEY_len: 84

Leftmost prefix rule

The left-most prefix is also associated with compound indexes. The indexes must be indexed in the same order as when they were created. Otherwise, the created compound indexes will not be used. Write sqlu, statement from left to right.

Or take the example above:

EXPLAIN SELECT * from people where name="Bill" and age="25" and sex="Female" and class="2"
Copy the code

If we change our query to the following way, will we still use the created composite index?

EXPLAIN SELECT * from people where age="25" and sex="Female" and class="2"
EXPLAIN SELECT * from people where sex="Female" 
EXPLAIN SELECT * from people where  class="2" and name="Zhang" and class="1"
EXPLAIN SELECT * from people where name="Zhang" and sex="Female" and class="2"
EXPLAIN SELECT * from people where  class="2" and name="Zhang" and sex="Female"
Copy the code

The answers are 1, 2, 3 no, 4, 5 yes.

Let’s look at 1, 2, 3:

Let’s look at 4 and 5: We can see that and derive thatLeftmost prefix ruleIs this:The eldest brother in the lead shall not die, and the middle brother shall not be broken

The above is today’s share, if there are mistakes, please forgive me, many correction! Thank you ~