mysql实战45讲重点知识手册
2022-03-20 18:00:54 2 举报AI智能生成
登录查看完整内容
《MySQL实战45讲》是一本由宁超老师所著的关于MySQL数据库的书籍。这本书通过45个案例，讲解了如何使用MySQL数据库来存储、查询和分析数据。书中涵盖了从基础概念到高级技巧的所有内容，包括数据库设计、SQL语言、索引优化、事务处理等。此外，书中还介绍了如何利用Python编程语言来操作MySQL数据库，以及如何使用MySQL Workbench等工具进行数据库管理和开发。总之，《MySQL实战45讲》是一本实用性强、内容丰富的MySQL学习手册，适合所有想要学习和掌握MySQL数据库技能的读者阅读。
作者其他创作
大纲/内容
https://funnylog.gitee.io/mysql45/
45讲
长连接
连接器
不建议使用，查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空；除非 静态表
显示指定mysql> select SQL_CACHE * from T where ID=10；
若命中，验证表权限
8.0后被整体删除
查询缓存
词法分析
如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒
Unknown column ‘k’ in ‘where clause’
语法分析
语义分析
分析器
在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
优化器
验证表权限
调用引擎接口取数据
执行器
server层
负责数据的存储和提取
内部结构
行、页、区、段、表空间
数据库 I/O 操作的最小单位是页
前后页指针
文件头
文件尾
页头
文件通用部分
最大最小纪录
用户记录
空闲空间
记录部分
页目录存储的是槽，槽相当于分组记录的索引，二分查找
B+树
页目录
索引部分
三个部分
页
共享表空间就是ibdata1，独立表空间放在每个表的.ibd（数据和索引）和.frm（表结构）为后缀的文件中。单独的表空间只存储该表的数据，索引和插入缓冲的BITMAP等信息
尝试去解决问题越快越好（提交或者杀死事务），因为不经过痛苦缓慢的 mysqldump 过程，你就不能回收浪费的磁盘空间。
一个老事务可能引起ibdata1文件快速增长
数据字典，也就是 InnoDB 表的元数据变更缓冲区双写缓冲区撤销日志
共享表空间 ibdata1
存储结构
server层与引擎层如何交互的？
InnoDB 默认
MyISAM
复杂查询中的 临时表
Memory
存储引擎层
crash-safe
环形 固定空间
WAL
“双1”配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit都设置成 1
落盘
脏页和干净页
刷脏页flush的四种场景
刷脏页
为什么快？
redo log
保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制
statement
基于binlog row模式恢复数据
row 推荐
mixed
binlog的三种格式对比
update流程
主从同步
binlog
区别
事物回滚 ，保证原子性
undo log
日志模块
查询流程
两阶段提交
更新流程
从tasks表2118212505条记录中删除47599697行数据
MySQL 快速删除大量数据（千万级别）的几种实践方案
删除
插入和更新操作可以将普通索引的操作记录到change buffer就同步返回结果，再异步merge到磁盘
适用于写多读少
redo log节省随机写磁盘的IO消耗(顺序写)，change buffer节省随机读磁盘的IO消耗
redo log中会记录 change buffer中的改动
change buffer
唯一索引需要将数据页读入内存，判定是否冲突，需要随机IO
普通索引 而不是 唯一索引
身份证号
等值查询和范围查询场景中的性能就都非常优秀
按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))
插入成本高，只适用于静态存储引擎
有序数组
把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置
适用于只有等值查询的场景
哈希表
树高 = log n
每个节点的左儿子小于父节点，父节点又小于右儿子
数据库存储大多不适用二叉树，因为树高过高，会适用N叉树
平衡二叉树
N叉树”的N值在MySQL中是可以被人工调整的么？
在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表，数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。
主键索引的叶子节点存的是整行数据。 聚簇索引（clustered index）。非主键索引的叶子节点内容是主键的值。 二级索引（secondary index）。
B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。
与B树的区别？
普通索引查询方式，则需要先搜索二级索引树，得到主键的值，再到主键索引树搜索一次。这个过程称为回表。
在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？
覆盖索引
联合索引的最左N个字段，或字符串索引的最左M个字符。
索引下推
最左前缀原则
索引的复用能力
空间占用尽量少
建立联合索引的时候，如何安排索引内的字段顺序?
失效的几种情况
联合索引
避免回表
回表
N叉树
索引存储结构
插入新纪录时，如果所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。
自增主键 是追加操作，不涉及到挪动其他记录，不会触发叶子节点的分裂。
字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢?
页分裂
只有一个索引；该索引必须是唯一索引。
KV场景
适合用业务字段直接做主键的场景
重建索引 k
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本，如 邮箱
索引前缀长度--区分度越高越好
注意使用前缀索引就用不上覆盖索引对查询性能的优化
倒序存储，用身份证的后六位做索引
span style=\
异同点
身份证建索引
字符串加索引
索引维护
基数 cardinality
采样
区分度
扫描行数
使用普通索引需要把回表的代价算进去，主键索引可以直接取出整行数据
采用force index强行选择一个索引
考虑修改语句，引导MySQL使用我们期望的索引
新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引
索引选择异常和处理
优化器选错索引的原因？
索引
事务的所有操作要么全部成功，要么全部回滚
原子性
应用层：总是从一个一致性的状态转换到另一个一致性的状态(不对应用出现错误状态数据)
一致性
多个事务并发执行时，一个事务的执行不应影响其他事务的执行
读未提交（read uncommitted）
半一致性读(semi-consistent)特性增加了update操作的并发性
读提交（read committed）
可重复读（repeatable read)
串行化（serializable ）
隔离级别
一个事务在处理过程中读取了另外一个事务未提交的数据
脏读
指一个线程中的事务读取到了另外一个线程中提交的insert的数据
gap间隙锁
幻读
一个事务范围内，多次查询某个数据，却得到不同的结果
不可重复读
可能性
异常情况
读视图 ReadView
DATA_TRX_ID表示最近修改该行数据的事务ID
DATA_ROLL_PTR则表示指向该行回滚段undo log的指针
undo log
MySQL InnoDB MVCC实现
MVCC 多版本并发控制
快照读和当前读
隔离的实现
mysql的默认隔离级别
隔离性
已被提交的事务对数据库的修改应该永久保存在数据库中 redo log保证持久性
持久性
ACID
回滚
FTWRL 用于全库备份时让整个库处于只读状态 mysqldump
全局锁
表锁 lock tables … read/write
如何安全地给小表加字段？
元数据锁 MDL
表级锁
其他事物只能不带锁查，不可写，不可带锁查
SELECT … FOR UPDATE
排他锁/悲观锁
其他事物可以不带锁或带共享锁查，不可写
select...lock in share mode
共享锁
版本号
乐观锁
两阶段锁
死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待
银行家算法 安全序列
避免
等待超时
死锁检测
解决
死锁
InnoDb 行锁
锁
事务
InnoDb核心
CREATE [OR REPLACE]VIEW 视图名称 [(字段列表)]AS 查询语句
创建
ALTER VIEW 视图名AS 查询语句;
修改
DESCRIBE 视图名；
查看
DROP VIEW 视图名;
当做正常的表来增删改查
不建议写入，当使用复杂的语句时，因为 MySQL 没办法精确定位实际数据表中的记录 (分组和聚合函数，或者是 UION 和 DISTINCT ）
优缺点
使用
视图
SHOW CREATE PROCEDURE test_procedure;
查询
调用
DROP PROCEDURE test_procedure;
存储过程
CREATE TRIGGER 触发器名称 {BEFORE|AFTER} {INSERT|UPDATE|DELETE}ON 表名 FOR EACH ROW 表达式;
SHOW TRIGGERS \\G;
由mysql通过事件驱动
DROP TRIGGER 触发器名称;
触发器
隐藏主键列(行标识 _rowid)
show table status
表信息
找出造成阻塞的process id
schema_table_lock_waits 阻塞表
innodb_lock_waits
sys 系统数据库
show processlist 查看线程状态
processlist
information_schema
系统信息
记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令
默认关闭，会占用大量资源，开发环境中可以开启，方便查看执行了哪些SQL语句 进行调试
通用查询日志
记录运行时间和检查记录数超过指定值的查询
my.ini配置
min_examined_row_limit
系统变量
慢查询日志
服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等
默认开启
错误日志
系统日志
索引覆盖
Using index
使用了索引下推
Using index condition
高效的
server层将对引擎层提取的结果进行再次过滤
using where
无法利用索引直接完成排序，需要额外对数据排序
Using filesort
低效的
Extra
explain
sort_buffer
优化 联合索引
sort_buffer_size 外部排序
全字段排序
rowid排序流程
rowid排序 max_length_for_sort_data
随机排序 order by rand()
案例
order by 排序优化
server 和 引擎层
where
优化
进阶操作
mysql重点知识手册
0 条评论
回复 删除
下一页
