MySQL 索引与优化必知必会

在大型互联网公司负责业务系统的研发工作,具有十年工作经验。对于 Java 后台研发相关技术有深入的理解与实践。

MySQL 是业务后台系统经常用到的结构化数据库。 掌握 MySQL 相关知识是研发人员必备的能力。 与此同时,在面试过程当中,MySQL 的知识点也是经常被当做面试题目,以此来考量候选人的能力。 随着业务量的增加,对于 MySQL 性能优化的要求也越来越高, 而索引方面是性能优化重点考虑的方向,所以深入理解 MySQL 索引对于未来的优化起到很重要的作用。 通过本 Chat,大家将学习到: MySQL 索引基本概念 MySQL 索引实现方式 MySQL 索引优化方案 MySQL 索引与锁的实现 MySQL 索引常见面试题 当前内容版权归码字科技所有并授权显示,盗版必究。

文章正文

MySQL 索引基本概念

1.1 什么是索引?

在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。(百度百科)

从上面的定义中我们可以知道,索引是一种存储结构;是数据库表中一列或若干列值的集合,或者是指向表中数据页物理标识的逻辑指针清单。在日常生活中索引就像是一本书的目录,一个商品的使用说明书,我们可以跳过大部分不关心的内容,直奔我们需要知道的主题。目录和使用说明书可以节省我们很多时间,索引也同样用来提高数据库的检索速度,提高数据库性能。

例如:有一张 student 表,其中有 5W 条记录,记录这 5W 个学生的信息。其中有一个 sno 的字段记录每个学生的学号,现在想要查询出学号为 XXXX 的人的信息。

在没有索引的情况下,我们需要一条一条的遍历表中的数据。在查询到匹配的数据后,先将该数据放入到结果集中,再继续查询直到将全部的数据查询完毕。

而在有索引的情况下, 可以将 sno 的 key 的值放在一个 n 叉树上( BTree )。查询数据时,会在 B 树上根据 sno 的值进行条件查询, 而不需要全局遍历。

1.2 索引的优点

  1. 索引的最明显的优点就是减少查询的速度,提高数据库的效率。
    • 通过索引查询可以缩短数据检索的时间
    • 通过索引可以加快表与表之间的关联查询时间
  2. 为排序或者分组的字段增加索引,可以提升排序和分组的效率。

1.3 索引的缺点

  1. 创建索引的时间成本会随着数据量的增大而增大。
  2. 索引创建之后,在对数据库表中的数据进行增、删、改等操作之后,相应的索引也需要进行维护,降低了数据维护的速度。
  3. 从本质上来讲,索引是通过空间来换取时间。也就是说,在我们缩短查询的时间成本的同时,势必要牺牲磁盘空间成本来存储索引。

1.4 索引的应用

根据上面所讲的优缺点我们知道,索引是需要在一定的场景下使用才能真正的提高效率的,需要根据具体情况进行分析决定是否使用索引。

  1. 表数据量较大且响应时间不能满足需求的时候,可以考虑使用索引。
  2. 索引建议创建在变动频率较小,且值内容较多的字段上。

索引分类

2.1 物理角度

  1. 集聚索引:决定数据在磁盘上的物理排序,一个表只能有一个聚集索引。
  2. 非集聚索引:不决定数据在磁盘的物理排序,索引上只包含建立索引的数据和一个类似聚集索引物理排序指针一样的定位符,通过这个定位符可以找到数据。

2.2 使用角度

在本小节,我们重点从应用开发中我们经常设置的索引类型来进行介绍。 总结来说比较常用的索引类型:普通索引、复合索引、唯一索引、主键索引;不常用的一些索引类型有:全文索引、空间索引。

2.2.1 普通索引

最基本的索引,它没有任何限制。普通索引是使用最为频繁的索引。在使用场景中我们选择建立普通索引要关注下面几点:

  • 目前索引的总数,索引总数太多会对数据新增造成效率影响。
  • 建立索引的字段的查询频次,以及表的总行数大小。
2.2.2 实战总结

**场景 1 **

在销售人员管理系统中,销售人员信息表存储了销售员工的基本信息,包括:用户 ID ,名称,部门,等级,职位,区域,电话号码,邮箱等。

在进行工单派发与查询的时候一个经常需要使用的查询场景,根据销售电话号来查询该销售其他的详细基本信息。 随着销售人员的不断增加,根据电话号码字段来查询这个语句性能不断下降,更加可怕的是随着业务不断发展,这种查询需求不断增加。 因此我们需要针对销售的电话号码列建立普通索引。

普通索引的建立能够有效地提高热频次查询语句的效率。避免慢 SQL 的产生。

在这里大家注意一下,慢 SQL 的治理,很大程度上需要借助索引来进行辅助。

2.2.3 复合索引

概念简介:在多个字段上创建索引,可以加速复合条件查询。复合索引也叫做联合索引。 复合索引的创建,我们需要寻找多条件频繁查询的语句来进行设置。

2.2.4 实战总结

**场景 1 **

交易中心的订单表,存储了用户交易订单相关的信息。表中重要的字段信息有用户 ID 、订单 ID 、商品 ID 、商品个数、支付金额、订单状态、交易日期、创建日期、付款时间、退款时间、物流 ID 等。

用户会有非常多的场景来查询某个订单状态下的订单列表。 比如我们经常会查询已支付但未到货的订单信息。 这就涉及到三个字段:用户 ID 、订单状态。

这时候我们就可以针对(用户 ID ,订单状态)这两个字段进行复合索引的创建。

当然复合索引随着业务不同,会有各种不同的配置。 使用复合索引的时候需要注意: 1.复合索引的最左前缀特点。复合索引由字段 (a,b,c) ,那么复合索引的查询可以支持 a | a,b | a,b,c 3 种组合进行查找,但不支持 b,c 进行查找。

2.2.5 唯一索引

一种唯一性索引。索引列值要唯一,但允许有空值。如果是组合索引,则组合后的列值必须唯一。

2.2.6 实战总结

场景 1

用户支付信息表,有一列为 pay_num 。这一类表示用户每次支付的唯一 ID ,是所有用户支付凭证记录。因此 pay_num 列设置唯一索引,来保证避免 pay_num 的重复。

与此同时,在业务中不同业务线也会经常根据 pay_num 来查询详细的支付详情信息。因此索引的建立有助于检索效率的提升。

在这个场景下,唯一索引起到了两个作用:避免重复保证唯一性,提高检索效率。

2.2.7 主键索引
隐藏内容 支付可见
购买文章 ¥8.99
订阅频道首月仅需 12 元/月,预计可省 1288 元
¥8.99
¥8.99购买
订阅频道免费读
× 订阅 Java 精选频道
首次订阅 ¥ 元/月 15元/月
订阅即可免费阅读所有精选内容