MySQL高级部分
MySQL逻辑架构
总体概述
1.连接层
最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2.服务层
2.1 Management Serveices & Utilities： 系统管理和控制工具
2.2 SQL Interface: SQL接口
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
2.3 Parser: 解析器
SQL命令传递到解析器的时候会被解析器验证和解析。
2.4 Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。
用一个例子就可以理解： select uid,name from user where gender= 1;
优化器来决定先投影还是先过滤。
2.5 Cache和Buffer： 查询缓存。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
3.引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
4.存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
首先，mysql的查询流程大致是：
mysql客户端通过协议与mysql服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法。
查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。。
然后，mysql默认使用的BTREE索引，并且一个大致方向是:无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。
MySQL存储引擎
查看命令:
1 如何用命令查看
#看你的mysql现在已提供什么存储引擎:
mysql> show engines;
#看你的mysql当前默认的存储引擎:
mysql> show variables like ‘%storage_engine%’;
1、InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
2、MyISAM存储引擎
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
3、Archive引擎
Archive档案存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。
Archive表适合日志和数据采集类应用。
根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。
4、Blackhole引擎
Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。
5、CSV引擎
CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
CSV引擎可以作为一种数据交换的机制，非常有用。
CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。
6、Memory引擎
如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。
7、Federated引擎
Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 | 行锁,操作时只锁某一行，不对其它行有影响， 适合高并发的操作 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
| 关注点 | 节省资源、消耗少、简单业务 | 并发写、事务、更大资源 |
| 默认安装 | Y | N |
| 默认使用 | N | Y |
| 自带系统表使用 | Y | N |
索引优化分析
性能下降原因分析:
- 数据过多 —> 分库分表
- 关联了太多的表，太多join —> SQL优化
- 没有充分利用到索引 —> 索引建立
- 服务器调优及各个参数设置 —> 调整my.cnf
Join图:
索引简介
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，
这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上
优势:
- 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势:
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，
都会调整因为更新所带来的键值变化后的索引信息 - 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
MySQL的索引结构
原理图：
BTree索引
【初始化介绍】
一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），
如磁盘块1包含数据项17和35，包含指针P1、P2、P3，
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
【查找过程】
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
B+Tree索引
B+Tree与B-Tree 的区别
1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。
思考：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？
- B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。 - B+树的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
请参考文章:
[https://blog.csdn.net/weixin_42782897/article/details/89960484] “Btree和B+tree的区别”
聚簇索引与非聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。
如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。
聚簇索引的好处：
- 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多个数据块中提取数据，所以节省了大量的io操作。
聚簇索引的限制：
-
对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
-
由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
-
为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种
MySQL索引分类
单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);
#单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name);
#删除索引：
DROP INDEX idx_customer_name on customer;
唯一索引
索引列的值必须唯一，但允许有空值
#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_no)
);
#单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
#删除索引：
DROP INDEX idx_customer_no on customer ;
主键索引
设定为主键后数据库会自动建立索引，innodb为聚簇索引
#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id)
);
CREATE TABLE customer2 (id INT(10) UNSIGNED ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id)
);
#单独建主键索引：
ALTER TABLE customer
add PRIMARY KEY customer(customer_no);
#删除建主键索引：
ALTER TABLE customer
drop PRIMARY KEY ;
#修改建主键索引：
#必须先删除掉(drop)原索引，再新建(add)索引
复合索引
即一个索引包含多个列
#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_name),
KEY (customer_no,customer_name)
);
#单独建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
#删除索引：
DROP INDEX idx_no_name on customer ;
基本语法
创建
CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))
删除
DROP INDEX [indexName] ON mytable;
查看
SHOW INDEX FROM table_name\G
使用ALTER命令
#有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
那些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引
- 单键/组合索引的选择问题， 组合索引性价比更高
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
哪些情况不要创建索引
- 表记录太少
- 经常增删改的表或者字段(Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件) - Where条件里用不到的字段不创建索引
- 过滤性不好的不适合建索引
性能分析
EXPLAIN关键字
Explain + SQL语句
执行后：
各个关键字解释
id*
select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况
- id相同，执行顺序由上至下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同不同，同时存在
关注点
id号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。
select_type*
SIMPLE
简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY
查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
DERIVED
在FROM列表中包含的子查询被标记为DERIVED(衍生)
MySQL会递归执行这些子查询, 把结果放在临时表里
SUBQUERY
在SELECT或WHERE列表中包含了子查询
DEPENDENT SUBQUERY
在SELECT或WHERE列表中包含了子查询,子查询基于外层
UNCACHEABLE SUBQUREY
UNION
若第二个SELECT出现在UNION之后，则被标记为UNION；
若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT
从UNION表获取结果的SELECT
table
显示这一行的数据是关于哪张表的
partitions
代表分区表中的命中情况，非分区表，该项为null
type*
显示查询使用了何种类型，
从最好到最差依次是：
system>const>eq_ref>ref>range>index>ALL
| 属性 | 说明 |
|---|---|
| system | 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描 |
| const | 表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快 如将主键置于where列表中，MySQL就能将该查询转换为一个常量 |
| eq_ref | 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描 |
| ref | 非唯一性索引扫描，返回匹配某个单独值的所有行. 本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而， 它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体 |
| *range | 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引 一般就是在你的where语句中出现了between、<、>、in等的查询 这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。 |
| *index | 出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组 |
| *all | Full Table Scan，将遍历全表以找到匹配的行 |
| index_merge | 在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中 |
| ref_or_null | 对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。 |
| index_subquery | 利用索引来关联子查询，不再全表扫描。 |
| unique_subquery | 该联接类型类似于index_subquery。 子查询中的唯一索引 |
备注：一般来说，得保证查询至少达到range级别，最好能达到ref。
possible_keys
显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key
实际使用的索引。如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引和查询的select字段重叠
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
key_len字段能够帮你检查是否充分的利用上了索引
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
rows
rows列显示MySQL认为它执行查询时必须检查的行数。----越少越好
filtered
这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数
Extra*
| 属性 | 说明 |
|---|---|
| Using filesort *** | 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序” |
| Using temporary *** | 使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。 |
| USING index | 表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！ 如果同时出现using where，表明索引被用来执行索引键值的查找; 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。 利用索引进行了排序或分组 |
| Using where | 表明使用了where过滤 |
| using join buffer *** | 使用了连接缓存 |
| impossible where | where子句的值总是false，不能用来获取任何元组 |
| select tables optimized away | 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者 对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算， 查询执行计划生成的阶段即完成优化。 |
单表使用索引及常见索引失效
索引(失效案例)
-
全值匹配我最爱
-
最佳左前缀法则 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
-
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
-
存储引擎不能使用索引中范围条件右边的列
-
mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
-
is not null 也无法使用索引,但是is null是可以使用索引的
-
like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
-
字符串不加单引号索引失效
一般性建议
- 对于单键索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
- 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
- 书写sql语句时，尽量避免造成索引失效的情况
关联查询优化
建议
- 保证被驱动表的join字段已经被索引
- left join 时，选择小表作为驱动表，大表作为被驱动表。
- inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
- 子查询尽量不要放在被驱动表，有可能使用不到索引。
- 能够直接多表关联的尽量直接关联，不用子查询。
子查询优化
尽量不要使用not in 或者 not exists ------ 用left outer join on xxx is null 替代
排序分组优化
ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
如果不在索引列上，filesort有两种算法：
mysql就要启动双路排序和单路排序
GROUP BY关键字优化
group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引
MySQL锁机制
1.锁的分类
按锁粒度从大到小分类：表锁，页锁和行锁；以及特殊场景下使用的全局锁
如果按锁级别分类则有：共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁；
以及Innodb引擎为解决幻读等并发场景下事务存在的数据问题，引入的Record Lock（行记录锁）、Gap Lock（间隙锁）、Next-key Lock（Record Lock + Gap Lock结合）等；
还就是我们面向编程的两种锁思想：悲观锁、乐观锁。
#1.加表锁
lock table tableName (read|write) -- 读锁或写锁
#2.解锁
unlock tables;
2.表锁
表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。
当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，大大降低并发度。
使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。
| Session-1 | session-2 |
|---|---|
| 1.为表mylock加表级读锁 lock table mylock read | 2.select * from mylock；可以查出记录 |
| 3.select * from mylock；可以查出记录 | 4.select * from dept 本库中的另一张表 可以查出记录 |
| 5.select * from dept 无法查询 报错 | 6.update dept 可以更新 |
| 7.update mylock 无法更新 报错 | 8.update mylock 无法更新 被阻塞 |
| 9.unlock tables 解锁 | \9. 阻塞状态瞬间结束更新成功 如果一直不解锁 等待时间过长报错执行失败 |
总结表级读锁：表级读锁 A连接锁住了T表
-
A连接只能读取T表 不能写T表(报错) 不能读写任何其他表
-
B连接只能读取T表 不能写T表(阻塞状态 等待A连接释放锁立刻执行写操作 如果A连接不解锁 等待时间过长报错执行失败) 可以读写其他表
表锁总结
- 简而言之 就是读锁会阻塞所有的(包括上锁的连接)写 所有连接可以一起读
- 写锁阻塞不包括本连接在内的所有的读写 本连接可以读写
- 上锁的连接只能操作被自己锁住的表 不能操作其他表
show open tables；查看那些表被锁了
show status like 'table%'
table_locks_immdeiate 表示表级锁定的次数 表示可以立即获取锁的查询次数，每立即获取锁 值加一
table_locks_waited 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加一) 此值高说明存在着较严重的表级锁争用情况
3.行锁
InnoDB与MyISAM最大的不同点： 1. InnoDB支持事务 2.InnoDB有行锁
行锁支持事务
与表锁正相反，行锁最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力从而提高系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
使用行级锁定的主要是InnoDB存储引擎。
- 适用场景：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新数据的情况，同时又有并发查询的应用场景。
A在事务中更新了T1表中的一条记录 ，就为这条记录加上了行锁，其他连接只能读这条记录，写这条记录要阻塞
等待A连接的提交 但是对其他行的记录可以读写
行锁失效
我们在演示上面的行锁的时候 update xxx where 后面都是有索引的列 如果我们update xxx where时条件不是索引列
或者是索引列但是我们因为某些操作导致索引失效那么就会把行锁变为表锁
也就是说 行锁的前提是where条件要是索引列 所以我们在更新的时候最好按照主键来更新
3.页锁
除了表锁、行锁外，MySQL还有一种相对偏中性的页级锁，页锁是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
使用页级锁定的主要是BerkeleyDB存储引擎。
4.共享(读)锁(Share Lock)
共享锁，又叫读锁，是读取操作（SELECT）时创建的锁。其他用户可以并发读取数据，但在读锁未释放前，也就是查询事务结束前，任何事务都不能对数据进行修改（获取数据上的写锁），直到已释放所有读锁。
如果事务A对数据B（1024房）加上读锁后，则其他事务只能对数据B上加读锁，不能加写锁。获得读锁的事务只能读数据，不能修改数据。
SQL显示加锁写法:
SELECT … LOCK IN SHARE MODE;
在查询语句后面增加LOCK IN SHARE MODE，MySQL就会对查询结果中的每行都加读锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请读锁，否则会被阻塞。其他线程也可以读取使用了读锁的表，而且这些线程读取的是同一个版本的数据。
5.排他(写)锁(Exclusive Lock)
排他锁又称写锁、独占锁，如果事务A对数据B加上写锁后，则其他事务不能再对数据B加任何类型的锁。获得写锁的事务既能读数据，又能修改数据。
SQL显示加锁写法:
SELECT … FOR UPDATE;
在查询语句后面增加FOR UPDATE，MySQL 就会对查询结果中的每行都加写锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请写锁，否则会被阻塞。另外成功申请写锁后，也要先等待该事务前的读锁释放才能操作。
- 共享锁很好理解，就是多个事务只能读数据不能改数据
- 排它锁：对于排他锁大家的理解可能就有些差别，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁
6.意向锁（Intention Lock）
意向锁属于表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁：
意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是 InnoDB 自动加的，不需要用户干预。
再强调一下，对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。
2856
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
