索引
什么是索引
- 我们可以设置一个索引或者多个索引
- 对一列或者多列合并成为一个索引key index(name+age)
- 索引是一个有序并且可以排序的数据结构
索引的优缺点
| 优势 | 劣势 |
|---|---|
| 提高数据检索的效率，降低数据库的IO成本 | 索引列也是要占用空间的。 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。 | 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。 |
总结
优点：加快查询效率
缺点：占空间，增删改效率低
索引的种类
- 普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。 - 唯一索引
索引列中的值必须是唯一的，但是允许为空值。 - 全文索引（替代模糊查询）
只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建, 只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 普通索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
索引的数据结构
B+Tree
B+树和B树最主要的区别在于非叶子节点是否存储数据的问题
B树：
非叶子节点和叶子节点都会存储数据。
B+树：
所有的数据都会出现在叶子节点。
叶子节点形成一个单向链表。
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的
特点
- 遍历全表的速度特别快，不需要查询了子节点之后，再返回父节点进行查询
- 范围查询的时候，只要找到第一个节点就能很快找到后面的节点，也是不需要再返回父节点进行查询
Hash（对象、值→数字）重复
特点
A. Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
B. 无法利用索引完成排序操作
C. 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引
哈希碰撞：值不同，哈希值相同
为什么InnoDB存储引擎选择使用B+tree索引结构?
A. 相对于二叉树，层级更少，搜索效率高；
B. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储
的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
C. 相对Hash索引，B+tree支持范围匹配及排序操作；
聚簇索引和非聚簇索引
聚簇索引也叫聚集索引，一般指主键索引
- 聚簇索引 其实就是围绕主键建立
回表查询
回表查询，实际查询两次，通过辅助索引（普通索引）实现的。因为辅助索引叶子节点不存放记录数据，只存放普通值和对应记录主键值
例如：表t中有四条记录
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
对应的主键索引（聚集索引），和辅助索引（普通索引）如下
查询语句如下:
create index idx_t_name on t(name);
select * from t where name='lisi';
执行流程：
1、先进行一次B+Tree查找，通过普通索引查找lisi对应的数据（叶子节点中）
2、再通过叶子节点中，data域中保存的lisi对应的主键值，进行一次B+Tree查找，找到对应记录行
3、将数据查找出来
这就是回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低，应为两次查找Tree，磁盘IO较多。
也就是说一般情况下，只要使用普通索引，并且select 字段不属于索引，单次普通索引Tree查找，无法获取满足的数据，会进行第二次Tree查找
覆盖索引
覆盖索引定义：MySQL 执行计划 explain 结果里的 key 有使用索引，如果 select 后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra 里一般都有 using index；覆盖索引一般针对的是辅助索引，整个査询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。
1.覆盖索引是一种数据查询方式，不是索引类型
2.在索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，那么就叫覆盖索引
3.查询的字段被使用到的索引树全部覆盖到
假设你定义一个联合索引
CREATE INDEX idx_name_age ON user(name,age);
查询名称为 liudehua 的年龄：
mysql> select name, age from user where name = 'liudehua';
上述语句中，查找的字段 name 和 age 都包含在联合索引 idx_name_age 的索引树中，这样的查询就是覆盖索引查询。
索引条件下推（Index Condition Pushdown）
5.6 以后完善的功能。只适用于二级索引。
ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。
索引的建立原则
- 如何建立
- 建立什么样的索引
- 在哪些字段建立索引
1). 针对于数据量较大，且查询比较频繁的表建立索引。
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，
避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增
删改的效率。
create unique index idx_user_phone_name on tb_user(phone,name); 1
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含
NULL值时，它可以更好地确定哪个索引最有效地用于查询。
sql优化方案
1.1插入数据(一次性往数据库表中插入多条记录)
insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
.....
- 批量插入数据
Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
- 手动控制事务
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;
-主键顺序插入 性能要高于乱序插入
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
1.2插入数据(一次性大批量插入数据)
- 主键顺序插入性能高于乱序插入
2.主键优化
3.order by优化
原则
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。
4.group by优化
在分组操作中，我们需要通过以下两点进行优化，以提升性能：
- 在分组操作时，可以通过索引来提高效率。
- 分组操作时，索引的使用也是满足最左前缀法则的。
5.limit优化
在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。
- 优化思路:
一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
6.count优化
概述
- MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个
数，效率很高； 但是如果是带条件的count，MyISAM也慢。 - InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。
用法
| count用法 | 含义 |
|---|---|
| count(主键) | InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null) |
| count(字段) | 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。 |
| count(数字) | InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。 |
| count(*) | InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。 |
按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)。
7.update优化
我们主要需要注意一下update语句执行时的注意事项。
update course set name = 'javaEE' where id = 1;
当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。
但是当我们在执行如下SQL时
update course set name = 'SpringBoot' where name = 'PHP' ;
当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。
索引失效的场景和如何避免索引失效
- 不要在索引列上进行运算操作， 索引将失效。
-- 当根据phone字段进行等值匹配查询时, 索引生效。
explain select * from tb_user where phone = '17799990015';
-- 当根据phone字段进行函数运算操作之后，索引失效。
explain select * from tb_user where substring(phone,10,2) = '15';
- 字符串类型字段使用时，不加引号，索引将失效。
接下来，我们通过两组示例，来看看对于字符串类型的字段，加单引号与不加单引号的区别：
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= 0;
explain select * from tb_user where phone = '17799990015';
explain select * from tb_user where phone = 17799990015;
- 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';
- 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;
-- 由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。
-- 然后，我们可以对age字段建立索引。
create index idx_user_age on tb_user(age);
- 如果MySQL评估使用索引比全表更慢，则不使用索引。
select * from tb_user where phone >= '17799990005';
select * from tb_user where phone >= '17799990015';
explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;
is null 、is not null是否走索引，得具体情况具体分析，并不是固定的
- 批量插入数据
联合索引
最左匹配原则
建立联合索引时会遵循最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配
例如：
为user表中的name、address、phone列添加联合索引
ALTER TABLE user ADD INDEX index_three(name,address,phone);
所以，下面的三个SQL语句都可以命中索引
SELECT * FROM user WHERE address = ‘北京’ AND phone = ‘12345’ AND name = ‘张三’;
SELECT * FROM user WHERE name = ‘张三’ AND address = ‘北京’;
SELECT * FROM user WHERE name = ‘张三’;
这三条SQL语句在检索时分别会使用以下索引进行数据匹配 (name,address,phone) (name,address) (name)
索引字段出现的顺序可以是任意的，MySQL优化器会帮我们自动的调整where条件中的顺序
如果联合索引中最左边的列不在查询条件中，则不会命中索引
SELECT * FROM user WHERE address = ‘北京’ ;
数据库优化
避免回表
在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能。那如何避免呢？
使用索引覆盖，举个例子：现有User表（id(PK),name(key),sex,address,hobby…）
如果在一个场景下，select id,name,sex from user where name =‘zhangsan’;这个语句在业务上频繁使用到，而user表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，不是使用单一索引，而是使用联合索引（name，sex）这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。
这就是一个典型的使用覆盖索引的优化策略减少回表的情况。
联合索引的使用
联合索引，在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。试想一下，索引的字段越多，是不是更容易满足查询需要返回的数据呢。比如联合索引（a_b_c），是不是等于有了索引：a，a_b，a_b_c三个索引，这样是不是节省了空间，当然节省的空间并不是三倍于（a，a_b，a_b_c）三个索引，因为索引树的数据没变，但是索引data字段的数据确实真实的节省了。
联合索引的创建原则，在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建的需要考虑到的优化场景，也可以在常需要作为查询返回的字段上增加到联合索引中，如果在联合索引上增加一个字段而使用到了覆盖索引，那建议这种情况下使用联合索引。
联合索引的使用：
考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。
当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。
补充 索引
前缀索引
当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让
索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建
立索引，这样可以大大节约索引空间，从而提高索引效率。
语法
create index idx_xxxx on table_name(column(n)) ;
执行计划 explain
建立连接→查看缓存（取消了）→词法解析 语法解析—解析树→查询优化器—根据解析树生成执行计划→执行引擎→存储引擎
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法:
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
| 字段 | 含义 |
|---|---|
| type | 表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all |
| select_type | 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等 |
事务
数据库并发有以下几种场景：
读-读：不存在任何问题。
读-写：有线程安全问题，可能出现脏读、幻读、不可重复读。
写-写：有线程安全问题，可能存在更新丢失等。
什么是数据事务？
事务（Transaction）是将⼀组操作封装成⼀个执⾏单元（封装到⼀起），这⼀个执⾏单元要么⼀起执⾏成功，要么⼀起失败，不会出现执⾏“⼀半”的情况。
注意：数据库引擎InnoDB才支持事务，MyISAM不支持事务。
为什么要使用事务？
以银⾏转账为例，张三给李四 转账，那么它的执⾏流程是这样的：
1、张三账户 -500 update account set money=money-500 where name = ‘张三’;
2、B李四账户 +500 update account set money=money+500 where name = ‘李四’;
如果执⾏了⼀半，断电了或者程序崩溃了，那么 A 账号的钱就永久消失了？那怎么办？⽤事务就可以解决这个问题，将这两个操作封装成⼀个执⾏单元，要么⼀起成功，要么⼀起失败。
事务的使用
1.开启事务：start transaction（mysql8之前）， begin（mysql8之后）
2. 执⾏多条 SQL
3. 提交事务：commit
4. 回滚事务：rollback
- savepoint 断点 存档
事务四大特性
① 原⼦性(Atomicity)：⼀个事务中的所有操作，要么全部执⾏成功，要么全部执⾏失败。
原⼦性是事务最重要的特性，全部执⾏失败并不是不执⾏，⽽是通过逆操作 rollback（回滚）数据。
实现原理：回滚是逆 SQL 操作：⽐如 A 账户 -500 元的逆操作就是 A 账户 +500 元。
② ⼀致性(Consistency)：⼀个事务在执⾏前后数据必须保持⼀种合法的状态，事务总是从⼀个⼀致状态到另⼀个⼀致状态。
举例说明：⽐如转账操作，转账之前两个账号的总额是 100 元（各个 50 元），那么两个账户相互转账之后的钱总额也是 100 元，也就是事务总是从⼀个⼀致状态到另⼀个⼀致状态，不会说转账之前是100 元，相互转账之后变成 80 了，这就不符合⼀致性了。
③ 隔离性(Isolation)：多个事务并发访问时，事务之间是相互隔离的，⼀个事务不应该被其他事务⼲扰，多个并发事务之间要相互隔离。
④ 持久性(Durability)：事务执⾏完成之后，它所做的所有修改都是永久的（不会丢失）。
数据存储到磁盘中就是持久的，不会丢失的，不会因为电脑重启⽽丢失。
事务关联的日志
- redo log
- undo log
以撤销操作为目的，返回指定某个状态的操作。
指事务开始之后，在操作任何数据之前，首先将需操作的数据备份到一个地方(undo log) 。
undo Log是为了实现事务的原子性而出现的产物。
总结：undo log是用来回滚数据的保障，未提交事务原子性的保障。
事务处理过程中，如果出现了错误或者用户执行了ROLLBACK语句，MySQL可以利用undo Log中的备份将数据恢复到事务开始之前的状态。
redo log以恢复操作为目的，重现操作。
指事务中操作的任何数据，会将最新的数据备份到一个地方(redo log) 。
redo log一般是在事务提交的时候，以顺序IO的方式写入磁盘。
具体的落盘策略可以进行配置。
redo log实现了事务的持久性
防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而保证事务中尚未写入磁盘的数据的持久性。
一旦事务成功提交且数据持久化落盘之后，此时redo log中的对应事务数据记录就失去了意义，所以redo log的写入是日志文件循环写入的。
总结：redo log是用来恢复数据的保障，已提交事务持久化的保障。
事务的三种并发问题
①脏读：⼀个事务读取到了另⼀个事务修改的数据之后，后⼀个事务⼜进⾏了回滚操作，从⽽导致第⼀个事务读取的数据是错误的。
②不可重复读：在⼀个事务中，两次查询同⼀条数据得到了不同的结果就是不可重复读。在⼀个事务两次查询中间，另⼀个事务把这条数据修改了。
③幻读：当同⼀查询在不同时间产⽣不同的结果，就是事务中的幻读问题。例如，⼀个 SELECT 被执⾏了两次，但是第⼆次返回了第⼀次没有返回的⼀⾏，那么这⼀⾏就是⼀个“幻像”⾏。
事务的隔离级别
1.READ UNCOMMITTED：读未提交，也叫未提交读，该隔离级别的事务可以看到其他事务中未提交的数据。该隔离级别因为可以读取到其他事务中未提交的数据，⽽未提交的数据可能会发⽣回滚，因此我们把该级别读取到的数据称之为脏数据，把这个问题称之为脏读。
2. READ COMMITTED：读已提交，也叫提交读，该隔离级别的事务能读取到已经提交事务的数据,因此它不会有脏读问题。
但由于在事务的执⾏中可以读取到其他事务提交的结果，所以在不同时间的相同SQL 查询中，可能会得到不同的结果，这种现象叫做不可重复读。
3.REPEATABLE READ：可重复读，是 MySQL 的默认事务隔离级别，它能确保同⼀事务多次查询的结果⼀致。但也会有新的问题，⽐如此级别的事务正在执⾏时，另⼀个事务成功的插⼊了某条数据，但因为它每次查询的结果都是⼀样的，所以会导致查询不到这条数据，⾃⼰重复插⼊时⼜失败（因为唯⼀约束的原因）。明明在事务中查询不到这条信息，但⾃⼰就是插⼊不进去，这就叫幻读（Phantom Read）。
解说2：例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
4.SERIALIZABLE：序列化，事务最⾼隔离级别，它会强制事务排序，使之不会发⽣冲突，从⽽解决了脏读、不可重复读和幻读问题，但因为执⾏效率低，所以真正使⽤的场景并不多。（Oracle的默认隔离级别是序列化）
客户端1在操作时，客户端2是不能操作的，一直在排队等待。
并发控制方案
- LBCC
第一种，我既然要保证前后两次读取数据一致，那么我读取数据的时候，锁定我要操作的数据，不允许其他的事务修改就行了。这种方案我们叫做基于锁的并发控制 LockBased Concurrency Control（LBCC）。
如果仅仅是基于锁来实现事务隔离，一个事务读取的时候不允许其他时候修改，那就意味着不支持并发的读写操作，而我们的大多数应用都是读多写少的，这样会极大地影响操作数据的效率。 - MVCC
所以我们还有另一种解决方案，如果要让一个事务前后两次读取的数据保持一致，那么我们可以在修改数据的时候给它建立一个备份或者叫快照，后面再来读取这个快照就行了。这种方案我们叫做多版本的并发控制 Multi Version Concurrency Control（MVCC）。
MVCC 的核心思想是： 我可以查到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。
问题：这个快照什么时候创建？读取数据的时候，怎么保证能读取到这个快照而不是最新的数据？这个怎么实现呢？
InnoDB 为每行记录都实现了两个隐藏字段：
DB_TRX_ID，6 字节：插入或更新行的最后一个事务的事务 ID，事务编号是自动递增的（我们把它理解为创建版本号，在数据新增或者修改为新数据的时候，记录当前事务 ID）。
DB_ROLL_PTR，7 字节：回滚指针（我们把它理解为删除版本号，数据被删除或记录为旧数据的时候，记录当前事务 ID）。
我们把这两个事务 ID 理解为版本号。
具体例子：https://www.processon.com/view/link/5d29999ee4b07917e2e09298
- MVCC 的查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行（或未删除）。
也就是不能查到在我的事务开始之后插入的数据，tom 的创建 ID 大于 2，所以还是只能查到两条数据。
锁
分类
- 锁的粒度
- 行锁
- 表锁
- 页锁
- 共享锁 读锁
- 互斥锁 写锁 独占锁
- 意向锁
- 意向共享锁
- 意向排它锁
- 乐观锁
- 悲观锁
- 字段锁
- 间隙锁 gap
- 临键锁 next-key
表锁
- 该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。
- 由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。
- 使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎
适用场景：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用
行锁
- 与表锁正相反，行锁最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。
- 由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力从而提高系统的整体性能。
- 行级锁定也最容易发生死锁。
- 使用行级锁定的主要是InnoDB存储引擎
适用场景：从锁的角度来说，行级锁则更适合于有大量按索引条件并发更新数据的情况，同时又有并发查询的应用场景。
页锁
- 除了表锁、行锁外，MySQL还有一种相对偏中性的页级锁，页锁是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。
- 页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。
- 另外，页级锁定和行级锁定一样，会发生死锁。
- 使用页级锁定的主要是BerkeleyDB存储引擎。
共享锁（Share Lock）
共享锁，又叫读锁，是读取操作（SELECT）时创建的锁。其他用户可以并发读取数据，但在读锁未释放前，也就是查询事务结束前，任何事务都不能对数据进行修改（获取数据上的写锁），直到已释放所有读锁。
如果事务A对数据B（1024房）加上读锁后，则其他事务只能对数据B上加读锁，不能加写锁。获得读锁的事务只能读数据，不能修改数据。
SELECT … LOCK IN SHARE MODE;-- 加锁写法
排他锁（Exclusive Lock）
排他锁又称写锁、独占锁，如果事务A对数据B加上写锁后，则其他事务不能再对数据B加任何类型的锁。获得写锁的事务既能读数据，又能修改数据。
SELECT … FOR UPDATE;
意向锁（Intention Lock）
意向锁属于表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁：
-
意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；
-
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是 InnoDB 自动加的，不需要用户干预。
再强调一下，对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁
共享锁：SELECT … LOCK IN SHARE MODE;
排他锁：SELECT … FOR UPDATE;
悲观锁和乐观锁
其实悲观锁和乐观锁，也并不是 MySQL 或者数据库中独有的概念，而是并发编程的基本概念。主要区别在于，操作共享数据时，“悲观锁”即认为数据出现冲突的可能性更大，而“乐观锁”则是认为大部分情况不会出现冲突，进而决定是否采取排他性措施。
MySQL的多版本并发控制 （MVCC），其本质就可以看作是种乐观锁机制，而排他性的读写锁、两阶段锁等则是悲观锁的实现。
死锁
死锁的四个必要条件
1、互斥条件
2、不可剥夺条件
3、请求与保持条件
4、循环等待条件
如何避免死锁？
预防死锁：通过设置某些限制条件，去破坏产生死锁的四个必要条件中的一个或几个条件，来防止死锁的发生。
避免死锁：在资源的动态分配过程中，用某种方法去防止系统进入不安全状态，从而避免死锁的发生。
检测死锁：允许系统在运行过程中发生死锁，但可设置检测机构及时检测死锁的发生，并采取适当措施加以清除。
解除死锁：当检测出死锁后，便采取适当措施将进程从死锁状态中解脱出来。
176
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
