MySQL index optimization: paging exploration detailed introduction

MySQL Index Optimization Paging Exploration

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 '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 'Work card number',
  PRIMARY KEY (`id`),
  KEY `index_union` (`name`,`age`,`position`)
) ENGINE=InnoDB AUTO_INCREMENT=450003 DEFAULT CHARSET=utf8;

450003 records

Limit paging execution status

Like select * from demo limit 90000,10; considering the table return, MySQL simply chooses to scan the entire table.

MySQL does not count 10 items directly from the 90000th row, but starts counting from the first leaf node and counts 90010 rows.

Case 1

For the above figure, when the id is continuously incremented, the primary key can be used to filter out the data after id=90000. Because the primary key index is a B+ tree structure, it is ordered.

Case 2

First sort by name, and then find 10 rows starting from row 90000. Although name is an index, the selected column is not saved in the index_union index tree.

Therefore, it will involve returning to the table, so MySQL directly chooses to scan the leaf nodes of the primary key index tree, first sorts more than 400,000 data according to name, and then calculates 90,000 rows + 10 rows.

Optimization method: Use subqueries to solve the most time-consuming sorting and table return problems. The joint index tree stores the primary key id. If you order by name, you can make full use of the entire index of name, age, and position because the sorting of the leftmost column is determined, and the sorting of the other two columns, age and position, is actually

The sorting is done, and the index tree can also be used for sorting through the Extra field.

The outermost query is related by primary key, so it can be almost ignored. 10+10 Because id is the primary key, you can directly scan 10 records in the temporary table.

This is the end of this article on the detailed introduction of MySQL index optimization and paging exploration. For more relevant MySQL paging exploration content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!