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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 二十、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
目录
相关文章
|
9天前
|
存储 SQL 关系型数据库
MySQL存储引擎
本文介绍了数据库优化的多个方面,包括选择合适的存储引擎、字段定义原则、避免使用外键和触发器、大文件存储策略、表拆分及字段冗余处理等。强调了从业务层面进行优化的重要性,如通过活动设计减少外部接口调用,以及在高并发场景下的流量控制与预处理措施。文章还提供了具体的SQL优化技巧和表结构优化建议,旨在提高数据库性能和可维护性。
MySQL存储引擎
|
23天前
|
存储 SQL 缓存
MySQL存储引擎如何完成一条更新语句的执行!
MySQL存储引擎如何完成一条更新语句的执行!
MySQL存储引擎如何完成一条更新语句的执行!
|
28天前
|
XML 关系型数据库 MySQL
MySQL 导出某些数据的技术详解
MySQL 导出某些数据的技术详解
94 2
|
2月前
|
存储 缓存 关系型数据库
MySQL高级篇——存储引擎和索引
MyISAM:不支持外键和事务,表锁不适合高并发,只缓存索引,内存要求低,查询快MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务、行级锁、外键,有一个毫无疑问的缺陷就是崩溃后无法安全恢复。5.5之前默认的存储引擎优势是访问的速度快,对事务完整性没有要求或者以SELECT、INSERT为主的应用针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高表名.frm 存储表结构;表名.MYD 存储数据 (MYData);
MySQL高级篇——存储引擎和索引
|
2月前
|
存储 关系型数据库 MySQL
技术解析:MySQL中取最新一条重复数据的方法
以上提供的两种方法都可以有效地从MySQL数据库中提取每个类别最新的重复数据。选择哪种方法取决于具体的使用场景和MySQL版本。子查询加分组的方法兼容性更好,适用于所有版本的MySQL;而窗口函数方法代码更简洁,执行效率可能更高,但需要MySQL 8.0及以上版本。在实际应用中,应根据数据量大小、查询性能需求以及MySQL版本等因素综合考虑,选择最合适的实现方案。
314 6
|
29天前
|
关系型数据库 MySQL 数据库
MySQL技术深度解析:每次最大插入条数探秘
MySQL技术深度解析:每次最大插入条数探秘
43 0
|
29天前
|
关系型数据库 MySQL 数据库管理
MySQL技术指南:如何更改数据字段的前几位数字
MySQL技术指南:如何更改数据字段的前几位数字
42 0
|
29天前
|
存储 缓存 关系型数据库
详细解析MySQL中的innodb和myisam
总之,InnoDB和MyISAM各有千秋,选择合适的存储引擎应基于对应用程序特性的深入理解,以及对性能、数据完整性和可扩展性的综合考量。随着技术发展,InnoDB因其全面的功能和日益优化的性能,逐渐成为更广泛场景下的首选。然而,在特定条件下,MyISAM依然保留其独特的价值。
90 0
|
30天前
|
消息中间件 监控 关系型数据库
MySQL数据实时同步到Elasticsearch:技术深度解析与实践分享
在当今的数据驱动时代,实时数据同步成为许多应用系统的核心需求之一。MySQL作为关系型数据库的代表,以其强大的事务处理能力和数据完整性保障,广泛应用于各种业务场景中。然而,随着数据量的增长和查询复杂度的提升,单一依赖MySQL进行高效的数据检索和分析变得日益困难。这时,Elasticsearch(简称ES)以其卓越的搜索性能、灵活的数据模式以及强大的可扩展性,成为处理复杂查询需求的理想选择。本文将深入探讨MySQL数据实时同步到Elasticsearch的技术实现与最佳实践。
66 0
|
3月前
|
监控 关系型数据库 MySQL
在Linux中,mysql的innodb如何定位锁问题?
在Linux中,mysql的innodb如何定位锁问题?

推荐镜像

更多