msyql QA

存储引擎

MySQL 支持多种存储引擎，常见的有 MyISAM、InnoDB、MEMORY 等。

• 大多数情况下，使用默认的 InnoDB 就对了，InnoDB 可以提供事务、行级锁、外键、B+ 树索引等能力。
• MyISAM 适合读更多的场景。
• MEMORY 适合临时表，数据量不大的情况。由于数据都存放在内存，所以速度非常快。

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

比较

• 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
• 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
• 外键：InnoDB 支持外键。
• 备份：InnoDB 支持在线热备份。
• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
• 其它特性：MyISAM 支持压缩表和空间数据索引。

MyISAM索引与InnoDB索引的区别？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

数据类型

整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

字符串

主要有 CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的。

VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

在进行存储和检索时，会保留 VARCHAR 末尾的空格，而会删除 CHAR 末尾的空格。

varchar与char的区别

char的特点

• char表示定长字符串，长度是固定的；
• 如果插入数据的长度小于char的固定长度时，则用空格填充；
• 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
• 对于char来说，最多能存放的字符个数为255，和编码无关

varchar的特点

• varchar表示可变长字符串，长度是可变的；
• 插入的数据是多长，就按照多长来存储；
• varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
• 对于varchar来说，最多能存放的字符个数为65532

总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。

时间和日期

MySQL 提供了两种相似的日期时间类型：DATETIME 和 TIMESTAMP。

相同点

1. 两个数据类型存储时间的表现格式一致。均为 YYYY-MM-DD HH:MM:SS
2. 两个数据类型都包含「日期」和「时间」部分。
3. 两个数据类型都可以存储微秒的小数秒（秒后 6 位小数秒）

DATETIME 和 TIMESTAMP 的区别

1. 日期范围：DATETIME 的日期范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999；TIMESTAMP 的时间范围是1970-01-01 00:00:01.000000 UTC 到 2038-01-09 03:14:07.999999 UTC
2. 存储空间：DATETIME 的存储空间为 8 字节；TIMESTAMP 的存储空间为 4 字节
3. 时区相关：DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区
4. 默认值：DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)

应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

null 别用

参考

1.count 数据丢失

2.distinct 数据丢失：当使用 count(distinct col1, col2) 查询时，如果其中一列为 NULL，那么即使另一列有不同的值，那么查询的结果也会将数据丢失

3.select 数据丢失： 如果某列存在 NULL 值时，如果执行非等于查询（<>/!=）会导致为 NULL 值的结果丢失。

4.导致空指针异常： 如果某列存在 NULL 值时，可能会导致 sum(column) 的返回结果为 NULL 而非 0，如果 sum 查询的结果为 NULL 就可以能会导致程序执行时空指针异常（NPE）

切分

水平切分

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

垂直切分

垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

Sharding 策略

• 哈希取模：hash(key) % N；
• 范围：可以是 ID 范围也可以是时间范围；
• 映射表：使用单独的一个数据库来存储映射关系。

Sharding 存在的问题

1. 事务问题

使用分布式事务来解决，比如 XA 接口。

2. 连接

可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。

3. ID 唯一性

• 使用全局唯一 ID（GUID）
• 为每个分片指定一个 ID 范围
• 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)

分库分表分区

参考

1. 分区：把一张表的数据分成多个区块，在逻辑上看最终只是一张表，但底层是由多个物理区块组成的。将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件。。分区表技术不是用于提升 MySQL 数据库的性能，而是方便数据的管理。 更多的是解决数据迁移和备份的问题。分区表的创建需要主键包含分区列。
2. 分表：把一张表按一定的规则分解成多个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。
3. 分库：把一个库拆成多个库，突破库级别的数据库操作I/O瓶颈。

分区 vs 分表： 分区已经能够在磁盘层面将一张表拆分成多个文件了，理论上前面提到的大表的问题都能得到有效解决。因为分区就是分表的数据库实现版本。在MySQL 5.1分区功能出现以前，要想解决超大表问题，只能采用分表操作，因为这类问题十分常见，MySQL才自带了一个分区功能，以达到相同的效果。所以你可以直接说分区就是分表的替代，分表是分区出现以前的做法。不过这不代表我们就没有必要学习分表了，相反，水平分表的功能或许可以用更加便捷的分区来替代，但是垂直分表的功能，分区却无法替代。

分表只能通过程序代码来实现，目前市面上有许多分表的框架。

在 MySQL 分区表中，唯一索引（UNIQUE KEY）必须包含分区键，否则 MySQL 无法保证唯一性约束。 MySQL 不支持在分区表中创建不包含分区键的唯一索引。

分表和分区的区别

1. 分区只是一张表中的数据和索引的存储位置发生改变，分表则是将一张表分成多张表，是真实的有多套表的配套文件
2. 分区没法突破数据库层面，不论怎么分区，这些分区都要在一个数据库下。而分表可以将子表分配在同一个库中，也可以分配在不同库中，突破数据库性能的限制。
3. 分区只能替代水平分表的功能，无法取代垂直分表的功能。

分区分表之外的分库作用

分区和分表可以把单表分到不同的硬盘上，但不能分配到不同服务器上。一台机器的性能是有限制的，用分库可以解决单台服务器性能不够，或者成本过高问题。

将一个库分成多个库，并在多个服务器上部署，就可以突破单服务器的性能瓶颈，这是分库必要性的最主要原因。

大表如何加字段

在业务快速迭代的开发场景中，频繁的数据库表结构变更是不可避免的事情，比如增加索引和字段。如果使用 MySQL 的 alter 语句，MySQL 首先会生成原始表的拷贝，在该临时表上进行 DDL 更新，完成后，删除原始表，rename 临时表。操作临时表期间 DDL session 会对整张表加上读锁，阻塞其它 session 的写操作；在最后的 rename 阶段，会对表加上写锁，阻塞其它所有操作。如果表很小，大小只有几十 MB，阻塞时间可以忽略；但若表数据很大，阻塞时间尝尝是应用无法忍受的。尤其在最后的 rename 阶段，涉及到删除原始表的操作，如果表很大，删除时间比较长，整张表的读写操作都是被阻塞的。

MySQL 5.6 开始引入了 Online DDL 支持，但是在 alter 过程的开始和结束还是会 block 所有的读写操作，并且不能覆盖所有的 DDL 种类。此外操作过程中不能控制速率，可能会引起较大的读写延迟；如果中途中断的话，也会有较长时间的回滚操作。另外在主从环境中，MySQL Online DDL 必须操作完 mater 后才能开始对 slave 的 DDL。

option1: 基于数据库的触发器trigger保持增量同步

所以在生产环境中经常会引入第三方的 Online Schema Migration 工具。比较出名的是 Percona 的 pt-online-schema-change 和 Facebook 的 Facebook OSC。他们都是基于数据库的 trigger 来实现 online schema migration。以 pt-online-schema-change 为例，DDL 过程包含以下步骤

• 创建一个与原表结构相同的临时表，在临时表上执行 DDL 操作
• 创建三个 triggers，分别将原表的 UPDATE、INSERT 和 DELETE 操作同步到临时表上
• 将原表的数据根据主键顺序分批次 copy 至临时表
• 用临时表替换原表，其中包括删除 trigger 的操作。

基于 trigger 的 migration 工具简单粗暴，但是有以下几个缺点：

• DDL 期间，每个针对原表的更新操作都会因为要执行 trigger 而增加额外的写操作，所以原表的更新操作都会受影响
• 基于 trigger 的写操作也会参与临时表的 lock 竞争，比如获取 auto_increment 的值
• 不能方便的暂停，因为基于 trigger 的方式，暂停就意味着数据丢失
• Trigger 只能在 master 机器上建立，不能方便的在 slave 上进行测试

option2: 基于binlog保持增量同步

gh-ost 的一大卖点就是 triggerless，它通过监听主从同步的 binlog 实现即时变更数据的同步。在默认模式下，gh-ost 通过下面步骤完成 online DDL。

• gh-ost 连接 MySQL server（可以是主，也可以是从），获取数据库的主从拓扑信息和作必要的验证
• 建立 ghost table（也就是 DDL 后的原表）和统计表（用来统计 DDL 状态）
• 从原表同步数据至 ghost table
• 通过监听 binlog，同步变更至 ghost table
• 完成同步后，进行表切换

相较于它方式，gh-ost 只需要拷贝原表数据和重播 binlog，原表的读写不受影响。整个migration 过程可以随时暂停（MySQL online DDL 和基于 trigger 的工具都是不支持的），因为只需要记录 binlong 的执行位置即可。gh-ost 还提供充分的流量监控和控制方式，并且支持在 slave 上进行测试。

QA

大表数据查询，怎么优化

1. 优化shema、sql语句+索引；

2. 第二加缓存，memcached, redis；

3. 主从复制，读写分离；

4. 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；

5. 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

MySQL主从复制工作原理

• 在主库上把数据更高记录到二进制日志
• 从库将主库的日志复制到自己的中继日志
• 从库读取中继日志的事件，将其重放到从库数据中

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

主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；

从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；

从：sql执行线程——执行relay log中的语句；

UPDATE 语句会锁定与查询条件和更新列相关的索引。

• UPDATE 语句会锁定与 WHERE 子句中使用的索引相关的条目。如果 WHERE 条件包含索引列，MySQL 会锁定这些索引条目以防止并发修改。

• 如果在 SET 子句中修改了某个有索引的列，MySQL 会锁定与该列相关的索引，特别是如果该列的值发生变化时，索引需要更新。

• UPDATE 语句通常会锁定行级索引。对于每个符合 WHERE 条件的行，MySQL 会在索引中锁定该行的相关条目。在某些情况下，可能会涉及间隙锁，尤其是在范围查询中（如 BETWEEN、>、< 等），以防止插入操作。

• 非where, set涉及的索引不会被锁定。主键索引一定会锁上。

货币存储

货币在数据库中 MySQL 常用 Decimal 和 Numeric 类型表示，这两种类型被 MySQL 实现为同样的类型。他们被用于保存与货币有关的数据。

例如 salary DECIMAL(9,2)，9(precision)代表将被用于存储值的总的小数位数，而 2(scale)代表将被用于存储小数点后的位数。存储在 salary 列中的值的范围是从-9999999.99 到 9999999.99。

DECIMAL 和 NUMERIC 值作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度。

之所以不使用 float 或者 double 的原因：因为 float 和 double 是以二进制存储的，所以有一定的误差。