MYSQL高级

数据库和数据库实例

数据库：数据库是是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合。

数据库实例：数据库实例是程序，是位于用户与操作系统之间的一层数据管理软件，用户对数据库数据的任何操作都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

MYSQL的配置文件查找顺序及注意事项

命令： mysql --help | grep my.cnf

/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 

主要注意的是，同一参数，MYSQL会以读取到的最后一个配置文件中的参数为准。

存储引擎是基于表的还是基于数据库的？

存储引擎是基于表的

MyISAM和INNODB对比

对比项	MYISAM	INNODB
外键	不支持	支持
事务	不支持	支持
锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
优势用途	读数据	innodb 主键为聚簇索引，基于聚簇索引的增删改查效率非常高
全文索引	支持	支持
是否是默认存储引擎	5.5之前是	5.5之后是

INNODB存储引擎支持非锁定读，即默认读取操作不会产生锁。
INNODB存储引擎和MYISAM存储引擎都是以B+树来实现索引的。
INNODB使用多版本并发控制来获得高并发性，并且实现了SQL的4种隔离级别，默认为REPEATABLE级别。
淘宝发起了去IO异化，ORACLE在互联网公司的使用比例下降。
对于表中数据，INNODB存储引擎采取了聚集的方式，因此每张表的存储都是按主键的顺序进行存放，如果没有显式地在表定义时指定主键，INNODB存储引擎会为每一行生成6字节的ROWID，并以此作为主键。
数据库系统与文件系统的一个最大的区别是是否支持事务。

MYSQL总体概览

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

MySQL整体分为三层：

（1）客户端，是各种编程语言的connector；

（2）MySQL服务，内部包含各种组件，实现各种功能；

（3）文件系统，数据存储与日志；

查看mysql已提供的存储引擎:

  mysql> show engines;

查看mysql默认的存储引擎:

  mysql> show variables like '%storage_engine%';

INNODB的架构

INNODB就是由一组线程和一块内存空间组成的，线程分类如下：

1. Master Thread
2. IO Thread
3. Purge Thread
4. Page Cleaner Thread

INNODB的缓冲池

INNODB存储引擎是基于磁盘存储的，并将其中记录按照页的方式进行管理，因此可将其看作是基于磁盘的数据库系统，在数据库系统中，由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。

对于数据库中的页进行修改操作，首先修改发生在缓冲池中，然后以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次发生更新时触发，而是通过一种称为check point的机制刷新回磁盘，提高了数据库的整体性能。

buffer pool hit rate

我们可以通过show engine innodb status来查看这个参数，该参数表示缓冲命中率，一般而言该参数不应该小于95 persent， 否则用户就应该检查是否是由于全表臊面引起的LRU列表被污染了。

可能导致SQL性能下降的几个因素

• SQL写的不好（该使用索引的时候没有使用）
• 关联了太多的表
• 索引失效
• 服务器调优及各个参数设置（缓冲、线程数等）

check point机制

解决的问题

• 缩短数据库的恢复时间
• 缓冲池不够用时，将脏页刷新到磁盘
• 重做日志不可用时，刷新脏页。

check point分类

• sharp Check Point(在关闭数据库的时候将所有脏页刷新回磁盘)
• Fuzzy Check Point(一定情形下，将部分脏页刷新回磁盘)

插入缓冲

插入缓冲的背景
在INNODB存储引擎中，主键是行唯一的标识符，通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的，不需要随机访问磁盘，速度较快。但是一张表中通常还会有多个辅助索引，在聚集索引的插入过程是顺序的，在辅助索引的插入过程是随机的，导致性能下降。

插入缓冲的实现
有了插入缓冲之后，对于非聚集索引的插入并不是直接插入到索引页中，而是先判断非聚集所以也是否在缓冲池中，如果命中，则直接插入，如果没有命中，则先放入到一个插入缓冲对象中，然后以一定的频率将插入缓冲对象和辅助索引页进行合并，这时通常能将多个插入合并到一个操作中，大大提高了非聚集索引插入的性能。插入缓冲实际上就是一个B+树。并且按照（space , offset）进行排序。

使用插入缓冲的前提

• 索引是辅助索引
• 索引不是唯一的

插入缓冲的坏处
如果应用程序进行了大量的插入操作，并且使用到了插入缓冲，若此时宕机了，这时必定在插入缓冲中有大量的数据没有合并到非聚集索引中，这时恢复可能需要很长时间。

合并插入缓冲的条件

1. 辅助索引页被读取到缓冲池时
2. insert buffer bitmap页追踪到该辅助索引页已无可用空间
3. Master Thread

两次写

如果说插入缓冲带给INNODB存储引擎的是性能上的提升，那么两次写带给INNODB存储引擎的是数据页的可靠性。

如果发生写失效，可以通过重做日志进行恢复，这时一个办法，但是必须清楚的是，重做日志中记录是对页的物理操作。如果这个页本身已经发生了损坏，再对其进行重做是没有意义的。我们的做法应该是在应用重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本还原页，再进行重做。

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

如何查看当前系统的写入数据的压力
我们可以通过以下命令查看
show global status like ‘innodb_dblwr%’\G
如果innodb_dblwr_pages:innodb_dblwr_writes远小于64:1，那么可以说系统写入压力不高

如何查看系统写入磁盘的页的个数
查看变量innodb_dblwr_pages_written的值

自适应哈希

innodb会监控各索引页的查询，如果观察到建立哈希所以可以带来速度提升，则建立哈希索引，称之为自适应哈希索引，自适应哈希索引是通过缓冲池的B+数页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引，INNODB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引

注意点：

1. 建立哈希索引，首先对于该页的访问模式必须是一样的
2. 以该模式访问该页100次或者页中记录/16次
3. 哈希索引只能用来搜索等值的查询

异步IO

好处：

1. 不需要等待当前IO请求完成就可以申请下一次的IO访问
2. 可以进行IO合并操作，即将多个IO合并为1个IO

索引组织表

在innodb存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。在innodb存储引擎表中，每张表都有一个主键，如果在创建表的时候没有显式定义主键，则innodb存储引擎会按照如下方式选择或创建主键

• 首先判断表中是否有非空的唯一索引，如果有，则该列即为主键
• 如果不符合上述条件，innodb存储引擎会自动创建一个6字节大小的指针
• 当有多个非空唯一索引时，innodb将选择创建表时第一个定义的非空唯一索引为主键，即定义索引的顺序，而不是字段的顺序

表空间

表空间可以看做是innodb存储引擎逻辑结构的最高层，所有的数据都存放在表空间中，如果用户启用了innodb_file_per_table的参数，那么每张表内的数据可以单独存放到一个表空间中，但是每张表的表空间内存存放的只是数据、索引和插入缓冲Bitmap页，其他数据如回滚（undo）信息、插入缓冲索引页、系统事务信息、二次写缓冲等还是存放在原来的共享表空间中

数据段是B+ 树的叶子节点，索引段是B+树的非索引节点

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

每个段开始时，先用32个页大小的碎片页来存放数据，在使用完这些页之后才是64个连续也的申请。这样做的目的是，对于一些小表，或者undo这类的段，可以在开始时申请较少的空间，节省磁盘容量。

在innodb中同样有页的概念，页是innodb磁盘管理的最小单位，在innodb存储引擎中，默认每个页的大小是16KB，可以通过参数innodb_page_size修改。

innodb存储引擎是面向行的，即数据是按照行进行存储的。每个页最多存放7992行记录。

Compact 和Redundant 对比

对比项	Compact	Redundant
varchar为空时是否占用空间	否	否
char为空时是否占用空间	否	是
隐藏列	事务ID列、回滚指针列	事务ID列、回滚指针列
如果没有主键	新增主键，主键占6字节空间
如果有主键	主键放指定的元素，该元素只出现一次	主键放指定的元素，该元素只出现一次

行溢出数据

innodb存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外，并不是说是BLOB就一定会产生行溢出数据，varchar就一定不会产生，至于是否会产生行溢出现象，主要看在一个数据页中是否可以存放至少两个记录，如果能就不会产生行溢出数据。对于会产生行溢出的列，其前768字节会存放在数据页中（B+Tree Node），从768字节往后，行溢出的数据存放在页类型为Uncompress BLOB页中。

varchar的最大长度是与编码格式息息相关的，因为在一个表中所有的varchar类型所占内存空间之和要小于等于65535字节，对于拉丁字符集默认只能存放65532个字符。在不使用Uncompress BLOB页的前提下，一个varchar类型占用的最大内存为8398字节。

char 类型的行结构存储

我们一般认为varchar是存储变长长度的字符类型，char是存储固定长度的字符类型，MYSQL4.1之后char（N）中N代表的是字符个数，即在不同的字符集下，char类型列内部存储的可能不是定长数据。

对于多字节字符编码的char数据类型的存储，innodb存储引擎在内部将其看做变长字符类型。即在变长长度列表中会记录char数据类型的长度。对于没有使用到的字节会使用0x20进行填充。

INNODB数据页的格式

页是innodb存储引擎管理数据库的最小磁盘单位，页类型为B-Tree Node的页存放的就是表中行的实际数据了，INNODB存储引擎表是B+树索引组织的，一个INNODB数据页包括以下内容：

在FILE Header中是包含指向上一页和下一页的指针的，所以这就是B+树索引针对B+树的改造。
在innodb存储引擎中，每个数据也中有两个虚拟的行记录，用来限定记录的边界，infimum记录是比该页中任何主键都要小的值，Supremum指比改页中任何记录的主键值都要大的值。这两个记录在页创建的时候被建立，并且在任何情况下不会被删除。伪记录中只存放了一个列：char(8)

页目录中并不是每一个记录都对应一个槽，即innodb存储引擎的页目录是一个稀疏目录。B+树索引本身并不能找到具体的一条记录，能找到的只是该记录所在的页，数据库把页载入到内存，然后通过页目录进行二叉查找，因此页目录是一个稀疏目录，因此二叉查找的结果只能是一个粗略的结果，因此innodb存储引擎必须通过记录头的next_record来继续查找相关记录。因为页目录是在内存中，因此查找速度非常快的。

约束

关系型数据库系统和文件系统的一个不同点是，关系型数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。

数据完整性的三种形式：

• 实体完整性保证表中有一个主键
• 域完整性保证数据每列的值满足特定的条件
• 参照完整性保证两张表之间的关系

约束和索引的区别：
约束是一个逻辑概念，用来保证数据的完整性，索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式

如何使用ENUM和SET来模仿CHECK约束？
如果我们想要通过ENUM实现CHECK约束的时候，一定要将参数sql_mode设置为STRICT_TRANS_TABLES，并且我们只能对离散值进行约束，而对于CHECK约束支持的连续值的范围约束或更复杂的约束，以上办法无能为力，可以使用触发器来实现对于值域的约束。

外键约束

在MYISAM存储引擎中是不支持外键约束的，对于外键的定义只是起到一个注释的作用，但是在INNODB存储引擎中支持外键约束的。添加外键约束的时候就会同时增加一个索引。

ON DELETE和ON UPDATE的可选值

• CASCADE（和父表执行一样的操作）
• SET NULL（子表相应的数据被设置为NULL）
• NO ACTION（抛出错误）
• RESTRICT（抛出错误）

设置外键的坏处
当我们在导入数据的时候，外键往往在导致外键约束的检查上花费大量时间，因为MYSQL数据库的外键是即时检查的，所以对导入的每一行都会进行检查。我们可以通过设置从而在导入过程中忽略外键检查。

触发器

什么是触发器？
触发器可以做到在执行INSERT、DELETE和UPDATE命令之前或之后自动调用SQL命令或存储过程。

最多可以给一个表建立6个触发器，即分别为INSERT、UPDATE、DELETE的BEFORE和AFTER各自定义一个，BEFORE和AFTER代表触发器发生的时间，表示是在每一行操作之前发生还是之后发生，当前MYSQL只支持FOR EACH ROW的触发方式，即按每一行记录进行触发。

视图

在MYSQL数据库中，视图是一个命名的虚表，视图中并不存放实际的数据，而存放SQL逻辑，使用上和表一样。
物化视图
物化视图并不是基于基表的虚表，而是根据基表实际存在的实表，存放在内存中，物化视图可以用于预先计算并保存多表连接（join）或聚集（group by）等耗时较多的SQL操作结果。在执行复杂查询时，就可以避免进行这些耗时的操作，从而快速得到结果。

物化视图的刷新是指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步，有两种选项：ON DEMAND和ON COMMIT，其中ON DEMAND意味着物化视图在用户需要的时候进行（手动）刷新，ON COMMIT意味着物化视图在对基表的DML操作提交的同时进行刷新。

物化视图的刷新方式有以下四种：FAST刷新采用增量刷新、COMPLETE即全部刷新、FORCE刷新回优先使用FAST刷新、NEVER是不对物化视图进行刷新

我们可以实现ON DEMAND和ON COMMIT两种方式，但是我们无法实现查询重写，这个只能从应用端进行控制。

查询重写
查询重写是指当对物化视图的基表进行查询时，数据库会自动判断能否通过查询物化视图来直接得到最终的结果，如果可以，则避免了聚集或链接等这类较为复杂的SQL操作，直接从计算好的物化视图中得到所需的数据。

分区表

分区功能并不是在存储引擎层完成的，因此不是只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持。单也并不是所有的存储引擎都支持，如CSV、FEDORATED、MERGE等。

分区的过程就是将一个表或者索引分解为多个更小、更好管理的部分。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理

MYSQL数据库支持的分区类型为水平分区，并不支持垂直分区。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指数据存放在各个分区中，但是所有数据的索引放在一个对象中。

分区类型

• range分区（连续的，主要用于日期列的分区）
• list分区（离散的）
• hash分区（通过用户自定义的函数将数据均匀地分布到预先定义的各个分区中）
• key分区（使用系统定义函数进行分区）
• COLUMNS分区

分区注意事项

• 不论创建何种类型的分区，如果表中存在主键或者唯一键（允许为NULL）时，分区列必须是唯一索引的一个组成部分

• 如果建表没有指定主键或者唯一键，可以指定任意一个列为分区列

• 对于RANGE分区的查询，优化器只能对YEAR()、TO_DATE()、TO_SECONDS()、UNIX_TIMESTAMP()这类函数进行优化选择

• 对于LIST分区的插入操作，如果遇到了分区未定义的值的时候，不同存储引擎的行为是完全不同的，MyISAM引擎会将之前的行数据都插入，但之后的数据不会被插入。而InnoDB将其看做是一个事务，因此没有任何数据插入。

• 插入一个不在分区定义中的值，会抛出一个异常（range。list）

• 可以将COLUMNS分区看做是RANGE分区和LIST分区的一种进化，COLUMNS分区可以直接使用非整形的数据进行分区，分区根据类型直接比较而得，不需要转化为整形。COLUMNS分区支持多个列的值进行分区。因此推荐使用COLUMNS分区

子分区

子分区是在分区的基础上再进行分区，MySQL数据库支持在RANGE和LIST的分区上再进行HASH或KEY的子分区。分区可能会给某些SQL语句性能带来提高，但是分区主要用于数据库的高可用性的管理。在OLTP应用中，对于分区的使用应该非常小心。
子分区注意事项

• 每个子分区的数量必须相同
• 要在一个子分区的任何分区上使用SUBPARTITION来明确定义任何子分区，就必须定义所有的子分区
• 每个SUBPARTITION子句必须包括子分区的一个名字
• 子分区的名字必须是唯一的

分区中NULL值的划分

• MySQL支持对NULL值做分区，MySQL将NULL值看做小于任何一个非NULL值，这和order by的操作是一样的
• 在RANGE分区下，NULL值会被放在最左边的分区中，因此如果删除最左边的分区，删除的不仅仅是符合条件的记录还 包括NULL值的记录
• 在LIST分区下使用NULL值，必须显式指出哪个分区中放入NULL值，否则报错
• 在HASH分区和KEY分区中，任何分区函数都会将含有NULL的值的记录返回0

分区与性能

• 数据库的应用分为：OLTP（在线事务处理）和OLAP（在线分析处理），一般应用中会涉及两种
• 对于OLAP的应用，分区的确可以很好地提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表
• 对于OLTP的应用，分区应该非常小心，这种应用下通常不会获取表中数据的10%，大部分是通过索引返回几条记录即可，由B+树索引的原理可知，对于一张大表，一般的B+树需要2-3次的磁盘IO。因此B+树索引可以很好的完成操作，不需要分区的帮助，并且设计不好的分区可能会带来严重的性能问题
• 在OLTP应用中，如果分区将会导致遍历所有分区，导致性能下降

在分区和表之间交换数据

MySQL5.6开始支持ALTER TABLE … EXCHANGE PARTITION语法，该语句允许分区或子分区中的数据与另一个费分区的表中的数据进行交换，如果非分区表中的数据为空，那么相当于将分区中的数据移动到非分区表中，若分区表中数据为空，则相当于将外部表中的数据导入到分区中

使用前提

• 要交换的表和分区具有相同的表结果，但不能包含分区
• 在非分区表中的数据必须在交换分区定义内
• 被交换的表中不能包含外键或者其他表对于该表的外键引用
• 用户除了ALTER、INSERT和CREATE权限外，还需要DROP权限
• 使用该语句时，不会触发交换表和被交换表上的触发器
• AUTO_INCREMENT列将会被重置

SQL执行顺序

手写

机读

随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：

索引

什么是索引

        MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。达到高效获取数据的手段是排序，所以可以认为索引是通过排序实现快速查找数据结构。

        在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

        一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。所以在选择高效的检索算法以及其实现上需要进行考虑，因为此时的主要矛盾在于磁盘IO次数，这也是为什么在文件系统或者数据库管理系统中不使用二叉树及其变种红黑树的原因（虽然查找的时间复杂度是树的高度，但是树挺高的）。

索引的好处

• 降低数据库的IO成本，提高用户查询速度 ，

• 利用索引的唯一性来控制记录的唯一性

• 可以加速表与表之间的连接

• 降低查询中分组和排序的时间 ，降低了CPU的消耗

索引的坏处

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息