Mysql调优及索引锁机制学习_mysql 走索引

Mysql

存储引擎简介

查看存储引擎

在mysql中，我们可以使用以下两个命令来查看当前使用和mysql默认支持的存储引擎。
show engines;

show variables like ‘%storage_engine%’;

可以看到Mysql一共支持非常多的存储引擎，目前主流的存储引擎就是InnoDB和MyIsam两种，实际上，5.5版本之后的mysql默认支持的存储引擎就是InnoDB。

MyIsam和InnoDB的区别

索引

索引（Index）是帮助Mysql高校获取数据的数据结构，可以类比字典的目录，是将数据排好序的快速查找的数据结构。

索引详解

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，
这样就可以在这些数据结构之上实现高级查找算法，这种数据结构就是索引。
下图，为索引的一种可能的数据结构示例：
为了加快col2数据信息的查找，可以维护一个右图所示的二叉查找树，每个节点分别包含一个索引键值和一个指向对应数据记录物理地址的指针。如此就可以利用二叉查找在一定的复杂度内获取到响应的数据，从而快速检索出符合条件的记录。
一般来说索引本身也很大，不可能全部存储在内存之中，因此索引往往以索引文件的形式存储在磁盘上。

**我们常说的索引，如果没有特别指明，一般多指B树（多路搜索树，不一定是二叉树）组织结构的索引。**其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是适用的B+树索引，统称索引。当然，除了B+树的数据结构之外，还有哈希索引（hash Index）等。

索引的优劣势

优势

• 类似图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本；
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

• 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引也是要占用空间的；
• 虽然索引大大提升了查询速度，同时却会降低更新表的速度，如对表进行Insert，Update，Delete操作都会调整因为更新所带来的键值变化后的索引信息；
• 索引只是提高效率的一个因素，如果Mysql中有大量数据的表，就需要花时间研究建立最优秀的索引。

索引分类

• 单值索引：即一个索引只包含单个列，一个表可以有多个单值索引；
• 唯一索引：索引列的值必须唯一，但是允许有空值；
• 复合索引：即一个索引包含多个列。

索引相关的命令

创建索引

• create [是否是唯一索引(unique)] index 索引名称 on 表名 (字段列表);
• alter 表名 add [是否是唯一索引(unique)] index [索引名称] on (字段列表);

删除索引

• drop index [索引名称] on 表名;

查看索引

• show index from 表名;

四种添加索引的案例

• alter table 表名 add primary key (字段列表);
  该语句添加一个主键索引，这意味着这个索引必须是唯一的，且不能为null；
• alter table 表名 add unique 索引名称(字段列表);
  该语句添加一个唯一索引，但是允许列值为null；
• alter table 表名 add index 索引名称(字段列表);
  添加普通索引，索引值允许出现多次；
• alter table 表名 add fulltext 索引名称(字段列表);
  该语句表示添加一个全文索引。

如何确定是否应该建立索引？

哪些情况应该建立索引？

• 主键自动建立唯一索引；
• 频繁作为查询条件的字段应该建立索引；
• 查询中与其他表关联的字段，外键关系字段建立索引；
• 频繁更新的字段不适合建立索引，因为对记录的每次更新同时会对索引进行更新；
• where条件中用不到的字段不建立索引；
• 单列/组合索引的选择：在高并发的情况下倾向建立组合索引；
• 查询中排序的字段如果通过索引去访问将大大提升排序速度；
• 查询中统计或者分组的字段也应该建立索引。

哪些情况不适合建立索引？

• 表的数据量过少；
• 经常增删改的表；
• 数据重复且分布平均的表字段，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果，因此应该只为最经常查询和最经常排序的数据列建立索引。

explain

explain表示查看执行计划，使用explain关键字可以模拟优化器执行sql查询语句，从而知道Mysql是如何处理你的sql语句的，进而分析你的查询语句或是表结构的性能瓶颈。

如何使用explain?
在我们的查询语句之前加上explain关键字就可以查看这条sql的执行计划：

可以看到两条命令的结果不同，下面的结果显示就是sql的执行计划。

explain可以做什么？

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

explain信息介绍

使用explain查看sql的执行计划会出现如下的表格信息：

接下来就依次介绍表格分别记录了什么数据。

id

id字段代表select查询的序列号，包含一组数字，表示查询过程中执行select子句或操作表的顺序。
事实上，我们写的sql的执行顺序很多时候并不像我们想的那样，mysql会根据自己的理解以及认为最优的情况来执行sql，id字段就是显示的mysql真正对sql的执行顺序。

explain中id会出现的三种情况：

• id相同，代表表的执行顺序由上至下。
  
• id不同，如果是子查询，id的序号会递增，id值越大的优先级越高，越先被执行。
  
• id有相同的也有不同的
  
  如果id值相同，可以认为是一组，在组中按照顺序执行，在所有组中id值越大的优先级越高，越先被执行。
  如上图中所示，第一行的表<derived2>代表的就是sql中的s1表，是mysql衍生出的虚拟表，后缀是2代表是id为2的表衍生出的，即<derived2>表是由id为2的t3表衍生而出。

select_type

select_type表示sql中每个表被查询时的查询类型，主要是用于区别普通查询，联合查询，子查询等复杂查询。

select_type的取值分类：

• SIMPLE：简单的select查询，查询中不包含子查询或者时UNION；
• PRIMARY：如果查询中包含任何复杂的子查询部分，则最外层的查询表被表示为PRIMARY主查询；
• SUBQUERY：在select或者where列表中包含的子查询查询的表被表示为SUBQUERY；
• DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生表），mysql会执行这些子查询，然后将结果放在临时表中；
• UNION：若第二个select出现在union之后，则被标记为NUION，若union被包含在from的子查询中，外层select将被标记为DERIVED；
• UNION RESULT：表示两个select被union连接之后查询出的结果集。

table

显示查询的数据来源于哪张表。

type

该字段显示mysql查询这张表的时候使用了何种类型，一般来说的类型又块到慢如下：
system>const>eq_ref>ref>range>index>all

• system：表只有一行记录，等于系统表，是const类型的特例；
• const：当mysql确定查询最多只会有一行匹配的时候，mysql优化器会在查询前读取它并且只读取一次，因此非常快。例如当主键被当作where子句的条件时，mysql会将这个查询转化为一个常量；
• eq_ref：唯一性索引扫描，指查询过程中对于每个索引键来说查询的结果只有一条与之匹配，常见于主键索引或者唯一键索引；
• ref：非唯一性索引扫描，返回匹配某个单独值得所有行。简单的来说就是eq_ref会根据索引找到仅有的一行数据，但是ref有可能会找到多行数据，所以它应该属于查找和扫描的混合体；
• range：根据索引检索给定范围的行，这个索引字段一般就是where语句中出现的between，>，<，in等查询的条件。这种范围的索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引；
• index：仅表示使用了索引，index与all的区别在于index只扫描了索引树，这通常比all快，因为索引文件通常比数据文件小；
• all：便利全表找到目标数据。

possible_keys

显示可能应用在这张表中的索引，一个或者多个。查询涉及到的字段上如果存在索引，则索引将被列出，但不一定被实际查询所使用。

key

在查询中实际使用的索引，如果为null，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅出现在key列表中。

key_len

表示在查询中使用的索引的字节数，可通过该列计算查询中使用的索引长度，在不损失精度的前提下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表的定义来计算的，并不是通过表检索出来的。

ref

显示索引中哪一列被使用了，如果可能的话，也可能是一个常量，表示索引的哪些列或者常量被用于查找。

rows

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

Extra

包含不适合在其他列显示但是十分重要的额外信息。

Extra的取值：

• Using filesort：说明在查询的排序中mysql无法使用定义的索引进行排序，而是自己在内部进行了排序，mysql中无法使用索引完成的排序操作称之为“文件排序”；
• Using temporary：说明Mysql在对查询结果排序时使用了临时表保存中间结果，常见于排序order by和分组查询group by；
• Using index：表示对应的select操作中使用了覆盖索引，避免访问了表的数据行，直接在索引中获取数据，效率不错。如果同时出现Using where表名在查询语句的条件查询中也使用到了索引，如果没有同时出现Using where，表明索引被用来读取数据而非执行查找动作；
• Using where：表明使用了where过滤查询；
• Using join buffer：表明使用了连接查询缓存；
• impossible where：表示where子句的值总是flase，不能用来获取任何元素；
• select tables optimized away
• distinct

覆盖索引
覆盖索引也被称之为索引覆盖，意思为目标查找的数据直接从索引树中就可以获取到，不必查询数据行。Mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说就是查询的列要被所建的索引完美覆盖。

索引优化案例

单表优化案例

建表sql

CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);

INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');

SELECT * FROM article;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

**案例：**查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 author_id。
解决案例的sql：SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

使用explain查询该sql的执行计划：
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

由上图可以看到，在查询的过程中是以便利全表的形式来获取到数据的，并且在排序的过程中也没有索引可以使用，采取的是文件排序的方式。

尝试创建复合索引优化sql：
创建索引：create index idx_article_ccv on article(category_id,comments,views);
查看索引：

创建了索引之后使用explain查看原sql的执行计划：

分析数据发现，使用索引之后查询的type变成了range，表示使用了索引进行范围查询，key字段也表示确实使用了刚刚创建的索引，但是extra中Using filesort文件排序仍然存在！

出现这种情况的原因是因为当复合索引做条件的时候遇到了范围查询之后，范围查询后面的条件将不会使用索引，由此，以上sql中order by子句其实是没有使用到索引的。

所以在此为了解决文件排序的问题，就必须要舍弃这个范围的索引查询，删除之前的索引，创建新的索引只包含category_id和views字段。

删除索引：drop index idx_article_ccv on article;
创建新的索引：create index idx_article_cv on article(category_id,views);
再次查看原sql的执行计划：

发现不存在文件排序的情况，并且索引查询的类型为ref，属于可以接受的类型，优化完成。

多表连接查询优化案例

建表sql

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

我们知道在多表连接的时候我们应该在连接字段上建立索引，但是两个表都有对应的连接字段，我们应该在哪一种情况下对哪一张表建立索引呢？

双表左外连接sql优化
查看左外连接的sql执行计划：explain select * from book left join class on book.card = class.card;
查看结果：

可以看到这条sql在没有索引的情况下是双表的全局扫描，共计扫描了40条数据。
接下来，我们尝试在左外连接的左表book上建立索引：create index idx_book_card on book(card);
再次查看原sql的执行计划：

可以看到，添加索引之后在左外连接中确实是使用到了索引，但是扫描的行数并没有变化，接下来删除这个索引，将索引建立在右表class的card字段上试试：
create index idx_class_card on class(card);
查看sql执行过程变化：

发现不仅使用到索引，而且扫描的条数由总计40条变化为了21条。
发生这种情况的原因是由于左右外连接的特性，所以应当在左外连接的时候将索引建立在右表的关联字段上，反之右外连接亦然。因为左外连接中左表的信息是会被全部查询出来的，所以全表扫描是必然的，右外也是一样，索引就必须建立在对立表中才有效果。

索引优化

索引失效

什么是索引失效？索引失效时指创建了索引却没有用到或者部分条件没有用到索引，类似于单表优化案例中的情况，当索引遇到reang范围查询的时候其后的查询语句就无法使用索引了。
接下来我们将采用案例来展示和分析各种索引失效的场景：

建表语句

CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24)  NULL DEFAULT '' COMMENT '姓名',
  age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表' ;

INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());

SELECT * FROM staffs;

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

由sql可知，创建了一个内部员工表插入数据并且根据name、age、pos字段创建了一个复合索引。
按照复合索引使用条件查询查看sql的执行过程：
explain select * from staffs where name='july' and age = 23 and pos = 'dev';

由以上信息可以看出，该sql使用了索引并且三个字段都使用了，索引长度为141，检索行数为1。
上面的情况是对复合索引正确的使用情况，接下来演示两种复合索引失效的情况：
explain select * from staffs where age = 23 and pos = 'dev';

由上可以看到，当使用age、pos字段进行查询的时候并不会经过索引，之前设定的复合索引失效了。
explain select * from staffs where name='july' and pos = 'dev';
当我使用name和pos字段进行条件查询的时候，发现使用的索引长度和仅仅使用name字段进行查询的索引长度是一致的，也就是说上方的两个条件的查询中pos字段的条件查询其实是索引失效的。

由上查询条件的索引失效或者部分失效就证实了复合索引的最佳左前缀法则。

最佳左前缀法则

指的是当使用复合索引作为查询条件的时候，查询条件的字段应当与复合索引的字段保持一致，从左边的字段开始并且顺序也要保持一致才能保证索引的不失效和不部分失效。
如上方的案例中，索引的字段顺序是name、age、pos，如果不从最左侧的name字段开始使用age和pos进行条件查询就会导致索引失效，如果使用name和pos进行查询就会因为字段缺失导致部分查询条件索引失效。
当复合索引的顺序是name、age、pos，查询的条件顺序为name、pos、age的时候也会使用到索引，mysql会内部对查询条件进行顺序转换，转换为和复合索引一致的情况。

不要在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效转向全表扫描。

例如：

上图两条sql都可以实现简单的条件查询，但是下面的使用了mysql的函数对字段进行了处理，所以导致了索引失效。

mysql中查询条件使用非等于（!=或者<>）会导致索引失效

is null和is not null是否会走索引

在mysql5.6的版本中测试结果：is null会走索引，is not null不会走索引。

关于like模糊查询相关的索引问题

建表语句：

CREATE TABLE `tbl_user` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `NAME` VARCHAR(20) DEFAULT NULL,
 `age` INT(11) DEFAULT NULL,
 email VARCHAR(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(NAME,age,email) VALUES('1aa1',21,'b@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('2aa2',222,'a@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('3aa3',265,'c@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('4aa4',21,'d@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('aa',121,'e@163.com');
1
2
3
4
5
6
7
8
9
10
11
12
13

创建name字段的索引：create index idx_user_name on tbl_user(name);
查看不同的like语句对索引的使用情况：

由数据可知，只有like语句的形式为aa%的时候才会使用到索引，模糊查询的左侧有%的情况都是无法使用索引的。
但是其实在模糊查询中%aa%的形式是最常用的，如何使得即可以使用这种形式又可以走索引呢？
我们可以使用覆盖索引解决这个问题：

由上图可以看到，当我们使用查询name字段的形式而不是select *的时候%aa%和%aa这两种形式都可以使用索引。这是因为之前创建的索引就是以name字段来创建的，在此形成了覆盖索引。如果查询的字段不符合覆盖索引的规律则同样也是无法使用索引的，例如我们不仅仅查询name字段，还加上email字段：

同样的，like也属于范围查询的内容，当like的百分号在右边的时候是可以使用索引的，但是like之后的查询条件就无法使用索引，因为范围查询之后的条件都会索引失效。
字符串类型的条件查询必须要加上引号，否则会索引失效

原因是因为当条件字段的数据类型为字符串的时候，你不将数值用引号包裹起来mysql会认为你传递的是一个整数，但是字段又是字符串类型，所以在后台mysql会自动的进行数据的类型转换。由上的经验可知，当索引字段进行计算或者自动、手动的类型转换时会失效。

少用or进行条件查询，遇到or会导致索引失效

order by优化

关于sql的order by子句，要尽可能的使用索引，避免mysql内部的文件排序。
建表sql

CREATE TABLE tblA(
  id int primary key not null auto_increment,
  age INT,
  birth TIMESTAMP NOT NULL,
  name varchar(200)
);

INSERT INTO tblA(age,birth,name) VALUES(22,NOW(),'abc');
INSERT INTO tblA(age,birth,name) VALUES(23,NOW(),'bcd');
INSERT INTO tblA(age,birth,name) VALUES(24,NOW(),'def');

CREATE INDEX idx_A_ageBirth ON tblA(age,birth,name);

SELECT * FROM tblA; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

使用order by的时候尽可能在索引列上完成排序，遵循复合索引的最佳左前缀法则。
例如，如上建表语句所示，创建了age、birth、name三个字段的复合索引，接下来演示 不同的order by子句使用索引的不同情况：

由截图的情况可以看出，当order by遵循了复合索引的最佳左前缀法则的时候是可以按照索引进行排序的，但是没有按照法则就会出现Using filesort的情况。

当遇到排序顺序的问题时，除了要遵循最佳左前缀法则的之外，排序顺序就必须要保持一致，如果第一个字段是升序排序，第二个字段是降序排序的时候就会导致索引失效。

并且，当查询条件与复合索引的字段顺序不一致的时候，mysql的优化器会在内部进行顺序的优化然后按照索引去查询，但是在排序中字段的顺序不一致并不能使用索引。

如果sql中同时有where条件以及order by排序，当where条件的字段使用了索引的最左前缀且定义为常量，则oredr by不符合最佳左前缀也能够使用索引

如上图所示，两条sql都可以使用索引，并且没有出现文件排序，即便order by子句并没有遵循最佳前缀，原因在于order by子句可以使用被定义为常量的where子句条件字段来组合成完整的复合索引。

如上图出现范围查询的时候，只要order by子句的字段和where范围查询之前的查询条件字段能够组成复合索引就不会出现文件排序。

如上图where子句的常量字段是age，order by子句字段是name，则不遵循最佳左前缀法则，出现了文件排序。

关于Using filesort的排序算法
由上述案例可知，当order by没有使用到自定义的索引的时候就会出现文件排序，文件排序是Mysql内部的排序法则，最好的情况是尽可能避免这种情况。

但是当无法避免的时候我们又应该如何的来改善sql的执行速度呢？首先，我们就先来了解filesort的排序算法。

双路排序
在mysql4.1之前采取的排序算法都是双路排序，双路排序会对磁盘进行两次IO访问操作，第一次根据order by的条件取出需要进行排序的列以及对应的行的指针，在buffer中排序完成之后再根据行的指针去发起IO读取磁盘中的数据。
双路排序可以保证两次就将数据排序成功，但是需要与磁盘进行两次的IO操作，是比较浪费时间的。

单路排序
单路排序会一次性取出满足条件行的所有字段，然后在sort buffer中进行排序，以空间换时间。
但是当数据量过大的时候，就会导致sort buffer的容量不足以装载下一次性取出的所有字段，所以mysql只能先将sort buffer填满进行排序之后存放到临时文件中，然后再去获取数据到sort buffer中进行排序。
循环如此，就可以完成排序。也就是说，当sort buffer的值不够大，但是数据量足够多的情况下，单路排序有可能造成更多次的磁盘IO访问。
并且，单路排序中对于排序的列的长度也有限制，如果列的长度大于max_length_for_sort_data参数设置的长度就会转为双路排序算法进行排序。

总结：

• 在排序的时候应该禁用select *，select *有可能会查询多余的字段，占用内存的空间，影响排序的效率；
• 单路排序比双路排序快，但是应当根据需求和具体情境修改mysql中sort_buffer_size和max_length_for_sort_data参数的值，使其能够保证使用单路排序算法进行排序。

group by优化

group by优化基本上和order by趋同，唯一不同的是group by有having，且where高于having，能够在where中限定的条件就不要写在having中。

慢查询日志

慢查询日志是Mysql提供的一种日志记录，它用来记录在Mysql中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的sql。Mysql默认的long_query_time的值为10s，也就是说查询时间超过10s的sql则会被记录在慢查询日志中。

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

查看慢查询日志是否开启以及如何开启
查看慢查询日志是否开启：show variables like '%slow_query_log%';

命令运行结果如上，OFF表示关闭，我们需要通过设置slow_query_log的值来开启慢查询日志。
开启慢查询日志：set global slow_query_log=1;

注意：开启的慢查询日志只对当前数据库有效，并且Mysql重启之后会失效，如果需要慢查询日志永久生效则需要对Mysql的配置文件进行修改。

关于慢查询日志存放位置的文件名：文件名的格式为主机名-slow.log。

什么情况下sql会被慢查询日志记载呢？
关于sql是否是慢sql，是由long_query_time参数来控制，默认情况下值为10s。
查看当前的long_query_time的值：show variables like 'long_query_time%';

关于这个参数的值，可以通过命令来修改，也可以修改Mysql配置文件来修改。假如sql的运行时间正好等于10s是不会被记录下来的，mysql只判断大于long_query_time的sql，而不是大于等于。

设置long_query_time的值：set global long_query_time=3;

设置完之后重新查看参数发现时间并没有改变，还是10s。
但是此时并不是设置失败，修改了这个参数之后需要使用show global variables like 'long_query_time';命令才可以看到变化，或者重新连接一个会话使用之前的命令也可以看到变化，如下：

使用select sleep(4)模拟慢sql，并查看慢查询日志的记录信息
因为当前环境无法模拟超过3s的慢sql，所以在此用到select sleep(4);来模拟慢sql，可以将其理解为多线程中的线程等待4s。

可以看到sql的执行时间为4s，接下来我们去查看保存的慢查询日志内容。
跟踪地址进入/var/lib/mysql目录中找到慢日志文件，打开查看信息：

查看当前系统记录了多少条慢sql信息
命令：show global status like '%slow_queries%';

日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志、查找、分析sql显然是个体力活，Mysql提供了日志分析工具：mysqldumpslow。
简单的来说就是在生产中可能会出现很多个慢sql，我们可以通过mysqldumpslow的指令帮我们筛选出我们先要优先、针对处理的慢sql。
查看mysqldumpslow帮助手册：mysqldumpslow --help

常用参数解析：

• s：表示以何种方式排序
• c：访问次数
• l：锁定时间
• r：返回记录
• t：查询行数
• al：平均锁定时间
• ar：平均返回记录数
• at：平均查询时间
• t：返回前面多少条数据

常用命令参考：
得到返回结果集最多的10个sql：mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
得到访问次数最多的10个sql：mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句：mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
另外建议将命令结合管道符使用，否则筛选结果太多的时候难以取得想要的结果：mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

使用脚本往数据库中插入1000w数据

为了接下来的学习和演示，在此我们将使用脚本往数据库中插入1000w的数据以供演示学习。
数据库和建表sql

# 新建库
create database bigData;
use bigData;

#1 建表dept
CREATE TABLE dept(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   
dname VARCHAR(20) NOT NULL DEFAULT "",  
loc VARCHAR(13) NOT NULL DEFAULT ""  
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ;  

#2 建表emp
CREATE TABLE emp  
(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/  
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/  
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/  
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/  
hiredate DATE NOT NULL,/*入职时间*/  
sal DECIMAL(7,2) NOT NULL,/*薪水*/  
comm DECIMAL(7,2) NOT NULL,/*红利*/  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/  
)ENGINE=INNODB DEFAULT CHARSET=UTF8 ; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

接下来我们将使用存储过程函数大批量的导入数据，但是在导入数据之前，我们需要将mysql的log_bin_trust_function_creators参数设置为1。
这个参数表示mysql是否信任存储函数的创建者，默认值为0，如果不进行更改的话会导致创建存储过程失败。

查看log_bin_trust_function_creators变量状态：
show variables like 'log_bin_trust_function_creators';

设置log_bin_trust_function_creators变量值：
set global log_bin_trust_function_creators=1;

接下来我们就使用一系列的存储过程函数来完成批量插入数据的功能。

创建随机生成英文字符串和随机生成随机数的存储过程：
我们需要在给员工编号或者部门编号添加数据的时候使用不重复的uuid，但是mysql并没有类似的函数给我们使用，所以需要手动书写存储过程来实现。

#生成唯一字符串函数
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$

#假如要删除
#drop function rand_string;

#生成随机数函数
DELIMITER $$
CREATE FUNCTION rand_num( ) 
RETURNS INT(5)  
BEGIN   
DECLARE i INT DEFAULT 0;  
SET i = FLOOR(100+RAND()*10);  
RETURN i;  
END $$

#假如要删除
#drop function rand_num;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

一次性插入100w的数据略显粗暴，我们通过存储过程实现自定义插入数据的多少。实际插入数据的时候每次插入50w的数据，1000w分20次插入：

按照参数往emp中插入多少条的数据：

DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
SET autocommit = 0;    
REPEAT
SET i = i + 1;  
INSERT INTO emp(empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());  
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$

#删除
DELIMITER ;
drop PROCEDURE insert_emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

按照参数往dept中插入多少条的数据：

#执行存储过程，往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
SET autocommit = 0;    
REPEAT  
SET i = i + 1;  
INSERT INTO dept (deptno ,dname,loc ) VALUES (START +i ,rand_string(10),rand_string(8)); 
UNTIL i = max_num  
END REPEAT;  
COMMIT;  
END $$ 

#删除
DELIMITER ;
drop PROCEDURE insert_dept;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

使用存储过程一次性添加十个部门信息：

DELIMITER ;
CALL insert_dept(100,10); 
1
2

使用存储过程一次性添加50w员工信息：

DELIMITER ;
CALL insert_emp(100001,500000); 
1
2

重复20次，往数据库中添加1000w的数据。

Show Profile

show profile是Mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于sql的调优测量。默认情况下是关闭状态，开启后会保存最近15次的sql运行结果。

查看show profile的启动状态：show variables like 'profiling';

开启show profile：set profiling = on;

开启了show profile之后我们需要运行一些sql来让其记录，并查看记录的结果。

案例sql如下：

 select * from dept;
 select * from emp group by id%10 limit 150000;
 select * from emp group by id%20 order by 5；
1
2
3

查看show profile的记录结果：show profiles;

由上图效果可知，show profiles;命令可以显示sql的详细执行时间以及对应的query_id信息，接下来我们就可以通过query_id去查看对应的sql在执行的各个阶段分别花了多少时间。

根据query_id查看sql执行的详细过程：show profile cpu,block io for query 1;

sql的详细执行过程可以查看的参数有很多，在上面的sql中只是查看了cpu和block io的内容，详细的可选参数列表如下：

• ALL：显示所有的开销信息
• BLOCK IO：显示块IO相关开销
• CONTEXT SWITCHES：上下文切换相关开销
• CPU：显示cpu相关开销信息
• IPC：显示发送和接收相关开销信息
• MEMORY：显示内存相关开销信息
• PAGE FAULTS：显示页面错误相关开销信息
• SOURCE：显示和source_function,source_file,source_line相关的开销信息
• SWAPS：显示交换次数相关开销的信息

由详细过程可以看到，show profile会罗列出非常多的详细信息，但是并不是所有的信息都需要关注的，以下几点当出现的时候说明sql出现了对应的比较严重的问题：

• converting HEAP to MyISAM：表示查询的结果太大了，内存无法存放还将其存放到磁盘中；
• Creating tmp table：表示创建了临时表，与之对应的步骤还有将查询内容复制到零食表，删除临时表等等。临时表的创建和数据复制都是非常消耗时间的；
• Copying to tmp table on disk：表示将内存中的临时表复制到磁盘中；

例如，在上方的示例sql的第二条就会出现临时表的问题：

全局查询日志

全局查询日志会将你所执行的所有的sql信息都记录下来，该功能仅适合在测试环境中使用，切记不要在生产中开启该功能。
开启全局查询日志：set global general_log=1;
设置日志输出以表的形式：set global log_output='table';
开启了之后mysql会将你执行的所有sql的情况记录在mysql库的general_log表中。
查看全局查询日志信息：select * from mysql.general_log;

Mysql锁机制

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

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

锁的分类

按照对数据操作的类型分为两类：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不互相影响；
• 写锁（排他锁）：当前写操作没有完成时，它会阻断其他写锁和读锁。

按照对数据操作的粒度分为两类：

• 行锁
• 表锁

表锁

表锁偏向于MyIsam引擎，开销小，加锁块。无死锁，锁定力度大，发生锁冲突的概率最高，并发度最低。

案例演示
建表sql：

create table mylock(
 id int not null primary key auto_increment,
 name varchar(20)
)engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

select * from mylock;
1
2
3
4
5
6
7
8
9
10
11
12

查看所有表上添加的锁信息：show open tables;

手动添加表锁：lock table 表名 read|write;

加锁之后再查看表的锁状态可以看到变化。

释放表锁：unlock tables;
了解了基本的查看锁、添加锁、释放锁的操作之后，接下来就演示在同一连接和不同连接下的表锁的读写锁分别会是什么情况。

表读锁演示
先给mylock表添加锁：lock table mylock read;
在添加锁的连接①中读表：

读取无误。

在新连接连接②中读表：

读取无误。

在添加锁的连接①中读取其他表：

报错，读取失败！

在新连接连接②中读取其他表：

读取无误。

在添加锁的连接①中修改数据：

修改失败！

在新连接的连接②中修改数据：

连接②中修改数据属于阻塞状态，等待连接①中释放表读锁之后修改成功：

总结：

• 当在某个连接中对某个表添加了表读锁之后，当前连接可以查看该表数据，其他连接也可以查看该表数据；
• 当在某个连接中对某个表添加了表读锁之后，当前连接无法读取其他表数据，因为当前表的锁未放开，其他连接可以查看其他表数据；
• 当在某个连接中对某个表添加了表读锁之后，当前连接无法修改该表数据，其他连接修改该表数据会进入阻塞状态，只到表读锁被释放。

表写锁演示：

先给mylock表加锁：lock table mylock write;
在添加锁的连接①中读表：

读取成功。

在添加锁的连接 ①中修改数据：

修改成功。

在添加锁的连接①中读取其他表：

读取失败！

在新连接的连接②中读取表：

读取数据进入阻塞状态，释放锁后读取成功。

总结：

• 当在某个连接中对某张表添加了表写锁的时候，当前连接读取该表数据无误；
• 当在某个连接中对某张表添加了表写锁的时候，当前连接修改表数据无误；
• 当在某个连接中对某张表添加了表写锁的时候，当前连接读取其他表失败，因为当前连接的锁未放开；
• 当在某个连接中对某张表添加了表写锁的时候，其他连接读取该表数据会进入阻塞，直到锁被释放才能读取到数据。

MyIsam在执行查询语句之前，会给涉及到的所有表添加读锁，在执行增删改操作之前，会给涉及到的所有表添加写锁。

表锁锁定分析

通过上方的案例，我们对于MyIsam下的表读写锁的情况有了基本的了解，那么当发生锁定阻塞的时候我们应该通过什么手段去分析呢？
我们可以通过table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。
查看信息命令：show status like 'table%';

信息参数解析：

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

此外，MyIsam的读写锁操作时写优先，这也说明MyIsam不适合作为写为主要业务的表的引擎。因为写锁后，其他线程不能进行任何操作，大量的更新会使查询很难得到锁，从而造成大量阻塞。

行锁

行锁偏向InnoDB存储引擎，开销大，加锁慢。会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
关于InnoDB和MyIsam的最大不同就在于：InnoDB支持事务，并且采用了行级锁。

关于事务

事务是由一组sql语句组成的逻辑处理单元，事务具有以下四个属性，通常称之为事务的ACID属性。

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

关于并发事务处理带来的问题：

• 更新丢失：当两个或者多个事务选择同一行数据进行操作，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会出现更新丢失的问题，最后的更新覆盖了其他事务所做的更新；
• 脏读：事务A读取到了事务B已经修改但是尚未提交的数据，还在这个数据的基础上进行了操作，此时如果事务B回滚，则A读取到的数据是无效的，不符合一致性；
• 不可重复读：在事务A中会对数据进行多次读取，在读取的过程中事务B对数据进行了修改，就会导致事务A多次读取前后的数据不一致，即不可重复读；
• 幻读：事务A按照以前的检索条件重新读取数据，读取到了事务B新增的数据，不符合隔离性。

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

数据库的隔离级别越严格，并发的副作用越小，但是付出的代价也就越大，因为事务的隔离级别实质上就是使事务在一定程度上“串行化”进行，这显然和“并发”是相矛盾的。

同时，不同的应用对读一致性和事务隔离级别的要求也是不同的，比如许多的应用对“不可重复读”和“幻读”并不敏感，可能更多的是关心数据有没有并发访问的能力。

查看当前数据库的事务隔离级别：show variables like 'tx_isolation';

了解了事务相关之后，接下来就进行行读写锁的案例分析。

建表sql

create table test_innodb_lock (a int(11),b varchar(16))engine=innodb;

insert into test_innodb_lock values(1,'b2');
insert into test_innodb_lock values(3,'3');
insert into test_innodb_lock values(4,'4000');
insert into test_innodb_lock values(5,'5000');
insert into test_innodb_lock values(6,'6000');
insert into test_innodb_lock values(7,'7000');
insert into test_innodb_lock values(8,'8000');
insert into test_innodb_lock values(9,'9000');
insert into test_innodb_lock values(1,'b1');

create index test_innodb_a_ind on test_innodb_lock(a);
create index test_innodb_lock_b_ind on test_innodb_lock(b);

select * from test_innodb_lock;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

由建表语句可知，我们在建立表结构以及插入数据之后分别给a字段和b字段建立了一个单值索引。

InnoDB的行锁演示
InnoDB默认的就是行级锁，和MyIsam的表锁演示一样，我们开启两个客户端对数据库进行操作，演示不同情况下的效果。
首先使用命令关闭InnoDB的事务自动提交：set autocommit = 0;
修改了自动提交之后sql语句就需要手动的通过commit去提交。
在连接①中修改数据并查看数据：

查看数据无误！

在连接②中查看数据：

数据未变化！

在连接①中commit操作之后再在连接②中查看数据：

查看数据无误！

在连接①中修改数据并且不commit：

然后在连接②中修改同一条数据：

连接②进入阻塞状态，等待连接①commit之后才会对数据进行修改：

如果阻塞时间太长会报错，提示阻塞等待超时。

总结：

• 当某个连接对数据的修改未commit的时候，其他的连接是无法读取到修改的数据的；
• 当某个连接对数据的修改未commit的时候，其他的连接如果修改相同行的数据会进入阻塞状态，一直到先修改的连接commit阻塞才停止；
• 当某个连接对修改的数据未commit，其他连接修改不同行的数据是不会有影响的。

索引失效行锁变表锁
由上可知，InnoDB是行锁，对于不同行的操作是不会有影响的，但是如果操作的时候出现了索引失效，则会将行锁转变为表锁，出现阻塞。

正常情况下行锁修改不同行不会出现阻塞。


当某个连接中出现了索引失效的时候会将行锁转变为表锁，会出现阻塞。如上所示，第一条sql出现了隐式的类型转换，导致了索引失效，所以下面连接中的第二个sql出现了阻塞。

连接一commit之后修改成功！

间隙锁

InnoDB是行锁，当我们检索一行的时候，InnoDB会将符合条件的被检索行锁定。
当我们使用范围条件而不是相等条件查找检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据的索引项加加锁。而对于符合条件范围但是并不存在的数据，其实也会被加锁，这种符合条件但是并不存在数据库的数据就叫间隙，锁定间隙的就是间隙锁。
也就是说，如果我们使用范围条件检索的时候，就算范围中有的数据并不存在数据库中也会被锁定，这样会导致如果查询过程中无法对符合范围的数据进行添加操作，会因为间隙锁而进入阻塞状态。

间隙锁演示

如上图数据可以看到，在a字段中并不存在数值为2的行，此时如果我使用范围查询，这个不存在的2也会被锁定。


可以看到当进行范围条件检索的修改的时候，对a值为2的行也进行了锁定，insert操作进入了阻塞状态，当修改操作commit之后才会添加成功：

手动锁定一行

在上面的案例中，我们都是通过InnoDB的行锁特性去感受行锁和间隙锁，但是我们如何手动指定锁定一行呢？
例如在工作中某一行的数据出现了问题，我们需要对数据进行复杂的修复操作，在修复的过程中不希望任何的sql进入来干扰工作进度，那么应该如何锁定这一行呢？
我们首先使用set autocommit = 1;来将事务提交切换为自动。

手动锁定一行语法

begin;

select * from 表名 where id = ‘’ for update；

commit;

由语法可知，通过begin;开始准备锁定，然后通过id指定查询某一行并进行锁定，接下来所做的任何操作都不会被其他sql干扰，只到我们使用了commit;之后其他连接对于这一行的操作才会结束阻塞。

锁定一行。

其他的操作阻塞中…

commit之后阻塞结束！

行锁锁定分析

和MyIsam的表锁相同，InnoDB表锁也有对应的锁定状态参数展示。

查看信息命令：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：从系统启动到现在总共等待的次数。

行锁优化建议

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