Mysql

1. MySQL的索引

1.1 索引的数据结构

B+树

多路平衡查找树，路数(degree) = 数据页一页大小 / 索引数据大小

MySql 默认一页大小是16k = 4 * 操作系统页(4KB)

1.2 MySQL常见的存储引擎

MyISAM

tableName.frm（建表文件）

tableName.myd（my-data 存放数据）

tableName.myi (my-index 存放索引)

InnoDB

tableName.frm

tableName.idb

可以看出 InnoDB数据和索引是放在同一个文件里，按照主键来组织数据的存储。所以主键索引才又叫聚集索引。

如果创建表没有指定哪一列做索引，InnoDB将自动生成一个6byte int型的隐藏主键。

主键索引的叶子节点数据区保存数据，其他索引的叶子节点数据区保存主键

CSV

tableName.frm

tableName.CSV (保存元数据)

tableName.CSM （数据行，可以用vi直接编辑）

1.3 索引选择原则

经常用的列优先（最左匹配原则）

离散度高的列优先

宽度小的列优先（一个数据页的degree越多，提高树的查找效率）

覆盖索引：要查询的值正好包含在索引中，从而不需要找到叶子节点。所以如果查询能通过命中联合索引直接返回的，一定不要用select *;

2. MySQL查询执行路径

1. 客户端发送查询请求

2. 查询缓存（可配可无）

3. 查询优化处理

4. 查询执行引擎

5. 返回客户端

MySQL查询执行路径

2.1 客户端与服务端的通信

半双工: 在任意一个时刻，要么是客户端接收服务端返回的数据，要么是客户端向服务端发送数据，这两个动作不能同时发生。也无法将一个消息切成小块进行传输。

客户端一旦开始接收数据就没有办法停下来发送指令。

对于一个MySQL连接，时刻都有一个状态来标识这个连接正在做什么

show full processlist / show processlist

常见状态有：

• Sleep ： 线程正在等待客户端发送数据
• Query：连接线程正在执行查询
• Locked： 线程正在等待表锁的释放
• Sorting result： 线程正在对结果进行排序
• Sending data : 向请求端返回数据

2.2 缓存

MySQL能缓存select操作的结果集和SQL语句。对于新的SELECT语句，先去查缓存，判断是否有可用的记录集。

判断标准为：与缓存的SQL语句完全一样，区分大小写。即必须一毛一样，可以简单认为存储了一个key-value的结构，key为SQL语句，value为SQL执行结果集。

可以通过如下命令查看缓存情况

show status like 'Qcache%'

缓存配置项：

• query_cache_type

  0 - 不启用 （默认值）

  1- 启用查询缓存。但可以通过加上SQL_NO_CACHE不缓存

  2- 启用。但需要在SQL上加SQL_CACHE才缓存

• query_cache_size

  缓存区的大小，默认1M。推荐64/128M

• query_cache_limit

  单个结果集可使用的缓存大小，结果大于该值则不缓存。默认1M

为什么mysql默认关闭了缓存呢？

1. 在查询之前要先检查是否命中缓存，浪费计算资源
2. 如果这个查询可以被缓存，那么执行完成后，MySQL发现缓存中没有这个查询，将会把结果集放入缓存中，带来额外的系统消耗
3. 最重要的一点，当对表进行写入和更新操作时，该表对应的所有缓存都将失效

所以，MySQL的缓存只适合以读为主的业务场景，数据生成之后就不常变的业务。比如 新闻、报表类、门户类等

2.3 查询优化处理

2.3.1 查询优化处理的三个阶段

• 解析SQL

  将sql语句解析成解析树

• 预处理阶段

  根据MySQL的语法规则进一步检查解析树的合法性。比如检查表和数据列是否存在，解析列名和别名等

• 查询优化器

  找到最优的执行计划

那么，查询优化器是怎么找到最优的执行计划呢？

MySQL的查询优化器是基于成本计算的原则。它会尝试各种执行计划数据抽样的方式进行试验，随机读取一个4K的数据块进行分析。

一些优化手段

• 等价变化规则： 基于联合索引，调整位置等
• 优化count、min、max等函数：
• 覆盖索引扫描
• 子查询优化
• 提前终止查询 ： limit
• IN的优化 : 先进行排序，再采用二分查找

2.3.2 怎么看执行计划

可以通过explain查看执行计划，执行计划的几个关键参数：

1. id

   id代表select查询的序号，标识执行的顺序

   • id相同，执行顺序由上往下。id相同的可以认为是一组
   • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先执行

2. select_type

   select_type标识查询的类型，可以区分普通查询、联合查询、子查询等

   • SIMPLE ：简单的select查询，查询中不包含子查询或者union查询
   • PRIMARY: 对于包含子查询的sql，外层查询将被标记为PRIMARY
   • SUBQUERY：表示在select或where列表中包含子查询
   • MATERIALIZED： 表示在where后面 in条件的子查询
   • UNION：若第二个select出现在union之后，则被标记为union
   • UNION RESULT： 从union表获取结果的select

3. table

   查询设计到的表，一般直接显示表名或别名

   • <unionM,N>由id为M,N查询联合产生的结果
   • <subqueryN> 由id为N的查询产生的结果

4. type

   访问类型，它在查询优化中是一个很重要的指标，结果从好到坏依次是:

   • system：表只有一行记录，或系统表，
   • const： 表示通过一次索引就找到了，用于比较primary key或者unique key
   • eq_ref：唯一索引扫描，对于每个索引健，表中只有一条记录与之匹配
   • ref：非唯一索引扫描，返回匹配某个单独值的所有行
   • range ： 只检索给定范围的行
   • index：full index scan。索引全表扫描
   • ALL ：full table scan。

5. possible_keys, key, rows, filtered

   • possible_keys ：查询过程中有可能用到的索引
   • key： 实际使用的索引
   • rows：大致估算找到所需记录要读取的行数
   • filtered：结果行数占所需要读取rows行数的百分比。filtered越大越好

2.3.3 Extra

执行计划中的额外信息

1. using filesort

   MySQL对数据使用一个外部的文件内容进行排序，而不是按照表内的索引进行排序读取

2. Using temporary

   使用临时表保存中间结果，常见于order by 或 group by

3. Using index:

   表示select操作中使用了覆盖索引，避免了访问表的数据行

4. Using where

   使用了where过滤条件

5. select tables optimized away

   基于索引优化MIN/MAX操作或者MyISAM存储引擎优化COUNT(*)操作， 不必等到执行阶段在进行计算， 查询执行计划生成的阶段即可完成优化

2.4 查询执行引擎

调用插件式的存储引擎API执行 执行计划

2.5 返回客户端

1. 需要做缓存的，执行缓存操作

2. 增量的返回结果给客户端：

   开始生成第一条结果时，MySQL就开始往请求方逐步返回数据。

   这么做的好处是：MySQL服务器无需保存过多的数据，从而浪费内存；用户体验好，拿到数据快

3. 如何定位慢SQL

3.1.1 慢查询日志配置

#查看是否开启慢查询日志
show variables like 'slow_query_log'
#开启慢查询日志
set global slow_query_log=on
#设置慢查询日志文件路径
set global slow_query_log_file='/var/lib/mysql/slow.log'
#将没有使用索引的查询记录到日志
set global log_queries_not_using_indexes=on
#设置慢查询阈值，单位：秒
set global long_query_time=10 

3.2.2 慢查询日志分析工具

mysqldumpslow --help

4. 事务

事务是数据库操作的最小工作单元，是一组不可再分的逻辑工作单元。

#开启事务
begin/start transaction --手工开启一个事务
#事务提交或回滚
commit/rollback
#设置事务自动开启
set session autocommit = on/off;

4.1 事务的ACID特性

• Automicity 原子性

  最小的工作单元，整个工作单元要么一起提交成功，要么全部失败回滚

• Consistency 一致性

  事务中操作的数据及状态改变是一致的，即写入资料的结果必须完全符合预设的规则。不会因为出现系统意外等原因导致状态的不一致

• Isolation 隔离性

  一个事务所操作的数据在提交之前，对其他事务的可见性设定（一般设定为不可见）

• Durability 持久性

  事务所作的修改就会永久保存，不会因为系统意外导致数据的丢失

4.2 事务并发带来的问题

• 脏读 ：事务A读到事务B 本该rollback的 内容

  ![脏读] 脏读.jpg

• 不可重复读：

  事务A先读取某一条数据，在执行自己逻辑的过程中，事务B修改了这条数据并提交事务。然后事务A再次读取该记录，发现两次数据不一致了。

不可重复读.jpg

• 幻读

幻读.jpg

4.3 事务的隔离级别

根据SQL92 ANSI/ISO标准：

• Read Uncommitted （未提交读、读未提交）

  事务未提交对其他事务也是可见的，未解决任何并发问题

• Read Committed 提交读

  一个事务开始之后，只能看到已提交的事务所做的修改。解决了脏读问题，存在不可重复读

• Repeatable Read 可重复度

  在同一个事务中，多次读取同样的数据结果是一样的，解决不可重复度问题。但这种隔离级别未定义解决幻读的问题

• Serializable 串行化

  最高的隔离级别，每个事务串行执行 ，解决所有问题

总结：

隔离级别	脏读	不可重复读	幻读
未提交读（Read Uncommitted）	可能	可能	可能
已提交读（Read Committed）	不可能	可能	可能
可重复读（Repeatable Read）	不可能	不可能	对InnoDB不可能
串行化（Serializable）	不可能	不可能	不可能

5. 锁

锁是用来管理不同事务对共享资源的并发访问

5.1 表锁、行锁

表锁与行锁的区别：

锁定粒度：表锁 > 行锁

加锁效率：表锁 > 行锁

冲突概率：表锁 > 行锁

并发性能：表锁 < 行锁

InnoDB存储引擎支持行锁和表锁（另类的行锁）

5.2 InnoDB的锁

• 共享锁（行锁）： Shared Locks
• 排他锁（行锁）：Exclusive Locks
• 意向共享锁（表锁）：Intention Shared Locks
• 意向排他锁（表锁）：Intention Exclusive Locks
• 自增锁： AUTO-INC Locks

其中，行锁的算法

• 记录锁 Record Locks
• 间隙锁 Gap Locks
• 临键锁 Next-key Locks

5.2.1 共享锁 VS 排他锁

• 共享锁

共享锁又称读锁，简称S(shared)锁。顾名思义，共享锁是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

加锁方式：

select * from users where id = 1 Lock IN SHARE MODE;

• 排他锁

排他锁又称写锁，简称X锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（不管是共享锁还是排他锁）。只有该获取了排他锁的事务可以对这行数据进行读取和修改。其他事务要读取数据可来自于快照。

加锁方式：

# for update 加X锁。 delete/update/insert默认加X锁
select * from users where id = 1 for update;

InnoDB的行锁到底锁了什么？

InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引条件进行数据检索，InnoDB才使用行级锁，否则，InnoDB将使用表锁（锁住索引的所有记录）

5.2.2 意向共享锁 和意向排他锁

• 意向共享锁 IS：

  表示事务准备给数据行加入共享锁。即，一个数据行要想加共享锁之前，必须先取得该表的意向共享锁，意向共享锁之间是可以互相兼容的。

• 意向排他锁 IX:

  表示事务准备给数据行加入排他锁。即，一个数据行加排他锁之前必须先取得该表的IX锁，意向排他锁之间是可以相互兼容的。

  意向锁是InnoDB操作数据之前自动加的，不需要用户干预。当一个事务想去进行表锁时，可以先判断意向锁是否存在，如果存在则可以快速返回该表不能启用表锁。

5.2.3 自增锁 AUTO-INC Locks

自增锁是针对自增列自增长的一个特殊的表级别锁

show variables like 'innodb_autoinc_lock_mode'

默认取值为1，代表连续，若事务未提交则ID永久丢失

5.3 锁的算法

前面我们说InnoDB的行锁是采用记录锁、间隙锁、临键锁来实现的，那么这三者又是什么呢？

5.3.1 记录锁 Record Locks

记录锁的功能是锁住具体的索引项。当sql执行按照唯一性索引（Primary Key 、Unique Key）进行数据的检索时，查询条件等值匹配且要查询的数据存在，这时SQL语句加上的锁即为记录锁Record Locks，锁住这行索引项。

例如：数据库中存在2条记录

id	name	age
1	Alice	20
2	Bob	22

事务A锁住id为1的记录即给主键索引为1的记录加了一个记录锁。

5.3.2 间隙锁 Gap Locks

当sql执行按照索引进行数据的检索时，若查询条件的数据不存在，这时SQL语句加上的锁即为Gap Locks , 锁住索引不存在的这段区间，且左开右开。

如：数据库user表存在3条记录：

id	name	age
1	Alice	20
2	Bob	22
5	Charlie	22

事务A尝试获取id=4 的锁，sql语句为：

# 事务A
begin;
select * from users where id = 4 for update;
#先获取锁 rollback先不执行
rollback;

然后事务B尝试插入 id = 3的记录：

# 事务B
set session autocommit = off; --设置会话不自动提交
insert into users values(3,"Eve",20);
rollback;

结果发现插表不成功，输出为：

1205 - Lock wait timeout exceeded; try restarting transaction

时间: 51.035s

再尝试把id=3 改为 id=4 同样插不进表。更新id=2 或 id=5 均可成功。由此可以验证结论为：间隙是指不存在的那段区间。锁住的范围是左开右开。 无穷大同理。

5.3.3 临键锁 Next-Key Locks

临键锁锁住的是 记录+区间（左开右闭）。当sql执行按照索引进行数据的检索时，若查询的条件为范围查找（between a and b、< 、>）并有数据命中，则此时的SQL语句加上锁为临键锁。锁住索引存在的那条记录 + 不存在的区间。左开右闭。

所以，InnoDB可以解决幻读，当根据条件范围检索时，检索范围内不存在的数据将被锁住，所以不可能插入新记录进去。

InnoDB采用临键锁作为行锁的默认算法。

当记录不存在，临键锁可退化成Gap锁；当条件精准匹配到记录，则退化成Record锁。

5.4 数据库的死锁

死锁发生的条件：

• 多个并发事务（2个及以上）
• 每个事务都持有锁或在等待锁
• 每个事务都需要继续再持有锁
• 事务之间产生加锁的循环等待，形成死锁

避免死锁：

• 类似的业务逻辑以固定的顺序访问表和数据行

• 大事务拆成小事务。一般大事务更容易产生死锁，业务允许的话把大事务拆成小事务

• 在同一个事务中，尽可能做到一次性锁定所有需要的资源，减少死锁概率

• 如果业务允许可以降低隔离级别

• 为表添加合理的索引。可以看到如果不走索引将会为表添加表锁。

了解完锁的特性后，再看下面这个例子：

begin;--事务A
update users set age = 0 where id = 1; -- 上X锁
select * from users where id =1; -- 结果： 1，Alice,0
-- age = 0的数据存到哪儿去了？
rollback;

事务B在事务A 提交或rollBack之前，查到的永远是正确的1，Alice，20。

是不是有这样的疑问：为什么给id=1 的记录加上X锁后，别的事务还是能对它进行查询呢？快照是什么？它怎么实现的？

6. MVCC

MVCC ，Multi version concurrency control ，多版本并发控制。

并发访问数据库时，对正在事务内处理的数据做多版本的管理，以避免写操作的堵塞造成无法读的并发问题。

6.1 增删改查对应的MVCC

MVCC： 一张表设计完以后，MySQL默认给每一行自动生成DB_TRX_ID（数据入库时事务版本号），DB_ROLL_PT（数据删除时的版本号）。

6.1.1 插入

假设当前的全局事务ID为1，

begin; -- 拿到当前系统的事务ID
insert users(id,name,age) values(1,"Alice",20);
insert users(id,name,age) values(2,"Bob",21);
commit;

此时，表中记录的包括版本相关的信息为：

id	name	age	DB_TRX_ID	DB_ROLL_PT
1	Alice	20	1	NULL
2	Bob	21	1	NULL

6.1.2 删除

假设当前的系统全局事务ID已经到了10. 再此之前没有其他事务操作过id=1 的记录

begin;  -- 拿到事务ID=10
delete users where id = 1;
commit;

事务提交后，id = 1的记录 DB_ROLL_PT = 10. MVCC相关信息变成：

id	name	age	DB_TRX_ID	DB_ROLL_PT
1	Alice	20	1	10
2	Bob	21	1	NULL

6.1.3 修改

修改操作是先做命中数据行的复制，然后将原来那行数据的删除版本号设置为当前的事务ID。假设当前全局ID到了20，事务执行：

begin; -- 拿到事务ID=20
update users set age = 20 where id = 2;
commit;

事务提交后，

id	name	age	DB_TRX_ID	DB_ROLL_PT
1	Alice	20	1	10
2	Bob	21	1	20
2	Bob	20	20	NULL

6.1.4 查询

查询数据行版本小于当前查询的事务版本，这样就确保了只能读取到在本次事务开始之前就已经存在的数据；另外，数据行的删除版本要么为NULL，要么大于本次查询版本号，确保取出的记录在本次查询开始之前没有被删除。

但是如果事务A(tx_id=2)查询id=1的数据，然后处理自己逻辑的同时，事务B(tx_id = 1)对id=1的数据修改并提交了，接着事务A再次查询了id=1的数据，MVCC是怎么解决不可重复读的？

6.2 Redu / Undo 日志

6.2.1 Undo Log

Undo意思是撤销，以撤销操作为目的，返回某个指定状态的操作。

Undo Log是指在事务开始以前，在任何操作开始之前，首先将需要操作的数据备份到一个地方(Undo Log)。

Undo Log是为了实现事务的原子性而出现的产物。事务处理过程中如果出现了错误或用户执行ROLLBACK语句，MySQL可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。即，Undo Log中的数据可以作为数据旧版本的快照 供其他并发事务进行快照读。

Undo Log 1.jpg

6.2.2 Redo Log

Redo 的意思是重做。Redo Log 是事务操作的任何数据，将最新的数据备份到一个地方（Redo Log）。

Redo Log不是随着事务的提交才写的，而是在事务的执行过程中就开始写入Redo Buffer中，具体什么时候写到磁盘的Redo Log可以通过配置来配。Innodb_flush_log_at_trx_commit:

• 0：每秒提交redo buffer->redo log -> flush cache to disk
• 1：每次事务提交执行全流程redo buffer->redo log -> flush cache to disk
• 2：每次事务提交执行redo buffer->redo log。 然后每隔一秒执行 flush cache to disk

Redo Log是为了确保事务的持久性而设计的。为了防止在故障发生的时候，尚有脏页未写入磁盘，在重启MySQL服务时，根据Redo Log进行重做，从而使未入磁盘的数据再次进行持久化。

Redo Log.jpg

7. 数据库表设计 三范式

• 第一范式（1NF）：字段具有原子性，不可再分。

• 第二范式（2NF）：属性完全依赖主键，不能出现仅依赖主键一部分的属性，如果出现了，那么这个属性和主关键字的这部分关系应该分离出来形成一个新的实体。

• 第三范式（3NF）：一个数据库表中不包含已在其他表中已包含的非主键信息。

  简单点说：

  1NF：每一列只有一个单一的值，不能再拆分

  2NF：每一行都有主键进行区分

  3NF：每一个表都不包含其他表已经存在的非主键信息

三范式的要求仅为库表设计提供一种理论依据，

• 过分满足第一范式将为表建立太多的列

• 过分满足第三范式将造成太多的表关联

• 使用外键将带来额外的开销