《Mysql实战45讲》笔记及总结归纳

  • 前言
  • 基础篇
  • 基础架构：sql语句如何执行？
  • 日志系统：更新语句如何执行？
  • 事务隔离：为什么你改了我还看不见
  • 深入浅出索引
  • 全局锁和表锁：给表加个字段怎么这么多阻碍？
  • 行锁功过：如何减少行锁对性能的影响？
  • 事务到底是隔离的还是不隔离的？
  • 实践篇
  • 其他
  • 林晓斌的心路历程


前言

作为一名软件开发者，对数据库无疑是要非常熟悉了。不光要在执行sql语句的时候，明白它在数据库中如何执行，更要掌握诸多的数据库优化技巧，让你的软件系统超级流畅。

基础篇

基础架构：sql语句如何执行？


mysql实战45讲极客时间 pdf_数据库

MySql大致分为两层：server层和存储引擎。
server层（连接器、查询缓存、分析器、优化器、执行器）：存储过程、触发器、视图。
存储引擎：数据的存储和提取。如InnoDB等

InnoDB的底层实现是B+树
常用操作：
解决长连接大量占用内存问题：mysql_reset_connection初始化连接资源

在查询缓存里查询：select SQL_CACHE * from T where ID=10

日志系统：更新语句如何执行？

update adm_log_alarm set c=c+1 where id = 2

1、执行语句之前要先连接数据库，这就是连接器的工作
2、分析器知道了这是一条更新语句，遂清空查询缓存（我们不提倡使用查询缓存的原因）
3、优化器决定使用哪个索引，然后执行器负责执行
4、更新操作涉及到了日志模块：redo log(重做日志)、bin log(归档日志)，来实现WAL技术：先写日志，后写磁盘。此技术可使数据库异常重启数据不丢失（crash-safe）
5、redo log是InnoDB特有的日志系统，是物理日志，循环写入；bin log是Mysql的server层实现的，是逻辑日志，追加写入（保证数据库可恢复到之前某时刻的状态）

事务隔离：为什么你改了我还看不见

事务：保证一组数据库操作，要么全部成功，要么全部失败。
事务隔离级别：

  • 读未提交（一个事务未提交时，它做的变更可以被别的事务看到）
  • 读提交（一个事务未提交后，它做的变更才被别的事务看到），oracel
  • 可重复读（一个事务执行过程中的数据，与启动时保持一致，当然在提交前，对外界不可见）,
  • 串行化（读、写加锁、若冲突，后访问的事务在等前访问的事务执行完）
    查询事务隔离级别：show variables like "transaction_isolation"

启动事务方式：

begin 或 start transaction      //结束用commit，回滚用rillback
set autocomit = 0        //执行sql即开启事务commit、rollback结束事务

我们建议set autocommit = 1通过显示方式启动事务

深入浅出索引

索引实现方式

  • 哈希表。适用于等值查询的场景，比如Memcached及其他一些Nosql引擎。
  • 有序数组。只适用于静态存储引擎
  • 搜索树。InnoDB使用了B+树索引模型，每一个索引对应着一颗B+树。
creat table T (id int primary key auto_increment, k int not null, name varchar(16), index(k) )engine = InnoDB

主键索引的叶子结点为整行数据，也称为聚簇索引，而非主键索引的叶子结点是主键的值，需要回到主键索引（回表），所以也称为二级索引。因此尽可能用主键查询
因此，从性能和存储空间考量，主键要尽可能的少占空间，普通索引的占用空间才越小。
建一个带索引的表：

CREATE TABLE T (
id int NOT NULL,
id_card VARCHAR(32) DEFAULT NULL,
name VARCHAR(32) DEFAULT NULL,
age int DEFAULT NULL,
PRIMARY KEY (id),
KEY id_card (id_card),      //key  添加索引
KEY name_age (name,age)        
)

覆盖索引：select ID from T where k between 3 and 5,此过程不需要回表。此为重用的一个优化手段。
最左前缀原则：联合索引的最左N个字段，重复使用索引。
索引下推：MySQL5.6引入索引下推，在索引遍历过程中，直接过滤掉不满足条件的记录，如select * from T where name like "张*" and age=10

全局锁和表锁：给表加个字段怎么这么多阻碍？

Mysql中的锁：全局锁、表锁、行锁

全局锁
使用场景式做全库逻辑备份

flush table with read lock   //FTWRL     应用场景：全库逻辑备份。

备用方案：
1、事务隔离，需支持
2、set global readonly = true //存在一些弊端：readonly有时用于判断主从库；客户端异常不会更新readonly状态，而FTWRL会释放这个全局锁。

表级锁：

读操作不互斥，读写互斥，写写互斥

lack tables T1 read,T2 write

元数据锁：Mysql5.5引入了MDL，访问表时自动加上，当对一个表做增删改查的时候，加MDL读锁，当要对表结构变更操作的时候，加MDL写锁。

如何安全的给小表加字段？
事务不提交，就一直占着MDL锁

alter table tbl_name NOWAIT/WAIT N add column   # 增加ddl

行锁功过：如何减少行锁对性能的影响？

两阶段锁协议

  • 概念：在InnoDb事务中，行锁是需要的时候加上，等到事务结束后释放。
  • 用途：如果事务中需要锁多个行，把最可能造成冲突、影响并发度的尽量往后放。

死锁与死锁检测

mysql实战45讲极客时间 pdf_mysql实战45讲极客时间 pdf_02


解决办法

  • timeout（时间不容易确定）✘
  • 死锁检测（耗费cpu）✘
  • 控制并发度（不适用于客户端很多的情况）✘
  • 在数据库服务端或中间件做并发控制。✔
  • 将一行改为逻辑上的多行来减少锁冲突 ✔

事务到底是隔离的还是不隔离的？

start transaction   # 直行到之后的第一个语句的时候，事务才真正启动
start transaction with consistent snapshot   # 马上启动一个事务

在可重复读隔离级别下，事务在启动的时候就拍了个“快照”（基于整库）。
数据表中的每行数据，可能有多个版本，每个版本都有自己的trx_id

一致性读视图

  • 期间某行数据被修改过，但是事务无论什么时候差，结果都是一样的
  • 用于支持可重复读和读提交隔离级别的实现

当前读

  • 更新数据都是先读后写的，这个读，只能读当前的值，称为“当前读”。
  • select语句如果加锁，也是当前读。

可重复读：核心是一致性读，而事务更新数据的时候，只能用当前读，如果当前记录的行锁被其他事务占用的话，就需要进入锁等待。
度提交：每一个语句执行前都会重新算出一个新的视图

实践篇

待补充。。。

其他

林晓斌的心路历程

看懂源码，了解原理，解决问题
学习路径：先要会用，然后可以发现问题
推荐书籍：高性能Mysql
DBA的前途：了解业务，做业务的架构师
好的习惯：多写sql，培养感觉知道语句执行的大概时间复杂度，是全表扫描还是索引扫描，需不需要回表等等