二十、MySQL技术体系之InnoDB存储引擎的索引

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 二十、MySQL技术体系之InnoDB存储引擎的索引

一、什么是索引?

1、对数据库表中一列或多列的值进行排序的一种数据存储结构。
2、类似书的目录,使用索引可以快速访问数据库表中的行记录数据。
3、基本原理:存储引擎通过遍历索引定位基表中的行,然后返回给Server层,再去为这些行记录数据进行where条件过滤。
4、InnoDB存储引擎的叶子结点数据是按照主键rowid进行排序的,目的是方便对磁盘做顺序I/O

二、索引的数据结构

MySQL数据库使用B+tree索引结构,B+tree是由二叉树 -> 平衡二叉树 -> B-tree树逐步演化而成。

1、二叉树结构

每个结点至多有2个子结点
二叉树有左右序之分,次序不能颠倒
二叉树中,左子树的键值永远比右子树的键值小,并且小于根结点的键值

2、平衡二叉树结构

- 解决了二叉树节点深度增加,查询的均分复杂度上升的问题,拥有比二叉树更快的查询速度
- 条件:
   左右两个子树的高度差的绝对值不超过1
   左右两个子树也都是平衡二叉树

3、B-tree结构(Btree、B树)

一个结点可以拥有多于2个子结点的多叉查找树
所有叶子结点都在同一层
叶子结点不包含任何关键字信息

4、B+tree索引结构

B-tree结构的变体,双向链表结构,一种多路搜索树
所有关键字信息都存放在叶子结点,并包含这些关键字记录的指针
叶子结点按照关键字大小顺序连接,因此访问关键字的顺序是连续性的,不需要回溯上一个结点
所有数据都存放在叶子结点中

三、B+tree索引分类

show index from 表名,
  可以查看表中有哪些索引,使用expalin命令可以查看SQL语句的执行计划(优化器对SQL语句的优化执行方案)。

1、聚集索引和普通索引

  • B+tree索引分为两大类:聚集索引和非聚集索引

聚集索引

叶子结点存放表中所有行数据记录的信息;
    对于聚集索引,数据即索引,索引即数据;
    创建主键时会自动创建;

普通索引

叶子结点不包含所有行的数据记录,只存放索引字段的键值和行记录数据的主键值;
    检索数据时,通过普通索引叶子结点找到主键,再通过主键来获取行数据记录;

MySQL语法:

ALTER TABLE 表名 ADD INDEX 索引名(列名);
      CREATE INDEX 索引名 ON 表名(列名);
create index ename_index on emp(ename);
drop index ename_index on emp;
show index on emp;

2、主键索引(聚集索引)和唯一索引

主键索引必须满足三个条件:

主键索引就是聚集索引

   主键值必须唯一;

   不能包含NULL值;

   必须保证值是自增的(自增保证insert是顺序的,基于磁盘的顺序访问特性,提高了存取效率);

  • 唯一索引必须满足一个条件:

     值必须是唯一的,但是值允许是NULL;

  • 一张表只能有一个主键索引,但是唯一索引可以有多个
  • MySQL语法:

   创建主键索引:创建主键时自动创建;

   创建唯一索引:ALTER TABLE emp add unique(列名);

3、覆盖索引

  • 通过索引就可以直接返回查询数据,不需要在查询索引之后再去回表查询数据,减少了磁盘I/O操作,因此查询速度快
  • 使用explain命令查看SQL语句的执行计划
    extra列出现Using index关键字,表明SQL语句使用了覆盖索引;
  • 符合覆盖索引的场景
    必须在select列出所需要的列,而不能写select * from emp;

4、前缀索引

  • BLOB、TEXT、较长的VARCHAR类型的列,为前面最左侧的若干个字符创建的一种索引
  • MySQL语法:
    ALTER TABLE emp add key(columnName(prefixLength));
  • 这种索引很小,所查询较快
  • 不足之处:

   不能在ORDER BY和GROUP BY中使用;

   不能用做覆盖索引

5、联合索引(复合索引)

  • 在表中两个或者两个以上的列上创建的索引
  • 利用索引上的附加列,可以缩小检索的段池范围,更快的检索数据
  • MySQL语法:
    create index idx_c1_c2 epm(列名c1,列名c2);
  • 在使用过程中,必须要满足最左前缀原则:

   创建索引时,将选择性高的列放在前面;

   一条查询语句可以只使用索引中的一部分,但是必须从最左索引列开始;

   如使用索引idx_c1_c2,可以检索c1或者c1,c2,但是不能直接检索c2;

  • 例外情况select * from emp where c1=? or c2=?

   不能使用联合索引,需要创建两个单列索引或者考虑使用union;

6、哈希索引

  • 采用哈希算法,把键值换算成哈希值
  • 只能进行等值查询,不能进行排序、模糊查找、范围查询
  • 查询速度快,检索时不需要像B+tree从根节点到叶子结点逐级查找,只需一次哈希算法即可立刻定位到数据位置

四、使用explain命令查看SQL语句的执行计划

1、type列,表示查询类型

  • 出现all关键字,说明全表扫描

2、key列,表示是否使用索引

  • 出现null关键字,说明没有使用索引

3、rows列,表示在SQL语句执行过程中扫描行数的估算值

  • 数值越大,说明需要扫描的行数越多,耗时越长

4、extra列,

  • 出现Using filesort关键字,说明排序使用了文件系统,性能较差
  • 出现Using temporary关键字,说明使用了内存临时表空间,性能较差
  • 出现Using index condition关键字,说明使用了索引过滤(ICP优化),where条件可以使用索引,在存储引擎层直接做过滤操作
  • 出现Using MRR关键字,说明范围扫描使用了MRR优化特性,

     把普通索引的叶子结点上查找到的主键值的集合存储到read_rnd_buffer;

     在read_rnd_buffer中对主键值排序;

     最后利用排序后的主键值集合进行顺序回表读取行记录数据;

     因此达到了进行顺序I/0访问磁盘的目标,避免查询过程中出现I/0随机访问,提高了I/O效率;

  • 出现Using join buffer(Batched Key Access)关键字,说明表连接使用BKA优化特性,

     BKA是提高join性能的算法,在读取被join表的记录时使用顺序I/0;

     当MySQL使用索引访问第二个join表时,使用一个join buffer收集第一个操作对象生成的相关列值,批量传递给引擎层做索引查找;

     使用时基于MRR特性;

5、filtered列,表示返回结果的行占需要读取到的行(rows列的值)的百分比

五、索引的利弊

1、使用索引的好处

  • 提高数据检索效率
  • 提高聚合函数的效率 (组函数 max(), min()...)
  • 提高排序效率
  • 使用覆盖索引可以避免回表

2、使用索引的正确方式

  • 适合创建索引的列:

   经常被查询的条件列;

   经常用于表连接的列;

   经常排序分组的列;

  • 不适合创建索引的列:

   选择性低的字段不要创建索引,如性别字段只能筛选出1/2的数据,区分度不大,不该创建索引;

   很少查询的列不要创建索引,需要事先和需求方沟通确认;

   大数据类型字段不要创建索引,如text、blob类型字段不要创建索引;

  • 避免的情况

   尽量避免不要使用NULL,MySQL对含有NULL值的列很难进行查询优化,会让索引、索引的统计信息及比较运算更加复杂,因此DBA经常推荐使用NOT NULL;

   尽量在MySQL客户端的应用程序中做运算和判断,不要让数据库做各种运算;

3、索引失效的情况

  • 通过索引扫描的行记录数超过全表的30%,优化器不会走索引,变成全表扫描
  • 联合索引中,第一个查询条件不是最左索引列
  • 联合索引中,第一个索引列使用范围查询,只能使用到部分索引,有ICP出现
  • 联合索引中,第一个查询条件不是最左前缀列
  • 模糊查询条件列,最左以通配符%开始(可以放到子查询里)
  • 两个单列索引,一个检索,一个排序,只能使用其中一个索引(查询语句中最多只能使用一个索引),这种情况考虑创建联合索引
  • 查询字段有索引,但是使用了函数运算

4、索引的副作用

  • 创建太多的索引,在DML操作时,将会增加维护索引的负担
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
存储 算法 关系型数据库
InnoDB与MyISAM实现索引方式的区别
InnoDB和MyISAM均采用B+树索引,但在实现上有所不同。InnoDB的主键索引在叶子节点存储完整数据记录,辅助索引则存储主键值;而MyISAM的主键索引与数据文件分离,仅存数据地址,且主辅索引无区别,支持非唯一主索引。
17 1
|
15天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
123 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
15天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的表空间
InnoDB是MySQL默认的存储引擎,主要由存储结构、内存结构和线程结构组成。其存储结构分为逻辑和物理两部分,逻辑存储结构包括表空间、段、区和页。表空间是InnoDB逻辑结构的最高层,所有数据都存放在其中。默认情况下,InnoDB有一个共享表空间ibdata1,用于存放撤销信息、系统事务信息等。启用参数`innodb_file_per_table`后,每张表的数据可以单独存放在一个表空间内,但撤销信息等仍存放在共享表空间中。
|
15天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的段、区和页
MySQL的InnoDB存储引擎逻辑存储结构与Oracle相似,包括表空间、段、区和页。表空间由段和页组成,段包括数据段、索引段等。区是1MB的连续空间,页是16KB的最小物理存储单位。InnoDB是面向行的存储引擎,每个页最多可存放7992行记录。
|
15天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL的InnoDB存储引擎
InnoDB是MySQL的默认存储引擎,广泛应用于互联网公司。它支持事务、行级锁、外键和高效处理大量数据。InnoDB的主要特性包括解决不可重复读和幻读问题、高并发度、B+树索引等。其存储结构分为逻辑和物理两部分,内存结构类似Oracle的SGA和PGA,线程结构包括主线程、I/O线程和其他辅助线程。
【赵渝强老师】MySQL的InnoDB存储引擎
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
|
1月前
|
存储 缓存 关系型数据库
详细解析MySQL中的innodb和myisam
总之,InnoDB和MyISAM各有千秋,选择合适的存储引擎应基于对应用程序特性的深入理解,以及对性能、数据完整性和可扩展性的综合考量。随着技术发展,InnoDB因其全面的功能和日益优化的性能,逐渐成为更广泛场景下的首选。然而,在特定条件下,MyISAM依然保留其独特的价值。
129 0
|
3月前
|
监控 关系型数据库 MySQL
在Linux中,mysql的innodb如何定位锁问题?
在Linux中,mysql的innodb如何定位锁问题?
|
3月前
|
SQL 存储 关系型数据库
"MySQL增列必锁表?揭秘InnoDB在线DDL,让你的数据库操作飞一般,性能无忧!"
【8月更文挑战第11天】在数据库领域,MySQL凭借其稳定高效的表现深受开发者喜爱。对于是否会在给数据表添加列时锁表的问题,MySQL的行为受版本、存储引擎等因素影响。从5.6版起,InnoDB支持在线DDL,可在改动表结构时保持表的可访问性,避免长时间锁表。而MyISAM等则需锁表完成操作。例如,在使用InnoDB的表上运行`ALTER TABLE users ADD COLUMN email VARCHAR(255);`时,通常不会完全锁表。虽然在线DDL提高了灵活性,但复杂操作或大表变更仍可能暂时影响性能。因此,进行结构变更前应评估其影响并择机执行。
73 6
|
5月前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.3-InnoDB中的锁
【MySQL技术内幕】6.3-InnoDB中的锁
199 57
下一篇
无影云桌面