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