学会MySQL索引

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

就一起来聊一聊MySQL索引。

什么是索引?

百度百科是这样描述的:

索引是为来加速对表中数据行中的检索而创建的一种分散的数据结果,时针对表而建立的,它是由数据页面以外的索引页面组成,每个索引页中的行都含有逻辑指针,以便加速检索物理数据

其实,索引的概念大家都很清楚,也知道索引能够提升查询效率,但大部分童鞋在怎么建索引,建在哪些字段上有以下常见误解:

  • 新建表时不需要建索引,后续才添加索引

  • where条件后的字段均建索引

  • 简单SQL不需要索引,联合查询才需要索引

  • 联合索引的顺序是where条件后字段的先后顺序

  • 对于区分度小的字段上也新建索引,如状态,性别等字段等。

索引区分度

在说上述问题之前,我们先来看看另一个概念,就是区分度。

区分度: 指字段在数据库中的不重复比

区分度在新建索引时有着非常重要的参考价值,在MySQL中,区分度的计算规则如下:

字段去重后的总数与全表总记录数的商。

例如:

select count(distinct(name))/count(*) from t_base_user;

结果如下:

count(distinct(name))/count(*)
1.0000

其中区分度最大值为1.000,最小为0.0000,区分度的值越大,也就是数据不重复率越大,新建索引效果也越好,在主键以及唯一键上面的区分度是最高的,为1.0000。在状态,性别等字段上面的区分度值是最小的。 (这个就要看数据量了,如果只有几条数据,这时区分度还挺高的,如果数据量多,区分度基本为0.0000。也就是在这些字段上添加索引后,效果也不佳的原因。)

值得注意的是:    如果表中没有任何记录时,计算区分度的结果是为空值,其他情况下,区分度值均分布在0.0000-1.0000之间。

如何建索引

   (一)  : 区分度
个人强烈建议, 建索引时,一定要先计算该字段的区分度,原因如下:

1. 单列索引
可以查看该字段的区分度,根据区分度的大小,也能大概知道在该字段上的新建索引是否有效,以及效果如何。区分度越大,索引效果越明显。

2.多列索引(联合索引)
多列索引中其实还有一个字段的先后顺序问题,一般是将区分度较高的放在前面,这样联合索引才更有效,例如:

select * from t_base_user where name="" and status=1;

像上述语句,如果建联合索引的话,就应该是:

alter table t_base_user add index idx_name_status(name,status);

而不是:

alter table t_base_user add index idx_status_name(status,name);

(二) 最左前缀匹配原则
  MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如

select * from t_base_user where type="10" and created_at<"2017-11-03" and status=1, (该语句仅作为演示)

在上述语句中,status就不会走索引,因为遇到<时,MySQL已经停止匹配,此时走的索引为:(type,created_at),其先后顺序是可以调整的,而走不到status索引,此时需要修改语句为:

select * from t_base_user where type=10 and status=1 and created_at<"2017-11-03"

即可走status索引。

(三) 函数运算
  不要在索引列上,进行函数运算,否则索引会失效。因为b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。

(四) 扩展优先
  扩展优先,不要新建索引,尽量在已有索引中修改。如下:

select * from t_base_user where name="andyqian" and email="andytohome"

在表t_base_user表中已经存在idx_name索引,如果需要加入idx_name_email的索引,应该是修改idx_name索引,而不是新建一个索引。

误解纠正

  上面说了,如何新建索引,现在我们就可以来回答,在第一步中存在的误解了。

误解一:   新建表时不需要建立索引,后续才加索引

答: 一个好的数据表设计,在一开始就要考虑索引的创建,而不是等到后续出问题了,影响业务使用了,才新建索引来救场,而且后续创建索引的成本也相对高很多。(这就是给生产事故留下生根发芽的机会呀)

误解二:   where条件后的字段均建索引

答: 这个误解比较常见,但where条件后的字段不需要全部建立索引,过多的索引,也会导致索引文件剧增,也还达不到期望中的效果。详细请参考上述新建索引的小节。

误解三:   简单SQL不需要建立索引,联合查询采建立索引

答: 这个误解就得好好说说了,现在互联网公司特别是B/S架构下,业务逻辑均剥离在代码逻辑层,到最后SQL层面,其实都是一些简单的SQL,只有些许连接查询,更多的还是单表操作,(C/S架构中有很多在SQL层面的写逻辑的),你说这些语句简不简单。

误解四:    联合索引的顺序是where条件后字段的先后顺序

答: 我们刚才说过,联合索引的顺序,是根据最左前缀原则,以及区分度来区分的,和where条件后字段的先后顺序无关。

误解五: 对于区分度较小的字段新建索引

答: 在区分度较小的字段上新建索引,基本无效,还会增加大量的索引文件,你说是不是得不偿失。

索引重不重要?

  上面介绍了MySQL索引的概念,新建索引时的一些技巧。这么理论的东西,对于平时没有使用或使用比较少的童鞋,此时对索引的重要性可能还没那么直观,那么,我就来说说我在索引上吃过的亏,踩过的坑!同时也是未建索引常见问题!

0. 导致慢查询
这个问题可是未建索引的常客哦,(这里也还有很多细节呢,如: 隐式类型转换等等)

1. 导致服务超时

场景 :
  在某次上线时,作为服务提供者,提供服务给业务方使用。一开始以为就提供一个简单的服务,也已经测试完成,心里还在窃喜,今天总算可以早早回家了!


描述 :
  实际一上线,在生产环境中导致业务方请求调用时,而且每次请求均超时,数据也已落地,此时只能review代码,最后发现生产中有个慢查询导致,活活的花费了10多秒,这个语句有多简单呢,你绝对想不到,其实就是一个单表的where条件查询语句。你说这种原因导致服务不可用,你说冤不冤,气不气!(这也是我为什么说,一个好的数据表设计,从一开始就要考虑新建索引了)。

2. 数据库服务器CPU 100%

在查询频率比较高的SQL上,如果由于未建索引,导致慢查询的话,那可是会导致数据库服务器CPU 100%,影响可是整个系统哦。

小结
上面说了好几类,由于没建立索引而导致的问题,轻则导致慢查询,影响系统效率,重则,导致CPU 100%,影响整个系统的使用,看到这里,你说索引重不重要?

最后

上面简单说了,索引是什么?有什么用,以及建立索引时的一些技巧,还着重说了,索引的重要性。那么索引这么重要,在平时编码时如何避免呢?以下是我个人的建议:

  1. 在建表时就应该考虑添加索引,如:外键字段,等等。

  2. 在写完SQL后,一定要查看执行计划。尽量避免全表扫描。

  3. 如果是已有表中添加索引,一定要先计算该字段的区分度。

  4. 联合索引,将区分度大放在前面。

  5. 遵从MySQL左列前缀优先原则\

[2]H. Berenson, P. Bernstein, J. Gray, J.Melton, E. O’Neil,and P. O’Neil. A critique of ANSI SQL isolation levels. InProceedings of the SIGMOD International Conference on Management of Data, pages1–10, May 1995.

[3]Michael J. Cahill, Uwe Röhm, and Alan D.Fekete. 2008. Serializable isolation for snapshot databases. In SIGMOD ’08:Proceedings of the 2008 ACM SIGMOD international conference on Management of data, pages 729–738, New York, NY, USA. ACM.

[4]Michael James Cahill. 2009. Serializable Isolation for Snapshot Databases. Sydney Digital Theses. University of Sydney, School of Information Technologies

[5] A. Fekete, D. Liarokapis, E. O’Neil, P.O’Neil, andD. Shasha. Making snapshot isolation serializable. www.codexueyuan.com In ACM transactions on database systems, volume 39(2), pages 492–528, June 2005.

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
1
分享
相关文章
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
400 66
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
575 9
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
310 80
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
129 22
 MySQL秘籍之索引与查询优化实战指南
浅入浅出——MySQL索引
本文介绍了数据库索引的概念和各种索引结构,如哈希表、B+树、InnoDB引擎的索引运作原理等。还分享了覆盖索引、联合索引、最左前缀原则等优化技巧,以及如何避免索引误用,提高数据库性能。
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
192 10