mysql

基础架构

大致分未2层

• server层
• 存储引擎

server层

server层包括

• 连接器
• 查询缓存
• 分析器
• 优化器
• 执行器

连接器

当一个看客户端和mysql进行tcp连接，使用show processlist就可以查看到当前连接数。

建立连接过程通常很复杂，所以建议使用长连接。

但是长连接在mysql使用过程会积累很多内存占用，所以经常因为内存占用过大，导致被系统kill（OOM），mysql重启。那么就需要有解决办法:

1. 定期断开长连接
2. Mysql_reset_connection重置连接，但是不需要重连和权限验证

查询缓存

8.0版本已经删除缓存功能

mysql会以k-v形式把sql保存到缓冲中，后面的分析步骤都无需执行。

但是如果修改数据就会导致缓存大规模失效需要重新更新，所以不建议使用。

分析器

执行步骤

1. 词法分析，他会根据mysql的策略判断语句中的 字符串是什么，又代表着什么
2. 语法分析，判断是否满足mysql的语法

优化器

在开始执行之前，还要先经过优化器的处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器

• 判断权限
• 调存储引擎接口

MYSQL基础

SQL技巧

sql执行顺序

sql编写顺序

SELECT DISTINCT
	<select_list>
FROM
	<left_table> <join_type>
JOIN 
	<right_table> ON <join_condition>
WHERE 
	<where_condition>
GROUP BY 
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_params>

sql执行顺序

FROM 		<left_table>
ON			<join_condition>
<josin_type>	JOIN	<right_table>
WHERE 		<where_condition>
GROUP BY	<group_by_list>
HAVING		<having_condition>
SELECT DISTINCT	<select_list>
ORDER BY	<order_by_condition>
LIMIT		<limit_params>

SQL优化

慢查询sql

查看sql查询频率

#global返回有所连接的连接操作信息  session则是当前连接信息
show global/session status like "com_%";

查看连接数

show processlist;

慢查询

vim /etc/my.conf

slow_query_log = 1
long_query_time = 5 #慢查询时间 超过1秒则为慢查询
slow_query_log_file = /data/mysql/mysql-slow.log

explain解析

explain是解析优化一个sql的执行

id 列

id代表每个条sql查询的执行顺序，id越大权重越大，则执行顺序越快

#id都是1，权重一样
select * from a,b,c;

# select a 的id=1  select b id=2
select * from a where id=(select * from b where = 1)

select_type列

select_type	含义	对应实例sql
SIMPLE	简单select查看，没有连表或子查询或union等	select * from a
PRIMARY	查询中有子查询，最外层的标记(间接的说就是id=1的标记)	select * from a where id=(select * from b where = 1) 第一个select才是primary
SUBQUERY	子查询标记 select * from a where id=(select * from b where = 1)	select * from a where id=(select * from b where = 1) 第二个select是subquery
DERIVED	在FROM列表中包含的子查询，被标记为DERIVED(衍生) MYSQL会递归执行 select * from (select * from b where = 1)	select * from (select * from b where = 1) 第一个select是DERIVED
UNION	UNION之后的sql	select a union select b 第二个select是union
UNION RESULT	在union表获取结果的select

从上之下，效率又高至低

table

• 数据源于的表
• 有null或temp table或实体表等

type 很重要

type	含义	对应sql实例
null	不访问任何表或索引，直接返回结果	select now()
system	表中只有一行记录，相当于系统表，它是const类型的特例	select * from a where id=(select * from b where = 1) 第一个select是system
const	常亮，只返回一条记录。所以是主键=value 或 唯一索引=value，就可以确保是一条返回记录	select * from a where id=1
re_ref	多表关联查询，且返回记录只有一条
ref	非唯一索引查询
range	检索给定返回的行，使用一个索引来选择行，where之后出现 >， < ，in 等操作
index	index 和 all的区别 ，index是了整个索引树，all是全表扫描
all	扫全表

从上之下，效率又高至低

possible key列

可能使用的索引

key列

实际使用的索引

注意：因为可能数据表内容少，虽然有索引，可是mysql优化时，觉得不必要去读取索引树，所以会导致某些能用索引的条件，没有使用索引

key_len列

索引最大的使用的字节数，并非使用时的实际长度。索引字节越短，效率就越高

rows 列

当前条件扫描的行数

Extra 列

extra	含义	对应sql实例
using filesort	使用了外部的索引排序(文件排序)，没有使用索引的排序功能。效率低	优化的方法：对排序条件加索引
using temporary	对临时表排序，常见于order by 和 group by 。效率低
using index	表明select操作使用了覆盖索引，避免访问表的数据行，效率不错
using where	在查找使用索引的情况下，需要回表查询数据
using index condition	查找使用了索引，但是需要回表查询
using index;using where	查找使用了索引，但是需要的数据都在索引列中 能找到，所以不需要回表查询

show profile 分析sql 的工具

#查看mysql是否支持profile
select @@have_profile;

#是否开启了profile
select @@profiling;

#开启profile
set profiling=1;

#查看每条sql的耗时
show profiles;
return query_id and duration

#查看sql的耗时具体时间
show profile for query query_id;

trace 分析优化器执行计划

查看mysql对sql进行了哪些具体的优化

# 开启优化器
set optimizer_trace="enable=on",end_markers_in_json=on; #开启，并返回结构是json

set optimizer_trace_max_mem_size=100000; #内存大小

#查看优化器表内容，内容里面记录了之前执行过的sql优化
select * from information_scheme.optimizer_trace\G

insert优化

单条 转 多条

insert into tb values(),()

事务插入

begin;
insert into tb values ();
insert into tb values ();
insert into tb values ();
commit;

主键有序插入

insert into tb values(1,"a");
insert into tb values(2,"b");
insert into tb values(3,"c");

ORDER BY 优化

#单列字段排序
select * from tb order by a; #虽然使用了搜索引，但是extra：using filesort,通过文件排序
select a,b,c from tb order by a; #因为覆盖索引的原因，extra：using index通过索引排序
select a,d from tb order by a; #虽然使用了搜索引，但是extra：using filesort,通过文件排序,d不是覆盖索引的字段

#多字段排序
select a,b from tb order by a,b #两个同时升降序 extra:using index
select a,b from tb order by a asc,b desv #两个升降序不同 extra:using filesort.
！！并且order by 后的字段必须要和索引的位置一致，才能using index。这个和是否命中索引不同，优化器不会自动优化sql

GROUP BY 优化

#group by 默认会根据 group by field进行排序。
select a from tb group by a;	#extra：using index;using filesort;
select a from tb group by a order by null; #extra:using index

嵌套查询

#少用子查询,尽量用多表联查或join
select a from tb1 where b in (select * from tb2); # type：index

select a from tb1,tb2 where tb1.a=1 and tb2.x=1; #type ：ref

select a from tb1 join tb2 on tb1.a = tb2.x where tb1.a=1; #type ：ref

优化OR 查询

#建议使用union替换or
select * from tb where id=1 or b=2; 	#key: null
select * from tb where id=1 union select * from tb where b=2; #key:a_b_c;null

优化分页

• 为什么分页会慢，是因为系统需要排序，所以慢。那么就要优化排序

#其实就是优化排序
#方案一：优化select * ，这样就需要使用了覆盖索引，这样就可以在索引树上排序 using index
select * from tb limit 20000,10; #花费时间2s key:null
select * from tb where id in (select id from tb limit 20000,10) AS tmp_tb #花费时间1s  key: primary

#方案二：只使用于自增id并且不能断层，那就是使用索引将前面的数据过滤掉
select * from tb where id>200000 limit 10; #key：primary  extra: using index condition

SQL提示

• use index：提示mysql使用某个索引

#创建单列索引
create index idx_a on tb_name(a);

#系统默认使用复合索引的情况下，可以提示系统使用单列idx_a索引
select * from tb use index(idx_a) where a='1';

• ignore index：忽略某个索引

#系统默认使用复合索引的情况下，告诉系统忽略a_b_c复合索引
select * from tb ignore index(a_b_c) where a='1';

• force index: 强制系统使用某个索引

#系统默认使用复合索引的情况下，强制系统使用单列idx_a索引
select * from tb force index(idx_a) where a='1';

客户端应用优化

数据库连接池

• 连接需要网络IO和进程/线程的创建(每个客户端通过 网络连接 服务端都要新起一个进程)，为了避免频繁的上下文创建，那么就需要持久化连接。
• 优化网络连接IO：连接池可以根据系统自身情况来更改IO模型(poll,epoll,select)
• 优化频繁创建和销毁连接：持久化连接池的N个连接，当请求过多时，创建更多的连接进行处理任务

添加cache层

• redis , memcache

负载均衡

• 主从复制binlog(读写分离)
• 分库分表
• 分布式(不过要确保连接是调到同一个主从复制集群中) + 主从复制

MYSQL 并发调优

max_conections

• 客服端最大连接数，默认是151。如果大于151，则进入等待队列中

back_log

• max_connects大于的151的连接，会放进队列中，back_log就是队列的大小，默认为80.如果连接数大于151+80,则之后的连接会抛连接错误，而不是阻塞等待。

table_open_cache

• 该参数用来控制所有SQL语句执行线程可打开的缓存表数量。所有连接数同时开启的表缓存的数量，比如150个连接，然后每个连接的sql关联了2个表，及150*2 = 300

thread_cache_size

• 为了加快连接数据库的速度，MYSQL会缓存一定数量的客户服务线程以备用。好像线程池啊

innodb_lock_wait_timeout

• innodb行锁等待时间

锁

锁类型

粒度细分

• 全局锁：给整个数据库实例加锁，命令: Flush Tables With Read Lock（FTWRL）
• 表锁 table lock：操作对象是整个数据表
• 行锁 row lock：操作对象是一行数据，Innodb引擎支持

对数据/表操作的类型分

• 读锁：又叫共享锁(S)，多个读操作可以共同进行，互不影响
• 写锁：又叫排他锁(X)，当前操作为执行完更改操作，会阻塞其他的写锁和读锁

粒度锁对比

粒度锁类型	特点
全局锁	整个表阻塞
表锁	MyISAM引擎，开销小，加锁快，不会出现死锁。粒度大并发小
行级锁	InnoDB引擎，开销大，加速慢，会出现死锁。粒度小，并发高
页面锁	BDB引擎，

全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份，如果备份不是一个逻辑时间点，那么视图(read view)是逻辑不一致的(破坏了隔离性)，导致数据不一致。所以备份数据时，应该生成一个全局事务，保证数据一致性。

mysqldump –single-transaction

表锁

显示表锁

显示表锁，在客户端断开连接时或使用unlock tables 可以释放锁。

显式读表锁

• 读锁不互斥
• 读写锁互斥
• 写写锁互斥

# x client
lock table a read; #显式加锁，是不会自动释放锁的

# xy client
select * from a ; #执行成功

#x client
select * from b; #执行失败，因为x客户端没有释放锁，因为系统默认会给x客户端对b表加锁，系统提示b表加锁失败。

#y client
select * from b;  #执行成功，因为y客户端没有加读锁

#x client
update a set name=1 where id=1; #执行失败，提示a表加了读锁，不能写

#y client
update a set name=1 where id=1; #执行阻塞，等待x客户端释放锁

显式写表锁

# x client
lock table tb write;

#x client
select #执行成功
insert #执行成功
update #执行成功

#y client
select #任何表都执行阻塞
insert #任何表都执行阻塞
update #任何表都执行阻塞

查看锁的情况

show open tables;
show status like "Table_locks%"

隐式表锁：MDL(matedata lock)元数据锁

MDL 是隐式表锁，是mysql加上的，无需用户显示添加

• MDL 读锁：当对一个表做**增删改查操作(DML)**的时候加上读锁；
• MDL 写锁：当要对**表结构变更操作(DDL)**的时候加上写锁。

互斥情况(所有读写锁互斥情况都是一样的)：

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

# MDL 隐式读锁
select * from tb;	#这样就默认加上了读锁

# MDL 隐式写锁
alter table tb add columns newColumn int(8)

行级锁

显示/隐式行锁

行锁的对象是数据行，InnoDB支持行锁，锁类型又分：

• 共享锁(S)：读锁，多个事物对于同一个数据可以都加上读锁，只能读不能修改
• 排他锁(X)：写锁，一个事物对一个数据添加了排他锁，其他事务就不能在对这个条数据加锁(XS都不行)，阻塞等待

update,delete,insert这些当前读语句，innodb引擎会隐式加上排他锁，事务提交后会解锁，这称为两阶段锁。

innoDB的每一个更新语句都会开启了一个新事务。如果autocommit=0，则事务不会被自动commit。autocommit默认是1

显示加锁

共享锁: select * from tb where a=1 lock in share mode;
排他锁：select * from tb where a=1 for update;

隐式加锁，并且不自动提交

#关闭自动提交，
set autocommit=0

#clinet x
update tb set b=1 where a=1;	#加上了行锁，并且没有自动提交
#clinet y
update tb set b=2 where a in (1,2,3,4,5);	#阻塞
#client x
commit;		#client y阻塞执行

行锁升级成表锁

因为mysql的行锁是作用于索引树上的，并不是数据页内的数据，当where条件后 没有索引 或者 索引失效，就会导致锁升级成 表锁

间隙锁

使用范围条件时，并请求使用共享锁或者排他锁。innodb会给符合的条件加上锁，对于符合条件又不存在的行也会加上锁，叫做间隙锁

#client x
select id from tb<5 for update;
#response
1,3,5存在，加上了写锁
2，4不存在，但是满足条件，就加上了间隙锁

#client y
insert into tb (id, a) values (2,1);	#client y阻塞，因为id=2被加上了间隙锁，等待client x 提交

作用：
为了防止幻读，A事务内第一次读a>100只有2条数据，如果没有间隙锁把a>100的锁定，那么B事务内插入一条数据并提交，此时A事务再去读就会导致看到了3条数据，那么就没有了隔离性。有了间隙锁B事务就会阻塞等待A事务提交。
不过这种加锁机制，会导致大量请求阻塞等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，需要尽量用相等条件来访问更新数据，避免范围条件。

Next-Key锁（行锁+间隙锁）

AUTO-INC锁

在执行插入语句时先加上表级别的AUTO-INC锁，插成功了立即释放，不需要等事务提交

innodb行锁争用情况

show status like "innodb_row_lock%"

innodb 死锁

死锁

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁

事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。

死锁策略

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

其实最终还是要通过更改业务代码来避免死锁，尽量使用短事务，把update统一后移

索引

索引概述

索引就是一种便于查找的数据结构，节点使用地址引用指向对应的数据块

索引的优缺点

优点

• 提高查询效率，降低IO成本(不需要把每个数据块数据查询出来，只需要查找内存中的索引，然后在去对应地址取数据)
• 索引已经对数据进行排序，降低排序成本，降低CPU消耗

缺点

• 会占用空间
• 更新删除表时，同时需要更新索引

索引分类

依据索引字段个数划分

• 单列索引
• 复合索引(多列索引)

聚簇索引 / 非聚簇索引

• 聚簇索引
  • 如果有主键PK，PK及聚簇索引
  • 如果没有PK，那么第一个not null unique列是聚簇索引
  • 如果上面两个都没有，则mysql会有一列隐藏row_id作为索引
• 非聚簇索引(普通索引)

唯一性

• 唯一索引：1列 或者 多列 时，他们的组合是有唯一性的，不能重复

查询

• 覆盖索引，查询的字段都在一颗索引B+树上，称为覆盖索引

索引结构

mysql包含的索引结构

• B+TREE
• HASH
• R-TREE
• Full-Text

B+Tree

B+TREE是一个N叉数。N叉数可以减少访问磁盘的次数，例子：

一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。

聚簇索引

extra_type : Using index

每个innodb表一定会有一个聚簇索引，

• 非叶子节点存放的是PK的值
• 叶子节点存放的是行数据，所以使用聚簇索引查询不需要回表

普通索引

extra_type : Using index condition

• 非叶子节点存放的是单列数据或多列数据

• 叶子节点存放的是 主键值

因为叶子节点存放的不是行数据，所以需要查找主键后，再回聚簇索引查找一次数据。这个过程称为回表查询

普通+其他无索引列条件

extra_type : Using index condition; using where

如果先命中了普通索引

1. 会通过普通索引查询结果集
2. 然后普通索引回表查询，获得新的结果集数据
3. 最后在结果集数据在用其他where条件进行过滤

没有命中索引条件

extra_type : Using where

直接在物理表中查询

索引创建

#创建索引
create index idx_name on tb_name(column1,column2);

#查看索引
show index from tb_name;

#删除索引
drop index idx_name on tb_name;

#alter
alter table tb_name add unique idx_name(column1,column2);
alter table tb_name add primary key idx_name(column1);
alter table tb_name add index idx_name(column1,column2);

索引设计原则

• 查询频率高，数据量大
• where条件查询较多的
• 尽量使用唯一索引
• 适当索引建立索引，不是越多越好，占的空间也大
• 使用短索引，长索引匹配也更慢
• 最左前缀原则

最左前缀(多列索引)

#创建多列索引
create index id_name on tb_name(column1, column2, column3)

#可以使用id_name这个索引的查询条件(查询条件中的字段不分先后)，下面5个条件都能命中索引，只是看mysql是否使用而已
where column1
where column1 and column2
where column1 and column2 and column3 (也可以是column3 and column1 and column2)

• 前缀索引：字符串的前N个字符作为索引键

  好处：

  • 减少索引占的空间

  影响：

  • 即使满足了覆盖索引，依然要回表查询
  • 如果长度设置不得当，会增加扫描的行数

alter table SUser add index index1(email); 		# 整个字符串的索引
alter table SUser add index index2(email(6));	# 字符串前6位索引

索引维护

新插入一条最新ID的数据，只需要在索引树的最右叶子右边加上即可，如果是需要中间加入，那么会有2种情况:

• 插入数据页没满的情况下，需要挪动树后面的节点，给新加入节点空出位置
• 插入数据页满了，需要新申请一个数据页，然后将部分数据挪到新页中

索引的使用

避免索引失效

CREATE TABLE (
    id int(10) primary key auto_incrment,
	a int(10) NOT NULL,
    b int(10) NOT NULL,
    c int(10) NOT NULL,
    d int(10) NOT NULL,
)engine=innodb default charset=utf8mb;
create index a_b_c on tb_name(a, b, c);

1、最左匹配

WHERE A=1;					#explain的 key=a_b_c	key_len = len(A)
WHERE A=1 AND B=1;			#explain的 key=a_b_c key_len = len(A)+len(b)
WHERE A=1 AND B=1 AND C=1;	#explain的 key=a_b_c key_len = len(A)+len(b)+len(c) = len(a_b_c)
WHERE C=1 AND A=1;			#explain的 key=a_b_c	key_len = len(A) 这个说明条件部分字段先后，myqsl优化器会自动调整条件字段位置，使其命中索引
WHERE B=1					#这个不会命中索引，因为不能跳过一楼，直接爬2楼。就像爬楼一样，只能一层一层的爬

2、范围条件右边的列，不能使用索引

WHERE B>1 AND A=1 AND C=1	#explain的 key=a_b_c key_len = len(A)+len(b),因为(a,b,c)c字段在b的右边

3、不要在索引列中进行运算操作，否则索引失效

WHERE (a+1)=2 	#索引失效

4.1对索引字段就行函数操作，

WHERE count(a)>0 

4.2、字符串字段不要漏了单引号，相当于给字段加上了强制类型转换函数，等同于例子4.1

#假设b字段是varchar(10)并且value=1
WHERE a=1 AND b=1		#explain的 key=a_b_c key_len=len(a),因为 b是字符串，如果改成
WHERE a=1 AND b='1'		#explain的 key=a_b_c key_len=len(a)+len(b)

5、尽量使用 覆盖搜索引，避免使用select *

• 覆盖索引：就是字段都包含在了某个索引内，这样在索引树上就有了查询的 值，不需要回表查询

EXPLAIN SELECT * FROM table WHERE a=1 AND b=1 AND C=1
#type：ref
#key：a_b_c
#extra：using index condition 查询完BTREE索引，在回表查询

EXPLAIN SELECT a,b,c FROM table WHERE a=1 AND b=1 AND C=1
#type：ref
#key：a_b_c
#extra：using where;using index 只在BTREE的叶子节点就把数据返回了

6、or分割开的条件，如果后面没有索引，则会让前面能使用索引的字段也失效

WHERE id=1 or a=1 	
#explain
#posiable_key：primary
#key: null 没命中,两个本来都会命中，但是因为or索引直接都失效了

7、以%开头的like查询，有可能导致索引失效

SELECT * FORM table WHERE a like "1%"; 	#这个能命中索引
SELECT * FORM table WHERE a like "%1%"; #这个无法能命中索引

#解决办法，就是使用覆盖索引，不让他回表查，就能命中索引
SELECT a,b,c FORM table WHERE a like "%1%";		#命中abc都在索引a_b_c中
SELECT a,b,c,d FORM table WHERE a like "%1%";	#不命中索引，d不在覆盖索引中

8、查表快的话，索引就会失效

#这个跟数据有关系，
#全表命中 或 命中90%的行

9、is null is not null 有时会索引失效

#这个跟数据有关系，
WHERE A IS NOT NULL 
#全表命中 或 命中90%的行

10、in会命中索引 not in不发命中索引

单列索引和复合索引

• 单列索引：只有一个字段的索引
• 复合索引：多个列索引，多个字段组成索引
• 覆盖索引：他和单列，复合索引的概念不一样，覆盖是指的select查询的字段是否被索引包含select a,b 或者 select a,b,c 那么，他们都是a_b_c的覆盖索引。这样在索引树上就有了查询的 值，不需要回表查询

查看索引使用情况

show global status like "Handler_red%";

优化器选择索引异常处理方案

• force index（index_name）： 强制使用某个索引
• 修改sql，诱导选择器使用另外一个index
• 删除索引，这个要综合考虑

存储引擎

存储引擎概述

• 存储数据，建立索引，更新查询数据 等等技术的实现方式
• 存储引擎基于表，而不是基于库

查看mysql支持的存储引擎

#查看存储引擎
show engines;

InnoDB 存储引擎

#创建表 并且使用innodb
create table (
id int(10) auto_increment primary key
)engine=innodb default charset=utf8;

数据文件

• .frm 存放 表结构
• .ibd 存放 数据 和 索引

特性

• 外键
• 行锁
• 事务

MyISAM

#创建表 并且使用innodb
create table (
id int(10) auto_increment primary key
)engine=myisam default charset=utf8;

数据文件

• .frm 存放表结构
• .MYD 存储数据(MY DATA)
• .MYI 存储索引(MY INDEX)

特性

• 表锁

MEMORY

特性

• 读表时，会将数据全部读入内存中，只合适数据量小且更新少的表

MERGE

特性

• 2个表结构相同的两个myisam表(A和B)，可以被一个merge引擎的表©合并起来，比如A，B插入数据，C虽然没有插入数，但是能查出表AB的数据集

日志系统

redolog 和 binlog。

redo log

它位于储存引擎层，作用：

1. 解决随机写磁盘IO
2. 当事务提交时数据库宕机，当数据库重启时将事务修改的数据同步到数据库中，依然可以不丢失之前的提交记录，这个能力称为crash-safe。

因为mysql是页式存储，需要更改某一条记录就需要把整页数据都刷新，如果每一次的更新操作都需要写进磁盘，磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。

WAL技术(Write-Ahead Logging)，就是解决上述问题，它的关键点就是先写日志，再写磁盘。

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存 buffer pool，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录flush到磁盘里面，这个时机请看下面的“两阶段提交后的flush脏页”

当写入事务 redolog作用

1. 数据会被写入内存buffer pool中(有可能是内存数据页，也可能是change buff)，避免写磁盘
2. 在写入redo log buffer中。
3. 事务提交时，redo log buffer会写入 redo log 日志中，虽然他也有写磁盘IO，但是不需要在查找数据存放在哪些数据页中，只需要追加写，就避免了随机写磁盘IO

此时redolog就已经算完成了事务。同时，InnoDB 引擎会在适当的时候，将这个操作记录flush到磁盘里面，这个时机请看下面的“两阶段提交后的flush脏页”

当mysql宕机 redolog作用 crash-safe

当mysql宕机，数据页丢失了只更改了内存的数据：

• 重启后，内存buffer pool数据丢失，所以无法从buffer pool同步数据
• 读取redolog，因为redolog是物理日志，记录了更改了哪些页有更改，此时将那些页从物理磁盘读到内存buffer pool里
• 根据redolog日志，把修改的数据同步到内存中，完成开机重启，避免了数据丢失

redolog日志结构

InnoDB的redo log 是固定大小的，例如是4个1G的文件。从头到尾的开始写入，如果超出了4G大小，那么会到头部循环写入

• write position： 当前写入位置，写入数据后后移
• check point：是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

当write pos追上了 check point，代表文件写满了，这个时候不能再执行更新语句，需要把check point后移，并把移动位置的数据刷新到mysql磁盘中。

bin log

从基础架构能篇知道，mysql大致分2层，server层和储存引擎。上面的redolog属于储存引擎的日志，binlog则属于server层的日志

binlog是没有crash-safe能力的，他只用于归档。binlog和redolog的不同：

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

查询过程

查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，然后可以认为数据页内部通过二分法来定位记录。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存buffer pool中。在 InnoDB 中，每个数据页的大小默认是 16KB。

更新数据的过程

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

两阶段提交

上面的redolog有两个状态，分别在不同步骤进行状态更改，这就是2阶段提交。

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。要说明这个问题，我们得从文章开头的那个问题说起：怎样让数据库恢复到半个月内任意一秒的状态？

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

1. 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；

2. 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。

仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，如果没有两阶段提交会出现什么情况呢？

• 先写redo log，后写binlog的情况。写完redolog，突然宕机，binlog没有写入成功。重启后通过redolog把数据刷到磁盘上，但是binlog没有，会导致主从数据不一致
• 先写binlog，后写redolog。写完binlog，宕机，redolog没有写入成功。重启后binlog同步到了从库，因为redolog没有记录所以主库没法更新这条数据，还是会导致数据不一致

两阶段提交如何解决的呢？4种情况

1. redolog prepare状态前宕机，2个记录都没写，直接就可以忽略这条记录
2. redolog 更新完prepare状态，binlog没有写入，宕机。当重启后，读redolog，是prepare状态就删掉这条日志，保证了redolog和binlog同步
3. redolog 更新完prepare状态，binlog写入,redolog没有到commit状态，宕机。当重启后删掉这条redolog日志，并且重写一条恢复语句到binlog。保持了数据一致
4. binlog写入,redolog到了commit状态，并且没有刷新磁盘，宕机。重启redolog读到的是commit状态，那么刷新到磁盘上

两阶段提交后 flush脏页

当事务提交完成后只是记录了redolog，并没有刷新到数据页中，此时内存和数据页内容不一致，我们称这个数据页叫**“脏页”**

flush就是将内存buffer pool里的数据，写入到磁盘数据页里。这里并不是根据redolog数据刷进磁盘数据页，因为redolog没有整个数据页的完整数据，只有buffer pool里面有

flush触发的时机有：

• redolog文件满了，write_postion追上了check_point，此时会拒绝所有的更改添加请求
• 内存buffer pool满了，此时要释放一些内存
• mysql空闲时
• mysql主动关机，刷新磁盘

事务

事务的4个特性ACID

• 原子性Atomicity：要么全部成功，要么全部失败
• 一致性Consistency：数据必须保持全部正确
• 隔离性Isolation：事务间互不影响 MVCC来保证，undolog日志和readview视图来实现MVCC
• 持久性Durability：binlog日志显示全量，redolog和WAL(Write Ahead Logging)来保证宕机不丢失数据

查询过程

查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，然后可以认为数据页内部通过二分法来定位记录。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存buffer pool中。在 InnoDB 中，每个数据页的大小默认是 16KB。

事务隔离级别

• read uncomitted 读未提交：A事务能读到B未提交事务数据，脏读
• read committed (rc)读已提交：解决了脏读，会出现 不可重复读，A事务读了某个索引，B事务修改了改索引并且提交，A事务再去读取时数据不一致
• repeatabel read (rr)可重复读：解决了不可重复读，会出现 幻读。在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。
  • 幻读：幻读只有在当前读的情况下才会出现，强调的是结果集的个数
  • 不可重复读：强调的是结果集中字段的内容变更
• Serializable(可序化)

快照读和当前读

快照读（普通读）

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制和undo log实现，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

当前读

也称锁定读(locking read),通过对读取到的数据(索引记录)加锁来保证数据一致性，当前读会对所有扫描到的索引记录进行加锁，无论该记录是否满足WHERE条件都会被加锁。

当前读语句：

• select for update ,
• select lock in share mode
• insert，update ，deleted【都具有排他锁】，他们读取的都是当前最新版本的有效数据，因为他们操作时是会竞争排他锁的，保证他们是最新有效的数据。所以当前读是基于临建锁(next lock)实现的。next lock = 行锁 + 间歇锁

更新数据时，都是先读后写，这个读就是当前读。读取该条数据的已经提交的最新的事务生成的readview

MVCC（Mulit-version Concurrency Control）

MVCC：多版本并发控制，实现对数据库的并发访问。MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

MVCC实现原理

它的实现原理主要是依赖记录中的 4个隐式字段，undo日志 ，Read View 来实现的。

隐式字段

• DB_TRX_ID：最新修改这条数据的事务ID(包括提交和未提交)
• DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本
• DB_ROW_ID：数据库隐含的自增ID,
• DEL_FLAG：软删除数据的标记位

undo日志

undo log主要分为两种：

• insert undo log
  代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
• update undo log
  事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

purge

• 从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。
• 为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

对MVCC有帮助的实质是update undo log ，undo log实际上就是存在rollback segment中旧记录链，它的执行流程如下：

1. 比如一个有个事务插入person表插入了一条新记录，记录如下，name为Jerry, age为24岁，隐式主键是1，事务ID和回滚指针，我们假设为NULL

2. 现在来了一个事务1对该记录的name做出了修改，改为Tom

• 在事务1修改该行(记录)数据时，数据库会先对该行加排他锁

• 然后把该行数据拷贝到undo log中，作为旧记录，既在undo log中有当前行的拷贝副本

• 拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务ID为当前事务1的ID, 我们默认从1开始，之后递增，回滚指针指向拷贝到undo log的副本记录，既表示我的上一个版本就是它

• 事务提交后，释放锁

  

3. 又来了个事务2修改person表的同一个记录，将age修改为30岁

• 在事务2修改该行数据时，数据库也先为该行加锁

• 然后把该行数据拷贝到undo log中，作为旧记录，发现该行记录已经有undo log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面

• 修改该行age为30岁，并且修改隐藏字段的事务ID为当前事务2的ID, 那就是2，回滚指针指向刚刚拷贝到undo log的副本记录

• 事务提交，释放锁

  

从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该undo log的节点可能是会purge线程清除掉，向图中的第一条insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）

巨人的肩膀：

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

READ VIEW(读视图)

Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

概念

read view主要有4个部分组成

• creator_trx_id：创建整个事务的事务id
• m_ids：未提交的事务id
• min_trx_id：最小未提交的事务id
• max_trx_id：下一个事务时生成的id(max_trx_id = current_max_trx_id + 1)

版本链对比规则

• 如果undo_log行的隐藏字段db_trx_id落在 小于 min_trx_id（未提交的最小事务id），那么此行是已提交的事务，数据可见

• 如果undo_log行的隐藏字段db_trx_id落在 大于max_trx_id（当前mysql最大的事务id），那么表示版本是由将来要启动的事务生成，数据不可见

• 如果undo_log行的隐藏字段db_trx_id落在min_id < trx_id < max_id

  • 情况1：落在数组中，则表示版本是由还没提交的事务生成，其他事务不可见，当前事务可见
  • 情况2：没落在数组中，这个版本是已提交了的事务生成，可见

可重复读隔离级别实现

• readview的生成时间：在创建事务后并第一次快照读时生成的readview不会改变！！！

• 不改变readview，避免不可重复读的原因：开启B事务再开启A事务，在A事务生成快照读并在提交前，B事务提交了修改x行内容，A事务根据不变的readview还是会判定B事务并未提交，保证了A事务中x行内容的可重复读

mvcc为基础，加部分条件

因为是要读到快照时的数据，所以undo_log读到数据的db_trx_id应该满足条件

2. 小于当前事务id undo_log.db_trx_id < creator_trx_id
3. 不在快照读readview的未提交事务内 not in (m_ids)
4. 从上往下数，满足条件1，2，3

幻读的出现

幻读:

• 在可重复读隔离级别下，普通的读取是快照读，是不会看见其他事务的修改和增加，所以是不会发生幻读的。所以幻读只在当前读才会发生。当前读（select for update, select lock in share mode, update , insert 等语句是当前读，可以理解为上锁的语句）
• 事务A的锁定条件(where d= 5)获得的行 有可能在 事务B操作后，事务A行数增加，这就是幻读

例子1：

1. 开启B事务，开启A事务
2. A事务当前读, select * from t where d=5 for update只作用于x条数据
3. B事务插入一条数据并且提交，insert into t (a,d)value(2,5); 或者 update t set a=3 where d=5;
4. 当A事务再次select * from t where d=5 for update，是会读到x+1条数据的。select for update当前读
5. A事务update set c=0 where d=5;会作用于x+1条数据。update也是当前读
   例子2：

[幻读实例]
实例1：https://learn.lianglianglee.com/%E4%B8%93%E6%A0%8F/MySQL%E5%AE%9E%E6%88%9845%E8%AE%B2/20%20%20%E5%B9%BB%E8%AF%BB%E6%98%AF%E4%BB%80%E4%B9%88%EF%BC%8C%E5%B9%BB%E8%AF%BB%E6%9C%89%E4%BB%80%E4%B9%88%E9%97%AE%E9%A2%98%EF%BC%9F.md

实例2 ： https://blog.csdn.net/wdj_yyds/article/details/131897705

解决幻读

todo： 间隙锁 和 next-key lock，当前读就会自动加上间隙锁和next-key锁

读已提交隔离级别实现

readview的生成时间：readview是会改变的，每次快照读时，是会更改该事务的readview！！！

快照读都会改变readview导致了不可重复度的原因：开启B事务再开启A事务，在A事务生成快照读并在提交前，B事务提交了修改X行的内容，当A事务再次快照读时，生成了新readview（readview会在未提交事务id集合中去掉的B事务id），就会判断B事务已提交，那么A事务两次读X行的内容就会不同，导致了不可重复读

mvcc为基础，加部分条件

因为是要读到快照时的数据，所以undo_log读到数据的trx_id应该满足条件

2. 小于当前事务id undo_log.trx_id < creator_trx_id
3. 不在快照读readview的未提交事务内 not in (m_ids)
4. 从上往下数，满足条件1，2，3

巨人的肩膀：

change buffer

innodb引擎数据存储是页式存储，所以读取时也是整页读取后，存放到内存中(buff pool)。

change buffer 是什么

当更改数据时，更新数据页不在内存中，为了避免随机读磁盘IO访问，在不影响数据一致性的前提下，更改内容会写入change buffer中。等之后有其他请求读到了这个数据页，并把数据页写入内存时，直接从change buffer合并到内存即可

随机读磁盘IO：数据在哪一些如果没有索引是不知道的，所以会去读数据页对比，这就有可能读很多也才可能找到，导致内存

他是一个内存存储，并且也是一个可以持久化数据

何时会使用到change buff

如果需要update数据时，

• 当修改的是内存内的数据时，就不需要直接修改内存数据，mysql会自行调度刷新到磁盘上
• 如果不在内存时，mysql并不是读取页，而是写到change buffer内，当下次数据页读到了内存中，在merge到内存中，用此方法来减少磁盘IO。此时数据页和内存中的数据是不一致的，此数据页也叫脏页

change buffer merge数据时机

• 下一个select 或者 update where 唯一索引时，会将数据页读到内存中，此时merge数据
• 系统后台线程定期更新
• mysql正常退出时

引出 唯一索引和普通索引的选择

• 唯一索引：因为唯一索引在update数据时，需要判断数据的唯一性，所以会需要先查找数据页，读入内存中，在进行数据比较。所以他是不需要进行change buffer的
• 普通索引：查找数据时无需比较，直接更新到change buffer中，等待下次merge数据

所以唯一索引的内存使用率比普通索引的内存使用率低

change buffer 和 redo log

他们是不是功能一样的？都是先找一个地方暂存，最后一次性更新数据。

还记得redolog是物理日志吗？他存放的就是：这些数据应该写入哪些页中，下图是修改page1和page2的数据过程

解释:

1. 添加数据(id1,k1)，innodb策略他是写入page1中，page1刚好在内存中(buffer pool)，所以直接更新内存
2. 添加数据(id2,k2)，innodb策略他是写入page2中，page2没有在内存中，写入change buffer
3. 写入和更改数据时，会插入redo log日志，所以步骤1也是把 add(id1,k1) to page1 写入redolog
4. 步骤2也会把 new change buffer item add(id2. k2) to page2写入redolog

总结:可以理解为，change buffer只到内存数据一致，redolog是到宕机后重启内存数据一致

count原理

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

MyISAM引擎

有一个统计字段，如果没有where条件会直接返回该字段，如果有条件还是会扫描全表

Innodb引擎

扫描全表，为什么，因为有事务，每个事务看到的总条数会不一样，还要判断版本链信息

解决办法

• 存缓存系统，累加，因为redis+mysql没有原子性，所有在并发系统下有可能出现数据不一致
• 存DB系统，开事务完成原子性，但是要新建一个表来存，还是算了把

不同的 count 用法

• 对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server -层拿到 id 后，判断是不可能为空的，就按行累加。

• 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

• 对于 count(字段) 来说：

  • 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
  • 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。

• 但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()，所以我建议你，尽量使用 count()。

order by 排序原理

我们可以通过explain sql来查看语句的执行情况

如果extra字段出现了using filesort表示需要排序。mysql会在buffer pool中分配一块内存用于排序，叫做sort_buffer。

如果聚簇索引返回需要排序的结果集小于参数sort_buffer_size，则会在内存完成排序，如果大于参数就会使用临时文件

全字段排序

全字段排序，就是把要查找的字段全部插入sort_buffer中

下面以这个sql为例，做讲解

select city,name,age from t where city='杭州' order by name limit 1000  ;

语句执行流程如下：

1. 开启sort_buffer，确认放入字段(city, name, age)
2. 从city索引找到满足key='杭州’条件的主键
3. 回表根据主键获取行数据，如果sort_buffer
4. 在回到city索引，根据步骤2位置，匹配下一个叶子节点是否满足条件，如果满足循环步骤2，3。不满足就停止匹配
5. 对sort_buffer中的数据按字段name进行快速排序
6. 按照排序结果取出1000行数据返回客户端

rowid排序

rowid排序，当要插入sort_buffer中的字段过长时，导致sort_buffer超出界值时，mysql会只将排序字段和主键ID插入sort_buffer，这就叫做rowid排序

下面以这个sql为例，做讲解

select city,name,age from t where city='杭州' order by name limit 1000  ;

语句执行流程如下：

1. 开启sort_buffer，确认放入字段(name, id)
2. 从city索引找到满足key='杭州’条件的主键
3. 回表根据主键获取行数据，如果sort_buffer
4. 在回到city索引，根据步骤2位置，匹配下一个叶子节点是否满足条件，如果满足循环步骤2，3。不满足就停止匹配
5. 对sort_buffer中的数据按字段name进行快速排序
6. 按照排序结果取出1000行数据，并按照id的值回到原表取出其余字段返回客户端

全字段排序和rowid排序对比

• 全字段少一次回原表查看字段，减少了一次磁盘IO，优先选用
• rowid排序减少了内存使用量

内存临时表

mysql> select word from words order by rand() limit 3;

随机取出words表中的3行数据，explain sql：

• Using temporary，表示的是需要使用临时表
• Using filesort，表示的是需要执行排序操作。

加入表中有10000个数据，语句执行流程：

1. 创建临时表，使用的时Memory引擎，所以全是内存操作。存入了2字段，第一个字段是R double类型，第二个字段是W varchar64类型
2. 从words表中，取出word值，并每一行都随机生成以0-1的double类型的值，插入R,W字段中，此步骤扫描了原表的10000行数据
3. 在临时表中，根据R字段排序
4. 初始化sort_buffer，有2个字段，一个是double类型，一个是整数类型
5. 将临时表的数据取出R字段(double类型)和位置信息(int类型，其实就是一个表的主键)，放到sort_buffer中。此步骤又扫描了临时表的10000行数据，总共2w行
6. sort_buffer根据double类型字段排序
7. 排序完成后，取出3条(limit 3)数据的位置信息，依次到内存临时表中取出word值，返回客户端。此步骤又扫描了3行数据，总共20003行

通过慢日志验证，扫描行数

# 开启慢查询
set global slow_query_log='ON';
# 设置慢查询时间为0
set global long_query_time = 0;

# Query_time: 0.900376  Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003
SET timestamp=1541402277;
select word from words order by rand() limit 3;

磁盘临时表

tmp_table_size 字段限制了临时内存表的大小，默认是16m。如果超出就会转成磁盘临时表，磁盘临时表的引擎是innodb。

查询为什么一行数据也会慢

查询一条数据也会慢的原因有很多

• MDL表锁，读写锁互斥
• 等待flush，redolog日志满了
• 查询如果是当前读(select for update , select lock in share mode)，等待其他事务完成
• 死锁，2个事务循环修改
• 慢查询

binlog主备同步

同步主备过程

主备同步是通过binlog日志来实现的。

1. 主库有一个单独的线程来处理从库的连接
2. 从库发起同步请求 start slave
3. 主库验证身份信息后，开始按照备库 B传过来的位置，从本地读取 binlog，发给备库
4. 备库获得binlog转存到relay log中专日志
5. 备库的sql_thread线程读取relay log，解析文件的命令，并执行

binlog的3中格式

statement

statement格式：存放的是原始sql，缺点是：即使是原始sql也会导致数据不一致

例如：delete from t where a>=4 and t_modified<='2018-11-10' limit 1;

limit只是取其中一个，因为主备库的有可能使用到的索引不同，主库只用a索引，备库使用t_modifyed索引，他们的第一条信息不一定是一样的，所以会可能删除的数据不是同一条

row（推荐使用）

row格式：存放的是操作的行主键和数据变更，需要用mysqlbinlog 命令来解析文件。

优点：是没有数据不一致的情况。通过mysqlbinlog可以查看到原始数据，这样误删，误改还能手动恢复。而statement只有一条sql

缺点是占用的空间大。如果删除了1w行数据，那么这一条sql就会占用很多空间，而statement只需要记录一条sql

mixed

MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式

互为主备库

互为主备，就能做到主备库都可以写入数据。

但是有 循环复制 问题：

1. 主备都开启了binlog。
2. 主库修改了数据，写入binlog，传送给了备库
3. 备库读到relay log，写入数据，又记录到备库的binlog。
4. 因为互为主备，导致主库又拿到备库的binlog更改，又做了同样的修改，这个就是循环复制

主从延迟

主从延迟：备库同步binlog，和从库出现了时间差。

在库备执行show slave status;命令，返回seconds_behind_master 字段值是主备延迟的时间差

下面是主从延迟的3个原因

• 备库性能差：备库配置低，因为更改和插入语句(当前读) 也会产生大量的读操作，所以当备库主机上有多个备库时，就会有资源争抢，导致主备延迟。

一般主备主机都是相同，因为需要做主从切换，所以一般不会是这个问题

• 备库压力大：因为只有主库有写权限，所以很多公司会拿备库来读，也有一些营运团队需要一些数据，做一些非正常查询，导致备库压力大

解决办法：一主多从，分摊压力

• 大事务：一个事务需要执行很长时间，当主库提交了事务完成后，备库同步也需要执行很长时间，这段时间就会出现主从数据不一致。典型案例就是执行DDL语句

主从切换策略

• 可靠性优先策略
• 可用性优先策略

可靠性优先策略

可靠性优先：数据一致性 大于 mysql可用性，主备库会有段时间只读，来避免切换时数据不一致

流程：

1. 判断备库 B 现在的主从延迟时间 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；【手动执行show slave status】
2. 把主库 A 改成只读状态，即把 readonly 设置为 true；
3. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
4. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
5. 把业务请求切到备库 B。

在步骤4之前，主备库有时readonly状态，写入业务会挂掉。但是保证了主库的所有数据都同步到了备库

可用性优先策略

可用性优先：mysql可用性 大于 数据一致性

流程：

1. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
2. 把业务请求切到备库 B。

一步直接切换了主从，因为主从延迟，主库有可能还有数据未同步到备库，所以有可能数据不一致。但是系统是一直可访问的

如何安全的给表加字段

因为MDL锁 直接的读写锁互斥，当表结构发生变化时，server会自动给表加上MDL写锁，之后其他session的增删改查(会自动加上MDL的读锁)会被阻塞。为了避免业务被阻塞，需要注意：

• 去myqsl的information_schema库的innodb_trx表看看有没有长事务，等事务结束后再执行
• 如果是热点库，那长事务也是会一直有的。这个时候就再alter table语句加上等待时间

深分页问题

问题出现

select * from table where name="a" limit 10000,10
某些管理后台数据展示页，当翻页到了几百页之后，数据查询就会很慢，这就是深分页问题。

原因

每次非聚簇索引查询时，从索引树上拿到 主键id，还需要回表查询一次数据行内容，当查询到N条数据，就需要回表N次，所以查询效率就会很低。

解决办法

关键就是要 避免多次回表。所以就是尽量使用聚簇索引。可是管理后台需要展示的数据会很大，dba不会允许开发将一个多列索引设计得很大。所以就需要使用一下方法：

select * from table where  id in 
(
 selelct id from table where name='a' limit 10000, 10
 )

这种方法子查询虽然也用到了limit 10000,10，但是他只需要在name索引树上查询，并不需要回表查询。避免了回表查询得开销，所以速度会有所提升