什么是索引

索引是帮助mysql高效获取数据的数据结构
是用来快速检索出具有特定值的记录

为什么要用索引

当没有索引的时候，由于数据是无序的，所以需要遍历数据表查找数据，当加了索引后数据会有一定的存储规则，然后查询的时候能够快速定位，加快查询速度

如何创建索引

引擎分类：
InnoDB引擎（聚集索引方式）
MyISAM引擎（非聚簇索引）

索引分类：
Hash索引 数据库不使用是因为hash对于 > < != 这种查询 不能解决
B+树索引

二叉树：容易出现不平衡，查询效率
B 树
B+ 树 数据只存储在叶子节点，非叶子节点只保存指向，解决了树的高度问题，减少查询深度

索引是帮助MySQL高效获取数据的排好序的数据结构，索引存储在一个文件里面

B-Tree
度(Degree)-节点的数据存储个数
叶节点具有相同的深度
叶节点的指针为空
节点中的数据key从左到右递增排列

B+Tree(B-Tree变种)
非叶子节点不存储data，只存储key，可以增大度
叶子节点不存储指针
顺序访问指针，提高区间访问的性能

B+Tree索引的性能分析
一般使用磁盘I/O次数评价索引结构的优劣
预读：磁盘一般会顺序向后读取一定长度的数据(页的整数倍)放入内存
局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用
B+Tree节点的大小设为等于一个页，每次新建节点直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，就实现了一个节点的载入只需一次I/O
B+Tree的度d一般会超过100，因此h非常小(一般为3到5之间)

MyISAM索引实现(非聚集)
MyISAM索引文件和数据文件是分离的

InnoDB索引实现(聚集)
数据文件本身就是索引文件
表数据文件本身就是按B+Tree组织的一个索引结构文件
聚集索引-叶节点包含了完整的数据记录

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
1、如果设置了主键，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。

2、如果表使用自增主键
那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页

3、如果使用非自增主键（如果身份证号或学号等）
由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。


为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)
1. 保持一致性：
当数据库表进行DML操作时，同一行记录的页地址会发生改变，因非主键索引保存的是主键的值，无需进行更改。
2. 节省存储空间：
Innodb数据本身就已经汇聚到主键索引所在的B+树上了， 如果普通索引还继续再保存一份数据，就会导致有多少索引就要存多少份数据

执行计划

使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈
下面是使用 explain 的例子：
在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）
使用的表

DROP TABLE IF EXISTS `actor`;
CREATE TABLE`actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `actor`(`id`, `name`, `update_time`) 
VALUES (1,'a','2017-12-22 15:27:18'), 
(2,'b','2017-12-22 15:27:18'), 
(3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film`(`id`, `name`) 
VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE`film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id`(`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO`film_actor` (`id`, `film_id`, `actor_id`) 
VALUES (1,1,1),(2,1,2),(3,2,1);

mysql> explain select from actor;


在查询中的每个表会输出一行，如果有两个表通过 join 连接查询，那么会输出两行。表的意义相当广泛：可以是子查询、一个 union 结果等。
explain 有两个变种：
1）explain extended：会在explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个百分比的值，rows filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。
mysql> explain extended select from film where id = 1;

mysql> show warnings;

2）explain partitions：相比explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。
*explain 中的列

explain 中每个列的信息。

1. id列
id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。MySQL将 select 查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。
复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。
id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行
1）简单子查询
mysql> explain select (select 1 from actor limit 1)from film;


2）from子句中的子查询
mysql> explain select id from (select id from film) as der;

这个查询执行时有个临时表别名为der，外部 select 查询引用了这个临时表

3）union查询
mysql> explain select 1 union all select 1;

union结果总是放在一个匿名临时表中，临时表不在SQL中出现，因此它的id是NULL。

2. select_type列
select_type 表示对应行是简单还是复杂的查询，如果是复杂的查询，又是上述三种复杂查询中的哪一种。
1）simple：简单查询。查询不包含子查询和union
mysql> explain select from film where id = 2;

2）primary：复杂查询中最外层的select
3）subquery：包含在 select 中的子查询（不在 from 子句中）
4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
用这个例子来了解 primary、subquery 和derived 类型
mysql> explain select (select 1 from actor where id = 1) from (select from film where id = 1) der;

5）union：在 union 中的第二个和随后的 select
6）union result：从 union 临时表检索结果的 select
用这个例子来了解 union 和 union result 类型：
mysql> explain select 1 union all select 1;


3. table列
这一列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。

4. type列
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index >ALL
一般来说，得保证查询达到range级别，最好达到ref
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
mysql> explain select min(id) from film;


const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system
mysql> explain extended select from (select from film where id = 1) tmp;

mysql> show warnings;


eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
mysql> explain select from film_actor left join film on film_actor.film_id =film.id;


ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
1. 简单 select 查询，name是普通索引（非唯一索引）
mysql> explain select from film where name =”film1”;


2.关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。 mysql>explain select film_id from film left join film_actor on film.id =film_actor.film_id;


range：范围扫描通常出现在 in(),between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
mysql> explain select from actor where id > 1;


index：扫描全表索引，这通常比ALL快一些。（index是从索引中读取的，而all是从硬盘中读取）
mysql> explain select from film;


ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了
mysql> explain select from actor;


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

6. key列
这一列显示mysql实际采用哪个索引来优化对该表的访问。
如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

7. key_len列
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。
mysql> explain select from film_actor where film_id = 2;

key_len计算规则如下：

• 字符串

char(n)：n字节长度
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2

• 数值类型

tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节　　

• 时间类型　

date：3字节
timestamp：4字节
datetime：8字节

• 如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

8. ref列
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

9. rows列
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

10. Extra列
这一列展示的是额外信息。常见的重要值如下：
Using index：查询的列被索引覆盖，并且where筛选条件是索引的前导列，是性能高的表现。一般是使用了覆盖索引(索引包含了所有查询的字段)。对于innodb来说，如果是辅助索引性能会有不少提高
mysql> explain select film_id from film_actor where film_id = 1;


Using where：查询的列未被索引覆盖，where筛选条件非索引的前导列
mysql> explain select from actor where name = ‘a’;


Using where Usingindex：查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据
mysql> explain select film_id from film_actor where actor_id = 1;


NULL：查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引
mysql>explain select from film_actor where film_id = 1;


Using index condition：与Using where类似，查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
mysql> explain select from film_actor where film_id > 1;


Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
1.actor.name没有索引，此时创建了张临时表来distinct
mysql> explain select distinct name from actor;

2.film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表
mysql> explain select distinct name from film;


Using filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
1.actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录 mysql> explain select from actor order by name;

2. film.name建立了idx_name索引,此时查询时extra是using index
mysql> explain select * from film order by name;

MVCC

索引最佳实践

使用的表

CREATE TABLE`employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT ''COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT ''COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULTCURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position`(`name`,`age`,`position`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
INSERT INTOemployees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTOemployees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTOemployees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

最佳实践
1. 全值匹配
EXPLAIN SELECT FROM employees WHERE name= ‘LiLei’;


EXPLAIN SELECT FROM employees WHERE name= ‘LiLei’ AND age = 22;


EXPLAIN SELECT FROM employees WHERE name= ‘LiLei’ AND age = 22 AND position=’manager’;


2.最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
EXPLAIN SELECT FROM employees WHERE age = 22 AND position =’manager’;
EXPLAIN SELECT FROM employees WHERE position = ‘manager’;
EXPLAIN SELECT FROM employees WHERE name = ‘LiLei’;


3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
EXPLAIN SELECT FROM employees WHERE name = ‘LiLei’;
EXPLAIN SELECT FROM employees WHERE left(name,3) = ‘LiLei’;


4.存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT FROM employees WHERE name= ‘LiLei’ AND age = 22 AND position =’manager’;
EXPLAIN SELECT FROM employees WHERE name= ‘LiLei’ AND age > 22 AND position=’manager’;


5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句
EXPLAIN SELECT name,age FROM employees WHERE name= ‘LiLei’ AND age = 23 AND position=’manager’;

EXPLAIN SELECT FROM employees WHERE name= ‘LiLei’ AND age = 23 AND position=’manager’;


6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
EXPLAIN SELECT FROM employees WHERE name != ‘LiLei’


7.is null,is not null 也无法使用索引
EXPLAIN SELECT FROM employees WHERE name is null


8.like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作
EXPLAIN SELECT FROM employees WHERE name like ‘%Lei’

EXPLAIN SELECT FROM employees WHERE name like ‘Lei%’


问题：解决like’%字符串%’索引不被使用的方法？
a）使用覆盖索引，查询字段必须是建立覆盖索引字段
EXPLAIN SELECT name,age,position FROM employees WHERE name like ‘%Lei%’;

b）当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效！

9.字符串不加单引号索引失效
EXPLAIN SELECT FROM employees WHERE name = ‘1000’;
EXPLAIN SELECT FROM employees WHERE name = 1000;


10.少用or,用它连接时很多情况下索引会失效
EXPLAIN SELECT FROM employees WHERE name = ‘LiLei’ or name = ‘HanMeimei’;


总结：

like KK%相当于=常量，%KK和%KK% 相当于范围

实战

创建test表（测试表）

drop table if exists test; 
create table test( id int primary key auto_increment, 
    c1 varchar(10),c2 varchar(10),c3 varchar(10),
    c4 varchar(10),c5 varchar(10) 
)ENGINE=INNODBdefaultCHARSET=utf8;
insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

创建索引

分析以下Case索引使用情况
Case 1：

分析：
①创建复合索引的顺序为c1,c2,c3,c4。
②上述四组explain执行的结果都一样：type=ref，key_len=132，ref=const,const,const,const。
结论：在执行常量等值查询时，改变索引列的顺序并不会更改explain的执行结果，因为mysql底层优化器会进行优化，但是推荐按照索引顺序列编写sql语句。
Case 2：


分析：
当出现范围的时候，type=range，key_len=99，比不用范围key_len=66增加了，说明使用上了索引，但对比Case1中执行结果，说明c4上索引失效。
结论：范围右边索引列失效，但是范围当前位置（c3）的索引是有效的，从key_len=99可证明。
Case 2.1：

分析：
与上面explain执行结果对比，key_len=132说明索引用到了4个，因为对此sql语句mysql底层优化器会进行优化：范围右边索引列失效（c4右边已经没有索引列了），注意索引的顺序（c1,c2,c3,c4），所以c4右边不会出现失效的索引列，因此4个索引全部用上。
结论：范围右边索引列失效，是有顺序的：c1,c2,c3,c4，如果c3有范围，则c4失效；如果c4有范围，则没有失效的索引列，从而会使用全部索引。
Case 2.2：

分析：
如果在c1处使用范围，则type=ALL，key=Null，索引失效，全表扫描，这里违背了最佳左前缀法则，带头大哥已死，因为c1主要用于范围，而不是查询。
解决方式使用覆盖索引。
结论：在最佳左前缀法则中，如果最左前列（带头大哥）的索引失效，则后面的索引都失效。
Case 3：

分析：
利用最佳左前缀法则：中间兄弟不能断，因此用到了c1和c2索引（查找），从key_len=66，ref=const,const，c3索引列用在排序过程中。
Case 3.1：

分析：
从explain的执行结果来看：key_len=66，ref=const,const，从而查找只用到c1和c2索引，c3索引用于排序。
Case 3.2：

分析：
从explain的执行结果来看：key_len=66，ref=const,const，查询使用了c1和c2索引，由于用了c4进行排序，跳过了c3，出现了Using filesort。
Case 4：

分析：
查找只用到索引c1，c2和c3用于排序，无Using filesort。
Case 4.1：

分析：
和Case 4中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为c1,c2,c3,c4，但是排序的时候c2和c3颠倒位置了。
Case 4.2：


分析：
在查询时增加了c5，但是explain的执行结果一样，因为c5并未创建索引。
Case 4.3：

分析：
与Case 4.1对比，在Extra中并未出现Using filesort，因为c2为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。
Case 5：

分析：
只用到c1上的索引，因为c4中间间断了，根据最佳左前缀法则，所以key_len=33，ref=const，表示只用到一个索引。
Case 5.1：

分析：
对比Case 5，在group by时交换了c2和c3的位置，结果出现Using temporary和Using filesort，极度恶劣。原因：c3和c2与索引创建顺序相反。

Case 6：

分析：
①在c1,c2,c3,c4上创建了索引，直接在c1上使用范围，导致了索引失效，全表扫描：type=ALL，ref=Null。因为此时c1主要用于排序，并不是查询。
②使用c1进行排序，出现了Usingfilesort。
③解决方法：使用覆盖索引。


Case 7：

分析：
虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。
Case 8：
EXPLAIN extended select c1 from test where c1 in(‘a1’,’b1’) ORDER BY c2,c3;

分析：
对于排序来说，多个相等条件也是范围查询

总结：
①MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
②order by满足两种情况会使用Using index。
#1.order by语句使用索引最左前列。
#2.使用where子句与order by子句条件列组合满足索引最左前列。
③尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀法则。
④如果order by的条件不在索引列上，就会产生Using filesort。
⑤group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。注意where高于having，能写在where中的限定条件就不要去having限定了。
通俗理解口诀：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用。


补充：in和exsits优化
原则：小表驱动大表，即小的数据集驱动大的数据集
in：当B表的数据集必须小于A表的数据集时，in优于exists
select from A where id in (select id from B)#等价于： 　　for select id from B　　for select from A where A.id = B.id
exists：当A表的数据集小于B表的数据集时，exists优于in
　　将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留
select from A where exists (select 1 from B where B.id = A.id) #等价于 for select from A for select from B where B.id = A.id#A表与B表的ID字段应建立索引
1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT 也可以是SELECT 1或select X,官方说法是实际执行时会忽略SELECT清单,因此没有区别
2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

锁和隔离级别

1. 概述

1.1 定义
　　锁是计算机协调多个进程或线程并发访问某一资源的机制。
　　在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

1.2 锁的分类
从性能上分为乐观锁(用版本对比来实现)和悲观锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分，分为表锁和行锁

2. 三锁

2.1 表锁（偏读）
表锁偏向MyISAM存储引擎，开销小，加锁快，无思索，锁定粒度大，发生锁冲突的概率最高，并发度最低。

2.1.1 基本操作
建表SQL

CREATE TABLE `mylock` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR (20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

插入数据

INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');

手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
查看表上加过的锁
show open tables;
删除表锁
unlock tables;

2.1.2 案例分析(加读锁）

当前session和其他session都可以读该表
当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待

2.1.3 案例分析(加写锁）

当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

2.1.4 案例结论
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。
1、对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作
总结：
简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

2.2 行锁（偏写）
行锁偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MYISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。
2.2.1 行锁支持事务
事务（Transaction）及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。
原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
并发事务处理带来的问题
更新丢失（Lost Update）
　　当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。
脏读（Dirty Reads）
　　一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。
　　一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。
不可重读（Non-Repeatable Reads）
　　一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
　　一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性
幻读（Phantom Reads）
　　一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
　　一句话：事务A读取到了事务B提交的新增数据，不符合隔离性
脏读是事务B里面修改了数据
幻读是事务B里面新增了数据

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


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

2.2.2 行锁案例分析
用下面的表演示，需要开启事务，Session_1更新某一行，Session_2更新同一行被阻塞，但是更新其他行正常

2.2.3 隔离级别案例分析

CREATETABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`balance` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO`test`.`account` (`name`, `balance`) VALUES ('lilei', '450');
INSERT INTO`test`.`account` (`name`, `balance`) VALUES ('hanmei', '16000');
INSERT INTO`test`.`account` (`name`, `balance`) VALUES ('lucy', '2400');

　　1、读未提交：
　　　（1）打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读），查询表account的初始值：
set tx_isolation=’read-uncommitted‘;

　　　（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：



　　　（3）这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：


　　　（4）一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏数据：


　　　（5）在客户端A执行更新语句update account set balance = balance - 50 where id =1，lilei的balance没有变成350，居然是400，是不是很奇怪，数据不一致啊，如果你这么想就太天真 了，在应用程序中，我们会用400-50=350，并不知道其他会话回滚了，要想解决这个问题可以采用读已提交的隔离级别


　　2、读已提交
　　　（1）打开一个客户端A，并设置当前事务模式为read committed（未提交读），查询表account的所有记录：
set tx_isolation=’read-committed‘;

　　　　（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：


　　　　（3）这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题：


　　　　（4）客户端B的事务提交

　　　　（5）客户端A执行与上一步相同的查询，结果 与上一步不一致，即产生了不可重复读的问题


　　3、可重复读
　　　（1）打开一个客户端A，并设置当前事务模式为repeatable read，查询表account的所有记录
settx_isolation=’repeatable-read‘;

　　　（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account并提交

　　　（3）在客户端A查询表account的所有记录，与步骤（1）查询结果一致，没有出现不可重复读的问题

　　　（4）在客户端A，接着执行update balance = balance - 50 where id =1，balance没有变成400-50=350，lilei的balance值用的是步骤（2）中的350来算的，所以是300，数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。

（5）重新打开客户端B，插入一条新数据后提交

（6）在客户端A查询表account的所有记录，没有 查出 新增数据，所以没有出现幻读

(7)验证幻读
在客户端A执行update account set balance=888 where id = 4;能更新成功，再次查询能查到客户端B新增的数据


　　4.串行化
　　　（1）打开一个客户端A，并设置当前事务模式为serializable，查询表account的初始值：
settx_isolation=’serializable‘;
mysql> set session transaction isolationlevel serializable;
Query OK, 0rows affected (0.00sec)
mysql>start transaction;
Query OK, 0rows affected (0.00sec)
mysql>select from account;
|id |name |balance |+———+————+————-+| 1 | lilei | 10000|| 2 | hanmei | 10000 || 3 | lucy | 10000 || 4 | lily | 10000 |+———+————+————-+4 rows inset (*0.00 sec)

　　（2）打开一个客户端B，并设置当前事务模式为serializable，插入一条记录报错，表被锁了插入失败，mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。

mysql> set session transaction isolationlevel serializable;
Query OK, 0rows affected (0.00sec)
mysql>start transaction;
Query OK, 0rows affected (0.00sec)
mysql>insert intoaccount values(5,’tom’,0);
ERROR 1205(HY000): Lock wait timeout exceeded; try restarting transaction

Mysql默认级别是repeatable-read，有办法解决幻读问题吗？
间隙锁在某些情况下可以解决幻读问题
要避免幻读可以用间隙锁在Session_1下面执行update account set name = ‘zhuge’ where id > 10 and id <=20;，则其他Session没法插入这个范围内的数据

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

2.2.5 行锁分析
通过检查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（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

2.2.6 死锁
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;
查看近期死锁日志信息：showengine innodb status\G;
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

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