皆非的万事屋

MySql知识总结

MySql基础

关系数据库概念

MyISAM 和 InnoDB 的区别

[scode type="green"]拓展一下[/scode]

[scode type="blue"]因此，对于咱们日常开发的业务系统来说，你几乎找不到什么理由再使用 MyISAM 作为自己的 MySQL 数据库的存储引擎。[/scode]

锁机制与 InnoDB 锁算法

[scode type="blue"]MyISAM 和 InnoDB 存储引擎使用的锁[/scode]

[scode type="blue"]表级锁和行级锁对比[/scode]

[scode type="blue"]InnoDB 存储引擎的锁的算法有三种[/scode]

死锁

表级锁不会产生死锁，所以解决死锁主要还是针对于最常用的InnoDB。

mysql死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序

[scode type="blue"]举例1[/scode]
例如两个用户同时投资，A用户金额随机分为2份，分给借款人1，2

B用户金额随机分为2份，分给借款人2，1

由于加锁的顺序不一样，死锁当然很快就出现了。

对于这个问题的改进很简单，直接把所有分配到的借款人直接一次锁住就行了。

Select * from xxx where id in (xx,xx,xx) for update

在in里面的列表值mysql是会自动从小到大排序，加锁也是一条条从小到大加的锁

[scode type="blue"]举例2[/scode]
在开发中，经常会做这类的判断需求：根据字段值查询（有索引），如果不存在，则插入；否则更新。

以id为主键为例，目前还没有id=22的行

# session1:
select * from t3 where id=22 for update;
Empty set (0.00 sec)
# session2:
select * from t3 where id=23  for update;
Empty set (0.00 sec)
# session1:
insert into t3 values(22,'ac','a',now());
# 锁等待中……
# session2:
insert into t3 values(23,'bc','b',now());
# ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

当对存在的行进行锁的时候(主键)，mysql就只有行锁。

当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（有gap锁）

锁住的范围为：

(无穷小或小于表中锁住id的最大值，无穷大或大于表中锁住id的最小值)

对于这种死锁的解决办法是：

insert into t3(xx,xx) on duplicate key update xx='XX';

[scode type="red"] 一般的情况，两个session分别通过一个sql持有一把锁，然后互相访问对方加锁的数据产生死锁。[/scode]
[scode type="red"]两个单条的sql语句涉及到的加锁数据相同，但是加锁顺序不同，导致了死锁。[/scode]

死锁预防策略

InnoDB引擎内部(或者说是所有的数据库内部)，有多种锁类型：事务锁(行锁、表锁)，Mutex(保护内部的共享变量操作)、RWLock(又称之为Latch，保护内部的页面读取与修改)。

InnoDB每个页面为16K，读取一个页面时，需要对页面加S锁，更新一个页面时，需要对页面加上X锁。

任何情况下，操作一个页面，都会对页面加锁，页面锁加上之后，页面内存储的索引记录才不会被并发修改。

因此，为了修改一条记录，InnoDB内部如何处理：

相对于事务锁，页面锁是一个短期持有的锁，而事务锁(行锁、表锁)是长期持有的锁。

因此，为了防止页面锁与事务锁之间产生死锁。InnoDB做了死锁预防的策略：持有事务锁(行锁、表锁)，可以等待获取页面锁；但反之，持有页面锁，不能等待持有事务锁。

根据死锁预防策略，在持有页面锁，加行锁的时候，如果行锁需要等待,则释放页面锁，然后等待行锁。

此时，行锁获取没有任何锁保护，因此加上行锁之后，记录可能已经被并发修改。

因此，此时要重新加回页面锁，重新判断记录的状态，重新在页面锁的保护下，对记录加锁。

如果此时记录未被并发修改，那么第二次加锁能够很快完成，因为已经持有了相同模式的锁。

但是，如果记录已经被并发修改，那么，就有可能导致本文前面提到的死锁问题。

MySQL死锁产生原因和解决方法

查询缓存

事务

[scode type="yellow"]数据事务的实现原理呢？[/scode]

并发事务带来哪些问题

SQL 标准定义了四个隔离级别

MySQL 的默认隔离级别

[scode type="share"]InnoDB 存储引擎提供了对 XA 事务的支持，并通过 XA 事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transactional resources）参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的 ACID 要求又有了提高。另外，在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE。————《MySQL 技术内幕：InnoDB 存储引擎(第 2 版)》7.7 章[/scode]

索引

概念

索引的优缺点

优点

[scode type="blue"]大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。[/scode]

索引的底层数据结构

Hash表 & B+树

为什么MySQL 没有使用其作为索引的数据结构？

B 树& B+树

总结

Mysql为什么最终选择了B+树作为索引的数据结构

索引类型

主键索引(Primary Key)

二级索引(辅助索引)

[scode type="blue"]二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。唯一索引，普通索引，前缀索引等索引属于二级索引。[/scode]

聚集索引与非聚集索引

聚集索引

非聚集索引

[scode type="share"]随机IO：假设我们所需要的数据是随机分散在磁盘的不同页的不同扇区中的，那么找到相应的数据需要等到磁臂（寻址作用）旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，一次进行此过程直到找完所有数据，这个就是随机IO，读取数据速度较慢。
顺序IO：假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序IO[/scode]

非聚集索引一定回表查询吗(覆盖索引)

非聚集索引不一定回表查询

# 用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。
 SELECT name FROM table WHERE name='guang19';
# 那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

覆盖索引

磁盘io次数

数据量小的话，直接把索引放到内存中，内存的O(logn)消耗是远远低于磁盘io的,所以可以忽略不计

数据量大的话，采用索引结构，我们这部分先从二叉树说起，对于普通二叉树，第一个步骤是二分，每次判断都是一次半数的数量级检索。假如有100W的数据，大概的时间复杂度是：log2N=1000000即N=20的节点获取，也就是磁盘I/O复杂度最大为O(20)，二分的时间复杂度是O(log2N)。

但是对于数据库来说，存储场景会更加复杂，二叉树的性能虽然好，但我们还是想要树的高度更低一些，存储的数据更多一些。因此mysql引入了B+树的概念。除了根节点之外，第二层级的数量得到了充分的扩展，相对于普通的二叉树，B+树的结构更加庞大又不失美感。

假设非叶节点不同元素占用情况为：下一条记录指针占4Byte，id值8Byte，目标记录指针4Byte，那么一个4Kb的磁盘块将大致可以容纳250个下级指针，100万行目标记录(假设叶子节点也是只保存了id值，则一个非叶子节点下面也包括大概250个叶子节点)只需log250N=1000000即N=3的I/O次数，充分提升了每次节点I/O带来的检索效用，时间复杂度是O（lognN）,这里的n是非叶子结点的个数。（PS：实际上innodb的数据页大小是16kb，这个n会更大，那么对应的，io次数也会更少）

在实际的查询中，IO次数可能会更小，因为有可能会把部分用到的索引读取到内存中，相对于磁盘IO来说，内存的io消耗可以忽略不计。

一般来说B+Tree的高度一般都在2-4层，MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作（根节点的那次不算磁盘I/O）。

最左原则

最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。例如某表现有索引(a,b,c)，现在你有如下语句：

select * from t where a=1 and b=1 and c =1;     #这样可以利用到定义的索引（a,b,c）,用上a,b,c
select * from t where a=1 and b=1;     #这样可以利用到定义的索引（a,b,c）,用上a,b
select * from t where b=1 and a=1;     #这样可以利用到定义的索引（a,b,c）,用上a,c（mysql有查询优化器）
select * from t where a=1;     #这样也可以利用到定义的索引（a,b,c）,用上a
select * from t where b=1 and c=1;     #这样不可以利用到定义的索引（a,b,c）
select * from t where a=1 and c=1;     #这样可以利用到定义的索引（a,b,c），但只用上a索引，b,c索引用不到

也就是说通过最左匹配原则你可以定义一个联合索引，使得多种查询条件都可以用到该索引。
值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。也就是：

select * from t where a=1 and b>1 and c =1; #这样a,b可以用到（a,b,c），c索引用不到

像select * from table where a = '1' and b > ‘2’ and c='3' 这种类型的sql语句，在a、b走完索引后，c肯定是无序了，所以c就没法走索引，数据库会觉得还不如全表扫描c字段来的快。

以index （a,b,c）为例建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。

[scode type="green"]最左匹配原则的原理[/scode]
联合索引的叶子节点键值数量不是一个，而是多个。

由于构建一颗 B+ 树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建 B+ 树。

该图就是一个形如(a,b,c)联合索引的 b+ 树，其中的非叶子节点存储的是第一个关键字的索引 a，而叶子节点存储的是三个关键字的数据。

这里可以看出 a 是有序的，而 b，c 都是无序的。但是当在 a 相同的时候，b 是有序的，b 相同的时候，c 又是有序的。

总结

[scode type="green"]联合索引的优点[/scode]

创建索引的注意事项

[scode type="yellow"]选择合适的字段创建索引[/scode]

[scode type="yellow"]选择合适的字段创建索引[/scode]

[scode type="yellow"]尽可能的考虑建立联合索引而不是单列索引[/scode]

[scode type="yellow"]注意避免冗余索引[/scode]

[scode type="yellow"]考虑在字符串类型的字段上使用前缀索引代替普通索引[/scode]

使用索引的一些建议

建立索引SQL

# 添加 PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
# 添加 UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
# 添加 INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
# 添加 FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
# 添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

MySQL 高性能优化规范建议

数据库命令规范

数据库基本设计规范

所有表必须使用 Innodb 存储引擎

阿里巴巴开发手册数据库部分的一些最佳实践

模糊查询

外键和级联

Tips:

MVCC

一致性非锁定读和锁定读

一致性非锁定读

对于 一致性非锁定读（Consistent Nonlocking Reads） 的实现，通常做法是加一个版本号或者时间戳字段，在更新数据的同时版本号 + 1 或者更新时间戳。
查询时，将当前可见的版本号与对应记录的版本号进行比对，如果记录的版本小于可见版本，则表示该记录可见

在 InnoDB 存储引擎中，多版本并发控制 (Multiversion Concurrency Control) 就是对非锁定读的实现。

如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)

在 Repeatable Read 和 Read Committed 两个隔离级别下，如果是执行普通的 select 语句则会使用 一致性非锁定读（MVCC）。并且在 Repeatable Read 下 MVCC 实现了可重复读和防止部分幻读

锁定读

如果执行的是下列语句，就是 锁定读（Locking Reads）

在锁定读下，读取的是数据的最新版本，这种读也被称为 当前读（current read）。锁定读会对读取到的记录加锁：

在一致性非锁定读下，即使读取的记录已被其它事务加上 X 锁，这时记录也是可以被读取的，即读取的快照数据。

上面说了，在 Repeatable Read 下 MVCC 防止了部分幻读，这边的 “部分” 是指在 一致性非锁定读 情况下，只能读取到第一次查询之前所插入的数据（根据 Read View 判断数据可见性，Read View 在第一次查询时生成）。

但是！如果是 当前读 ，每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。

所以， InnoDB 在实现 Repeatable Read 时，如果执行的是当前读，则会对读取的记录使用 Next-key Lock ，来防止其它事务在间隙间插入数据

InnoDB 对 MVCC 的实现

MVCC 的实现依赖于：隐藏字段、Read View、undo log。

在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。

每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

隐藏字段

在内部，InnoDB 存储引擎为每行数据添加了三个 隐藏字段：

ReadView

class ReadView {
  /* ... */
private:
  trx_id_t m_low_limit_id;      /* 大于等于这个 ID 的事务均不可见 */

  trx_id_t m_up_limit_id;       /* 小于这个 ID 的事务均可见 */

  trx_id_t m_creator_trx_id;    /* 创建该 Read View 的事务ID */

  trx_id_t m_low_limit_no;      /* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */

  ids_t m_ids;                  /* 创建 Read View 时的活跃事务列表 */

  m_closed;                     /* 标记 Read View 是否 close */
}

Read View 主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”

undo-log

undo log 主要有两个作用：

在 InnoDB 存储引擎中 undo log 分为两种： insert undo log 和 update undo log：

不同事务或者相同事务的对同一记录行的修改，会使该记录行的 undo log 成为一条链表，链首就是最新的记录，链尾就是最早的旧记录。

数据可见性算法

在 InnoDB 存储引擎中，创建一个新事务后，执行每个 select 语句前，都会创建一个快照（Read View），快照中保存了当前数据库系统中正处于活跃（没有 commit）的事务的 ID 号。

其实简单的说保存的是系统中当前不应该被本事务看到的其他事务 ID 列表（即 m_ids）。当用户在这个事务中要读取某个记录行的时候，InnoDB 会将该记录行的 DB_TRX_ID 与 Read View 中的一些变量及当前事务 ID 进行比较，判断是否满足可见性条件

RC 和 RR 隔离级别下 MVCC 的差异

在事务隔离级别 RC 和 RR （InnoDB 存储引擎的默认事务隔离级别）下，InnoDB 存储引擎使用 MVCC（非锁定一致性读），但它们生成 Read View 的时机却不同

虽然 RC 和 RR 都通过 MVCC 来读取快照数据，但由于 生成 Read View 时机不同，从而在 RR 级别下实现可重复读

在 RC 隔离级别下，事务在每次查询开始时都会生成并设置新的 Read View，所以导致不可重复读

MVCC + Next-key-Lock 防止幻读

InnoDB存储引擎在 RR 级别下通过 MVCC和 Next-key Lock 来解决幻读问题：

执行普通 select，此时会以 MVCC 快照读的方式读取数据：

执行 select...for update/lock in share mode、insert、update、delete 等当前读：

窗口函数

MySQL从8.0开始支持窗口函数，这个功能在大多商业数据库和部分开源数据库中早已支持，有的也叫分析函数。

窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。

对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

窗口函数和普通聚合函数也很容易混淆，二者区别如下：

按照功能划分，可以把MySQL支持的窗口函数分为如下几类：

窗口函数的基本用法：函数名（[expr]） over子句
其中，over是关键字，用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算

进阶

高可用

配置主从，即master和多个slave，slave读取主产生的bin-log日志（I/O操作）来同步数据，但是主挂了怎么办？

mycat 中间件可以做读写分离以及数据库的分片策略，但是目前还是只支持5.7以下的数据库，强制改8会出一些问题。（感觉现在差不多死了），mycat2 一直出不来

sharding-jdbc 是最近比较火的嵌入式sql代理工具，介于ORM与JDBC之间，对原有JDBC层做增强，通过拦截SQL语句，可以做读写分离与分片策略，（sharding-proxy也可以做，但是个数据库中间件，类似mycat，需要单独部署）

(目前项目中博主用的sharding-jdbc比较多)

分布式ID

在对数据库(表)水平拆分之后，表中数据的主键ID已经不能使用自增来实现了，否则会有重复，解决：

Snowflake

雪花算法（Snowflake）是twitter公司内部分布式项目采用的ID生成算法，开源后广受国内大厂的好评，在该算法影响下各大公司相继开发出各具特色的分布式生成器。

Snowflake生成的是Long类型的ID，一个Long类型占8个字节，每个字节占8比特，也就是说一个Long类型占64个比特。
Snowflake ID组成结构：正数位（占1比特）+ 时间戳（占41比特）+ 机器ID（占5比特）+ 数据中心（占5比特）+ 自增值（占12比特），总共64比特组成的一个Long类型。

//Twitter的SnowFlake算法,使用SnowFlake算法生成一个整数，然后转化为62进制变成一个短地址URL
public class SnowFlakeShortUrl {
    //起始的时间戳
    private final static long START_TIMESTAMP = 1480166465631L;
    //每一部分占用的位数
    private final static long SEQUENCE_BIT = 12;   //序列号占用的位数
    private final static long MACHINE_BIT = 5;     //机器标识占用的位数
    private final static long DATA_CENTER_BIT = 5; //数据中心占用的位数
    //每一部分的最大值
    private final static long MAX_SEQUENCE = -1L ^ (-1L << SEQUENCE_BIT);
    private final static long MAX_MACHINE_NUM = -1L ^ (-1L << MACHINE_BIT);
    private final static long MAX_DATA_CENTER_NUM = -1L ^ (-1L << DATA_CENTER_BIT);
    //每一部分向左的位移
    private final static long MACHINE_LEFT = SEQUENCE_BIT;
    private final static long DATA_CENTER_LEFT = SEQUENCE_BIT + MACHINE_BIT;
    private final static long TIMESTAMP_LEFT = DATA_CENTER_LEFT + DATA_CENTER_BIT;
    private long dataCenterId;  //数据中心
    private long machineId;     //机器标识
    private long sequence = 0L; //序列号
    private long lastTimeStamp = -1L;  //上一次时间戳
    private long getNextMill() {
        long mill = getNewTimeStamp();
        while (mill <= lastTimeStamp) {
            mill = getNewTimeStamp();
        }
        return mill;
    }
    private long getNewTimeStamp() {
        return System.currentTimeMillis();
    }
    //根据指定的数据中心ID和机器标志ID生成指定的序列号
    //@param dataCenterId 数据中心ID
    //@param machineId    机器标志ID
    public SnowFlakeShortUrl(long dataCenterId, long machineId) {
        if (dataCenterId > MAX_DATA_CENTER_NUM || dataCenterId < 0) {
            throw new IllegalArgumentException("DtaCenterId can't be greater than MAX_DATA_CENTER_NUM or less than 0！");
        }
        if (machineId > MAX_MACHINE_NUM || machineId < 0) {
            throw new IllegalArgumentException("MachineId can't be greater than MAX_MACHINE_NUM or less than 0！");
        }
        this.dataCenterId = dataCenterId;
        this.machineId = machineId;
    }
    //产生下一个ID
    public synchronized long nextId() {
        long currTimeStamp = getNewTimeStamp();
        if (currTimeStamp < lastTimeStamp) {
            throw new RuntimeException("Clock moved backwards.  Refusing to generate id");
        }
        if (currTimeStamp == lastTimeStamp) {
            //相同毫秒内，序列号自增
            sequence = (sequence + 1) & MAX_SEQUENCE;
            //同一毫秒的序列数已经达到最大
            if (sequence == 0L) {
                currTimeStamp = getNextMill();
            }
        } else {
            //不同毫秒内，序列号置为0
            sequence = 0L;
        }
        lastTimeStamp = currTimeStamp;
        return (currTimeStamp - START_TIMESTAMP) << TIMESTAMP_LEFT //时间戳部分
                | dataCenterId << DATA_CENTER_LEFT       //数据中心部分
                | machineId << MACHINE_LEFT             //机器标识部分
                | sequence;                             //序列号部分
    }    
    public static void main(String[] args) {
        SnowFlakeShortUrl snowFlake = new SnowFlakeShortUrl(2, 3);

        for (int i = 0; i < (1 << 4); i++) {
            //10进制
            System.out.println(snowFlake.nextId());
        }
    }
}

基于雪花的开源分布式ID工具

uid-generator

uid-generator是由百度技术部开发。

uid-generator是基于Snowflake算法实现的，与原始的snowflake算法不同在于，uid-generator支持自定义时间戳、工作机器ID和 序列号 等各部分的位数，而且uid-generator中采用用户自定义workId的生成策略。

uid-generator需要与数据库配合使用，需要新增一个WORKER_NODE表。当应用启动时会向数据库表中去插入一条数据，插入成功后返回的自增ID就是该机器的workId数据由host，port组成。

Leaf

Leaf由美团开发。

Leaf同时支持号段模式和snowflake算法模式，可以切换使用。

Tinyid

Tinyid 由滴滴开发。

Tinyid是基于号段模式原理实现的与Leaf如出一辙，每个服务获取一个号段（1000,2000]、（2000,3000]、（3000,4000]

一致性Hash

当我们可以生成分布式ID之后的另一个重要问题就是，我们对一条记录的存放/查询策略，对于新增的数据，我们应该放在哪个数据库的哪个表？拿到一个ID，我们应该如何定位到哪个数据库的哪个表？

其中关于定位数据库的具体实现已经可以通过sharding-JDBC/proxy的配置实现了，主要是配置中的策略如何定？

由于考虑到机器扩展和宕机，对ID直接取余这种Hash操作已经不能适用，这里我们需要了解一致性Hash。

概念

一致性哈希算法也是使用取模的方法，但不是简单的对自定义数量进行取模，是对2^32取模

列式数据库

优缺

优点：

缺点：

当前页面是本站的「Google AMP」版。查看和发表评论请点击：完整版 »