MySQL index leftmost principle example code

Preface

I was recently reading about MySQL indexes. When I saw the combined index, I found a leftmost principle. I studied it in depth by searching for relevant information. Let's take a look at the detailed introduction.

Create a table

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

The first

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

The second

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

The third

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)

The fourth

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

It can be seen from this that only queries in which the where clause of SQL contains the first field of the joint index can hit the index. This is called the leftmost matching feature of the index. The use of joint indexes has nothing to do with the order in which the where conditions are written. MySQL query analysis will be optimized and the index will be used. However, to reduce the pressure on the query analyzer, it is best to be consistent with the left-to-right order of the index.

The data items of the b+ tree are composite data structures. For example, when (name, age, sex) is used, the b+ tree builds the search tree in order from left to right. For example, when data such as (Zhang San, 20, F) is retrieved, the b+ tree will first compare the name to determine the next search direction. If the names are the same, the age and sex will be compared in turn to finally get the retrieved data. However, when data such as (20, F) without a name is found, the b+ tree does not know which node to check in the first step, because the name is the first comparison factor when the search tree is established. It is necessary to search based on the name first to know where to query next.

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.