mysql Index Leftmost Principle Instance Code

Preface

Recently in the view of MySQL index knowledge, see the combination of index, there is a leftmost principle, through the search for relevant information in-depth study under, the following words are not much to say, to see a detailed introduction

Table building

CREATE TABLE `user` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(10) DEFAULT NULL,
 `sex` tinyint(1) DEFAULT NULL,
 `age` tinyint(2) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `Index_user` (`name`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

Test sql

Type 1

mysql> explain SELECT * FROM `user` where name="tom" \G
*************************** 1. row ***************************
   id: 1
 select_type: SIMPLE
  table: user
 partitions: NULL
   type: ref
possible_keys: Index_user
   key: Index_user
  key_len: 43
   ref: const
   rows: 1
  filtered: 100.00
  Extra: NULL

Type 2

mysql> explain SELECT * FROM `user` where age=18 and name="tom" \G
*************************** 1. row ***************************
   id: 1
 select_type: SIMPLE
  table: user
 partitions: NULL
   type: ref
possible_keys: Index_user
   key: Index_user
  key_len: 45
   ref: const,const
   rows: 1
  filtered: 100.00
  Extra: NULL

Type 3

mysql> explain SELECT * FROM `user` where age=18 \G
*************************** 1. row ***************************
   id: 1
 select_type: SIMPLE
  table: user
 partitions: NULL
   type: ALL
possible_keys: NULL
   key: NULL
  key_len: NULL
   ref: NULL
   rows: 3
  filtered: 33.33
  Extra: Using where
1 row in set, 1 warning (0.00 sec)

Type 4

mysql> explain SELECT * FROM `user` where name="tom" and age=18 \G
*************************** 1. row ***************************
   id: 1
 select_type: SIMPLE
  table: user
 partitions: NULL
   type: ref
possible_keys: Index_user
   key: Index_user
  key_len: 45
   ref: const,const
   rows: 1
  filtered: 100.00
  Extra: NULL
1 row in set, 1 warning (0.00 sec)

Summarize

Thus, only queries in sql that contain the first field of the federated index in where can hit the index, which is called the leftmost matching feature of the index. The use of federated indexes is independent of the order in which mysql conditions are written, and mysql query analysis is optimized to use indexes. However, to reduce the pressure on the query analyzer, it is best to use the left-to-right order of the index.

The data items of b + tree are composite data structures. For example, when (name, age, sex), b + tree establishes search trees from left to right. For example, when data such as (Zhang 3, 20, F) are retrieved, b + tree will compare name first to determine the search direction of the next step. If name is the same, then compare age and sex in turn, and finally get the retrieved data. However, when there is no name data like (20, F), the b + tree does not know which node to look up in the first step, because name is the first comparison factor when the search tree is established, and it is necessary to search according to name before knowing where to look up in the next step.