1.存储引擎

InnoDB和MyISAM区别


 InnoDB MyISAM
是否支持行级锁 支持，默认为行级锁 不支持
是否支持事务 支持 不支持
是否支持外键 支持 不支持
是否支持数据库异常崩溃后恢复 支持 不支持
是否支持MVCC 支持 不支持
  • 其它特性：MyISAM 支持压缩表和空间数据索引。

    2.事务

    事务：数据库事务可以保证多个对数据库的操作构成一个逻辑上的整体，这个整体操作遵循：要么都成功，要么都不成功。
    1. # 开启一个事务
    2. START TRANSACTION;
    3. # 多条 SQL 语句
    4. SQL1,SQL2...
    5. ## 提交事务
    6. COMMIT;

    2.1 事务特性（ACID）

  1. 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  2. 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
  3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

  4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

    2.2 数据事务的实现原理呢？

    我们这里以 MySQL 的 InnoDB 引擎为例来简单说一下。
    MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
    MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。
    保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

    2.3 并发事务引起的问题

  5. 脏读：一个事务对一个数据进行了更新，但没有提交到数据库，另一个事务读取到了这个数据，然后使用了这个数据。这个数据就是脏数据，依据“脏数据”所做的操作可能是不正确的。

  6. 丢失修改：A事务读取了数据，B事务也读取了这个数据，A事务对数据进行修改，B事务也对该数据进行修改。这样A事务的修改操作就被丢失了。(这类型问题可以通过给 SELECT 操作加上排他锁来解决)
  7. 不可重复读：在一个事务内多次读取同一数据。在这个事务没结束时，另一个事务也访问该数据。在第一个事务两次读取之间，由于第二个事务的修改导致第一个事务的两次读取的数据不一样。

  8. 幻读：在事务A读取几行数据，在这个事务没有结束时，另一个事务B插入了一些数据。然后A事务再次读取这些数据时，会发现多了一些原本不存在的记录，就好像幻觉一样，称为幻读。

    2.4 事务隔离级别

  9. READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

  10. READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  11. REPEATABLE-READ(可重复读)： 保证在同一个事务中多次读取同样数据的结果是一样的。可以阻止脏读和不可重复读，但幻读仍有可能发生。
  12. SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB存储引擎的默认支持的隔离级别是REPEATABLE-READ。

3.索引

优点

  1. 加快数据的检索速度
  2. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。

缺点

  1. 创建索引和维护索引需要耗费许多时间。
  2. 索引需要耗费一定的空间。

大多数情况下，索引查询都是比全表扫描快，但是数据库的数据量不大时，使用索引也不定能够带来很大的提升。

3.1 数据结构

  1. B+树索引
  2. Hash索引
    1. 缺点：Hash冲突。只支持精确查找，不支持顺序和范围查询。
  3. 全文索引
  4. 空间数据索引

Hash和B+树索引的区别

  1. hash进行等值查询更快，但无法进行范围查询。因为经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，故不能范围查询。同里，也不支持使用索引进行排序。
  2. hash不支持模糊查询以及多列索引的最左前缀匹配，因为hash函数的值不可预测，如AA和AB的算出的值没有相关性
  3. hash在任何时候都避免不了回表查询
  4. 虽然等值上查询效率高，但性能不稳定，因为某个键值大量重复存在时，产生hash碰撞，此时查询效率反而降低。

B树和B+树的区别

  • B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
  • B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

image.png
在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构。
MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

类型

  • 全文索引（fulltext）：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引。
  • 普通索引（normal）
  • 空间索引（spatial）
  • 唯一索引（unique）：索引列中的值必须是唯一的，但是允许为空值。
  • 主键索引：索引列中的值必须是唯一的，不允许有空值。
  • 前缀索引：在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

按照索引列数量分类

  • 单列索引
  • 组合索引：组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

MySQL - 图2
MySQL - 图3

3.2 优缺点

优点

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

  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

    缺点

  • 创建和维护索引需要耗费时间，这种时间随着数据量的增加而增加，这样就降低了数据的维护速度。

  • 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

    3.3 设计原则

  1. 选择唯一性索引
  2. 为常做为查询条件的字段建立索引
  3. 为经常需要排序、分组和联合操作的字段建立索引
  4. 限制索引数目（每个索引都需要占用磁盘空间，索引越多需要的磁盘空间越大，修改表时，对索引的重构和更新很麻烦）
  5. 小表不建议建立索引
  6. 尽量使用数据量少的索引（如果索引的值很长，那么查询的速度会受影响。此时尽量使用前缀索引）
  7. 删除不在使用的索引或很少使用的索引

    3.4 索引失效

    1）like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效；
    2）or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有左右查询字段均为索引时，才会生效；（不确定）
    3）联合索引不使用第一列，索引失效；
    4）数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描；
    5）在索引列上使用 IS NULL 或 IS NOT NULL操作。最好给列设置默认值。
    6）在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
    7）对索引字段进行计算操作、字段上使用函数。
    8）当 MySQL 觉得全表扫描更快时（数据少）

    4.优化

    4.1 查询性能优化

    Explain

    | id | ID代表执行select子句或操作表的顺序，如果包含子查询，则会出现多个ID。值越大，优先级越高，越先被执行。 | | —- | —- | | select_type | 常用的有 SIMPLE 简单查询，UNION 联合查询，SUBQUERY 子查询等。 | | table | 要查询的表 | | possible_keys | 可选择的索引 | | key | 实际使用的索引 | | rows | 扫描的行数 | | type | 索引查询类型。
    - const：使用主键或者唯一索引进行查询的时候只有一行匹配
    - ref：使用非唯一索引
    - range：使用主键、单个字段的辅助索引、多个字段的辅助索引的最后一个字段进行范围查询
    - index：和all的区别是扫描的是索引树。1）查询的字段是索引的一部分，覆盖索引。2）使用主键进行排序
    - all：全表扫描
    - system：触发条件：表只有一行，这是一个const type的特殊情况
    - eq_ref：触发条件：在进行连接查询的，使用主键或者唯一索引只匹配到一行记录的时候
    system>const>eq_ref>ref>range>index>all | | ref | 显示索引的哪一列被使用了,如果可能的话是一个常数,哪些列或常量被用于查找索引列上的值。 | | extra | |

4.2 优化数据访问

减少请求的数量

  • 只返回必要的列：最好不要使用SELECT*
  • 只返回必要的列：使用limit语句来限制返回的数据

  • 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

    减少服务器端扫描的行数

  • 最有效的是使用索引来覆盖查询

    4.3 重构查询方式

    切分大查询

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

    分解大连接查询

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

  • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。

  • 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。减少锁竞争；
  • 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。

  • 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

    5.锁

    锁类型

    共享锁（S Lock）

    允许事务读一行数据

    排他锁（X Lock）

    允许事务删除或者更新一行数据

    意向共享锁（IS Lock）

    事务想要获得一张表中某几行的共享锁

    意向排他锁

    事务想要获得一张表中某几行的排他锁

    MVCC

    多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

    基本概念

    版本号

  • 系统版本号：每开启一个新事务，系统版本号就会自动递增。

  • 事务版本号：事务开始时的系统版本号

隐藏的列
MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

  • 创建版本号：指示创建一个数据行的快照时的系统版本号；
  • 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

Undo日志
MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。

实现过程

https://mp.weixin.qq.com/s/J3kCOJwyv2nzvI0_X0tlnA

锁算法

RecordLock

锁定一个记录上的索引，而不是记录本身。如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。

Gap Lock

锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。

  1. SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

Next-Key Lock

它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。

在 InnoDB 存储引擎中，SELECT 操作的不可重复读问题通过 MVCC 得到了解决，而 UPDATE、DELETE 的不可重复读问题通过 Record Lock 解决，INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。

6.分库分表数据切分

水平切分

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

sharding策略

  • 哈希取模：hash(key)%N
  • 范围：可以是 ID 范围也可以是时间范围

  • 映射表：使用单独的一个数据库来存储映射关系

    sharding存在的问题

    事务问题
    使用分布式事务来解决，比如 XA 接口
    连接
    可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。
    唯一性

  • 使用全局唯一 ID （GUID）

  • 为每个分片指定一个 ID 范围
  • 分布式 ID 生成器（如 Twitter 的 Snowflake 算法）

    垂直切分

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

7.复制

主从复制

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

  • binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
  • I/O 线程 ：负责从主服务器上读取- 二进制日志，并写入从服务器的中继日志（Relay log）。
  • SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

image.png

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
读写分离能提高性能的原因在于：

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

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