mysql-基础2：索引、事务、锁

一、索引介绍

（一）索引类型

索引可以提升查询速度，会影响where查询，以及order by排序。MySQL索引类型如下：

• 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
• 从应用层次划分：普通索引、唯一索引、主键索引、复合索引
• 从索引键值类型划分：主键索引、辅助索引（二级索引）
• 从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

// 创建索引
create index user_name on user(name);
alter table user add index user_name (name);

// 查询索引
show index from user ;

// 删除索引
drop index user_name on user;

1、普通索引

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

创建普通索引的方法如下：

• CREATE INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
• CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名) );

2、唯一索引

与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。

创建唯一索引的方法如下：

• CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
• CREATE TABLE tablename ( […], UNIQUE [索引的名字] (字段名) ;

3、主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。

创建主键索引的方法如下：

• CREATE TABLE tablename ( […], PRIMARY KEY (字段名) );
• ALTER TABLE tablename ADD PRIMARY KEY (字段名);

4、复合索引

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。

复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。

创建组合索引的方法如下：

• CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2…);
• ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2…);
• CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名1，字段名2…) );

复合索引使用注意事项：

• 何时使用复合索引 ，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
• 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

5、全文索引

①使用

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

创建全文索引的方法如下：

• CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
• CREATE TABLE tablename ( […], FULLTEXT KEY [索引的名字] (字段名) ;

查询时使用方式：

select * from user  where match(name) against('aaa');

全文索引使用注意事项：

• 全文索引必须在字符串、文本字段上建立。
• 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
• 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
• 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

select * from user  where match(name) against('a*' in boolean mode);

②最大最小字符

// 通过以下命令可以查看建表语句，可以看到是myisam引擎还是innodb引擎
show create table user;

③切词：syntax字符

create fulltext index user_name on user(name);-- 创建全文索引
select * from user where match(name) against('aaa'); -- 全文索引搜索

--更新数据，然后再次搜索
update user set name='b+aaa' where id=5;
update user set name='c+aaa' where id=6;

④默认是等值匹配

解决方式就是：在匹配字符后面加*号，并且在布尔模式下查询，就可以查询到了。

（二）索引原理

1、理论知识

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

• 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。

• 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

  索引涉及的理论知识：二分查找法、Hash和B+Tree。

2、索引效率查询：

show engine innodb status \G;

3-1、二分查找法

二分查找法也叫作折半查找法，它是在有序数组中查找指定数据的搜索算法。

• 优点：等值查询、范围查询性能优秀
• 缺点：更新数据、新增数据、删除数据维护成本高。

查找方法：

• 首先定位left和right两个指针

• 计算(left+right)/2

• 判断除2后索引位置值与目标值的大小比对

• 索引位置值大于目标值就-1，right移动（即，right移动到索引位置-1的地方）；如果小于目标值就+1，left移动（即，left移动到索引位置+1的地方）

  举个例子，下面的有序数组有17 个值，查找的目标值是7，过程如下：

  

3-2、hash结构

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。

其结构如下所示：

从上面结构可以看出，Hash索引可以方便的提供等值查询，但是对于范围查询就需要全表扫描了。Hash索引在MySQL 中Hash结构主要应用在Memory原生的Hash索引 、InnoDB 自适应哈希索引。

InnoDB提供的自适应哈希索引功能强大，接下来重点描述下InnoDB 自适应哈希索引。

InnoDB自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。

InnoDB自适应哈希索引：在使用Hash索引访问时，一次性查找就能定位数据，等值查询效率要优于B+Tree。

自适应哈希索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页建立哈希索引来加速访问。另外InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行人工干涉。

show engine innodb status \G;
show variables like '%innodb_adaptive%';

3-3、B +TREE 结构

MySQL数据库索引采用的是B+Tree结构,在B-Tree结构上做了优化改造。

①B-Tree结构

• 索引值和data数据分布在整棵树结构中

• 每个节点可以存放多个索引值及对应的data数据

• 树节点中的多个索引值从左到右升序排列

  

B树的搜索：

1. 从根节点开始，对节点内的索引值序列采用二分法查找
2. 如果命中就结束查找。
3. 没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

②B+Tree结构

• 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值

• 叶子节点包含了所有的索引值和data数据

• 叶子节点用指针连接，提高区间的访问性能

  

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，
然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，
显然B+Tree效率高。

4、聚簇索引和辅助索引

聚簇索引和非聚簇索引：B+Tree的叶子节点的主键索引和行记录一起存放就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。

主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。

在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。

①聚簇索引（聚集索引）

聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree
的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就
是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。

通常说的主键索引就是聚集索引。

InnoDB的表要求必须要有聚簇索引：

• 如果表定义了主键，则主键索引就是聚簇索引
• 如果表没有定义主键，则第一个非空unique列作为聚簇索引
• 否则InnoDB会创建一个隐藏的row-id作为聚簇索引

②辅助索引

InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率。一个InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。

③非聚簇索引

与InnoDB表存储不同，MyISAM数据表的索引文件和数据文件是分开的，被称为非聚簇索引结构。

（三）索引分析与优化

1、EXPLAIN

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化。例如：

EXPLAIN SELECT * from user WHERE id < 3;

针对几个主要的字段，进行说明

①select_type

表示查询的类型。常用的值如下：

• SIMPLE ： 表示查询语句不包含子查询或union
• PRIMARY：表示此查询是最外层的查询
• UNION：表示此查询是UNION的第二个或后续的查询
• EXPLAIN SELECT * from user WHERE id < 3;
• DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
• UNION RESULT：UNION的结果
• SUBQUERY：SELECT子查询语句
• DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。

1）PRIMARY、UNION 、UNION RESULT

2）SUBQUERY

3）DEPENDENT SUBQUERY

②type

表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。

• ALL：表示全表扫描，性能最差。
• index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。（先扫描索引获得一个顺序，然后按照顺序获取表记录，对于有序的数据查询，性能比ALL要高。）
• range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
• ref：表示使用非唯一索引进行单值查询。
• eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。（一对一的关系）
• const：表示使用主键或唯一索引做等值查询，常量查询。
• NULL：表示不用访问表，速度最快。

1）ALL

2）index

3）NULL

4）const

5）ref

6）eq_ref

③rows

MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。

④key_len

表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。

key_len的计算规则如下：

• 字符串类型

  字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4

  • char(n)：n*字符集长度
  • varchar(n)：n * 字符集长度 + 2字节

• 数值类型

  • TINYINT：1个字节
  • SMALLINT：2个字节
  • MEDIUMINT：3个字节
  • INT、FLOAT：4个字节
  • BIGINT、DOUBLE：8个字节

• 时间类型

  • DATE：3个字节
  • TIMESTAMP：4个字节
  • DATETIME：8个字节

• 字段属性

  NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

1）int

2）varchar

⑤Extra

Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：

• Using where：表示查询需要通过索引回表查询数据。
• Using index：表示查询需要通过索引，索引就可以满足所需数据。
• Using filesort：表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。
• Using temprorary：查询使用到了临时表，一般出现于去重、分组等操作。

1）Using where

2）Using index

3）Using filesort

4）Using temprorary

2、回表查询

在之前介绍过，InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。

总结：通过索引查询主键值，然后再去聚簇索引查询记录信息

3、覆盖索引

①demo1

②demo2

4、最左前缀原则

复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，
那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

①demo1

②demo2

5、LIKE查询

面试题：MySQL在使用like模糊查询时，索引能不能起作用？
回答：MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。

select * from user where name like '%o%'; --不起作用
select * from user where name like 'o%'; --起作用
select * from user where name like '%o'; --不起作用

6、NULL查询

面试题：如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效？
对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等。

①demo1

②deom2

③结论

虽然MySQL可以在含有NULL的列上使用索引，但是不建议。

因为，如果索引列的值是NULL，需要增加额外空间来记录其值是否为NULL。对于MyISAM表，每一个空列额外占用一位，四舍五入到最接近的字节。

所以，最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以
设置系统当前时间或某个固定的特殊值，例如’1970-01-01 00:00:00’。

7、索引与排序

MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。

• 如果我们Explain分析SQL，结果中Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。
• 如果Extra属性显示Using index时，表示覆盖索引，也表示所有操作在索引上完成，也可以使用index排序方式
• 建议尽可能采用覆盖索引。

filesort有两种排序算法：双路排序和单路排序。

• 双路排序：需要两次磁盘扫描读取，最终得到用户数据。
  • 第一次将排序字段读取出来，然后排序；
  • 第二次去读取其他字段数据。
• 单路排序：从磁盘查询所需的所有列数据，然后在内存排序将结果返回。如果查询数据超出缓存sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。
  • 解决方案：少使用select *；增加sort_buffer_size容量和max_length_for_sort_data容量。

以下几种情况，会使用index方式的排序。

• ORDER BY 子句索引列组合满足索引最左前列

  explain select id from user order by id; --对应(id)、(id,name)索引有效
  

• WHERE子句+ORDER BY子句索引列组合满足索引最左前列

  explain select id from user where age=18 order by name; --对应(age,name)索引
  

以下几种情况，会使用filesort方式的排序。

• 对索引列同时使用了ASC和DESC

  explain select id from user order by age asc,name desc; --对应(age,name)索引
  

• WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in
  等）

  explain select id from user where age>10 order by name; --对应(age,name)索引
  

• ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列

  explain select id from user order by name;  --对应(age,name)索引
  

• 使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引

  explain select id from user order by name,age; --对应(name)、(age)两个索引
  

• WHERE子句与ORDER BY子句，使用了不同的索引

  explain select id from user where name='tom' order by age; --对应(name)、(age)索引
  

• WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式

  explain select id from user order by abs(age); --对应(age)索引
  

  有多个索引的情况下，会进行排查，选择一个查询数最少的索引

（四）查询优化

1、慢查询定位

①开启慢查询日志

show variables like '%slow_query_log%';

通过如下命令开启慢查询日志

SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;

• long_query_time：指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询记录到日志文件中。
• log_queries_not_using_indexes：表示会记录没有使用索引的查询SQL。前提是slow_query_log的值为ON，否则不会奏效。

②慢查询日志文件路径说明

slow_query_log_file对应的文件，就在数据库存放目录下。

而数据库存放目录，可以通过以下命令查看数据库文件存放位置

show global variables like "%datadir%";

③查看慢查询日志

1）直接打开log文本查看

日志参数说明：

• time：日志记录的时间
• User@Host：执行的用户及主机
• Query_time：执行的时间
• Lock_time：锁表时间
• Rows_sent：发送给请求方的记录数，结果数量
• Rows_examined：语句扫描的记录条数
• SET timestamp：语句执行的时间点
• select....：执行的具体的SQL语句

2） 还可以使用mysqldumpslow查看

MySQL 提供了一个慢查询日志分析工具mysqldumpslow，可以通过该工具分析慢查询日志内容。

在 MySQL bin目录下执行下面命令可以查看该使用格式。

perl mysqldumpslow.pl --help

运行如下命令查看慢查询日志信息：

perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\OAK-slow.log

3）第三方分析工具，比如pt-query-digest、mysqlsla等。

2、慢查询优化

①索引和慢查询

1）如何判断是否为慢查询？

MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可以根据自己的业务需要进行调整。

 show variables like '%long_query%';

2）如何判断是否应用了索引？

SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain命令分析查看，检查结果中的 key 值，是否为NULL。

• 应用了索引是否一定快？

  查询是否使用索引，只是表示一个SQL语句的执行过程；而是否为慢查询，是由它执行的时间决定的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。

  我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果扫描行数减少了，效率才会得到提升。

  对于一个大表，不止要创建索引，还要考虑索引过滤性，过滤性好，执行速度才会快。

  比如，用explain分析下面的sql

  select * from user where id>0;
  

  虽然使用了索引，但是还是从主键索引的最左边的叶节点开始向右扫描整个索引树，进行了全表扫描，此时索引就失去了意义。

  而像 select * from user where id = 2; 这样的语句，才是我们平时说的使用了索引。它表示的意思是，我们使用了索引的快速搜索功能，并且有效地减少了扫描行数。

②提高索引过滤性

假如有一个5000万记录的用户表，通过sex='男’索引过滤后，还需要定位3000万，SQL执行速度也不会很快。

其实这个问题涉及到索引的过滤性，比如1万条记录利用索引过滤后定位10条、100条、1000条，那他们过滤性是不同的。索引过滤性与索引字段、表的数据量、表设计结构都有关系。

如以下案例：

CREATE TABLE student  (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` varchar(20),
  `sex` varchar(20),
  `age` int 
) ENGINE = InnoDB CHARACTER SET = utf8;


INSERT INTO student(`id`, `name`, `sex`, `age`) VALUES (1, 'zhangsan', '男', 20);
INSERT INTO student(`id`, `name`, `sex`, `age`) VALUES (2, 'lisi', '男', 22);
INSERT INTO student(`id`, `name`, `sex`, `age`) VALUES (3, 'wangqu', '男', 20);
INSERT INTO student(`id`, `name`, `sex`, `age`) VALUES (4, 'zhaoliu', '男', 26);
INSERT INTO student(`id`, `name`, `sex`, `age`) VALUES (5, 'xiaoling', '女', 23);

造数据：insert into student (name,sex,age) select name,sex,age from student;

-- SQL案例：
select * from student where age=20 and name like 'zhang%';-- （全表扫描）

• 优化之前

• 优化1

  create index age on student(age);
  

  可以看到，扫描数量减半

  

• 优化2

   create index age_name on student(age,name);
  

  此时，扫描数量再次减少

  

• 优化3

  可以看到，index condition pushdown 优化的效果还是很不错的。再进一步优化，我们可以把名
  字的第一个字和年龄做一个联合索引，这里可以使用 MySQL 5.7 引入的虚拟列来实现。
  

  虚拟列

  alter table student add first_name varchar(10) generated always as (left(name, 5)), add index(first_name, age);
  
  explain select * from student where first_name='zhang' and age=20 \G;
  

  

③慢查询原因总结

• 全表扫描：explain分析type属性all
• 全索引扫描：explain分析type属性index
• 索引过滤性不好：靠索引字段选型、数据量和状态、表设计
• 频繁的回表查询开销：尽量少用select *，使用覆盖索引

3、分页查询优化

①一般分页介绍

一般的分页查询使用简单的 limit 子句就可以实现。limit格式如下：

SELECT * FROM 表名 LIMIT [offset,] rows

• 第一个参数指定第一个返回记录行的偏移量，注意从0开始；
• 第二个参数指定返回记录行的最大数目；
• 如果只给定一个参数，它表示返回最大的记录行数目；

思考1：如果偏移量固定，返回记录量对执行时间有什么影响？

用到的命令

show variables like 'profiling';
set profiling=1;
show profiles;

select * from student limit 10000,1;
select * from student limit 10000,10;
select * from student limit 10000,100;
select * from student limit 10000,1000;
select * from student limit 10000,10000;

结果：

在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录量越大，所花费的时间也会越来越多。

思考2：如果查询偏移量变化，返回记录数固定对执行时间有什么影响？

select * from student limit 1,100;
select * from student limit 10,100;
select * from student limit 100,100;
select * from student limit 1000,100;
select * from student limit 10000,100;

结果：

在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加。（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而且查询的数据越多，也会拖慢总查询速度。）

②分页优化方案

待优化sql：

select * from student limit 10000,100;

第一步：利用覆盖索引优化

select id from student limit 10000,100;

优化结果查看：

第二步：利用子查询优化

select * from student where id>= (select id from student limit 10000,1) limit 100;

原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。

优化结果查看：

二、MySql事务和锁

（一）ACID 特性

在关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足这 4 个特性，即所谓的 ACID：
原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

1、 原子性

原子性：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

修改—》Buffer Pool修改—》刷盘。可能会有下面两种情况：

• 事务提交了，如果此时Buffer Pool的脏页没有刷盘，如何保证修改的数据生效？ Redo
• 如果事务没提交，但是Buffer Pool的脏页刷盘了，如何保证不该存在的数据撤销？Undo

每一个写事务，都会修改BufferPool，从而产生相应的Redo/Undo日志，在Buffer Pool 中的页被刷到磁盘之前，这些日志信息都会先写入到日志文件中，如果 Buffer Pool 中的脏页没有刷成功，此时数据库挂了，那在数据库再次启动之后，可以通过 Redo 日志将其恢复出来，以保证脏页写的数据不会丢失。如果脏页刷新成功，此时数据库挂了，就需要通过Undo来实现了。

2、持久性

持久性：指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不应该对其有任何影响，不会丢失。

如下图所示，一个“提交”动作触发的操作有：binlog落地、发送binlog、存储引擎提交、flush_logs，check_point、事务提交标记等。这些都是数据库保证其数据完整性、持久性的手段。

MySQL的持久性也与WAL技术相关，redo log在系统Crash重启之类的情况时，可以修复数据，从而保障事务的持久性。通过原子性可以保证逻辑上的持久性，通过存储引擎的数据刷盘可以保证物理上的持久性。

3、 隔离性

隔离性：指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并发事务是隔离的。

InnoDB 支持的隔离性有 4 种，隔离性从低到高分别为：读未提交、读提交、可重复读、可串行化。锁和多版本控制（MVCC）技术就是用于保障隔离性的（后面课程详解）。

4、一致性

一致性：指的是事务开始之前和事务结束之后，数据库的完整性限制未被破坏。
一致性包括两方面的内容，分别是约束一致性和数据一致性。

• 约束一致性：创建表结构时所指定的外键、Check、唯一索引等约束，可惜在 MySQL 中不支持Check 。
• 数据一致性：是一个综合性的规定，因为它是由原子性、持久性、隔离性共同保证的结果，而不是单单依赖于某一种技术。

一致性也可以理解为数据的完整性。数据的完整性是通过原子性、隔离性、持久性来保证的，而这3个特性又是通过 Redo/Undo 来保证的。逻辑上的一致性，包括唯一索引、外键约束、check 约束，这属于业务逻辑范畴。

ACID 及它们之间的关系如下图所示，4个特性中有3个与 WAL 有关系，都需要通过 Redo、Undo 日志来保证等。

WAL的全称为Write-Ahead Logging，先写日志，再写磁盘。

（二）事务控制的演进

1、并发事务

事务并发处理可能会带来一些问题，比如：更新丢失、脏读、不可重复读、幻读等。

• 更新丢失

  当两个或多个事务更新同一行记录，会产生更新丢失现象。可以分为回滚覆盖和提交覆盖。

  • 回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了。
  • 提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了

• 脏读

  一个事务读取到了另一个事务修改但未提交的数据。

• 不可重复读

  一个事务中多次读取同一行记录不一致，后面读取的跟前面读取的不一致。

• 幻读

  一个事务中多次按相同条件查询，结果不一致。后续查询的结果和面前查询结果不同，多了或少了几行记录。

2、排队（串行化）

最简单的解决方法，就是完全顺序执行所有事务的数据库操作，不需要加锁，简单的说就是全局排队。

序列化执行所有的事务单元，数据库某个时刻只处理一个事务操作，特点是强一致性，处理性能低。

3、排它锁

引入锁之后就可以支持并发处理事务，如果事务之间涉及到相同的数据项时，会使用排他锁，或叫互斥锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。

注意：在整个事务1结束之前，锁是不会被释放的，所以，事务2必须等到事务1结束之后开始。

4、读写锁

读和写操作：读读、写写、读写、写读。

读写锁就是进一步细化锁的颗粒度，区分读操作和写操作，让读和读之间不加锁，这样下面的两个事务就可以同时被执行了。

读写锁，可以让读和读并行，而读和写、写和读、写和写这几种之间还是要加排他锁。

4、MVCC

多版本控制MVCC，也就是Copy on Write的思想。MVCC除了支持读和读并行，还支持读和写、写和读的并行，但为了保证一致性，写和写是无法并行的。

在事务1开始写操作的时候会copy一个记录的副本，其他事务读操作会读取这个记录副本，因此不会影响其他事务对此记录的读取，实现写和读并行。

1）MVCC概念

MVCC（Multi Version Concurrency Control）被称为多版本控制，是指在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。多版本控制很巧妙地将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量及读写性能。

如何生成的多版本？每次事务修改操作之前，都会在Undo日志中记录修改之前的数据状态和事务号，该备份记录可以用于其他事务的读取，也可以进行必要时的数据回滚。

2）MVCC实现原理

MVCC最大的好处是读不加锁，读写不冲突。在读多写少的系统应用中，读写不冲突是非常重要的，极大的提升系统的并发性能，这也是为什么现阶段几乎所有的关系型数据库都支持 MVCC 的原因，不过目前MVCC只在 Read Commited(读已提交) 和 Repeatable Read（可重复读） 两种隔离级别下工作。

在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读 （Current Read）。

• 快照读：读取的是记录的快照版本（有可能是历史版本），不用加锁。（select）
• 当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发修改这条记录。（select… for update 或lock in share mode，insert/delete/update）

举例如下：

假设 F1～F6 是表中字段的名字，1～6 是其对应的数据。后面三个隐含字段分别对应该行的隐含ID、事务号和回滚指针，如下图所示。

具体的更新过程如下：
假如一条数据是刚 INSERT 的，DB_ROW_ID 为 1，其他两个字段为空。当事务 1 更改该行的数据值时，会进行如下操作，

• 用排他锁锁定该行；记录 Redo log；

• 把该行修改前的值复制到 Undo log，即图中下面的行；

• 修改当前行的值，填写事务编号，使回滚指针指向 Undo log 中修改前的行。

  过程如下图所示。

此时，处理之后的结果如下图所示：

接下来事务2操作，过程与事务 1 相同，此时 Undo log 中会有两行记录，并且通过回滚指针连在一起，通过当前记录的回滚指针回溯到该行创建时的初始内容，如下图所示。

MVCC已经实现了读读、读写、写读并发处理，如果想进一步解决写写冲突，可以采用下面两种方案：

• 乐观锁
• 悲观锁

说明：Purge Thread线程可以清除不再需要的undo log日志，所以不会因为MVCC造成undo log日志爆满

（三）事务隔离级别

1、隔离级别类型

前面提到的“更新丢失”、”脏读”、“不可重复读”和“幻读”等并发事务问题，其实都是数据库一致性问题。

为了解决这些问题，MySQL数据库是通过事务隔离级别来解决的，数据库系统提供了以下 4 种事务隔离级别供用户选择。

事务隔离级别	回滚覆盖	脏读	不可重复读	提交覆盖	幻读
读未提交 （Read Uncommitted）	×	可能发生	可能发生	可能发生	可能发生
读已提交 （Read Committed）	×	×	可能发生	可能发生	可能发生
可重复读 （Repeatable Read）	×	×	×	×	可能发生
串行化 （Serializable ）	×	×	×	×	×

四种隔离级别总结

脏读等的含义，见本笔记【事务控制的演进】

• 读未提交

  Read Uncommitted 读未提交：解决了回滚覆盖类型的更新丢失，但可能发生脏读现象，也就是可能读取到其他会话中未提交事务修改的数据。

• 读已提交

  Read Committed 读已提交：只能读取到其他会话中已经提交的数据，解决了脏读。但可能发生不可重复读现象，也就是可能在一个事务中两次查询结果不一致。

• 可重复读

  Repeatable Read 可重复读：解决了不可重复读，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上会出现幻读。

  简单的说幻读指的是当用户读取某一范围的数据行时，另一个事务又在该范围插入了新行，当用户在读取该范围的数据时会发现有新的幻影行（读取到的数据条数不一样）。

• 可串行化

  Serializable 串行化：所有的增删改查串行执行。它通过强制事务排序，解决相互冲突，从而解决幻度的问题。这个级别可能导致大量的超时现象的和锁竞争，效率低下。

数据库的事务隔离级别越高，并发问题就越小，但是并发处理能力越差（代价）。读未提交隔离级别最低，并发问题多，但是并发处理能力好。以后使用时，可以根据系统特点来选择一个合适的隔离级别，比如对不可重复读和幻读并不敏感，更多关心数据库并发处理能力，此时可以使用Read Commited隔离级别。

事务隔离级别，针对Innodb引擎，支持事务的功能。像MyISAM引擎没有关系。

2、 事务隔离级别和锁的关系

1. 事务隔离级别是SQL92定制的标准，相当于事务并发控制的整体解决方案，本质上是对锁和MVCC使 用的封装，隐藏了底层细节.

2. 锁是数据库实现并发控制的基础，事务隔离性是采用锁来实现，对相应操作加不同的锁，就可以防 止其他事务同时对数据进行读写操作。

3. 对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在 开发中手动的设置锁。

   MySQL默认隔离级别：可重复读

   Oracle、SQLServer默认隔离级别：读已提交

   一般使用时，建议采用默认隔离级别，然后存在的一些并发问题，可以通过悲观锁、乐观锁等实现处 理。

3、 MySQL隔离级别控制

MySQL默认的事务隔离级别是Repeatable Read，查看MySQL当前数据库的事务隔离级别命令如下：

show variables like 'tx_isolation';
select @@tx_isolation;

设置事务隔离级别可以如下命令：

set tx_isolation='READ-UNCOMMITTED';
set tx_isolation='READ-COMMITTED';
set tx_isolation='REPEATABLE-READ';
set tx_isolation='SERIALIZABLE';

（四）锁机制和实战

1、锁分类

①从操作的粒度

分为表级锁、行级锁和页级锁。

• 表级锁：每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB 等存储引擎中。
• 行级锁：每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。
• 页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般。应用在BDB 存储引擎中。

	行锁	表锁	页锁
MyISAM		√
BDB		√	√
InnoDB	√	√

②从操作的类型

分为读锁和写锁。

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

IS锁、IX锁：意向读锁、意向写锁，属于表级锁，S和X主要针对行级锁。在对表记录添加S或X锁之前，会先对表添加IS或IX锁。

总结：

• S锁：事务A对记录添加了S锁，可以对记录进行读操作，不能做修改，其他事务可以对该记录追加S锁，但是不能追加X锁，需要追加X锁，需要等记录的S锁全部释放。
• X锁：事务A对记录添加了X锁，可以对记录进行读和修改操作，其他事务不能对记录做读和修改操作。

③从操作的性能

分为乐观锁和悲观锁。

• 乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。

• 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。

  共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

2、行锁原理

在InnoDB引擎中，我们可以使用行锁和表锁，其中行锁又分为共享锁和排他锁。

InnoDB行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：

• RecordLock锁：锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）

• GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）

• Next-key Lock锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）

在RR隔离级别，InnoDB对于记录加锁行为都是先采用Next-Key Lock，但是当SQL操作含有唯一索引时，Innodb会对Next-Key Lock进行优化，降级为RecordLock，仅锁住索引本身而非范围。

1. select ... from 语句：InnoDB引擎采用MVCC机制实现非阻塞读，所以对于普通的select语句，InnoDB不加锁
2. select ... from lock in share mode语句：追加了共享锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
3. select ... from for update语句：追加了排他锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
4. update ... where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
5. delete ... where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
6. insert语句：InnoDB会在将要插入的那一行设置一个排他的RecordLock锁。

以“update t1 set name=‘XX’ where id=10”操作为例，举例子分析下 InnoDB 对不同索引的加锁行
为，以RR隔离级别为例。

演示：

 create table t1(
  id int,
  name varchar(20)
 )engine=innodb charset=utf8;
create index id on t1(id);

 insert into t1 values(1,'a');
 insert into t1 values(3,'b');
 insert into t1 values(5,'c');
 insert into t1 values(7,'d');

①主键加锁

加锁行为：仅在id=10的主键索引记录上加X锁。

②唯一键加锁

加锁行为：现在唯一索引id上加X锁，然后在id=10的主键索引记录上加X锁。

③非唯一键加锁

加锁行为：对满足id=10条件的记录和主键分别加X锁，然后在(6,c)至(10,b)、(10,b)至(10,d)、(10,d)至(11,f)2个范围分别加Gap Lock。

④无索引加锁

加锁行为：表里所有行和间隙都会加X锁。（当没有索引时，会导致全表锁定，因为InnoDB引擎锁机制是基于索引实现的记录锁定）。

3、悲观锁

悲观锁（Pessimistic Locking），是指在数据处理过程，将数据处于锁定状态，一般使用数据库的锁机制实现。从广义上来讲，前面提到的行锁、表锁、读锁、写锁、共享锁、排他锁等，这些都属于悲观锁范畴。

①表级锁

表级锁每次操作都锁住整张表，并发度最低。

常用命令如下：

手动增加表锁

lock table 表名称 read|write,表名称2 read|write;

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

• 表级读锁：当前表追加read锁，当前连接和其他的连接都可以读操作；但是当前连接增删改操作会报错，其他连接增删改会被阻塞。
• 表级写锁：当前表追加write锁，当前连接可以对表做增删改查操作，其他连接对该表所有操作都被阻塞（包括查询）。
• 总结：表级读锁会阻塞写操作，但是不会阻塞读操作。而写锁则会把读和写操作都阻塞。

1）表锁读锁测试

 create table dept(
    deptno int,
    dname varchar(20)
)engine=innodb charset=utf8;


 insert into dept values(1,'java');

 insert into dept values(2,'h5');

 insert into dept values(3,'ui');

 insert into dept values(4,'big');

2）表锁写锁测试

②共享锁（行级锁-读锁）

• 共享锁又称为读锁，简称S锁。共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
• 使用共享锁的方法是在select … lock in share mode，只适用查询语句。

总结：事务使用了共享锁（读锁），只能读取，不能修改，修改操作被阻塞。

演示

③排他锁（行级锁-写锁）

• 排他锁又称为写锁，简称X锁。排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能对该行记录做其他操作，也不能获取该行的锁。
• 使用排他锁的方法是在SQL末尾加上for update，innodb引擎默认会在update，delete语句加上for update。
• 行级锁的实现其实是依靠其对应的索引，所以如果操作没用到索引的查询，那么会锁住全表记录。

总结：事务使用了排他锁（写锁），当前事务可以读取和修改，其他事务不能修改，也不能获取记录锁（select… for update）。如果查询没有使用到索引，将会锁住整个表记录。

演示

4、乐观锁

乐观锁是相对于悲观锁而言的，它不是数据库提供的功能，需要开发者自己去实现。在数据库操作时，想法很乐观，认为这次的操作不会导致冲突，因此在数据库操作时并不做任何的特殊处理，即不加锁，而是在进行事务提交时再去判断是否有冲突了。

乐观锁实现的关键点：冲突的检测。

悲观锁和乐观锁都可以解决事务写写并发，在应用中可以根据并发处理能力选择区分，比如对并发率要求高的选择乐观锁；对于并发率要求低的可以选择悲观锁

①乐观锁实现原理

• 使用版本字段（version）

  先给数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1。version
  是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改。

  

• 使用时间戳（Timestamp）

  与使用version版本字段相似，同样需要给在数据表增加一个字段，字段类型使用timestamp
  时间戳。也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳
  进行对比，如果一致则提交更新，否则就是版本冲突，取消操作。

②乐观锁案例

使用下单过程作为案例，描述下乐观锁的使用。

create table products(
  id int PRIMARY key,
	quantity int,
	version int
)engine=INNODB CHARSET=utf8;

insert into products VALUES(1,100,1);

• 第一步，打开两个mysql命令窗口，并且都开启事务

  此时，两个事务查询的结果如下：

  

• 第二步，事务A，执行update语句，并且提交

  执行成功，更新row为1

update products set quantity=quantity-1, version= version+1 where id=1 and  version=1;

• 第三步，事务B，执行update语句

  update products set quantity=quantity-1, version= version+1 where id=1 and  version=1;

同样执行成功，但是更新的row为0

除了自己手动实现乐观锁之外，许多数据库访问框架也封装了乐观锁的实现，比如
hibernate框架。MyBatis框架大家可以使用OptimisticLocker插件来扩展。

5、死锁与解决方案

常见的死锁现象和解决方案：

1、表锁死锁

产生原因：
用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
用户A–》A表（表锁）–》B表（表锁）
用户B–》B表（表锁）–》A表（表锁）

解决方案：
这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

2、行级锁死锁

产生原因1：
如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。
解决方案1：
SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划"对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。

产生原因2：
两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。

解决方案2：

• 在同一个事务中，尽可能做到一次锁定所需要的所有资源
• 按照id对资源排序，然后按顺序进行处理

3、共享锁转换为排他锁

产生原因：
事务A 查询一条纪录，然后更新该条纪录；此时事务B 也更新该条纪录，这时事务B 的排他锁由于事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A 再执行更新操作时，此处发生死锁，因为事务A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务B 已经有一个排他锁请求，并且正在等待事务A 释放其共享锁。

事务A: select * from dept where deptno=1 lock in share mode; //共享锁,1
update dept set dname=‘java’ where deptno=1;//排他锁,3
事务B: update dept set dname=‘Java’ where deptno=1;//由于1有共享锁，没法获取排他锁，需
等待，2

解决方案：

• 对于按钮等控件，点击立刻失效，不让用户重复点击，避免引发同时对同一条记录多次操作；
• 使用乐观锁进行控制。乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统性能。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中；

4、死锁排查

MySQL提供了几个与锁有关的参数和命令，可以辅助我们优化锁操作，减少死锁发生。

• 查看死锁日志

  show engine innodb status\G; --查看近期死锁日志信息。
  

  使用方法：1、查看近期死锁日志信息；2、使用explain查看下SQL执行计划
  

• 查看锁状态变量

  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、死锁演示