MySQL学习笔记

Mysql数据结构选择

索引的本质：索引是帮助MySQL高效获取数据的排好序的数据结构。
索引的数据结构有：

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

MySql存储引擎：B+Tree（B-Tree的变种）。
选取B+Tree的原因（举例：按顺序插入1，2，3，4，5，6，7，8，9等九个数字，查找数字6）：

• 二叉树：二叉树在插入节点时，会根据根节点将大于根节点的数字插入二叉树的右边，将小于根节点的数据插入到二叉树的走边，所以在按顺序插入的时候，1为根节点，之后的数据全部在根节点的有右边，所以查找的时候需要查找6次。

• 红黑树：自平衡二叉查找树，同二叉树一样，只是在插入的的时候会自动变成一个平衡二叉树。因此在插入上述9个数据的时候会自动平衡成一棵平衡二叉树，相比较二叉树而言查询效率更高。如图片所示：查找6的时候只需要在和根节点对比一次，大于根节点，查找右节点对比是6，及查找成功。
  

• Hash表：因为Hash表是key-val结构，所以索引只存对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。很多时候比B+Tree索引更高效。但同时Hash只能满足“=”的条件，不能，不能适用于范围查找，且自身存在Hash冲突问题。Hash索引的优缺点

• B-Tree：多路搜索树，B-Tree的叶节点具有相同的深度，叶指针为空，所有索引元素不重复，叶节点的数据从左到右递增排序。B-Tree索引每个索引下对应着该索引的数据信息。

• B+Tree：是B-Tree的变种，相比较B-Tree在节点将数据存储的方式，B+Tree非叶子节点不存储数据信息，只存储索引，以存储更多的索引信息；叶子节点包含所有的索引字段；叶子节点用指针连接起来，提高了区间的访问性能。
  
  总结选取B+Tree的原因：MySQL存储引擎使用page这种存储形式，一个page比如是16k，那么在没有存储数据信息的B+Tree中（假设一个索引是bigint类型，占用8byte），16*1024/8=2048个索引。同样B-Tree（假设一个节点带着数据的大小为1k）中，那么只能存储16/1=16个。在存储的1000万数据时：
  B+Tree树的高度（及查询次数）：
  B-Tree树的高度：
  
  由此可见n为树的高度，以及查询的次数，B+Tree查询的效率要比B-Tree快很多。

InnoDb存储引擎

索引分类（就InnoDB存储引擎）

• 聚簇（聚集）索引：将数据存储与索引放到了一块，找到索引也就找到了数据，每个表只能有一个，一般在主键上，如果没有设置主键，那么mysql会自己查找一列数据都不相同的列作为聚簇索引，如果没有全部数据不相同的咧，那么会自己创建一个虚拟的rowId，保证数据的唯一性。
• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行
• 联合索引（非聚簇索引）：多个字段组成的索引，该索引具有索引最左前缀原则，会先根据最左便的字段进行排序。
  **注意：**在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

InnDB索引实现（聚集）：

• 表数据文件本身就是按B+Tree组织的一个索引文件。
• 聚集索引的叶节点包含了完整的数据记录。
• InnoDB表必须建立主键（为了根据主键完成聚簇索引的构建）
• InnoDB推荐使用的主键是整型且自增（整型数字占用空间少，因为InnoDB索引是排好序的，整型数字对比起来简单，构建聚簇索引时更快）
• 非聚簇索引结构的叶子节点存储的值是主键值：是为了一致性和节省存储空间，所以根据非聚簇索引查找数据时，最后需要在根据主键Id回表在去查一下，根据聚簇索引查找是直接出结果。
  联合索引的底层存储结构的样子：
  

索引设计原则

1. 代码先行,索引后上(一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引)

2. 联合索引尽量覆盖查询条件(比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的
   where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。)

3. 不要在小基数的字段上建立索引(例如:性别 男/女这种的字段)

4. 长字符串我们可以采用前缀索引(例如:varchar(255)创建索引所在内存较大,可以KEY index(name(20),age) 这样将前面的字段添加到联合索引中)

5. where与order by冲突时优先where

SQL优化分析器Explain

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈
在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是
执行这条SQL
**注意：**如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中
官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

Explan执行结果

explain中的列的信息：

1. id列
   id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。
   id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
2. select_type列
   select_type 表示对应行是简单还是复杂的查询。
   1）simple：简单查询。查询不包含子查询和union
   
   2）primary：复杂查询中最外层的 select
   
   3）subquery：包含在 select 中的子查询（不在 from 子句中）
   
   4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
   用这个例子来了解 primary、subquery 和 derived 类型

 mysql> set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合并优化
 mysql> set session optimizer_switch='derived_merge=on'; #还原默认配置

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

3. table列
这一列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。
4. type列
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到range级别，最好达到ref
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。
const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。
system是const的特例，表里只有一条元组匹配时为system。

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

ref：相比较eq_ref，不使用唯一索引，二十使用普通索引或者唯一索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的结果。

range：范围扫描，通常出现在在in(),between,>,<,>=等范围行操作中（like也是，前提是like的字段加有索引，如果没有索引的话就是All）。使用一个索引给定范围的行。
remark字段加有索引：

remark字段没有索引：

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

ALL：即全盘扫描，扫描你的聚簇索引下的所有叶子节点。通常这种情况下添加索引解决来进行优化。

5.possible_keys列
这一列显示的是查询时可能用到的索引。
explain时可能出现possible_keys有列，而key显示NULL情况，这种情况是因为表中数据不多，mysql认为索引对查询的帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

6.key列
这一列显示的是查询时实际用到的索引。
如果没有用到索引则为NULL。如果想强制mysql查询时使用或者忽略possible_keys列中的索引，在查询中使用force index（强制使用）、ignore index（忽略使用）。

7.key_len列：该列显示的时使用的索引的字节数，通过该字段可以确定具体使用了索引中的那些列。
key_len计算规则：

• 字符串类型，char(n)或者varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果时utf-8，一个数字或字母均占1个字节，一个汉字占3个字节。
  • char(n)：如果存汉字，则长度为3n字节。
  • varchar(n)：如果存汉字，则长度为3n+2字节，加的2字节用来存储字符串的 长度，因为varchar是变长字符串。
• 数值类型
  • tingint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节
• 时间类型
  • date：3字节
  • timestamp：4字节
  • datetime：8字节
• 如果字段允许为NULL，则需要1字节来记录是否为NULL。

8. ref列：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名
9. rows列：该列展示的是mysql估计要读取并检查的行数。注意：这个不是结果集里面的行数，是mysql自己的预估值。
10. Extra列：这一列展示的是额外的信息，常见的重要值如下：
    1）Using index：使用覆盖索引
    覆盖索引定义：MySQL执行计划explain结果里面的key有使用索引，如果select后面查询的字段都可以从这个索引树中获取，这种情况一般可以说用到了覆盖索引，extra里一般都有using index； 覆盖索引一般针对的都是辅助索引，整个查询结果只通过辅助索引就拿到结果，不需要辅助索引树找到主键，然后在通过主键去主键索引树中获取其他字段的值。

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

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

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

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

6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

索引使用总结

1.全值匹配
2.最左前缀法则（如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。）
3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
4.存储引擎不能使用索引中范围条件右边的列（例如联合索引中三个值，当第二个值使用范围查找时，第三个索引值就使用失败）。
5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句。
6.mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。
7.is null,is not null 一般情况下也无法使用索引。
8.like以通配符开头（‘$abc…’）mysql索引失效会变成全表扫描操作。（最好是like索引字段前几个字符是确定的，比如like ‘LI%’等，不要使用like‘%il’。如果一定使用like ‘%ll%’时，使用覆盖索引，查询字段必须建立覆盖索引字段，不要使用select * ；如果不能使用覆盖索引则可能需要借助搜索引擎）。
9.字符串不加单引号索引失效（注意数据类型一定要匹配）。
10.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。
11.范围查询优化，由于mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，所以当发现添加条件后的查询和全盘扫描相差不多是就会选择全盘扫面，因为走索引还需要跟据索引的叶子节点的id去主索引树中查找。所以当想走索引的时候就可以将范围分成多个区间进行。

常见的SQL优化

• 数据统计count优化，运行效率：
  有索引时：count()≈count(1)>count(字段)>count(主键 id)
  无索引时：。 count()≈count(1)>count(主键 id)>count(字段)

• 联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描。

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

• like KK% 一般情况都会走索引。

• Order by与Group by优化

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

  Using filesort详解：
  filesort文件排序方式

• 单路排序（排序结果直接返回）：是一次性取出满足条件的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里面显示<sort_key,additional_fileds>或者<sort_key,packed_additional_fileds>

• 双路排序（又叫回表排序模式，排序后的结果需要根据行ID去取其他字段信息）：是首先根据条件取出相应的排序字段和可以直接定位的行数据ID，然后在sort_buffer中进行排序，排序完成后，需要根据ID再次去取回其他字段数据信息；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >。

  Mysql通过不比较系统变量max_length_for_sort_data(默认为1024字节)的大小和需要查询的字段总大小来判断是使用那种排序方式。

• 如果总字段长度小于max_length_for_sort_data，那么使用单路排序模式。

• 如果总字段长度大于max_length_for_sort_data，那么使用双路排序模式。
  注意，如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

set max_length_for_sort_data = 10; ‐‐employees表所有字段长度总和肯定大于10字节

• 分页查询优化

  --分页查询sql
  select * from employees limit 10000,10;
  

  表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查 询了 10 条记录，实际这条 SQL 是先读取 10010条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。
  常见的分页场景优化技巧：
  1、根据自增且连续的主键排序的分页查询
  没有添加order by，表示通过主键索引

  select * from employees limit 90000,5;
  --优化为：
  select * from employees where id > 90000 limit 5;
  

  该优化需要同时满足两个条件：
  1. 主键自增且连续，中间不能缺失。
  2. 结果必须是按照主键排序的。
  2、根据非主键字段排序的分页查询

  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;
  

  优化关键是：让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录。

• Join关联查询优化
  mysql的表关联常见有两种算法：

  • Nested-Loop Join 算法

  • Block Nested-Loop Join 算法

    1、嵌套循环连接 Nested-Loop Join（NLJ）算法 ：一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到相关字段，根据关联字段在另一张表（被驱动表）里面取出满足条件的行，然后取出两张表的结果集合。

    • 当使用inner join 和 join时，优化器一般会选择数据量比较小的表作为驱动表，所以使用inner join 和 join时，并不一定排在前面的就是驱动表。
    • 当使用left join时，左表为驱动表,右表为被驱动表
    • 当使用right join时,右表时驱动表左表是被驱动表
    • 使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。
      如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block Nested-Loop Join算法。

    2、基于块的嵌套循环链接Black Nested-Loop Join (BNL)算法:把驱动表中的数据添加到join_buffer中,然后继续扫描被驱动表,把被驱动表每一行取出来跟join_buffer中的数据做对比.
    Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。

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

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

  对于join的优化:

  • 关联字段添加索引,让MySQL做join操作时尽量选择NLJ算法.
  • 小表驱动大表,写多链接sql时如果明确知道那张表时小表可以用straight_join写法固定链接驱动方式,省去MySQL自动优化的时间.
    注意:
  • straight_join只适用于inner join,不适用left_join 和 right_join,因为后面两个已经确定了驱动表
  • 尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

  小表的定义:在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据 量，数据量小的那个表，就是“小表”，应该作为驱动表。

• in和exsits优化
  原则:小表驱动大表,及小的数据驱动大的数据.
  in: 当B表数据集小于A表数据集时,in优先于exists

  select * from A where id in (select id from B)
  

  exists:当A表的数据集小于B表的数据集时,exists优先于in

  select * from A where exists (select 1 from B where B.id = A.id)
  

  existe解释: 将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留.

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

• 基于慢SQL查询做优化
  可以根据监控后台的一些慢sql,针对慢sql查询做特定的索引优化.
  关于慢sql查询不清楚的可以参考这篇文章：https://blog.csdn.net/qq_40884473/article/details/89455740

数据库的事务

概述

我们的数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题。
这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。

事务及其ACID

事务:是有一组SQL语句组成的逻辑处理单元, 事务具有一下四个属性,通常简称为事务的ACID属性.

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

并发事务处理带来的问题

• 更新丢失(Lost Update)或脏写:当两个或多个事务处理同一行数据,然后基于最初选定的值进行更新改行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新的问题-最后的更新覆盖了其他事务的更新操作.
• 脏读(Dirty Reads):一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。
  一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。 此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。
• 不可重复读(Non-Repeatable-Reads):一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
  一句话：事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
• 幻读(Phantom Reads):一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
  一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

事务隔离级别

“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制
来解决

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。
同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。
常看当前数据库的事务隔离级别: show variables like ‘tx_isolation’;
设置事务隔离级别：set tx_isolation=‘REPEATABLE-READ’;
Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别.

锁

概念

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

锁分类

• 从性能上分为乐观锁(用版本对比实现)和悲观锁
• 从对数据库操作上分为读锁和写锁(都是悲观锁)
  • 读锁(共享锁,S锁(Shared)):针对同一份数据,多个读操作可以同时进行且相互之间不受影响.
  • 写锁(排他锁,X锁(eXclusive)):当前写操作没有完成前,他会阻断其他写锁和读锁
• 从对数据操作的粒度上分为表锁和行锁

  • 表锁:每次操作锁住整张表.开销小,加锁快;不会出现死锁;锁的粒度大,发生锁冲突的概率较高,并发度最低;一般应用于整张表数据搬迁场景.

    • 手动添加表锁:

      ock table 表名称 read(write),表名称2 read(write);
      

    • 查看表上加过的锁

      show open tables;
      

    • 删除表锁

      unlock tables;
      

  • 行锁:每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最
    高。

    InnoDB与MYISAM的最大不同有两点：

    1. InnoDB支持事务(TRANSACTION)
    2. InnoDB支持行级锁

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

• 间隙锁(Gap Lock)
  间隙锁:锁的就是两个值之间的空隙.
  Mysql默认级别是repeatable-read，有办法解决幻读问题吗？
  间隙锁在某些情况下可以解决幻读问题。
  假设account表里数据如下：
  
  那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，
  在Session_1下面执行 update account set name = ‘zhuge’ where id > 8 and id <18;，则其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据 ，即id在(3,20]区间都无法修改数据，注意最后那个20也是包含在内的。
  间隙锁是在可重复读隔离级别下才会生效。

• 临键锁(Next-key Lock)
  Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

  无索引行锁会升级为表锁:锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁.(InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为
  表锁。)

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

  锁分析

  通过检查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:系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：
Innodb_row_lock_time_avg （等待平均时长） Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，
然后根据分析结果着手制定优化计划。

查看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;

死锁:
set tx_isolation=‘repeatable-read’;
Session_1执行：select * from account where id=1 for update;
Session_2执行：select * from account where id=2 for update;
Session_1执行：select * from account where id=2 for update;
Session_2执行：select * from account where id=1 for update;
查看近期死锁日志信息：show engine innodb status\G;
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

锁优化建议

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

此文档是我跟随视频学习的记录总结,如有问题欢迎大家指出,谢谢支持