Building tables

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;

Testing sql

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

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

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)

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)

summary

Thus, only a query in sql that contains the first field of the joint index can hit the index, which is called the left-most matching feature of the index. The use of joint indexes is independent of the order in which conditions are written, and mysql query analysis is optimized to use indexes. But to reduce the pressure on query analyzers, it's best to keep the index in left-to-right order.

When the data item of b + tree is a composite data structure, such as (name,age,sex), b + tree builds the search tree in the order of left to right. For example, when data such as (Zhang San, 20,F) are retrieved, b + tree will compare name first to determine the next search direction. If the name is the same, then compare age and sex in turn, the most important is age and sex. Then we get the retrieved data; but when (20,F) such data without name comes, the b + tree does not know which node to look for in the first step, because when building the search tree, name is the first comparison factor, and we must search according to name first to know where to go next.

Welcome to scan the two-dimensional code below, and continue to pay attention to:

Internet Engineer (id:phpstcn), we study together and make progress together