您需要了解的有关数据库索引的所有信息都在这里

 2024-01-30 04:01:19  阅读 0

数组索引超出范围_二维码生成 索引超出了数组界限_数组超过了索引界限

数据库索引,相信大家都不陌生。

索引是一种对数据库表中一个或多个列的值进行排序的结构,并允许快速访问数据库表中的特定信息。 索引作为辅助查询工具,设计得当的索引可以大大减轻DB的查询压力。 我们都知道DB是项目的核心也是最薄弱的地方。 如果压力太大,很容易造成故障,造成不可预测的影响。 。 因此,无论是日常开发还是面试,索引都是必须掌握的知识体系。

当然,虽然需要掌握,但索引中的知识点很多,很多初学者往往会错过。 这也是我想写这篇知识点总结的原因。 不仅是与读者分享,也是给自己一个全面的检讨。 我希望它对你有帮助。

好了,废话不多说,咱们进入正题。

首先声明一下,本文索引的所有知识点都是基于MySQL数据库的。

索引的优点和缺点

优势:

1.大大加快数据查询速度

2、唯一索引可以保证数据库表中每一行的唯一性

3、加速度表连接时间

缺点:

1、创建和维护索引需要时间,因此索引数量不能太多。

2.索引是一种占用磁盘空间的数据结构。

3、更新表时,还必须动态维护索引,降低了维护速度。

指数类型

索引的出现是为了提高查询效率,但是索引的实现方式有很多种,所以这里也引入索引模型的概念。 这里介绍三种常用于索引的数据结构,即哈希表、有序数组和搜索树。

哈希索引

哈希表也叫哈希表,其主要设计思想是利用哈希函数映射键的位置来找到值存储的地方。 读取时,也是通过键码直接找到位置并存储在其中。 这类数据结构的平均搜索复杂度为O(1)。

比如我们维护一张身份证信息和用户名的表,我们需要根据身份证号来查询姓名。 哈希索引大概是这样的:

数组超过了索引界限_二维码生成 索引超出了数组界限_数组索引超出范围

这种索引结构的优点是在随机添加或删除单个元素方面效率很高。 缺点是哈希表中的元素不一定是按顺序排列的,所以如果要做区间查询的话,速度很慢。

假设我想找到所有ID号在图片中[,]范围内的用户,我必须把他们全部扫描出来。

因此,哈希表结构适用于只有等价查询的场景。

有序数组索引

有序数组索引无论是在等效查询还是区间查询场景下都非常高效。 我们以上面的图片为例。 如果使用有序数组实现,它将如下所示:

数组超过了索引界限_二维码生成 索引超出了数组界限_数组索引超出范围

数组的元素根据ID号按顺序排列。 当你想要查询数据时,可以使用二分法快速获取数据。 时间复杂度为O(logN)。 而且,因为是按顺序排列的,所以查询一定区间内的数据也是非常快的。

当然,有序数组的缺点也很明显。 尽管搜索速度很快,但添加或删除元素可能需要移动所有后续元素。 这是数组的天然缺陷。 因此,有序数组索引只适合静态存储引擎。 例如,如果要保存2017年某个城市的所有人口信息,则此类数据不会再次修改。

搜索树索引

说到搜索树,我们最熟悉的是二叉搜索树。 二叉搜索树的特点是每个节点的左子小于父节点,父节点小于右子,左右子树也分别 对于二叉搜索树来说,平均时间复杂度是 O(log2(n))。

数组超过了索引界限_数组索引超出范围_二维码生成 索引超出了数组界限

它既具有链表快速插入、删除操作的特点,又具有数组快速查找的优点。 同时,由于二叉搜索树本身是有序的,因此也支持范围搜索。

说到这里,二叉搜索树似乎是索引的一个不错的选择,但事实并非如此。

首先我们要明确这棵树是存在于磁盘上的。 每次我们都要从磁盘中读取对应的节点,然而二叉搜索树的节点是随机存储在文件中的,所以读取一个节点可能需要一次磁盘IO,但是二叉搜索树比较高大上。 例如,一棵包含一百万个元素的平衡二叉树,其高度超过十层,这意味着大多数情况下只需要一次数据检索。 十多次磁盘IO的成本太高,所以一般不会使用二叉搜索树来建立索引。

为了使查询读取尽可能少的磁盘,查询过程必须访问尽可能少的数据块。 也就是说,树的高度要尽可能的低,即采用多路搜索树,存储引擎采用的这种多路搜索树就是我们常说的B+树。

索引结构

它是MySQL中最常用的搜索引擎。 其索引底层结构采用B+树,所有数据都存储在B+树中。 每个索引对应一棵B+树。

数组超过了索引界限_数组索引超出范围_二维码生成 索引超出了数组界限

B+树的特点是:

这种结构有两个优点:

指数分类

根据结构,数据库索引可以分为聚集索引和非聚集索引。

聚集索引也称为聚集索引,它根据每个表的主键构造一棵B+树。 同时,叶子节点存储整个表的行记录数据。 简单来说,就是我们常说的主键索引。 在聚集索引之上创建的索引称为辅助索引。 访问辅助索引中的数据始终需要进行第二次查找。

非聚集索引,也叫非聚集索引、二级索引。 这种索引将数据和索引分开存储,索引结构的叶子节点指向数据对应的位置。

聚集索引

聚集索引用于将主键组织成B+树,行数据存储在叶子节点上。 我们首先假设一个用户表。 该表包含 id、name 和几个字段。

数组超过了索引界限_数组索引超出范围_二维码生成 索引超出了数组界限

图片表示的索引结构大致是这样的:

二维码生成 索引超出了数组界限_数组超过了索引界限_数组索引超出范围

从图中可以看出,如果我们使用条件“where id = 14”来查找主键,则可以根据B+树检索算法找到对应的叶子节点,进而获得行数据。

如果对Name列进行条件搜索,则需要两步:第一步在辅助索引B+树中检索Name,并到达其叶子节点获取对应的主键。 第二步利用主键对主索引B+树种再进行一次B+树检索操作,最终到达叶子节点获取整行数据。 (重点是辅助索引需要通过其他key来创建)

这就是聚集索引的结构,而非聚集索引的代表是MyISM,这也是MySQL中常见的搜索引擎。

非聚集索引

非聚集索引的两棵B+树看起来没有什么不同。 节点的结构完全相同,但存储的内容不同。 主键索引B+树的节点存储主键,辅助键索引B+树的节点存储辅助键。 索引本身不存储数据。 数据存储在单独的地方。 两棵B+树的叶子节点使用一个地址来指向真实的表数据。

数组索引超出范围_二维码生成 索引超出了数组界限_数组超过了索引界限

看起来非聚集索引的效率比聚集索引要高,因为不需要两次检查B+树,那么为什么最常用的引擎仍然使用这种存储结构呢? 它的优点是什么?

1、在聚集索引中,由于行数据和叶子节点存储在一起,所以同一页中会有多行数据。 当访问同一个数据页的不同行记录时,该页已经被加载到其中。 当再次访问时,就会在内存中完成访问,而不必访问磁盘。 这样主键和行数据一起加载到内存中,找到叶子节点就可以立即返回行数据。 因此,如果按照主键ID来组织数据,可以更快地获取数据。

2、辅助索引使用主键作为“指针”而不是使用地址值作为指针的好处是,减少了发生行移动或者数据页分裂时辅助索引的维护工作**。 使用主键值作为指针将使辅助索引更加高效。 索引占用的空间较多,好处是移动行时不需要更新辅助索引中的“指针”。 **也就是说,行的位置(实现中位于16K Page)会随着数据库中数据的修改而改变(之前的B+树节点分裂和Page分裂),使用聚集索引可以保证无论这棵主键B+树的节点如何变化,辅助索引树都不会受到影响。

3、聚集索引适合排序和范围查询,非聚集索引不适合。

覆盖指数

说到辅助索引,我们还可以扩展另一个特殊索引,那就是覆盖索引。

前面提到,访问聚集索引中的数据需要进行二次查找,即首先找到辅助键的叶子节点,获取主键对应的节点,然后使用主键索引来查询数据。 这还是比较慢的。 事实上,如果我们第一次查找就能得到需要的字段,就不需要第二次查找主键,也就是不需要“回表”。

对于上面的表,它有 id、name 三个字段,我为 name 添加了索引。 查询数据的时候,我是这样写的语句:

select name from user where name like '张%';

因为我们的语句使用了索引,并且返回的字段存在于叶子节点中,所以查询时不会返回表,多棒啊~~

因此,如果必填字段恰好是索引列,请尽量使用这种查询方式,而不是使用*语句。

指数类型

前面提到的指数分类是按照结构来划分的。 如果按照范围来划分,索引还可以分为以下类型:

普通索引:这是最基本的索引类型,没有唯一性等限制。

CREATE INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)

唯一索引:与普通索引基本相同,但所有索引列只能出现一次以保持唯一性。

CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)

主键:与唯一索引一样,不能有重复的列,但本质上主键不能视为索引,而是约束,必须指定为“KEY”。 与唯一索引的区别是:

全文索引:全文索引的索引类型是,可以在TEXT类型的列上创建。 在.6之前的版本中,只有存储引擎支持全文索引。 在5.6及以后的版本中,存储引擎和存储引擎都支持全文索引。

CREATE FULLTEXT INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)

联合索引:联合索引实际上并不是索引分类,它是包含多个字段的普通索引。 例如,如果有一个联合索引,名为index(a, b),则在搜索时可以使用a和b作为条件。

最左匹配原则

在联合索引中,使用最左边的优先级,可以匹配从最左边开始的任何连续索引。 同时,在搜索范围查询(>、3和d=4等语句时,d不使用索引,因为c字段是范围查询,其后面的字段将停止匹配。

指数什么时候到期?

1.对索引列使用函数或者表达式,比如这样

select * from test where  num  +  1 = 5

MySQL无法解析这种等式。 这完全是用户的行为。 索引列应该被视为独立列,这样索引才会生效。

2.存在NULL值条件

select * from user where user_id is not null;

在设计数据库表时,我们应该尽量避免NULL值。 如果数据为空,我们可以给一个默认值,比如数字类型为0或-1,字符类型为空字符串。

3. 使用 or 表达式作为条件。 如果某一列没有索引,其他列的索引将不起作用。

select * from user where user_id = 700 or user_name = "老薛";

这样,如果有索引,但没有,那么执行过程中索引就会失效。 这就是为什么在开发中应尽可能少地使用 OR 的原因,除非两个字段都建立了索引。

4、列与列的比较,在某个表中,两列(id和c_id)有单独的索引。 以下查询条件不会被索引。

select * from test where id = c_id;

5、数据类型转换。如果列类型是字符串,则数据必须用引号引起来,否则将无法使用索引。

create index `idx_user_name` ON user(user_name)
select * from user where user_name = 123;

像上面一样,虽然创建了索引,但是查询时条件并没有被当做字符串处理,所以不会使用索引。

6. NOT 条件

当查询条件为NOT时,索引定位比较困难,此时执行计划可能更倾向于全表扫描。 此类查询条件包括:、NOT、in、not

select * from user where user_id<>500;
select * from user where user_id in (1,2,3,4,5);
select * from user where user_id not in (6,7,8,9,0);
select * from user where user_id exists (select 1 from user_record where user_record.user_id = user.user_id);

7. Like 查询以 % 开头

使用模糊搜索时,尝试使用尾随通配符。 例如,如果您想查找姓张的人,您可以使用“张%”。 这样,在建立索引时,可以从前面匹配索引列,但是如果要搜索姓张的人,可以从前面匹配索引列。 ',那么将使用全表扫描

8.多列索引遵循最左匹配原则,如上所述

何时使用索引

前面提到,索引虽然可以加快查询速度,但也会占用空间。 因此,不创建的索引越多越好。 为了让索引得到有效的利用,我们必须把索引留给最有用的查询字段。 一般来说,换句话说,应该在这些字段上创建索引:

同样,有些字段不应建立索引。 这些列包括

关键词

它是MySQL的一个关键字,通过它我们可以检查搜索语句的性能。

数组超过了索引界限_二维码生成 索引超出了数组界限_数组索引超出范围

这是查询表的数量。 总共有超过 3000 万行。 这么大的数据量,我们在查找的时候就必须使用索引。 至于索引是否生效,我们也可以通过this关键字来查看

看,搜索的项数瞬间下降到了16条,并且使用的索引是 ,这证明我们的索引是有效的。

关于几个重要的参数,我们需要了解一些:

id:查询序列号

:查询的类型主要区分普通查询和联合查询、子查询等复杂查询。

类型:

type显示的是访问类型,这是一个比较重要的指标。 结果值从最好到最差是:

> const > > ref > > > > > 范围 > 索引 > ALL

最高效的,ALL已经是全表扫描了。 一般来说,查询至少要达到范围级别。

钥匙:

显示 MySQL 实际决定使用的键。 如果没有选择索引,则键为 NULL。

key=primary的话,表示使用了主键;
key=null表示没用到索引。

指示 MySQL 可以使用哪个索引来查找该表中的行。 如果为空,则没有相关索引。 这时候就需要检查一下语句中是否有什么东西导致索引失败。

行:

表示执行计划中预计扫描的行数,这是一个估计值。

额外的:

总结

好了,关于索引的知识点介绍就到此为止。 最后总结一下索引需要注意的事项。

1、根据表数据的使用情况创建索引。 您不能创建太多索引。 一般情况下,不建议一个表中的索引字段超过6个。

2、刀刃要用好刀。 它经常用于查询。 没有太多重复的数据。 对于搜索行数不超过表数据 4% 的字段,索引效果更好。

3、创建联合索引时,注意最左匹配原则。 请记住,最左边的字段是必填字段。 我因此受了很多苦。

4. 查询语句应该使用执行计划来检查性能。

参考:

MySQL实践讲座45

终于

虽然都是基础知识,但我还是花了一天的时间才整理出来。 5000多字,算得上是一篇干文章了。 如果您觉得有用,希望您转发或者点击。 喜欢之类的,我不要求四连,能连两连或者连一连我就很满足了。 你们的努力是我继续创作的动力!

如本站内容信息有侵犯到您的权益请联系我们删除,谢谢!!


Copyright © 2020 All Rights Reserved 京ICP5741267-1号 统计代码