MySQL常见知识点整理

MySQL常见知识点整理

事务基础

什么是数据库的事务？

  事务是数据库管理系统（DBMS）执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

  有些文章会说事务是数据库最小的工作单元，这种说法并不准。事务是作为单个逻辑工作单元执行，并不是最小的工作单元。

哪些存储引擎支持事务？

  InnoDB支持事务。

The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data.

Chapter 15 Alternative Storage Engines ：MySQL 5.7 Supported Storage Engines

事务四大特性 ★★★★★

  事务四个特性，分别是原子性(Atomicity)、一致性(Consistent)、隔离性(Isolation)、持久性(Durable)。在日常使用中，会用四个特性的首字母简称ACID。

  原子性、隔离性、持久性，都是为了实现一致性。

原子性
  事务是一个不可再分的逻辑工作单元，所以事务是必定会具备原子性的。原子性在事务中的提现便是，一个事务要么成功，要么失败，不可能存在部分成功或者部分失败的情况。

  原子性，在 InnoDB 里面是通过 undo log 来实现的，它记录了数据修改之前的值（逻辑日志），一旦发生异常，就可以用 undo log 来实现回滚操作。

  不存在部分成功的场景也有一点不准确，基于SAVEPOINT功能可以rollback到某个SAVEPOINT，实现某些场景下部分成功。

一致性
  指的是数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。比如主键必须是唯一的，字段长度符合要求。

  除了数据库自身的完整性约束，还有一个是用户自定义的完整性。

隔离性
  当多个事务同时去操作同一张表或者同一行数据，必然会产生一些并发操作。隔离性就是很多个的事务，对表或者 行的并发操作，应该是透明的，互相不干扰的。通过这种方式，我们最终也是保证业务数据的一致性。

持久性
  我们对数据库的任意的操作，只要事务提交成功，那么结果就是永久性的，不可能因为我们重启了数据库的服务器，它又恢复到原来的状态了。

  持久性怎么实现呢？数据库崩溃恢复（crash-safe）是通过什么实现的？持久性是通过 redo log 来实现的。当我们操作数据库数据的时候，会先写到内存的Buffer Pool 里面，同时记录 redo log，如果在刷盘之前出现异常，在重启后就可以读取 redo log的内容，写入到磁盘，保证数据的持久性。

事务在什么时候开始和结束？

  以InnoDB为例，当我们在执行DML语句的时候，会自动开启一个事务，默认情况下会自动提交。

  手动开启事务有两种方式，begin;和start transaction;。

  手动结束事务也有两种方式，提交事务，commit; 和 回滚事务 rollback;。

  还有一种异常情况，客户端会话连接断开的时候，事务也会结束。

show variables like 'autocommit';

set autocommit = 0|1|ON|OFF;

  InnoDB 里面有一个autocommit的参数，默认值是 ON，分成两个级别，session级别和global级别。如果它的值是TRUE / ON 的话，我们在操作数据的时候，会自动开启一个事务，和自动提交事务。

事务隔离级别 ★★★★★

• 聊聊你理解的事务隔离级别？
• 事务并发会带来哪些问题？

脏读
读未提交
//todo 画图介绍脏读

不可重复读
读已提交
//todo 画图介绍不可重复读

幻读
脏读和不可重复读是基于主键查找数据时产生的不一致。幻读是基于范围查找，读到其他已提交事务新增数据。
//todo 画图介绍幻读

  在SQL92(http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt)中定义了四个隔离级别，表格的右侧P1、P2、P3分别代表了事务并发的3个问题，脏读，不可重复读，幻读。

 _Level__________________P1_____________P2_____________P3____________
| READ UNCOMMITTED     | Possibe      | Possibe      | Possibe      |
| READ COMMITTED       | Not Possible | Possibe      | Possibe      |
| REPEATABLE READ      | Not Possible | Not Possible | Possibe      |
| SERIALIZABLE         | Not Possible | Not Possible | Possibe      |

Read Uncommitted（未提交读）

  一个事务可以读取到其他事务未提交的数据，会出现脏读，所以叫做 RU，它没有解决任何的问题。

Read Committed（已提交读）

  一个事务只能读取到其他事务已提交的数据，不能读取到其他事务未提交的数据，它解决了脏读的问题，但是会出现不可重复读的问题。

Repeatable Read（可重复读）

  它解决了不可重复读的问题，也就是在同一个事务里面多次读取同样的数据结果是一样的，但是在这个级别下，没有定义解决幻读的问题。

Serializable（串行化）

  在这个隔离级别里面，所有的事务都是串行执行的，也就是对数据的操作需要排队，已经不存在事务的并发操作了，所以它解决了所有的问题。

InnoDB

InnoDB的事务隔离级别 ★★★★★

  当使用MySQL的InnoDB引擎时，不需要使用串行化的隔离级别去解决幻读问题。

事务隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	不可能
串行化	不可能	不可能	不可能

如何解决数据的读一致性问题 ★★★★

LBCC

  第一种，既然要保证前后两次读取数据一致，那么读取数据的时候，锁定我要操作的数据，不允许其他的事务修改就行了。这种方案叫做基于锁的并发控制 Lock Based Concurrency Control（LBCC）。

  如果仅仅是基于锁来实现事务隔离，一个事务读取的时候不允许其他时候修改，那就意味着不支持并发的读写操作，而我们的大多数应用都是读多写少的，这样会极大地影响操作数据的效率。

MVCC

https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html

  另一种解决方案，如果要让一个事务前后两次读取的数据保持一致，那么我们可以在修改数据的时候给它建立一个备份或者叫快照，后面再来读取这个快照就行了。这种方案我们叫做多版本的并发控制 Multi Version Concurrency Control（MVCC）

  MVCC 的核心思想是： 我可以查到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。

InnoDB 锁的基本类型

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

锁的基本模式——共享锁

第一个行级别的锁就是我们在官网看到的 Shared Locks （共享锁），我们获取了一行数据的读锁以后，可以用来读取数据，所以它也叫做读锁。而且多个事务可以共享一把读锁。那怎么给一行数据加上读锁呢？

我们可以用 select lock in share mode;的方式手工加上一把读锁。

释放锁有两种方式，只要事务结束，锁就会自动事务，包括提交事务和结束事务。

锁的基本模式——排它锁

第二个行级别的锁叫做 Exclusive Locks（排它锁），它是用来操作数据的，所以又叫做写锁。只要一个事务获取了一行数据的排它锁，其他的事务就不能再获取这一行数据的共享锁和排它锁。

排它锁的加锁方式有两种，第一种是自动加排他锁，可能是同学们没有注意到的：

我们在操作数据的时候，包括增删改，都会默认加上一个排它锁。

还有一种是手工加锁，我们用一个 FOR UPDATE 给一行数据加上一个排它锁，这个无论是在我们的代码里面还是操作数据的工具里面，都比较常用。

释放锁的方式跟前面是一样的。

锁的基本模式——意向锁

意向锁是由数据库自己维护的。

也就是说，当我们给一行数据加上共享锁之前，会自动在这张表上面加一个意向共享锁。

当我们给一行数据加上排他锁之前，会自动在这张表上面加一个意向排他锁。

反过来说：

如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁。

锁的算法

t2 这张表 id 有一个主键索引。我们插入了 4 行数据，主键 id 分别是 1、4、7、10。

我们这里的划分标准是主键 id。

这些数据库里面存在的主键值，我们把它叫做 Record，记录，那么这里我们就有 4 个 Record。

根据主键，这些存在的 Record 隔开的数据不存在的区间，我们把它叫做 Gap，间隙，它是一个左开右开的区间。

假设我们有 N 个 Record，那么所有的数据会被划分成多少个 Gap 区间？答案是 N+1，就像我们把一条绳子砍 N 刀，它最后肯定是变成 N+1 段。

最后一个，间隙（Gap）连同它左边的记录（Record），我们把它叫做临键的区间，它是一个左开右闭的区间。

如果主键索引不是整型，是字符怎么办呢？字符可以排序吗？ 基于 ASCII 码

记录锁

第一种情况，当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一

条记录的时候，这个时候使用的就是记录锁。

比如 where id = 1 4 7 10 。

间隙锁

第二种情况，当我们查询的记录不存在，无论是用等值查询还是范围查询的时候，它使用的都是间隙锁。

临键锁

第三种情况，当我们使用了范围查询，不仅仅命中了 Record 记录，还包含了 Gap 间隙，在这种情况下我们使用的就是临键锁，它是 MySQL 里面默认的行锁算法，相当于记录锁加上间隙锁。

比如我们使用>5 < 9 ， 它包含了不存在的区间，也包含了一个 Record 7。

锁住最后一个 key 的下一个左开右闭的区间。

select * from t2 where id >5 and id <=7 for update; 锁住(4,7]和(7,10]

select * from t2 where id >8 and id <=10 for update; 锁住 (7,10]，(10,+∞)**

总结：为什么要锁住下一个左开右闭的区间？——就是为了解决幻读的问题。

InnoDB和MyISAM中锁的对比

  MySQL中锁是由存储引擎的提供的。常用的引擎有InnoDB和MyISAM。InnoDB支持表锁与行锁，而MyISM只支持表锁。

行锁

• 优点
  • 粒度小
• 缺点
  • 获得、释放所做的工作更多
  • 容易发生死锁
• InnoDB实现
  • 共享锁
  • 排他锁
  • 间隙锁
    • 通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息来实现的
  • 锁优化
    • 尽可能让数据检索都通过索引来完成
    • 合理设计索引
    • 尽可能减少基于范围的数据检索过滤条件
    • 尽可能控制事务的大小
    • 业务允许的情况下，尽量使用较低基本的事务隔离

表锁

• 优点
  • 实现逻辑简单
  • 获得，释放锁快
  • 避免死锁
• 缺点
  • 颗粒度太大，并发不够高
• MyISAM实现

查询优化

什么场景下MySQL索引会失效？

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

MySQL5.0后有index merge。or分开的条件可以分开选择索引查询数据，然后聚合返回。

2.最左前缀匹配原则

了解复合索引的匹配原则，避免无法匹配索引。

3.索引列不能参与计算，保持列“干净”

匹配符左边不要搞任何方法调用和计算

4.隐形类型转换

varchar的字段匹配的时候用int数字会索引失效

5.选择区分度低的列作为索引

由于索引扫描后要利用索引中的指针去逐一访问记录，假设每个记录都使用索引访问，则读取磁盘的次数是查询包含的记录数T，而如果表扫描则读取磁盘的次数是存储记录的块数B，如果T>B的话索引就没有优势了。对于大多数数据库来说，这个比例是10%（oracle，postgresql等），即先对结果数量估算，如果小于这个比例用索引，大于的话即直接表扫描。

6.like查询是以%开头

7.IS NULL不能利用索引，只能全表扫描

8.当MySQL一旦估计检查的行数可能会”太多”，范围查找优化将不会被使用,而使用全表扫描。 （这条可能和5是同一个东西）

如何进行慢SQL查询

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

打开慢日志开关

  因为开启慢查询日志是有代价的（跟 bin log、optimizer-trace 一样），所以它默认是关闭的：

show variables like 'slow_query';

-- 还有一个参数，控制执行超过多长时间的 SQL 才记录到慢日志，默认是 10 秒。

show variables like '%long_query%';

-- 可以直接动态修改参数（重启后失效）。
set @@global.slow_query_log=1; -- 1 开启，0 关闭，重启后失效 
set @@global.long_query_time=3; -- mysql 默认的慢查询时间是 10 秒，另开一个窗口后才会查到最新值 

show variables like '%long_query%'; 
show variables like '%slow_query%';

  除此之外，还可以修改配置文件 my.cnf。
以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。

slow_query_log = ON 
long_query_time=2 
slow_query_log_file =/var/lib/mysql/localhost-slow.log

  模拟慢查询：

select sleep(10);

慢日志分析

show global status like 'slow_queries'; -- 查看有多少慢查询 
show variables like '%slow_query%'; -- 获取慢日志目录

cat /var/lib/mysql/ localhost-slow.log

MySQL Dump Slow

https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

  MyQL 提供了 mysqldumpslow 的工具，在 MySQL 的 bin 目录下。

mysqldumpslow --help

  例如：查询用时最多的 10 条慢 SQL：

mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/localhost-slow.log

• Count 代表这个 SQL 执行了多少次；
• Time 代表执行的时间，括号里面是累计时间；
• Lock 表示锁定的时间，括号是累计；
• Rows 表示返回的记录数，括号是累计。

如何查看执行计划?

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

explain 查询sql

  有时会发现不同环境下，相同的sql执行计划并不相同，所以需要确认环境之间的差异。

select version(); 
show variables like '%engine%';