Detailed introduction to paging exploration of MySQL index optimization

Source of the article ： Learn through http://www.bdgxy.com/

??MySQL?? Paging exploration of index optimization

Table structure

CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT ' full name ',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT ' Age ',
  `position` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT ' Position ',
  `card_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT ' Job card No ',
  PRIMARY KEY (`id`),
  KEY `index_union` (`name`,`age`,`position`)
) ENGINE=InnoDB AUTO_INCREMENT=450003 DEFAULT CHARSET=utf8;

450003 Data

limit Paging execution

image select * from demo limit 90000,10; Considering the back table , therefore mysql Simply choose full table scanning .

mysql Not directly from the third party 90000 Line start calculation 10 strip , Instead, start counting from the first leaf node , Calculation 90010 That's ok .

Case a

For the above figure , When id It's a time of continuous self increase , You can use the primary key to filter out id=90000 Later data . Because the index of the primary key is B+ Tree structure , It's orderly .

Case 2

First according to name Sort , And then from the 90000 Line up to find 10 That's ok , although name It's the index , but select Of is listed in index_union There is no save in the index tree .

So it will also involve the back table , therefore mysql Directly select the leaf node of the primary key index tree , First the 40 According to name Arrange order well , And then calculate 90000 That's ok +10 That's ok .

An optimization method ： Using sub query to solve the most time-consuming sorting and back to table problems , The joint index tree species have a primary key id,order by name If you can name、age、position The entire index is fully used because it determines the sort of the leftmost column , The other two columns age、 and position Actually, too.

It's in order , adopt Extra Fields can also be sorted using an index tree .

The outermost query is associated according to the primary key , So you can almost ignore .10+10 because id It's the primary key , You can take the temporary watch directly 10 Data to scan .

This is about MySQL That's all for the detailed introduction of paging exploration of index optimization , More about MySQL For pagination exploration content, please search rookie tutorial www.piaodoo.com Previous articles or continue to browse the relevant articles below. I hope you can support rookie tutorials in the future www.piaodoo.com！