mysql笔记

1.MYSQL基础

1.1事务的基本要素（ACID）

原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，出错会全部回滚
一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏
隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰
持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚

1.2事务的并发问题

赃读：事务A更新了数据，事务B读了更新后的数据，事务A出错后滚，此时事务B就读取了脏数据
不可重复读：事务A多次读取同一数据，事务B作了更新并提交，导致事务A读取同一数据时，结果不一样
幻读：简单的来说select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。注意一点幻读只存在于数据的插入。

1.3数据库隔离级别

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

  RU :在未提交读级别，事务中的修改，即使没有提交，对其他事务也都是可见的，三种问题都会出现。

  RC :大多数数据库系统的默认隔离级别都是提交读（但Mysql不是）。定义：一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。

RR : Mysql默认隔离级别，定义：可重复读解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。跟读已提交有本质的区别：

　　　　leg: 现有两个事物A ,B。

　　　　　　

允许顺序 A事务 B事务

第一步
select name from A where id = 10

结果是： name = zero

第二步
update A set name=jo where id = 10

事务B: commit;

第三步
select name from A where id = 10

结果是 name = zero

事务A commit;

再次

select name from A where id = 10

结果是 name = jo

　　　　上述可以看出来：跟读已提交有本质的区别，至于原因是MVCC(多版本控制，这个很有意思)

   可串行化（serializable）：这个基本不会用，它通过强制事务串行执行，避免了前面所说的幻读问题。可串行化会在读取的每一行数据上都加上锁，所以可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑用该级别。当使用这个时候，导致mysql的性能太差。

允许顺序	A事务	B事务
第一步	select name from A where id = 10 结果是： name = zero
第二步		update A set name=jo where id = 10 事务B: commit;
第三步	select name from A where id = 10 结果是 name = zero 事务A commit; 再次 select name from A where id = 10 结果是 name = jo

2.MVCC(多版本控制)

　　上面mysql事务隔离级别是: RR(可重复读)的时候，到底是怎么解决脏数据，和不可重复读的呢？

需要知道这个问题根本，就必须知道什么叫做：MVCC(多版本控制)。

MVCC: Multiversion concurrency control，多版本并发控制，提供并发访问数据库时，对事务内读取的到的内存(及快照)做处理，用来避免写操作堵塞读操作的并发问题。MVCC提供了 时间一致性的处理思路，在MVCC下读事务时，通常使用一个时间戳或者事务ID来确定访问哪个状态的数据库及哪些版本的数据。读事务跟写事务彼此是隔离开来的，彼此之间不会影响。假设同一份数据，既有读事务访问，又有写事务操作，实际上，写事务会新建一个新的数据版本，而读事务访问的是旧的数据版本，直到写事务提交，读事务才会访问到这个新的数据版本。

假设当前有两个事物：A 和 B ，假设tabA 中有2条数据【id,name】 [1，jo] [10,zero]

事务A 事务ID 事务B 事务ID

在同一时刻访问同一个数据库和表（tabA） select name from tabA where id = 10 V1 select name from tabA where id = 10 V2

这个时刻，对于事务A和B而言，看到的表（tabA）是相同的，且会在内存中生成快照。假设快照分别为:V1_Photo ，V2_Photo。V1_Photo 和V2_Photo里面的数据是一样，注意是：同一时刻。

这样就可以满足事物A看到的数据id=10永远来至V1_Photo中数据了。即使事物B对id=10的时候数据修改了也没有关系。

总结：是不是可以完美的解决脏数据的问题，和可重复读的问题，谁叫别人读的是时刻快照呢

MVCC可以认为是行级锁的一个变种，它可以在很多情况下避免加锁操作，因此开销更低。MVCC的实现大都都实现了非阻塞的读操作，写操作也只锁定必要的行。InnoDB的MVCC实现，是通过保存数据在某个时间点的快照来实现的。一个事务，不管其执行多长时间，其内部看到的数据是一致的。也就是事务在执行的过程中不会相互影响。下面我们简述一下MVCC在InnoDB中的实现。

　　InnoDB的MVCC，通过在每行记录后面保存两个隐藏的列来实现：一个保存了行的创建时间，一个保存行的过期时间（删除时间），当然，这里的时间并不是时间戳，而是系统版本号，每开始一个新的事务，系统版本号就会递增。在RR隔离级别下，MVCC的操作如下：

select操作。
- InnoDB只查找版本早于（包含等于）当前事务版本的数据行。可以确保事务读取的行，要么是事务开始前就已存在，或者事务自身插入或修改的记录。
- 行的删除版本要么未定义，要么大于当前事务版本号。可以确保事务读取的行，在事务开始之前未删除。
insert操作。将新插入的行保存当前版本号为行版本号。
delete操作。将删除的行保存当前版本号为删除标识。
update操作。变为insert和delete操作的组合，insert的行保存当前版本号为行版本号，delete则保存当前版本号到原来的行作为删除标识。

　　由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge

RR(可重复读)的时候,出现了幻读怎么办？

答案不言而喻：加锁加行锁。注意一点锁是针对当前事务的，对当前事务而言，加了行锁也是可以做写操作的。对其他的事务而言，需要等待这个事务完成，将锁资源释放。因此会造成一定是阻塞。

注意项：

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。
在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。
行级锁分为排它锁（写锁）、共享锁（读锁）、间隙锁。

简单的行锁：

select * from tabA where id =10 for update

　　RR(可重复读)的时候，为啥出现幻读？

MVCC快照产生的，对事务A和B而言，事务A是看不到事务B的快照的，所以当事务A和B都提交插入的时候，id是唯一索引，不能插入两个一模一样的，导致出现插入失败问题。

3.sql优化

sql优化：一般是指对索引的使用。下面是引用别人的文章,和自己一点点总结：

1、查询谓词没有使用索引的主要边界,换句话说就是select *，可能会导致不走索引。

比如，你查询的是SELECT * FROM T WHERE Y=XXX;假如你的T表上有一个包含Y值的组合索引，但是优化器会认为需要一行行的扫描会更有效，这个时候，优化器可能会选择TABLE ACCESS FULL，但是如果换成了SELECT Y FROM T WHERE Y = XXX，优化器会直接去索引中找到Y的值，因为从B树中就可以找到相应的值。

2、单键值的b树索引列上存在null值，导致COUNT(*)不能走索引。

如果在B树索引中有一个空值，那么查询诸如SELECT COUNT(*) FROM T 的时候，因为HASHSET中不能存储空值的，所以优化器不会走索引，有两种方式可以让索引有效，一种是SELECT COUNT(*) FROM T WHERE XXX IS NOT NULL或者把这个列的属性改为not null (不能为空)。

3、索引列上有函数运算，导致不走索引

如果在T表上有一个索引Y，但是你的查询语句是这样子SELECT * FROM T WHERE FUN(Y) = XXX。这个时候索引也不会被用到，因为你要查询的列中所有的行都需要被计算一遍，因此，如果要让这种sql语句的效率提高的话，在这个表上建立一个基于函数的索引，比如CREATE INDEX IDX FUNT ON T(FUN(Y));这种方式，等于Oracle会建立一个存储所有函数计算结果的值，再进行查询的时候就不需要进行计算了，因为很多函数存在不同返回值，因此必须标明这个函数是有固定返回值的。

4、隐式转换导致不走索引。

索引不适用于隐式转换的情况，比如你的SELECT * FROM T WHERE Y = 5 在Y上面有一个索引，但是Y列是VARCHAR2的，那么Oracle会将上面的5进行一个隐式的转换，SELECT * FROM T WHERE TO_NUMBER(Y) = 5,这个时候也是有可能用不到索引的。

5、表的数据库小或者需要选择大部分数据，不走索引。

leg: 枚举字段，建议不用加上。这个是优化器认为全表扫描比较快，原因是枚举字段就只有几个值，例如 is_deleted （1 或者 0），索引意义不大。

6、cbo优化器下统计信息不准确，导致不走索引

很长时间没有做表分析，或者重新收集表状态信息了，在数据字典中，表的统计信息是不准确的，这个情况下，可能会使用错误的索引，这个效率可能也是比较低的。

7、！=或者<>(不等于），可能导致不走索引，也可能走 INDEX FAST FULL SCAN

例如select id from test where id<>100，这原因是当表的数据过大时，优化器认为全表扫描比走索引更加快捷。

8、表字段的属性导致不走索引，字符型的索引列会导致优化器认为需要扫描索引大部分数据且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式，

由于字符型和数值型的在insert的时候排序不同，字符类型导致了聚簇因子很大，原因是插入顺序与排序顺序不同。详细点说，就是按照数字类型插入（1..3200000），按字符类型（'1'...'32000000'）t排序，在对字符类型使用大于运算符时，会导致优化器认为需要扫描索引大部分数据且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式。简单的来说就是就是：对字段为数字会走索引，但是对为字符串的可能不会。

9.建立组合索引，但查询谓词并未使用组合索引的第一列，此处有一个INDEX SKIP SCAN概念,这个是mysql的最左原则决定的。

10、like '%liu' 百分号在前，这个是mysql的最左原则决定的。如果这样写的话，就会走索引（like 'liu%' 百分号在后面），原因是mysql的索引数结构决定的，原理很简单，就不解释了。

11，not in ,not exist

可以尝试把not in 或者 not exsts改成左连接的方式（前提是有子查询，并且子查询有where条件）

12.开放区间扫描（>= 和 <= ），封闭区间（between xxx and max_value）,都是走索引的。

13.在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用。

4.MySQL日志系统：redo log、binlog、undo log 区别与作用

简单总结一下,参考链接里面有：

redo log:是InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻（crash-safe），以此来保证数据的完整性。他的关键点是先写日志，再写磁盘。大小是固定的，即记录满了以后就从头循环写。作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

binlog: binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有crash-safe能力的。追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。作用：binlog可以作为恢复数据使用，主从复制搭建。

undo log: 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

5.常识问题

回表：指的是：当二级索引去搜索的时候，得到主键ID(简单的理解一下)，在通过主键索引（聚集索引）在查询一次，这个过程就是回表。

覆盖索引：当我们去搜索一个二级索引字段时（leg: name字段），如果select name from 表 where name = “zero”，这个时候会产生索引覆盖。可以直接使用索引查询而不需要回表。常用的sql优化方案。

索引下推：简单的说：就是回表时，从二级索引到主键索引，这个过程叫做索引下推。通过二级索引过滤，主键索引搜索次数就会减少，mysql默认启用索引下推，我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制：

SET optimizer_switch = 'index_condition_pushdown=off';

6.间隙锁

数据库表结构：

id（主键索引） name（2级索引）

1 5

10 15

20 20

假设执行顺序是：

事务V1 处理逻辑:

select * from tab_A where id = 14 for update; 第1步

insert tab_A value(14,20); 第4步

事务V2 处理逻辑:

select * from tab_A where id = 15; 第2步

insert tab_A value(14,10); 第3步

当执行1步的时候，mysql会将id [10,20]这个区间(及间隙)锁住，不让其他事务做写操作，必须等待当前事务V1结束后，才能执行V2的写操作。但是不是说：第4步比第3步先执行。实际测试中还是那个事务先执行完成（不是说事务提交完成）谁先写入。V2事务确实会等待V1事务结束，V2写才会生效。

如上：我让V1先执行第一步，然后在执行V2全部事务，此时V2会出现阻塞，直到V1事务结束，V2也是完成了，V2会一直等待V1的Commit。

但是V1会爆出：name（二级索引）= 20 已经存在。V2会正常插入成功。

所以，千万不要以为mysql在执行过程中会现将第4步提前。

还有一个点如果select * from tab_A where id = 14 for update; 变成select * from tab_A where id = 10 for update;间隙锁的范围是也是[10,20],这个需要理解间隙二字。

　　如果间隙锁加到了2级索引上会怎么样？

假设执行顺序是：

事务V1 处理逻辑:

select * from tab_A where name = 5 for update; 第1步

insert tab_A value(5,20); 第4步

事务V2 处理逻辑:

select * from tab_A where id = 5; 第2步

insert tab_A value(15,12); 第3步

事务V3 处理逻辑:

insert tab_A value(5,20); 第4步