解析MySQL索引的作用

目录
  • 1、索引用于减少需要扫描的记录数量
  • 2、索引用于排序
    • 1、分析下面的查询语句:
    • 2、使用联合索引进行排序时的注意事项
    • 3、不可以使用索引进行排序的情况:
  • 3、索引用于分组
  • 总结

面试题:索引的作用?

首先建立一张数据库表:

create table single_table(
	id int not auto_increment,
	key1 varchar(100),
	key2 int,
	key3 varchar(100),
	key_part1 varchar(100),
	key_part2 varchar(100),
	key_part3 varchar(100),
    common_field varchar(100),
	primary key(id),          # 聚簇索引
	key idx_key1(key1),       # 二级索引
	unique key uk_key2(key2), # 二级索引,而且该索引是唯一二级索引
	key idx_key3(key3),       # 二级索引
	key idx_key_part(key_part1,key_part2,key_part3) # 二级索引,也是联合索引
)Engine=InnoDB CHARSET=utf8;

1、索引用于减少需要扫描的记录数量

对于某个查询来说,最简单粗暴的执行方案就是扫描表中的所有记录,判断每一条搜索记录是否符合搜索条件。如果符合,就将其发送到客户端,否则就跳过该记录。这种执行方案被称为全表扫描。

对于InnoDB存储引擎来说,全表扫描意味着从聚簇索引第一个叶子节点的第一条记录开始,沿着记录所在的单向链表向后扫描,直到最后一个叶子节点的最后一条记录,如果可以利用B+树查找索引列值等于某个值的记录,这样就可以减少需要扫描的记录的数量。

由于B+树叶子节点中的记录是按照索引列值有小到大的顺序排序的,所以只需要扫描某个区间或者某些区间中的记录也可以明显减少需要扫描的记录的数量。

对于查询语句:

select * from single_table where id>=2 and id<=100;

这个语句其实就是想查找id值在[2,100]区间中的所有聚簇索引记录,我们可以通过聚簇索引对应的B+树快速的找到id=2的那条聚簇索引记录,然后沿着记录所在的单向链表向后扫描,直到某条聚簇索引记录的id值不在[2,100]区间中为止,与扫描全部的聚簇索引记录相比,这种方式大大减少了需要扫描的记录数量,所以提升了查询效率。

其实,对于B+树来说,只要索引列和常数使用=、<=>、in、not in、is null、is not null、>、<、>=、<=、between、!=、或者like操作符连接起来,就可以产生扫描区间,从而提高查询效率。

2、索引用于排序

我们在编写查询语句时,经常需要使用order by子句对查询出来的记录按照某种规则进行排序。在一般情况下,我们只能把记录加载到内存中,然后再用一些排序算法在内存中对这些记录进行排序。有时查询的结果集可能太大以至于在内存中无法进行排序,此时就需要暂时借助磁盘的空间来存放中间结果,在排序操作完成后再把排序的结果返回给客户端。

在MySQL中,这种在内存中或者磁盘中进行排序的方式称为文件排序,但是如果order by子句中使用了索引列,就有可能省去在内存或磁盘中排序的步骤。

1、分析下面的查询语句:

select * form single_table order by key_part1,key_part2,key_part3 limit 10;

这个查询语句的结果集需要先按照key_part1值排序,如果记录的key_part1值相同,再按照key_part2值排序,如果key_part1值和key_part2值都相同,再按照key_part3排序。而我们建立的联合索引idx_key_part就是按照上面的规则排序的,如下为idx_key_part索引的简化示意图:

所以我们可以从第一条idx_key_part二级索引记录开始,沿着记录所在的单向链表向后扫描,取10条二级索引记录即可。由于我们的查询列表是*,也就是需要读取完整的用户记录,所以针对获取到的每一条二级索引记录都执行一次回表操作,将完整的用户记录发送给客户端。这样就省去了给10000条记录排序的时间。

这里我们在执行查询语句时加了limit语句,如果不限制需要获取的记录数量,会导致为大量二级索引记录执行回表操作,这样会影响整体的性能。

2、使用联合索引进行排序时的注意事项

在使用联合索引时,需要注意:order by子句后面的列的顺序也必须按照索引列的顺序给出;如果给出order by key_part3,key_part2,key_part1的顺序,则无法使用B+树索引。

之所以颠倒排序列顺序就不能使用索引,原因还是联合索引中页面和记录的排序规则是规定的,即先按照key_part1值排序,如果记录的key_part1值相同,再按照key_part2值排序,如果记录的key_part1值和key_part2值都相同,再按照key_part3值排序。如果order by子句的内容是order by key_part3,key_part2,key_part1,那就要求先按照key_part3值排序,如果记录的key_part3值相同,再按照key_part2值排序,如果记录的key_part3值和key_part2值都相同,再按照key_part1值排序,这显然是冲突的。

3、不可以使用索引进行排序的情况:

(1) ASC、DESC混用;

对于使用联合索引进行排序的场景,我们要求各个排序列的排序规则是一致的,也就是要么各个列都是按照升序规则排序,要么都是按照降序规则排序。

(2) 排序列包含非一个索引的列;

有时用来排序的多个列不是同一个索引中的,这种情况也不能使用索引进行排序,比如下面的查询语句:

select * from single_table order by key1,,key2 limit 10;

对于idx_key1的二级索引记录来说,只按照key1列的值进行排序,而且在key1列相同的情况下是不按照

key2列的值进行排序的,所以不能使用idx_key1索引执行上述查询。

(3) 排序列是某个联合索引的索引列,但是这些排序列在联合索引中并不连续;

(4) 排序列不是以单独列名的形式出现在order by子句中;

3、索引用于分组

有时为了方便统计表中的一些信息,会把表中的记录按照某些列进行分组。比如下面的分组查询语句:

select key_part1,key_part2,key_part3,count(*) fron single_table group by key_part1,key_part2,key_part3;

这个查询语句相当于执行了3次分组操作:

  • 先按照key_part1值把记录进行分组,key_part1值相同的所有记录划分为一组;
  • key_part1值相同的每个分组中的记录再按照key_part2的值进行分组,将key_part2值相同的记录放到一个小分组中,看起来像是在一个大分组中又细分了好多小分组。
  • 再将上一步中产生的小分组按照key_part3的值分成更小的分组。所以整体上看起来就像是先把记录分成一个大分组,然后再把大分组分成若干个小分组,最后把若干个小分组再细分为更多的小分组。

上面这个查询语句就是统计每个小小分组包含的记录条数。

如果没有idx_key_part索引,就得建立一个用于统计的临时表,在扫描聚簇索引的记录时将统计的中间结果填入这个临时表。当扫描完记录后,再把临时表中的结果作为结果集发送给客户端。

如果有了idx_key_part索引,恰巧这个分组顺序又与idx_key_part的索引列的顺序一致,因此可以直接使用idx_key_part的二级索引进行分组,而不用建立临时表了。

与使用B+树索引进行排序差不多,分组列的顺序页需要与索引列的顺序一致,也可以值使用索引列中左边连续的列进行分组。

总结

本篇文章就到这里了,希望能够给你带来帮助,也希望您能够多多关注我们的更多内容!

(0)

相关推荐

  • 深入解析MySQL索引数据结构

    目录 概述 索引数据结构 二叉树 红黑树 B-Tree B+Tree Hash 索引 InnoDB 索引实现(聚集) 索引文件和数据文件是分离的(非聚集) 聚集索引和非聚集索引 联合/复合索引 参考资料 总结 概述 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息. 索引数据结构 二叉树 二叉树(binary tree)是指树中节点的度不大于 2 的有序树,它是一种最简单且最重要的树.二叉树的递归定义为:二叉树是一棵空树,或者是一棵由一个根节点和两棵互不

  • MySQL索引长度限制原理解析

    这篇文章主要介绍了MySQL索引长度限制原理解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 索引 TextField是不支持建立索引的 MySQL对索引字段长度有限制 innodb引擎的每个索引列长度限制为767字节(bytes),所有组成索引列的长度和不能大于3072字节 myisam引擎的每个索引列长度限制为1000字节,所有组成索引列的长度和不能大于1000字节 varchar的最大长度是指字符长度,若数据库字符集为utf-8,则一个

  • MySQL索引结构详细解析

    目录 简介 索引结构(树) 为什么用树,而不用哈希表 BTree索引 B+Tree索引 聚簇索引与非聚簇索引 索引分类 性能分析 索引创建场景 简介 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法.这种数据结构,就是索引. 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上. 优点: 1.类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本. 2.通过

  • 关于MySQL索引的深入解析

    前言 我们知道,索引的选择是优化器阶段的工作,但是优化器并不是万能的,它有可能选错所要使用的索引.一般优化器选择索引考虑的因素有:扫描行数,是否排序,是否使用临时表. 使用explain分析sql explain是很好的自测命令,勤于使用explain有助于我们写出更合理的sql语句以及建立更合理的索引: mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) ord

  • MySQL索引事务详细解析

    目录 一.索引 1.概念 2.作用 3.缺陷 4.使用场景 5.使用 1.查看索引 2.创建索引 3.删除索引 6.案例 二.事务 1.为什么使用事务 2.事务的概念 3.使用 4.特性 一.索引 1.概念 索引是一种特殊的文件,包含着对数据表里所有记录的引用指针,可以对表中的一列或者多列创建索引,并指定索引的类型,各类索引有各自的数据结构实现. (这里对于索引也只是简单了解,写了些皮毛) 更浅显易懂的来说:数据库的数据保存在硬盘,硬盘不知道具体保存在哪个位置,索引就是用来告诉硬盘数据在哪个位置

  • 解析MySQL索引的作用

    目录 1.索引用于减少需要扫描的记录数量 2.索引用于排序 1.分析下面的查询语句: 2.使用联合索引进行排序时的注意事项 3.不可以使用索引进行排序的情况: 3.索引用于分组 总结 面试题:索引的作用? 首先建立一张数据库表: create table single_table( id int not auto_increment, key1 varchar(100), key2 int, key3 varchar(100), key_part1 varchar(100), key_part2

  • 深入解析MySQL索引的原理与优化策略

    目录 索引的概念 索引的原理 索引的类型 索引的使用 索引的使用方式 注意事项 索引优化技巧 索引的概念 MySQL索引是一种用于加速数据库查询的数据结构,它类似于书籍的目录,能够快速指导我们找到需要的信息.MySQL索引可以根据一定的算法和数据结构进行排序和存储,从而实现高效的数据查找和访问.在数据库中,索引可以加速数据的查询和更新操作,提高系统性能. MySQL支持多种索引类型,常见的包括B-tree索引.哈希索引和全文索引等.其中,B-tree索引是最常用的一种,它是一种平衡树结构,可以将

  • MySQL索引机制的详细解析及原理

    目录 一.索引的类型与常见的操作 二.常见的索引详解与创建 三.索引的原理 1.通过实验介绍B+tree 2.延伸 四.聚簇索引和非聚簇索引 1.使用聚簇索引的优势 2.什么情况下无法使用索引 总结 一.索引的类型与常见的操作 前缀索引 MySQL 前缀索引能有效减小索引文件的大小,提高索引的速度.但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index). 复合索引 集一个索引包含多个列(最左前

  • MySQL索引详细解析

    目录 1. MySQL 索引的最左前缀原则 2. 前缀索引 3. 索引下推(ICP——Index Condition Pushdown) 4. 查看 MySQL 语句是否用到索引 5. 为什么官方建议用自增长主键作为索引 6. 如何创建索引 7. 创建索引注意事项 8. 使用索引一定可以提高查询性能嘛 9. 索引失效 1. MySQL 索引的最左前缀原则 左前缀原则是联合索引在使用时要遵循的原则,查询索引可以使用联合索引的一部分,但是必须从最左侧开始.在创建联合索引时,要根据业务需求,where

  • Mysql中explain作用详解

    一.MYSQL的索引 索引(Index):帮助Mysql高效获取数据的一种数据结构.用于提高查找效率,可以比作字典.可以简单理解为排好序的快速查找的数据结构. 索引的作用:便于查询和排序(所以添加索引会影响where 语句与 order by 排序语句). 在数据之外,数据库还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用数据.这样就可以在这些数据结构上实现高级查找算法.这些数据结构就是索引. 索引本身也很大,不可能全部存储在内存中,所以索引往往以索引文件的形式存储在磁盘上. 我们

  • MySQL索引失效的典型案例

    典型案例 有两张表,表结构如下: CREATE TABLE `student_info` (   `id` int(11) NOT NULL,   `name` varchar(10) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `idx_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 CREATE TABLE `student_score` (   `id` int(11) NOT NULL,

随机推荐