编程
数据库
Mysql

Mysql

第一章 数据库基础与架构

并发问题：读写锁（共享锁、排他锁）、死锁及其处理（超时解锁或检测后事务回滚）

事务：原子性、一致性、隔离性、持久性（ACID 特性）

锁粒度：表锁、行级锁（最少行级排他锁事务回滚）

隔离级别：未提交读、提交读、可重复读、可串行化（脏读、幻读问题）

Mysql 混合引擎事务：Mysql 中事务由引擎实现，混合引擎可能会导致事务不可靠。

多版本并发控制（MVCC）：行级锁不是实现事务的最佳选择，大多使用 MVCC 快照（快照保留着所有改动的记录，这是事务提交或回滚的基础）。InnoDB 引擎通过递增系统版本号实现快照事务。

架构

第一层 连接处理、授权认证、安全等；
第二层 Mysql 服务器，负责解析、分析、优化、缓存、以及所有内置函数、存储过程、触发器、视图等；
第三层（底层） 存储引擎，例如 InnoDB、MyISAM。

InnoDB 概览

引擎非常复杂。

InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现的，行的创建版本号和行的删除版本号。系统版本号（事务的 ID）。

  1. 通过 MVCC 实现事务、通过间隙锁策略防止幻读。
  2. 表基于聚簇索引建立，对主键查询有很高的性能，但是二级索引必须包含主键列，如果主键列很大，其它所有索引都会很大。
  3. 可预测性预读；
  4. 有插入缓冲区；
  5. 支持热备份。

隐式加锁

  1. InnoDB 自动加意向锁、用户无法操作，加锁前必须取得意向。
  2. 对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁（X)；
  3. 对于普通 SELECT 语句，InnoDB 不会加任何锁，且不与锁互斥；
  4. 无索引或索引失效，行锁会升级为表锁；
  5. 范围更新会对涉及数据集加行锁，产生间隙锁；

显示加锁

  1. 共享锁（读锁、S 锁）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
  2. 排他锁（写锁、X 锁) ：SELECT * FROM table_name WHERE … FOR UPDATE
  3. 乐观锁：加版本号自行实现；
  4. 悲观锁：即排他锁；
  5. 行锁：即共享锁、排他锁；
  6. 表锁：LOCK TALBES 由 Mysql 服务器层管理，设置不对 InnoDB 无法识别；

第四章 Schema 与数据类型优化

  1. 更小的通常更好。
  2. 整型比字符操作代价更低，值比较时字符排序更复杂。
  3. 尽量避免 NULL，索引、索引统计、值比较更复杂，且占空间。
  4. 整数类型：有符号与无符号使用相同的存储空间、具有相同性能，按需选择。然而整数计算一般使用 64 位 BIGINT、即使在 32 位系统。
  5. 实数类型：FLOAT 和 DOUBLE 使用标准浮点运算进行近似计算，DECIMAL 支持精确计算，但是空间、计算性能不如浮点。一般将最小单位换算为整数。
  6. 字符串类型：4.1 版本开始，列可自定义字符集和排序规则，很大程度上会影响性能。VARCHAR 每行需要 1-2 个额外字节记录长度，如果表 ROW_FORMAT=FIXED 会定长存储。变长在更新时产生碎片，过长 MyISAM 拆片存储、InnoDB 分裂页且超长存为 BLOB。
  7. 枚举类型：枚举非常紧凑，保存为整数映射，占 1-2 字节，按整数排序，适合代替字符串类型。对于可能改变的字符串，或者用于和 VARCHAR 关联，不应替换为枚举。
  8. 时间类型：TIMESTAMP 占 4 字节，DATETIME 占 8 字节，都是整数存储。TIMESTAMP 只使用 DATETIME 一半的空间，且忽略时区，但是时间范围小、只能表示 1970-2038 年。
  9. 位数据类型：Mysql 把 BIT 当字符串类型，MyISAM 打包存储所有 BIT 列，然而 InnoDB 等引擎使用足够的最小整数类型、不能节省空间。Mysql 会结合场景，返回二进制 ASCII 码字符，或者十进制的计算结果，应该谨慎使用 BIT 类型。
  10. 标识符选择：整数类型是最好的选择，避免使用字符串类型，尤其是 MyISAM 会压缩字符串、查询很慢。注意随机字符串。如 md5、sha1 散列会导致磁盘随机访问、聚簇索引碎片、缓存失效。UUID 有一定的顺序，可通过 UNHEX 转二进制 BINARY 存储。
  11. 特殊类型数据：例如 IPv4 不应使用字符串类型，使用 32 位无符号整数类型。IPv4 的小数点只是为了阅读容易，可通过 Mysql 的 INET_ATON、INET_NTOA()转换。
  12. Mysql 的 schema 设计陷阱：因服务器+引擎 API 的通信架构，MyISAM 变长行和 InnoDB 行总是需要转换，代价非常高，且取决于列的数量。Mysql 限制关联最多 61 张表，经验建议在 12 以内。枚举避免值过多，可以用整数作外健关联到字典表或查找表。
  13. 范式和反范式：字段原子性、主键记录唯一性、去冗余；适当冗余可以避免关联查询，并且可用触发器更新缓存值。缓存表和汇总表。反范式操作适当牺牲了写操作性能，但是显著提升了读操作性能。

第五章 创建高性能的索引

  1. 数据量越大，索引对性能影响越大。
  2. MyISAM 索引引用行的物理位置，InnoDB 索引引用主键。
  3. 索引可用于查询中的 ORDER BY 操作，对多值排序，按 Schema 定义索引时列的顺序。
  4. 多列联合索引循序很重要，Mysql 只能高效地使用索引的最左前缀列，将选择性最高的列放索引最前列。B-Tree 如果不是按照索引的最左列开始查找，则无法使用索引，可以建立列相同但是顺序不同的索引。
  5. InnoDB 对 B-Tree 索引支持“自适应哈希索引”。
  6. 哈希索引不适用于有大量哈希冲突的场景，适用于被关联的查找表。
  7. 自定义伪哈希索引，新建纯数字 CRC32 哈希列，对哈希列加 B-Tree 索引，查询时搜哈希列 and 被索引列。
  8. 对于 TB 级数据，可以建立元数据表，记录用户信息在哪张表。
  9. 对有索引的 where 查询条件列做运算，会导致 Mysql 不使用索引。
  10. 数据太长可选取选择性高的列，取前缀做前缀索引，优点是索引小，缺点是 ORDER、GROUP 无法使用索引。
  11. 聚簇索引将数据行和相邻的键值紧凑地存储在一起。InnoDB 通过主键聚集数据，没有主键会隐式定义主键。聚簇索引查询快，但是插入更新可能需要页分裂，而且二级索引访问需要两次查找。
  12. 覆盖索引是指索引包含所有需要查询的字段值，由于不回库取行，效率很高。B-Tree 支持“只访问索引的查询”，即覆盖索引。
  13. Mysql 索引为何用 B+树：层级少利于减少磁盘 IO，叶子页双向链表、更适合范围查询。

重点：B+tree、hash、选择性。

主键索引
唯一索引
普通索引
多列索引(联合索引)
覆盖索引
前缀索引
前缀压缩索引(仅 MyISAM)
聚簇索引

问：非聚簇索引?

问：索引都是基于聚簇索引？

问：表建立联合索引(a,b,c)，查询条件 a=1,b>2,c=3，此时为什么 c 条件的索引会失效？

答：简单点来说，该组合索引是一个以 a 字段排序而 b 与 c 相对有序的 B+树，引擎可以通过二分查找定位到 a=1 的数据，b 在 a=1 确定得情况下是有序的（所以 b 的有序是相对的），依然可以通过二分查找取出所有 b 大于 2 的数据，但这些数据的 b 字段可能有很多个不同的值，所以 c 字段是无序的，无法用二分查找来查询 c=3 的数据，故 c 用不到索引。

分库分表与分区表

分区表和分库分表都是常见的数据库分片技术，可以提高数据库的性能和可扩展性。它们各自有优缺点，可以根据实际情况来选择。

分区表的优点：

  1. 查询效率高：分区表可以将数据按照分区键进行分割，可以在查询时只扫描需要的分区，提高查询效率。
  2. 管理成本低：分区表只需要一个表，管理成本相对较低。
  3. 数据一致性：分区表的分区都在同一个物理表中，数据一致性相对较好。
  4. 分布式数据库可以将分区落在不同磁盘上，大幅提升性能。

分区表的缺点：

  1. 分区键的选择比较重要，如果分区键选择不当，可能导致数据分布不均匀，影响查询效率。
  2. 单个分区的数据量仍然可能很大，不适合单个节点承载大量数据的场景。

分库分表的优点：

  1. 可扩展性好：分库分表可以将数据分散到多个节点上，可以提高数据库的可扩展性和负载均衡性。
  2. 单个节点的数据量相对较小，可以提高查询效率。

分库分表的缺点：

  1. 数据一致性难以维护：分库分表的节点之间需要进行数据同步和数据一致性维护，如果同步不及时或者出现问题，可能会影响查询结果的准确性。
  2. 管理成本较高：分库分表需要多个节点进行管理和维护，管理成本相对较高。
  3. 查询效率可能较低：当需要跨节点进行查询时，需要进行跨节点查询和数据合并，可能会影响查询效率。

总之，如果数据量较小、查询需求比较集中、对数据一致性要求较高，则使用分区表可能更合适；如果数据量较大、查询需求比较分散、对数据一致性要求不那么高，则使用分库分表可能更合适。

为什么 mysql 分区表不常用

为什么 mysql 表分区不是很常用？ - 知乎

在单机 mysql 实例（不是分布式数据库）使用分区表的原因，主要是因为单表数据量太大导致索引过大，从而降低了查询性能。

考虑一个巨大的单表并且主键字段较大的最坏情形，我们来计算一下主表 b+树的高度。

例子 1。 比如单表 100 亿行，每行数据平均占用 1000 字节的存储空间，16KB 的 page size，那么主表页节点就要占用约 10TB 空间，约 7 亿个页面。假设主键占用空间较大导致内节点每个索引行平均占据 256 字节，于是每个内节点页面存放 64 个索引行。那么主表 b+树的高度就是

1 + ceiling(lg(64, 700000000)) = 6

假设主键索引取的非常蠢，导致内节点每个索引行占据 512 字节，那么主表 b+树的高度是

1 + ceiling(lg(32, 700000000)) = 7

例子 2。 假设上述数据只有 1 千万行，那么用相同的方法计算可得主表 b+树的高度分别是 4 （256 字节的主键索引）和 5（512 字节的主键索引） — 高度只相差 2。

假设例子 1 的主表真的做了 1000 个分区那么每个分区表就是例子 2 的主表，那么通过分区，可以让每次树搜索减少 2 次页面获取（极大概率从 buffer pool 获取，否则系统性能无法实用）。 这个差别确实会导致例子 2 查询性能有所提升，但是区别其实并不大。

另外，分区后另一个性能优势是分散了根节点的访问，从而提升并发性能。不过要知道 b+树做遍历并不会持有内节点页面的事务锁，只需要短暂持有根结点页面的 read latch，所以除了页面分裂（低频操作）以外的遍历，是可以并发执行的。

综上，我认为这单机做表分区获得的性能提升的理论上限很有限，估计也就 10%以内，随数据和查询特征略有波动，随数据库系统的实现也有不同。感兴趣的同学可以使用 postgresql 或者 mysql 做一个实验对比一下。在不同的数据库系统实现中，分区表的实际性能与单表相比，提升各不相同，甚至未必能提升，甚至会降低。

mysql 分区表的详情如下。

首先，如果很多表都做分区，会导致 mysql innodb 数据目录下文件数目非常多（比如 1000 个表分区会产生 2000 个文件），从而使操作系统的文件系统工作效率降低。并且由于 mysql 打开表文件的数目限制（该限制虽然可以手动修改但是也受限与操作系统可用资源量）从而导致打开的表反复被淘汰和重新打开，从而降低了所有查询的性能。

在 mysql5.7 的早期版本中，分区表的实现性能较差，与相同数据量的单表相比性能下降约 10%。后来在 mysql5.7.19 才做了优化，可以去http://bugs.mysql.com上面看一下这个 bug。但是即使这个 bug 修复之后，分区表仍然比相同数据量的单表有大约 5%的性能下降（8 个分区，100GB 数据量，sysbench oltp 测例）。

在上例中如果单表占据 10TB 空间，那么单个服务器节点的计算资源（内存，cpu，存储）恐怕已经无法支持业务运行了，所以大概率还是要做分库分表才行。而如果单表只有 1TB 以内的空间那么完全没必要做表分区。也就是说，在单节点 mysql 实例中做表分区并没有什么必要，也不会有明显的性能优势。

如果是做分库分表的话，那么通过分区表来实现分库分表是一些简单的分表中间件常用的方法，也比较有效。在昆仑分布式数据库中，我们在计算节点中实现分库分表，在存储节点中永远使用单表做存储，不使用分区表，就是基于上述 mysql 分区表性能降低的原因。

排序与分组

排序和分组的顺序，应该先分组再排序。这是因为分组操作会将数据按照分组关键字进行划分，然后再对每个分组进行排序。如果先进行排序操作，会导致数据的排列方式发生变化，进而导致分组结果错误。

三大范式

第一范式（1NF）：原子性，字段不可再分；
第二范式（2NF）：依赖性，在满足第一范式的基础上，所有非主键字段都依赖于主键；
第三范式（3NF）：传递性，在满足第二范式的基础上，非主键字段之间不存在传递依赖。

其它

utf8_general_ci case insensitive 大小写不敏感
utf8_general_cs case sensitive 大小写敏感

binlog，二进制，记录写入性操作，主要用于主从复制和数据恢复。
redolog，为了提升事务处理性能，先写日志、再写磁盘。
undolog，MVCC 失败回滚，记录相反的 sql 语句。

最后更新于
RabbitMQ