vlambda博客
学习文章列表

一. Mysql DBA职业规划

关系型数据库基础理论：MVCC/ACID/范式设计/索引设计

分布式数据库基础理论：2PC/CAP/base/paxos算法

• 运维DBA

• 负责数据库日常维护

• 监控数据库状态

• 搭建高可用集群

• 备份恢复

• 数据迁移

• 开发DBA

• 负责数据库设计

• 负责应用程序的建表

• 负责表的索引创建

• SQL审计

• SQL优化

• 内核DBA

• 负责数据库内核级开发

• 处理内核级的相关

• 跟踪最新数据库内核

二. Mysql体系结构

1. MySQL体系架构概述

（1） Connectors指的是不同语言中与SQL的交互。
（2）Management Serveices & Utilities：系统管理和控制工具，例如备份恢复、Mysql复制、集群等。 
（3）Connection Pool: 连接池：管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求 ,cobar ，mycat 等。

（4）SQL Interface: SQL接口：接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
（5）Parser: 解析器，SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本， 主要功能：将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的 ；如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。
（6）Optimizer: 查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。 
用一个例子就可以理解：select uid,name from user where gender = 1; 
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤 ;这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤 ;将这两个查询条件联接起来生成最终查询结果 .

（7） Cache和Buffer（高速缓存区）：查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。 
通过LRU算法将数据的冷端溢出，未来得及时刷新到磁盘的数据页，叫脏页。 
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 
（8）Engine:存储引擎。存储引擎是mysql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎） 
现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB 
5.6版本以前默认使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。5.6版本以后使用InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。存储引擎是基于表的，而不是基于数据库，是mysql数据库的核心.

2.  mysql 数据库与实例的关系

• 实例用来操作数据库文件

• 实例：由数据库后台进程/线程以及一个共享内存区组成 ，共享内存可以被运行的后台进程/线程所共享

• 数据库：物理操作系统文件或其他形式文件类型的集合

• 数据库和实例是1：1的关系。

3.Innodb架构

MyISAM存储引擎的特点

1）myisam 引擎不支持事务，表锁设计，支持全文索引，主要面向一些OLAP （ETL 类的应用）数据库应用。5.5.8版本之前的默认存储引擎。

2）myisam引擎的缓冲池只缓存索引文件，而不缓冲数据文件。

3）myisam引擎表有MYD（存放数据文件）和MYI（存放索引文件）组成。

4）可以使用myisampack工具压缩或者解压表，压缩后为只读。

NDB存储引擎的特点

1）NDB是一个集群存储引擎，类似于oracle的RAC集群。

2）NDB的特点是数据全部放在内存中。

3）劣势：JOIN操作在数据库层完成，而不是在存储引擎层完成的，有巨大的网络开销，此类查询速度很慢。

memory存储引擎

1）数据放在内存中，重启就消失，适于存放零时表

innodb存储引擎的特点

1）设计的目的是为了OLTP(在线事务处理)场景应用，特点为：行锁设计，支持外键。5.5.8版本后为默认引擎。mysql中第一个完整支持ACID事务的存储引擎。

2）innodb引擎将数据存储在一个表空间中。它也可以将innodb引擎的表存储在一个独立的ibd文件中。

3）innodb引擎通过多版本并发控制（MVCC）来获得高并发性能。提供了插入缓冲（insert buffer），二次写（double write），自适应哈希索引，预读（read ahead）。

4）对表中数据的存储采用聚集方式。按照主键顺序存储。若是没有显式的定义主键，innodb引擎会默认为每一行生成一个rowid，作为主键。

5）插入/更新 操作 。

6）存储限制64TB.

7） 存储消耗较大。

数据文件包括：

表结构定义文件; 慢查询日志（有定义时长）;错误日志; 通用日志（默认不开，影响性能，通常用于审计）; 数据文件; 数据库配置文件

实例

1.内存结构

• buffer pool（类似于oracle 的sga）  innodb_buffer_pool_size  通常大小为物理内存的60-80%。默认安装是128M.

• index page    #page 是内存的叫法，block 是磁盘的叫法，默认大小16KB，Btree保存。

• adaptive hash index  #自适应hash。按照效率：链表查找< 二分查找= Btree < hash 查找。

• insert buffer page   #缓存二级索引（非唯一），空间换时间

• undo page              #存放在ibdata中。

• lock info                  #行锁信息，当行锁达到1000w 的时候升级为表级锁。

• data driectionary    #数据字典，存放在ibdata中。

• additional memary pool   ：额外的内存池。

• redo log buffer   innodb_log_buffer_size默认大小为16M,  innodb_log_file_size默认48M.  LSN：logsequence nuber

• double wirte

2.线程结构(16个)

查看方法：ps -ef|grep 3306  拿到进程号，然后pstack 【进程号】， ps -T -p 10029

ps -eLf |grep mysql|grep -v grep

• monitor thread

• dict stat thread

• lock outtime thread

• error monitor thread

• buf dump thread   #做数据预热的。当数据库需要维护的时候，可以把热数据进行保存，再启动的时候可以直接加载。存放space_id ,page_namber 两个信息到ib_buffer_pool 文件中。需要开启两个参数。show variables like '%dump%' 可以搜索。

• master thread：负责将缓冲池中的数据异步刷新到磁盘。保证数据的一致性。具有最高线程优先级。

• page clear thread：用于脏页刷新

• purge thread group  #清理undo，避免undo 过大。回收已经使用并分配的undo页。默认4个线程，innodb_purge_threads

UNDO和REDO的关系

• insert buffer thread

• read thread group

• write thread group

• redo log thread

• recv write thread   #恢复线程

• rollback clear thread  #回滚线程。

• full text search thread

• user thread

• 数据库

• 系统表空间

  insert buffer segment

  double write segment

  rollback write segment

  index segment

  undo segment

  driectionary segment

• 用户表空间

  insert buffer bigmap page

  leaf page segment

  no leaf page segment

• redo log   #innodb 引擎特有的，区别于server 层的binlog日志。

• archive redo log

4. 客户机/服务器模型

1）一个连接进程和mysql实例进行通信。

2）进程通信方式：

• 管道：命名管道 需要在my.cnf  文件中开启：--enable-named-pipe  

• 命名字，tcp/IP套接字（最常用的方式），mysql -h 192.168.145.137 -uzabbix -p

• unix域套接字。show variables like 'socket'   ---> /var/lib/mysql/mysql.sock   ---> mysql -uzabbix -S /var/lib/mysql/mysql.sock

服务器进程

5. sql语句处理过程

#查询缓冲大小，默认是关闭的。show variables like '%query_cache_size%';+------------------+---------+| Variable_name | Value |+------------------+---------+| query_cache_size | 1048576 |+------------------+---------+1 row in set (0.00 sec)

6. 内存结构及参数

服务器以三种不同的类别分配内存：

连接/会话 （类似于oracle 的pga）

• 排序缓冲区

• 读取缓冲区

• 临时表

binlog_cache_sizeread_buffer_sizeread_rnd_buffer_sizejoin_buffer_sizesort_buffer_sizetmp_table_sizethread_cache_sizebulk_insert_buffer_sizenet_buffer_lengththread_stack

服务器/共享 （类似于oracle 的sga）

• 查询高速缓冲

• 线程高速缓冲

query_cachetable_cahcetable_definition_cache

存储引擎/共享

• 日志缓冲区

• 缓冲池

innodb_buffer_pool_sizeinnodb_additional_mem_pool_sizeinnodb_log_buffer_size

7. MySQL 如何使用磁盘空间

8.MySQL物理存储结构

frm：表结构定义文件。

myd：表结构数据文件

myi: 表的索引文件

9. MySQL逻辑存储结构

三.揭密MySQL databock and binlog的格式

1. InnoDB 物理存储结构

innnodb中数是按照B树索引的方式存放的，B树分为叶子节点和非叶子节点。

二级索引最终是指向主键索引的。

rowid ：六个字节，默认索引。

2.InnoDB 逻辑存储结构

逻辑存储结构由大到小依次为：

• 表空间（独立，共享表空间）

  show variables like '%innodb_file_per_table%'   =on   #表示每个表一个表空间，方便迁移，管理。

  共享的表空间放在ibdata。

  表空间查询：select * from information_schema.INNODB_SYS_TABLES;

• 段（segment），可以理解为其中的表（table）

• 区（extent）默认由64个连续的page构成，为分配空间的最小单位。大小为：16K*64=1M。

• 页(page) 每次读入内存的单位，可以结合业务设置为2k，4k，8k。

• 行（row）

3. InnoDB 文件结构

第一个块：文件头部，记录这个文件有多少个块（磁盘的概念）组成。

第二个块：insert buffer

第三个块：

4. InnoDB Page结构

5. InnoDB 行记录结构

# 查看某个表的行记录格式：show table status like '%test%' \G;

6. InnoDB 数据块解析

 #从第三个块开始存储数据。hexdump -C -v test.ibd|head -n 4096|tail -n 1024[mysql@localhost wgw]$ hexdump -C -v test.ibd|head -n 4096|tail -1024|grep wg*0000c090 02 43 80 00 00 01 77 67 71 03 00 00 00 18 00 21 |.C....wgq......!|0000c0b0 1a 01 10 80 00 00 01 77 67 77 03 00 00 00 20 00 |.......wgw.... .|0000c0d0 01 1b 01 10 80 00 00 03 77 67 79 03 00 00 00 28 |........wgy....(|0000c0f0 00 01 1e 01 10 80 00 00 03 77 67 63 00 00 00 00 |.........wgc....|[mysql@localhost wgw]$

7. Binlog 日志生成的流程

三个会话做update操作，就会产生三个线程，当各自用户commit的时候就会写入binlog cache，然后各自的线程发起write操作，分别写入各自的cache中，此时其他用户还是看不到写入的最新结果，接着flush到操作系统的缓存中，此时所有的用户都可以看见了，但是此时机器宕机将不会保持，还得通过sync进行刷入磁盘持久化。

8. Binlog 日志解析

show binlog events in 'binlog.000001';#查看binlog日志内容。mysqlbinlog -v -v binlog.000011# 记录可以用作流量回放。

四.MySQL锁机制与事务机制实现

1.事务概述

什么是事务

       事务定义了一个服务操作的序列，由服务器保证这些操作序列在多个客户端并发访问和服务器出现故障时的原子性。

MVCC:多版本并发控制。

并发存在的问题：

• 脏读 （dirty read） 在数据库中不允许发生。在未提交的情况下，多次查询得到不同的结果。例如：存款，在未点击确认的时候，界面已经可以看到你存入的数额。

• 不可重复读（unrepeable read）：某条记录被其他回话修改，在本次事务中可以查到别人的修改。例如你查账，同时公司财务给你打了本月工资（已提交），你在这时会发现账户增加了数额。oracle中会发生，但是mysql的默认隔离级别下，不会发现账户增加数额。

• 幻读：两次查询到的记录数量不一致，第一次为1条记录，过一会儿变成了5条记录。

当锁发生时需要从如下几个方面去考虑

1. 查询条件是否为主键，如上面实例id。

2. 当前数据库的隔离级别是什么：

   show variables like '%isol%';  默认级别：REPEATABLE-READ

   set tx_isolation='READ-UNCOMMITTED';   #设置为读未提交。该模式下将会出现脏读。

3. 查询条件是否有索引，并且是唯一索引。

4. 查看sql的执行计划。

事务的属性

A （atomicity：原子性） redo and undo  要么提交，要么回滚。

C (consistency：一致性) undo  数据库的完整性没有没破坏。

I (isolation：隔离性)   lock  一个事务的修改，其他事务不可见。

D (durable：持久性)  redo  数据保存到磁盘中。

   隔离级别：4种。

• 读未提交（read uncommitted）级别很低，并发很高，但是不安全，生产环境不允许。

• 读已提交 （read commited）：oracle，db2，ms，pg的默认隔离级别。并发第二高。

• 可重复读  （repeatable read）：mysql的默认隔离级别，可以避免脏读和不可重复读。生产环境可以设置为read commited级别，来提高并发能力。同时大部分应用也不会因此而产生现实的问题。

• 串行 （serializable）

事务编程的不好习惯

2. 锁的概念

• 锁用于在多个事务访问同一对象时，按照先后次序给排序

• innodb 和oracle都是行级锁。myisam是表锁。微软行级锁，某些时候会锁升级。

• 行锁的分类（按照范围分类）

• 行记录锁

• 间隙锁：索引记录间隙上的锁。例如在第一条索引记录之前，最后一条索引记录之后加的间隙锁。间隙锁只有的不可重复读隔离级别下才生效，解决幻读的问题。

• 下一键锁：索引记录锁及索引记录之间的间隙锁二者的组合锁。

• lock于latch的区别

• 事务/线程

• 数据库对象/内存结构对象

• 长/短

• 表锁行锁/ 互斥

• 死锁：有/无

• innodb中的锁模式

IS,IX  表级锁；s，x 行级锁。例如在进行update的时候会在表上加意向排它锁IX, 然后在记录行上加行级排他锁X. 如果此时其他回话来删除这个表，就会发现表粒度上有意向排他锁，所以这个表就不能删除，返回报错。就不会深入到行级锁去探测。提高并发性。

行锁分析

create table t2(id int,name varchar(10),primary key (id),key(name));insert into t2 values (1,'a'),(3,'a'),(5,'c'),(7,'g'),(10,'i');select * from t2;

gap锁只有在REPEATABLE-READ级别下才有作用，解决幻读问题，上图有6个gap锁，间隙之间不允许插入数据。针对普通索引。

测试行锁

• 测试record lock

  测试1:  行级锁发上等待。因为行级排他锁和行级共享锁形成冲突。

  select * from information_schema.INNODB_LOCKS;

  select * from information_schema.INNODB_LOCK_WAITS;

测试2:  会发生锁等待，两个排它锁不能共存。name 为辅助索引。锁最终还是备追溯到主键上。因为B数结构。

测试 gap lock

测试1：

测试2：

测试3：

这条sql会加哪些锁？

上表的隔离级别为不可重复读，最终这条语句执行完会在普通索引上加3把x锁，4把gap锁，最终传到到主键上加3把x锁。

MDL锁（meta data lock）

由于DDL操作，无法保护事务，因此5.5 版本以后引入MDL锁，用于保证DML和DDL操作之间的一致性。在mysqldump（备份）的时候，不能做DDL操作，会提示Waiting for table metadata lock

解决MDL锁的方法

• 解决DDL高并发，尽量避免使用。

• 上线DB不要轻易做alter table

• 干掉ddl回话:  show processlist; kill id;

死锁原理与分析

死锁产生的原因：

• 产生回路：两个或者两个以上的事务在执行过程中，分别持有一把锁，然后加另一把锁(AB--BA），产生死锁。例如两个小孩分别有自己的玩具，但是都不贡献自己的玩具，还想要对方的玩具，使得游戏无法继续。

• 加锁顺序不一致:两个或者两个以上的事务在同时执行过程中，因为争夺资源而造成等待产生死锁。小孩抢同一个玩具，互不相让。

• 减少死锁的方法

• 自动死锁检测，优先回滚小事务

• 设置超时 innodb_lock_wait_timeout默认为10秒。

• 尽快提交事务，小事务不容易死锁

• 降低事务隔离级别，变为rc级别。降低死锁发生的概率

• 涉及多表，多行的的事务，每个事务顺序保持一致。降低死锁概率。固话为存储过程或者函数。

• 通过索引优化sql执行效率，减少扫描范围

查找死锁的方法

show engine innodb status\G;show variables like '%dead%';set innodb_print_all_deadlocks=on   #保留死锁的信息到error日志中。select * from information_schema.innodb_trx limit 5;select * from information_schema.innodb_locks ;select * from information_schema.innodb_lock_waits ;# 查询锁信息SELECTr.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_queryFROM information_schema.innodb_lock_waits wINNER JOIN information_schema.innodb_trx bON b.trx_id= w.blocking_trx_idINNER JOIN information_schema.innodb_trx rON r.trx_id= w.requesting_trx_id;