MySQL最左前缀索引

# 建立组合索引
CREATE TABLE t3
(
    id    INT NOT NULL,
    username CHAR(30) NOT NULL,
    age  INT NOT NULL,
    info VARCHAR(255),
    INDEX MultiIdx(id, username, info(100)) # 组合索引遵循最左前缀原则
);

# 走索引id和username的组合索引
EXPLAIN SELECT * FROM t3 WHERE id=1 AND username='zs'\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: MultiIdx
          key: MultiIdx
      key_len: 94
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

# 由于不存在username，因此只对id进行索引
EXPLAIN SELECT * FROM t3 WHERE id=1 AND info='man'\G; 

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: MultiIdx
          key: MultiIdx
      key_len: 4
          ref: const
         rows: 1
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

# 由于最左前缀id不存在，因此不走索引MultiIdx
EXPLAIN SELECT * FROM t3 WHERE username='zs' AND info='man'; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

# 即使索引组合是乱序，只要存在最左前缀MySQL就会对其进行索引组合优化
EXPLAIN SELECT * FROM t3 WHERE username='zs' AND id=1 AND info='man'; 

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: MultiIdx
          key: MultiIdx
      key_len: 397
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)