导图社区架构师必须掌握的mysql知识

架构师必须掌握的mysql知识

高级程序员和架构师需要掌握的mysql基础知识；中级程序员可以看看学习；初级程序员建议先好好练习 sql.

提示: 本内容由社区用户上传并分享。平台不对内容的真实性、合法性、知识产权归属及是否侵害第三方权利进行事前审核或保证。本内容可能包含受版权保护的图片、字体或其他第三方素材，使用前请自行确认授权范围。

编辑于2023-10-11 10:17:00

架构师
mysql
必知必会
高级知识

遥遥知识库

他的近期作品查看更多>>

架构师必须掌握的mysql知识

社区模板帮助中心，点此进入>>

遥遥知识库

他的近期作品查看更多>>

相似推荐
大纲

互联网9大思维
- 42.1k
- 987
- 2.5k
- 402
- 0
MindMaster
组织架构-单商户商城webAPP 思维导图。
- 20.3k
- 3
- 187
- 8
- 1
Kacyun
域控上线
- 5.7k
- 173
- 11
- 4
- 0
jackrao
python思维导图
- 11.6k
- 560
- 243
- 6
- 0
(*^▽^*)
css
- 5.1k
- 1
- 43
- 3
- 0
A张舫
CSS
- 7.9k
- 276
- 189
- 33
- 0
journey
计算机操作系统思维导图
- 9.9k
- 362
- 213
- 14
- 0
journey
计算机组成原理
- 5.2k
- 98
- 71
- 8
- 0
journey
IMX6UL(A7)
- 3.8k
- 41
- 5
- 0
- 0
Handler XU
考试学情分析系统
- 5.0k
- 51
- 10
- 1
- 0
蒋龙

MySQL

常见的MySQL存储引擎

MyISAM

特点

**MyISAM索引文件和数据文件是分离的(非聚集/簇)** 表存储在磁盘中，如果不改动，存储MySQL安装目录的该库的data目录下对于MyISAM存储引擎用三个文件存储数据 **xx.frm（表结构文件），xx.MYD （数据文件），xx.MYI（索引文件）**

SQL 走 MyISAM 中索引时的流程

当我们使用一条SQL语句走MyISAM索引时：第一步先从MYI的B+树结构找到data （数据所在行磁盘地址），第二步有MYD文件找到具体的数据。 ![在这里插入图片描述](https://img-blog.csdnimg.cn/72b00619c1a24417aed645a6352265d1.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM1Mjk3Mw==,size_16,color_FFFFFF,t_70)

InnoDB

特点

**InnoDB存储引擎索引与数据是不分离的（聚集/簇）** 表存储在磁盘中，如果不改动，存储MySQL安装目录的该库的data目录下对于 InnoDB 存储引擎用两个文件存储数据 **xx.frm（表结构文件），xx.idb （数据文件+索引文件）** - 表数据文件本身就是按 B+Tree 组织的一个索引结构文件 - 聚集索引-叶节点包含了完整的数据记录 - **一张表只有一个聚集索引（即主键索引）**，其他索引都是二级索引，**叶子节点的 data 中存储叶子节点的主键值**，由该值进行**回表操作**到主键索引中查找数据，这样既能满足快速找到数据也能保证数据的**一致性**和**节约存储空间**。如果没有唯一主键，则MySQL会选择唯一列，如果没有，会自己维护一个隐藏列（如rowid）作为聚簇索引。 **主键索引** ![在这里插入图片描述](https://img-blog.csdnimg.cn/0429c3482f1147d3979132582775d35d.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM1Mjk3Mw==,size_16,color_FFFFFF,t_70) **二级索引** ![在这里插入图片描述](https://img-blog.csdnimg.cn/3337ed352b554af0bff375732642294c.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM1Mjk3Mw==,size_16,color_FFFFFF,t_70)

SQL 走 InnoDB 中索引时的流程

当我们使用一条SQL语句走 InnoDB 索引时：直接从 idb 的 B+ 树结构的叶子节点中找到具体数据

使用建议

建议表必须建立一个主键，并且推荐使用**整型**的**自增**主键 **原因：** 1.如果不建立主键，MySQL 会找一列所有值都不相等的列来用于构建 B+树，如果没有这样的列，MySQL 会创建一个隐藏列来维护这个B+树 2.整型比长串的占用空间更少，比较大小时相对来说更容易，能更快的定义数据的位置 3.B+Tree 需要维护自身平衡，自增不会导致树分裂

索引

**索引的本质就是一种排好序数据结构**。数据库中的列存在磁盘上，存储不是连续的，**索引记录着这些列在磁盘上的位置（地址）**。同时，索引也存储在磁盘上。

索引的数据结构

二叉树

当存储的数据为单边增长时，树太深，对于使用二叉树进行搜索时不太友好。 ![在这里插入图片描述](https://img-blog.csdnimg.cn/0825e062ac694fc3bd0cac2f14fa783e.gif)

红黑树

当数据量较大时，树的高度不可控 ![在这里插入图片描述](https://img-blog.csdnimg.cn/5014f01005d84770b572a9f9856c0d71.gif)

Hash

![在这里插入图片描述](https://img-blog.csdnimg.cn/5aa232a25a784ca5b0afaeabb466479b.gif)

优点

- **很多时候使用 Hash 索引要比 B+ 树索引更高效** - **对索引的 key 进行一次 Hash 计算就可以定位出数据存储的位置**

缺点

- Hash冲突问题 - **不支持范围查询**，仅支持 “=” 或 “in”

B-Tree

- 叶子节点具有相同的深度 - 索引索引元素不重复 - 节点索引树从左到右递增 - 叶子节点没有指针 ![在这里插入图片描述](https://img-blog.csdnimg.cn/165495de158640bca0c2ffec6d51afdd.gif)

B+Tree

![在这里插入图片描述](https://img-blog.csdnimg.cn/e0beea483e2a4a9385bed4d4f190fa43.gif)

优点

- 非叶子节点不存储data，只存储索引(冗余索引)，每页可以放更多的索引 - 叶子节点包含所有索引字段 - 叶子节点用指针连接，提高区间访问的性能 - 从左到右递增，使用 B+ 树优化后的结构，具有**双向箭头指针**，支持范围查找 ![在这里插入图片描述](https://img-blog.csdnimg.cn/126f1485fa724ca880bab32d486bbecf.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM1Mjk3Mw==,size_16,color_FFFFFF,t_70)

为什么使用B+树而非B树

- **相比于B-Tree相同的高度下，B+Tree 能存储更多的数据**：B-Tree 非叶子节点存储数据，而 B+ 树非叶子节点不存储数据（data），一颗高度为3的B+树大约能存储2千万+ 数据。 - 变种 B+Tree 具有双向指针，支持范围查询，而 B-Tree 没有指针，范围查找速度慢（跨区间需重新检索） MySQL 默认页文件大小16384（16K）假设一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为BigInt类型，即长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)。一颗高度为3的 B+ 树能存储的数据为：1170*1170*16 = 21902400

索引查询时MySQL的优化

**低版本的MySQL根节点常驻内存，高版本的MySQL非叶子节点常驻内存**，内存操作对比与磁盘IO时间几乎可以忽略不计，如果使用了索引先在内存中进行定位叶子节点位置，再将该区间的叶子节点进行磁盘IO，Load到内存进行查询

索引种类

是否聚簇分

聚簇/集索引

索引与数据是不分离 InnoDB中，**一张表只有一个聚集索引（即主键索引）**，其他索引都是二级索引 **MyISAM索引文件和数据文件是分离的(非聚集/簇)** 单从索引角度来说，非聚集索引查找速度不如聚集索引，非聚集索引找到索引位置后还需要根据索引找到数据对应的位置

非聚簇/集索引

索引与数据是分离

按类型分

普通索引

**INDEX** 最基本的索引，无任何限制

唯一索引

**UNIQUE** 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

主键索引

**PRIMARY KEY** 特殊的唯一索引，一个表只能有一个主键，不允许有空值

复合索引

多个字段上创建的索引，需遵循最左前缀原则 ![在这里插入图片描述](https://img-blog.csdnimg.cn/abc9842e769d4b3d8c8dab9ac33834fe.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM1Mjk3Mw==,size_16,color_FFFFFF,t_70)

索引结构

![在这里插入图片描述](https://img-blog.csdnimg.cn/abc9842e769d4b3d8c8dab9ac33834fe.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM1Mjk3Mw==,size_16,color_FFFFFF,t_70)

最左前缀原则

在 MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配即：当a,b,c为联合索引时遵循最左匹配原则，即：a,ab,abc索引都会生效,但b,c,bc,ac等不会生效（如果使用执行计划会可以看到，type列为index，扫描索引树，效率相对于最左匹配的索引效率极低），所以一`定要注意索引顺序，最常用的最段要放在最前面`。 - 例如，创建一个`idx_name_age_postion`联合索引，它的索引树图如上图，由图可以看出 name 值是有序的，age 值是无序的，但是在name值相等的情况下age值又是有序的。由此可以看出MySQL创建联合索引时首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序,以此类推。所以age或postion单独作为条件时，索引是无效的。 - 当a,b,c三个索引都用到时，只有全匹配，无论顺序如何，索引是有效的，MySQL执行计划会对其进行优化，自动使用最优方案执行。

全文索引

**FULLTEXT** 仅 MyISAM 引擎支持。只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。更像一种搜索引擎，用来查找关键字，且对中文不友好

关于索引的 SQL 优化相关建议

**1.全值匹配** **2.字符串不加单引号索引失效** ```sql #有效使用索引 explain select * from student_inndb where idCard = '123456'; #索引失效 explain select * from student_inndb where idCard = 123456; ``` **3.最左前缀法则** **4.复合索引中范围条件右边的列的索引不能被使用** ```sql #使用索引name_age_idcard_idx explain select * from student_inndb where name ='张三' and age = 20 and idCard = '123456'; #使用name_age_idcard_idx的前缀name_age explain select * from student_inndb where name ='张三' and age > 20 and idCard = '123456'; ``` **5.尽量使用覆盖索引，减少 select * 语句** **6.MySQL 使用 `!=， <>，not in ，not exists 会导致索引失效`，从而全表扫描。使用< 、> 、<=，>=时 MySQL 内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引** **7.不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描** **8.对于 is null，is not null 一般情况下也无法使用索引** **9.对于 like 以通配符开头 `'%xxx'` 可能会导致索引失效而进行全表扫描（借助搜索引擎或覆盖索引优化）** 对于 like 不以通配符开头 'xxx%' 会使用下面的索引 `explain select * from student_inndb where name like '张%';` ![在这里插入图片描述](https://img-blog.csdnimg.cn/294ede186df04f27b7901f7360f426da.png) 对于 like '%xxx' 如果使用了覆盖索引 `explain select name,age,idCard from student_inndb where name like '%张%';` ![在这里插入图片描述](https://img-blog.csdnimg.cn/b0069c943877422cb937fed7c2f2fee7.png) like 'xx%'相当于=常量，'%xx' 和 '%xx%'相当于范围 **10.范围查询优化**：MYSQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。对于大范围的查询可以将其拆分为多个小范围查询 **11.如果查询的结果集没多少，走索引还需要回表操作，MySQL 内部优化器可能会直接让该查询走全部扫描** 总结 | WHERE语句（a_b_c_idx 为索引） | 索引是否被使用 | | :----------------------------------------------------------- | :----------------- | | **where a = 3** | **Y，使用到 a** | | **where a = 3 and b = 4** | **Y，使用到a,b** | | **where a = 3 and b = 4 and c = 5** | **Y，使用到a,b,c** | | **where b = 3 或 c = 3 或 b = 3 and c = 4** | **N** | | **where a = 3 and c = 4** | **Y，使用到a** | | **where a = 3 and b > 4 and c = 5** | **Y，使用到a,b** | | **where a = 3 and b like 'xx%' and c = 4** | **Y，使用到a,b,c** | | **where a = 3 and b like '%xx' and c = 4** | **Y，使用到a,** | | **where a = 3 and b like '%xx%' and c = 4** | **Y，使用到a,** | | **where a = 3 and b like 'x%xx%' and c = 4** | **Y，使用到a,b,c** |

索引优化实例

SQL优化原则

减少访问量

**减少访问量：** 数据存取是数据库系统最核心功能，所以IO是数据库系统中最容易出现性能瓶颈，减少SQL访问IO量是SQL优化的第一步；数据块的逻辑读也是产生CPU开销的因素之一。 - 减少访问量的方法：创建合适的索引、减少不必访问的列、使用索引覆盖、语句改写。

减少计算操作

**减少计算操作：** 计算操作进行优化也是SQL优化的重要方向。SQL中排序、分组、多表连接操作等计算操作都是十分消耗CPU的。 - 减少SQL计算操作的方法：排序列加入索引、适当的列冗余、SQL拆分、计算功能拆分。

联合索引第一个字段用范围不会走索引

`EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';` ![在这里插入图片描述](https://img-blog.csdnimg.cn/a381d353f9a04a84ac8cad3fbc5c924e.png) 原因：MySQL 内部可能觉得第一个字段就用范围，结果集应该很大，还需要回表，回表效率不高，不如直接采用全表扫描但是我们可以强制走索引 `EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';` ![在这里插入图片描述](https://img-blog.csdnimg.cn/6bc6f76a1d0449a5a207f1aff45c3b6f.png) ```sql -- 关闭查询缓存 set global query_cache_size=0; set global query_cache_type=0; -- 执行时间0.321s SELECT * FROM employees WHERE name > 'LiLei'; -- 执行时间0.458s SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei'; ``` **使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高** 对于这种情况，如果可以使用覆盖索引，就使用覆盖索引进行优化 `EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';` ![在这里插入图片描述](https://img-blog.csdnimg.cn/23fc1ea5f0244c67a6ea7fcb0c6f6a1b.png)

in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

** ```sql EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager'; #表数据量大走索引，数据量小全表扫描 EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager'; ``` ![在这里插入图片描述](https://img-blog.csdnimg.cn/da668a589dcb4ece92a520a91a9e0bcc.png) 将十万行数据的employees表复制一份插入几行数据，再进行查询 ![在这里插入图片描述](https://img-blog.csdnimg.cn/9779b0cdd6344a209385b0e9eb921b7b.png) 发现进行了全表扫描 ![在这里插入图片描述](https://img-blog.csdnimg.cn/334ccd1415324fc984069e7aaf99b090.png)

like xx% 无论数据量多少一般情况都会走索引

索引下推

MySQL 底层使用**索引下推（Index Condition Pushdown，ICP）** 来对 like xx%进行优化。 **索引下推：** 对于辅助的联合索引(idx_name_age_position)，通常按照最左前缀原则，`SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager'` 因为在 **name 是范围查询**，过滤完后，age 和 position 是无序的，**后续索引无法使用**，只会走name字段索引。 **MySQL5.6 以前：** 先在索引树中匹配 name 是 'LiLei' 开头的索引，然后根据索引下的主键进行回表操作，在主键索引上在匹配 age 和 position **MySQL 5.6以后：** 引入索引下推，现在索引树种匹配 name 是 'LiLei' 开头的索引，同时将该所与树通有的所有条件字段进行判断，过滤掉不符合条件的记录再回表匹配其他条件及查询整行数据。 **优点：** 过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数，提高查询效率。 **MySQL 范围查找为什么没有使用索引下推优化？** 可能因为范围查找结果集一般较大，like xx%在大多数情况下，过滤后结果集较小。而结果集大的时候，每次检索出来都要匹配后面的字段，不一定比立即回表要快。但是也不是绝对的，有些时候 Like xx%也不会走索引下推。

MySQL 如何选择索引

**会根据执行预估出 cost_for_plan 花费时间的值来选择是否使用索引，使用哪个索引** 先来看两条 SQL 语句： ```sql # MySQL直接使用全表扫描 EXPLAIN select * from employees where name > 'a'; # MySQL走索引 EXPLAIN select * from employees where name > 'zzz'; ``` ![在这里插入图片描述](https://img-blog.csdnimg.cn/52e0532dc7304390a01186527444f6e1.png) 我们发现第一条 SQL 进行了全表扫描，第二条 SQL 走了索引。对应第一条SQL，MySQL 通过计算执行成本发现走索引成本比全部扫描更高（走索引需要遍历 name 字段，再进行回表操作查出最终数据，比直接查聚簇索引树更慢）。对于这种情况`可以使用覆盖索引进行优化`。至于 MySQL 如何选择最终索引，可以用 **Trace 工具**进行查看。但**开启trace工具会影响 MySQL 性能，用完之后需立即关闭。** ```sql #开启trace set session optimizer_trace="enabled=on",end_markers_in_json=on; #关闭trace set session optimizer_trace="enabled=off"; #使用trace select * from employees where name > 'a' order by position; select * from information_schema.OPTIMIZER_TRACE; ``` ![在这里插入图片描述](https://img-blog.csdnimg.cn/a00f5c4998cc4ac89c5249a99bf5d3a4.png)

常见SQL的深入优化

Order by与Group by优化

案例

```sql # 案例1 explain select * from employees where name = 'Lucy' and position = 'dev' order by age; ``` **分析：** 案例1 由最左前缀法则分析出索引中间不能出现断层，索引重新是使用了 name 索引，也可以从key_len = 3n + 2 看出。age 索引列用在排序过程中，因为Extra字段里没有 Using filesort 而是 Using index condition 。 ![在这里插入图片描述](https://img-blog.csdnimg.cn/8d0c953dba914689b107509b7d3afb9f.png) ```sql #案例2 explain select * from employees where name = 'Lucy' order by position; ``` **分析：** 案例2 索引查询使用了 name 索引，但排序由于跳过了 age 所以Extra字段出现了 Using filesort 。 ```sql #案例3 explain select * from employees where name = 'Lucy' order by age, position; ``` ![在这里插入图片描述](https://img-blog.csdnimg.cn/d997ce1afcab4760bd348c82a49b3efb.png) **分析：** 案例3 查询时使用了 name 索引，age 和 postion 用于排序，不会出现 Using filesort ```sql #案例4 explain select * from employees where name = 'Lucy' order by position,age; ``` ![在这里插入图片描述](https://img-blog.csdnimg.cn/8614dd69153740b9ab6ee7f228f807d5.png) **分析：** 案例4 查询时使用了 name 索引，age 和 postion 顺序与创建索引树不一致，出现了 Using filesort ```sql #案例5 explain select * from employees where name = 'Lucy' and age = 22 order by position,age; ``` ![在这里插入图片描述](https://img-blog.csdnimg.cn/dd377f9a808848ff9251e8d920daaa4b.png) **分析：** 案例5 查询时使用了 name 索引，age 和 postion 顺序与创建索引树不一致，但 age 为常量，MySQL 会自动优化，不会出现 Using filesort ```sql #案例6 explain select * from employees where name = 'Lucy' order by age,position desc; ``` ![在这里插入图片描述](https://img-blog.csdnimg.cn/52287d61fa8b4befb8b98149d50ee11e.png) **分析：** 案例6 排序顺序一样，但 order by 默认升序，导致与索引的排序方式不同，出现了 Using filesort 。 MySQL8.0 以上版本有降序索引可以支持这种查询。 ```sql #案例7 explain select * from employees where name = 'Lucy' or name = 'LiLei' order by age; ``` ![在这里插入图片描述](https://img-blog.csdnimg.cn/faaa254f766c4589ab77cba8d2a0740f.png) **分析：** 案例7 对于排序来说，多个相等条件也是范围查询，出现了 Using filesort 。 ```sql #案例8 #SQL-1 explain select * from employees where name > 'zzz' order by name; #SQL-2 explain select * from employees where name > 'a' order by name; ``` ![在这里插入图片描述](https://img-blog.csdnimg.cn/97528fa17ddd435885f5b2819b85a386.png) **分析：** 案例8 原因同前面的例子，可以使用覆盖索引优化。

总结

**MySQL排序总结：** 1、MySQL支持两种方式的排序 **filesort** 和 **index**，**Using index是指MySQL扫描索引本身完成排序。Using filesort 是指MySQL扫描聚簇索引（整张表）进行排序**。index效率高，filesort效率低。 2、order by 满足两种情况会使用Using index（不绝对）。 a.order by 语句使用索引最左前列。 b.使用where子句与order by子句条件列组合满足索引最左前列。 3、尽量在索引列上完成排序，遵循最左前缀法则。 4、如果 order by 的条件不在索引列上，就会产生Using filesort。 5、能用覆盖索引尽量用覆盖索引 6、**group by** 与 order by 很类似，其实质是**先排序后分组**（group by 底层先执行一次 order by 再进行分组），遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null 禁止排序。注意，**where高于having**，**能写在where中的限定条件就不要去having限定了**。

文件排序

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。 - 字段的总长度 < max_length_for_sort_data ，使用单路排序 - 字段的总长度 >max_length_for_sort_data ，使用双路排序 **单路排序会把所有需要查询的字段都放到 sort buffer 中排序**，而**双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。**

单路排序

**单路排序**：**是一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序**。用trace工具得到sort_mode信息显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields > **单路排序过程：** a.从索引 name 找到第一个满足 name = 'Lucy' 条件的主键 id b.回表根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中 c.从索引name找到下一个满足 name = 'Lucy' 条件的主键 id d.重复步骤 2、3 直到不满足 name = 'Lucy' e.对 sort_buffer 中的数据按照字段 position 进行排序 f.返回结果

双路排序

**双路排序（又叫回表排序模式）**：**先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段**。用trace工具得到sort_mode信息显示< sort_key, rowid > **双路排序过程：** a.从索引 name 找到第一个满足 name ='Lucy' 的主键 id b.根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中 c.从索引 name 取下一个满足 name = 'Lucy' 记录的主键 id d.重复 3、4 直到不满足 name = 'Lucy' e.对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序 f.遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出所有字段的值返回

分页查询优化

```sql select * from employees limit 10000,10 ``` 这条 SQL 语句实际查询了 10010 条记录，然后丢弃了前面的 10000 条记录，所以，在数据量很大时，执行效率是非常非常低的。一般需要对分页查询进行优化。

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

```sql select * from employees where id > 90000 limit 5; ``` 当一个表的主键连续且自增时，可以使用该方法进行优化，但如果**自增不连续会造成数据丢失**。

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

```sql #优化前 select * from employees ORDER BY name limit 90000,5; #优化后 select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id; ``` 先通过排序和分页操作先查出主键，然后根据主键查出对应的记录。 ![在这里插入图片描述](https://img-blog.csdnimg.cn/5f2d60b8d3b0445fb4d07083ed172141.png)

join关联查询优化

两种算法

MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，**有索引的情况下 NLJ 算法比 BNL算法性能更高**

嵌套循环连接 Nested-Loop Join(NLJ) 算法

原理：一次一行循环地从第一张表（驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。 ```sql explain select * from t1 inner join t2 on t1.a= t2.a; ``` ![在这里插入图片描述](https://img-blog.csdnimg.cn/1b1e088b66154522b00094f400c11a1b.png) **从执行计划可以了解的信息：** a.驱动表是 t2，被驱动表是 t1(inner join时 SQL优化器会小表驱动大表，外连接则根据连接类型区分 b.使用了 NLJ算法。如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ 整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)。因此**整个过程扫描了 200 行** 。

基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

原理：把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比 ```sql explain select * from t1 inner join t2 on t1.b= t2.b; ``` ![在这里插入图片描述](https://img-blog.csdnimg.cn/107f56475da14bfa987927e1764407bd.png) 整个过程对表 t1 和 t2 都做了一次全表扫描，因此**扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100**。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的**判断次数是 100 * 10000= 100 万次（非扫描次数）**。 **注意：** join_buffer 的大小是由参数 join_buffer_size 控制，默认256k。如果 t2 放不下就会使用**分段策略**（先从 t2 表取出部分数据，比对完就清空 join_buffer，再重新拿出来余下的部分进行比对）。 **被驱动表的关联字段无索引为什么要选择使用 BNL 算法而不使用 NLJ 算法？** 如第二条 SQL，如果使用 NLJ 算法扫描行数为 100 * 10000 = 100万，这个是磁盘扫描。使用 BNL 算法仅需扫描 100100 行。

优化建议

- 尽量少关联（在阿里规范中，关联表不能超过三种，可以后端代码单独查询，循环关联） - 小表驱动大表，写多表连接 SQL 时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，节约 MySQL 优化器判断时间.`select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表` - 关联字段加索引，大表关联字段一定要加索引，尽量使得 MySQL 在进行 join 操作时选择NLJ算法 - 多表连接是非常难以优化的，最好95%的场景都使用单表来完成，复杂场景交个JAVA代码，大规模计算交给大数据工具，无需效率才考虑连接

in和exsits优化

原则：**小表驱动大表** ```sql # in 先执行括号里面的 select * from A where id in (select id from B) #exists 先执行括号外面的 #select * 可以用 select 1 替换，没有区别 #exists 子查询内部会进行优化，并非逐条对比 #exists 子查询往往也可以用 jion 来代替，何种最优需要具体问题具体分析 select * from A where exists (select 1 from B where B.id = A.id) ```

count查询优化

四种方式

**注意：根据某个字段 count 不会统计字段为 null 的行** ```sql #扫描二级索引，按行累加 explain select count(1) from employees; #扫描辅助索引按行累加（辅助索引比聚簇索引小） explain select count(id) from employees; #把 name 拿到内存，不为 null 就累加 explain select count(name) from employees; #不取值，按行累加 explain select count(*) from employees; ```

效率对比

**字段有索引：** count(* )≈count(1)>count(字段)>count(主键 id) 段)>count(主键 id) **字段无索引：** count(*)≈count(1)>count(主键 id)>count(字段)

优化建议

1.**对于 MyISAM存储引擎的表做不带where条件的count查询性能是很高的，数据总行数直接写在磁盘上，查询不需要计算**。innodb 存储引擎的表则不会记录(因为有MVCC机制) 2.**对与不用知道确切行的可以直接使用**`show table status`，它是一个估值，使用该查询效率很高 3.**将总数维护到 Redis 里面**，插入或删除表数据行的时候同时维护 Redis 里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和Redis 操作的事务一致性。 4.**增加数据库计数表**，插入或删除表数据行的时候同时维护计数表，且它们在同一个事务里操作

索引设计原则

代码先行，索引后上

先开发完主体业务代码，再把涉及到该表相关sql都要拿出来分析之后再建立索引

联合索引尽量覆盖条件

可以设计一个或者两三个联合索引(单值索引要少建)，让每一个联合索引都尽量去包含SQL语句里的 where、order by、group by 的字段，且这些联合索引字段顺序尽量满足 SQL查询的最左前缀原则

不要在小基数字段上建立索引

无法进行快速的二分查找，不能能发挥出B+树快速二分查找的优势来，没有意义

尽量对字段类型较小的列设计索引

尽量对字段类型较小的列设计索引，比如 Tinyint 之类，字段类型较小的话，占用磁盘空间小，搜索的时性能更好

长字符串可以采用前缀索引

比如针对某个字段的前20个字符建立索引，即：每个值的前20个字符放入索引树中，搜索时会先匹配前而是个字符，再回表到聚簇索引取出来完整的 name 字段值进行比较。但排序（order by 和 group by）时无法使用该索引

where 与 order by 冲突时优先 where

大多数情况下根据索引进行 where 筛选一般筛选出来的数据比较少，然后做排序成本会更低

基于慢SQL查询做优化

可以根据监控后台的一些慢 SQL，针对这些慢 SQL 查询做特定的索引优化（MySQL有提供，只需设置具体参数）

Explain

作用

用于模拟优化器执行SQL语句，在select 语句之前增加 explain 关键字查看执行计划（`不会执行这条 SQL`）其后跟 `show warnings` 命令可以得到优化后的查询语句，从而看出优化器优化了什么，部分时候优化后的SQL是无法执行的 **注意：** 如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中 `explain select * from student_inndb where serialNo='1';` `show warnning ;`![在这里插入图片描述](https://img-blog.csdnimg.cn/da50a8f68b6f44209871ca333c90c454.png)

Explain 中的列

select 的序列号，有几个 select 就有几个 id，id 的顺序是按 select 出现的顺序增长的。 **即：id 越大执行优先级越高，id相同则从上往下执行，id 为NULL最后执行。**

select_type

**对应行是简单还是复杂的查询**。它有以下几种类型： 1) **simple** 简单查询。查询不包含`子查询`和`union`。 `explain select * from student_inndb where serialNo ='1';` ![在这里插入图片描述](https://img-blog.csdnimg.cn/1f41ac00390c488797983c5f4cb93461.png) 2) **primary** 复杂查询中最外层的 select。 3) **subquery** 包含在 select 中的子查询（非 from 子句中的） 4) **derived** 包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）版本较高的 MySQL 会对衍生表进行合并优化，展示前先关闭衍生表优化 `set session optimizer_switch='derived_merge=off'; #on表示打开` 再执行以下 SQL 进行演示 `explain select (select 1 from sys_role where id = 1) from (select * from sys_role where id = 1) derive;` ![在这里插入图片描述](https://img-blog.csdnimg.cn/9abf2e301177430faa8630deffc8d97f.png) 5) **union** 在 union 中的第二个和随后的 select `explain select * from student_inndb where serialNo = '1'union all select * from student_inndb where serialNo='2';` ![在这里插入图片描述](https://img-blog.csdnimg.cn/cb5dcb13e85a457c88afb89da90198a6.png)

table

**这一行正在访问哪个表。** `当 from 子句中有子查询时`，table列是 `<derivenN>`格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。 `当有 union 时`，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行 id。 `explain select * from student_inndb where serialNo = '1'union select * from student_inndb where serialNo='2';` ![在这里插入图片描述](https://img-blog.csdnimg.cn/0a91c11309574005beed6ae3d7718ce5.png)

type

这一列非常非常重要，表示**关联类型或访问类型**。即MySQL决定如何查找表中的行，查找数据行记录的大概范围。最优查询等级划分为：`system > const > eq_ref > ref > range > index > ALL` `一个好的SQL语句至少要达到range级别，最好达到ref。杜绝出现ALL级别.` NULL类型：一个特例，MySQL 执行时，部分查询没有必要访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。此类型的查询语句效率也是极高的。 `explain select min(serialNo) from student_inndb;` ![在这里插入图片描述](https://img-blog.csdnimg.cn/392d226115eb4354bf447d0ad90aed6b.png) 下面来描述具体了类型： 1) **system** 表只有一行记录，const类型的特例，基本不会出现，可以忽略。 `explain select * from (select * from sys_role where id = 1) tmp;` ![在这里插入图片描述](https://img-blog.csdnimg.cn/ed8fe6d2c99248198509bc96665c7ece.png) 2) **const** 通过索引一次就查询出来了，const用于比较`主键索引（primary key）`或`唯一索引（unique key）`。`只需匹配一行数据`，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。 `explain select * from sys_user where id=1;` ![在这里插入图片描述](https://img-blog.csdnimg.cn/ce3f26c403e84f5ab85d4b7112237f2c.png) 3) **eq_ref** `primary key`或`unique key`索引的所有部分被连接使用，唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。简单的 select 查询不会出现这种类型。 `explain select * from sys_user_role left join sys_role on sys_user_role.role_id = sys_role.id;` ![在这里插入图片描述](https://img-blog.csdnimg.cn/2084a994373e4d089f132d326a8485a0.png) 4) **ref** 相比 eq_ref，不使用唯一索引，而是使用`普通索引`或者`唯一性索引的部分前缀`，索引要和某个值相比较，可能会找到多个符合条件的行。 a.简单查询，user_id 为联合索引前缀 `explain select * from sys_user_role where user_id = 1;` ![在这里插入图片描述](https://img-blog.csdnimg.cn/2dc06b1818c54c7f8bac0ce22c99aa2f.png) b.关联表查询，idx_role_user_id是role_id和user_id的联合索引 `explain select role_id from sys_role left join sys_user_role on sys_role.id = sys_user_role.role_id;` ![在这里插入图片描述](https://img-blog.csdnimg.cn/67bb39e0b2084136a56570988643c5c3.png) 5) **range** 范围扫描通常出现在 in, between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引。 `explain select * from sys_user where id > 0;` ![在这里插入图片描述](https://img-blog.csdnimg.cn/e0b5831b95824660896751ce6622df78.png) 6) **index** `只遍历全索引树就能拿到结果，一般是扫描某个二级索引`。这种扫描不会从索引树根节点开始快速查找，而是`直接对二级索引的叶子节点遍历和扫描`，速度还是比较慢的，这种查询一般为使用了**覆盖索引 [^1]**，二级索引一般比较小，所以通常比 ALL 快。 `explain select * from sys_role;` ![在这里插入图片描述](https://img-blog.csdnimg.cn/f15eca5f082b47b89b4460f8d766fc56.png) 7) **ALL** `全表扫描`，扫描你的聚簇索引的所有叶子节点。这种情况通常应该被杜绝，这是就要考虑添加索引来进行优化。 `explain select * from sys_user;` ![在这里插入图片描述](https://img-blog.csdnimg.cn/5ff96e5970b24a03834075a6e6154816.png)

possible_keys

**查询时可能使用哪些索引来查找** 该列值为 NULL，则未使用到索引。有时会出现 possible_keys 有值，而 key 值为 NULL 的情况，这是因为表中数据不多，MySQL 认为索引对此查询帮助不大，而选择全表查询。

key

**用哪个索引来优化** 如果没有使用索引，则该列是 NULL。如果想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 force index、ignore index。

key_len

**MySQL 在索引里使用的字节数** 通过这个值可以算出具体使用了索引中的哪些列 `explain select * from sys_user_role where role_id = 1;` ![在这里插入图片描述](https://img-blog.csdnimg.cn/7b2398cda8f840d1b94d11bc21fb02d0.png) 通过该值，可以推算出该查询使用了 idx_role_user_id 的 role_id 来进行索引查找。 **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字节） - **时间类型：** date（3字节）、timestamp（4字节）、datetime（8字节） - **字段允许为NULL，需1个字节记录** - **索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引**

ref

这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：sys_role.id）

rows

**扫描行数** 该值是预估值。非结果集里面的行数。

Extra

**额外的详细说明信息** 常见的不太友好的值有：Using filesort，Using temporary。 1）Using index：使用覆盖索引 覆盖索引定义：`查询的结果集在索引树里面全部包含，无需回表`，这种情况一般可以说是用到了覆盖索引，extra里一般都有 using index；覆盖索引一般针对的是辅助（二级）索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值 `explain select name from sys_role; name在索引树中可以直接找到` ![在这里插入图片描述](https://img-blog.csdnimg.cn/7d091bc616eb45c9aa3c65790c24270b.png) 2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖 `explain select * from sys_user where name = 'a';` ![在这里插入图片描述](https://img-blog.csdnimg.cn/1dd79983b41a487a8f15543c89cc59d7.png) 3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围； `explain select * from sys_user_role where role_id > 1; ` ![在这里插入图片描述](https://img-blog.csdnimg.cn/cbd080b0093a43ed8c8bf0f0a1440f0f.png) 4）Using temporary：mysql需要创建一张临时表来处理查询。这种情况首先就要想到使用索引进行优化 对比下面两条SQL： `explain select distinct name from sys_user;` `explain select distinct name from sys_role;` 第一条SQL的name无索引，需要临表来去重，第二条SQL的name有索引，无需临时表 ![在这里插入图片描述](https://img-blog.csdnimg.cn/77149efbaeb8454f92d5c73105e66117.png) 5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，数据量大时需要在磁盘进行排序。一般考虑使用索引优化。 对比下面两条SQL： `explain select * from sys_user order by name;` `explain select * from sys_role order by name;` 第一条SQL的name无索引，会检索整个表，保存排序关键字name和对应的id，然后排序name并检索行记录，第二条SQL的name有索引，且表中只又id,name两个索引字段，使用覆盖索引，第三张图片，是添加了一个字段后生成的，由于查询所有，需要检索整个表。![在这里插入图片描述](https://img-blog.csdnimg.cn/36b25b251eac4622aecb14bb7d32a82d.png) 6）Select tables optimized away：使用了聚合函数（比如 max、min）访问某个索引字段 对比下面两条SQL： `explain select max(name) from sys_user; ` `explain select max(name) from sys_role; ` ![在这里插入图片描述](https://img-blog.csdnimg.cn/d2f77749d5f640209800d70ceb13ff68.png)

partitions

高版本 MYSQL 使用 `explain` 直接就可以展示的列，低版本MySQL使用`explain partitions`才能展示。若查询是基于分区表的，会显示查询将访问的分区

filtered

高版本 MYSQL 使用 `explain` 直接就可以展示的列，低版本MySQL使用`explain extended`才能展示，一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的 id 值比当前表 id 值小的表）

MySQL事务与锁

事务

MySQL中的事务是什么

MySQL中的事务是由一组SQL语句组成的逻辑处理单元。数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作。就会带来一系列的问题（脏写，脏读等）。，为了解决多事务的并发问题，数据库设计了**事务隔离机制**、**锁机制**、**MVCC多版本并发控制隔离机制**等来解决多事务并发问题。

ACID属性

原子性(Atomicity)

事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行（操作层面，所有操作要么全部成功要么全部失败）

一致性(Consistent)

在事务开始和完成时,数据都必须保持一致状态（数据层面）

隔离性(Isolation)

数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行（事务处理过程中的中间状态对外部是不可见的）

持久性(Durable)

事务完成之后,它对于数据的修改是永久性的，即使出现系统故障也能够保持

并发事务带来的问题

脏写/更新丢失(Lost Update)

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题：**最后的更新覆盖了由其他事务所做的更新**

脏读（Dirty Reads）

**事务A读取到了事务B已经修改但尚未提交的数据**，还在这个数据基础上做了操作。此时，如果B 事务回滚，A读取的数据无效，不符合一致性要求。

不可重读（Non-Repeatable Reads）

**事务A内部的相同查询语句在不同时刻读出的结果不一致**，不符合隔离性

幻读（Phantom Reads）

**事务A读取到了事务B提交的新增数据，不符合隔离性**

事务的隔离级别

**“脏读”**、**“不可重复读”** 和 **“幻读”** 都是**数据库读一致性问题**，必须由数据库提供一定的事务隔离机制来解决。数据库提供了一些几种隔离级别，来解决这些的问题。**MySQL 默认的隔离级别是可重复读**，一般的公司也默认使用该隔离级别。Spring 框架也可以使用参数来进行设置。如果设置了则使用框架默认隔离级别，未设置则使用数据库设置的隔离级别。

读未提交(Read uncommitted)

读已提交(Read committed)

可重复读(Repeatable read)

可串行化(Serializable)

不同隔离级别解决的事务问题

| 隔离级别 | 脏读 | 不可重复读 | 幻读 | | :------------------------- | :----- | :--------- | :----- | | 读未提交(Read uncommitted) | 可能 | 可能 | 可能 | | 读已提交(Read committed) | 不可能 | 可能 | 可能 | | 可重复读(Repeatable read) | 不可能 | 不可能 | 可能 | | 可串行化(Serializable) | 不可能 | 不可能 | 不可能 | **数据库的事务隔离越严格，并发副作用越小，付出的代价也就越大。** 比如串行化无论增删查改都会对数据进行加锁操作，在这期间，其他事务是无法对这些记录进行操作的。 ```sql #常看当前数据库的事务隔离级别 show variables like 'tx_isolation'; #设置事务隔离级别后面加上对应的英文单词 set tx_isolation='REPEATABLE-READ' ```

锁

数据库中的锁是什么

计算机协调多个进程或线程并发访问某一资源的机制叫锁。在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，**数据也是一种供需要用户共享的资源。**如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素

分类

性能上

乐观锁

**乐观锁（使用版本对比实现）**：每次对数据进行操作都会认为没有其他事务对该数据进行操作，仅在提交前进行比对，被修改则重新操作

悲观锁

**悲观锁**：每次数据操作前都认为会有其他事务对其进行操作，而直接加锁

操作类型上

读锁（共享锁，S锁(Shared)）

针对同一份数据，多个读操作可以同时进行而不会互相影响 **读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞**

写锁（排它锁，X锁(eXclusive)）

当前写操作没有完成前，它会阻断其他写锁和读锁 **读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞**

数据操作粒度上

表锁

**表锁**：**每次操作直接锁住整张表**。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景 ```sql #手动增加表锁(读锁或写锁) lock table 表名称 read/write,表名称2 read/write; #查看表上加的锁 show open tables; #删除表锁 unlock tables; ```

行锁

**行锁**：**每次操作锁住一行数据。**开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高无索引行锁会升级为表锁： **InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁**

间隙锁(Gap Lock)

间隙锁，锁的就是两个值之间的空隙。间隙锁在某些情况下可以解决幻读问题。假设某张表 Student 中有记录如下5条记录： | id | name | | :--- | :--- | | 1 | 张三 | | 2 | 李四 | | 3 | 王五 | | 10 | 赵二 | | 20 | 马六 | 那么这张表就可以划分为三个间隙区间 (3,10)、（10，20）、(20,+∞)，假如在 Session_1 下面执行 update Student set name = '刘一' where id > 8 and id < 18;，则其他 Session 没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在 (3,20] 区间都无法修改数据。假如在 Session_1 下面执行 update Student set name = '刘一' where id > 8 and id < 21;，则其他 Session 没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在 (3,+∞] 区间都无法修改数据。注意**间隙锁在可重复读隔离级别下才会生效。**

临键锁(Next-key Locks)

行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁

MyISAM 和 InnoDB 加锁对比

MyISAM 在执行查询语句 SELECT 前，会自动给涉及的所有表加读锁，在执行 update、insert、delete 操作会自动给涉及的表加写锁。 InnoDB 在执行查询语句 SELECT 时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。 InnoDB存储引擎由于实现了行级锁，在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但整体并发处理能力要远远优于 MYISAM 的表级锁定的。当系统并发量高的时候，InnoDB 的整体性能和 MYISAM 相比就会有比较明显的优势了。但 InnoDB 的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB 的整体性能表现不仅不能比 MYISAM 高，甚至可能会更差。

锁情况分析

```sql #通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况 show status like 'innodb_row_lock%'; ``` 五种状态： 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系统库锁相关数据表：** ```sql #查看事务 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; ```

锁优化建议

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

两种机制

MVCC（多版本并发控制）机制

MySQL在默认的可重复读保证了事务较高的隔离性，同样的一条查询SQL，在一个事务中，多次执行查询即使其他事务已提交的修改也不会影响当前事务SQL的查询结果。这种隔离性就是依靠 **MVCC (Multi-Version Concurrency Control) 机制** 来实现的（可串行化是加互斥锁）。MySQL在**读已提交**和**可重复读**隔离级别下都实现了MVCC机制。**通过MVCC机制避免了频繁加锁影响性能也保证了较高的隔离性。**

实现原理

**MVCC机制实现原理：** 通过 read-view 机制与 undo 版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据

 undo 日志版本链

**undo 日志版本链：** 一行数据被多个事务依次修改过后，在每个事务修改完后，MySQL会**保留修改前的数据 undo 回滚日志**，并且用两个**隐藏字段 trx_id 和 roll_pointer** 把这些**undo日志串联**起来形成一个**历史记录版本链**（如下图）。 ![在这里插入图片描述](https://img-blog.csdnimg.cn/23ff70cae65a4eb2938a1114329c43e3.png) ![在这里插入图片描述](https://img-blog.csdnimg.cn/55ec66d940484d539bd5ff26f8cbb84a.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAQ29kaW5nIE1hY2hpbmU=,size_20,color_FFFFFF,t_70,g_se,x_16) 前面两行是该表中的一行记录，后面两个维护的隐藏字段 `trx_id（事务ID）`和 `roll_pointer（指向上一次修改的记录）`，**蓝色表示当前记录**，红色表示以前的版本。组成`undo日志`和一条完整的`undo日志版本链`。 **可重复读隔离级别中**，当事务开启后，执行任何查询SQL（innoDB）时就会生成当前事务的**一致性视图 read-view**，该视图在事务结束之前都不会变化(**已提交读**是**每次查询时都会生成新的**)。开启事务的 **begin/start transaction** 命令并不是一个事务的起点，在**执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动**，才会向MySQL申请事务ID，MySQL内部是严格按照事务的启动顺序来分配事务ID的。

read view

**可重复读隔离级别中**，当事务开启后，执行任何查询SQL（innoDB）时就会生成当前事务的**一致性视图 read-view**，该视图在事务结束之前都不会变化(**已提交读**是**每次查询时都会生成新的**) **read view的组成：****执行查询时所有未提交事务id数组**（数组里最小的id为 min_id ）和**已创建的最大事务id**（max_id）组成事务里的任何SQL查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。 ![在这里插入图片描述](https://img-blog.csdnimg.cn/f1647e3463494d07a57ce49c1dbc2c9f.png) MySQL 中将事务分为三个区间，如上图，**未提交事务**的事务ID**一定大于min_id**，**已提交事务**的事务ID**不一定大于min_id**，事务ID大于max_id一定是未开始事务。

版本链对比规则

**版本链比对规则：** 1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的； 2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）； 3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况 a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)； b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。对于删除的情况可以认为是 update 的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的 trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

BufferPool 缓存机制

作用

磁盘随机读写的性能非常差的，直接更新磁盘文件在高并发的情况下是不可行的。所以，MySQL提供了一套 BufferPool 缓存机制。**保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能在各种异常情况下也保证数据的一致性。** 更新内存性能极高，顺序写效率也远高于随机读写磁盘文件。BufferPool机制使得MySQL在较高配置的机器上每秒可以并发几千的读写请求。

原理

![在这里插入图片描述](https://img-blog.csdnimg.cn/8dca55ac88cd4a5b8116c5128e30301b.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAQ29kaW5nIE1hY2hpbmU=,size_20,color_FFFFFF,t_70,g_se,x_16)

其他

常用命令

```sql #连接MySQL mysql -h 127.0.0.1 -u UserName -p pwd -P 3306 #创建新用户 CREATE USER 'username'@'host' IDENTIFIED BY 'password'; #赋权限,%表示所有(host)： grant all privileges on *.* to 'username'@'%'; #修改密码 update user set password=password(”123456″) where user=’root’; #查看当前用户的权限 show grants for root@"%"; #显示所有数据库 show databases; #打开数据库 use dbname； #查看库中有哪些表 show tables #显示表mysql数据库中user表的列信息） describe user #查看连接（包括用户、正在执行的操作、状态等） show processlist #刷新连接 flush privileges #关闭某连接 kill id #查询库中所有的表 select * from information_schema.tables where table_schema='zhebase'; #查询表信息（字段，字段类型，是否为空，编码，备注等） select * from information_schema.columns where table_schema='zhebase' and table_name='student_inndb'; #查看MySQL权限 Host列表示那个Ip可以连接，User表示用户，后面的字段是权限 select * from mysql.user; #查看全局服务器关闭非交互连接之前等待活动的秒数 show global variables like "wait_timeout"; #设置全局服务器关闭非交互连接之前等待活动的秒数（默认8小时不发送命令自动断连） set global wait_timeout=28800; ``` ![在这里插入图片描述](https://img-blog.csdnimg.cn/515912026c7e41acaa19fe7ca2ac0210.png)

长连接问题

开发当中我们大多数时候用的都是长连接，把连接放在Pool内进行管理，但是长连接有时候会导致 MySQL 占用内存飙升，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM)，从现象看就是 MySQL 异常重启了。怎么解决这类问题呢？ **1、定期断开长连接。** 使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。 **2、如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。** 这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

SQL执行过程

1.客户端提交一条语句 2.先在查询缓存（相当于一个Map，SQL语句是Key，结果集是Map）查看是否存在对应的缓存数据，如有则直接返回(一般有的可能性极小，因此一般建议关闭查询缓存)。MySQL 8.0开始取消了缓存器，5.0默认关闭 3.交给解析器处理，解析器会将提交的语句生成一个解析树。 4.预处理器会处理解析树，形成新的解析树。这一阶段存在一些SQL改写的过程。 5.改写后的解析树提交给查询优化器。查询优化器生成执行计划。 6.执行计划交由执行引擎调用存储引擎接口，完成执行过程。这里要注意，MySQL的Server层和Engine层是分离的。 7.最终的结果由执行引擎返回给客户端，如果开启查询缓存的话，则会缓存

鸡肋的MySQL查询缓存

- 使用场景极少，表一改动就需要重新维护 - innerDB，MyISAM 等引擎层有 buffer_pool 会自动缓存查询频繁的数据 - 可以使用第三方中间件代替 - LRU淘汰策略 ```sql #my.cnf配置文件中，一般将my.cnf参数 query_cache_type 设置成 DEMAND query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE 关键词时才缓存 ```

MySQL内部组件结构

![在这里插入图片描述](https://img-blog.csdnimg.cn/3e667bca7f4b459ea06d0018daf9eada.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM1Mjk3Mw==,size_16,color_FFFFFF,t_70)

MySQL连接过程

1.完成经典的 TCP 握手建立连接 2.验证用户登录用户名密码 3.验证连接权限，是否运行改Ip连接（User表中的Host字段） 4.开辟专属 session 空间，连接后默认长连接，无操作8小时有效 5.将user表权限加入专属空间 6.每次执行命令在专属空间中查找是否有权限进行操作（权限修改后，如不重写连接，权限仍然不会改变，即使刷新连接也是如此）

MySQL优化器与执行计划

**工作过程：** 1.词法分析、语法分析、语义检查 2.预处理阶段(查询重写等) 3.查询优化阶段(可详细划分为逻辑优化、物理优化两部分) 4.查询优化器优化依据，来自于代价估算器估算结果(它会调用统计信息作为计算依据) 5.交由执行器执行

词法分析器原理

词法分析器分成6个主要步骤完成对sql语句的分析 1、词法分析 2、语法分析 3、语义分析 4、构造执行树 5、生成执行计划 6、计划的执行

查询优化器

- 负责生成 SQL 语句的有效执行计划的数据库组件 - 优化器是数据库的核心价值所在，它是数据库的“大脑” - 优化SQL，某种意义上就是理解优化器的行为 - 优化的依据是执行成本（CBO） - 优化器工作的前提是了解数据，工作的目的是解析SQL，生成执行计划 - 只要有WHERE的地方就会用到重写优化器，并非SELECT独有 **举例：** ```java Select EMPLOYEE.Name , WELFARE.Bonus From EMPLOYEE , WELFARE Where EMPLOYEE.Seniority > 5 And EMPLOYEE.Seniority = WELFARE.Seniority ; Select EMPLOYEE.Name , WELFARE.Bonus From EMPLOYEE , WELFARE Where EMPLOYEE.Seniority > 5 And EMPLOYEE.Seniority = WELFARE.Seniority And EMPLOYEE.Seniority > 5; ``` 查询重写：因为第一条将EMPLOYEE中Seniority>5的行与WELFARE中的所有行作外连接再来找Seniority相等的行，而第二条则是将EMPLOYEE中Seniority>5的行和WELFARE中Seniority>5的行作外连接再来找Seniority相等的行，第二条语句只有更少的行参与外连接，效率更高。写SQL时查询优化器自动重写。

SQL执行顺序

(7) SELECT (8) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP <group_by_list> (6) HAVING <having_condition> (9) ORDER BY <order_by_list> (10) LIMIT <limit_number>

MySQL数据类型选择

在设计表示，选择数据类型时一般先确定大的类型（数字，字符串，时间，二进制），然后再根据有无符号、取值范围、是否定长等确定具体的数据类型。在设计时，尽量使用更小的数据类型以达到更优的性能。并且在定义时尽量使用NOT NULL，避免NULL值。

数值类型

**首先了解：** - **1byte = 8 bit (1字节等于8位，当需要符号时，符号占用1位)** - **float的指数位有8位，尾数位有23位，符号位1位，float的指数范围，为-127~+128，按补码的形式来划分。有效位数7位** - **double 有效位数15位** - **对DECIMAL(M,D) ，如果M>D，为 M+2否则为D+2字节** | 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 | | :---------- | :----------- | :-------------------------------- | :------------------- | :------------- | | TINYINT | 1 字节 | [27,27-1] | [0,28-1] | 小整数值 | | SMALLINT | 2 字节 | [215,215-1] | [0,216-1] | 大整数值 | | MEDIUMINT | 3 字节 | [223,223-1] | [0,224-1] | 大整数值 | | INT/INTEGER | 4 字节 | [231,231-1] | [0,232-1] | 大整数值 | | BIGINT | 8 字节 | [263,263-1] | [0,264-1] | 极大整数值 | | FLOAT | 4 字节 | 约-3.40E+38 ~ 3.40E+38 | 约0~3.40E+38 | 单精度浮点数值 | | DOUBLE | 8 字节 | 约1.7E-308～1.7E+308 | 约0~1.7E+308 | 双精度浮点数值 | | DECIMAL | DECIMAL(M,D) | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 | **建议：** 1. 如果整型数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。 2. 建议使用TINYINT代替ENUM、BITENUM、SET。 3. 避免使用整数的显示宽度，不要用INT(10)类似的方法指定字段显示宽度，直接用 INT。使用显示宽度后会不足自动填充0，但对查询无影响，查询结果不会自动填充0。 4. DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。 5. 建议使用整型类型来运算和存储实数。 6. 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

日期和时间

![在这里插入图片描述](https://img-blog.csdnimg.cn/27705194c2624df7a958075076c70a50.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAQ29kaW5nIE1hY2hpbmU=,size_20,color_FFFFFF,t_70,g_se,x_16) **建议：** 1. MySQL能存储的最小时间粒度为秒。 2. 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。 3. 用MySQL的内使用类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。 4. 当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后）， MySQL会自动返回记录插入的确切时间。 5. TIMESTAMP是UTC时间戳，与时区相关。 6. DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关。 7. 除非有特殊需求，一般的公司建议使用TIMESTAMP，比DATETIME更节约空间，大公司使用DATETIME，因为不用考虑TIMESTAMP将来的时间上限（1970-2037）问题。 8. 不要使用Unix的时间戳保存为整数值，处理起来极其不方便。

字符串

| 类型 | 大小 | 用途 | | :--------- | :---------------- | :----------------------------------------------------------- | | CHAR | 0-255字节 | 定长字符串，char(n)当插入的字符串实际长度不足n时，插入空格进行补充保存。在进行检索时，尾部的空格会被去掉。 | | VARCHAR | 0-65535 字节 | 变长字符串，varchar(n)中的n代表最大列长度，插入的字符串实际长度不足n时不会补充空格 | | TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 | | TINYTEXT | 0-255字节 | 短文本字符串 | | BLOB | 0-65535字节 | 二进制形式的长文本数据 | | TEXT | 0-65535字节 | 长文本数据 | | MEDIUMBLOB | 0-16777215字节 | 二进制形式的中等长度文本数据 | | MEDIUMTEXT | 0-16777215字节 | 中等长度文本数据 | | LONGBLOB | 0-4 294967295字节 | 二进制形式的极大文本数据 | | LONGTEXT | 0-4 294967295字节 | 极大文本数据 | **建议** 1. 字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。 2. CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。 3. 尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。 4. BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。 5. BLOB和TEXT都不能有默认值。