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