MySQL 实战 45 讲总结

用户头像
`
发布于: 2020 年 07 月 13 日

最近上完了极客时间的MySQL的课程,收获很多!课程链接:https://time.geekbang.org/column/intro/100020801

为了让自己更好的梳理知识点,这里总结了这门课所学到的内容,希望对大家也有帮助。

MySQL 总结

1. MySQL的目的

MySQL是一个数据库软件,主要目的是为了实现数据的CRUD,同时需要支持一些其它的业务场景。MySQL的原理了不同的软件设计思路,这些思路的主要目的是为了支撑不同的用户场景和需求。 下面先总结用户场景,再总结设计思路和原理。 最后在考虑实际操作中可能存在的优化手段。 

2. 用户场景

  1. 单thread crud

  2. Concurrent threads crud

  3. 表结构改变

  4. 主从架构

  5. 数据库备份

3. 数据库设计原理

数据库为了满足这些用户场景,主要使用了三种基本设计思路: 

1) 索引(加快查询)

2) 锁(处理concurrent threads crud)

3) WAL结构(redo log + bin log) 

3.1 数据库一个query的流程

1) 客户端:发起链接,发出请求

2) 连接器:管理链接,权限验证

3)分析器:分析语法

4)缓存:如果分析器判断缓存里有查询的数据,直接进入缓存提取数据

5)优化器:执行计划生成,索引选择

6)执行器:操作引擎,返回结果

7)存储引擎:存储数据,提供读写接口.存储引擎有innodb, 缓存,myISAM等。

3.2 单thread crud的设计

单thread crud有两个问题需要解决:

1) 怎么加速查询

2) 怎么做到crash safe.

3.2.1 加速查询

1) 查缓存。 

这个很好理解,但是其实最好不要turn on 缓存,因为缓存可能被频繁更新,挺贵的。 

2)索引

索引的基本结构是B+ tree,一般一个node默认有1024个children, 主要十亿行数据的索引树,也只有3层树。索引在物理结构上是另外的磁盘文件,因此索引会消耗额外的磁盘空间,同时数据被更新时,索引结构可能也会改变,因此也会增加额外的operation cost,但是由于大多数业务场景是读多写少,因此可以用索引。 

索引分为主键索引和非主键索引。 

主键索引的leaves指向数据,非主键索引的leaves指向主键。因此非主键索引需要作回表操作。

非主键索引的优化有: 

1) 覆盖索引。对于联合索引,使用前面几个key columns,查询联合索引的后一个columns,不需要回表。

2)主键查询:根据非主键索引查询主键值,不需要回表。 

3)最左前缀:可以用联合索引的最左边的columns作where 查询。 

需要注意的是,优化器选择索引的时候,是根据索引列的统计信息做选择,有时候统计信息不准确,会让优化器作出错误的选择。这时候的优化手段有:

1) force index

2) rebuild index. 

普通索引还有唯一索引和非唯一索引两类,它们的区别在于update语句,唯一索引更慢,因为它需要去磁盘确定这个索引是否存在,而非唯一索引直接把这个更新值放在 内存的change buffer里。 

3) change buffer

Update语句将结构先放在内存的一块change buffer里,再查询的时候可以直接访问change buffer,这样可以加快update和select语句。同时change buffer满了以后才会写入磁盘。 change buffer的大小是可以控制的。

3.2.2 Crash safe

怎么确保数据库可以crash safe呢。 

为了保证crash safe,如果是INNODB的引擎,主要采用两阶段commit办法。 

Step 1: redo log prepare (阶段1)

Step 2: binlog写入操作

Step 3: redo log commit (阶段2)

如果Step 1结束后失败,那么transaction失败

如果Step 2结束后失败,transaction可以判定为成功,因为MySQL会根据redo log and binlog自动commit redo log. 

两阶段commit可以configure每多少个command写入磁盘,这样可以优化数据库performance. 

3.3 concurrent threads crud

数据库会维持一个thread pool,每个connection都会pick一个thread(thread and connection 区别?)。 在concurrent情境下,一个问题是多个crud transaction可能会操作同样的数据,那怎么确保它们的操作顺序呢?为此引入了锁的观念。 

为了理解锁,需要理解transaction。 一组被同时发起的操作会在一个transaction里,这些操作要么全部成功,要么全部失败。在transaction的基础上,进一步引入锁的概念。需要注意理解的是,在锁的基础上,怎么理解一组transaction互相影响后的结果。 

3.3.1 四种隔离界别的transaction

  1. 可重复读,默认级别, 一个transaction执行过程中看见的数据,综合它开启式看见的一样。未提交变更,其它事务不可见

  2. 读提交,未提交变更,其它事务不可见

  3. 读未提交。未提交变更,其它事务可以看见变更

  4. 串行,同一行记录,写有写锁,读又读锁,任何一个锁,必须在事务结束后才释放,并且会阻碍其它事务访问这行记录。 

可重复读和读提交的区别在哪里呢:假设两个事务A和B,A只有读操作,B有更新操作,更新1到2. 如果是可重复读,A从头至尾只读到1, 如果是读提交,A在B提交后,读到2. 

事务隔离实现是用MVCC(多版本控制)实现的,也就是每一个事务会对数据库加入一个版本数,数据库会记录每一行数据的版本历史,数据库会根据每个事务的版本数提供给其应得的数据。 

3.3.2 理解锁

在事务基础上,又有了锁这个概念,锁分为全局锁,表级锁,行锁。 

全局锁:数据库只可读,可以用于全库逻辑备份,但是不建议这样做,可重复读级别的事务就可以满足这个要求

表级锁:分为表锁和元表锁,又分为读锁和写锁。读锁不互斥,写锁排斥其它锁。 表锁需要显式使用,元数据锁自动加上。 

行锁:行锁在引擎层实现。在可重复读下,行锁的读是一致性读,但是行锁的更新是当前读(这一点很重要!) update, delete, insert会自动加入排他锁,select for update也会加入排他锁。select in share mode会加入共享锁,update/share mode也是当前读。 

幻读:一个transaction先后两个读,后一个读读到了新的行,叫做幻读。这是由于其它transaction insert and commit,这个transaction当前读读到了不该读的行数。 这叫幻读。 为了解决幻读,有间隙锁的概念。间隙锁在可重复读级别才有。 间隙锁和行锁够成next-key lock. Next-key lock会影响并发度。在实际操作中,可能会用读提交和binlog = row 格式?。 

间隙锁加锁规则: 

  1. 加锁基本单位是next-key lock(间隙锁加行锁)。 

  2. Select中访问到的对象才会加锁(覆盖索引时不会进入主键索引)

  3. 索引的等值查询,在给唯一索引加锁的时候,next-key lock退化为行锁

  4. 索引上的等值查询,向右遍历时且最后一个值不满足等值条件,next-key lock退化为间隙锁。 (其它query的等值update就可以执行)

  5. 一个bug: 唯一索引上的范围查询会访问到不满足条件的第一个值位置。

详细解释: https://time.geekbang.org/column/article/75659 要注意覆盖索引和主键索引被锁的区别

3.3 表结构改变

表结构改变会加元数据锁。 

3.4 主从架构

主从架构中用长链接连接master and slave,用binlog传递数据。binlog 传递数据的最好格式是row,而不是statement.因为statement可能会导致主从不一致(比如使用limit语句,可能会在主从数据库里产生不同效果)。

主备之间可能有延迟,一般binlog传输很快,所以延迟主要出现在备库relay log.那么可能有以下原因: 

1) 备库性能差

2) 备库压力大(运维查询)

3)大事务。 

目前备份数据库使用了多线程进行备份,遵循两个原则:1. 同一行的更新由同一个worker执行

2. 同一个事务由同一个workder执行。 

多线程备份几种策略

  1. 按表

  2. 按行

  3. 按库

  4. 按commit_id 

一主多从模式在切换主库的时候需要判断从库从哪里开始读,MYSQL的GTID完美解决了这个问题。GTID基本是每个transaction有一个全局唯一的ID, 通过比较新主库和从库的ID的差集,来判断从库从哪里开始读。

3.4数据库备份

在可重复的情况下,可以直接备份。 

4.实际操作中的优化手段。 

4.1 order by. 

全字段排序和rowid排序。全字段排序是把符合条件的数据全部取出来,进行排序。rowid排序,是只取需要排序的数据column,排好序,再会数据库按序取数据。 

如果 MySQL 实在是担心排序内存太小,会影响排序效率,才会采用 rowid 排序算法,这样排序过程中一次可以排序更多行,但是需要再回到原表去取数据。如果 MySQL 认为内存足够大,会优先选择全字段排序,把需要的字段都放到 sort_buffer 中,这样排序后就会直接从内存里面返回查询结果了,不用再回到原表去取数据。这也就体现了 MySQL 的一个设计思想:如果内存够,就要多利用内存,尽量减少磁盘访问。对于 InnoDB 表来说,rowid 排序会要求回表多造成磁盘读,因此不会被优先选择

尽量将业务逻辑写在业务代码中,让数据库只做“读写数据”的事情。

4.2 JOIN

第一个问题:能不能使用 join 语句?如果可以使用 Index Nested-Loop Join 算法,也就是说可以用上被驱动表上的索引,其实是没问题的;如果使用 Block Nested-Loop Join 算法,扫描行数就会过多。尤其是在大表上的 join 操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源。所以这种 join 尽量不要用。所以你在判断要不要使用 join 语句时,就是看 explain 结果里面,Extra 字段里面有没有出现“Block Nested Loop”字样。第二个问题是:如果要使用 join,应该选择大表做驱动表还是选择小表做驱动表?如果是 Index Nested-Loop Join 算法,应该选择小表做驱动表;如果是 Block Nested-Loop Join 算法:在 join_buffer_size 足够大的时候,是一样的;在 join_buffer_size 不够大的时候(这种情况更常见),应该选择小表做驱动表。所以,这个问题的结论就是,总是应该使用小表做驱动表。

Join优化:1. 对index nested loop join. MRR优化。将主键排序放在内存里, 然后一条条去查询,增加顺序读的可能性。 由此演化出BKA优化。MRR针对非join,BKA针对join. 

2. 对block nested loop join. 问题是: BNL 算法对系统的影响主要包括三个方面:可能会多次扫描被驱动表,占用磁盘 IO 资源;判断 join 条件需要执行 M*N 次对比(M、N 分别是两张表的行数),如果是大表就会占用非常多的 CPU 资源;可能会导致 Buffer Pool 的热数据被淘汰,影响内存命中率。

解决办法: 1. 在原表上加索引,2. 还是用有索引的临时表,

3. hash join需要在业务层实现。mysql不支持hash join. 

4.3 临时表

适用场景是分库分表,查询到的数据在临时表中做聚合

内存表,指的是使用 Memory 引擎的表,建表语法是 create table … engine=memory。这种表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在。除了这两个特性看上去比较“奇怪”外,从其他的特征上看,它就是一个正常的表。

临时表,可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表,写数据的时候是写到磁盘上的。当然,临时表也可以使用 Memory 引擎。

  1. 建表语法是 create temporary table …。

  2. 一个临时表只能被创建它的 session 访问,对其他线程不可见。所以,图中 session A 创建的临时表 t,对于 session B 就是不可见的。

  3. 临时表可以与普通表同名。

  4. session A 内有同名的临时表和普通表的时候,show create 语句,以及增删改查语句访问的是临时表。

  5. show tables 命令不显示临时表

创建临时表以后,要考虑是否需要drop temp_table manually.如果server自己会做,就不用,否则就用。 这涉及到主备一致的问题。 

有时候一些query会自动创建临时用表:

1. Union

2. Groupby 

相应的也有一些优化: 1. Groupby column加索引, 2. 直接排序。 

 

4.4脏页

由于change buffer的存在,磁盘数据和内存数据不一样时,磁盘数据为脏页。如果数据库崩了,那么内存数据就丢了。

数据变更一开始只写入磁盘的redo log,在必要的时候,数据库会根据redo log 更新磁盘数据。 必要的情景:

  1. Flush语句会迫使数据库更新磁盘数据。 

  2. Redo log快写满了。redo log逻辑结构是一个闭环的四个内存区

  3. 内存快满了,需要淘汰脏页。 

  4. 数据库不忙的时候

  5. 数据库要关闭的时候

脏页控制策略: 告诉InnoDB的IO能力,参数: innodb_io_capacity,设置成磁盘的IOPS。 引擎是按照一个比例乘以innodb_io_capaicty来刷磁盘。 

Mysql在刷脏页还有一个默认的策略,就是把邻居脏页页刷掉,在机械硬盘时代有意义,SSD可以关掉这个功能。innodb_flush_neighbors是这个参数。 

4.5函数操作

对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。

4.6 KILL process

Mysql可以kill两种东西:一个线程的query,和一个线程的connection. 

一个线程被终止以后,需要进入线程判断逻辑以后才能确定是否需要终止query/command,然后进而进行回收操作。如果一个线程在等待状态,那么kill connection是不会kill query的,需要线程开始进入inno db 才会判断出需要kill. 同时对于大事务/大表DDL的kill, 需要清除临时表,因此也比较慢。 

一个问题是kill table structure update query,怎么kill 

发布于: 2020 年 07 月 13 日 阅读数: 16
用户头像

`

关注

还未添加个人签名 2019.05.29 加入

还未添加个人简介

评论

发布
暂无评论
MySQL实战45讲总结