MySQ

注意：所有文章除特别说明外，转载请注明出处.

MySQL高级

数据文件：

MyISAM：

1. frm 文件：存放表结构

   2. myd 文件：存放表数据

      3. myi 文件：存放表索引

INNODB：

1. frm 存放表结构
2. ibd：

   1.MySQL逻辑架构

​

​

连接层：，所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的 ：连接处理，身份验证，安全性等等。

服务层。这是MySQL的核心部分。通常叫做 SQL Layer。在 MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断， sql解析，行计划优化， query cache 的处理以及所有内置的函数(如日期,时间,数学运算,加密)等等。各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视 图等。

引擎层：通常叫做StorEngine Layer ，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统 一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏 了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事 物，或者取出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器 的请求。

存储层：数据存储层，主要是将数据存储到运行于裸设备上的文件系统之上，并完成与存储引擎的交互。

2.存储引擎

1.查看命令：

show engines;

支持八种存储引擎，默认使用InnoDB。

2.InnoDB 和 MyISAM

2.2.1两者的区别：

2.2.2 为什么MyISAM 查询要比 InnoDB 要快

INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多；
1）数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少；
2）innodb寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快
3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护

MVCC ( Multi-Version Concurrency Control )多版本并发控制：

​ InnoDB：通过为每一行记录添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。但是InnoDB并不存储这些事件发生时的实际时间，相反它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。让我们来看看当隔离级别是REPEATABLE READ时这种策略是如何应用到特定的操作的：

​ SELECT InnoDB必须每行数据来保证它符合两个条件：

　　1、InnoDB必须找到一个行的版本，它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)。这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。

　　2、这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。

2.2.3主要区别：

• 1).［事务］MyISAM是非事务安全型的，而InnoDB是事务安全型的，默认开启自动提交，宜合并事务，一同提交，减小数据库多次提交导致的开销，大大提高性能。
• 2).［锁］MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
• 3).［全文索引］MyISAM支持全文类型索引，而InnoDB不支持全文索引。
• 4).［查询效率］MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
• 5).［外健］MyISAM不支持外健，InnoDB支持。
• 6).［count］==MyISAM保有表的总行数，InnoDB只能遍历==。
• 6).MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
• 7).InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。
• 8）MyIsam索引和数据分离，InnoDB在一起，MyIsam天生非聚簇索引，最多有一个unique的性质，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为“聚簇索引”
• 具体见：https://blog.csdn.net/silyvin/article/details/80140153

9）InnoDB提供多版本数据支持 https://blog.csdn.net/zhangliangzi/article/details/51379274，MyIsam不支持

10）两者都仅支持B＋树索引，不支持hash索引

2.2.4应用场景：

• 1).MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行==大量的SELECT查询==，那么MyISAM是更好的选择。
• 2).InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要==执行大量的INSERT或UPDATE操作==，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

2.2.5为什么存储要使用B+树

定义：

​ B+树是一种平衡的多路查找树

特点：

1. 有n棵子树的结点中含有n个关键字，每个关键字不保存数据，只用来索引，所有数据都保存在叶子节点。

2. 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3. 所有的非终端结点可以看成是索引部分，结点中仅含其子树（根结点）中的最大（或最小）关键字。

人话：

1. 每一个父节点的元素都出现在子节点中，是子节点中最大（最小的）元素。
2. 叶子节点包含了全部的数据信息；
3. 每一个叶子节点都指向了下一个叶子节点，形成了一个有序链表；
4. ==**非叶子节点，只起到索引的作用。**==，不存储数据。

==优势：==

1. 单一节点存储更多的元素，使得查询的IO次数更少。

2. 所有查询都要查找到叶子节点，查询性能稳定。

3. 所有叶子节点形成有序链表，便于范围查询。(底部有链表)

4. B+树还有一个最大的好处，方便扫库，B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持。这是==数据库选用B+树的最主要原因==。

3.索引优化分析

3.1 SQL执行顺序

select
	* 
from 
	<left_table> <join_type>
where
	<where_condition>
group by
	<group_by_list>
having
	<having_condition>
order by
	<order_by_condition>
limit
	<limit_number>

3.2 ==7种join理论==

表结构：

​

inner join
select * from emp e inner join dept d on  d.id = e.deptid;

left join 
select * from emp e left join dept d on  d.id = e.deptid;

right join
select * from emp e right join dept d on  d.id = e.deptid;

A 的独有（B的 id为 null）
select * from emp e left join dept d on  d.id = e.deptid where d.id is null;

B 的独有（A 的id为null）
select * from emp e right join dept d on  d.id = e.deptid where e.id is null;

全集
 select * from emp e right join dept d on  d.id = e.deptid
 union
 select * from emp e left join dept d on d.id= e.deptid;

A的独有 加上 B的独有
select * from emp e right join dept d on  d.id = e.deptid where e.id is null
union
select * from emp e left join dept d on  d.id = e.deptid where d.id is null;

3.3 什么是索引：

​ 索引是一种数据结构；

​ 官方定义：索引是一种帮助MySQL高效获取数据的数据结构。

​ 可以简单理解为是一种==排好序的快速查找数据结构==

3.3.1 索引的优势劣势

优点：创建索引可以大大提高系统的性能:
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

​ 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
​ 第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
​ 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

3.3.2索引的分类

• 单值索引：即一个索引包含一个列
• 唯一索引：索引列的值必须唯一
• 复合索引：一个索引可以包含多个列

==创建索引==：

创建：
create index indesName on tableName(columnName(length));

alter tableName Add index indexName on (columnName(length));

删除;
Drop index indexName on tableName；

查看索引：
show index from tableName;

3.3.3 那些情况适合建立索引？

1. 主键建立唯一索引
2. 频繁作为查询条件的字段
3. 查询中与其他表有关联的字段，比如外键建立索引
4. 查询中排序的字段创建索引将大大提高排序的速度
5. 查询中统计或者分组字段
6. ==高并发的情况下一般选择复合索引==

3.3.4 那些情况不适合建立索引？

1. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引文件。
2. where条件里用不到的字段不创建索引
3. 表记录太少。
4. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。如果某个数据列包含许多重复的内容，为它建立索引就没太大的实际效果。

3.3.5 什么是聚簇索引？什么是非聚簇索引？

​ 在《数据库原理》里面，对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。==正式因为如此，所以一个表最多只能有一个聚簇索引==。

不过这个定义太抽象了。在SQL Server中，索引是通过二叉树的数据结构来描述的，我们可以这么理解

​ 聚簇索引：索引的叶节点就是数据节点。

​ 非聚簇索引：叶节点仍然是索引节点，只不过有一个指针指向对应的数据块

3.4 性能分析前提

1. MysqI中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行划(他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)

2. 当客户端向MySQL请求一条Query,命令解析器模块完成请求分类，区别出是SELECT并转发给MySQLQuery Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些 常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一- 些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有)，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

3.5explain 命令

3.5.1 怎么玩？

explain + SQL 语句
例:
	explain select * from emp;

​ 包含的信息：

3.5.2 能干什么？

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以被使用
• 哪些索引实际被使用
• 表之间的引用
• 每张表有多少行被优化器查询

3.5.3 explain id介绍：

​ select 查询的序列号，包含一组数字，表示查询中执行的select字句或者操作表的顺序。

​ 三种情况：

1. id 相同：执行顺序由上至下
2. id 不同：值越大越先执行
3. id 相同 也 不同：值越大越先执行，相同的执行顺序由上至下

3.5.4 explain之select_type和table介绍

table：显示这一行数据时来自于哪一张表的。

select_type：显示此次查询的类型：

1. simple：简单的 select 查询，其中不包含子查询或者 union
              2. primary：查询中若包含任何复杂的子部分，则最外层被标记为primary
          3. subquery：子查询
                    4. derived：衍生表
        5. union：若第二个select 出现在union 之后 ，则被标记为union
                 6. union result：

3.5.5 explain之type介绍

type代表查询用了何种类型

==从最好到最差依次为：==

​ ==system>const>eq_ref>ref>range>index>all==

一般来说最好达到 range 最好是 ref！

type详解：

1. system：表只有一行记录，这是const类型的特例，平时不会出现，可以忽略不计
2. const：常量查询
3. eq_ref：唯一性索引扫描，常见于主键或者唯一索引
4. ref：非唯一性索引扫描，返回匹配某个值的所有行（本质上也是索引查询），他可能会查出多个行与之匹配；
5. range：范围查询
6. index：遍历索引树
7. all：全表扫描

3.5.6 explain之possible_keys和key介绍

possible_keys：理论上要用到的索引，==实际上并不一定被用到。==

key：实际被用到的索引

3.5.7explain之key_len介绍

key_len：表示索引中用到的字节数，可以通过该数值计算查询中使用到的索引的长度，==越小越好==

key_len大，查询的精度一般也大。理论上key_len越小越好，但是查询精度的提高必须得牺牲一定的空间

3.5.8 explain之ref介绍

ref： 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或者常量被用于查找索引列上的值。

3.5.9 explain之rows介绍

rows：根据表统计的信息以及索引的选用情况，大致估算出找到所需记录所==需要读取的行数。==

3.5.10 explain之Extra介绍

主要有以下三种情况：

​ Using filesort：九死一生，使用了文件内排序（MySQL中无法利用索引完成的排序操作称为:文件排序”）

​ Using temporary：十死无生，用不上索引，还使用了临时表

​ Using index：发财了，大好事，用上了索引

==3.6心法口诀==

==全值匹配我最爱，最左前缀要遵守；==
==带头大哥不能死，中间兄弟不能断；==
==索引列上少计算，范围之后全失效；==
==百分like加右边，覆盖索引不写星；==
==不等空值还有or，索引失效要少用；==
==字符串里有引号，SQL高级也不难；==

3.7小表驱动大表（exists 和 in）

永远小表驱动大表！！！
select * from emp e where e.deptno in (select deptno from dept d);（in 里面是小表）

select * from emp e where exists (select 1 from dept d where e.deptno = d.deptno);
（exists 里面是大表）

3.8 order by 和 group by 优化

order by 里面默认是升序 ASC。别跳楼机（上下上下）。

order by 满足以下两种情况会使用useing index：
==1、order by 语句使用索引最左前列==
==2、使用 where 字句 和 order by 字句条件列组合满足索引最左前列。==

3.8.1 单路双路算法

​ order by 双路算法：Mysql4.1之前是使用双路排序，字面的意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段，在buffer进行排序，再从磁盘读取其他字段。文件的磁盘IO非常耗时的，所以在Mysql4.1之后，出现了第二种算法，就是单路排序。
​ 读取两次 排序一次 读取数据一次

​ order by 单路算法：从磁盘读取查询所需要的所有列，按照ORDER BY在buffer对它进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在了内存里。
​ 是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序

​ 存在问题：有可能单路排序算法一次拿不出数据，那么就还比双路排序更消耗IO，效率更慢！
在sort_buffer中，单路排序要比双路排序占很多空间，因为单路排序把所有的字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能读取sort_buffer容量大小的数据，进行排序(创建tmp文件，多路合并)，排完再取sort_buffer容量大小，再次排序…从而多次I/O。偷鸡不成蚀把米。
​ ==一次拿不完，需要多拿几次。。。==
比如：内存就是2M，一次查1000条数据刚好，也就是最大1000条数据，但是一次要查5000条，那么不够了，照这样需要查5次刚好，如果把2M改为10M，那么就刚好了

3.8.2 如何提高 order by的速度：

1. order by 时select * 不要用，这是一个大忌，要写出只需要query 的字段即可。

   ​ a：当查询的字段大小综合小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法—单路排序，否则用老算法—多路排序。假设只需要查10个字段，但是SELECT *会查80个字段，那么就容易把sort_buffer缓冲区用满。

   b：两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size大小。
   

2. 尝试提高 sort_buffer_size

   ​ 不管用哪种算法，提高这个参数都会提高效率。当然要根据系统能力去提高，因为这个参数是针对每个进程的。

3. 尝试提高 max_length_for_sort_data

   ​ 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率

3.8.3 order by 内功心法：

为排序使用索引
==MYSQL两种排序方式：文件排序和扫描有序索引排序==
==Mysql能为排序和查询使用相同的索引。也就是创建索引先把数据排序了，查询的时候再利用索引，一举两得。==

KEY a_b_c(a,b,c)
order by 能使用索引最左前缀

ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,c DESC

如果where使用索引的最左前缀定义为常量，则order by 能使用索引

WHERE a = const ORDER BY b,c
WHERE a = const AND b = const ORDER BY c
WHERE a = const AND b > const ORDER BY b,c

不使用索引进行排序

ORDER BY a ASC,b DESC, c DESC //排序不一致
WHERE g = const ORDER BY b,c //丢失a索引
WHERE a = const ORDER BY c //丢失b索引
WHERE a = const ORDER BY a,d //d不是索引的一部分
WHERE a in (….) ORDER BY b,c //对于排序来说，多个相等条件也是范围查询

GROUP BY的优化

GROUP BY实质上是先排序后进行分组，遵照索引的最佳左前缀。
当无法使用索隐裂，考虑增大max_length_for_sort_data和sort_buffer_size的参数设置。
==WHERE 高于 HAVING，能写在WHERE限定的条件就不要去HAVING限定了。==

4.慢查询日志

4.1 什么是慢查询日志？

​ MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中==响应时间超过阀值==的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

​ long_query_time的默认值为10，意思是运行10S以上的语句。

​ 默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

4.2 查看慢查询日志是否开启

​ 命令：(默认关闭的!!如果不是调优需要请不要开启！)

查看：
show variables like '%slow_query_log%';

设置开启：
set global slow_query_log = 1;

需要注意的是使用set global slow_query_log=1;开启慢查询日志==只对当前数据库生效,如果MySQL重启后则会失效。==

一般不建议永久开启慢查询日志，如果一定要做，只能修改配置文件!!(自行百度)

4.3 设置其他参数

4.3.1 查看当前默认慢查询时间：

show VARIABLES LIKE '%long_query_time%'；
默认是10秒

long_query_time默认是10秒。也就是==大于10秒==的sql会记录在慢查询日志中。非大于等于。

4.3.2 修改慢查询时间：（需要重连）

set global long_query_time = 3; 设置慢查询时间为3秒

show variables like '%long_query_time%';再次查看 不好使了

show global variables like '%long_query_time%';需要这个命令查看

然后重新连接就好了。

4.3.3 查看慢查询条数

命令：

show GLOBAL STATUS LIKE '%Slow_queries%';

4.3.4 永久生效配置：

4.4 日志分析工具 mysqldumpslow

​ 在生产环境之中，如果要手工分析日志，查找，分析SQL，显然是一个体力活，MySQL提供了日志分析工具，mysqldumpslow！

4.4.1 windows怎么做！！

​ 以下是windows环境需要安装其他支持软件的步骤，非windows直接忽略
​ windows要执行mysqldumpslow需要安装ActivePerl因为windows上这是一个perl脚本,需要安装ActivePerl才能执行，linux下就可以直接执行mysqldumpslow了。
​ 安装ActivePerl的步骤我就不多说了。安装好后会在环境变量PATH中找到perl的bin目录。我重启了电脑让环境变量生效。

验证ActivePerl是否安装成功

​

4.4.2 怎么玩？

5. show profile

5.1show profile 是什么？

是MySQL提供用来分析当前会话中语句执行的资源消耗情况，可以用于SQL调优的测量。

默认情况下是关闭的，默认存储最近15条的运行记录。

5.2 怎么玩？

1. 开启功能，默认是关闭的
2. 运行SQL （select * from emp;）
3. 查看结果：show profiles;
4. 进行诊断：show profile cpu，block io for query 上一步前面的问题SQL的号码

步骤：
show variables like 'profiling';查看是否开启

set profiling = on;开启
 
show variables like 'profiling';再次查看

运行SQL：

==查看结果：==

show profiles;

进行诊断：

show profile cpu,block io query 6;

show profiles 后面还可以有别的参数：

5.3致命伤：

那么如何查看show profile + 参数 查询出来的结果呢？

==如果在status出现了以下内容，证明sql可能有问题==

1. converting HEAP to MYISAM

2. Creating tmp table 创建临时表

3. Copying to tmp table on disk

4. locked

5.3.1 converting HEAP to MYISAM

==查询结果太大。内存不够用了，往磁盘上搬了。==

5.3.2 Creating tmp table

==创建临时表：==

​ ==拷贝数据到临时表==

​ ==用完再删除==

5.3.3 Copying to tmp table on disk

==把内存中临时表复制到磁盘，危险！！！！==

5.3.4 locked（表锁了）

5.3.5 其他演示：

6.数据库的锁机制

6.1 锁的定义

锁是计算机用来协调多个进程或线程并发访问某一个资源的机制。

在数据库之中，除了传统的计算资源（cpu，RAM，IO等）的争用之外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性，有效性是所有数据库，必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁是十分重要的，也十分复杂。

6.2锁的分类

读写锁，行锁，表锁。

读锁（共享锁）：针对同一份数据，多个读操作之间同时进行 不会有任何的影响

写锁（排它锁）：当前操作没有完成的时候，他会阻断其他的写锁和读锁。

表锁（偏读）

行锁（偏写）

7. MyISAM 锁

7.1 读锁（MyISAM）

偏向 MyISAM引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率高，并发度最低。

建表：

7.1.1 手动添加表锁：

lock table 表名字  read(write), 表名字2 read(write),其它;

show open tables;查看表的锁定情况

lock table mylock read,emp write;
给mylock表加读锁（共享锁），给emp表加写锁（排它锁）

unlock tables; 释放锁

7.1.2 读锁场景模拟：

给mylock 加上读锁：

都可以查互不影响。

尝试session 1 进行update 操作：

不可以进行update操作；

尝试session 1 进行select 其他表操作：

不可以进行读其他表操作；

尝试 session 2 进行update 操作；

session 2 一直处于阻塞状态；

==在session 1解除锁定之后，session 2 才会执行。==

==7.1.3 读锁总结==

session 1	session 2
可以读mylock	可以读mylock
不可以读别的表	可以读别的表
不可以update mylock	不可以update（必须等待session 1释放锁）
不可以update 其他表	可以update其他表

==一句话：读锁，别的可以读，写要阻塞，自己只能读自己。==

7.2写锁（MyISAM）

7.2.1 写锁场景模拟

session 1 给 mylock 加上 写锁；

session 1 可以读 可以写 不可以的读其他的

sessoin 2 连读都不可以

只有在 session 1 释放锁之后才可以查询；

7.2.2 写锁总结

session 1	session 2
可以读mylock	不可以读mylock
不可以读别的	可以读别的
可以update mylock	不可以update mylock

==一句话总结：写锁，自己只可以自己玩自己的，不能玩别的，别人只能玩别的，不能玩你的。（独占）==

7.3 MyISAM案例总结

锁类型	可否兼容	读锁	写锁
读锁	是	是	否
写锁	是	否	否

结论:

结合上表,所以对MyISAM表进行操作,会有以下情况:

1、对MyISAM表的读操作(加读锁) ,不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。

2、对MyISAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作

==简而言之,就是读锁会阻塞写,但是不会堵塞读。而写锁则会把读和写都堵塞,==

7.4 其他查看命令

看看那些表 被锁了

show status like 'table%';

解释：

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每次获取数值加一；
Table_locks_waited：出现表级锁定而发生等待的次数（不能立即获取锁，每次等待数值加一），==此值高说明出现严重的表级锁争用情况。==

show status like ‘table%’;

主要看两行的后一行 Table_locks_waited的值偏大是不好的；

==myisam的读写锁调度是写优先，myisam不适合做写为主表的引擎。写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。==

8. INNODB 锁

InnoDB与MyISAM的最大不同有两点：一是支持事务，而是采用了行级锁。

8.1 数据库的事务（ACID）：

⑴ 原子性（Atomicity）

​ 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

⑵ 一致性（Consistency）

　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

　　拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

⑶ 隔离性（Isolation）

　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

　　即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

　　关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

⑷ 持久性（Durability）

　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

　　例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

8.2 事务的隔离性

​ 以上介绍完事务的四大特性(简称ACID)，现在重点来说明下事务的隔离性，当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，在介绍数据库提供的各种隔离级别之前，我们先看看如果不考虑事务的隔离性，会发生的几种问题：

​8.2.1更新丢失

​ 当两个或多个事务选择同行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题一一最后的更新覆盖了由其他事务所做的更新。
​ 例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。

如果在一一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

8.2.2 脏读

​ 一个事务读到另外一个事务已经修改还没有提交的数据。此时如果A事务回滚，那么 B读取到的数据就无效了，不符合一致性。

8.2.3 不可重复读

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读”.

事务A读取到了事务B已经提交的修改数据，不符合隔高性

一句话:一个事务先后读取同一条记录，但两次读取的数据不同。

8.2.4 幻读

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读“；

一句话:事务A读取到了事务B体提交的新增数据，不符合隔高性。

多说一句:幻读和脏读有点类似，

==脏读是事务B里面修改了数据，幻读是事务B里面新增了数据。==

8.3 数据库的事务隔离级别

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

隔离级别	读数据一致性	脏读	不可重复读	幻读
读未提交（read-uncommitted）	最低级别，只能保证不读取物理上损坏的数据	是	是	是
读已提交（read-committed）	语句级	否	是	是
可重复读（repeatable-read）	事务级	否	否	是
串行化（serializable）	最高级别，事务级	否	否	否

​ 数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“==串行化”==进行，这显然与==“并发”==是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

==8.3.1 INNODB RR 隔离级别下 如何 避免幻读的==

官方文档：在 RR 级别下，如果查询条件能使用上唯一索引，或者是一个唯一的查询条件，那么仅加行锁，如果是一个范围查询，那么就会给这个范围加上 gap 锁或者 next-key锁 (行锁+gap锁)。

• 其实 就是 通过 快照读（伪MVCC模式） + undo log + read view

  • TRX_ID(事务 ID), ROLL_PTR(回滚指针)

  

• next-key锁（行锁 加上 gap锁）

  • gap 出现的场景
    • 不走索引（会上锁所有的gap）
    • where 命中不全（5,7,9 只命中了 5,7） 那么会在 5 - 9 之家加上 gap锁

8.3.1 查看当前事务的隔离级别

show variables like 'tx_isolation';
默认是 RR（可重复读） 级别

8.3.2 ：Spring 中的事务传播行为

在TransactionDefinition接口中定义了七个事务传播行为：

（1）PROPAGATION_REQUIRED 如果存在一个事务，则支持当前事务。如果没有事务则开启一个新的事务。

（2）PROPAGATION_SUPPORTS 如果存在一个事务，支持当前事务。如果没有事务，则非事务的执行

（3）PROPAGATION_MANDATORY 如果已经存在一个事务，支持当前事务。如果没有一个活动的事务，则抛出异常。

（4）PROPAGATION_REQUIRES_NEW 总是开启一个新的事务。如果一个事务已经存在，则将这个存在的事务挂起。

（5）PROPAGATION_NOT_SUPPORTED 总是非事务地执行，并挂起任何存在的事务

（6）PROPAGATION_NEVER 总是非事务地执行，如果存在一个活动事务，则抛出异常；

（7）PROPAGATION_NESTED如果一个活动的事务存在，则运行在一个嵌套的事务中. 如果没有活动事务, 则按TransactionDefinition.PROPAGATION_REQUIRED 属性执行

8.3.3 事务是否只读

是否只读

​ 如果事务只对后端的数据库进行读操作，数据库可以利用事务ID只读特性来进行一些特定的优化。通过将事务设置为只读，你就可以给数据库一个机会，让他应用它认为合适的优化措施。因为是否只读是在事务启动的时候由数据库实施的，所以只有对那些具备可能启动一个新事务的传播行为（ PROPAGATION_REQUIRED , PROPAGATION_REQUIRED_NEW , PROPAGATION_NESTED ）的方法来说，才有意义。

8.3.3 事务超时

​ 为了使应用程序很好地运行，事务不能运行太长时间。因为超时时钟会在事务开始时启动，所以只有对那些具备可能启动一个新事务的传播行为（
PROPAGATION_REQUIRED , PROPAGATION_REQUIRED_NEW , PROPAGATION_NESTED
）的方法来说，才有意义。

8.3.34.事务回滚

​ 事务回滚规则定义了哪些异常会导致事务回滚而哪些不会。默认情况下，事务只有在遇到运行时期异常才回滚，而在遇到检查型异常时不会回滚

1. Spring管理事务默认回滚的异常是什么？

​ 答案是 RuntimeException

8.4 行锁（INNODB）

8.4.1 行锁案例模拟

建表，建立索引；

演示前先把 autocommit 关闭了，

set autocommit = 0；

模拟开始：

1) session 1 update 一行数据：

session 2 查询不到，因为是session 1 没有提交；

提交之后：

2) session 1 和 session 2 同时 update 同一行数据：

session 1 完成了 ，session 2 处于阻塞状态。

3） session 1 和 session 2 同时 update 不同行数据：

两个sessionupdate不同的行，不会有冲突

==8.4.2 索引失效行锁变表锁==

update test_innodb set a = 41 where b = 4000;  varchar 没加单引号 导致索引失效
update test_innodb set b = '5000' where a= 5; 导致不同行也会阻塞，行锁变为表锁

8.4.3 间隙锁的危害

前提a的值并不是自增的，1,3,4,5,6,7,8,9(表数据没有 2 )

演示：

session 1 操作 a>1 and a<6 的数据，虽然没有 2 但是他把2 也锁了，导致session 2 插入阻塞；

间隙锁:

​ 什么是间隙锁？

​ 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁;==对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”==，

​ InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁) 。

​ 危害

​ 因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。

​ 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定.而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

​ ==简单来说，session1使用了范围条件检索数据，比如id范围在3到10，那么即便id为6的数据不存在，它这不存在的一行也会被锁。如果另一个session想要插入id为6的数据的时候，就会阻塞。==

8.4.4 如何锁定一行

三步走：

begin;
select * from test_innodb where a = 3 for update;
这时候其实已经锁定了某一行
commit;  
提交后就解锁了

锁定一行的三步：

begin;

select * from test_innodb_lock where a=8 ==for update==;

这时候其实已经锁定了某一行

commit; 提交后就解锁了

8.4.5 行锁总结

​ Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的 整体性能和MyISAM相比就会有比较明显的优势了。

​ 但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

8.4.6 查看行锁状态

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：从系统启动到现在，总等待次数

对于这五个变量，有三个比较重要:

==Innodb_row_lock_current_waits:当前正在等待的锁定的数量==

==Innodb_row_lock_waits：从系统启动到现在，总等待次数==

==Innodb_row_lock_time：从系统启动到现在，锁定的总时间长度==

==8.4.7 优化建议==

1）尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。

2）合理设计索引，尽量缩小锁的范围

3）尽可能较少检索条件，避免间隙锁

4）尽量控制事务大小，减少锁定资源量和时间长度

5）尽可能低级别事务隔离

==8.5 prepare（预处理） 机制==

8.5.1 普通流程

​ 指客户端将要执行的SQL发送给服务器，服务器先进行编译后立刻执行

• mysql客户端发送SQL指令，服务器端接受指令，进行编译执行，返回结果给客户端
• 客户端接受执行结果，解析结果、
• 客户端再次发送sql指令(可能相同)，服务器端接受指令，编译执行，返回执行结果
• 客户端接受执行结果，解析结果

​ 这样每条sql语句请求一次，mysql服务器就要接收并处理一次，当一个脚本文件对同一条语句反复执行多次的时候，mysql服务器压力会变大

8.5.2 prepare 流程

​ 是指客户端将要执行的SQL先发送给服务器，服务器先进行编译，不执行。等到客户端要服务端执行时，发送一条执行指令，让服务器执行已经提前处理好的SQL指令是减轻服务器压力的一种技术。

• mysql客户端发送SQL指令(预处理包装)，服务器端接受指令，进行编译，返回预处理编译结果

• 客户端接受编译结果，解析结果，发送预处理执行指令

• 服务器端接受指令，执行编译后的预处理指令，返回执行结果

• 客户端接受执行结果，解析结果

==其实就是省去了 编译的时间。==

8.5.3 prepare 如何操作

将sql语句强制一分为二：

• 第一部分为前面相同的命令和结构部分
• 第二部分为后面可变的数据部分

​ 在执行sql语句的时候，首先将前面相同的命令和结构部分发送给MySQL服务器，让MySQL服务器事先进行一次预处理（此时并没有真正的执行sql语句），而为了保证sql语句的结构完整性，在第一次发送sql语句的时候将其中可变的数据部分都用一个数据占位符来表示！比如问号？就是常见的数据占位符！

8.5.4 预处理实现

1、 设置语句

2、set 值

3、 EXECUTE 执行

4、 删除 PREPARE

mysql> PREPARE prod FROM "INSERT INTO examlple VALUES(?,?)"; 
mysql> SET @p='1'; 
mysql> SET @q='2'; 
mysql> EXECUTE prod USING @p,@q; 
mysql> SET @name='3'; 
mysql> EXECUTE prod USING @p,@name; 
mysql> DEALLOCATE PREPARE prod;

​ 每一次执行完EXECUTE时，须执行DEALLOCATE PREPARE … 语句，这样可以释放执行中使用的所有数据库资源（如游标），不仅如此，如果一个session的预处理语句过多，可能会达到max_prepared_stmt_count的上限值

==8.5.5 预处理优点==

• 预处理的执行效率相对于一般的sql执行操作，效率比较高,因为第二次执行只需要发送查询的参数，而不是整个语句
• 由于每次执行预处理语句时仅需发送参数的数据，从而减少了网络通信量
• 使用PAREPARE STATEMENT可以减少每次执行SQL的语法分析
• 预处理可以防止sql注入，因为预处理将sql语句与数据分开发送

9.mysql 主从复制

.