08MySQL核心篇一

最新推荐文章于 2024-07-13 14:25:12 发布

SMP!

最新推荐文章于 2024-07-13 14:25:12 发布

阅读量108

点赞数

本文链接：https://blog.csdn.net/weixin_45598345/article/details/119165262

版权

本文详细讲解了MySQL中的索引结构（B树与辅助/聚集索引）、索引管理、执行计划优化、不同存储引擎（如InnoDB和MyISAM）的特点、事务处理、锁机制以及关键参数。深入探讨了索引对查询性能的影响和存储引擎在性能和安全性方面的选择.

摘要由CSDN通过智能技术生成

MySQL核心篇一

1.MySQL核心一

1.MySQL核心一

01.索引及执行计划

a.索引作用与种类

作用：提供了类似于书中目录的作用,目的是为了优化查询
种类：
B树索引  ---MySQL默认的引擎是B树
Hash索引
R树
Full text
GIS

b.B树索引上的功能分类（辅助索引聚集索引）

在这里插入图片描述

https://blog.csdn.net/weixin_42237937/article/details/104473495?spm=1001.2014.3001.5501

看上面这篇文章详细接介绍了辅助索引和聚集索引

c. 辅助索引(S)怎么构建B树结构的? 聚集索引©怎么构建B树结构的?

S
(1). 索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度
id  name  age  gender
select  *  from  t1 where id=10;
问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO.

C
(1) 在建表时,设置了主键列(ID)
(2) 在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚集索引组织表)
(3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点

d.辅助索引细分与索引树高度

辅助索引细分：
1.普通的单列辅助索引
2.联合索引
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表
查询
3.唯一索引
索引列的值都是唯一的.

在这里插入图片描述

e.索引的创建与管理命令

在这里插入图片描述

f.什么情况需要回表查询

在这里插入图片描述

g.执行计划获取及分析

介绍：

(1)
获取到的是优化器选择完成的,他认为代价最小的执行计划.
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.
如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。
(2) select 获取数据的方法
1. 全表扫描(应当尽量避免,因为性能低)
2. 索引扫描
3. 获取不到数据

执行计划获取：
在这里插入图片描述

h.优化器针对索引的算法

在这里插入图片描述

i.优化器算法的查找与开启

在这里插入图片描述

j.索引下推快速查询（适用于多条件联合查询）

在这里插入图片描述

k.mrr算法查询

在这里插入图片描述

L.SNLJ算法

在这里插入图片描述

02.存储引擎

a.简介

相当于Linux文件系统，只不过比文件系统强大

功能了解：
数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持
等.

介绍(Oracle MySQL)：
InnoDB
MyISAM
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV

引擎种类查看：
show engines;
存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎类型。
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持:
TokuDB    
RocksDB
MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
现在很多的NewSQL,使用比较多的功能特性.

在这里插入图片描述
InnoDB个MyISAM存储引擎的替换：

环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
职责
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM
解决方案:
1.升级MySQL 5.6.10版本
2. 迁移所有表到新环境
3. 开启双1安全参数

b.InnoDB简介与优缺点

在这里插入图片描述

优点：
1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL )

在这里插入图片描述

c.存储引擎查看

在这里插入图片描述

d.碎片的整理与释放

在这里插入图片描述

e.存储引擎体系结构（宏观结构）

在这里插入图片描述

f.存储引擎体系结构（微观结构）

在这里插入图片描述

g.事务以及事务的ACID特性

在这里插入图片描述

h.事务的生命周期（事务控制语句）

在这里插入图片描述

自动提交事务：

应用场景：

设置方法：

i.隐式事务语句提交

在这里插入图片描述

j.InnoDB事务的ACID如何保证

redo log 重做日志
在这里插入图片描述

在这里插入图片描述

undo lods 回滚日志：

k.MySQL锁机制

“锁”顾名思义就是锁定的意思。
“锁”的作用是什么？
在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与).
悲观锁:行级锁定(行锁)
谁先操作某个数据行,就会持有<这行>的(X)锁.
乐观锁: 没有锁

在这里插入图片描述
隔离级别

影响到数据的读取,默认的级别是 RR模式.
transaction_isolation   隔离级别(参数)
负责的是,MVCC,读一致性问题
RU  : 读未提交,可脏读,一般部议叙出现
RC  : 读已提交,可能出现幻读,可以防止脏读.
RR  : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
SR   : 可串行化,可以防止死锁,但是并发事务性能较差
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.
例如:
[world]>select * from city where id=999 for update;
[world]>commit;