02-MySQL_mysql数据结构

一、MySQL底层数据结构及算法

1. MySQL索引

索引是帮助MySQL高效获取数据的排好序的数据结构

2. 索引的数据结构

• 二叉树
• 红黑树
• Hash表
• B-tree

3. B-Tree结构

• 叶子节点具有相同的深度，叶子节点的指针非空
• 所有索引的元素不重复
• 节点中数据索引从左到右递增排列

4. B+Tree结构(B-Tree的变种)

• 非叶子节点不存储数据data，只存储索引(冗余)，可存放更多的索引
• 叶子节点包含所有的索引字段
• 叶子节点用指针连接，提高区间访问的性能

5. Hash结构

• 对索引的key进行一次Hash运算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+树索引更高效
• 仅能满足“=”，“in”，不支持范围查询
• Hash冲突问题

二、MySQL存储引擎

1. MyISAM存储引擎

myISAM存储引擎文件和数据是分离的(非聚集索引)

• .frm文件：存储表结构
• .MYD：存储数据
• .MYI：存储索引及对应内存地址

2. InnoDB存储索引

• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引-叶子节点包含了完整的数据记录

3. MyISAM存储引擎和InnoDB存储引擎的区别

• MyISAM不支持事务，InnoDB支持事务
• MyISAM只支持表锁，InnoDB支持行锁和表锁
• MyISAM不支持外键，InnoDB支持外键
• 在删除的时候MyISAM是重新建表，而InnoDB是一行一行的删除

4. 为什么建议InnoDB表必须建主键，并且推荐使用整形自增主键？

• 建主键
  • 建立主键，MySQL可以通过主键来建立B+树，如果没有主键索引，MySQL会在每个列中找一个不相同的列来作为索引
  • 如果没有不同的数据的列，MySQL会建一个隐藏列来维护数据，这样就增加了MySQL的工作量，影响MySQL效率
• 整形主键自增
  • 整形占用空间小，节省空间
  • 整形便于比较大小，提高性能
  • 自增主键，在树插入的时候总是在后面新增元素，不需要在插入不连续的元素的时候，导致树分裂平衡，提高效率

5. 为什么非主键索引结构叶子节点存储的是主键值？

• 一致性
• 节约存储空间

6. 联合索引底层存储结构长什么样？

• 先比较name，再比较age，position

三、Explain执行计划

1. Explain介绍

• 使用Explain关键字可以模拟优化器执行SQL语句，分析查询语句或是结构的性能瓶颈
• 在select语句之前加上Explain关键字，MySQL会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL
• 如果form中包含子查询，任会执行该子查询，将结果放入临时表中

2. Explain中的列

• id：id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按照select出现的顺序增长的，id列越大，优先级越高，id相同则从上往下执行，id为null，则最后执行
• select_type：表示对应的行是简单查询还是复杂查询
• simple：简单查询，查询不包含子查询和union

# simple例子：
explain select * from film where id = 2;
1
2

• primary：复杂查询中的最外层select
• subquery：包含着select中的子查询(不在from子句中)
• derived：包含在form子句中的子查询，MySQL会将结果存放在一张临时表中，也称派生表

# primary 、subquery、derived例子
set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合 并优化
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der; n
1
2
3

set session optimizer_switch='derived_merge=on'; #还原默认配置
1

• union：在union中的第二个和随后的select

# union例子
explain select 1 union all select 1;
1
2

• table：表示Explain的一行正在访问哪个表
• 当form子句中有子查询时，table列是格式，表示当前查询依赖id=n的的查询，于是先执行id=n的查询
• 当有union时，UNION RESULT 的table列的值为<union1,2>，1和2表示参与union的select列id
• type：表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围
• 依次从最优到最差分别为：system -> const -> eq_ref -> ref -> range -> index -> All
  • 一般来说，得保证查询达到range级别，最好达到ref
• null：MySQL能够在优化阶段分解查询语句，在执行阶段用不着在访问表或索引。例如，在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

explain select min(id) from film;
1

• const，system：mysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings的结果)。用于primary key或者unique key的所有列与常数作比较时，所以表最多有一个匹配行，读取1次，速度比较快，system是const的特例，表里只有一条元组匹配时为system

 explain select * from (select * from film where id = 1) tmp; 
1

show warnings;
1

• eq_ref：primary key或unique key 索引的所有部分被连接使用，最多会返回一条符合条件的记录，这可能是在const之外最好的连接类型了，简单的select查询不会出现这种type

 explain select * from film_actor left join film on film_actor.film_id = film.id;
1

• ref：相比于eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
• 简单的select查询，name是普通索引(非唯一索引)

explain select * from film where name = 'film1'; 
1

• 关联表查询，idx_film_actor_id是film和actor_id的联合索引，这里使用到了film_actor的左前缀film_id部分

explain select film_id from film left join film_actor on film.id = film_actor.film_id; 
1

• range：范围扫描通常出现在int()，between，>，<，>=等操作中。使用一个索引来检索给定范围的行

explain select * from actor where id > 1;
1

• index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点进行遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比All快一些

explain select * from film; 
1

• All：全表扫描，扫描聚簇索引的所有叶子节点，通常情况下这需要添加索引来进行优化了

explain select * from actor;
1

• possible_keys：这一列显示查询可能使用那些索引来查找
  • Explain可能出现possible_keys有列，而key显示null的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询
  • 如果是该列为null，则没有相关的索引，这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用Explain查看效果
• key：这一列显示MySQL实际采用哪个索引优化对该表的访问
  • 如果没有使用索引，则该列是null，如果想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用force index、ignore index
• key_len：这一列显示了MySQL在索引列里面使用的字节数，通过这个值可以算出具体使用了索引中的哪些列
  • 例如：film_actor的联合索引 idx_film_actor_id 由film_id 和 actor_id 两个int组成，并且每个int是4个字节，通过结果中的key_len=4可以推断出查询使用了第一列：film_id列来进行查找

explain select * from film_actor where film_id = 2; 
1

• key_len的计算规则如下：
• 字符串：，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占用1个字节，一个汉字占用3个字节
• char(n)：如果存汉字长度就是3n字节
• varchar(n)：如果存汉字则长度是3n+2字节，加的2字节是用来存储字符串长度，因为varchar是变长字符串
• 数值类型：
  • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节
• 时间类型：
  • data：3字节
  • timestamp：4字节
  • datatime：8字节
  • 如果字段允许为null，需要1字节记录是否为null
  • 索引最大长度为768字节，当字符串过长时，MySQL会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引
• ref：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const(常量）、字段名(film.id)
• rows：这一列是MySQL估计要读取并检测的行数，注意这个不是结果集里的行数
• Extra：这一列展示的是额外信息，常见的重要提示如下：
  • Using index：使用覆盖索引

覆盖索引定义：MySQL执行计划Explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树种获取，这种情况一般可以说是用到了覆盖索引，Extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引找到主键，再通过主键去主键索引树中获取其他字段的值

explain select film_id from film_actor where film_id = 1; 
1

• Using where：使用where语句来处理结果，并且查询的列未被索引覆盖

 explain select * from actor where name = 'a'; 
1

• Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导的范围

explain select * from film_actor where film_id > 1;
1

• Using temporary：MySQL需要创建一张临时表来处理查询，出现这种情况一般是要进行优化的，首先想到的就是用到索引来优化

actor.name没有用到索引，此时创建了张临时表来distinct

explain select distinct name from actor; 
1

film.name建立了idx_name索引，此时查询是extra是Using index，没有临时表

explain select distinct name from film;
1

• Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序，这种情况下一般也是考虑使用索引来优化

actor.name：未创建索引，会浏览整个表，保存排序关键字name和对应的id，然后排序name并检索行记录

 explain select * from actor order by name;
1

file.name建立了idx_name索引，此时查询时extra是Using index

explain select * from film order by name; 
1

• Select tables optimized away：使用某些聚合函数(比如max，min)来访问存在索引的某个字段时

explain select min(id) from film; 
1

四、索引优化

1. 索引优化实践

• 全值匹配
• 最左前缀原则
• 不在索引列上做任何操作(计算，函数，自动或手动类型转换)，会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引(只访问索引的查询(索引列包含查询列))，减少select * 语句
• MySQL在使用不等于(! = 或者 < > ) ，not in ，not exists的时候无法使用索引会导致全表扫描
• < 小于 ，> 大于， <= ，>= 这些，MySQL内部优化器会根据检索比例，表大小等多个因素整体评估是否使用索引
• is null ，is not null 一般情况也不会走索引
• like以通配符开头，MySQL索引失效会变成全表扫描
  • 解决like %字符串% 索引不能被使用的方法
    • 使用索引覆盖，查询字段必须是建立覆盖索引字段
    • 如果不能是覆盖索引则可能借助搜索引擎
• 字符串不加单引号索引失效
• 少用or 或者 in，用它查询时，mysql不一定使用索引，MySQL内部优化器会根据索引比例，表大小，等多个因素整体评估是否使用索引
• 范围查询优化
  • 例如，给年龄添加单值索引

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ; 
explain select * from employees where age >=1 and age <=2000; 
1
2

• 没有走索引的原因：MySQL内部优化器会根据检索比例，表大小等因素整体评估是否走索引。这个可能是单次数据量查询过大导致优化器最终选择不走索引
• 优化方法：可以将大的范围拆分为多个小范围

explain select * from employees where age >=1 and age <=1000; 
explain select * from employees where age >=1001 and age <=2000; 
1
2

# 删除索引
ALTER TABLE `employees` DROP INDEX `idx_age`; 
1
2

2. 索引常用总结

like KK%相当于=常量，%KK和%KK% 相当于范围

3. order by 和 group by 优化总结

• MySQL支持两种方式的排序，filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低
• order by 满足两种情况会使用Using index
  • order by语句使用索引的最前列
  • 使用where子句与order by子句条件列组合满足索引最左前列
• 尽量在索引上完成排序，遵循索引建立(索引创建的顺序)时的最左前缀法则
• 如果order by的条件不在索引列上，就会产生Using filesort
• 能用覆盖索引尽量用覆盖索引
• group by 与 order by 类似，其实质就是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序，注意：where 高于having，能写在where中的限定条件就不要用having去限定了

3.1. Using filesort文件排序原理

• filesort文件排序方式
  • 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序，用trace工具可以看到sort_mode信息里显示 < sort_key，additional_fields > 或者 < sort_key，packed_additional_fields >，详细过程如下：
    • 从索引name找到第一个满足name="zhangsan"的条件的主键id
    • 根据主键id取出整行，取出所有字段的值，存入sort buffer中
    • 从索引name找到下一个满足name满足name = "zhangsna"条件的主键id
    • 重复上面两个步骤，直到不满足name=“zhangsan”
    • 对sort buffer中的数据按照字段position进行排序
    • 返回结果给客户端
  • 双路排序：又叫回表排序模式，是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行id，然后在sort buffer中进行排序，排序完成之后再取出其他需要的字段，用trace工具可以看到sort_mode信息里显示 < sort_key，rowid >，详细过程如下：
    • 从索引name找到第一个满足name="zhangsan"条件的主键id
    • 根据主键ID取出整行，把排序字段position 和主键ID这两个字段放到sort buffer中
    • 从索引name取出下一个满足name = "zhangsna"记录的主键id
    • 重复上面两个步骤，知道不满足name=“zhangsan”
    • 对sort_buffer中的字段按照position排序
    • 遍历排序好的id和字段position，按照id的值回到原表中取出所有字段的值返回给客户端
• mysql 通过比较系统变量 max_length_for_sort_data（默认1024字节）的大小和需要查询的字段总大小来判断使用哪种排序模式
  • 如果字段的总长度小于max_length_for_sort_data，那么使用单路排序模式
  • 如果字段的总长度大于max_length_for_sort_data，那么使用多路排序模式
• 总结
  • 单路排序模式会将所有需要查询的字段都放到sort buffer中，而双路排序只会把主键和需要排序的字段放入sort buffer中进行排序，然后再通过主键回到原表中查询需要的字段
  • 如果MySQL排序内存sort buffer配置的比较小并且没有条件继续增加了，可以适当把max_length_for_sort_data设置小点，让优化器去选择双路排序算法，可以在sort buffer中一次排序更多的行，只是需要再根据主键回到原表取数据
  • 如果MySQL的排序内存有条件可以配置比较大，可以适当增大max_length_for_sort_data 的值，让优化器选择单路排序，把需要的字段全部放到sort buffer中，这样排序后就会直接从内存里返回查询的结果
  • 所以，MySQL通过max_length_for_sort_data这个参数来控制排序，在不同的场景使用不同的排序方式，从而提升效率
  • 注意：如果全部使用sort buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort buffer(默认1M)，MySQL很多参数设置都是做过优化的，不要轻易调整

3.2. 索引设计原则

• 代码先行，索引后上
  • 一般是等主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析后再建索引
• 联合索引尽量覆盖条件
  • 设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql里的where、order by、group by字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则
• 不要在小基数字段上建立索引
  • 索引基数是指这个字段在表中总共有多少不同的值，比如一张表中有100万行记录，其中有个性别字段，其值不是男就是女，那么这个字段的基数就是2
  • 如果对这个字段建立索引的话，还不如全表扫描，因为索引树中就包含男和女两个值，根本没有办法进行二分查找，那用索引就没有太大意义
  • 一般建立索引，尽量使用那些基数比较大的字段，这样才能发挥出B+树快速二分查找的优势
• 长字符串可以使用前缀索引
  • 尽量使用字段类型较小的列设计索引，因为字段类型小的话，占用磁盘空间也会小，在搜索的时候性能也会好一点，但是很多时候我们需要对varchar(255)来建立索引
  • 对于这种大字段可能比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引
  • 就是说对这个字段里的每个值前20个字符放在索引树里，类似于 KEY index(name(20),age,position)
  • 此时，在where条件里搜索的时候，如果是根据name字段来搜索，那么就会优先去索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据后，再回到聚簇索引提取出来完整的name字段值进行比对
  • 但是，如果是order by name，那么此时name字段因为在索引树里仅仅包含前20个字符，所以这个排序是没法用上索引的，group by也是同理
• where 与 order by冲突优先where
  • 在where 与 order by出现索引设计冲突时，一般情况下往往都是让where条件去使用索引来快速筛选一部分数据，接着再进行排序
  • 因为大多数情况下，基于索引进行where筛选往往可以最快速度筛选出需要的少部分数据，然后做排序的成本可能会小很多
• 基于sql慢查询优化
  • 可以根据监控后台的一些慢sql，针对这些慢sql做特定的索引优化

4. 分页查询优化

4.1 根据自增且连续的主键排序的分页查询

根据自增且连续主键排序的分页查询的例子：

select * from employees limit 90000,5; 
1

该sql表示查询从第90001行开始的五行数据，没添加单独order by，表示通过主键排序，因为主键是自增且连续的，所以可以改写成按照主键去查询从第90001开始的五行数据，如下：

select * from employees where id > 90000 limit 5; 
1

查询的结果一致，对比查询计划

EXPLAIN select * from employees limit 90000,5; 
1

mysql> EXPLAIN select * from employees where id > 90000 limit 5; 
1

显然改写后的SQL走了索引，而且扫描的行数大大减少，执行效率高，但是这条改写的SQL在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，如下图所示：


两条SQL的结果并不一样，因此如果不连续，不能使用上面的描述的优化方法，另外，如果原SQL是order by 非主键的字段，按照上面说的方法改写会导致两条SQL的结果不一致，所以这种改写得满足以下两个条件

• 主键自增且连续
• 结果是按照主键排序的

4.2 根据非主键字段排序的分页查询

根据非主键字段排序的分页查询，SQL如下：

select * from employees ORDER BY name limit 90000,5; 
1

EXPLAIN select * from employees ORDER BY name limit 90000,5;
1

发现并没有使用name字段的索引(key字段对应的值为null)，具体原因：扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引，因此优化的关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL改写如下：

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
1

需要的结果与原SQL一致，执行时间减少了一半以上，对比优化前后SQL的执行计划

原SQL使用的是filesort排序，而优化后的SQL使用的是索引排序

5. Join关联查询优化

MySQL的表关联常见有两种算法

• Nested-Loop-Join 算法
• Block Nested-Loop-Join 算法

5.1 嵌套循环连接 Nested-Loop-Join（NLJ）算法

一次一行循环地从第一张表（驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集

# t1表10000条数据，t2表100条数据
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a; 
1
2

从执行计划可以看出：

• 驱动表是t2，被驱动表是t1，先执行的就是驱动表（执行计划结果的id如果一样则按照从上往下执行SQL），优化器一般会优先选择小表作驱动表，所以使用inner join时，排在前面的表并不一定就是驱动表
• 当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表是驱动表，左表是被驱动表，当使用join时，MySQL会选择数量比较小的表作为驱动表，大的作为被驱动表
• 使用了NLJ算法，一般join语句中，如果执行计划Extra中未出现Using join buffer则表示使用的join算法是NLJ

上面SQL的大致流程如下

• 从表t2中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）
• 从第一步数据中，取出关联字段a，到表t1中查找
• 取出表t1中满足条件的行，跟t2中获取到的结果合并，并作为结果返回给客户端
• 重复上面3步

整个过程会读取t2表的所有数据(扫描100行)，然后遍历这每行数据中字段a的值，根据t2表中的a的值索引扫描t1表中的对应行(扫描100次t1表的索引，1次扫描可以认为最终只扫描t1表一行完整数据，也就是总共t1表也扫描了100行)，因此整个过程扫描了200行，如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低，MySQL会选择Block Nested-Loop-Join算法

5.2 基于块的嵌套循环连接 Block Nested-Loop-Join （BNL）算法

把驱动表的数据读到join buffer中，然后扫描被驱动表，把被驱动表每一行读取出来跟join buffer中的数据做对比

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b; 
1

Extra中的Using join buffer（Block Nested Loop）说明该关联查询使用的是BNL算法
上面SQL大致流程如下：

• 把t2的所有数据放入到join buffer中
• 把表t1中每一行取出来，跟join buffer中数据做对比
• 返回满足join条件的数据

整个过程对表t1和t2都做了一次全表扫描，因此扫描的总行数为10000（表t1的数据总量）+ 100（表t2中的总数） = 10100。并且join buffer里的数据是无序的，因此对表t1中的每一行，都要做100次判断，所以内存中的判断次数是100 * 10000 = 100万次
这里表t2才100行，如果表t2是一个大表，join buffer放不下怎么办呢？

• join buffer的大小是由参数join_buffer_size设定的，默认值是256K，如果放不下表t2的数据的话，策略很简单，就是分段放
• 比如t2表中有1000行记录，join buffer中一次只能存放800行数据，那么执行过程就是先往join buffer里放800行记录，然后从t1表里取出数据跟join buffer中数据对比得到部分结果
• 然后清空join buffer，再放入剩余的200行记录，再次从t1表里取数据跟join buffer中数据对比，所以就多扫了一次t1表

被驱动表的关联字段没索引为什么要选择使用BNL算法而不使用Nested-Loop-Join呢？

• 如果上面第二条sql使用Nested-Loop-Join，那么扫描的行数为100 * 10000 = 100万次，这个是磁盘扫描
• 很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快很多
• 因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用BNL算法。如果有索引一般选择NLJ算法，有索引的情况下NLJ算法比BNL算法性能更高

5.3 关联SQL的优化

• 关联字段加索引，让MySQL做join操作时尽量选择NLJ算法
• 小表驱动大表，写多表连接sql时，如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去MySQL优化器自己判断的时间
• straight_join解释：straight_join功能和join类似，但是能让左边的表来驱动右边的表，能改变表优化器对于联表查询的执行顺序，比如：
• select * from t2 straight_join t1 on t2.a = t1.a；代表指定MySQL选择t2表作为驱动表
  • straight_join只适用于inner join，并不适用于left join，right join。（因为left join ，right join已经指代了表的执行顺序）
  • 尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的，使用straight_join一定要谨慎，因为部分情况下人为指定的执行顺序并不一定会比优化引擎靠谱
• **对于小表的定义：**在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是小表，应该作为驱动表

5.4 in 和 exists优化

原则：小表驱动大表，即小的数据集驱动大的数据集

• in：当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B)
#等价于： 
for(select id from B){ 
    select * from A where A.id = B.id 
} 
1
2
3
4
5

• exists：当表A的数据集小于表B的数据集时，exists优于in
  • 将主查询A的数据，放到子查询B中做条件验证，根据验证结果(true或false)来决定查询的数据是否保留

select * from A where exists (select 1 from B where B.id = A.id) 
# 等价于: 
for(select * from A){ 
    select * from B where B.id = A.id 
} 
# A表与B表的ID字段应建立索引 
1
2
3
4
5
6

  - EXISTS只返回TRUE或 FALSE，因此子查询中的SELECT * 也可以用SELECT 1 替换，官方说法是实际执行时，会忽略SELECT 清单，因此没有区别
  - EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的对比
  - EXISTS子查询往往也可以用join来代替，何种最优需要具体问题具体分析
1
2
3

5.5 count(*)查询优化

‐‐ 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
set global query_cache_size=0; 
set global query_cache_type=0; 

EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees; 
EXPLAIN select count(name) from employees; 
EXPLAIN select count(*) from employees; 
1
2
3
4
5
6
7
8

以上4条SQL只有根据某个字段count不会走统计字段为null值的数据行

四个SQL的执行计划一样，说明这四个SQL执行效率应该差不多

• 字段有索引：count(*) ≈ count(1) > count(字段) > count(主键id)。字段有索引，count(字段)统计走二级索引，二级索引存储的数据比主键索引少，所以count(字段) > count(主键id)
• 字段无索引：count(*) ≈ count(1) > count(主键id) > count(字段)。字段没有索引count(字段)走不了索引，count(主键id)还可以走主键索引，索引count(主键id) > count(字段)

count(1)和count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点
count()是例外，MySQL并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代count()
为什么对于count(id)，MySQL最终选择辅助索引而不是主键的聚集索引？

• 因为二级索引相对主键索引存储的数据更少，检索性能更高，MySQL内部做了点优化

6. 常见优化方法

6.1 查询MySQL自己维护的总行数

• 对于MyISAM存储引擎的表做不带where条件的count查询性能是很高的，因为MyISAM存储引擎的表的总行数会被MySQL存储在磁盘上，查询不需要计算

• 对于InnoDB存储引擎的表mysql不会存储表的总记录数（因为有MVCC机制），查询count需要实时计算

6.2 show table status

• 如果只需要指定表总行数的估计值可以用如下SQL查询，性能很高

6.3 将总数维护到Redis里

• 插入或删除表数据行的时候，同时维护Redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和Redis操作的事务一致性

6.4 增加数据库计数表

• 插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

五、事务

1. 事务及其ACID属性

1.1 事务ACID属性

事务是由一组SQL语句组成的逻辑单元，事务具有以下4个属性，通常简称为事务的ACID属性

• 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要不全都不执行
• 一致性（Consistent）：在事务开始和完成时，数据必须保持一致，这就意味着所有相关的数据规则必须应用于事务的修改，以保证数据的完整性
• 隔离性（Isolation）：数据库提供一定的隔离机制，保证事务在不受外部并发操作的影响的独立环境执行，这意味着事务在处理的过程中的中间状态对外部是不可见的，反之亦然
• 持久性（Durable）：事务完成之后，数据的修改是永久性的，即使出现故障也能保持

1.2 并发事务处理带来的问题

• 更新丢失或脏写：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新的问题，最后的更新覆盖了其他事务所作的更新
• 脏读：事务A读取到了事务B还未提交的数据，不符合一致性
• 不可重复读：事务A内部的相同查询语句在不同时刻读取的结果不一致，不符合隔离性
• 幻读：事务A读取到了事务B提交的新增数据，不符合隔离性

1.3 事务的隔离级别

• 读未提交（Read uncommitted）：所有的事务都可以看到未提交事务的执行结果，会产生脏读，幻读，不可重复读
• 读已提交（Read commited）：事务开始提交前，所作的任何数据改变都是不可见的，会产生幻读，不可重复读
• 可重复度（Repeatable Read）：保证同一个事物的多个实例在并发读取的时候，会看到同样的数据行，会产生幻读，通过MVCC解决幻读问题
• 串行化（Serializable）：最高的隔离级别，在每一行都加锁，不会出现脏读，幻读，不可重复读，但是影响效率

1.4 查看当前数据库的事务隔离级别

show variables like 'tx_isolation'
1

1.5 设置事务隔离级别

set tx_isolation='REPEATABLE-READ'
1

MySQL默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别，默认用MySQL设置的隔离级别，如果Spring设置了就用已经设置了的隔离级别

2. 锁

锁分类

• 从性能上分：乐观锁和悲观锁
• 从对数据库的操作的类型分：读锁和写锁
• 读锁，又称共享锁，S锁，针对同一个数据，多个读操作可以同时进行而不会受到影响
• 写锁，又称排它锁，X锁，当前写操作没有完成前，它会阻断其他读锁和写锁
• 从数据操作的粒度分：表锁和行锁

2.1 表锁

每次操作锁住整张表，开销小，加锁快；不会出现死锁；锁粒度大，发生锁冲突的概率最高，并发度最低，一般用在整表数据迁移的场景

• 手动增加表锁：lock table 表名称 read(write)，表名称2 read(write)
• 查看表上加过的锁：show open tables
• 删除表锁：unlock tables

加读锁：

• 当前session和其他session都可以读该表
• 当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待

加写锁：

• 当前session对该表的增删改查都没问题，其他session对该表的所有操作都被阻塞

结论：

• 对MyISAM表的读操作(加读锁)，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求，只有当读锁释放了之后，才会执行其他进程的写操作
• 对MyISAM表的写操作(加写锁)，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作

2.2 行锁

每次操作锁住一行数据，开销大，加锁慢，会出现死锁，锁的粒度小，发生锁冲突的概率最低，并发最高
InnoDB和MyISAM的最大不同有两点：

• InnoDB支持事务
• InnoDB支持行锁

一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞
总结：

• MyISAM在执行查询语句select前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁
• InnoDB在执行查询语句select时（非串行隔离级别），不会加锁，但是update、insert、delete操作会加行锁
• 简而言之，就是读锁会阻塞写，但是不会阻塞读，而写锁会阻塞读和写

行锁分析：
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的竞争情况

show status like 'innodb_row_lock%';
1

对各个状态量的说明如下：

• Innodb_row_lock_current_waits：当前正在等待锁定的数量
• innodb_row_lock_time：从系统启动到现在锁定总时间长度（等待总时长）
• innodb_row_lock_time_avg：每次等待所花的平均时间（等待平均时长）
• innodb_row_lock_time_max：从系统起动到现在等待最长的一次所花时间
• innodb_row_lock_waits：系统启动后到现在总共等待的次数（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多等等待，然后根据分析结果着手定制优化计划
查看INFORMATION_SCHEMA系统库锁相关数据表

# 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX
# 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS
# 查看锁等待
select * from INFORMATION_SCHEMA。INNODB_LOCK_WAITS
# 释放锁，trx_mysql_thread_id可以从InnoDB_TRX表里查看到
kill trx_mysql-thread_id
# 查看所等待详细信息
show engine innodb status\G
1
2
3
4
5
6
7
8
9
10

2.3 间隙锁

间隙锁，锁的是两个值之间的间隙，MySQL默认的级别是repeatable-read，间隙锁在某些情况下可以解决幻读问题
假设account表中数据如下：

那么间隙就有id为(3,10)，(10,20)，(20,正无穷)三个区间，在session_1下面执行update count set name = ‘zhangsan’ where id > 8 and id <18；则其他session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在(3,20]区间都无法修改数据，注意最后的20也包含在内
间隙锁是在可重复度隔离级别下才生效

2.4 临键锁（Next-key Locks）

Next-key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁
无索引行锁会升级为表锁
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁

• session1执行：update account set balance = 800 where name = ‘lilei’
• session2对该表任何操作都会被阻塞住

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁
锁定某一行还可以用lock in share mode（共享锁），和 for update（排它锁），例如：select * from test_innodb_lock where a = 2 for update，这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交
结论：

• InnoDB存储引擎由于实现了行级锁，虽然在锁定机制的实现方面带来了性能损耗可能比表级锁会要更高，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定。当系统并发量高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了
• 但是InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB不仅不能呢个比MyISAM高，甚至可能会更差

2.5 死锁

set tx_isolation = 'repeatable-read'
session1执行：select * from account where id = 1 for update；
session2执行：select * from account where id = 2 for update；
session1执行：select * from account where id = 2 for update；
session2执行：select * from account where id = 1 for update；
查看近期死锁日志信息：show engine innodb status\G
1
2
3
4
5
6

大多数情况MySQL可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况MySQL没法自动检测死锁
锁优化建议：

• 尽可能让所有数据检索都通过索引完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少检索条件范围，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
• 尽可能低级别事务隔离

六、 MVCC多版本并发控制与buffer pool缓存机制

1. MVCC多版本并发控制

• MySQL在可重复读隔离级别下如何保证事务的隔离性？同样的sql查询语句在一个事务里多次查询的结果相同，就算其他事务对数据有修改也不会影响当前事务SQL查询的结果
• 这个隔离性就是靠MVCC机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁的互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有的锁互斥来实现的
• MySQL在读已提交和可重复读隔离级别下都实现了MVCC机制

2. undo日志版本链和read view机制

undo日志版本链是指一行数据被多个事务一次修改后，在每个事务修改完后，MySQL会保留修改前的数据undo回滚日志，并且设置两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录

在可重复读级别，当事务开启，执行任何查询sql时会产生当前事务的一致性视图read-view，该视图在事务结束之前都不会变化（如果是读已提交隔离级别在每次执行查询SQL时都会重新生成），这个视图由执行时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果都需要从对应版本链里的最新数据开始逐条read-view作比对从而得到最终的快照结果。
版本链对比规则：

• 如果row的trx_id落在绿色部分（trx_id < min_id），表示这个版本是已提交的事务生成的，这个事务是可见的
• 如果row的trx_id落在红色部分（trx_id > max_id），表示这个版本是由将来启动的事务生成的，是不可见的（如果row的trx_id是当前自己的事务，则是可见的）
• 如果row的trx_id落在黄色部分（min_id < trx_id < max_id），那么就有两种情况：
  • 若row的trx_id在视图数组中，表示这个版本是由还没提交的事务生成的，不可见（如果row的trx_id就是当前自己的事务，则是可见的）
  • 若row的trx_id不在视图数组中，表示这个版本是已经提交了的事务生成的，可见

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录，如果delete_flag标记为true，意味着记录已被删除，则不返回数据
注意：
begin/start transaction 命令不是一个事务的起点，在执行到他们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向MySQL申请事务id，MySQL内部是严格按照事务的启动顺序来分配事务id的
总结：
MVCC机制的实现就是通过read-view机制与undo版本链对比机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本的数据

3. InnoDB引擎SQL执行的BufferPool机制

执行update t set name = ‘zhangsan666’ where id = 1，id为1的数据name的原值为zhangsan

• 加载缓存数据到缓存池BufferPool，加载id为1的记录所在的整页数据
• 将更新前的旧值写入undo日志文件，name = ‘zhangsan’
• 更新内存数据
• 写redo日志到Redo Log Buffer
• 准备提交事务，redo日志写入磁盘，name = ‘zhangsan666’
• 准备提交事务binlog日志写入磁盘，name = ‘zhangsan666’
• 写入commit标记到redo日志文件，提交事务完成，该标记为了保证事务提交后，redo和binlog数据一致
• 将BufferPool中修改的数据随机写入磁盘，以page为单位写入，这步做完磁盘里的name = ‘zhansan666’
• undo、redo和binlog日志文件的作用：
• undo日志文件：如果事务提交失败要回滚数据，可以用undo日志里面的数据恢复BufferPool里的缓存数据
• redo日志文件：如果事务提交成功，BufferPool里的数据还没来得及写入磁盘，此时系统宕机了，可以用redo日志里的数据恢复BufferPool里的缓存数据
• binlog日志文件：binlog日志主要用来恢复数据库磁盘文件里的数据

3.1 为什么MySQL不能直接更新磁盘上的数据而去设置这么一套复杂的机制来执行SQL

来一个请求就是直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据，性能可能相当差

• 因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的
• MySQL这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性
• 更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件
• 正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几千的读写请求