
今天是2020年1月16号，记录这周发生的一件充值事故，1月12号周一下班后被通知某家代理商出现消耗金额超过充值金额800多万，当时第一反应是计费又出问题了，要帮忙定位问题，在家连zoom，翻看整个代理商从开户到现在每个月的充值数据，然后查询代理商每个月月月初的账户数据快照（实际每天晚上23:59都会快照一份存在hive中），一直到了晚上12点半，一直没有定位到问题出现的原因，然后领导就下线睡觉了，这个问题也出现一段时间不急于这一天了。

1月17号，大家都在吐槽年底了，为啥比平时还忙。针对昨天的bug，领导去支持了，我这边手上有两个大的功能点需要迭代上线。晚上回家后，被通知问题出现平台充值这边，不是计费的问题，头大，通过充值数据和消耗数据对比发现11月之前的正常的，12月开始出现消费大于充值金额；问题出现在11月份，还是完全没有思路，因为还是没有证据确定是充值问题还是计费问题，后续还是在翻看一直到12点半。

1月18号，下午定了一个会议室，专门来查这件事情，从代理商充值开始到12月1号00:00充值金额 - 12月1号00:00代理商下所有广告主的消耗金额 - 12月1号00:00代理商剩余金额 - 12月1号00:00代理商下广告主剩余金额。发现差值有：8486863.50元，这个时候可以错略确定的是充值问题，计费不会出现这么大的问题，而且19年1月份计费没有出现过重大bug。翻看代理商的充值记录有一条:8525163.53, 数值相近。不会是巧合，而且充值一般都是整数，问题出现了。

记得当时代理商是要预充值3000万，而数据库充值记录的金额字段是用的int(11), 单位：分，最多能存2147483647，所以当时后来又给代理商补了8525163.53，按道理这会应该it

也没有问题，问题出现账户表上，充值记录金额是有符号的，而账户余额是unsigned无符号的, 最大值到4294967295，是能存下3000万的。虽然充值记录显示2147483647，但实际账户确实是充值了3000万。

**充值是有事务的，为啥没有回滚？如何避免这种情况的发生**

MySQL默认情况下，当一个数据超出定义列的数据类型的范围时，数据会以数据类型所充许的最大值存储。不会出现报错，事务没有回滚。

在这种情况下，我们希望在插入或更新字段时，如果插入或更新的值超出定义的字段数据类型的范围时，提出报错，禁示修改数据，通过修改全局变量sq_mode   `STRICT_ALL_TABLES` 实现

```
[SQL] insert into t6(`num`) value(2147483648);
[Err] 1264 - Out of range value for column 'num' at row 1
```

**充值没有监控吗？为啥会出现这么重大bug**

功能版本迭代，开始建表是int，后续有种改不动的感觉。大家都开始回避这个棘手问题。直至出现这样的问题，所以监控是非常必要的，代理商自助充值的功能也是在出现问题增加的监控，这是第二次出现充值问题。

**总结**， 经历两次充值事故后了以下几点：

- 充值类型：`big int`, `decimal`，其他类型就不要考虑了，留坑而已
- 充值sql_mod的session连接，一定要设置成为 `STRICT_ALL_TABLES`
- 充值记录要完善，充值记录，账户充值后快照记录，多写一份数据备份记录，意味着可以更快的定位问题
- 监控，对充值逻辑的反向验证，这个根据具体业务实现逻辑来。走社会主义特色道路。
- 数据快照，公司重要数据一定要做每日快照，像这次如果没有代理商账户的每日快照数据备份，即便定位到了问题，也没有证据充分证明这件事，追账打官司复杂多就高了。

最后这两点不管是用在哪里都合适，就是因为用在哪里都合适所以很难重视起来，只有吃过亏的才知道。

最近公司审计发现某家代理商的消耗数据要比充值金额多出来了几百万？

int导致的充值事故报告

mysql中常见的日志文件：
- 错误日志(error log)
- 二进制日志(binlog)
- 慢查询日志(slow log)
- 查询日志(log)

# 错误日志
查看错误日志的位置
```shell
mysql> show variables like 'log_error'\G;
```

# 慢查询日志

开启慢查询日志`log_slow_queries`
```shell
mysql> show variables like 'log_slowqueries'\G;
```
慢查询时间`long_query_time`
```shell
mysql> show variables like 'long_query_time'\G
```

sql没有用到索引也会记录到慢查询日志
```shell
mysql> show variables like 'long_throttle_queries_not_using_indexes'\G;
```

mysqldumpslow查看执行时间最长的10条sql
```shell
$ mysqldumpslow -s -al -n 10 mysqlslow.log
```

mysql支持将慢查询日志写入到slow_log表，**log_output**指定慢查询输出格式，默认为file，设置为table会写入到slow_log表

```shell
mysql> show create table mysql.slow_log;
```

# rds执行日志记录sls
阿里云关系数据库RDS（MySQL版本）的SQL执行日志已经与日志服务打通，提供准实时的SQL执行分析功能，并提供内置可深钻的报表、支持自定义审计规则、异常报警、报表订阅与生态对接能力等。
- 据库的登录、退出。
- DDL（Data Definition Language）：对数据库结构定义的SQL，例如CREATE、ALTER DROP、TRUNCATE、COMMENT等。
- DML（Data Manipulation Language）：SQL操作语句，包括SELECT、INSERT、UPDATE、DELETE等。
- 其他SQL执行，包括任何其他通过SQL执行的控制，例如回滚、控制等。
- 失败的SQL执行也会被记录。
- 对SQL执行的延迟、执行结果、影响的行数等信息的提取。

https://help.aliyun.com/document_detail/118266.html

# 二进制日志
- max_binlog_size: 二进制文件最大值，超过该值产生新的文件，后缀加1
- binlog_cache_size: innodb所有未提交的事物都会被记录缓存中去，事物提交时，在写二进制日志中。当一个session开启一个事物时，会自动初始化一个大小为binlog_cache_size大小的缓存，当事物的记录大于binlog_cache_size的时候，会把缓冲的日志写入一个临时文件
    - binlog_cache_use 缓冲写二进制日志次数 
    - binlog_cache_disk_use 临时文件写二进制日志次数

查看binlog文件的内容：
```shell
mysql> mysqlbinlog --start-position=203 douyacun.00007
```

# 套接字文件
本地mysql可以使用套接字文件来连接
```shell
mysql> show variables like 'socket';
```

# pid文件
进程pid，可以用pid文件来重启进程
```shell
mysql> show variables like "";
```



错误日志,慢查询日志,查询日志,二进制日志

mysql日志文件

**mysql支持输入表情符号的问题**

1. 备份,备份所有需要升级的字符串编码的数据库

    - dump出来

2. 升级

    utf8mb4是MySQL5.5.3版本之后支持的字符集

3. 修改

    可以为一个database设置字符编码，可以为一张表设置字符编码，甚至可以为某一个字段设置字符编码

    - 查看当前系统默认的字符集设置

    ```
    mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
    +--------------------------+-----------------+
    | Variable_name            | Value           |
    +--------------------------+-----------------+
    | character_set_client     | utf8            |
    | character_set_connection | utf8            |
    | character_set_database   | utf8            |
    | character_set_filesystem | binary          |
    | character_set_results    | utf8            |
    | character_set_server     | utf8            |
    | character_set_system     | utf8            |
    | collation_connection     | utf8_general_ci |
    | collation_database       | utf8_general_ci |
    | collation_server         | utf8_general_ci |
    +--------------------------+-----------------+
    10 rows in set (0.01 sec)

    mysql>

    ```

    - 查看database的字符编码

    ```
    mysql> show create database polarsnow;
    +-----------+--------------------------------------------------------------------+
    | Database  | Create Database                                                    |
    +-----------+--------------------------------------------------------------------+
    | polarsnow | CREATE DATABASE `polarsnow` /*!40100 DEFAULT CHARACTER SET utf8 */ |
    +-----------+--------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql>
    ```

    - 查看table的字符编码

    ```
    mysql> show create table ps;
    +-------+---------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                |
    +-------+---------------------------------------------------------------------------------------------+
    | ps    | CREATE TABLE `ps` (
      `name` varchar(100) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +-------+---------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    ```

    - 查看column的字符编码

    ```
    mysql> show full columns from ps;
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    | Field | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    | name  | varchar(100) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    1 row in set (0.00 sec)
    ```

    - 修改database默认的字符集
    ```
    ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

    mysql> ALTER DATABASE polarsnow CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
    Query OK, 1 row affected (0.03 sec)

    mysql> show create database polarsnow;
    +-----------+--------------------------------------------------------------------------------------------------+
    | Database  | Create Database                                                                                  |
    +-----------+--------------------------------------------------------------------------------------------------+
    | polarsnow | CREATE DATABASE `polarsnow` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
    +-----------+--------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> show tables;
    +---------------------+
    | Tables_in_polarsnow |
    +---------------------+
    | ps                  |
    +---------------------+
    1 row in set (0.00 sec)

    mysql> show create table ps;
    +-------+---------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                |
    +-------+---------------------------------------------------------------------------------------------+
    | ps    | CREATE TABLE `ps` (
      `name` varchar(100) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +-------+---------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> show full columns from ps;
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    | Field | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    | name  | varchar(100) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
    +-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
    1 row in set (0.00 sec)

    mysql> create table test_tb2 (tb2 varchar(100) );
    Query OK, 0 rows affected (0.21 sec)

    mysql> show tables;
    +---------------------+
    | Tables_in_polarsnow |
    +---------------------+
    | ps                  |
    | test_tb2            |
    +---------------------+
    2 rows in set (0.00 sec)

    mysql> show create table test_tb2;
    +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table    | Create Table                                                                                                                                              |
    +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
    | test_tb2 | CREATE TABLE `test_tb2` (
      `tb2` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
    +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql>
    ```

    - 修改column默认的字符集

    ` ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; `

4. 修改配置文件
```
SET NAMES utf8 COLLATE utf8_unicode_ci becomes SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci

> vim /etc/my.cnf
# 对本地的mysql客户端的配置
[client]
default-character-set = utf8mb4

# 对其他远程连接的mysql客户端的配置
[mysql]
default-character-set = utf8mb4

# 本地mysql服务的配置
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
> service mysqld restart
```

5. 检查修改
    ```
    mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
    +--------------------------+--------------------+
    | Variable_name            | Value              |
    +--------------------------+--------------------+
    | character_set_client     | utf8mb4            |
    | character_set_connection | utf8mb4            |
    | character_set_database   | utf8mb4            |
    | character_set_filesystem | binary             |
    | character_set_results    | utf8mb4            |
    | character_set_server     | utf8mb4            |
    | character_set_system     | utf8               |
    | collation_connection     | utf8mb4_unicode_ci |
    | collation_database       | utf8mb4_unicode_ci |
    | collation_server         | utf8mb4_unicode_ci |
    +--------------------------+--------------------+
    10 rows in set (0.00 sec)
    ```

6. 修改 & 优化所有数据表

    ```
    mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
    +--------------------------+--------------------+
    | Variable_name            | Value              |
    +--------------------------+--------------------+
    | character_set_client     | utf8mb4            |
    | character_set_connection | utf8mb4            |
    | character_set_database   | utf8mb4            |
    | character_set_filesystem | binary             |
    | character_set_results    | utf8mb4            |
    | character_set_server     | utf8mb4            |
    | character_set_system     | utf8               |
    | collation_connection     | utf8mb4_unicode_ci |
    | collation_database       | utf8mb4_unicode_ci |
    | collation_server         | utf8mb4_unicode_ci |
    +--------------------------+--------------------+
    10 rows in set (0.00 sec)
    ```

7. 最重要的修改 php 的数据库连接,不用担心, `utf8mb4` 支持 `utf8` 的链接!

    ```
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST'),
        'port' => env('DB_PORT'),
        'database' =>env('BYIDB_DATABASE'),
        'username' => env('DB_USERNAME'),
        'password' => env('DB_PASSWORD'),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
    ```

utf8mb4格式存储数据


[toc]

# 全局锁

对整个数据实例加锁，FTWRL

```sql
flush table with read lock
```

使用这个语句后其他线程的语句会阻塞：数据更新语句，数据表定义语句和更新事务的提交语句

**使用场景**：全库逻辑备份，mysqldump导出的时候使用MVCC的一致性保证innodb导出数据，但是对于MyISAM来说是不支持的，备份只能通过FTWRL来保证

**为什么不用 set global readonly = true**

1.  readonly的值，一般被用作判断一个库是主库还是从库，修改global变量的方式影响面大，不建议使用
2.  异常处理机制上，如果执行FTWRT由于客户端发生异常断开，mysql会自动释放这个全局锁，而设置全局readonly后，如果客户端发生异常，则数据库会一直保持readonly状态，导致整个库事件处于不可写状态，风险较高

# 表级锁

mysql表级锁有两种: 

**表锁**

与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放

```sql
lock tables t1 read, t2 write;
```

其他线程写t1，读写t2都会被阻塞，该线程也只能读t1，读写t2

不推荐使用，粒度太大，目前很少见到了

**MDL（元数据锁，meta data lock）**

MDL不需要显示使用，在访问一个表的时候会自动加上，MDL的作用是，保证读写的正确性，防止在一个查询在执行期间另一线程对这个表结构做变更，删除了一列，导致查询线程的结果和表结构对不上。

对表做CURD时，加MDL读锁；对表结构做变更操作时，加MDL写锁；

-   MDL读锁之间不互斥，可以并发读写
-   MDL写锁互斥，两个线程同时修改，只能等拿到锁的线程执行完成

| 时间点 | 会话A                                | 会话B                        | 会话C                             | 会话D                                                |
| ------ | ------------------------------------ | ---------------------------- | --------------------------------- | ---------------------------------------------------- |
| 1      | begin; <br />Select count(*) from t; |                              |                                   |                                                      |
| 2      |                                      | Select * from t where id =2; |                                   |                                                      |
| 3      |                                      |                              | alter  table t add column c3 int; |                                                      |
| 4      |                                      |                              |                                   | Show  processlist；B：copy to tmp table              |
| 5      |                                      |                              | 阻塞                              | Show  processlist;B：Waiting for table metadata lock |
| 6      | A：执行完毕                          |                              |                                   |                                                      |
| 7      |                                      | B: 执行完成                  |                                   | Show  processlist; B：rename table                   |
| 8      | Select  count(*) from t;             |                              |                                   |                                                      |
| 9      |                                      |                              | C：执行完毕                       |                                                      |
| 10     |                                      |                              |                                   | Show  processlist; A: Sending data                   |
| 11     | A:执行完毕                           |                              |                                   |                                                      |

session A先启动，这是会对t加一个MDL读锁，session B需要的也是加MDL读锁。读锁不互斥可以正常执行

而session C需要MDL写锁，而之后所有要在表t上申请MDL读锁的请求也会被session C阻塞。等于现在这个表t完全不可读写了，而客户端超时会有重试机制，新的session请求，这个库的线程很快就爆满。

**如何安全的进行表结构变更**

如果是热表的话，就麻烦了，虽然数据量不大，但是请求频繁。这时候kill掉未必管用，因为新的请求马上就来了，一般能遇到这种情况的公司都是有点实力的了，必定会有DBA的支撑了，很幸运和DBA聊了一下

一般表结构变更直接执行就好了，如果遇到一直拿不到MDL锁的情况, 考虑

-   处理慢查询，商量能不能kill
-   解决长事务，会一直占着MDL锁，[长事物解决方案](innodb事务#长事务)
-   对于热表的话，因为查询很频繁，会一直拿不到MDL写锁，考虑晚上执行，到这就很头疼了。
-   如果是上亿数据的大表（数据几千万，甚至过亿）的话, 或者说一直拿不到MDL写锁的话，推荐方案：
    -   [pt-online-schema-change](https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html)
    -   [gh-ost](https://github.com/github/gh-ost)
    -   [阿里云-不锁表结构变更](https://help.aliyun.com/document_detail/98373.html)

主要原理：

-   1、创建临时表：CREATE TABLE tmp_table_table LIKE table_name
-   2、变更临时表结构： ALTER TABLE tmp_table_table XXXX
-   3、全量拷贝数据：INSERT IGNORE INTO tmp_table_table (SELECT %s FROM table_name FORCE INDEX (%s) WHERE xxx
-   4、增量数据binlog同步： UPDATA/INSRT/DELETE tmp_table_name
-   5、切换新旧表： RENAME TABLE table_name to old_tmp_table_table, tmp_table_name to table_name



# 行锁

**两阶段锁协议**

解锁阶段：事务提交或者回滚

加锁阶段：第一个变更开始

在innodb事务中，行锁是在需要的时候才加上的，但并不是不需要了就立即释放，而是要等到事务结束时才释放，这个就是两阶段锁协议

**加锁原则**

1.  原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间。
2.  原则2：查找过程中访问到的对象才会加锁。
3.  优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
4.  优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
5.  一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

## 三种算法

**幻像读**

innodb事物默认的隔离级别是REPEATBLE RRAD， 

幻想读是指在同一事物下，连续执行两次相同的sql，会有不同的结果，第二次sql可能会返回之前不存在的行。

next key lock是如何解决的?

```sql
select * from t where key > 20 for update;
```

next key lock会锁住(20, ∞]区间，在此事物未提交期间，key > 20记录都是不能插入的。

- Record Lock

单个行记录上锁，总是锁住索引记录。如果表在建立是并没有建立索引，innodb会使用隐式的主键来锁

索引可以是主键、唯一、普通索引

- Gap lock

间隙锁，锁定一个范围，但不包含记录本身。

- Next key lock

gap lock + record lock 锁定范围并锁定记录本身。

innodb对于行的查询采用的是这种方式，eg. 有一个索引，有10、11、13、20四个值，那么该索引被Next key lock的区间是

- (-∞, 10)
- [10, 11)
- [11, 13)
- [13, 20)
- [20, +∞)

**等值间隙锁**

```sql
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
```



| session A                             | session B                                | session C                             |
| ------------------------------------- | ---------------------------------------- | ------------------------------------- |
| begin;                                |                                          |                                       |
| update t set  d = d + 1 where id = 7; |                                          |                                       |
|                                       | insert into t  values(8,8,8);     block; |                                       |
|                                       |                                          | update t set d = d + 1 where id = 10; |

Session B 会被block，因为session A的加锁区间是(5,10]; 因为id=7不存在，退化为间隙锁。session B会被阻塞，10是开区间，这行是可以修改的。

**非唯一索引等值锁**

| session A                                         | session B                                     | session C                                   |
| ------------------------------------------------- | --------------------------------------------- | ------------------------------------------- |
| begin;                                            |                                               |                                             |
| select id from t where c = 5 lock in  share mode; |                                               |                                             |
|                                                   | update t set d = d + 1 where id = 5;          |                                             |
|                                                   | update t set d = d + 1 where c =  5; (block); |                                             |
|                                                   |                                               | insert into t values  (7,7,7);     (block); |

1. `update t set d = d + 1 where id = 5;` 为什么不会阻塞？

where条件是id = 5，session A `select id from t where c = 5 lock in  share mode;` 

lock in share mode只锁**覆盖索引**，不会影响主键索引。for update 就不一样了，系统会认为接下来会更新数据，顺便会对主键索引上满足条件的索引加锁。

如果session A查询是 `select d from t where c = 5 lock in share mode;`  session B的`update t set d = d + 1 where id = 5;`  就会被阻塞了。

2. `update t set d = d + 1 where c = 5; ` 为什么会阻塞？

加锁原则中的优化2: 索引上等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。加锁的范围会变为 (c, 10];  session c `insert into t values  (7,7,7);`  也会被阻塞



## 死锁

并发线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入等待的状态。超时以后客户端会又重试机制，这时mysql的连接数就上来了，负载飙升。

**死锁处理策略**：

- 超时策略：`innodb_lock_wait_timeout` 默认50s
- 死锁检测：`innodb_deadlock_detect` 默认时开启的

**避免死锁的建议**：

- 通过两阶段锁协议我们知道：行锁是在事务提交的时候才会释放锁，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
- 控制访问相同资源的并发事务量，比如：抢购商品分成10条记录，或放入队列

## 乐观锁

[乐观锁应用场景](https://segmentfault.com/a/1190000008935924)

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，不会上锁，但是更新时会判断在此期间有没有人更新这个数据。实现方式有两种：

- 使用数据版本(version)记录机制实现
- 使用时间戳

```sql
SELECT * FROM table_name WHERE ... FOR UPDATE。
```

select： 读锁，共享锁
update \ insert \ delete : 每次都会自己加排他锁

一个商品A, 库存有10个， 一百人来抢，不要超卖
update table set num=num-1 where num=10;

select num from table;
update table set num=num-1 where num=9;

## 悲观锁: 

锁的是索引， 如果没有索引，直接锁表。

总是假设最坏的情况，每次去拿数据时总会假设别人会修改，所以每次在拿数据时都会加锁。别人想拿这条数据就会阻塞直到他拿到锁，

### 共享锁

读取的任何行上设置共享模式锁定，其他会话可以读取行，但是在事务提交之前不能修改它们，如果这些行中的任何一个被尚未提交的另一个事务修改，则查询等待直到该事务结束，然后使用最新职

```sql
-- 5.7
SELECT * FROM table_name WHERE ... lock in share mode
-- 8.0
SELECT * FROM table_name WHERE ... for share
```

### 排他锁 

排他锁也叫写锁，简称x锁，原理：一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁（排他锁或者共享锁），即一个事务在读取一个数据行的时候，其他事务不能对该数据行进行增删改查。

子查询中的行不会被锁定，除非在子查询中也指定了锁

```sql
SELECT * FROM table_name WHERE ... FOR UPDATE。
```

注意点:
- 对于select语句，innodb不会加任何锁，也就是可以多个并发去进行select的操作，不会有任何的锁冲突，因为根本没有锁。
- 对于insert，update，delete操作，innodb会自动给涉及到的数据加排他锁，只有查询select需要我们手动设置排他锁。
- 共享锁和排他锁都是锁的行记录，意向共享锁和意向排他锁锁定的是表。

悲观锁，每次在拿数据都会上锁，其他session想拿这个数据就会被阻塞知道它拿到锁

**脏读**

脏数据：未提交的数据

脏读：一个事物读取到了另一个事物未提交的数据

脏读违背了事物的隔离性，关注一下innodb是如何隔离事务的。

现在不会见到脏读了，数据隔离级别要设置成 READ UNCOMMITTED级别才可以，现在默认级别是READ REAPEATABLE

那么READ UNCOMMITTED只有特殊场景才会遇到，如：slave

**不可重复读**

不可重读读是指：在一个事物内多次读取同一数据集合，在这个事物还没有结束时，另一个事物对该数据集合做了一些DML操作。这样就发生了同一事物内两次读取同一数据集合，数据不一致。

不可重复读和脏读的区别是，脏读是读未提交，而不可重复读的已经提交

next key lock算法下，不仅锁住扫描到的索引，还锁住了这些索引覆盖的范围，这个范围内的插入是不允许的，这样就避免了另外一个事物在这个范围内插入数据，导致多次读取数据不一致。

**阻塞**

一个事物中的锁需要等待另一个事物中的锁释放他所占的资源，这就是阻塞

**innodb_lock_wait_timeout** 用来控制等待的时间默认50秒

**innodb_rollback_on_timeout** 用来设置在等待超时是否对进行中的事物进行回滚。



为啥数据库CPU会飙升，真的是业务上涨导致的吗，考虑过死锁吗？如何避免死锁？ 什么是MDL锁？

mysql锁


innodb是索引存储表，表数据按主键顺序存放

[toc]

# 基础概念

聚簇索引、辅助索引、联合索引

## 聚簇索引

聚集索引按照每张表的主键构造一棵B+树，叶子节点存放的是整张表的行记录数据，叶子节点被称为数据页。

每张表只能按照一颗B+树排序，每张表只能有一个聚集索引

聚集索引对于主键的排序查找和范围查找速度非常快

没有主键或唯一索引，innodb默认会生成一个行号(不可见)，作为聚集索引的主键

## 辅助索引

辅助索引叶子节点不会包含行记录的所有数据。叶子节点除了包含键值以外还会包含主键，来查找叶子节点以外的其它数据。

辅助索引通过主键id来关联记录

## 联合索引

对表上的多个列进行索引

联合索引的技巧

1. 覆盖索引：从索引即可查询到所需字段，不需要查询聚集索引中的记录，减少io操作。

2. 最左前缀：对a,b创建索引

   - 可以用到索引
     - where a = 1;
     - where a = 1 and b = 2;
     - where a = 1 order by b;

   - 用不到索引
     - where b = 2;

3. 索引下推：a like 'hello%’ and b >10，检索出来的数据会先过滤掉b <= 10 的数据，然后在进行回表查询，减少回表查

## MRR(multi-range read)

目的： 减少磁盘的随机访问，将随机访问变为顺序的数据访问。

MRR执行过程：

- 优化器将二级索引查询到的记录放到一块缓存中
- 如果二级索引扫描到文件的末尾或者缓冲区已满，使用快排对缓冲区中的内容按照住建进行排序
- 用户线程调用MRR接口取cluster index, 然后根据cluster index取行数据
- 当根据缓冲区中的cluster index取完数据，继续调用2、3过程，直至扫面结束 

MRR好处：

- 使数据访问变得较为顺序，在查询辅助索引时，首先根据得到的结果，按照主键排序，
- 减少缓冲池中页被替换的次数
- 批量处理对键值的查询

explain 可以看到extra列 using MRR

- [MySQL · 特性分析 · 优化器 MRR & BKA](http://mysql.taobao.org/monthly/2016/01/04/)

## ICP

mysql 5.6开始支持，将where的过滤部分放到存储层

```sql
select * from salaries
where (from_date between "2019-01-10" and "2019-02-10") and (salaries between 3800 and 4000)
```

不启用ICP，则数据需要先取出from_date between 2019-01-10 and 2019-02-10的数据，然后再过滤 3800-4000的数据

启用ICP，则会在索引取出时就会进行where过滤，前提是where条件被索引覆盖

explain 可以看到extra列 using index condition

- [MySQL · 特性分析 · Index Condition Pushdown (ICP)](http://mysql.taobao.org/monthly/2015/12/08/)

# 疑难杂症

## 普通索引和唯一索引应该怎么选择？

例如身份证号，身份证号具有唯一性，由于身份证号比较大，不建议把身份证号作为主键，要么给身份证号加一个唯一索引，要么加一个普通索引。如果业务代码已经保证不会写入重复的身份证号，那么是选择普通索引还是唯一索引比较好？

查询过程：

- 对于普通索引，查找到第一个满足条件的索引后，需要继续查找下一个记录，知道碰到第一个不满足条件的记录
- 对于唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录后，会停止继续检索

这点查询性能影响很小，可以忽略，所以唯一索引和普通索引查询性能相差不多(别钻牛角尖，如果是字段重复度高，这种情况不符合我们讨论的场景)。

更新过程：

​	对于插入一条(4,500)

- 这个记录要更新的目标页在内存中
  - 唯一索引，找到3和5之间的位置，判断没有冲突值，插入这个值，语句结束；
  - 普通索引，找到3和5之间的位置，插入这个值，语句结束
- 这个记录要更新的目标页没有在内存中
  - 唯一索引，需要将目标页读入到内存中，判断没有冲突，插入这个值，语句结束
  - 普通索引，将更新记录在change buffer中，语句执行结束

数据从磁盘读入到内存涉及到随机I/O的访问，是数据库成本最高的操作之一。change buffer减少了随机磁盘访问。对于提升更新性能很明显

## change buffer

**什么条件下可以使用change buffer？**

对于唯一索引，所有的更新操作都要先判断这个操作是否违反唯一约束，要判断值是否就必须先把数据页读入到内存中才能判断，如果已经读入到内存中，那直接更新内存会更快，没必要使用change buffer了

唯一索引更新就不能使用change buffer了，实际上只有普通索引可以使用

**change buffer的使用场景？**

- change buffer对于更新过程有加速作用
- merge是数据真正更新的时刻，在一个数据库做merge之前，change buffer记录的越多，收益越大。写多读少的场景change buffer使用效果最好。
- 如果一个业务的更新模式写入之后马上做查询，将更新记录在了change buffer中，但之后由于马上要访问这个数据页，马上会触发merge，反而增加change buffer的维护成本。

**change buffer和redo log**

change buffer主要是在更新时，不用将数据读入到内存中，节省的是随机读磁盘的I/O消耗。

redo log主要节省的是随机写磁盘的I/O消耗（顺序写）

## 如何重建索引和主键索引？

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，造成空间浪费

```sql
alter table T engine=InnoDB;
```

## 字符串如何创建索引?

**前缀索引**

定义好长度，可以做到既节省空间，又不用额外增加太多的查询成本。

例如邮箱，对于每个记录都是只取前6个字节

```sql
alter table user add index index2(email(6));
```

- 占用的空间更小
- 增加额外的扫描次数
- 用不上覆盖索引对查询性能的优化，需要回主键索引查一次

```sql
select id, email from SUser where email='douyacun@gmail.com';
```

当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？

```sql
select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from user;
```

**倒序存储**

例如：身份证号前面的几位是地址码重复度高，这个索引的区分度就非常低了。按照前缀索引来创建的索引需要创建12位以上，才能满足区分度要求。

存储身份证号的时候把它倒过来存，每次查询的时候，可以这么写：

```sql
select field_list from t where id_card = reverse(:id_card);
```

**hash字段**

使用crc32函数得到校验码使用整型存储，只在这个列上加索引，索引空间就会小很多。但是CRC32函数很容易碰撞，需要加上原条件判断

```sql
select field_list from t where id_card_crc = crc32(:id_card) and id_card = :id_card;
```

普通索引和唯一索引应该怎么选择？字符串如何加索引才能更高效并且节省空间？