MySQL的索引问题

MySQL索引的介绍

  • 1、关于索引
    • 1.1 首先来看一下的关于对索引的理解
    • 1.2 关于使用索引的优缺点
    • 1.3 如何判断数据库的索引有没有生效?
    • 1.4 如何评估一个索引创建的是否合理?
    • 1.5 所有的字段都适合创建索引吗?
  • 2. MySQL的Hash索引和B树索引有什么区别?
  • 3. 索引的数据结构 B+Tree

1、关于索引

1.1 首先来看一下的关于对索引的理解

索引是一个单独的、存储在磁盘上的数据库结构,包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行,所有MySQL列类型都可以被索引,对相关列使用索引是提高查询操作速度的最佳途径。

索引是在存储引擎中实现的,因此,每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。MySQL中索引的存储类型有两种,即BTREE和HASH,具体和表的存储引擎相关。索引底层数据结构存在很多种类型,常见的索引结构有: B 树B+树 Hash红黑树。在 MySQL 中,无论是 Innodb 还是 MyIsam,都使用了 B+树作为索引结构

1.2 关于使用索引的优缺点

优点: 使用索引可以大大加快 数据的检索速度(大大减少检索的数据量), 这也是创建索引的最主要的原因。通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

缺点: 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候,如果数据有索引,那么索引也需要动态的修改,会降低 SQL 执行效率。索引需要使用物理文件存储,也会耗费一定空间。

但是,使用索引一定能提高查询性能吗?大多数情况下,索引查询都是比全表扫描要快的。
但是 如果数据库的数据量不大,那么使用索引也不一定能够带来很大提升

1.3 如何判断数据库的索引有没有生效?

可以使用EXPLAIN语句查看索引是否正在使用。

举例,假设已经创建了book表,并已经在其year_publication字段上建立了普通索引。执行如下语句:

EXPLAIN SELECT * FROM book WHERE year_publication=1990;
EXPLAIN语句将为我们输出详细的SQL执行信息,其中:

possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。

key行是MySQL实际选用的索引。

如果possible_keys行和key行都包含year_publication字段,则说明在查询时使用了该索引。

1.4 如何评估一个索引创建的是否合理?

建议按照如下的原则来设计索引:

  • 避免对经常更新的表进行过多的索引,并且索引中的列要尽可能少。应该经常用于查询的字段创建索引,但要避免添加不必要的字段。

  • 数据量小的表最好不要使用索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。

  • 在条件表达式中经常用到的不同值较多的列上建立索引,在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值,因此就无须建立索引,如果建立索引不但不会提高查询效率,反而会严重降低数据更新速度。

  • 当唯一性是某种数据本身的特征时,指定唯一索引。使用唯一索引需能确保定义的列的数据完整性,以提高查询速度。

  • 在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。

1.5 所有的字段都适合创建索引吗?

并不是。

下列几种情况,是不适合创建索引的:

  • 频繁更新的字段不适合建立索引;
  • where条件中用不到的字段不适合建立索引;
  • 数据比较少的表不需要建索引;
  • 数据重复且分布比较均匀的的字段不适合建索引,例如性别、真假值;
  • 参与列计算的列不适合建索引。

2. MySQL的Hash索引和B树索引有什么区别?

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树,对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据。它们有以下的不同:

hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围。

hash索引不支持使用索引进行排序,原理同上。

hash索引不支持模糊查询以及多列索引的最左前缀匹配,原理也是因为hash函数的不可预测。

hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询。

hash索引虽然在等值查询上较快,但是不稳定,性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差。而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低。

因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

3. 索引的数据结构 B+Tree

下图是B-Tree数据结构的示意图,分为上下两部分,下部分数据页,上部分是索引
页。数据页中存储的是表中的记录,索引页存储的是索引数据。
索引的数据结构图
如上图是一颗B-Tree数据结构,其中包含了数据页和索引页,数据页和索引页中虚线框存储的是数据项,实线框中的P1、P2、P3是指针。可以看到每个数据页或索引页包含多个数据项和指针,如“索引页1”包含数据项17和35,包含指针P1、P2、P3。P1指向小于17的索引页,P2指向在17和35之间的索引页,P3指向大于35的索引页。真实的数据存在于叶子节点(数据页),即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。
如果要查找数据项29,那么首先会把索引页1由磁盘加载到内存,此时发生一次IO(Input/Output,即读写磁盘)操作,在内存中用二分查找确定29在17和35之间,锁定“索引页1”的P2指针,通过索引页1的P2指针把索引页3由磁盘加载到内存,发生第二次IO操作,29在26和30之间,锁定索引页3的P2指针,通过指针加载数据页8到内存,发生第三次IO操作,同时内存中做二分查找找到29,结束查询,总计三次读取硬盘。真实的情况是,3层的B-Tree结构可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高是巨大的。如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常高。
对于任何DBMS(DataBase Manager System数据库管理系统),索引都是进行查询优化的最主要的因素。文章来源地址https://www.uudwc.com/A/b1oa3/

原文地址:https://blog.csdn.net/Best_Basic/article/details/133341548

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请联系站长进行投诉反馈,一经查实,立即删除!

上一篇 2023年10月28日 19:09
有时候,使用 clang -g test.c 编译出可执行文件后,发现 gdb a.out 进行调试无法读取符号信息,为什么?
下一篇 2023年10月28日 21:09