MySQL知识点总结
2023-05-24 11:17:48 0 举报AI智能生成
MySQL的知识点总结，适合后端、测试、运维
作者其他创作
大纲/内容
SQL语法DQL
SQL语法DDL、DML、DCL
数据库对象
表
存储数据的逻辑单元，以行和列的形式存在，列为字段，行为记录
数据字典
系统表、存储数据库相关信息的表
约束
概念
执行数据校验规则，用于保证数据完整的规则
分类
非空约束
not null
唯一约束
unlque
主键约束
primary key
外键约束
foreign key
检查约束
check
默认值的表
default
视图
视图的理解
视图是从一个或几个基本表（或者视图）导出的表，它与基本表不同，是一个虚表
视图的作用
提高了重要性
对数据重构，却不影响程序的运行
提高了安全性能
创建视图
查看视图
更新视图
修改、删除视图
索引
存储过程和存储函数
概述
一组经过预先编译的SQL语句的封装
分类
没有参数（无参数无返回）
仅带IN类型（有参数无返回）
仅带OUT类型（无参数有返回）
既带IN又带OUT（有参数有返回）
带INOUT（有参数有返回）
创建存储过程
调用存储过程
存储函数使用
存储过程和存储函数查看、修改、删除
优缺点
优点
一次编译多次使用，提升SQL执行效率
减少开发工作量，抽象后模块可重复使用
安全性强
减少网络传输量
减少数据库IO次数
缺点
可移植性差，无法跨库移植
调试困难
版本管理困难，比如表索引引发变化可能导致存储过程失效，且存储过程本身无版本管理
不适合高并发场景，不能很好的应对分库分表
触发器
概念
当对数据库表中数据执行插入、更新和删除，需要同时执行一些数据库的逻辑时，可以使用触发器来实现
创建
查看、删除
优缺点
优点
可以确保数据的完整性
可以在操作数据之前，对数据进行合法性检查
缺点
不受应用层面控制且由事件驱动，导致维护困难
相关数据表结构更改会导致触发器出错
MySQL8.0新特性
窗口函数
公用表表达式
MySQL架构
MySQL的数据目录
主要数据目录
文件存放路径：/application/mysql
相关命令：/application/mysql/bin
配置文件目录：/etc/my.cnf
数据库文件系统
db.out
.frm
.idb
.MYD
.MYI
MySQL自带的数据库
MySQL
存储了MySQL的用户账户和权限信息，一些存储过程、事件定义信息、运行过程中产生的日志信息、一些帮助信息及时区信息
information_schema
保存着MySQL服务器维护的所有其他数据库信息，比如有哪些表、视图、触发器、列、索引
performance_schema
保存着MySQL服务器运行过程中的一些状态信息，用于监控服务器各类性能指标
sys
通过视图的形式把information_schema和persormance_schema结合起来，帮助系统管理员和开发人员监控MySQL技术性能
用户和权限管理
权限表
user表、db表、table_priv表等
用户管理
创建
修改
删除用户
设置密码
权限管理
授予
查看
回收权限
角色管理
创建、查看、激活、删除角色
配置文件的使用
逻辑结构
逻辑架构分析
子主题 1
连接层
服务层
SQL接口
解析器
优化器
查询缓存
引擎层
SQL查询流程
子主题 1
查看执行计划
数据库缓冲池
存储引擎
查看默认引擎
设置表的索引
不同存储引擎
innoDB
MySQL默认事务形引擎，可以确保事务的完整提交和回滚
除了增加和查询外，还需要更新和删除操作，那么应该优先用innoDB
innoDB写的效率差一些，并且会占用更多磁盘空间以保存数据和索引
innoDB不仅缓存索引还会缓存真实数据，对内要求较高，而且内存大小对性能有确定性影响
MyISAM
支持全文索引、压缩、空间函数，但不支持事务、行级锁、外键、数据库服务器崩溃后无法安全恢复
查询和插入速度快
针对数据统计有额外的常数存储，故count（*）的查询效率很高
数据文件结构.frm存储结构.MYD存储数据.MYI存储索引
Archive
CSV
Memory
innoDB和MyISAM的对比
innoDB提供了良好的事务管理、崩溃修复功能和并发控制。因为innoDB支持事务，所以对于要求事务完整性的场合需要选innoDB，缺点是读写效率稍差，占用数据空间比较大
MyISAM适合以读写操作和插入操作为主，只有很少的更新、删除操作，并对事务的要求不高。优点为占用空间小，处理速度快，缺点是不支持事务的完整性和并发性
子主题 3
innoDB数据存储结构
关系图
innoDB行格式
COMPACT
Redundant
Dynamic
Compressed
数据页组成部分
文件头
最大最小记录
用户记录
空闲空间
页目录
文件尾
区的结构
一个区会分配64个连续的页
段的结构
若干个零散的页面及一些完整的区组成
表空间
系统表空间
独立表空间
撤销表空间
临时表空间
索引及调优
索引数据结构
索引优缺点
优点
提高数据效率，降低数据库IO成本
通过创建唯一索引，可以保证数据库中每一行数据的唯一性
对于有依赖关系的子表和父表联合查询时，可以提高查询速度
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低CPU消耗
缺点
创建和维护索引需要耗费时间，且随着数据量的增加，耗费时间也会增加
索引需要占用磁盘空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸
索引虽然提高了查询速度，同时却会降低更新表的速度，因为当对表中的数据进行增加、删除和修改时，需要动态的维护索引
innoDB索引
聚簇索引
二级索引（辅助索引、非聚簇索引）
联合索引
常见数据结构
Hash
AVL
B-Tree
B+Tree
R-Tree
索引的创建和设计原则
索引创建和删除
索引分类
那些情况适合创建索引
那些情况不适合创建索引
性能分析工具
常见性能参数
数据库服务器优化
系统性能参数
统计SQL查询成本：last_query_cost
慢查询日志
慢查询日志分析工具：mysqldumpslow
SQL执行成本
show profile
分析查询语句：explain
索引优化和查询优化
调优维度和调优方向
索引失效的情况
关联查询优化
子查询优化
排序优化
group by 优化
主键如何设计
数据库设计规范
六大范式
反范式化
ER模型
数据库对象编写建议
数据库其他调优策略
如何定位调优问题
调优的维度和步骤
优化MySQL服务器
优化数据库结构
大表优化
限定查找范围
读写分离
垂直拆分
水平拆分
其他调优策略
服务器语句超时处理
创建全局通用表空间
临时表性能优化
隐藏索引对调优的帮助
事务
事务基础知识
事务处理原则
事务的ACID特性
事务的状态
事务的隔离级别
MySQL事务日志
redo日志
redo日志
redo日志写入过程
redo日志刷盘时机
崩溃恢复
undo日志
undo日志
通用链表结构
undo页面链表
undo日志具体写入过程
锁
并发事务访问相同记录
读-读情况
写-写情况
读-写或写-读情况
锁的不同角度分类
按照数据操作类型划分
读锁
写锁
按照对数据的操作粒度划分
表级锁
表级共享锁/独占锁
意向锁
自增锁
元数据锁
行锁
页级锁
按程序员角度划分
悲观锁
乐观锁
按加锁方式划分
显示锁
隐藏锁
全局锁
死锁
锁的内存结构
多版本并发控制
快照读
当前读
版本链
ReadView
InnoDB是如何解决幻读的
日志与备份
其他数据库日志
慢日志查询
通用查询日志
错误日志
二进制日志
中继日志
数据定义语句日志
主从复制
主从复制原理
一主一从常见配置
主机配置
从机配置
主从同步数据一致性问题
双主双从
垂直拆分
水平拆分
数据库备份与恢复
mysqldump实现逻辑备份
备份全部数据库
备份部分数据库
备份部份表
备份单表部分数据
只备份结构或只备份数据
物理备份：直接备份整个数据库
mysql命令恢复速度
单库备份中恢复单库
全量备份恢复
从全量备份中恢复单库
从单库备份中恢复单表
数据库迁移
物理迁移和逻辑迁移
相同版本数据库间迁移的注意点
不同版本数据库间迁移的注意点
全量迁移
表的导入与导出
收藏
收藏
0 条评论
下一页
