面试-MySQL总结

三范式

三范式定义(范式和反范式)

1NF:每个数据项都是最小单元,不可分割,确定行列之后只能对应一个数据。

2NF:每一个非主属性完全依赖于候选码(属性组的值能唯一的标识一个元组,但是其子集不可以)。

3NF:每一个非主属性既不传递依赖于,也不部分依赖于(主码=候选码为多个市,从中选出一个作为主码)。

BCNF主属性(候选码中的某一个属性)内部也不能部分或传递依赖于码。

4NF :没有多值依赖。

MySQL的复制原理以及流程

基本原理流程,3个线程以及之间的关联;

  1. 主:binlog线程——记录下所有改变了数据库数据的语句,放进master上的binlog中;
  2. 从:io线程——在使用start slave 之后,负责从master上拉取 binlog
    内容,放进 自己的relay log中;
  3. 从:sql执行线程——执行relay log中的语句;

MySQL技术内幕:InnoDB存储引擎(第2版)

数据类型

MySQL数据类型-菜鸟教程 

MYSQL中数据类型介绍

整数: int(m)里的m是表示数据显示宽度,浮点数,定点数。

字符串:char(n)4.0 n 代表字节,5.0 n 代表字符 (UTF-8=3zj,GBK=2zj)

 char 固定的字符数,空格补上;检索速度快。

 varchar 字符数+1个字节(n<=255)或2个字节(n>255)

 text 字符数+2个字节;不能有默认值;索引要指定前多少个字符;文本方式存储

 blob 二进制方式存储

mysql中myisam与innodb的区别

姜承尧

存储引擎

各种存储引擎的区别与联系   
 
(存储数据技术和策略,存储机制、索引技巧、锁定水平等)

数据库存储引擎 
   show table status 显示表的相关信息

InnoDB与MyISAM的比较(从5.7开始innodb存储引擎成为默认的存储引擎。)

 锁机制:行级锁,表级锁

 事务操作:事务安全,不支持

InnoDB
(1)可靠性要求比较高,要求事务;(2)表更新和查询都相当的频繁,并且行锁定的机会比较大的情况。

 MySQL4.1之后每个表的数据和索引存储在一个文件里。

 InnoDB
采用了MVCC来支持高并发,并且实现了四个标准的隔离级别。其默认级别是REPEATABLE
READ(可重复读) ,行级锁。

 自动灾难恢复。与其它存储引擎不同,InnoDB表能够自动从灾难中恢复。

 外键约束。MySQL支持外键的存储引擎只有InnoDB。

 支持自动增加列AUTO_INCREMENT属性。

MyIsam  (1)做很多count
的计算;(2)插入不频繁,查询非常频繁;(3)没有事务。

 表存储在两个文件中,数据文件(MYD)和索引文件(MYI)

 表级锁,读=共享锁,写=排它锁。

 适合选择密集型的表,插入密集型的表。

5点不同

  • InnoDB支持事务,MyISAM不支持,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务;

  • InnoDB支持外键,而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败;

  • InnoDB是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。而MyISAM是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

  • InnoDB支持MVCC, 而MyISAM不支持

  • InnoDB不保存表的具体行数,执行select count(*) from
    table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;
    但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含
    where条件时,两种表的操作是一样的。

  • Innodb不支持全文索引,而MyISAM支持全文索引,查询效率上MyISAM要高;

  • 对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。

  • DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除

第1章 MySQL体系结构和存储引擎

数据库ACID

数据库的ACID

数据库事务介绍

原子性(Atomicity)一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作。

一致性(Consistency)数据库总是从一个一致性的状态转换到另一个一致性的状态。

隔离性(Isolation)一个事务所做的修改在最终提交以前,对其他事务是不可见的。

持久性(Durability)一旦事务提交,则其所做的修改不会永久保存到数据库。

4 种隔离级别

MVVC的简单介绍

READ
UNCOMMITTED(未提交读)脏读
:事务中的修改,即使没有提交,对其他事务也都是可见的。

READ
COMMITTED(提交读)不可重复读
:事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。

REPEATABLE
READ(可重复读):幻读:
一个事务按相同的查询条件读取以前检索过的数据,其他事务插入了满足其查询条件的新数据。产生幻行。

SERIALIZABLE(可串行化) 强制事务串行执行

MVVC是个行级锁的变种,它在普通读情况下避免了加锁操作,自特定情况下加锁

innodb引擎的4大特性

  • 插入缓冲(insert buffer)
    插入主键聚集索引,是顺序的,不需要磁盘的随机读取;但是这也导致同一个表中的非聚集索引不是顺序的,因为B+树的特性决定了非聚集索引插入的离散型。
    插入缓存就是为提高非聚集索引的插入和更新操作的性能而做的优化设计,其原理将插入数据先放到内存就直接返回上层,上层看来已经插入成功,其实插入数据还在内存中,内部会触发内存的索引数据与物理的索引数据进行合并操作,合并时将多个插入合并到一个操作(正好一个索引页),这样大大提高了对非聚集索引插入的性能。

  • 二次写(double write)
    为了提升数据页的可靠性。
    写数据页的时候宕机怎么办?
    重做日志,但是如果物理页已经损坏了怎么版?
    用doubleWrite:发现物理页损坏了,则找到其前面的一个副本,用副本来还原当前页,再重做日志。

  • 自适应哈希索引(ahi)
    原先的索引是B+树结构,当查询频繁,建立哈希可以提高效率,则自动构建哈希索引,提高速度。

  • 异步IO(Async IO)
    同时发起多个IO请求(索引页的扫描),可以将多个IO请求合并为一个IO操作,同时将每个IO请求的结果进行Merge。

  • 刷新邻接页
    刷新一个脏页的同事检查所在区的其他页是否需要一起刷新。

>>
在上述例子中使用了mysqld_safe命令来启动数据库,当然启动MySQL实例的方法还有很多,在各种平台下的方式可能又会有所不同。

Mysql死锁问题

Mysql悲观锁总结和实践

Mysql乐观锁总结和实践

SELECT … LOCK IN SHARE MODE SELECT … FOR UPDATE:(LOCK IN SHARE
MODE 在有一方事务要Update 同一个表单时很容易造成死锁)

乐观锁:取锁失败,产生回溯时影响效率。

 取数据时认为其他线程不会对数据进行修改。

 更新时判断是否对数据进行修改,版本号机制或CAS操作。

悲观锁:每次取数据都会加锁。

innodb_lock_wait_timeout 等待锁超时回滚事务:  【超时法】

直观方法是在两个事务相互等待时,当一个等待时间超过设置的某一阀值时,对其中一个事务进行回滚,另一个事务就能继续执行。在innodb中,参数innodb_lock_wait_timeout用来设置超时时间。

wait-for graph算法来主动进行死锁检测:  【等待图法】

innodb还提供了wait-for
graph算法来主动进行死锁检测,每当加锁请求无法立即满足需要并进入等待时,wait-for
graph算法都会被触发。

2者selectcount(*)哪个更快,为什么

myisam更快,因为myisam内部维护了一个计数器,可以直接调取。

>>
当启动实例时,MySQL数据库会去读取配置文件,根据配置文件的参数来启动数据库实例。这与Oracle的参数文件(spfile)相似,不同的是,Oracle中如果没有参数文件,在启动实例时会提示找不到该参数文件,数据库启动失败。而在MySQL数据库中,可以没有配置文件,在这种情况下,MySQL会按照编译时的默认参数设置启动实例

索引

索引(存储引擎 快速找到记录的一种数据结构,索引的基本功能)

什么是B-Tree

MySQL索引背后的数据结构及算法原理

MySQL性能优化-慢查询分析、优化索引和配置

MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

(1)、varchar与char的区别
char是一种固定长度的类型,varchar则是一种可变长度的类型

(2)、varchar(50)中50的涵义
最多存放50个字符,varchar(50)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order
by col采用fixed_length计算col长度(memory引擎也一样)

(3)、int(20)中20的涵义
是指显示字符的长度
但要加参数的,最大为255,比如它是记录行数的id,插入10笔资料,它就显示00000000001
~~~00000000010,当字符的位数超过11,它也只显示11位,如果你没有加那个让它未满11位就前面加0的参数,它不会在前面加0
20表示最大显示宽度为20,但仍占4字节存储,存储范围不变;

(4)、mysql为什么这么设计
对大多数应用没有意义,只是规定一些工具用来显示字符的个数;int(1)和int(20)存储和计算均一样;

>>
从概念上来说,数据库是文件的集合,是依照某种数据模型组织起来并存放于二级存储器中的数据集合;数据库实例是程序,是位于用户与操作系统之间的一层数据管理软件,用户对数据库数据的任何操作,包括数据库定义、数据查询、数据维护、数据库运行控制等都是在数据库实例下进行的,应用程序只有通过数据库实例才能和数据库打交道。

索引类型:

 B-Tree索引 索引列的顺序影响者是否使用索引。

 哈希索引

 无法用于排序。

 只支持全部匹配。

 只支持等值比较。

 有很多哈希冲突时,效率不太高。

 空间数据索引(R-Tree)无需前缀查询,从所有维度查询数据。

 全文检索 查找文本中的关键词,类似于搜索引擎做的事情。

innodb的事务与日志的实现方式

(1)、有多少种日志;

  • 错误日志:记录出错信息,也记录一些警告信息或者正确的信息。
  • 查询日志:记录所有对数据库请求的信息,不论这些请求是否得到了正确的执行。
  • 慢查询日志:设置一个阈值,将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
  • 二进制日志binlog:记录对数据库执行更改的所有操作。
  • 中继日志relay log:
  • 事务日志 redo log / undo log:

(2)、事物的4种隔离级别

  • 读未提交(RU)
  • 读已提交(RC)
  • 可重复读(RR)
  • 串行

(3)、事务是如何通过日志来实现的,说得越深入越好。
事务日志是通过redo和innodb的存储引擎日志缓冲(Innodb log
buffer)来实现的,当开始一个事务的时候,会记录该事务的lsn(log sequence
number)号; 当事务执行时,会往InnoDB存储引擎的日志
的日志缓存里面插入事务日志;当事务提交时,必须将存储引擎的日志缓冲写入磁盘(通过innodb_flush_log_at_trx_commit来控制),也就是写数据前,需要先写日志。这种方式称为“预写日志方式”

>> 需要特别注意的是,存储引擎是基于表的,而不是数据库。

 具体类型介绍:

单列索引:不允许为空

 普通索引 不允许有空值

 唯一索引

 主键索引 在 InnoDB 引擎中很重要

组合引擎:多个字段上创建的索引,复合索引时遵循最左前缀原则。

 查询中某个列有范围查询,则其右边的所有列都无法使用查询

全文索引:

空间索引:

参考:细说mysql索引、我的MYSQL学习心得(九)
索引

MySQL binlog的几种日志录入格式以及区别

(1)、binlog的日志格式的种类和分别
(2)、适用场景;
(3)、结合第一个问题,每一种日志格式在复制中的优劣。

  • Statement:
    每一条会修改数据的sql都会记录在binlog中,过程导向(没有关注结果)。
    优点:记录sql语句上下文相关信息
    缺点:存储过程,或function,以及trigger的调用和触发无法被正确复制
  • Row:
    不记录sql语句上下文相关信息,仅保存哪条记录被修改成什么样子,结果导向(不关注过程)。
    优点:仅需要记录那一条记录被修改成什么了。所以会非常清楚的记录下每一行数据修改的细节。
    缺点:产生大量的日志内容。
  • Mixedlevel:
    是以上两种level的混合使用,一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则
    采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式

>>
关于NDB存储引擎,有一个问题值得注意,那就是NDB存储引擎的连接操作(JOIN)是在MySQL数据库层完成的,而不是在存储引擎层完成的

MySQL索引详解 (一般使用磁盘I/O次数评价索引结构的优劣。)

 磁盘存取原理

 局部性原理与磁盘预读

M 阶 B-Tree

 图片 1

 根节点至少有2个子树。

 每个非叶子节点由n-1个key和n个指针组成。

 分支节点至少拥有m/2颗子树,最多拥有m个子树。(除根节点和叶子结点外)

 所有叶节点具有相同的深度,等于树高 h。

 每个叶子节点最少包含一个key和两个指针,最多包含2d-1个key和2d个指针。

B+ Tree

 内节点不存储data,只存储key。

 叶子节点不存储指针。

MySQL 索引实现

 MyISAM 索引文件和数据文件是分离,非聚集索引。

 InnoDB 叶节点包含了完整的数据记录,聚集索引。根据主键聚集。

MySQL数据库cpu飙升到500%的话他怎么处理?

(1)、没有经验的,可以不问;
(2)、有经验的,问他们的处理思路。

  • 找出占用的线程杀掉,分析日志,找问题,解决
  • mysql> show processlist; 找出占用cpu的线程
  • 常见问题 :
    1. 睡眠连接过多,严重消耗mysql服务器资源(主要是cpu,
      内存),并可能导致mysql崩溃。
      解决办法 :
      mysql的配置my.ini文件中wait_timeout,
      即可设置睡眠连接超时秒数,如果某个连接超时,会被mysql自然终止。
      mysql> set global wait_timeout=20;
    2. 增加 tmp_table_size 值
    3. SQL语句没有建立索引
    4. 函数计算的,放到应用层进行

>>
相信在任何一本关于数据库原理的书中,可能都会提到数据库与传统文件系统的最大区别在于数据库是支持事务的

EXPLAIN 字段介绍

 possible_keys:显示可能应用在这张表中的索引。

 key:实际使用的索引。

 key_len:使用的索引的长度,越短越好。

 ref:显示索引的哪一列被使用了。

 rows:MySQL认为必须检索的用来返回请求数据的行数。

 type:使用了何种类型。从最好到最差的连接类型为system、const(常量)、eq_ref、ref、range、index(索引全表扫描)和ALL(全表扫描)。

sql优化

  • 使用explain,分析优化, 各item的意义;
    select_type
    表示查询中每个select子句的类型
    type
    表示MySQL在表中找到所需行的方式,又称“访问类型”
    possible_keys
    指出MySQL能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
    key
    显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
    key_len
    表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
    ref
    表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
    Extra
    包含不适合在其他列中显示但十分重要的额外信息

  • profile的意义以及使用场景
    查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中
    Systemlock, Table lock 花多少时间等等

>> MySQL提供了一个非常好的用来演示MySQL各项功能的示例数据库,如SQL
Server提供的AdventureWorks示例数据库和Oracle提供的示例数据库。据我所知,知道MySQL示例数据库的人很少,可能是因为这个示例数据库没有在安装的时候提示用户是否安装(如Oracle和SQL
Server)以及这个示例数据库的下载竟然和文档放在一起

视图 

MySQL数据库视图

MySQL –
视图算法

视图最简单的实现方法是把select语句的结果存放到临时表中。具有性能问题,优化器很难优化临时表上的查询。

 合并算法 :select语句与外部查询视图的select语句进行合并,然后执行。

 临时表算法 :先执行视图的select语句,后执行外部查询的语句。

视图在某些情况下可以提升性能,并和其他提升性能的方式叠加使用。

 视图不可以跨表进行修改数据,

 创建有条件限制的视图时,加上“WITH CHECK OPTION”命令。

备份计划,mysqldump以及xtranbackup的实现原理

(1)、备份计划;
利用空闲间隔
长期全量备份
每天增量备份
删除1个月前的备份数据

(2)、备份恢复时间;
(3)、xtrabackup实现原理
在InnoDB内部会维护一个redo日志文件,我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时,InnoDB会检查数据文件和事务日志,并执行两个步骤:它应用(前滚)已经提交的事务日志到数据文件,并将修改过但没有提交的数据进行回滚操作。

>>
在Linux和UNIX环境下,还可以使用UNIX域套接字。UNIX域套接字其实不是一个网络协议,所以只能在MySQL客户端和数据库实例在一台服务器上的情况下使用。用户可以在配置文件中指定套接字文件的路径,如–socket=/tmp/mysql.sock。当数据库实例启动后,用户可以通过下列命令来进行UNIX域套接字文件的查找:

触发器

 触发器的触发事件 , 可以是 INSERT 、UPDATE 或者 DELETE 。

 触发时间 , 可以是 BEFORE 或者 AFTER。

 同一个表相同触发时间的相同触发事件 , 只能定义一个触发器,只支持基于行触发。

 触发器的原子性,InnoDB支持事务,MyISAM不支持。

500台db,在最快时间之内重启

采用docker swarm
或者自动化配置和部署工具,如Puppet、Chef、Ansible和SaltStack

第2章 InnoDB存储引擎

事件

   
 
类似于Linux的定时任务,某个时间或者每隔一段时间执行一段SQL代码。

innodb的读写参数优化

(1)、读取参数
global buffer pool以及 local buffer;

(2)、写入参数;
innodb_flush_log_at_trx_commit
innodb_buffer_pool_size

(3)、与IO相关的参数;
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0

(4)、缓存参数以及缓存的适用场景。
query cache/query_cache_type

>>
从MySQL数据库的官方手册可得知,著名的Internet新闻站点Slashdot.org运行在InnoDB上。Mytrix、Inc.在InnoDB上存储超过1
TB的数据,还有一些其他站点在InnoDB上处理插入/更新操作的速度平均为800次/秒。这些都证明了InnoDB是一个高性能、高可用、高可扩展的存储引擎。

备份

数据备份(深入浅出Mysql 27章 备份与恢复)

 全备份与增量备份的比较。

 确保 MySQL 打开 log-bin 选项,有了 BINLOG,MySQL
才可以在必要的时候做完 整恢复,或基于时间点的恢复,或基于位置的恢复。

逻辑备份(将数据库中的数据备份为一个文本文件,备份的文件可以被查 看和编辑。)

物理备份

 冷备份:cp移动数据文件的方法。

 恢复:移动数据文件,使用 mysqlbinlog 工具恢复自备份以来的所有
BINLOG。

 热备份:(将要备份的表加读锁,然后再 cp 数据文件到备份目录。)

 MyISAM:mysqlhotcopy工具。

 ibbackup 是 Innobase 公司(www.innodb.com)的一个热备份工具。

你是如何监控你们的数据库的?你们的慢日志都是怎么查询的?

监控的工具有很多,例如zabbix,lepus,我这里用的是lepus

>>
后台线程的主要作用是负责刷新内存池中的数据,保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件,同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态。

恢复

     完全恢复

 将备份作为输入执行。

 将备份后执行的日志进行重做。

    
不完全恢复(跳过误操作语句,再恢复后
 面执行的语句,完成我们的恢复。)

 基于时间点的操作。跳过故障发生时间。

 基于位置的恢复。找到出错语句的位置号,并跳过位置区间。

 

你是否做过主从一致性校验,如果有,怎么做的,如果没有,你打算怎么做?

主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等

>> 在InnoDB存储引擎中大量使用了AIO(Async
IO)来处理写IO请求,这样可以极大提高数据库的性能。而IO
Thread的工作主要是负责这些IO请求的回调(call back)处理

日志

错误日志:记录了当 mysqld 启动和停止时,以及服务器在
运行过程中发生任何严重错误时的相关信息。

二进制文件:记录了所有的 DDL(数据定义语言)语句和
DML(数据操纵语言)
语句,不包括数据查询语句。语句以“事件”的形式保存,它描述了数据的更改过程。(定期删除日志,默认关闭)。

查询日志:记录了客户端的所有语句,格式为纯文本格式,可以直接进行读取。(log
日志中记录了所有数据库的操作,对于访问频繁的系统,此日志对系统性能的影响较大,建议关闭,默认关闭)。

慢查询日志:慢查询日志记录了包含所有执行时间超过参数long_query_time(单位:秒)所设置值的
SQL
语句的日志。(纯文本格式)MySQL日志文件之错误日志和慢查询日志详解。

日志文件小结:

 系统故障时,建议首先查看错误日志,以帮助用户迅速定位故障原因。

 记录数据的变更、数据的备份、数据的复制等操作时,打开二进制日志。默认不记录此日志,建议通过–log-bin
选项将此日志打开。

 如果希望记录数据库发生的任何操作,包括 SELECT,则需要用–log
将查询日志打开,
此日志默认关闭,一般情况下建议不要打开此日志,以免影响系统整体性能。

 查看系统的性能问题, 希望找到有性能问题的SQL语 句,需要 用
–log-slow-queries 打开慢查询日志。对于大量的慢查询日志,建议使用
mysqldumpslow 工具 来进行汇总查看。

表中有大字段X(例如:text类型),且字段X不会经常更新,以读为为主,请问

(1)、您是选择拆成子表,还是继续放一起;
(2)、写出您这样选择的理由。
拆带来的问题:连接消耗 + 存储拆分空间;不拆可能带来的问题:查询性能;
如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区)
顺序IO,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗
如果能容忍不拆分带来的查询性能损失的话:上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择

18、MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的?为什么是这样子的?
InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,
如果 id 不是索引键那么InnoDB将完成表锁,,并发将无从谈起

>> 可以通过命令SHOW ENGINE INNODB STATUS来观察InnoDB中的IO
Thread:

开放性问题:

一个6亿的表a,一个3亿的表b,通过外间tid关联,你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录
1、如果A表TID是自增长,并且是连续的,B表的ID为索引
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。
select * from b , (select tid from a limit 50000,200) a where b.id = a
.tid;

>>
具体来看,缓冲池中缓存的数据页类型有:索引页、数据页、undo页、插入缓冲(insert
buffer)、自适应哈希索引(adaptive hash
index)、InnoDB存储的锁信息(lock info)、数据字典信息(data
dictionary)等

>> 从InnoDB
1.0.x版本开始,允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争,增加数据库的并发处理能力。可以通过参数innodb_buffer_pool_instances来进行配置,该值默认为1。

>> 从MySQL
5.6版本开始,还可以通过information_schema架构下的表INNODB_BUFFER_POOL_STATS来观察缓冲的状态

>>
在InnoDB存储引擎中,缓冲池中页的大小默认为16KB,同样使用LRU算法对缓冲池进行管理

>>
。稍有不同的是InnoDB存储引擎对传统的LRU算法做了一些优化。在InnoDB的存储引擎中,LRU列表中还加入了midpoint位置。新读取到的页,虽然是最新访问的页,但并不是直接放入到LRU列表的首部,而是放入到LRU列表的midpoint位置。这个算法在InnoDB存储引擎下称为midpoint
insertion strategy。

>>
那为什么不采用朴素的LRU算法,直接将读取的页放入到LRU列表的首部呢?这是因为若直接将读取到的页放入到LRU的首部,那么某些SQL操作可能会使缓冲池中的页被刷新出,从而影响缓冲池的效率。

>>
常见的这类操作为索引或数据的扫描操作。这类操作需要访问表中的许多页,甚至是全部的页,而这些页通常来说又仅在这次查询操作中需要,并不是活跃的热点数据。如果页被放入LRU列表的首部,那么非常可能将所需要的热点数据页从LRU列表中移除,而在下一次需要读取该页时,InnoDB存储引擎需要再次访问磁盘。

>> Buffer pool hit
rate,表示缓冲池的命中率,这个例子中为100%,说明缓冲池运行状态非常良好。通常该值不应该小于95%。若发生Buffer
pool hit
rate的值小于95%这种情况,用户需要观察是否是由于全表扫描引起的LRU列表被污染的问题。

>> 执行命令SHOW ENGINE INNODB
STATUS显示的不是当前的状态,而是过去某个时间范围内InnoDB存储引擎的状态。从上面的例子可以发现,Per
second averages calculated from the last 24
seconds代表的信息为过去24秒内的数据库状态。

>> 在LRU列表中的页被修改后,称该页为脏页(dirty
page),即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘,而Flush列表中的页即为脏页列表。需要注意的是,脏页既存在于LRU列表中,也存在于Flush列表中

>>
重做日志缓冲一般不需要设置得很大,因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件,因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可

>> 当前3TB的MySQL数据库已并不少见,但是3
TB的内存却非常少见。目前Oracle Exadata旗舰数据库一体机也就只有2
TB的内存。

>> 因此Checkpoint(检查点)技术的目的是解决以下几个问题:□
缩短数据库的恢复时间;□ 缓冲池不够用时,将脏页刷新到磁盘;□
重做日志不可用时,刷新脏页。

>> 对于InnoDB存储引擎而言,其是通过LSN(Log Sequence
Number)来标记版本的。而LSN是8字节的数字,其单位是字节。每个页有LSN,重做日志中也有LSN,Checkpoint也有LSN。可以通过命令SHOW
ENGINE INNODB STATUS来观察:

>> InnoDB存储引擎的关键特性包括:□ 插入缓冲(Insert Buffer)□
两次写(Double Write)□ 自适应哈希索引(Adaptive Hash Index)□
异步IO(Async IO)□ 刷新邻接页(Flush Neighbor Page)

>> InnoDB存储引擎开创性地设计了Insert
Buffer,对于非聚集索引的插入或更新操作,不是每一次直接插入到索引页中,而是先判断插入的非聚集索引页是否在缓冲池中,若在,则直接插入;若不在,则先放入到一个Insert
Buffer对象中,好似欺骗。数据库这个非聚集的索引已经插到叶子节点,而实际并没有,只是存放在另一个位置。

>> 然后再以一定的频率和情况进行Insert
Buffer和辅助索引页子节点的merge(合并)操作,这时通常能将多个插入合并到一个操作中(因为在一个索引页中),这就大大提高了对于非聚集索引插入的性能。

>>
辅助索引不能是唯一的,因为在插入缓冲时,数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生,从而导致Insert
Buffer失去了意

>> 正如前面所说的,目前Insert
Buffer存在一个问题是:在写密集的情况下,插入缓冲会占用过多的缓冲池内存(innodb_buffer_pool),默认最大可以占用到1/2的缓冲池内存。

>> InnoDB从1.0.x版本开始引入了Change Buffer,可将其视为Insert
Buffer的升级。从这个版本开始,InnoDB存储引擎可以对DML操作——INSERT、DELETE、UPDATE都进行缓冲,他们分别是:Insert
Buffer、Delete Buffer、Purge buffer。

>>
innodb_change_buffer_max_size值默认为25,表示最多使用1/4的缓冲池内存空间。而需要注意的是,该参数的最大有效值为50。

>> 可能令绝大部分用户感到吃惊的是,Insert
Buffer的数据结构是一棵B+树。在MySQL 4.1之前的版本中每张表有一棵Insert
Buffer B+树。而在现在的版本中,全局只有一棵Insert Buffer
B+树,负责对所有的表的辅助索引进行Insert Buffer。

>>
而这棵B+树存放在共享表空间中,默认也就是ibdata1中。因此,试图通过独立表空间ibd文件恢复表中数据时,往往会导致CHECK
TABLE失败

>> 。这是因为表的辅助索引中的数据可能还在Insert
Buffer中,也就是共享表空间中,所以通过ibd文件进行恢复后,还需要进行REPAIR
TABLE操作来重建表上所有的辅助索引。

>> 如果说Insert
Buffer带给InnoDB存储引擎的是性能上的提升,那么doublewrite(两次写)带给InnoDB存储引擎的是数据页的可靠性。

>>
当发生数据库宕机时,可能InnoDB存储引擎正在写入某个页到表中,而这个页只写了一部分,比如16KB的页,只写了前4KB,之后就发生了宕机,这种情况被称为部分写失效(partial
page
write)。在InnoDB存储引擎未使用doublewrite技术前,曾经出现过因为部分写失效而导致数据丢失的情况。有经验的DBA也许会想,如果发生写失效,可以通过重做日志进行恢复。这是一个办法。但是必须清楚地认识到,重做日志中记录的是对页的物理操作,如偏移量800,写’aaaa’记录。如果这个页本身已经发生了损坏,再对其进行重做是没有意义的。这就是说,在应用(apply)重做日志前,用户需要一个页的副本,当写入失效发生时,先通过页的副本来还原该页,再进行重做,这就是doublewrite

>>
在对缓冲池的脏页进行刷新时,并不直接写磁盘,而是会通过memcpy函数将脏页先复制到内存中的doublewrite
buffer,之后通过doublewrite
buffer再分两次,每次1MB顺序地写入共享表空间的物理磁盘上,然后马上调用fsync函数,同步磁盘,避免缓冲写带来的问题。在这个过程中,因为doublewrite页是连续的,因此这个过程是顺序写的,开销并不是很大。在完成doublewrite页的写入后,再将doublewrite
buffer中的页写入各个表空间文件中,此时的写入则是离散的。

>>
InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,称之为自适应哈希索引(Adaptive
Hash Index,AHI)。

>> 值得注意的是,哈希索引只能用来搜索等值的查询,如SELECT*FROM
table WHERE
index_col=’xxx’。而对于其他查找类型,如范围查找,是不能使用哈希索引的,因此这里出现了non-hash
searches/s的情况

>>
用户可以在发出一个IO请求后立即再发出另一个IO请求,当全部IO请求发送完毕后,等待所有IO操作的完成,这就是AIO。AIO的另一个优势是可以进行IO
Merge操作,也就是将多个IO合并为1个IO,这样可以提高IOPS的性能

>> 需要注意的是,Native
AIO需要操作系统提供支持。Windows系统和Linux系统都提供Native
AIO支持,而Mac OSX系统则未提供

>> 参数innodb_use_native_aio用来控制是否启用Native
AIO,在Linux操作系统下,默认值为ON

>>
在关闭时,参数innodb_fast_shutdown影响着表的存储引擎为InnoDB的行为。该参数可取值为0、1、2,默认值为1。

>>
参数innodb_force_recovery影响了整个InnoDB存储引擎恢复的状况。该参数值默认为0,代表当发生需要恢复时,进行所有的恢复操作,当不能进行有效恢复时,如数据页发生了corruption,MySQL数据库可能发生宕机(crash),并把错误写入错误日志中去。

>>
参数innodb_force_recovery还可以设置为6个非零值:1~6。大的数字表示包含了前面所有小数字表示的影响

第3章 文件

>>
默认情况下,MySQL实例会按照一定的顺序在指定的位置进行读取,用户只需通过命令mysql–help
| grep my.cnf来寻找即可。

>> Oracle数据库存在所谓的隐藏参数(undocumented
parameter),以供Oracle“内部人士”使用,SQL
Server也有类似的参数。有些DBA曾问我,MySQL中是否也有这类参数。我的回答是:没有,也不需要。即使Oracle和SQL
Server中都有些所谓的隐藏参数,在绝大多数的情况下,这些数据库厂商也不建议用户在生产环境中对其进行很大的调整。

>> MySQL数据库中的参数可以分为两类:□ 动态(dynamic)参数□
静态(static)参数动态参数意味着可以在MySQL实例运行中进行更改,静态参数说明在整个实例生命周期内都不得进行更改,就好像是只读(read
only)的

>>
当出现MySQL数据库不能正常启动时,第一个必须查找的文件应该就是错误日志文件,该文件记录了错误信息,能很好地指导用户发现问题。

>>
设置long_query_time这个阈值后,MySQL数据库会记录运行时间超过该值的所有SQL语句,但运行时间正好等于long_query_time的情况并不会被记录下。也就是说,在源代码中判断的是大于long_query_time,而非大于等于

>>
另一个和慢查询日志有关的参数是log_queries_not_using_indexes,如果运行的SQL语句没有使用索引,则MySQL数据库同样会将这条SQL语句记录到慢查询日志文件。

>> MySQL
5.6.5版本开始新增了一个参数log_throttle_queries_not_using_indexes,用来表示每分钟允许记录到slow
log的且未使用索引的SQL语句次数。该值默认为0,表示没有限制。在生产环境下,若没有使用索引,此类SQL语句会频繁地被记录到slow
log,从而导致slow log文件的大小不断增加,故DBA可通过此参数进行配置。

>> MySQL
5.1开始可以将慢查询的日志记录放入一张表中,这使得用户的查询更加方便和直观。慢查询表在mysql架构下,名为slow_log

>>
查看slow_log表的定义会发现该表使用的是CSV引擎,对大数据量下的查询效率可能不高。用户可以把slow_log表的引擎转换到MyISAM,并在start_time列上添加索引以进一步提高查询的效率。

>>
不能忽视的是,将slow_log表的存储引擎更改为MyISAM后,还是会对数据库造成额外的开销。

>>
用户可以通过额外的参数long_query_io将超过指定逻辑IO次数的SQL语句记录到slow
log中。该值默认为100,即表示对于逻辑读取次数大于100的SQL语句,记录到slow
log中。而为了兼容原MySQL数据库的运行方式,还添加了参数slow_query_type,用来表示启用slow
log的方式

>>
查询日志记录了所有对MySQL数据库请求的信息,无论这些请求是否得到了正确的执行。默认文件名为:主机名.log

>> 。同样地,从MySQL
5.1开始,可以将查询日志的记录放入mysql架构下的general_log表中,该表的使用方法和前面小节提到的slow_log基本一样

>> 二进制日志(binary
log)记录了对MySQL数据库执行更改的所有操作,但是不包括SELECT和SHOW这类操作,因为这类操作对数据本身并没有修改。

>>
使用事务的表存储引擎(如InnoDB存储引擎)时,所有未提交(uncommitted)的二进制日志会被记录到一个缓存中去,等该事务提交(committed)时直接将缓冲中的二进制日志写入二进制日志文件,而该缓冲的大小由binlog_cache_size决定,默认大小为32K。

>>
此外,binlog_cache_size是基于会话(session)的,也就是说,当一个线程开始一个事务时,MySQL会自动分配一个大小为binlog_cache_size的缓存,因此该值的设置需要相当小心,不能设置过大。当一个事务的记录大于设定的binlog_cache_size时,MySQL会把缓冲中的日志写入一个临时文件中,因此该值又不能设得太小

>>
Binlog_cache_use记录了使用缓冲写二进制日志的次数,binlog_cache_disk_use记录了使用临时文件写二进制日志的次数

>>
默认情况下,二进制日志并不是在每次写的时候同步到磁盘(用户可以理解为缓冲写)。因此,当数据库所在操作系统发生宕机时,可能会有最后一部分数据没有写入二进制日志文件中,这会给恢复和复制带来问题

>>
即使将sync_binlog设为1,还是会有一种情况导致问题的发生。当使用InnoDB存储引擎时,在一个事务发出COMMIT动作之前,由于sync_binlog为1,因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志,但是提交还没有发生,并且此时发生了宕机,那么在MySQL数据库下次启动时,由于COMMIT操作并没有发生,这个事务会被回滚掉。但是二进制日志已经记录了该事务信息,不能被回滚。

>>
如果当前数据库是复制中的slave角色,则它不会将从master取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入,要设置log-slave-update。如果需要搭建master=>slave=>slave架构的复制,则必须设置该参数。

>> MySQL
5.1开始引入了binlog_format参数,该参数可设的值有STATEMENT、ROW和MIXED

>>
上面的这个例子告诉我们,将参数binlog_format设置为ROW,会对磁盘空间要求有一定的增加。而由于复制是采用传输二进制日志方式实现的,因此复制的网络开销也有所增加。

>>
要查看二进制日志文件的内容,必须通过MySQL提供的工具mysqlbinlog。对于STATEMENT格式的二进制日志文件,在使用mysqlbinlog后,看到的就是执行的逻辑SQL语句

>>
但不论表采用何种存储引擎,MySQL都有一个以frm为后缀名的文件,这个文件记录了该表的表结构定义。

>>
frm还用来存放视图的定义,如用户创建了一个v_a视图,那么对应地会产生一个v_a.frm文件,用来记录视图的定义,该文件是文本文件,可以直接使用cat命令进行查看

>>
设置innodb_data_file_path参数后,所有基于InnoDB存储引擎的表的数据都会记录到该共享表空间中。若设置了参数innodb_file_per_table,则用户可以将每个基于InnoDB存储引擎的表产生一个独立表空间

>>
。独立表空间的命名规则为:表名.ibd。通过这样的方式,用户不用将所有数据都存放于默认的表空间

>>
这些单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息,其余信息还是存放在默认的表空间中

>>
在默认情况下,在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。在MySQL官方手册中将其称为InnoDB存储引擎的日志文件,不过更准确的定义应该是重做日志文件(redo
log
file)。为什么强调是重做日志文件呢?因为重做日志文件对于InnoDB存储引擎至关重要,它们记录了对于InnoDB存储引擎的事务日志。

>>
每个InnoDB存储引擎至少有1个重做日志文件组(group),每个文件组下至少有2个重做日志文件,如默认的ib_logfile0和ib_logfile1。为了得到更高的可靠性,用户可以设置多个的镜像日志组(mirrored
log
groups),将不同的文件组放在不同的磁盘上,以此提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致,并以循环写入的方式运行。

>>
InnoDB存储引擎先写重做日志文件1,当达到文件的最后时,会切换至重做日志文件2,再当重做日志文件2也被写满时,会再切换到重做日志文件1中。

>>
若磁盘本身已经做了高可用的方案,如磁盘阵列,那么可以不开启重做日志镜像的功能

>>
二进制日志会记录所有与MySQL数据库有关的日志记录,包括InnoDB、MyISAM、Heap等其他存储引擎的日志。而InnoDB存储引擎的重做日志只记录有关该存储引擎本身的事务日志。

>>
其次,记录的内容不同,无论用户将二进制日志文件记录的格式设为STATEMENT还是ROW,又或者是MIXED,其记录的都是关于一个事务的具体操作内容,即该日志是逻辑日志。而InnoDB存储引擎的重做日志文件记录的是关于每个页(Page)的更改的物理情况。

>>
此外,写入的时间也不同,二进制日志文件仅在事务提交前进行提交,即只写磁盘一次,不论这时该事务多大。而在事务进行的过程中,却不断有重做日志条目(redo
entry)被写入到重做日志文件中。

>>
在InnoDB存储引擎中,对于各种不同的操作有着不同的重做日志格式。到InnoDB
1.2.x版本为止,总共定义了51种重做日志类型。

>>
在第2章中已经提到,写入重做日志文件的操作不是直接写,而是先写入一个重做日志缓冲(redo
log buffer)中,然后按照一定的条件顺序地写入日志文件

>>
从重做日志缓冲往磁盘写入时,是按512个字节,也就是一个扇区的大小进行写入。因为扇区是写入的最小单位,因此可以保证写入必定是成功的。因此在重做日志的写入过程中不需要有doublewrite。

>>
因此为了保证事务的ACID中的持久性,必须将innodb_flush_log_at_trx_commit设置为1,也就是每当有事务提交时,就必须确保事务都已经写入重做日志文件。那么当数据库因为意外发生宕机时,可以通过重做日志文件恢复,并保证可以恢复已经提交的事务。

第4章 表

>>
在InnoDB存储引擎中,表都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index
organized table)。

>> 在InnoDB存储引擎表中,每张表都有个主键(Primary
Key),如果在创建表时没有显式地定义主键,则InnoDB存储引擎会按如下方式选择或创建主键:□
首先判断表中是否有非空的唯一索引(Unique NOT
NULL),如果有,则该列即为主键。□
如果不符合上述条件,InnoDB存储引擎自动创建一个6字节大小的指针。

>>
当表中有多个非空唯一索引时,InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键

>>
。这里需要非常注意的是,主键的选择根据的是定义索引的顺序,而不是建表时列的顺序。

>> 可以通过下面的SQL语句判断表的主键值:

>> _rowid可以显示表的主键,因此通过上述查询可以找到表z的主键

>>
另外需要注意的是,_rowid只能用于查看单个列为主键的情况,对于多列组成的主键就显得无能为力了

>>
从InnoDB存储引擎的逻辑存储结构看,所有数据都被逻辑地存放在一个空间中,称之为表空间(tablespace)。表空间又由段(segment)、区(extent)、页(page)组成。页在一些文档中有时也称为块(block),

>> InnoDB存储引擎的逻辑存储结构大致如图4-1所示。

>>
第3章中已经介绍了在默认情况下InnoDB存储引擎有一个共享表空间ibdata1,即所有数据都存放在这个表空间内。如果用户启用了参数innodb_file_per_table,则每张表内的数据可以单独放到一个表空间内。

>>
如果启用了innodb_file_per_table的参数,需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页,其他类的数据,如回滚(undo)信息,插入缓冲索引页、系统事务信息,二次写缓冲(Double
write
buffer)等还是存放在原来的共享表空间内。这同时也说明了另一个问题:即使在启用了参数innodb_file_per_table之后,共享表空间还是会不断地增加其大小。

>>
InnoDB存储引擎不会在执行rollback时去收缩这个表空间。虽然InnoDB不会回收这些空间,但是会自动判断这些undo信息是否还需要,如果不需要,则会将这些空间标记为可用空间,供下次undo使用。

>>
我用python写了一个py_innodb_page_info小工具,用来查看表空间中各页的类型和信息,用户可以在code.google.com上搜索david-mysql-tools进行查找

>> 因为前面已经介绍过了InnoDB存储引擎表是索引组织的(index
organized),因此数据即索引,索引即数据。那么数据段即为B+树的叶子节点(图4-1的Leaf
node segment),索引段即为B+树的非索引节点(图4-1的Non-leaf node
segment)。

>>
区是由连续页组成的空间,在任何情况下每个区的大小都为1MB。为了保证区中页的连续性,InnoDB存储引擎一次从磁盘申请4~5个区。在默认情况下,InnoDB存储引擎页的大小为16KB,即一个区中一共有64个连续的页。

>> InnoDB
1.0.x版本开始引入压缩页,即每个页的大小可以通过参数KEY_BLOCK_SIZE设置为2K、4K、8K,因此每个区对应页的数量就应该为512、256、128。

>> InnoDB
1.2.x版本新增了参数innodb_page_size,通过该参数可以将默认页的大小设置为4K、8K,但是页中的数据库不是压缩。这时区中页的数量同样也为256、128。总之,不论页的大小怎么变化,区的大小总是为1M。

>>
在用户启用了参数innodb_file_per_talbe后,创建的表默认大小是96KB。区中是64个连续的页,创建的表的大小至少是1MB才对啊?其实这是因为在每个段开始时,先用32个页大小的碎片页(fragment
page)来存放数据,在使用完这些页之后才是64个连续页的申请。这样做的目的是,对于一些小表,或者是undo这类的段,可以在开始时申请较少的空间,节省磁盘容量的开销

>>
因为已经用完了32个碎片页,新的页会采用区的方式进行空间的申请,如果此时用户再通过py_innodb_page_info工具来看表空间文件t1.ibd,应该可以看到很多类型为Freshly
Allocated Page的页:

>> 从InnoDB
1.2.x版本开始,可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成,则所有表中页的大小都为innodb_page_size,不可以对其再次进行修改。除非通过mysqldump导入和导出操作来产生新的库

>> 每个页存放的行记录也是有硬性定义的,最多允许存放16KB /
2-200行的记录,即7992行记录

>>
InnoDB存储引擎提供了Compact和Redundant两种格式来存放行记录数据,

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website