mysql索引（三）

落日映苍穹つ 2023-03-03 06:28 29阅读 0赞

## 离散度 ##

*  公式：count(dinstic(column\_name)) : count(\*)
 *  离散度越低，越不适合创建索引  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzODc1Nzg0NjIy_size_16_color_FFFFFF_t_70]  
    表中的数据一共**120W**，通过**EXPLAIN**语句分析，在**address**这个字段上创建索引，还是需要扫描约**100W**次，与全表扫描的开销差不多，没什么意思。
 *  所以不是每个字段都需要创建索引，就算创建了索引对查询效率的提升也不大，反而会浪费存储的空间（因为索引是一种数据结构，是存放在磁盘上面的）。

## 联合索引最左匹配 ##

1.  语法：  
    ALTER TABLE emp add INDEX`empidx_name_phone`(`name`,`phone`);
2.  根据联合索引最左匹配原则以下哪些sql会使用索引  
    （1）EXPLAIN SELECT \* FROM emp WHERE `name` = ‘张三’ and phone = ‘13899998888’;  
    （2）EXPLAIN SELECT \* FROM emp WHERE phone = ‘13899998888’ AND `name` = ‘张三’;  
    （3）EXPLAIN SELECT \* FROM emp WHERE `name` = ‘张三’ ;  
    （4）EXPLAIN SELECT \* FROM emp WHERE phone = ‘13899998888’;
3.  答：（1），（2），（3）都会使用索引
4.  为什么唯独（4）没有使用索引，明明也创建的索引  
    因为联合索引是一种数据结构，必须要根据创建索引的顺序去匹配，中间不能中断。很明显在语法中可以看见先创建的索引是’name’后面是‘phone’，（4）直接查询的是‘phone’，跳过了‘name’，那么根据联合索引的最左匹配原则，首先匹配的是左边，‘phone’匹配不到。
5.  为什么（2） 也是先匹配的 phone，还是走的索引  
    因为mysql存在一个优化器，执行sql的时候这条sql被优化了，所以不受影响。
6.  特点：必须从左边开始连续，不能中断。

## 什么是回表？ ##

主键索引查询 where id =1，查询走的是主键索引的b+tree、  
辅助索引查询 where name = ‘张三’ 查询走的是辅助索引的b+tree

*  select \* from emp where name = ‘张三’  
    相当于name = 张三走了一遍辅助索引，因为是 \* 号，查询了一些辅助索引数据结构中不存在的数据，所以又去扫描了一遍主键索引，这种多扫描了一遍的操作叫做**回表**

## 什么是索引覆盖 ##

*  select name from emp where name = ‘张三’  
    相当于辅助索引数据结构里面已经包含了name这个数据，就不需要再去主键索引中再扫描一遍，这种少扫描了一遍的操作叫做**索引覆盖**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzODc1Nzg0NjIy_size_16_color_FFFFFF_t_70]: /images/20230208/d7b6ce1f4a874eaf8c7eda438438efed.png