mysql实战45讲重点知识手册

2022-03-20 18:00:54   4  举报





AI智能生成

《MySQL实战45讲》是一本由宁超老师所著的关于MySQL数据库的书籍。这本书通过45个案例，讲解了如何使用MySQL数据库来存储、查询和分析数据。书中涵盖了从基础概念到高级技巧的所有内容，包括数据库设计、SQL语言、索引优化、事务处理等。此外，书中还介绍了如何利用Python编程语言来操作MySQL数据库，以及如何使用MySQL Workbench等工具进行数据库管理和开发。总之，《MySQL实战45讲》是一本实用性强、内容丰富的MySQL学习手册，适合所有想要学习和掌握MySQL数据库技能的读者阅读。

mysql

mysql实战45讲

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+树。

主键索引的叶子节点存的是整行数据。聚簇索引（clustered index）。 非主键索引的叶子节点内容是主键的值。二级索引（secondary index）。

B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

与B树的区别？

回表

普通索引查询方式，则需要先搜索二级索引树，得到主键的值，再到主键索引树搜索一次。这个过程称为回表。

避免回表

覆盖索引

在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

最左前缀原则

联合索引的最左N个字段，或字符串索引的最左M个字符。

索引下推

联合索引

建立联合索引的时候，如何安排索引内的字段顺序?

索引的复用能力

空间占用尽量少

失效的几种情况

索引维护

页分裂

插入新纪录时，如果所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。

自增主键 是追加操作，不涉及到挪动其他记录，不会触发叶子节点的分裂。

字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢?

适合用业务字段直接做主键的场景

KV场景

只有一个索引； 该索引必须是唯一索引。

重建索引 k

字符串加索引

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本，如邮箱

索引前缀长度--区分度越高越好

注意使用前缀索引就用不上覆盖索引对查询性能的优化

身份证建索引

倒序存储，用身份证的后六位做索引

<span style="font-weight: 700; color: rgb(0, 0, 0); font-family: "PingFang SC", "Lantinghei SC", "Microsoft Yahei", "Hiragino Sans GB", "Microsoft Sans Serif", "WenQuanYi Micro Hei", Helvetica, sans-serif; font-size: 16px;">hash字段 创建一个整数字段，来保存身份证的校验码，同时创建索引

异同点

优化器选错索引的原因？

区分度

基数 cardinality

采样

扫描行数

使用普通索引需要把回表的代价算进去，主键索引可以直接取出整行数据

<span style="color: rgb(53, 53, 53); font-family: "PingFang SC", "Lantinghei SC", "Microsoft Yahei", "Hiragino Sans GB", "Microsoft Sans Serif", "WenQuanYi Micro Hei", Helvetica, sans-serif; font-size: 16px;">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保证持久性

回滚

undo log

锁

全局锁

FTWRL 用于全库备份时让整个库处于只读状态  mysqldump

表级锁

表锁 lock tables … read/write

元数据锁 MDL

如何安全地给小表加字段？

InnoDb 行锁

排他锁/悲观锁

SELECT … FOR UPDATE

其他事物只能不带锁查，不可写，不可带锁查

共享锁

select...lock in share mode

其他事物可以不带锁或带共享锁查，不可写

乐观锁

版本号

两阶段锁

死锁

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待

避免

银行家算法 安全序列

解决

等待超时

死锁检测

<span style="color: rgb(255, 90, 5); font-family: "PingFang SC", "Lantinghei SC", "Microsoft Yahei", "Hiragino Sans GB", "Microsoft Sans Serif", "WenQuanYi Micro Hei", Helvetica, sans-serif; font-size: 16px;">怎么解决由这种热点行更新导致的性能问题呢？

进阶操作

视图

创建

CREATE [OR REPLACE] VIEW 视图名称 [(字段列表)] AS 查询语句

修改

ALTER VIEW 视图名 AS 查询语句;

查看

DESCRIBE 视图名；

删除

DROP VIEW 视图名;

使用

当做正常的表来增删改查