一、事务

1.什么是事务？

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。MySQL 默认采用自动提交模式。也就是说，如果不显式使用 START TRANSACTION 语句来开始一个事务，那么每个查询都会被当做一个事务自动提交。事务简单来说：一个Session中所进行所有的操作，要么同时成功，要么同时失败。

2.ACID

  • 原子性（Atomicity）
    原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

  • 一致性（Consistency）
    事务前后数据的完整性必须保持一致。

  • 隔离性（Isolation）
    事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

  • 持久性（Durability）

    持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

3.事务隔离级别

数据库定义了4个隔离级别：

  1. 序列化：Serializable【可避免脏读，不可重复读，虚读】
  2. 可重复读：Repeatable read【可避免脏读，不可重复读】
  3. 已提交读：Read committed【可避免脏读】
  4. 未提交读：Read uncommitted【级别最低，什么都避免不了】
  • 分别对应Connection类中的4个常量
    • TRANSACTION_READ_UNCOMMITTED
    • TRANSACTION_READ_COMMITTED
    • TRANSACTION_REPEATABLE_READ
    • TRANSACTION_SERIALIZABLE
  • 脏读：一个事务读取到另外一个事务未提交的数据。
  • 不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改。
  • 虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
  • 简单总结：脏读是不可容忍的，不可重复读和虚读在一定的情况下是可以的【做统计的肯定就不行】

二、范式

数据库中的范式指的是数据库的设计规范，范式具有包含关系。一个数据库设计如果符合第二范式，一定也符合第一范式。如果符合第三范式，一定也符合第二范式…范式逐级加严，一般数据库只需满足 3NF 即可。

1.第一范式：属性不可分

当关系模式 R 的所有属性都不能在分解为更基本的数据单位时，称 R 是满足第一范式的，简记为 1NF。

1NF 是针对于数据表的列的规范，即数据表的每一列都是不可分割的原子数据项，而不能是数组，集合，记录等非原子数据项，说白了就是，不能把好几列的数据合在一起，且每一列的数据都是不可分割的。

2.第二范式：每个非主属性完全函数依赖于键码

如果关系模式 R 满足第一范式，并且 R 的所有非主属性都完全依赖于 R 的每一个候选关键属性，称 R 满足第二范式，简记为 2NF。

2NF 基于第一范式，非码属性必须完全依赖码，即非主键数据必须依赖主键数据。“码”(主键)是数据表用来唯一区分实例或记录的数据项，若没有，可人为添加。对于第一范式针对列来说，第二范式则是针对于行的规范。第二范式需要确保数据库表中每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

3.第三范式：非主属性不传递函数依赖于键码

设 R 是一个满足第一范式条件的关系模式，X 是 R 的任意属性集，如果 X 非传递依赖于 R 的任意一个候选关键字，称 R 满足第三范式，简记为 3NF。

3NF 需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

三、存储引擎

这里主要介绍两个搜索引擎：InnoDB 和 MyISAM

1.InnoDB

MySQL 5.5 及之后版本的默认存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

特性

  • InnoDB为事务性存储引擎
  • 完全支持事物的 ACID 特性
  • Redo log （实现事务的持久性） 和 Undo log（为了实现事务的原子性，存储未完成事务log，用于回滚）
  • InnoDB支持行级锁
  • 行级锁可以最大程度的支持并发
  • 行级锁是由存储引擎层实现的
  • 实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ 间隙锁（Next-Key Locking）防止幻影读
  • 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升

应用场景

  • 可靠性要求比较高，或者要求事务
  • 表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。

2.MyISAM

MySQL 5.5 版本之前的默认存储引擎，在 5.0 以前最大表存储空间最大 4G，5.0 以后最大 256TB。

MyISAM 存储引擎由 .myd（数据）和 .myi（索引文件）组成，.frm文件存储表结构（所有存储引擎都有）

特性

  • 并发性和锁级别 （对于读写混合的操作不好，为表级锁，写入和读互斥）
  • 表损坏修复
  • MyISAM 表支持的索引类型（全文索引）
  • MyISAM 支持表压缩（压缩后，此表为只读，不可以写入。使用 myisampack 压缩）

应用场景

  • 没有事务
  • 只读类应用（插入不频繁，查询非常频繁）
  • 空间类应用（唯一支持空间函数的引擎）
  • 做很多 count 的计算

3.InnoDB 和 MyISAM 区别

对比项 MyISAM InnoDB
主外键 不支持 支持
事务 不支持 支持
行表锁 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作
缓存 只缓存索引，不缓存真实数据 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间 小 大
关注点 性能 事务
其他 MyISAM 表是保存成文件的形式，在跨平台的数据转移中使用 MyISAM 存储会省去不少的麻烦。MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。 InnoDB 表比 MyISAM 表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。InnoDB 支持在线热备份。
应用场景 MyISAM 管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的 SELECT 查询，那么 MyISAM 是更好的选择。 InnoDB 用于事务处理应用程序，具有众多特性，包括 ACID 事务支持。如果应用中需要执行大量的 INSERT 或 UPDATE 操作，则应该使用 InnoDB，这样可以提高多用户并发操作的性能。

四、数据类型

1.整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

2.浮点型

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

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

3.字符串

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

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

VARCHAR 会保留字符串末尾的空格，而 CHAR 会删除。

4.时间和日期

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

DATATIME

能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。

它与时区无关。

默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATATIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

TIMESTAMP

和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，使用 4 个字节，只能表示从 1970 年 到 2038 年。

它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。

MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

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

五、索引

1.索引使用场景

索引能够轻易将查询性能提升几个数量级。

  1. 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效。
  2. 对于中到大型的表，索引就非常有效。
  3. 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

2.B+ 树

B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。

B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。在 B+ Tree 中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，且不为 null，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

主要特点：内节点只存指针，叶子节点只存数据，有序

3.为什么选 B+ 树？

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B Tree 作为索引结构，主要有以下两个原因：

  1. 更少的检索次数

    平衡树检索数据的时间复杂度等于树高 h，而树高大致为 O(h)=O(logdN)，其中 d 为每个节点的出度。

    红黑树的出度为 2，而 B Tree 的出度一般都非常大。红黑树的树高 h 很明显比 B Tree 大非常多，因此检索的次数也就更多。

    B+Tree 相比于 B-Tree 更适合外存索引，因为 B+Tree 内节点去掉了 data 域，因此可以拥有更大的出度，检索效率会更高。

  2. 利用磁盘预读特性

    为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。

    操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

4.索引分类

B+ 树索引
  • B+Tree 索引是大多数 MySQL 存储引擎的默认索引类型。
  • 因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组。
  • 可以指定多个列作为索引列，多个索引列共同组成键。
  • 适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。

主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

哈希索引

InnoDB 引擎有一个特殊的功能叫 “自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

哈希索引能以 O(1) 时间进行查找，但是失去了有序性，它具有以下限制：

  • 无法用于排序与分组。
  • 只支持精确查找，无法用于部分查找和范围查找。
全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

空间数据索引（R-Tree）

MyISAM 存储引擎支持空间数据索引，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

5.索引种类

  • 聚簇索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

    • 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

      一种唯一性索引，必须指定为 primary key。一个表可以有多个唯一索引，但只能有一个主键。主键一定是唯一，唯一不一定是主键。且主键索引可以被其他表引用当外键，唯一索引不可以。

  • 非聚簇索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

    • 普通索引：仅加速查询
    • 唯一索引：加速查询 + 列值唯一（可以有null）
    • 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
    • 全文索引：对文本的内容进行分词，进行搜索，可以在char、varchar或text类型的列上创建。

6.联合索引

什么是联合索引？

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。对于复合索引：Mysql 从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

例如索引是 key index (a,b,c)，可以支持[a]、[a,b]、[a,b,c] 3种组合进行查找，但不支 [b,c] 进行查找。当最左侧字段是常量引用时，索引就十分有效。

命名规则
  • 需要加索引的字段，要在 where 条件中
  • 数据量少的字段不需要加索引
  • 如果 where 条件中是OR关系，加索引不起作用
  • 符合最左原则

7.索引的特点

  • 可以加快数据库的检索速度
  • 降低数据库插入、修改、删除等维护的速度
  • 只能创建在表上，不能创建到视图上
  • 既可以直接创建又可以间接创建
  • 可以在优化隐藏中使用索引
  • 使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引

优点

  • 创建唯一性索引，保证数据库表中每一行数据的唯一性
  • 大大加快数据的检索速度，这是创建索引的最主要的原因
  • 加速数据库表之间的连接，特别是在实现数据的参考完整性方面特别有意义
  • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
  • 通过使用索引，可以在查询中使用优化隐藏器，提高系统的性能

缺点

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
  • 索引需要占用物理空间，除了数据表占用数据空间之外，每一个索引还要占一定的物理空间，如果建立聚簇索引，那么需要的空间就会更大
  • 当对表中的数据进行增加、删除和修改的时候，索引也需要维护，降低数据维护的速度

8.索引失效的情况

  • 如果MySQL估计使用全表扫秒比使用索引快，则不使用索引。

    例：如果列 key 均匀分布在 1 和 100 之间，下面的查询使用索引就不是很好：

    1
    select * from table_name where key>1 and key<90;

  • 如果条件中有 or，即使其中有条件带索引也不会使用。

    例：如果在 key1 上有索引而在 key2 上没有索引，则该查询也不会走索引：

    1
    select * from table_name where key1='a' or key2='b';

  • 复合索引，如果索引列不是复合索引的第一部分，则不使用索引。（即不符合最左前缀）

    例：复合索引为(key1,key2)，则下列查询将不会使用索引：

    1
    select * from table_name where key2='b';

  • 如果 like 是以 % 开始的，则该列上的索引不会被使用。

    例：下列查询即使 key1 上存在索引，也不会被使用如果列类型是字符串，那一定要在条件中使用引号引起来，否则不会使用索引。

    1
    select * from table_name where key1 like '%a';

  • 如果列为字符串，则 where 条件中必须将字符常量值加引号，否则即使该列上存在索引，也不会被使用。

    例：如果key1列保存的是字符串，即使key1上有索引，也不会被使用。

    1
    select * from table_name where key1=1;

  • 如果使用 MEMORY/HEAP 表，并且 where 条件中不使用 “=” 进行索引列，那么不会用到索引，head 表只有在 “=” 的条件下才会使用索引。

9.在什么情况下适合建立索引？

  • 为经常出现在关键字 order by、group by、distinct 后面的字段，建立索引。
  • 在 union 等集合操作的结果集字段上，建立索引。其建立索引的目的同上。
  • 为经常用作查询选择 where 后的字段，建立索引。
  • 在经常用作表连接 join 的属性上，建立索引。
  • 考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。

10.主键、外键和索引的区别

定义 作用 个数
主键 唯一标识一条记录，不能有重复的，不允许为空 用来保证数据完整性 主键只能有一个
外键 表的外键是另一表的主键，外键可以有重复的，可以是空值 用来和其他表建立联系用的 一个表可以有多个外键
索引 该字段没有重复值，但可以有一个空值 是提高查询排序的速度 一个表可以有多个惟一索引

11. SQL 约束有哪几种？

  • NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
  • UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
  • PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
  • FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
  • CHECK: 用于控制字段的值范围。

六、查询性能优化

1.用 Explain 进行分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

比较重要的字段有：

  • select_type : 查询类型，有简单查询、联合查询、子查询等
  • key : 使用的索引
  • rows : 扫描的行数

2.优化数据访问

  • 减少请求的数据量
    • 只返回必要的列：最好不要使用 SELECT * 语句。
    • 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
    • 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。
  • 减少服务器端扫描的行数
    • 最有效的方式是使用索引来覆盖查询。

3.重构查询方式

  • 切分大查询

    一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

    1
    DELEFT FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
    1
    2
    3
    4
    5
    rows_affected = 0
    do {
        rows_affected = do_query(
        "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
    } while rows_affected > 0

  • 分解大连接查询

    将一个大连接查询（JOIN）分解成对每一个表进行一次单表查询，然后将结果在应用程序中进行关联，这样做的好处有：

    • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
    • 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
    • 减少锁竞争；
    • 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可扩展。
    • 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。
    1
    2
    3
    4
    SELECT * FROM tab
    JOIN tag_post ON tag_post.tag_id=tag.id
    JOIN post ON tag_post.post_id=post.id
    WHERE tag.tag='mysql';
    1
    2
    3
    SELECT * FROM tag WHERE tag='mysql';
    SELECT * FROM tag_post WHERE tag_id=1234;
    SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

七、分库分表

简单来说，数据的切分就是通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）中，以达到分散单台设备负载的效果，即分库分表。

数据的切分根据其切分规则的类型，可以分为如下两种切分模式。

  • 垂直（纵向）切分：把单一的表拆分成多个表，并分散到不同的数据库（主机）上。
  • 水平（横向）切分：根据表中数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上。

1.水平切分

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

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

Sharding 优点
  • 单库单表的数据保持在一定的量级，有助于性能的提高
  • 切分的表的结构相同，应用层改造较少，只需要增加路由规则即可
  • 提高了系统的稳定性和负载能力
Sharding 缺点
  • 切分后，数据是分散的，很难利用数据库的 join 操作，跨库 join 性能较差
  • 拆分规则难以抽象
  • 分片事务的一致性难以解决
  • 数据扩容的难度和维护量极大
Sharding 策略
  • 哈希取模：hash(key) % NUM_DB
    • 比如按照 userId mod 64，将数据分布在64个服务器上
  • 范围：可以是 ID 范围也可以是时间范围
    • 比如每台服务器计划存放一个亿的数据,先将数据写入服务器 A.一旦服务器 A 写满,则将数据写入服务器 B,以此类推. 这种方式的好处是扩展方便.数据在各个服务器上分布均匀
  • 映射表：使用单独的一个数据库来存储映射关系
Sharding 存在的问题及解决方案
  • 事务问题：使用分布式事务来解决，比如 XA 接口。
  • JOIN：可以将原来的 JOIN 查询分解成多个单表查询，然后在用户程序中进行 JOIN。
  • ID 唯一性
    • 使用全局唯一 ID：GUID。
    • 为每个分片指定一个 ID 范围。
    • 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)。

2.垂直切分

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

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

垂直切分的优点
  • 拆分后业务清晰，拆分规则明确
  • 系统之间进行整合或扩展很容易
  • 按照成本、应用的等级、应用的类型等将表放到不同的机器上，便于管理
  • 便于实现动静分离、冷热分离的数据库表的设计模式
  • 数据维护简单
垂直切分的缺点
  • 部分业务表无法关联（Join），只能通过接口方式解决，提高了系统的复杂度
  • 受每种业务的不同限制，存在单库性能瓶颈，不易进行数据扩展和提升性能
  • 事务处理复杂

3.垂直切分和水平切分的共同点

  • 存在分布式事务的问题
  • 存在跨节点 Join 的问题
  • 存在跨节点合并排序、分页的问题
  • 存在多数据源管理的问题

八、主从复制与读写分离

主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

  • binlog 线程 ：负责将主服务器上的数据更改写入二进制文件（binlog）中。
  • I/O 线程 ：负责从主服务器上读取二进制日志文件，并写入从服务器的中继日志中。
  • SQL 线程 ：负责读取中继日志并重放其中的 SQL 语句。

读写分离

主服务器用来处理写操作以及实时性要求比较高的读操作，而从服务器用来处理读操作。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

MySQL 读写分离能提高性能的原因在于：

  • 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
  • 从服务器可以配置 MyISAM 引擎，提升查询性能以及节约系统开销；
  • 增加冗余，提高可用性。

九、锁

MySQL/InnoDB 的加锁，一直是一个面试中常问的话题。例如，数据库如果有高并发请求，如何保证数据完整性？产生死锁问题如何排查并解决？在工作过程中，也会经常用到，乐观锁，排它锁等。

注：MySQL 是一个支持插件式存储引擎的数据库系统。下面的所有介绍，都是基于 InnoDB 存储引擎，其他引擎的表现，会有较大的区别。

版本查看

1
select version();

存储引擎查看

MySQL 给开发者提供了查询存储引擎的功能，我这里使用的是 MySQL5.6.4，可以使用：

1
SHOW ENGINES

1.乐观锁

假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将 version 字段的值一同读出，数据每更新一次，对此 version 值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的 version 值进行比对，如果数据库表当前版本号与第一次取出来的 version 值相等，则予以更新，否则认为是过期数据。例：

a. 数据库表设计三个字段，分别是 id,value,version

1
select id,value,version from TABLE where id=#{id}

b. 每次更新表中的value字段时，为了防止发生冲突，需要这样操作

1
2
3
update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version};

2.悲观锁

假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟 Java 中的 synchronized 很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。以排它锁为例：

要使用悲观锁，我们必须关闭 mysql 数据库的自动提交属性，因为 MySQL 默认使用 autocommit 模式，也就是说，当你执行一个更新操作后，MySQL 会立刻将结果进行提交。我们可以使用命令设置 MySQL 为非 autocommit 模式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
set autocommit=0;
# 设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

# 1. 开始事务 (三者选一就可以)
begin; / begin work; / start transaction;

# 2. 查询表信息
select status from TABLE where id=1 for update;

# 3. 插入一条数据
insert into TABLE (id,value) values (2,2);

# 4. 修改数据为
update TABLE set value=2 where id=1;

# 5. 提交事务
commit;/commit work;

3.共享锁

共享锁又称读锁（read lock），是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务 T 对数据 A 加上共享锁后，则其他事务只能对 A 再加共享锁，不能加排他锁。获得共享锁的事务只能读数据，不能修改数据。

打开第一个查询窗口

1
2
3
4
#三者选一就可以
begin; / begin work; / start transaction;

SELECT * from TABLE where id = 1  lock in share mode;

然后在另一个查询窗口中，对 id 为 1 的数据进行更新

1
update TABLE set name="www.souyunku.com" where id =1;

此时，操作界面进入了卡顿状态，过了超时间，提示错误信息

如果在超时前，执行 commit，此更新语句就会成功。

1
2
[SQL]update  test_one set name="www.souyunku.com" where id =1;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

加上共享锁后，也提示错误信息

1
2
3
update test_one set name="www.souyunku.com" where id =1 lock in share mode;
[SQL]update  test_one set name="www.souyunku.com" where id =1 lock in share mode;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1

在查询语句后面增加 lock in share mode，MySQL 会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

加上共享锁后，对于 update,insert,delete 语句会自动加排它锁。

4.排它锁

排他锁 exclusive lock（也叫 writer lock）又称写锁。

排它锁是悲观锁的一种实现，在上面悲观锁也介绍过。

若事务 1 对数据对象 A 加上 X 锁，事务 1 可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到事物 1 释放 A 上的锁。这保证了其他事务在事物 1 释放 A 上的锁之前不能再读取和修改 A。排它锁会阻塞所有的排它锁和共享锁。

读取为什么要加读锁呢：防止数据在被读取的时候被别的线程加上写锁。

使用方式：在需要执行的语句后面加上 for update 就可以了。

5.行锁

行锁又分共享锁和排他锁，由字面意思理解，就是给某一行加上锁，也就是一条记录加上锁。

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

共享锁：

名词解释：共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前其他事务只能再加共享锁，除此之外其他任何类型的锁都不能再加了。

1
2
#结果集的数据都会加共享锁
SELECT * from TABLE where id = "1"  lock in share mode;

排他锁：

名词解释：若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取，不能进行写操作，需等待其释放。

1
select status from TABLE where id=1 for update;

可以参考之前演示的共享锁，排它锁语句

由于对于表中 id 字段为主键，就也相当于索引。执行加锁时，会将 id 这个索引为 1 的记录加上锁，那么这个锁就是行锁。

6.表锁

如何加表锁

innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的。

Innodb中的行锁与表锁

前面提到过，在 Innodb 引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？ 只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

在实际应用中，要特别注意 InnoDB 行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

7. 死锁

死锁（Deadlock） 所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

解除正在死锁的状态有两种方法：

第一种：

查询是否锁表

1
show OPEN TABLES where In_use > 0;

查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

1
show processlist

杀死进程id（就是上面命令的id列）

1
kill id

第二种：

查看当前的事务

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查看当前锁定的事务

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看当前等锁的事务

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

杀死进程

1
kill 进程ID

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。 产生死锁的四个必要条件：

  1. 互斥条件：一个资源每次只能被一个进程使用。
  2. 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
  3. 不剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺。
  4. 循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

下列方法有助于最大限度地降低死锁：

  1. 按同一顺序访问对象
  2. 避免事务中的用户交互
  3. 保持事务简短并在一个批处理中
  4. 使用低隔离级别
  5. 使用绑定连接