MySQL 一些基础知识

聚簇索引与非聚簇索引区别与使用场景

聚集索引和非聚集索引的根本区别是表中记录的物理顺序和索引的排列顺序是否一致。

• 聚簇索引的表记录的物理顺序与索引的排列顺序一致
  • 优点
    • 查询速度较快,第一个索引值被查询到，具有连续索引值的记录也一定物理的紧跟其后。
  • 缺点
    • 对表记录修改速度较慢，为了保证表中记录的物理顺序与逻辑顺序一致，而把数据插入至数据页相应的位置，必须在数据页进行数据重排，降低了执行速度。在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效。

所以建议使用聚簇索引的场合为：

• 某列包含了小数目的不同值。
• 排序和范围查找

其它方面的区别：

• 聚集索引和非聚集索引都采用了 B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点。
• 由于行数据和叶子节点存储在一起， 这样主键和行数据是一起被载入内存的， 找到叶子节点就可以立刻将行数据返回了， 如果按照主键 Id 来组织数据， 获得数据更快。
• 辅助索引使用主键作为"指针", 而不是使用地址值作为指针的好处是， 减少了当出现行移动或者数据页分裂时,辅助索引的维护工作， InnoDB 在移动行时无须更新辅助索引中的这个"指针"。 也就是说行的位置会随着数据库里数据的修改而发生变化， 使用聚簇索引就可以保证不管这个主键 B+树的节点如何变化， 辅助索引树都不受影响。

所以使用非聚簇索引的场合为：

• 此列包含了大数目的不同值；
• 频繁更新的列

Explain 关键字

标识	说明
id	选择标识符
select_type	表示查询的类型
table	输出结果集的表
partitions	匹配的分区
type	表示表的连接类型
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
ref	列与索引的比较
rows	扫描出的行数(估算的行数)
filtered	按表条件过滤的行百分比
Extra	执行情况的描述和说明

• select_type

1. SIMPLE(简单SELECT，不使用UNION或子查询等)
2. PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
3. UNION(UNION中的第二个或后面的SELECT语句)
4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
5. UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
6. SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
7. DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
8. DERIVED(派生表的SELECT, FROM子句的子查询)
9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

• type
  ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

• key_len
  表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
  不损失精确性的情况下，长度越短越好

• rows
  估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

MVCC（多版本并发控制）

MySQL的innodb采用的是行锁，而且采用了多版本并发控制来提高读操作的性能，MVCC只在REPEATABLE READ和READ COMMITED两个隔离级别下工作
MVCC做法就是子啊每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号。
1.在插入操作是：记录的创建版本号就是事务的版本号
假设现在我插入一条数据，事务id为1，那么初始记录值如下

2.在更新数据时做法是先将就数据标记为删除，再输入一条新的数据。（旧数据的删除版本就是更新数据时的事务id）

3.删除操作，把事务版本号作为删除版本号

4.查询操作
在查询时要符合以下两个条件的记录才能被事务查询出来：
1.InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，只么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
2.行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
这样就能保证各个事务互不影响，这样有点乐观锁的意思。

共享锁/排他锁

共享锁（share lock 也称S锁）

共享锁表示多个事务可以对同一个数据集加锁，这时其它事务也可以对该数据集进行读取操作并且继续加S锁，但此时并不能对加了共享锁的数据进行修改操作（直到事务执行完成锁释放）
产生共享锁场景
select * from “user” lock in share mode
那么什么时候使用共享锁呢？
比较适用于两表存在关系时的写操作一个表是child表，一个是parent表，假设child表的某一列child_id映射到parent表的c_child_id列，那么从业务角度讲，此时我直接insert一条child_id=100记录到child表是存在风险的，因为刚insert的时候可能在parent表里删除了这条c_child_id=100的记录，那么业务数据就存在不一致的风险。正确的方法是再插入时执行select * from parent where c_child_id=100 lock in share mode,锁定了parent表的这条记录，然后执行insert into child(child_id) values (100)就不会存在这种问题了。

排他锁（Exclusive Lock，也称X锁）

排他锁也叫写锁（X）
排他锁表示对数据进行写操作。如果一个事务对对象加了排他锁，那么其它事务就不能继续对这个对象加锁了。
产生排他锁的场景：
select * from ad_plan for update;
排它锁的使用场景：
订单商品数量
电商系统中计算一种商品的剩余数量，在产生订单之前需要确认商品数量>=1,产生订单之后应该将商品数量减1。
1 select amount from product where product_name=‘XX’;
2 update product set amount=amount-1 where product_name=‘XX’;
显然1的做法是是有问题，因为如果1查询出amount为1，但是这时正好其他session也买了该商品并产生了订单，那么amount就变成了0，那么这时第二步再执行就有问题。那么采用lock in share mode可行吗，也是不合理的，因为两个session同时锁定该行记录时，这时两个session再update时必然会产生死锁导致事务回滚。

间隙锁

推荐链接

事务

事务基本要素

1. 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
2. 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
3. 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
4. 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读-读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

mysql 默认为可重复读

对幻读的解释

几种常见Log

Undo Log

数据库事务开始之前，会将要修改的记录存放到Undo日志里，当事务回滚时或者数据库崩溃时，可以利用Undo日志，撤销未提交事务对数据库产生的影响。

Undo Log产生和销毁：Undo Log在事务开始前产生；事务在提交时，并不会立刻删除undo
log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进
行回收处理。Undo Log属于逻辑日志，记录一个变化过程。例如执行一个delete，undolog会记
录一个insert；执行一个update，undolog会记录一个相反的update。

Undo Log 作用：

• 实现事务的原子性
  Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执
  行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。
• 实现多版本并发控制（MVCC）
  Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log
  保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快
  照读。

Redo Log

指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做
日志。
Redo Log 的生成和释放 : 随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生
Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入
到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）。

BinLog 日志

Redo Log 是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary
log（二进制日志），简称Binlog。Binlog是记录所有数据库表结构变更以及表数据修改的二进制
日志，不会记录SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的
消耗时间。开启Binlog日志有以下两个最重要的使用场景：

• 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到
  Binlog后实现数据恢复达到主从数据一致性。
• 数据恢复：通过mysqlbinlog工具来恢复数据。