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;

Test sql

First kind

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 kinds

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 kinds

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 kinds

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

It can be seen that only the query where contains the first field of the union index in sql can hit the index, which is called the leftmost matching feature of the index The use of the union index is independent of the order in which the where condition is written. mysql query analysis will optimize and use the index. However, to reduce the pressure of query analyzer, it is better to keep the order of index from left to right.

The data item of b + tree is a composite data structure. For example, when (name,age,sex), the b + tree establishes a search tree from left to right. For example, when (Zhang San, 20,F) such data is retrieved, the b + tree will first compare name to determine the next search direction. If the name is the same, then compare age and sex in turn, and finally get the retrieved data; but when (20,F) such data When there is no name data, the b + tree does not know which node to query in the first step. Because name is the first comparison factor when establishing the search tree, you must search according to name first to know where to query in the next step.