1、存储引擎

Mysql默认的存储引擎是InnoDB，在5.7版本只有InnoDB是支持事务的。

• 查看默认的存储引擎：mysql> show variables like ‘%storage_engine%’;
• 查看表的存储引擎：show table status like “table_name” ;

MyISAM和InnoDB区别？

在5.5之前，默认引擎是MyISAM 。优点有：全文索引，压缩，空间函数，但是不支持事务和行级锁，最大的缺陷是崩溃之后无法安全恢复，在5.5之后，引入InnoDB并成为默认引擎。

两者的对比

	MyISAM	InnoDB
行级锁	表级锁	表级锁和行级锁(默认)
事务和崩溃后安全恢复	不支持	支持，同时具有回滚，崩溃修复能力
外键	不支持	支持
MVCC	不支持	支持

MVCC多版本控制，比单纯的锁更加高效，只在Read Committed和Repetable read两个隔离级别工作，可以使用乐观锁和悲观锁来实现。

MyISAM更注重性能。但不是首选。

2、字符集与校对规则

字符集指的是一种从而二进制编码到某类字符符号的映射。校对规则是指某种字符集下的排序规则。MySql中每一种字符集都会对应一系列的校对规则。

M采用的是类似继承的方式制定字符集的默认值，每个数据库和每张数据表都有自己的默认值，他们逐层继承。比如：某个库中所有表的默认字符集将是该数据库所指定的字符集(这些表在没有指定字符集的情况下，才会采用摩恩字符集)。

详细参考：字符集与校对规则

3、索引

参考

索引的数据结构主要有：B树和哈希索引。哈希索引的底层就是哈希表，因为在绝大多数需求为单条记录查询的时候，可以选择哈希索引，其他情况下选择B树索引。

B树使用的是B+树。两种存储引擎的实现方式不同：

• MyIASM

  叶节点的data区域存放是是数据记录的地址。在索引检索的时候，按照B树算法索引，指定的Key存在，则取出data域的值，然后以data域的值为地址读取对应的数据记录。这被称为“非聚簇索引”。

• InnoDB

  数据文件本身就是索引文件。树的叶节点data域保存了完整的数据记录，因此InnoDB表数据文件本身就是主索引，这称为“聚簇索引”。其余的索引作为辅助索引，辅助索引的data记录的是相应记录主键的值而不是地址。

  在根据主索引搜索时，直接找到Key所在的节点；在根据辅助索引查找时，先取出主键的值，再走一遍主索引。因此在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，会造成主索引频繁分裂。

4、查询缓存

8.0版本移除，不实用。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启查询缓存要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十MB比较合适。

5、事务

事务的四大特性

• 原子性：事务是自小的执行单位。原子性确保动作要么全部完成，要么全部不执行。
• 一致性：数据库从一个正确状态变化到另一个一致性状态。
• 隔离性：并发访问数据库，两个用户的事务不干扰。并发事务之间的数据库是独立的。
• 持久性：对数据库的改变是持久的，即使数据库发生故障也不应该有影响。

并发事务带来的问题

• 脏读

  一个事务修改数据库，还没提交。另一个事务读数据，因为这个数据还没提交，所以读出的是脏数据。对脏数据的操作可能不正确。

• 丢失修改

  两个事务读一个数据，第一个事务修改之后第二个事务也修改，这样第一个事务修改的结果就丢失了。保留第二次修改的结果。

• 不可重复读

  一个事务内多次读用一个数据，在两次读数据之间，由于第二个事务修改数据，导致两次读的数据不同，称为不可重复读。

• 幻读

  它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读的重点是多次读取发现某些列被修改。

幻读重点是多次读取发现记录增多后者减少。

数据库的隔离级别：

• 读未提交

  允许读取尚未提交的数据变更，可能导致脏读幻读或者不可重复读。

• 读已提交

  允许读取并发事务已经提交的数据。避免脏读，但是幻读和不可重复读可能发生。

• 可重复读

  对同一字段的多次读取结果都是一致的，除非被本身事务自己修改。

• 可串行化

  最高的更隔离级别。服务ACID，所以的事务逐个执行，这样事务之间不产生干扰

InnoDB默认是可重复读。使用的是next-key lock算法，避免幻读，已经达到了可串行化的隔离级别。隔离级别越低，锁越少，所以大部分数据库的隔离级别都是读已提交，但是InnoDB使用可重复读，并且不会有性能损失。InnoDB在分布式事务的情况下使用可串行化的隔离级别。

锁机制与InnoDB锁算法

• 表级锁：粒度最大的锁，整张表加锁。实现简单，资源消耗少，加锁快，不会出现死锁。出发锁冲突的概率高，并发度最低。
• 行级锁：粒度最小，当前操作行加锁。大大减少数据库操作的冲突，并发度高。开销大，加锁慢，会出现死锁。

InnoDB存储引擎的算法有三种：

• Reacord lock：单行记录锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身。
• Next-key lock：record+gap，包含记录本身。

补充：

1. innodb对于行的查询使用next-key lock
2. Next-locking keying为了解决Phantom Problem幻读问题
3. 当查询的索引含有唯一属性时，将next-key lock降级为record key
4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
5. 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

6、大表优化

MYSQL表单过大，数据库的CRUD性能下降，措施如下：

• 限定数据范围

  禁止不带任何限制数据范围条件范围的查询语句。

• 读写分离

  主库写，从库读。

• 垂直分区

  数据表的相关性进行拆分。把一张列比较多的表拆分成多张表。

  • 优点：列数据变小，在查询时减少读取的block数，减少IO次数。简化表结构，易于维护。
  • 缺点：主键冗余，需要管理冗余列，引起join操作，事务变得复杂。

• 水平分区

  保持数据表结构不变，通过策略存储数据分片，这样每一片数据分散到不同的表或者库中，达到分布式的目的。

  水平拆分支持大的数据量。但是分表仅仅解决了单一表数据量大的问题，但是表数据还在同一台机器上，其实对于并发能力没有太多提升。所以水平拆分最好分库。

  分片支持大的数据量存储，但是分片事务难以解决，逻辑复杂，尽量不分片，非要分片，尽量选择客户端分片，可以减少一次和中间件的网络IO

  数据库分片的两种常见方案：

  • 客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC （推荐） 、阿里的TDDL是两种比较常用的实现。
  • 中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

分库分表之后，ID主键如何处理：

• UUID：太长无序不可读，查询效率低，比较适合用于生成名字唯一的标识比如文件的名字。
• 自增ID：两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
• redis：性能好，灵活方便，不依赖数据库。
• 雪花算法

7、池化

这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。除了初始化资源，池化设计还包括如下这些特征：池子的初始值、池子的活跃值、池子的最大值等，这些特征可以直接映射到java线程池和数据库连接池的成员属性中。这篇文章对池化设计思想介绍的还不错。

数据库连接本质就是一个 socket 的连接。数据库服务端还要维护一些缓存和用户权限信息之类的 所以占用了一些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重用这些连接。为每个用户打开和维护数据库连接，尤其是对动态数据库驱动的网站应用程序的请求，既昂贵又浪费资源。在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。 连接池还减少了用户必须等待建立与数据库的连接的时间。

8、常见问题：

MySQL高性能规范

参考阅读

SQL执行的很慢的原因

参考阅读

行存储与列存储

摘自

主流的（OLTP）数据库大多数采用行存储，随着分析性数据库（OLAP）数据库的兴起，列存储又变的流行。

列存储优势一方面体现在存储上节约空间，减少IO。另一方面依靠列式数据结构做了计算上的优化。

什么是列式存储

传统OLTP数据库通常采用行存储，所有的列依次排列成一行，以行为单位存储，再配合B+树或者SS-Table作为索引，就能快速通过主键找到相应的行数据。

行存储对于OLTP场景很自然：大多数操作都是以实体为单位，把一行数据存储在相邻的位置是个很好的选择。

对于OLAP场景来说，一个典型的查询需要遍历整张表，进行分组、排序、聚合等操作，这样一来行存储就没有优势了。更糟糕的是，分新型SQL通常不会用到所有的列，仅仅对某些感兴趣的列运算，一行中的无关列也不得不参与扫描。

列存储就是为这样的需求设计的。如下图所示，同一列的数据被一个一个紧挨着放在一起，表的每列构成一个长数组。

列存储对于OLTP的场景不友好，一行数据的写入需要同时修改多个列。但是对于OLAP数据库有着很大的优势。

• 当查询语句只设计到部分列时，只需要扫描相关的列。
• 每一列的数据都是相同类型，彼此间的相关性更大，对列数据存储的压缩效率高。

Bigtable(HBase)是列存储吗？

其实不是列存储，是按照key-value pair存储数据，和列存储无关系。

但是BT有列簇概念。列簇可以指定给某个locality group，决定改列簇数据的物理位置，从而让同一主键的各个列簇分别存放在最优的物理节点上，

由于 column family 内的数据通常具有相似性，对它做压缩要比对整个表压缩效果更好。

列式数据库可以是关系型、也可以是 NoSQL，这和是否是列式并无关系。

DSM 分页模式

我们知道，由于机械磁盘受限于磁头寻址过程，读写通常都以一块（block）为单位，故在操作系统中被抽象为块设备，与流设备相对。这能帮助上层应用是更好地管理储存空间、增加读写效率等。这一特性直接影响了数据库储存格式的设计：数据库的 Page 对应一个或几个物理扇区，让数据库的 Page 和扇区对齐，提升读写效率。

大多数服务于在线查询的DBMS采用NSM即安行存储的方式，将完整的行从header开依次存放。页的最后有一个索引，存放了页内各行的起始偏移量。由于每行的长度不一定固定，索引可以帮我们快速找到需要的行，无序逐个扫描。但是缺点在于，如果每次只涉及到很小的一部分列，那多余的列依然浪费内存以及CPU cache，导致更多的IO，为了避免这一问题，分析性数据库采用DSM列存储：将relationan按照列拆分成多个sub-relation。类似的，在页尾部存放一个索引。

NSM可以快速取出某一行的数据，因为一行的数据保存在同一页；DSM能更好的利用CPU cache以及更紧凑的压缩。

分布式储存系统虽然不再有页的概念，但是仍然会将文件切割成分块进行储存，但分块的粒度要远远大于一般扇区的大小（如 HDFS 的 Block Size 一般是 128MB）。更大的读写粒度是为了适应网络 IO 更低的带宽以获得更大的吞吐量，但另一方面也牺牲了细粒度随机读写。

列存储与分布式文件系统

在现代的大数据架构中，GFS，HDFS等分布式文件系统已经成为存放按规模数据集的主流方式，。分布式文件系统相比单机的磁盘，具备多副本高可用容量大成本低等优势，但是也有一些单机架构没有的问题。

• 读写均要经过网络，吞吐量可以追平甚至超过硬盘，但是延迟要比硬盘大得多，且受网络环境影响很大。
• 可以进行大吞吐量的顺序读写，但随机访问性能很差，大多不支持随机写入。为了抵消网络的 overhead，通常写入都以几十 MB 为单位。

以上缺点对于重度依赖读写的OLTP场景数据库来说是致命的。所以很多定位于OLAP的列存储放弃OLTP能力，从而构建在分布式文件系统之上。

充分发挥分布式文件系统的性能，有以下几种方式：按块读取数据，流式读取，追加写入等。

总结

本文介绍了列式存储的存储结构设计。抛开种种繁复的细节，我们看到，以下这些思想或设计是具有共性的。

1. 跳过无关的数据。从行存到列存，就是消除了无关列的扫描；ORC 中通过三层索引信息，能快速跳过无关的数据分片。
2. 编码既是压缩，也是索引。Dremel 中用精巧的嵌套编码避免了大量 NULL 的出现；C-Store 对 distinct 值的编码同时也是对 distinct 值的索引；PowerDrill 则将字典编码用到了极致（见下一篇文章）。
3. 假设数据不可变。无论 C-Store、Dremel 还是 ORC，它们的编码和压缩方式都完全不考虑数据更新。如果一定要有更新，暂时写到别处、读时合并即可。
4. 数据分片。处理大规模数据，既要纵向切分也要横向切分，不必多说。

MYSQL复制

摘自简书

保证主服务器(master)和从服务器(Slave)的数据是一致性的，向master插入数据后，slave会自动从master把修改的数据同步过来(有一定延迟)，通过这种方式保证数据一致性，就是mysql复制。

复制能解决什么问题？

• 高可用和故障切换

  master挂掉后可以指定一台slave充当master继续保证服务运行。

• 负载均衡

  开发中可能会遇到锁表，导致暂时不能使用读的操作，使用主从复制，主库负责写，从库负责读，这样即使主库锁表，读从库也能保证业务的正常运行。

  调查发现一般读写的比例是10:1，所以需要多个slave。保证了系统的高可用。

• 数据备份

• 业务模块化

  可以一个业务模块读取slave，再针对不同的业务场景进行数据库的索引创建和根据业务选择mysql引擎，不同的slave可以根据不同需求设置不同的索引和存储引擎。

主从节点需要注意：

(1)主从服务器操作系统版本和位数一致；
(2) Master和Slave数据库的版本要一致；
(3) Master和Slave数据库中的数据要一致；
(4) Master开启二进制日志，Master和Slave的server_id在局域网内必须唯一；

复制的流程

• master将数据改变写到二进制日志(binary log)中，也就是配置文件login-bin 指定的文件。
• slave通过线程IO读取日志文件并写入到中继日志（relay log）
• slave重做中继日志中的事件，把中继日志的事件信息一条条的本地执行，完成数据在本地的存储，从而实现将改变反映到他自己的数据（数据重放）。

复制涉及到三个线程：

• 主节点binary log dump线程（IO线程）

  slave连接master时，master创建log dump线程，发送bin-log内容。在读取bin-log中的操作时，线程会给bin-log加锁。

• 从节点IO线程‘

  当从节点执行start slave命令之后，从节点创建一个IO线程来连接主节点，请求从主库中更新bin-log。IO线程收到主节点的binlog sump线程发来的更新之后，保存在本地的relay-log中。

• 从节点SQL线程

  读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据库的一致性。

复制类型

• 基于语句的复制 statement-base Replication(SBR)

  在master上执行的SQL语句，在slave上会执行相同的语句。Mysql默认采用基于语句的复制，效率比较高。一旦发现没法精准复制时，会自动选基于行的复制。

  优点是只需要记录修改数据的sql语句到binlog，减少binlog日志量，节约IO。

  缺点是语句很复杂的时候，slave执行消耗过多资源，而基于行复制的话，只会记录变更的行记录。

• 基于行的复制

  把改变的内容复制到slave，而不是把命令在slave执行一遍。

  优点：只会记录变更的行记录，哪怕一个语句很复杂，但是它最后只影响几条记录，那么行的复制，只会把影响到几条记录记录到binlog，降低slave重放日志时的资源消耗。

  缺点：日志庞大，不利于数据库的还原。

• 混合类型的复制

  默认采用基于语句的复制，当发现基于语句的复制无法精确的复制时，采用基于行的复制。

数据库三范式

• 第一范式(确保每列保持原子性)

  最基本的范式。如果数据库表中所有字段都是不可分解的原子值，说明满足第一范式。如地址，有时候需要访问地址中的省份部分，有时候访问城市部分，这时候将地址拆分成省份、城市、详细地址等多个部分进行存储，这样设计就满足了第一范式。

• 第二范式(确保表中的每列都和主键相关)

  在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

  

  例如这张表，就要拆成，订单信息表，订单项目表，商品信息表。

  

• 第三范式(确保每列都和主键直接相关，而不是间接相关)

  比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。