11. 知道什么是回表查询嘛 ?
当我们为一张表的name字段建立了索引 , 执行如下查询语句 :
select name,age from user where name='Alice'
那么获取到数据的过程为 :
- 根据name='Alice'查找索引树 , 定位到匹配数据的主键值为id=18
- 根据id=18到主索引获取数据记录 (回表查询)
先定位主键值，再定位行记录就是所谓的回表查询，它的性能较扫一遍索引树低
12. 知道什么叫覆盖索引嘛 ?
覆盖索引是指只需要在一棵索引树上就能获取SQL所需的所有列数据 , 因为无需回表查询效率更高
实现覆盖索引的常见方法是：将被查询的字段，建立到联合索引里去。
执行如下查询语句 : select name,age from user where name='Alice'
因为要查询 name和 age二个字段 , 那么我们可以建立组合索引
create index index_name_age on user(name,age)
那么索引存储结构如下 :
这种情况下, 执行select name,age from user where name='Alice' , 会先根据name='Alice', 找到记录 , 这条记录的索引上刚好又包含了 age 数据 , 直接把 Alice 77数据返回 , 就不会执行回表查询 , 这就是覆盖索引
13. 知道什么是左前缀原则嘛 ?
在mysql建立联合索引时会遵循左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到 ;
例如 : create index index_age_name_sex on tb_user(age,name,sex);
上述SQL语句对 age,name和sex建一个组合索引index_age_name_sex,实际上这条语句相当于建立了(age) , (age,name) , (age,name,sex)三个索引 .
select * from tb_user where age = 49 ; -- 使用索引 select * from tb_user where age = 49 and name = 'Alice' ; -- 使用索引 select * from tb_user where age = 49 and name = 'Alice' and sex = 'man'; -- 使用索引 select * from tb_user where age = 49 and sex = 'man'; -- 使用索引 , 但是只有 age 匹配索引 sex没有走索引 select * from tb_user where name = 'Alice' and age = 49 and sex = 'man' ; -- 使用索引 , 因为MySQL的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引 select * from tb_user where name = 'Alice' and sex = 'man' ; -- 不会使用索引
14. 什么情况下索引会失效 ?
MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度，编写合理化的SQL能够提高SQL的执行效率
- 不要在列上使用函数和进行运算
- 不要在列上使用函数，这将导致索引失效而进行全表扫描。
- 尽量避免使用 != 或 not in或 <> 等否定操作符
- 尽量避免使用 or 来连接条件
- 多个单列索引并不是最佳选择，建立组合索引代替多个单列索引, 可以避免回表查询
- 查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找
- 索引不会包含有NULL值的列
- 当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。
- like 语句的索引失效问题like 的方式进行查询，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询
15. 索引是越多越好嘛? 什么样的字段需要建索引, 什么样的字段不需要 ?
需要创建索引情况
- 主键自动建立主键索引
- 频繁作为查询条件的字段应该创建索引
- 多表关联查询中，关联字段应该创建索引 (on 两边都要创建索引)
- 查询中排序的字段，应该创建索引
- 频繁查找字段 , 应该创建索引
- 查询中统计或者分组字段，应该创建索引
不要创建索引情况
- 表记录太少
- 经常进⾏行行增删改操作的表
- 频繁更新的字段
- where条件里使用频率不高的字段
