Shulou(Shulou.com)06/02 Report--

This article mainly explains the "MySQL index how to achieve paging exploration", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL index how to achieve paging exploration" bar!

Paging Exploration Table structure for MySQL Index Optimization

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 Implementation of 450003 pieces of data limit paging

Like select * from demo limit 9000010; considering returning to the table, mysql simply chooses a full table scan.

Instead of counting 10 items directly from line 90000, mysql counts 90010 lines from the first leaf node.

Case one

For the above figure, when the id is continuously self-increasing, you can use the primary key to filter out the data after id=90000. Because the index of the primary key is a B + tree structure, it is ordered.

Case two

Sort by name, and then find 10 rows from row 90,000. although name is an index, the columns of select are not saved on the index_union index tree.

So it also involves returning the table, so mysql directly selects the leaf node of the primary key index tree, sorts more than 400,000 data according to name, and then calculates 90000 rows + 10 rows.

Optimization method: use subqueries to solve the most time-consuming sorting and table return problems. If the federated index tree has the primary key id,order by name, it can make full use of the whole index of name, age, and position because the sort of the leftmost column is determined, and so are the other two columns age and position.

Sorted, through the Extra field can also be sorted using an index tree.

The outermost query is associated based on the primary key, so it can be almost ignored. 10: 10 because id is the primary key, you can scan 10 items of data from the temporary table directly.

Thank you for your reading, the above is the content of "how to achieve paging exploration of MySQL index". After the study of this article, I believe you have a deeper understanding of how to achieve paging exploration of MySQL index, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.