Mysql进阶优化篇02——索引失效的10种情况及原理(二)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 文章目录1️⃣ 数据库调优的场景2️⃣数据准备


3.2 不遵守最左前缀匹配原则

运行如下sql

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name='abcd';

将使用索引idx_age。

下面的sql不会使用索引,因为我没没有创建classId或者name的索引。或

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.name='abcd';

在 MySQL 建立联合索引时会遵守最佳左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。


MySQL 可以为多个字段创建索引,一个索引可以包括 16 个字段,对于多列字段,过滤条件要使用所以那必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法使用。如果查询条件中没有使用这些字段中的第一个字段时,多列索引不会被使用

下面的sql查询就是遵守这一原则的正确打开方式。


EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.classId=4 AND student.name='abcd';

思考:下面sql会不会使用索引呢?

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.age = 30 AND student.name='abcd';
• 1

答案是会!因为优化器会执行优化的哦,会调整查询条件的顺序。不过在开发过程中我们还是要保持良好的开发习惯哟。

思考:删去索引idx_age_classididx_age,只保留idx_age_classid_name

DROP INDEX idx_age_classid ON student;
DROP INDEX idx_age ON student;

执行如下sql,会不会使用索引?

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE  student.age = 30 AND student.name='abcd';
• 1

案是会,但是只会用一部分。看看执行结果。

使用了idx_age_classid_name,但是key_len是5,也就是说只使用了age部分的排序,因为age是int类型,4个字节加上null值列表一共5个字节哦。想想就知道,B+树是先按照age排序,再按照classid排序,最后按照name排序,因此不能跳过classId的排序直接就使用name的排序哦。


3.3 不按照递增顺序插入主键

对于一个使用 InnoDB 存储引擎的表来说,在我们没有显式的创建索引时,表中的数据实际上都是存储在 聚簇索引 的叶子节点的。而记录又是存储在数据页中,数据页和记录又是按照 记录主键值从小到大 的顺序进行排序,所以如果我们 插入 的记录的 主键是依次增大 的话,那我们每插满一个数据页就换到下一个数据页继续插,而如果我们插入的 主键值忽大忽小 的话,就比较麻烦了,假设某个数据页存储的记录已经满了,它存储的主键值在 1~100 之间


如果此时再插入一条主键值为 9 的记录,那它插入的位置就如下图:

可这个数据页已经满了,再插进来咋办呢?我们需要把当前 页面分裂 成两个页面,把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么?意味着:性能损耗!所以如果我们想尽量避免这样无谓的性能损耗,最好让插入的记录的 主键值依次递增 ,这样就不会发生这样的性能损耗了。 所以我们建议:让主键具有 AUTO_INCREMENT ,让存储引擎自己为表生成主键,而不是我们手动插入


我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性,在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小,顺序写入,减少页分裂。


🎀Tips:

我们一般将主键策略设置为自动递增AUTO_INCREMENT哦!(核心业务表除外,后面会介绍这种情况)

3.4 计算、函数、类型转换(自动或手动)导致索引失效

思考:这两条 sql 哪种写法更好

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; 

从执行结果上说,上面sql执行结果没有区别。但是从运行效率上说,第1条sql比之后的要好,因为第一条可以使用上索引!而因为第二条使用了函数,即使建立索引也会导致索引失效。


为何使用函数时优化器会使索引失效呢?您想想,我们只是对student.name字段建立了索引,但并没有对LEFT(student.name,3)建立索引,使用函数后的关键字跟我们建立的B+树可对应不来,怎么能使用B+树优化查询呢?


3.5 类型转换导致索引失效

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

创建联合索引。

CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);
• 1


执行结果如下。

注意到key_len是10,说明值使用到了idx_age_classId_name索引中的age与classId部分,而name则没有用上。这是因为classId>20是范围查询,导致其右边的列索引失效。


如果想要完全使用到索引,需要按如下方式创建索引:先写等值查询的列,再写范围查询的列

create index idx_age_name_classid on student(age,name,classid); 

🎨Q:为什么条件查询会导致范围条件后面的列索引失效?

比如说有三个字段 a b c,建立复合索引a_b_c

此时叶子节点的数据排序后可能为

(a=1 b=1 c=1) (a=1 b=2 c=1) (a=1 b=2 c=3)

(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)

(a=3 b=0 c=1) (a=3 b=3 c=5) (a=3 b=8 c=6)

假设查找 select a,b,c from table where a = 2 and b = 5 and c = 2

此时先根据a = 2找到第二行的四条数据

(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)

然后根据b=5查到两条

(a=2 b=5 c=1) (a=2 b=5 c=2)

最后根据c=2查到目标数据

(a=2 b=5 c=2)

接下来 假设使用了范围条件

select a,b,c from table where a = 2 and b >1 and c = 2

此时先根据a = 2找到第二行的四条数据

(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)

然后根据b>1查到四条数据

(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)

此时要查找c=2了 但是我们发现 这四条数据的c分别是

3,5,1,2 是无序的 所以索引失效了

总结:

因为前一个条件相同的情况下,后续列才会是有序的。


🎃Tips:

应用开发中范围查询,例如:金额查询,日期查询往往都是范围查询。应将查询条件放置where语句最后。(创建的联合索引中,务必把范围设计到的字段写在最后)

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
4
分享
相关文章
MySQL实现文档全文搜索,分词匹配多段落重排展示,知识库搜索原理分享
本文介绍了在文档管理系统中实现高效全文搜索的方案。为解决原有ES搜索引擎私有化部署复杂、运维成本高的问题,我们转而使用MySQL实现搜索功能。通过对用户输入预处理、数据库模糊匹配、结果分段与关键字标红等步骤,实现了精准且高效的搜索效果。目前方案适用于中小企业,未来将根据需求优化并可能重新引入专业搜索引擎以提升性能。
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
35 9
图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
当 MySQL 磁盘 I/O 次数过高时,可通过调整参数优化。控制刷盘时机以降低频率:组提交参数 `binlog_group_commit_sync_delay` 和 `binlog_group_commit_sync_no_delay_count` 调整等待时间和事务数量;`sync_binlog=N` 设置 write 和 fsync 频率,`innodb_flush_log_at_trx_commit=2` 使提交时只写入 Redo Log 文件,由 OS 择机持久化,但两者在 OS 崩溃时有丢失数据风险。
38 3
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
79 9
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
随着数据量增长和业务扩展,单个数据库难以满足需求,需调整为集群模式以实现负载均衡和读写分离。MySQL主从复制是常见的高可用架构,通过binlog日志同步数据,确保主从数据一致性。本文详细介绍MySQL主从复制原理及配置步骤,包括一主二从集群的搭建过程,帮助读者实现稳定可靠的数据库高可用架构。
84 9
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
112 15
MySQL底层概述—7.优化原则及慢查询
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案,最后总结了迁移体验,包括工具部署和操作特点,也指出功能有优化空间及暂不支持的部分,期待其不断优化。
云数据库:从零到一,构建高可用MySQL集群
在互联网时代,数据成为企业核心资产,传统单机数据库难以满足高并发、高可用需求。云数据库通过弹性扩展、分布式架构等优势解决了这些问题,但也面临数据安全和性能优化挑战。本文介绍了如何从零开始构建高可用MySQL集群,涵盖选择云服务提供商、创建实例、配置高可用架构、数据备份恢复及性能优化等内容,并通过电商平台案例展示了具体应用。