揭秘数据库性能飞跃的秘密：深入理解索引与执行计划的艺术

已于 2024-09-11 14:42:01 修改
阅读量1.5w 收藏 17
点赞数 20
分类专栏： MySQL数据库 文章标签： 数据库 adb android mysql数据库索引和执行计划
于 2024-09-11 14:39:17 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xyyy060908/article/details/142099213
版权
  •  作者简介：我是团团儿，是一名专注于云计算领域的专业创作者，感谢大家的关注
  •  座右铭：   云端筑梦，数据为翼，探索无限可能，引领云计算新纪元
  •  个人主页：团儿.-CSDN博客

目录

前言：

正文：

一.索引

1. 索引的作用

2. 索引的分类(算法) **

3. BTREE索引算法演变(了解) ***

二.Btree索引功能上的分类

1.聚集索引(唯一性) ***

2.辅助索引（多个） ***

3.聚集索引和辅助索引的区别 *****

三.辅助索引细分

四.索引的命令操作

1.查询索引

2.创建索引

单列的辅助索引:

多列的联合索引:

唯一索引:

前缀索引

3.删除索引

五.压力测试准备:

1.未做优化前压测

2.索引优化后

六.执行计划分析

1.作用

2.获取执行

3.分析执行计划

3.1 table        

3.2 type

全表扫描: ALL

索引扫描:

4.添加索引：

5.index: 全索引扫描

6.range: 索引范围扫描(> < >= <= , between and ,or,in,like )

7.ref: 辅助索引等值查询

eq_ref :

const(system) :

前言：

在这个信息化的时代，掌握数据库的知识就像是掌握了打开宝藏的钥匙。

索引和执行计划作为数据库管理的重要工具，能够帮助我们高效地组织和检索数据。

通过深入了解这两者的工作原理及其在实际应用中的优化方法，我们可以显著提升数据库的性能表现。

今天，我们就来聊聊MySQL数据库中的索引和执行计划，以及如何利用它们的优化功能。

正文：

一.索引

1. 索引的作用

类似于一本书中的目录,通过索引可以快速定位到数据具体的物理存储位置，起到优化查询的作用

2. 索引的分类(算法) **

B树    

默认使用的索引类型(原型：平衡二叉树算法)

R树

Hash

FullText

GIS 索引

3. BTREE索引算法演变(了解) ***

B- 叶子节点无水平指针

B+

叶子节点有水平指针，可以方便范围查询

B*

枝节点有水平指针，mysql当前默认

二.Btree索引功能上的分类

1.聚集索引(唯一性) ***

(1)MySQL 会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的.

(2)MySQL进行存储数据时,会按照聚集索引列值的顺序,有序存储数据行

(3)聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列向上生成枝和根

2.辅助索引（多个） ***

(1) 提取索引列的所有值,进行排序

(2) 将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点

(3) 在叶子节点中的值,都会对应存储主键ID

3.聚集索引和辅助索引的区别 *****

(1) 表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可

(2) 在一张表中,聚集索引只能有一个,一般是主键.

(3) 辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.

(4) 聚集索引,叶子节点存储的时有序的整行数据.

(5) MySQL 的表数据存储是聚集索引组织表，辅助索引查询表。

注：mysql的查询过程就是通过辅助索引找到主键索引的id号，再通过主键索引查数据行

三.辅助索引细分

单列辅助索引

联合索引(覆盖索引) *****

唯一索引

四.索引的命令操作

1.查询索引

desc city;

PRI   ==> 主键索引

MUL   ==> 辅助索引

UNI   ==> 唯一索引

show index from city\G（\G行转列）

2.创建索引

单列的辅助索引:

alter table city add index idx_name(name);

show index from city\G

多列的联合索引:

alter table city add index idx_c_p(countrycode,population);

show index from city\G

唯一索引:

alter table city add unique index uidx_dis(district);

注：有联合索引无法创建唯一索引

select count(district) from city;

select count(distinct district) from city;

前缀索引

alter table city add index idx_dis(district(5));

show index from city\G

3.删除索引

alter table city drop index idx_name;

alter table city drop index idx_c_p;

alter table city drop index idx_dis;

五.压力测试准备:

source /tmp/t100w.sql

1.未做优化前压测

mysqlslap --defaults-file=/etc/my.cnf \

--concurrency=100 --iterations=1 --create-schema='world' \

--query="select * from t100w where k2='MN89'" engine=innodb \

--number-of-queries=200 -uroot -verbose

2.索引优化后

创建k2列索引：

alter table t100w add index idx_k2(k2);

再次并发测试：

mysqlslap --defaults-file=/etc/my.cnf \

--concurrency=100 --iterations=1 --create-schema='world' \

--query="select * from t100w where k2='MN89'" engine=innodb \

--number-of-queries=200 -uroot -verbose

六.执行计划分析

1.作用

将优化器 选择后的执行计划 截取出来.便于管理管判断语句得执行效率

2.获取执行

desc   SQL语句

explain SQL 语句

mysql>

desc select * from t100w where k2='MN89';

3.分析执行计划

3.1 table        

表名

3.2 type

查询的类型:

全表扫描: ALL
索引扫描:

index 全索引扫描

range 范围

ref 等值

eq_ref 联合等值

const(system) 主键等值

NULL 没有索引

4.添加索引：

use world

desc city;

alter table city add index idx_c_p(countrycode,population);

5.index: 全索引扫描

desc select countrycode  from city;

desc select countrycode from city where countrycode='CHN' and population>5000000;

6.range: 索引范围扫描(> < >= <= , between and ,or,in,like )

desc select * from city where id>2000;

desc select  * from city where countrycode like 'CH%';

对于辅助索引来讲,!= 和not in等语句是不走索引的

对于主键索引列来讲,!= 和not in等语句是走range


 

desc select  * from city where countrycode='CHN' or countrycode='USA';

desc select  * from city where countrycode in ('CHN','USA');

一般改写为 union all

desc

select  * from city where countrycode='CHN'

union all

select  * from city where countrycode='USA';

7.ref: 辅助索引等值查询

desc

select  * from city where countrycode='CHN'

union all

select  * from city where countrycode='USA';

eq_ref :

多表连接时,子表使用主键列或唯一列作为连接条件

A join B

on a.x = B.y

desc select b.name,a.name ,a.population  

from city as a

join country as b

on a.countrycode=b.code

where a.population<100;

const(system) :

主键或者唯一键的等值查询

desc select * from  city where id=100;

期待您的关注~

团儿.
关注
专栏目录
数据库设计能力飞跃：计算机二级考试的高效备考策略
08-01
- 信息系统与数据库：主要考察信息系统分析、设计和数据库管理知识。 - 软件测试：主要考察软件测试的基本方法和技能。 考试形式一般包括笔试和上机操作考试，考试内容覆盖理论知识和实践技能。通过计算机二级考试...
性能飞跃的催化剂：揭秘Java JIT编译器的魔法
07-21
1. **解释执行与性能监控**：当Java程序启动时，JVM会首先采用解释器的方式执行字节码，这种方式虽然可以快速启动并保证跨平台兼容性，但执行效率相对较低。与此同时，JVM还会监控字节码的执行情况，识别出那些频繁...
实时性能的飞跃：VINS系统的优化策略
07-13
2. **实时性能**：能够实时处理视觉和惯性数据，适用于动态环境。 3. **高精度定位**：即使在视觉信息不足的情况下也能保持较高的定位精度。 4. **自动初始化**：系统能够自动进行初始化，无需外部干预。 5. **在线...
数据库系统概论课件：第13章 数据库新技术发展.ppt
06-24
【数据库系统概论：第13章 数据库...数据库技术的发展历程展示了从早期的简单数据管理到现在的复杂信息处理能力的飞跃。随着技术的不断进步，数据库将继续在信息时代发挥关键作用，满足日益增长的数据处理和分析需求。
在 MySQL 中通过自定义哈希分片实现大规模数据的多线程并行处理20241008
Narutolxy的博客
10-08 685
mysql
MySQL数据库的详细学习步骤
2401_87352036的博客
10-11 1069
MySQL数据库的详细学习步骤可以归纳为以下几个阶段，每个阶段都包含了特定的学习内容和目标。
Ubuntu有关redis的命令
weixin_53717695的博客
10-06 335
Ubuntu有关redis的命令
基于Java(Jsp+Sevlet）+MySql 实现的（Web）成绩管理系统
神仙别闹的自留地
10-08 1646
如果能把负责学生成绩管理的模块独立出来形成一个独立的系统，便可以有效降低教务系统的数据量，不仅可以方便管理员对于所有学生的信息进行系统的管理，而且便于教师对学生成绩进行查询和修改，学生也可以查询自己的成绩。因此，开发一套合适的、兼容性好的系统是很有必要的。：DAO层主要是做数据持久层的工作，负责与数据库进行联络的一些任务都封装在此，DAO层的设计首先是设计DAO的接口，然后定义此接口的实现类，然后就可在模块中调用此接口来进行数据业务的处理，而不用关心此接口的具体实现类是哪个类，显得结构非常清晰。
鲁班到家上门安装维修系统源码开发之结构功能解析
weixin_43744973的博客
10-08 1109
鲁班到家上门安装维修系统的源码开发是一个复杂但富有成效的过程。通过详细的需求分析、技术选型、功能模块设计以及前后端分离开发，该系统不仅提升了服务效率，还通过数字化管理优化了用户体验。未来，该系统有望在智能家居售后服务领域发挥更加重要的作用，引领行业向更加高效、专业、透明的方向发展。
如何使用ssm实现宠物论坛设计网站
最新发布
寒夜
10-12 510
【代码】ssm宠物论坛设计网站
ATAM需求说明-系统架构师（七十六）
ke1ying的博客
10-08 1076
1体系结构权衡分析法ATAM(Architecture Trade Off Analyzer Method)是一种常见的结构权衡分析法，该框架主要关注系统的（），针对性能、安全性、可用性和可修改性，在系统开发前进行分析、评价和这种。A共享数据库的集成方式通常将应用程序的数据存储在一个共享数据库中，通过制定统一的数据库模式来处理不同应用集成需求。场景和需求的收集，架构视图和场景的实现，属性模型的构造和分析，架构评价和折中。7企业信息资源集成管理的前提是对企业（）的集成，其核心是对企业（）的集成。
nacos修改为单机启动模式和数据库连接
shenxiaomo1688的博客
10-12 195
nacos默认为集群方式启动，如果想修改为单机启动，可以修改nacos的bin目录下的startup.cmd，linux环境为startup.sh,将export MODE="cluster"修改为export MODE="standalone"。1.nacos的conf目录下有一个nacos-mysql.sql文件，在mysql数据库中新建nacos数据库，然后在nacos数据库执行nacos-mysql.sql脚本，将数据导入到mysql;
PostgreSQL学习笔记十：锁机制详解
软件行业技术文化交流。
10-11 1059
它通过不同级别的锁来控制对数据对象的并发访问，主要包括表级锁、行级锁、页级锁、咨询锁（Advisory Locks）以及死锁（Deadlocks）。咨询锁可以用于实现更细粒度的锁控制，例如，在不同的事务之间同步对特定资源的访问。咨询锁可以是会话级别的或事务级别的，允许用户在不同的进程或事务之间进行协调。：设计事务时，应尽量减少锁的持有时间，并避免长时间运行的事务，因为这会增加死锁的风险。这些锁可以是行级的、事务级的，或者是咨询锁（advisory locks），它们用于控制对表或行的并发访问。
应对Redis大Key挑战：从原理到实现
欢迎来到【技术拾光者】，在这里，我们专注分享编程教程、技术趋势、实战案例和优质资源。不定期举办技术问答、专家访谈和职业指导。加入我们，共同探索技术世界，提升技能，拓展职业道路！
10-09 1160
在使用Redis时，大Key问题会导致性能下降、内存消耗过多等问题。本文详细探讨了大Key问题的成因、影响及解决方案。大Key问题通常由大字符串、大集合或大对象引起，影响系统的内存使用和响应时间。通过拆分大Key、分页读取、异步删除和监控预警等方法，可以有效解决大Key问题。文章提供了Java示例代码，展示如何处理和优化Redis中的大Key问题，包括手动拆分大Key、分页读取大集合和异步删除大Key等。通过这些方法，开发者可以显著提升Redis的性能和稳定性。
2024Java最新面试题总结（针对于一些小厂、中厂）
weixin_62375676的博客
10-07 1641
2024最新Java面试题，针对一些小厂，中厂
LlamaIndex 结构化数据库交互指南增强（text2sql）
洛阳泰山的博客
10-10 1235
许多现代数据系统都依赖于结构化数据，例如或Snowflake数据仓库。LlamaIndex提供了许多由LLM提供支持的高级功能，可以从中创建结构化数据 非结构化数据，以及通过增强的文本到 SQL 功能分析这些结构化数据。：任何应用程序都应该意识到，执行 任意SQL查询可能存在安全风险。建议 根据需要采取预防措施，例如使用受限角色、只读 数据库、沙箱等。本指南帮助介绍这些功能中的每一项。SQLsql。
【MySQL】入门篇—数据库基础：数据库管理系统（DBMS）的功能
thinking_chou的专栏
10-11 608
示例：使用SQL创建一个"书籍"表该SQL语句使用数据定义语言(DDL)创建了一个名为"books"的表。表包含5个字段:book_id(主键)、title、author、publication_year和category。每个字段都定义了数据类型和约束,如主键、非空等。
同城O2O系统源码与跑腿配送平台的架构设计与开发方案详解
meihusdk的博客
10-08 565
同城O2O系统与跑腿配送平台的开发，既是技术的挑战，也是商业机会的探索。通过合理的架构设计与清晰的开发方案，开发者可以构建出高效、稳定且易于扩展的平台，为用户提供更为便捷的服务。
数据库-01MYSQL-001MySQL知识点查漏补缺
10-12 117
mysql数据库知识点锦集
Apache Spark 2.0：Tungsten引擎深度解析与性能飞跃
4. 性能监控与自适应优化：Tungsten引擎还集成了性能监控工具，可以根据实时运行情况动态调整优化策略，进一步提升性能。 Tungsten执行引擎代表了Spark向更高性能和更低延迟方向的重大进化。它不仅优化了计算过程，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值