MySQL 索引最左前缀原则

灰太狼 2023-01-03 14:29 283阅读 0赞

# MySQL 索引最左前缀原则 #

*  索引最佳左前缀法则：**带头大哥不能死、中间兄弟不能断**

### 1、准备数据 ###

#### 建表 ####

CREATE TABLE IF NOT EXISTS staff (
    	id INT PRIMARY KEY auto_increment,
    	name VARCHAR(50),
    	age INT,
    	pos VARCHAR(50) COMMENT '职位',
    	salary DECIMAL(10,2)
    );

#### 插入数据 ####

INSERT INTO staff(name, age, pos, salary) VALUES('Alice', 22, 'HR', 5000);
    INSERT INTO staff(name, age, pos, salary) VALUES('Bob', 22, 'RD', 10000);
    INSERT INTO staff(name, age, pos, salary) VALUES('David', 22, 'Sale', 120000);

### 2、测试&Explain分析 ###

#### 2.1 创建索引 ####

创建了一个基于 name, age, pos 三个字段的索引

CREATE INDEX idx_nameAgePos ON staff(name, age, pos);

#### 2.2 索引测试 ####

*  **Case\#1：只根据 name 字段来查询**

EXPLAIN SELECT * FROM staff WHERE name = 'Alice';

结果：

type=ref
    key=索引
    ref=const
    ken_len=53

--------------------

*  **Case\#2：只根据 name & age 字段来查询**

EXPLAIN SELECT * FROM staff WHERE name = 'Alice' AND age = 22;

结果：和 Case\#1 差不多，但是：

key_len=58
    ref=const, const

--------------------

*  **Case\#3：根据 name & age & pos 来查询**

EXPLAIN SELECT * FROM staff WHERE name = 'Alice' AND age = 22 AND pos = 'HR';

结果：索引仍然生效，同时，key\_len & ref 比 Case\#2 中的结果更丰富

--------------------

*  **Case\#4：根据 age & pos 来查询**

EXPLAIN SELECT * FROM staff WHERE age = 22 AND pos = 'HR';

结果：没有索引，全表扫描

--------------------

*  **Case\#5：根据 name & pos 来查询**

EXPLAIN SELECT * FROM staff where name = 'Alice' AND pos = 'HR';

结果：和 Case\#1 相同（说明 pos 字段没有用上索引）

--------------------

### 3、总体分析 ###

*  **`Case1，2，3`** 都用了上索引，且使用索引长度依次增加 **（key\_len=53,58,111 且 ref=1个const,2个const,3个const），符合最佳左前缀法则；**
 *  Case4 中**没有带头大哥**（火车头），于是，**全表扫描**；
 *  Case5 中只有 name 字段使用上了索引，**中间兄弟（中间车厢）age 断了，于是，后面的兄弟（车厢）pos 挂了；**

### 4、 总结 ###

**`最佳左前缀法则：带头大哥不能死、中间兄弟不能断`**

*  当 select \* from T where a = 1 and b = 3 的时候， 数据库系统可以直接从索引文件中直接二分法找到 A = 1 的记录，然后再 B = 3 的记录; 但如果你 where b = 3 则需要遍历这个索引表的全部
 *  mysql 建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：
    
     *  如果有一个 `2` 列的索引 (col1, col2)，则已经对 **(col1)、(col1, col2)** 上建立了索引；
     *  如果有一个 `3` 列索引 (col1, col2, col3)，则已经对 **(col1)、(col1, col2)、(col1, col2, col3)** 上建立了索引；

### 5、**索引底层原理** ###

*  `B+树`的数据项是复合的数据结构，比如 (name,age,sex) 的时候，**b+树是按照从左到右的顺序来建立搜索树的**
 *  比如当 (张三,20,F) 这样的数据来检索的时候，b+树会**优先比较 name来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；**
 *  但当 (20,F) 这样的没有 name 的数据来的时候，b+ 树就不知道第一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询 —> **`所以三个联合索引一个都不会走, 直接全局扫描`**
 *  比如当 (张三, F) 这样的数据来检索时，**b+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到**，然后再匹配性别是 F 的数据了， 这个是非常重要的性质，即索引的最左匹配特性。（这种情况无法用到联合索引）–> **`张三列的索引可以利用上, 由于中间兄弟断掉了, 所以F的索引也不会使用到`**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTg5OTgw_size_16_color_FFFFFF_t_70]

### 6、**mysql 查询优化器** ###

*  如果建的索引是 (name, cid)。而查询的语句是 cid=1 AND name=’小红’。为什么还能利用到索引？
    
     *  当按照索引中所有列进行精确匹配（“=” 或 “IN”）时，**索引可以被用到**，并且 type 为 const。理论上索引对顺序是敏感的，**但是由于 `MySQL 的查询优化器` 会自动调整 where 子句的条件顺序以使用适合的索引，所以 MySQL 不存在 where 子句的顺序问题而造成索引失效**

### 7、索引失效的注意事项 ###

*  **范围查询**
    
     *  mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。即，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引
 *  **like 语句的索引问题**
    
     *  如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀
     *  在 like “value%” 可以使用索引，但是 like “%value%” 不会使用索引，走的是全表扫描
 *  **不要在列上进行运算**
    
     *  如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描  
        例如 select \* from user where YEAR(birthday) < 1990  
        可以改造成 select \* from users where birthday <’1990-01-01′
 *  **索引不会包含有 NULL 值的列**
    
     *  只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL
 *  **覆盖索引的好处**
    
     *  如果一个索引包含所有需要的查询的字段的值，我们称之为覆盖索引。覆盖索引是非常有用的工具，能够极大的提高性能。因为，只需要读取索引，而无需读表，极大减少数据访问量

>  *  尽量选择区分度高的列作为索引，区分度的公式是 `count(distinct col)/count(*)`，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上，即平均 1 条扫描 10 条记录

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTg5OTgw_size_16_color_FFFFFF_t_70]: /images/20221119/d6f5c112ebab4545b4c161c19fb39098.png