数据库 -- 索引并不是万能的

数据库 -- 索引并不是万能的

数据库 -- 索引并不是万能的

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息。但是索引也不是万能的 ,有时候发现我们 sql 中索引不生效的,我们深入理解下索引的原理,以及误区,


InnoDB是如何存储数据的?

MySQL把数据存储和查询操作抽象成了存储引擎,不同的存储引擎,对数据的存储和读取方式各不相同。MySQL支持多种存储引擎,并且可以以表为粒度设置存储引擎。因为支持事物,我们最常用的是InnoDB

虽然数据保存在磁盘中,但其处理是在内存进行的。为了减少磁盘随机读取次数,InnoDB 采用页而不是行但粒度来保存数据,即数据被分成若干页,以页为单位保存在磁盘中,InnoDB的页大小,一般是16kb。各页中又一个页目录,方便按照主键查询记录。

数据页结构:

数据库 -- 索引并不是万能的

页目录通过槽把记录分成不同的小组,没个小组有若干条记录。如图所示,记录中最前面的小方块的数字,代表的是当前分组的记录条数,最小和最大的槽指向 2个特殊的伪记录。有了槽之后,我们按照主键搜索页中记录时,就可以采用二分法快速搜索,无需从最小记录开始遍历整个页中记录链表。

举例:搜索主键(pk) = 15的记录
  • 先二分得出槽中间位是(0+6)/2=3 , 看到其指向的记录是 12 < 15 , 所以需要从 #3 槽后继续搜索;
  • 再使用二分搜索出 #3槽和 #6槽的中间位 (3+6)/2=4.5 取整4,#4槽对应的记录是 16 > 15,所以记录一定在#4槽中;
  • 在从 #3 槽指向的12号记录开始向下搜索3次,定位到15号记录。

聚簇索引和非聚簇索引

InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分;

  一般建表会用一个自增主键做聚簇索引,没有的话MySQL会默认创建,但是这个主键如果更改代价较高,故建表时要考虑自增ID不能频繁update这点。

  我们日常工作中,根据实际情况自行添加的索引都是辅助索引,辅助索引就是一个为了需找主键索引的二级索引,现在找到主键索引再通过主键索引找数据;
数据库 -- 索引并不是万能的

B+ 树的特点包括:
: 最底层的节点叫作叶子节点,用来存放数据;
: 其他上层节点叫作非叶子节点,仅用来存放目录项,作为索引;
: 非叶子节点分为不同层次,通过分层来降低每一层的搜索量;
: 所有节点按照索引键大小排序,构成一个双向链表,加速范围查找。

  • 因此,InnoDB 使用 B+ 树,既可以保存实际数据,也可以加速数据搜索,这就是聚簇索
    引。如果把上图叶子节点下面方块中的省略号看作实际数据的话,那么它就是聚簇索引的示

意图。由于数据在物理上只会保存一份,所以包含实际数据的聚簇索引只能有一个。

  • InnoDB 会自动使用主键(唯一定义一条记录的单个或多个字段)作为聚簇索引的索引键
    (如果没有主键,就选择第一个不包含 NULL 值的唯一列)。上图方框中的数字代表了索

引键的值,对聚簇索引而言一般就是主键。

为了实现非主键字段的快速搜索,就引出了二级索引,也叫作非聚簇索引、辅助索引。二级索引,也是利用的 B + 数的数据结构
数据库 -- 索引并不是万能的

这次二级索引的叶子节点中保存的不是实际数据,而是主键,获得主键值后去聚簇索引中获
得数据行。这个过程就叫作回表。

回表是什么意思?就是你执行一条sql语句,需要从两个b+索引中去取数据

表tbl有a,b,c三个字段,其中a是主键,b上建了索引,然后编写sql语句

        SELECT * FROM tbl WHERE a=1

这样不会产生回表,因为所有的数据在a的索引树中均能找到

    SELECT * FROM tbl WHERE b=1

这样就会产生回表,因为where条件是b字段,那么会去b的索引树里查找数据,但b的索引里面只有a,b两个字段的值,没有c,那么这个查询为了取到c字段,就要取出主键a的值,然后去a的索引树去找c字段的数据。
查了两个索引树,这就叫回表。索引覆盖就是查这个索引能查到你所需要的所有数据,不需要去另外的数据结构去查。其实就是不用回表。


考虑额外创建二级索引的代价


创建二级索引的代价,主要表现在维护代价、空间代价和回表代价三个方面。

  • 维护代价:创建 N 个二级索引,就需要再创建 N 棵 B+ 树,新增数据时不仅要修改聚簇索引,还需要修改这 N 个二级索引。
  • 空间代价:虽然二级索引不保存原始数据,但要保存索引列的数据,所以会占用更多的空间
  • 回表代码:二级索引不保存原始数据,通过索引找到主键后需要再查询聚簇索引,才能得到我们想要的数据

不是所有针对索引列的查询都能用上索引

1. 索引只能匹配列前缀

比如下面的 LIKE 语句,搜索 name 后缀为 name123 的用户无法走索引,执行计划的 type=ALL 代表了全表扫描:

EXPLAIN SELECT * FROM person WHERE NAME LIKE '%name123' LIMIT 100

数据库 -- 索引并不是万能的

把百分号放到后面走前缀匹配,type=range 表示走索引扫描,key=name_score 看到实际走了索引

EXPLAIN SELECT * FROM person WHERE NAME LIKE 'name123%' LIMIT 100

数据库 -- 索引并不是万能的

2. 条件涉及函数操作无法走索引。

比如搜索条件用到了 LENGTH 函数,肯定无法走索引

EXPLAIN SELECT * FROM person WHERE LENGTH(NAME)=7

数据库 -- 索引并不是万能的

3.联合索引只能匹配左边的列

对 name 和 score 建了联合索引,但是仅按照 score 列搜索无法走索引

EXPLAIN SELECT * FROM person WHERE SCORE>45678

数据库 -- 索引并不是万能的
个人博客地址:http://blog.yanxiaolong.cn/

上一篇:【SQL 学习】表连接--natural join 的一个bug


下一篇:SAP R3 Oracle 9i 客户端安装 windows 用户端