MySQL基础
基本
Innodb和MySIAM两种引擎的区别
MYIASM:管理非事务表，提供高速存储和检索，以及全文搜索能力，如果在应用中执行大量的select操作，应选择MYIASM引擎
Innodb:用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量的insert和update操作，应选择innodb引擎。
什么是主键?什么是外键?
主键:表格里的(一个或多个)字段，只用来定义表格里的行;主键里的值总是唯一的；
外键:一个用来建立两个表格之间关系的约束； 这种关系一般都涉及一个表格里的主键字段与另外一个表格(尽管可能是同一个表格)里的一系列相连的字段。那么这些相连的字段就是外键。
主键在本表中是唯一的、不可为空的，外键可以重复可以唯空； 外键和另一张表的主键关联，不能创建对应表中不存在的外键；
索引
数据结构
哈希表
哈希型的索引，对于排序查询时间复杂度会退化为O(n)，而树型的“有序”特性，依然能够保持O(log(n)) 的高效率。 所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。
数组
如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，往中间插入一个记录就必须得挪动后面所有的记录，成本太高。 有序数组索引只适用于静态存储引擎，比如要保存的是2017年某个城市的所有人口信息，这类不会再修改的数据。
二叉树
二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。 想象一下一棵100万节点的平衡二叉树，树高20。一次查询可能需要访问20个数据块。 在机械硬盘时代，从磁盘随机读一个数据块需要10ms左右的寻址时间。 也就是说，对于一个100万行的表，如果使用二叉树来存储，单独访问一个行可能需要20个10ms的时间，这个查询会很慢。
为什么不适合用作数据库索引？ (1)当数据量大的时候，树的高度会比较高，数据量大的时候，查询会比较慢； (2)每个节点只存储一个记录，可能导致一次查询有很多次磁盘IO；
“N叉”树
为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。 那么，就不应该使用二叉树，而是要使用“N叉”树。这里，“N叉”树中的“N”取决于数据块的大小。
以InnoDB的一个整数字段索引为例，这个N差不多是1200。 这棵树高是4的时候，就可以存1200的3次方个值，这已经 17 亿了。 考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。 其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。
B树
B树，它的特点是： (1)不再是二叉搜索，而是m叉搜索； (2)叶子节点，非叶子节点，都存储数据； (3)中序遍历，可以获得所有节点；
B树被作为实现索引的数据结构被创造出来，是因为它能够完美的利用“局部性原理”。
B树为何适合做索引？ (1)由于是m分叉的，高度能够大大降低； (2)每个节点可以存储j个记录，如果将节点大小设置为页大小，例如4K，能够充分的利用预读的特性，极大减少磁盘IO；
B+树
B+树，仍是m叉搜索树，在B树的基础上，做了一些改进： (1)非叶子节点不再存储数据，数据只存储在同一层的叶子节点上； B+树中根到每一个节点的路径长度一样，而B树不是这样。 (2)叶子之间，增加了链表，获取所有节点，不再需要中序遍历；
这些改进让B+树比B树有更优的特性： (1)范围查找，定位min与max之后，中间叶子节点，就是结果集，不用中序回溯； 画外音：范围查询在SQL中用得很多，这是B+树比B树最大的优势。 (2)叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储； (3)非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引；
跳表
LSM 树
InnoDB 的索引模型
在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。
每一个表是好几棵B+树, 树结点的key值就是某一行的主键，value是该行的其他数据。新建索引就是新增一个B+树，查询不走索引就是遍历主B+树。
B+ 树
InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。
聚簇索引（clustered index）–主键索引
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
非主键索引
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。
- 基于主键索引和普通索引的查询有什么区别？
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树； 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树， 得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
怎样维护索引
显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。 从性能和存储空间方面考量，自增主键往往是更合理的选择。
页分裂
如果插入的数据不是在索引最后，或者最后一页索引所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。
有没有什么场景适合用业务字段直接做主键的呢？
还是有的。比如，有些业务的场景需求是这样的：
- 只有一个索引；
- 该索引必须是唯一索引。 典型的 KV 场景。
索引重建?
非主键:
alter table T drop index k;
alter table T add index(k);
删除重建普通索引貌似影响不大，不过要注意在业务低谷期操作，避免影响业务。
主键：
alter table T drop primary key;
alter table T add primary key(id);
如果删除，新建主键索引，会同时去修改普通索引对应的主键索引，性能消耗比较大。 drop主键索引会导致其他索引失效，但drop普通索引不会。 顺序应是先删除k列索引，主键索引。然后再创建主键索引和k列索引。
- 直接删掉主键索引是不好的，它会使得所有的二级索引都失效，并且会用ROWID来作主键索引；
- 看到mysql官方文档写了三种措施， 第一个是整个数据库迁移，先dump出来再重建表（这个一般只适合离线的业务来做）； 第二个是用空的alter操作，比如ALTER TABLE t1 ENGINE = InnoDB;这样子就会原地重建表结构（真的吗？）； 第三个是用repaire table，不过这个是由存储引擎决定支不支持的（innodb就不行）。
alter table T engine=InnoDB
正确使用索引
数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
即使建立索引，索引也不会生效：
-
like ‘%xx’ select * from tb1 where name like ‘%cn’;
-
使用函数 select * from tb1 where reverse(name) = ‘wupeiqi’;
-
or select * from tb1 where nid = 1 or email = ‘seven@live.com’; 特别的：当or条件中有未建立索引的列才失效，以下会走索引 select * from tb1 where nid = 1 or name = ‘seven’; select * from tb1 where nid = 1 or email = ‘seven@live.com’ and name = ‘alex’
-
类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然… select * from tb1 where name = 999;
-
!= select * from tb1 where name != ‘alex’ 特别的：如果是主键，则还是会走索引 select * from tb1 where nid != 123
-
select * from tb1 where name > ‘alex’ 特别的：如果是主键或索引是整数类型，则还是会走索引 select * from tb1 where nid > 123 select * from tb1 where num > 123
-
order by select email from tb1 order by name desc; 当根据索引排序时候，选择的映射如果不是索引，则不走索引 特别的：如果对主键排序，则还是走索引： select * from tb1 order by nid desc;
- 组合索引最左前缀 如果组合索引为：(name,email) name and email – 使用索引 name – 使用索引 email – 不使用索引
几个问题
- 覆盖索引是什么？
就是假如有一个联合索引(a,b,c)，如果只是需要a,b,c这几个字段的数据，查询时就不需要根据主键id去聚集索引里面回表查询了
SELECT a,b,c FROM user where a = 1
这个就是覆盖索引。
- 现在一般自增索引都设置为bigint?
现在很多业务插入数据很凶残，容易超过int上限，实际上是建议设置bigint unsigned
- 如果插入的数据是在主键树叶子结点的中间，后面的所有页如果都是满的状态，是不是会造成后面的每一页都会去进行页分裂操作，直到最后一个页申请新页移过去最后一个值?
- 不会，只会分裂它要写入的那个页面。每个页面之间是用指针串的，改指针就好了，不需要“后面的全部挪动
- 插入数据如果是在某个数据满了页的首尾，为了减少数据移动和页分裂，会先去前后两个页看看是否满了，如果没满会先将数据放到前后两个页上，不知道是不是有这种情况?
对，减为了增加空间利用率
- 非聚集索引上为啥叶子节点的value为什么不是地址，这样可以直接定位到整条数据，而不用再次对整棵树进行查询?
作者回复: 这个叫作“堆组织表”，MyISAM就是这样的，各有利弊。想一下如果修改了数据的位置的情况，InnoDB这种模式是不是就方便些
- 外键
外键可以用来做约束 但是这种约束关系是在数据库里面做的（类似于存储过程，其实是一种逻辑）
这种情况下，等于数据库里面也有业务逻辑， 这个就要看项目管理上做得怎么样
如果能够把这些关系也作为代码的一部分，其实是可以的 之前很多人会觉得说加了存储过程、触发器、外键这些以后，代码逻辑混乱， 一个原因也是因为没有把数据库里的逻辑像代码一样管理好
- InnoDB到底支不支持哈希索引？
对于InnoDB的哈希索引，确切的应该这么说：
（1）InnoDB用户无法手动创建哈希索引，这一层上说，InnoDB确实不支持哈希索引；
（2）InnoDB会自调优(self-tuning)，如果判定建立自适应哈希索引(Adaptive Hash Index, AHI)，
能够提升查询效率，InnoDB自己会建立相关哈希索引，这一层上说，InnoDB又是支持哈希索引的；
当业务场景为下面几种情况时：
-
很多单行记录查询（例如passport，用户中心等业务）
-
索引范围查询（此时AHI可以快速定位首行记录）
-
所有记录内存能放得下
AHI往往是有效的。
当业务有大量like或者join，AHI的维护反而可能成为负担，降低系统效率，此时可以手动关闭AHI功能。
事务
事务定义
1.什么是事务
事务：事务是并发控制的基本单元，事务是一个操作序列，要么都执行，要么都不执行， 是一个不可分割的工作单位，事务是维护数据库一致性的单位。 锁：在所以的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。 与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。
四个ACID基本性质：
1.原子性：要么都执行，要么都不执行。 2.一致性：合法的数据才可以被写入。 3.隔离性：允许多个用户并发访问。 4.持久性：事务结束后，事务处理的结果必须得到固化。即一旦提交，对数据库改变是永久的。
这几个特性不是一种平级关系：
只有满足一致性，事务的执行结果才是正确的。 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时要只要能满足原子性，就一定能满足一致性。 在并发的情况下，多个事务并发执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。 事务满足持久化是为了能应对数据库崩溃的情况。
事务的语句：
1.开始事务：BEGIN TRANSACTION
2.提交事务：COMMIT TRANSACTION
3.回滚事务：ROLLBACK TRANSACTION
事务是由一组SQL语句组成的逻辑处理单元，是满足ACID 特性的一组操作，可以通过Commit 提交一个事务，也可以使用Rollback 进行回滚。
并发一致性问题
1、更新丢失(Lost Update)
T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。 如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。
2、脏读
一句话：事务B读取到了事务A已修改但尚未提交的的数据，还在这个数据基础上做了操作。 此时，如果A事务回滚Rollback，B读取的数据无效，不符合一致性要求。
解决办法: 把数据库的事务隔离级别调整到 READ_COMMITTED T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
3、不可重复读(Non-Repeatable Reads)
在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。 那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样， 这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。
一句话：一个事务范围内两个相同的查询却返回了不同数据。
同时操作，事务1分别读取事务2操作时和提交后的数据，读取的记录内容不一致。 不可重复读是指在同一个事务内，两个相同的查询返回了不同的结果。
解决办法: 如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。把数据库的事务隔离级别调整到REPEATABLE_READ
T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
4、幻读
一个事务T1按相同的查询条件重新读取以前检索过的数据，却发现其他事务T2插入了满足其查询条件的新数据，这种现象就称为“幻读”。（和可重复读类似，但是事务 T2 的数据操作仅仅是插入和删除，不是修改数据，读取的记录数量前后不一致）
一句话：事务A 读取到了事务B提交的新增数据，不符合隔离性。
解决办法: 如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题。 把数据库的事务隔离级别调整到SERIALIZABLE_READ。
如何解决幻读？
- 使用串行化读的隔离级别
- MVCC+next-key locks：next-key locks由record locks(索引加锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)
T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。
事务隔离级别
“脏读”、”不可重复读”和”幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。 同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
MYSQL常看当前数据库的事务隔离级别：show variables like ‘tx_isolation’;
1、读未提交 (Read Uncommitted)
最低的隔离等级，允许其他事务看到没有提交的数据，会导致脏读。
2、读已提交 (Read Committed)
被读取的数据可以被其他事务修改，这样可能导致不可重复读。也就是说，事务读取的时候获取读锁，但是在读完之后立即释放(不需要等事务结束)，而写锁则是事务提交之后才释放，释放读锁之后，就可能被其他事务修改数据。该等级也是 SQL Server 默认的隔离等级。
3、可重复读(Repeatable Read)
所有被Select获取的数据都不能被修改，这样就可以避免一个事务前后读取数据不一致的情况。但是却没有办法控制幻读，因为这个时候其他事务不能更改所选的数据，但是可以增加数据，即前一个事务有读锁但是没有范围锁，为什么叫做可重复读等级呢？那是因为该等级解决了下面的不可重复读问题。(引申：现在主流数据库都使用 MVCC 并发控制，使用之后RR（可重复读）隔离级别下是不会出现幻读的现象。)
MYSQL默认是REPEATABLE-READ 。
4、串行化(Serializable)
所有事务一个接着一个的执行，这样可以避免幻读 (phantom read)，对于基于锁来实现并发控制的数据库来说，串行化要求在执行范围查询的时候，需要获取范围锁，如果不是基于锁实现并发控制的数据库，则检查到有违反串行操作的事务时，需回滚该事务。
5、总结
1，未提交读：读数据时不会检查使用任何锁；
2，已提交读：只读取提交的数据并等待其他事务释放锁；
3，可重复读：会保持共享锁到事务结束；
4，可序列化：不仅会锁定影响的数据，还会锁定这个范围；
读未提交: 一个事务还没提交时，它做的变更就能被别的事务看到。
读提交: 一个事务提交之后，它做的变更才会被其他事务看到。
可重复读 : 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化: 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
四个级别逐渐增强，每个级别解决一个问题，事务级别越高，性能越差，大多数环境(Read committed 就可以用了)
并发控制
通过并发控制保证数据一致性的常见手段有：
1.锁（Locking）
2.数据多版本（Multi Versioning）
锁
普通锁，被使用最多：
(1)操作数据前，锁住，实施互斥，不允许其他的并发任务操作；
(2)操作完成后，释放锁，让其他任务执行；
简单的锁住太过粗暴，连“读任务”也无法并行，任务执行过程本质上是串行的。
共享锁vs排他锁
-
共享锁（Share Locks，记为S锁），读取数据时加S锁 共享锁之间不互斥，简记为：读读可以并行
-
排他锁（eXclusive Locks，记为X锁），修改数据时加X锁 排他锁与任何锁互斥，简记为：写读，写写不可以并行
对应到数据库，可以理解为，写事务没有提交，读相关数据的select也会被阻塞。 一旦写数据的任务没有完成，数据是不能被其他任务读取的，这对并发度有较大的影响。 即使写任务没有完成，其他读任务也可能并发，这就引出了数据多版本。
数据多版本
数据多版本是一种能够进一步提高并发的方法，它的核心原理是：
（1）写任务发生时，将数据克隆一份，以版本号区分；
（2）写任务操作新克隆的数据，直至提交；
（3）并发读任务可以继续读取旧版本的数据，不至于阻塞；
数据多版本，通过“读取旧版本数据”能够极大提高任务的并发度。
总体思路
提高并发的演进思路，就在如此：
1.普通锁，本质是串行执行
2.读写锁，可以实现读读并发
3.数据多版本，可以实现读写并发
redo，undo，回滚段
redo日志
redo日志用于保障，已提交事务的ACID特性。
- 随机写优化为顺序写
数据库事务提交后，必须将更新后的数据刷到磁盘上，以保证ACID特性。 磁盘随机写性能较低，如果每次都刷盘，会极大影响数据库的吞吐量。 优化方式是，将修改行为先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上，这样能极大提高性能。
假如某一时刻，数据库崩溃，还没来得及刷盘的数据，在数据库重启后，会重做redo日志里的内容， 以保证已提交事务对数据产生的影响都刷到磁盘上。
undo日志
undo日志用于保障，未提交事务不会对数据库的ACID特性产生影响。
为什么用undo： 数据库事务未提交时，会将事务修改数据的镜像（即修改前的旧版本）存放到undo日志里， 当事务回滚时，或者数据库崩溃时，可以利用undo日志，即旧版本数据，撤销未提交事务对数据库产生的影响。
对于insert操作，undo日志记录新数据的PK(ROW_ID)，回滚时直接删除；—-自增ID不连续问题
对于delete/update操作，undo日志记录旧数据row，回滚时直接恢复；
回滚段
存储undo日志的地方，是回滚段。
多版本并发控制（Multi Version Concurrency Control, MVCC）
行锁，并发，事务回滚等多种特性都和MVCC相关。 InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁
MVCC就是通过“读取旧版本数据”来降低并发事务的锁冲突，提高任务的并发度。
MVCC如何工作
InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现。 这两个列一个保存了行的创建时间，一个保存行的过期时间（删除时间）。 当然存储的并不是真实的时间而是系统版本号（system version number）。 每开始一个新的事务，系统版本号都会自动新增。 事务开始时刻的系统版本号会作为事务的版本号，用来查询到每行记录的版本号进行比较。
影响
旧版本数据存储在哪里？
存储旧版本数据，对MySQL和InnoDB原有架构是否有巨大冲击？
(1)旧版本数据存储在回滚段里；
(2)对MySQL和InnoDB原有架构体系冲击不大；
InnoDB的内核，会对所有row数据增加三个内部属性：
(1)DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID；
(2)DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针；
(3)DB_ROW_ID，6字节，单调递增的行ID；
快照读
普通的select语句都是快照读
这种一致性不加锁的读（Consistent Nonlocking Read），就是InnoDB并发如此之高的核心原因之一。
显式加锁，非快照读是指：
select * from t where id>2 lock in share mode;
select * from t where id>2 for update;
MySQL是怎么解决幻读的问题的？
幻读就是事务执行过程中，在查询一个范围的数据时，有新的数据插入到这个范围，导致两次查询的数据不一致。 因为读分为快照读和实时读；
快照读
普通的SELECT语句都是普通读，也就是读取的数据都是事务开始时那个状态的数据，普通读的幻读问题主要是通过MVCC来解决的，具体可以看上面的MVCC中的查询操作。
实时读
SELECT *** FOR UPDATE 在查询时会先申请X锁
SELECT *** IN SHARE MODE 在查询时会先申请S锁
就是实时读，就是读取的是实时的数据，而不快照数据，读的时候会加Next-Key Lock锁住当前的记录，以及左右两个区间的间隙，这样在读的时候就不能往查询范围插入数据了。
如何避免长事务对业务的影响？
这个问题，可以从应用开发端和数据库端来看。
开发端
首先，从应用开发端来看：
- 确认是否使用了 set autocommit=0。 这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过general_log的日志来确认。 一般框架如果会设置这个值，也就会提供参数来控制行为，目标就是把它改成1。
- 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。 有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。 这种只读事务可以去掉。
- 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令， 来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。（为什么会意外？在后续的文章中会提到这类案例）
数据库端
其次，从数据库端来看：
- 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
- Percona 的 pt-kill 这个工具不错，推荐使用；
- 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
- 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。 如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。
锁机制
加锁是为了实现并发控制。数据库是一个多用户资源， 若对并发控制不加控制会读取和存储不正确的数据，破坏数据的一致性（脏读，不可重复读，幻读等）可能会产生死锁。 锁机制保证在一个事务释放锁之前其他事务不可以进行修改。
行级锁;
表级锁;
表级别的锁有两种，一种是表锁，一种是元数据锁MDL。
表锁 lock table:
就是使用lock table user_table read/write命令来对表进行加读锁或者写锁 加读锁后，表对所有线程都是只能读，即便是当前线程也只能读表，不然会数据不一致。 加写锁后，表是对当前线程写，其他线程不能读，不能回数据不一致。 可以通过unlock tables来解锁，客户端断开时也会自动释放锁，但是影响所有线程，影响面太大了。
元数据锁MDL(MetaData Lock) 分为读锁和写锁，加读锁时，所有的线程都可以读表，加写锁时，只能一个线程写，其他的不能读。 锁不用显式使用，是访问一个表时，自动加上的。 对表进行增删改查时，会加读锁。 对表结构做修改时，会加写锁。
目的是为了在增删改查时不能修改表结构，修改表结构时不能去增删改查。
悲观锁
事务每次操作之前假设有其他事务会修改需访问的数据，会要求上锁;
乐观锁
事务每次操作之前假设没有其他事务会修改需访问的数据，不会要求上锁;
共享锁
对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源
共享锁（S锁）：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的 事务只能读数据，不能修改数据。
排他锁（X锁）：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务 既能读数据，又能修改数据。
共享锁下其它用户可以并发读取，查询数据。但不能修改，增加，删除数据。资源共享。
全局锁
就是对整个数据库加锁，让整个数据库处于只读状态，所有更新操作停止。（如果是主库就不能执行更新语句，从库也不能执行同步过来的bin log）;
最常用的场景是对数据库加锁，让数据库只能读，然后对整个数据库做逻辑备份（就是将所有数据生成SQL写入备份文件。）
做逻辑备份有三种方式：
1.全局锁
对数据库执行
Flush tables with read lock命令让整个库处于只读状态。
2.利用innodb的事务隔离性（可重复读）
就是通过官方自带的逻辑备份工具mysqldump来进行逻辑备份时，可以设置一个参数-single-transaction，这样导数据的时候就会开启一个事务，这样利用innodb的mvcc机制可以保证在事务执行过程中，读到的数据都跟事务开始时的一致，并且执行过程中，其他事务可以执行更新操作， 不会对他造成影响。这种方法必须要求数据库所有表的引擎都是innodb才行。
3.set global readonly=true
执行这个命令也可以让全库只能读，但是第一有些系统会使用readonly来做一个操作，例如根据readonly是否为true判断数据库是否是从库，第二是如果执行这个命令后，客户端断开连接后，数据库会一直处于只读状态，如果是FTWRL命令发送异常会释放全局锁。（如果是从库，设置read-only对super user权限无效）
自增锁
插入语句主要分为两种：
1.能确定插入行数的，例如插入一条或者多条数据，INSERT…
2.不能确定行数的，例如从一个表查询出满足条件的数据，然后插入另外一个表，INSERT…SELECT
在所有模式中，如果一个事务回滚，这些自增值将被“丢失”。
innodb_autoinc_lock_mode为0 这种是tradition模式，每次执行一条插入语句时都会去申请表级别的auto_increment锁
innodb_autoinc_lock_mode为1 这种是consecutive模式，执行不确定数量的插入语句时，才会去申请表级别的auto_increment锁， 执行确定数量的插入语句时，只需要执行前去获取 AUTO_INCREMENT 计数器的互斥锁并在获取主键后直接释放， 不需要等待当前语句执行完成。
innodb_autoinc_lock_mode为2 交叉模式 所有的插入语句都不需要获取表级别的 AUTO_INCREMENT 锁， 如果binlog_format为statement模式，如果从服务器上的计数器的值可能会与主服务器不一致， 可能会有同一行数据在主从数据库上id不一样的情况，如果binlog_format为row模式，那么就不影响。
意向锁
意向锁定的主要目的是表明有人正在锁定表中的行，或者打算锁定表中的行，这样再加表级别的排斥锁X，共享锁S时，避免了去查询每一行数据，判断是否加了行锁，减小了性能开销。
意向共享锁（IS锁）
事务让一行数据只能读，需要申请对这行数据加行级别的锁共享锁S，在申请S锁之前会主动申请表级别的共享意向锁IS锁。
意向排斥锁(IX锁)
事务在更新某一行数据时，需要申请对这行数据加行级别的锁排斥锁X，在申请X锁之前会申请IX
意向锁之间是兼容的，IS锁和IX是兼容，因为可能对第一行数据加S锁，那么会申请IS锁，对第二行数据加X锁，此时跟第一行的数据的S锁不冲突，所以也会先申请IX锁，由此可见，IS锁和IX之间不冲突，IS锁，IX锁与行级别的S，行级别的X之间也不冲突。
意向锁只是跟表级别的S，X锁可能会冲突。
表级别的S锁 表级别的X锁 IS 兼容 不兼容 IX 不兼容 不兼容
意向锁的作用:
假如没有意向锁，执行lock table read命令来申请表锁，让整个表只能读，在获得表级别的只读锁之前，需要执行的步骤是：
1.数据库会先判断当前表是否加了排斥锁，因为这个时候要是加了排斥锁，是只能由加了那个排斥锁的事务来更新数据，其他事务都不能读数据，只能阻塞等待。
2.如果当前表没有加排斥锁，那么就需要对每一行数据进行判断，判断是否加了行级别的X锁，如果加了只能阻塞等待，这样需要对一行进行判断，性能开销太大了。
所以才有了意向锁，在获得表级别的只读锁之前，需要执行的步骤是：
1.第一步还是跟上面的步骤一一样
2.第二步只需要判断当前锁是否加了表级别的意向排斥锁，因为如果加了意向排斥锁，说明正在有事务在对数据加行锁，对数据进行更新，这样避免了对每一行数据进行判断，判断是否加了行锁。
Innodb的锁
行锁
共享锁: S锁，就是读锁，允许事务读一行数据，不能被修改。所以读锁之间不排斥
互斥锁: X锁，就是写锁，就是让当前事务可以修改这行数据，其他事务不能修改这行数据
记录锁record lock:
记录锁定是对单条索引记录的锁定。例如， SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 可以防止从插入，更新或删除行。
间隙锁 gap lock:
间隙锁就会对记录之间的间隙加锁，防止数据插入。就是在使用实时读(SELECT FOR … UPDATE)或者更新，为了防止读的过程中有新的数据插入，会对读的数据的左右区间进行加锁，防止其他事务插入数据，所以间隙锁之间是不排斥的，间隙锁排斥的只是插入数据的操作。
下一键锁 next-key lock:
next-key lock就是会锁记录以及记录之间的间隙，就是 record lock 和 gap lock的组合，就是会对索引记录加记录锁 + 索引记录前面间隙上的锁”，就是对要更新的数据的左右两个端点加间隙锁，
例如num是一个普通索引，非唯一性索引，已有数据是1，5，10，20，30
那么 next-key lock可以锁定的区间是
(负无穷,1] (1,5] (5,10] (10,20] (20,30] (30,正无穷)
//更新操作
update table set note = '1' where num = 10;
//或者是使用实时读
SELECT * FROM table WHERE num = 10 for UPDATE;
如果num是唯一性索引，那么只需要对num为10的这条索引加锁就行了，因为不用担心其他事务再插入一条num为10的数据，因为会有唯一性判断。但是如果num是非唯一性索引，为了防止事务执行过程中有num为10的数据插入，那么会对（5，10]和（10，20]这两个区间加锁。
乐观锁&&悲观锁
区别
乐观锁不会上锁，只是在执行更新的时候判断一下在此期间别人是否修改了数据：如果别人修改了数据则放弃操作，否则执行操作。 操作数据时直接把数据锁住，直到操作完成后才会释放锁；上锁期间其他人不能修改数据
实现方式
乐观锁和悲观锁是两种思想，它们的使用是非常广泛的，不局限于某种编程语言或数据库。
悲观锁：加锁，加锁既可以是对代码块加锁，也可以是对数据加锁（如MySQL中的排它锁）
乐观锁：CAS机制和版本号机制
1.CAS（Compare And Swap）:
CAS操作包括了3个操作数：
需要读写的内存位置(V)
进行比较的预期值(A)
拟写入的新值(B)
如果内存位置V的值等于预期的A值，则将该位置更新为新值B，否则不进行任何操作。
2.版本号机制:
在数据中增加一个字段version，表示该数据的版本号，每当数据被修改，版本号加1。 当某个线程查询数据时，将该数据的版本号一起查出来；当该线程更新数据时，判断当前版本号与之前读取的版本号是否一致，如果一致才进行操作。
使用
乐观锁适用的场景受到了更多的限制，无论是CAS还是版本号机制： CAS只能保证单个变量操作的原子性，当涉及到多个变量时，CAS是无能为力的，而synchronized则可以通过对整个代码块加锁来处理。再比如版本号机制，如果query的时候是针对表1，而update的时候是针对表2，也很难通过简单的版本号来实现乐观锁。
当竞争不激烈 (出现并发冲突的概率小)时，乐观锁更有优势，因为悲观锁会锁住代码块或数据，其他线程无法同时访问，影响并发，而且加锁和释放锁都需要消耗额外的资源。 当竞争激烈(出现并发冲突的概率大)时，悲观锁更有优势，因为乐观锁在执行更新时频繁失败，需要不断重试，浪费CPU资源。
乐观锁加锁吗？
1.乐观锁本身是不加锁的，只是在更新时判断一下数据是否被其他线程更新了
2.有时乐观锁可能与加锁操作合作
CAS有哪些缺点？
1.ABA问题
假设有两个线程——线程1和线程2，两个线程按照顺序进行以下操作：
(1) 线程1读取内存中数据为A；
(2) 线程2将该数据修改为B；
(3) 线程2将该数据修改为A；
(4) 线程1对数据进行CAS操作
在第(4)步中，由于内存中数据仍然为A，因此CAS操作成功，但实际上该数据已经被线程2修改过了
解决：对于ABA问题，比较有效的方案是引入版本号，内存中的值每发生一次变化，版本号都+1；在进行CAS操作时，不仅比较内存中的值，也会比较版本号，只有当二者都没有变化时，CAS才能执行成功。
2.高竞争下的开销问题
在并发冲突概率大的高竞争环境下，如果CAS一直失败，会一直重试，CPU开销较大。针对这个问题的一个思路是引入退出机制，如重试次数超过一定阈值后失败退出。 当然，更重要的是避免在高竞争环境下使用乐观锁。