59. MySQL索引是如何提高查询效率的呢?(MySQL面试第二弹)中

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 59. MySQL索引是如何提高查询效率的呢?(MySQL面试第二弹)中

59. MySQL索引是如何提高查询效率的呢?(MySQL面试第二弹)中


考虑维度优势

例如action列的值包含:1、2、3、4、5,那么该列的维度就是5。

维度越高(理论上维度的最大值就是数据行的总数),数据列包含的独一无二的值就越多,索引的使用效果越好。

对于维度很低的数据列,索引几乎不会起作用,因此没有必要加索引。

例如性别列的值只有男和女,每种查询结果占比大约50%。一般当查询优化处理器发现查询结果超过全表的30%的时候,就会跳过索引,直接进行全表扫描。

对短小的值加索引

对短小的值加索引,意味着索引所占的空间更小,可以减少I/O活动,同时比较索引的速度也更快。

尤其是主键,要尽可能短小。

另外,InnoDB使用的是聚集索引(clustered index),也就是把主键和数据行保存在一起。主键之外的其他索引都是二级索引,这些二级索引也保留着一份主键,这样在查询到索引以后,就可以根据主键找到对应的数据行。如果主键太长的话,会造成二级索引占用的空间变大。

比如下面的action索引保存了对应行的id。

为字符串前缀加索引

前边已经讲过短小索引的种种好处了,有时候一个字符串的前几个字符就能唯一标识这条记录,这个时候设置索引的长度就是非常划算的做法。

前面已经提供了设置索引length的例子,这里就不举例子了。

复合索引的左侧索引

创建复合索引的语法如下:

我们可以看到,最左侧的column1索引总是有效的。

索引加锁

对于InnoDB来说,索引可以让查询锁住更少的行,从而可以在并发情况下拥有更佳表现。

下面演示一下查询锁与索引之间的关系。

前面使用的t_user_action_log表目前有一个id为主键,还有一个二级索引action。

下面这条语句的修改范围是id值为1 2 3 4所在的行,查询锁会锁住id值为1 2 3 4 5所在的行。

update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where id<5;

首先创建数据库连接1,开启事务,并执行update语句

set autocommit=0;
begin;
update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where id<5;

然后开启另外一个连接2,分别执行下面几个update语句

-- 没有被锁
update ijiangtao_local_db_mysql.t_user_action_log set name='c2' where id=6;
-- 被锁
update ijiangtao_local_db_mysql.t_user_action_log set name='c2' where id=5;

你会发现id=5的数据行已经被锁定,id=6的数据行可以正常提交。

  1. 连接1提交事务,连接2的id=1id=5的数据行可以update成功了。
-- 在连接1提交事务
commit;

如果不使用索引

ip_address没有索引的话,会锁定全表。

连接1开启事务以后commit;之前,连接2对该表的update全部需要等待连接1释放锁。

set autocommit=0;
begin;
update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where ip_address='8.8.8.1';

覆盖索引

如果索引包含满足查询的所有数据,就被称为覆盖索引(Covering Indexes),覆盖索引非常强大,可以大大提高查询性能。

覆盖索引高性能的原因是:

索引通常比记录要小,覆盖索引查询只需要读索引,而不需要读记录。

索引都按照值的大小进行顺序存储,相比与随机访问记录,需要更少的I/0。

大多数数据引擎能更好的缓存索引,例如MyISAM只缓存索引。

ijiangtao_local_db_mysql表的action列包含索引。使用explain分析下面的查询语句,对于索引覆盖查询(index-covered query),分析结果Extra的值是Using index,表示使用了覆盖索引 :

explain select `action` from ijiangtao_local_db_mysql.t_user_action_log;

聚簇索引

聚簇索引(Clustered Indexes)保证关键字的值相近的元组存储的物理位置也相同,且一个表只能有一个聚簇索引。

字符串类型不建议使用聚簇索引,特别是随机字符串,因为它们会使系统进行大量的移动操作。

并不是所有的存储引擎都支持聚簇索引,目前InnoDB支持。

如果使用聚簇索引,最好使用AUTO_INCREMENT列作为主键,应该尽量避免使用随机的聚簇主键。

从物理位置上看,聚簇索引表比非聚簇的索引表,有更好的访问性能。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
16天前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
9天前
|
SQL 缓存 关系型数据库
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴因未能系统梳理MySQL缓存机制而在美团面试中失利。为此,尼恩对MySQL的缓存机制进行了系统化梳理,包括一级缓存(InnoDB缓存)和二级缓存(查询缓存)。同时,他还将这些知识点整理进《尼恩Java面试宝典PDF》V175版本,帮助大家提升技术水平,顺利通过面试。更多技术资料请关注公号【技术自由圈】。
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
|
3天前
|
SQL 算法 关系型数据库
面试:什么是死锁,如何避免或解决死锁;MySQL中的死锁现象,MySQL死锁如何解决
面试:什么是死锁,死锁产生的四个必要条件,如何避免或解决死锁;数据库锁,锁分类,控制事务;MySQL中的死锁现象,MySQL死锁如何解决
|
10天前
|
SQL 关系型数据库 MySQL
美团面试:Mysql如何选择最优 执行计划,为什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴面试美团时遇到了关于MySQL执行计划的面试题:“MySQL如何选择最优执行计划,为什么?”由于缺乏系统化的准备,小伙伴未能给出满意的答案,面试失败。为此,尼恩为大家系统化地梳理了MySQL执行计划的相关知识,帮助大家提升技术水平,展示“技术肌肉”,让面试官“爱到不能自已”。相关内容已收录进《尼恩Java面试宝典PDF》V175版本,供大家参考学习。
|
7天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
38 0
|
7天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
34 0
|
26天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
103 6
|
23天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
56 3
Mysql(4)—数据库索引
|
26天前
|
SQL Ubuntu 关系型数据库
Mysql学习笔记(一):数据库详细介绍以及Navicat简单使用
本文为MySQL学习笔记,介绍了数据库的基本概念,包括行、列、主键等,并解释了C/S和B/S架构以及SQL语言的分类。接着,指导如何在Windows和Ubuntu系统上安装MySQL,并提供了启动、停止和重启服务的命令。文章还涵盖了Navicat的使用,包括安装、登录和新建表格等步骤。最后,介绍了MySQL中的数据类型和字段约束,如主键、外键、非空和唯一等。
62 3
Mysql学习笔记(一):数据库详细介绍以及Navicat简单使用
|
9天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
45 2