【MySQL】MySQL开发注意事项与SQL性能优化步骤

最新推荐文章于 2024-07-21 17:07:57 发布

老衲の少女心

最新推荐文章于 2024-07-21 17:07:57 发布

阅读量555

点赞数

分类专栏： #资源文章标签：数据库大数据 mysql

本文链接：https://blog.csdn.net/wan_yuan/article/details/110130430

版权

#资源专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL简介

地表最流行数据库

在这里插入图片描述

名副其实

在这里插入图片描述

什么是MySQL？

世界上应用最广泛且灵活的开源关系数据库

MySQL 是应用最广泛的开源关系数据库，是许多常见网站，应用程序和商业产品使用的主要关系数据存储，MySQL 有20多年的社区开发和支持历史，是一种可靠，稳定而安全的基于SQL的数据库管理系统，MySQL数据库适用于各种使用案例，包括任务关键型应用程序，动态网站以及用于软件，硬件和设备的嵌入式数据库

MySQL特点

易安装：支持rpm等包安装，支持解压安装，支持编译安装，docker部署
易扩展：开源特性，插件式存储引擎，灵活的高可用架构
易使用：标准SQL，丰富工具
高性能：基于硬件支撑，基于架构设计

MySQL资源

在这里插入图片描述

MySQL文档

在这里插入图片描述

MySQL架构

接入层：外部接口
服务层：数据库功能实现层
引擎层：数据存取的具体实现
存储层：物理存储

MySQL概念—存储引擎

MySQL 中的数据用各种不同的技术存储在文件（或者内存）中，这些技术中的每一种技术都使用不同的存储机制，索引技巧，锁定水平并且最终提供广泛的不同功能和能力，通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能

在这里插入图片描述

MySQL概念—数据库，模式，用户

数据库和模式是同一个概念，是若干个数据库对象的集合

在这里插入图片描述
用户用于实现访问数据库的权限认证

在这里插入图片描述

MySQL概念—数据库，用户，存储引擎关系

单个用户可以连接多个数据库
一个数据库对应单个存储引擎

MySQL安装—rpm安装

在这里插入图片描述

MySQL安装—解压安装

在这里插入图片描述

MySQL安装—编译安装

在这里插入图片描述

MySQL安装—docker部署

在这里插入图片描述

MySQL安装—四种安装对比

安装方式	环境依赖	复杂度	安装时间	定制性	限制	适用场景
rpm安装	外网或repo源或rpm包	简单	短	差	单机单库	开发测试
解压安装	外网或二进制包	略难	短	高	单机多库	线上部署
编译安装	外网或源码包	较难	长	高	单机多库	学习
docker部署	外网或docker仓库	简单	短	高	单机多库	开发测试

MySQL架构

MySQL架构 — 系统架构

在这里插入图片描述

MySQL架构 — 文件，进程，内存

在这里插入图片描述

MySQL架构 — 文件管理

在这里插入图片描述

MySQL架构 — 日志系统

错误日志（error）
- MySQL服务实例启动，运行或者停止等相关信息
普通查询日志（general）
- MySQL服务实例运行的所有SQL语句或者MySQL命令
二进制日志（binary）
- 对数据库执行的所有更新语句，不包括 select 和 show 语句
慢查询日志（slow）
- 执行时间超过 long_query_time 设置值的SQL语句，或者没有使用索引的SQL语句
重做日志（redo）
- 记录了事务修改后的内容，以便实例crash时可以恢复事务
回滚日志（undo）
- 记录事务修改前的内容，以便事务回滚时可以将数据恢复到事务开始前

MySQL架构 — 日志系统_binlog

binlog内容
- 记录MySQL数据库的所有更新操作，但不记录对数据库的查询select或show等（这些可以被general日志记录）
binlog用途
- 主从复制
- 增量备份恢复
binlog模式
- statement：记录sql语句（一个sql只记录一次）
- row：记录每一行修改记录（一个sql影响多行会产生多条记录）
- mix：根据
binlog查看
- 自带工具mysqlbinlog查看

binlog示例

在这里插入图片描述

MySQL架构 — 日志系统_redo/undo

在这里插入图片描述

MySQL复制技术 — 基本原理

在这里插入图片描述

MySQL复制技术 — 复制技术发展

事务丢失问题
主库宕机时，主库上已提交的事务未发送到从库。
半同步改进
确保至少一个从库收到 binlog。
数据不一致问题
从库收到binlog并生成binglog后主库确认提交，若此时从库sql进程还未将数据写入，查询或主从切换存在主从不一致。
超时退化为异步
复制从库接收应答超时后，主从复制退化为异步复制
增强半同步改进
至少一个从库完成写库后返回提交确认，主库才确认完成commit
若环境主库性能下降
弱网环境等待从库应答会降低主库性能
组复制要求
至少3节点的集群。
原理
基于paxos协议进行广播通知所有主节点，携带全局事务号确保所有节点接受事务的顺序一致性。可容忍失败节点：(n-1)/2

在这里插入图片描述

MySQL复制技术 — 复制技术对比

主从复制	无法应用的场景	优点	缺点
组复制	对性能要求高，追求高吞吐，低延迟的场景	数据一致性好	性能低，吞吐量降低，TP999上升
异步复制	对数据一致性要求高	性能高	数据不一致问题比较严重
半同步复制	对数据一致性要求高	性能中	数据不一致问题比异步复制好一点，但是有时候会退化为异步复制
增强半同步复制	对数据一致性要求高，无法忍受数据不一致的问题	性能中	数据不一致问题比半同步复制好一些，但是有时候会退化为异步复制

MySQL高可用架构

在这里插入图片描述

MySQL工具

MySQL工具—开发工具—HeidiSQL

在这里插入图片描述

MySQL工具—开发工具—SQLyog

在这里插入图片描述

MySQL工具—开发工具—Workbench

在这里插入图片描述

MySQL工具—备份恢复

文本导出

select * from Table
into outfile '/路径/文件名'
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n';

文本导入

LOAD DATA INFILE /路径/文件名' 
INTO TABLE Table
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\r\n';

逻辑备份

mysqldump
--master-data=2
--flush-logs
--single-transaction
--databases db1 db2
--user=<xxx>
--password=<xxxx>
--port=<3306>
--host=<10.0.0.1>
> backup_fulldb.sql;

逻辑恢复

mysql
--user=<xxx>
--password=<xxxx>
--port=<3306>
--host=<10.0.0.1>
< backup_fulldb.sql;

MySQL工具—备份恢复—工具对比

类型	工具	使用场景	特征
导出	mysql命令之select into outfile	文本导出	纯文本
导入	mysql命令之load data infile	文本导入	纯文本
备份	mysqldump	少量数据备份	逻辑备份，单线程
备份/恢复	xtrabackup	大量数据备份，快速主从搭建	物理备份，多线程
备份	mydumper		逻辑备份，多线程
恢复	mysqlloader		逻辑备份，多线程

MySQL开发

MySQL开发—数据库对象—与Oracle的异同

在这里插入图片描述

MySQL数据结构----数据类型

在这里插入图片描述

MySQL数据结构----数据类型—数值

在这里插入图片描述

MySQL数据结构----数据类型—字符

在这里插入图片描述

MySQL数据结构----数据类型—二进制

在这里插入图片描述

MySQL数据结构----数据类型—时间

在这里插入图片描述

MySQL开发—字符集—相关参数

在这里插入图片描述

MySQL开发—字符集—主要分类

在这里插入图片描述

MySQL开发—索引结构

主键
btree+索引结构：叶子结点包含完整的行数据
二级索引
btree+索引结构：叶子结点包含主键的指针和二级索引字段值

在这里插入图片描述

MySQL开发—索引结构–主键与非主键

主键查询无需回表
非主键查询需要回表

在这里插入图片描述

MySQL开发—设计参考—合适的数据类型

数值
建议整数使用 int，小数使用 decimal
字符类型
不建议使用枚举，集合类型，大对象，建议使用varchar2
二进制
不建议使用大对象存储图音视，大文本
日期
- 区别一：时区支持：timestamp支持时区。datetime不支持时区
- 区别二：数据范围：timestamp有效数据范围为1970 ~ 2038，datetime为0001 ~ 9999
- 建议：建议使用datetime

MySQL开发—设计参考—主键的选择

在这里插入图片描述

自增序列情况下增加650：直接在主键树的最右侧添加
非自增序列情况下增加350：需要在300和400之间插入，要移动前后叶子结点，若相关页存放已满，还需要分裂块

建议：使用自增序列，默认使用int，超过40亿使用bigint

MySQL开发—设计参考—索引选择

如何选择索引字段
- where，on中经常出现的
- 和其他表有关联的
- group by，order by 中出现的
- 非空字段
排列联合索引字段
- 高频出现的字段放最前面
- 重复率低的字段放最前面
影响索引使用
- 对索引列计算
- 字段可为空
- 避免使用不确定查询（or，not/not in，<>，like）

MySQL开发—设计参考—字符集

character set: utf8mb4 
collate:
	utf8mb4_0900_ai_ci 
	utf8mb4_unicode ci 
	utf8mb4_general ci

字符集设置

db级全局设置最佳
表级设置次选
避免字段级设置

字符集选择

utf8mb4

MySQL优化

MySQL优化—执行计划—查看方式

在这里插入图片描述

MySQL优化—执行计划—查看特定SQL

查看特定sql

explain

在这里插入图片描述

查看特定连接的sql执行计划

explain for connection <线程id>

在这里插入图片描述

MySQL优化—执行计划—阅读方式

在这里插入图片描述
阅读方法

不同的id由大往小读
相同的id由上往下读

示例：

先访问 id = 1 的t1表，返回结果集给上一层id=1
id=1的有两条记录
- 先访问id=1且table=<derived2>(注：此处为子查询s1)，返回结果集给下一个id=1的其他记录
- 在访问id=1 且table=t2的表，进行关联匹配

伪代码

loop s1<derived2> for (select max(id) as id form t1):
	loop t2 for (select * from t2):
		if t2.id = s1.id then
			return t2;
		else
			next;
		end if;
	end loop;
end loop;

MySQL优化—执行计划—属性简介

id：查询序号
select_type：查询方式
table：所使用的表名称
– <unionM,N>：该行是id为M和N的行的并集
– <derivedN>：该行是id为N的行的派生表
– <subqueryN>：该行是物化子查询的结果
partitions：使用的分区信息，NULL表示该表不是分区表
type：连接类型
possible_keys：可能使用的索引，如果此列是NULL，则没有相关的索引，建议检查 WHERE字句，以确定是否适合创建索引或调整where条件
key：MySQL实际使用的索引。在大多数情况下，key中的值都在possible_key里面
key_len：显示索引使用的字节数，key_len显示的值为索引字段的最大长度，并非实际使用长度，越短越好
ref：ref列显示哪些列或者常量与key中的索引进行比较，以从表中选择行
rows：MySQL查询需要遍历的行数。
filtered:被条件过滤的行数百分比。最大值为100，表示没有行过滤，值从100减小表示过滤增加。rows表示检查的行数，rows * filtered/100表示过滤后的行数，也就是与下表进行连接的行
Extra：执行计划的额外信息

MySQL优化—执行计划—select-type

在这里插入图片描述
示例

在这里插入图片描述
简单查询

在这里插入图片描述

子查询

在这里插入图片描述
关联查询

在这里插入图片描述

MySQL优化—执行计划—type（连接类型）

连接优先级：System --> const --> eq_ref --> ref --> fulltext --> ref_or_unll --> index_merge --> unique_subquery --> index_subquery --> range --> index --> ALL

system : 该表只有一行，这是const连接的特殊情况。（单行常量表）
const：该表最多只有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中的值做为常量，因为它值读取一次。const在基于主键或者唯一性索引比较时使用。(主键/唯一索引的等值查询)
eq_ref：与驱动表的连接查询，后表(被驱动表)仅读取一行数据，当被驱动表存在主键索引或者unique+not null时使用， eq_ref用于使用"="运算的索引列。（主键/唯一索引的关联查询）
ref：与驱动表的连接查询，后表(被驱动表)读取一行或多行数据。ref用于仅使用key的最左前缀，或者说key不是 PAIMARY KAY或UNIQUE索引。换句话说，如果连接无法根据KEY选择单个行，则使用ref，否则使用eq_ref。ref可以用于使用"=“或者”<=>"运算符进行的比较。（左侧索引/普通索引的等值查询）
fulltext：使用FULLTEXT进行连接
ref_or_null：这种方式类似于ref，但是MySQL会额外搜索包含NULL值的行
index_merge：索引合并优化，把多个索引合并为一个使用
unique_subquery：该类型替换eq_ref形式下的IN子查询，子查询中最多返回一个值，提高查询效率
index_subquery：该类型类似于unique_subquery，它用来替代子查询，子查询中返回值的个数不确
range：使用索引去检索一个范围的行数据，key列是使用到的索引，ref列为NULL。range可以在=，<>，>,>=,<,<=,IS NULL,<=>,BETWEEN,LIKE或IN() （索引范围扫描）
index：index与ALL相似，只是索引树被扫描（全索引扫描）
ALL：全表扫描，如果驱动表不是以const方式获取数据的，则可以会导致非常糟糕的查询性能。通常可以添加索引来避免权标扫描（全表扫描）