乐趣区

关于面试:深入浅出mysql索引总结下-一文就OK

jiezi

目录

  • 汇集索引和非汇集索引

    • 汇集索引
    • 汇集索引应用场景
    • 非汇集索引
    • 非汇集索引应用场景
  • 什么是回表
  • 笼罩索引
  • 索引生效问题
  • 索引最左匹配准则
  • 索引总结

汇集索引和非汇集索引

《数据库原理》外面的解释：汇集索引的程序就是数据的物理存储程序，而非汇集索引的程序和数据物理排列无关。因为数据在物理寄存时只能有一种排列形式，所以一个表只能有一个汇集索引。在 SQL SERVER 中，索引是通过二叉树的数据结构来形容的；咱们能够如此了解这个两种索引：汇集索引的叶节点就是数据节点，而非汇集索引的叶节点依然是索引节点，只不过其蕴含一个指向对应数据块的指针。

汇集索引

汇集索引中键值的逻辑程序决定了表中相应行的物理程序。

汇集索引确定表中数据的物理程序。汇集索引相似于电话簿，后者按姓氏排列数据。因为汇集索引规定数据在表中的物理存储程序，因而一个表只能蕴含一个汇集索引。但该索引能够蕴含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

汇集索引对于那些常常要搜寻范畴值的列特地无效。应用汇集索引找到蕴含第一个值的行后，便能够确保蕴含后续索引值的行在物理相邻。

例如，如果应用程序执行 的一个查问常常检索某一日期范畴内的记录，则应用汇集索引能够迅速找到蕴含开始日期的行，而后检索表中所有相邻的行，直到达到完结日期。这样有助于进步此 类查问的性能。

同样，如果对从表中检索的数据进行排序时常常要用到某一列，则能够将该表在该列上汇集（物理排序），防止每次查问该列时都进行排序，从而节省成本。

当索引值惟一时，应用汇集索引查找特定的行也很有效率。例如，应用惟一雇员 ID 列 emp_id 查找特定雇员的最疾速的办法，是在 emp_id 列上创立汇集索引或 PRIMARY KEY 束缚。

汇集索引应用场景

  • 此列蕴含无限数目的不同值
  • 查问的后果返回一个区间的值
  • 查问的后果返回某值雷同的大量后果集

非汇集索引

一种索引，该索引中索引的逻辑程序与磁盘上行的物理存储程序不同。

索引是通过二叉树的数据结构来形容的，咱们能够这么了解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点依然是索引节点，只不过有一个指针指向对应的数据块。

非汇集索引指定了表中记录的逻辑程序，但记录的物理程序和索引的程序不统一，汇集索引和非汇集索引都采纳了 B + 树的构造，但非汇集索引的叶子层并不与理论的数据页相重叠，而采纳叶子层蕴含一个指向表中的记录在数据页中的指针的形式。

非汇集索引比汇集索引档次多，增加记录不会引起数据程序的重组。

非汇集索引应用场景

  • 此列蕴含了大量数目不同的值
  • 查问的完结返回的是大量的后果集
  • order by 子句中应用了该列

什么是回表

假如，咱们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。

这个表的建表语句是：

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值别离为 (100,1)、(200,2)、(300,3)、(500,5) 和(600,6)，两棵树的示例示意图如下

SQL 语句 select * from T where k between 3 and 5 执行过程：

  1. 在 k 索引树上找到 k=3 的记录，获得 ID = 300；
  2. 再到 ID 索引树查到 ID=300 对应的 R3；
  3. 在 k 索引树取下一个值 k=5，获得 ID=500；
  4. 再回到 ID 索引树查到 ID=500 对应的 R4；
  5. 在 k 索引树取下一个值 k=6，不满足条件，循环完结。

在这个过程中，回到主键索引树搜寻的过程，咱们称为 回表。能够看到，这个查问过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。

也就是说，基于非主键索引的查问须要多扫描一棵索引树。因而，咱们在利用中应该尽量应用主键查问。

笼罩索引

如果执行一条 SQL 语句 select ID from T where k = 3，这时只须要查找到 iD 的值即可，而 ID 值恰好存在与 k 索引树上，不须要进行回表。也就是说，在这个查问外面，索引 k 曾经“笼罩了”咱们的查问需要，咱们称为 笼罩索引。

因为笼罩索引能够缩小树的搜寻次数，显著晋升查问性能，所以应用笼罩索引是一个罕用的性能优化伎俩。

索引生效问题

  • 不在索引列上做任何操作（计算、函数、（主动 or 手动）类型转换），会导致索引生效而转向全表扫描
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROMemployees WHERE left(name,3) = 'LiLei';

  • 给 hire_time 减少一个一般索引：
ALTER TABLE `employees`
ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE 
EXPLAIN  select * from employees where date(hire_time) ='2018-09-30';

转化为日期范畴查问，会走索引：

EXPLAIN  select * from employees where hire_time >='2018-09-30 00:00:00'  and hire_time <='2018-09-30 23:59:59';

  • 存储引擎不能应用索引中范畴条件左边的列
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND
position ='manager';

  • mysql 在应用不等于（！= 或者 <>）的时候无奈应用索引会导致全表扫描
EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

  • is null,is not null 也无奈应用索引
EXPLAIN SELECT * FROM employees WHERE name is null

  • like 以通配符结尾（’$abc…’）mysql 索引生效会变成全表扫描操作
EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

EXPLAIN SELECT * FROMemployees WHERE name like 'Lei%'

问题：解决 like’% 字符串 %’ 索引不被应用的办法？

  1. 应用笼罩索引，查问字段必须是建设笼罩索引字段
EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

  1. 如果不能应用笼罩索引则可能须要借助搜索引擎
  • 字符串不加单引号索引生效
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;

  • or 连贯索引生效
explain select * from user where name =‘2000’or age = 20 or pos =‘cxy’;

  • order by

失常（索引参加了排序），没有违反最左匹配准则。

explain select * from user where name = 'zhangsan' and age = 20 order by age,pos;

违反最左前缀法令，导致额定的文件排序（会升高性能）。

explain select name,age from user where name = 'zhangsan' order by pos;

  • group by

失常（索引参加了排序）。

explain select name,age from user where name = 'zhangsan' group by age;

违反最左前缀法令，导致产生长期表（会升高性能）。

explain select name,age from user where name = 'zhangsan' group by pos,age;

索引最左匹配准则

最左前缀匹配准则：在 MySQL 建设联结索引时会恪守最左前缀匹配准则，即最左优先，在检索数据时从联结索引的最右边开始匹配。

要想了解联结索引的最左匹配准则，先来了解下索引的底层原理。索引的底层是一颗 B + 树，那么联结索引的底层也就是一颗 B + 树，只不过联结索引的 B + 树节点中存储的是键值。因为构建一棵 B + 树只能依据一个值来确定索引关系，所以数据库依赖联结索引最左的字段来构建。

举例：创立一个（a,b）的联结索引，那么它的索引树就是下图的样子。

能够看到 a 的值是有程序的，1，1，2，2，3，3，而 b 的值是没有程序的 1，2，1，4，1，2。然而咱们又可发现 a 在等值的状况下，b 值又是按顺序排列的，然而这种程序是绝对的。这是因为 MySQL 创立联结索引的规定是首先会对联结索引的最右边第一个字段排序，在第一个字段的排序根底上，而后在对第二个字段进行排序。所以 b = 2 这种查问条件没有方法利用索引。

索引总结

假如 index(a,b,c)

like KK% 相当于 = 常量，%KK 和 %KK% 相当于范畴

文章也会继续更新，能够微信搜寻「迈莫 coding」第一工夫浏览。每天分享优质文章、大厂教训、大厂面经，助力面试，是每个程序员值得关注的平台。

退出移动版