先介绍ON DUPLICATE KEY UPDATE
插入时存在就更新，不存在就插入。应设置主键或者唯一索引情况下。
此时我们需要的就是如果存在则更新,如果不存在则新增. 在MySQL中也有这样的功能.INSERT 中ON DUPLICATE KEY UPDATE
INSERT 中ON DUPLICATE KEY UPDATE的使用（本文重点）
如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。例如，如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果：
mysql>INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; （存在a=1 就更新 条件 c=c+1）
mysql>UPDATE table SET c=c+1 WHERE a=1;
如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。
注释：如果列b也是唯一列，则INSERT与此UPDATE语句相当：
mysql>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2与多个行相匹配，则只有一个行被更新。通常，您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。
您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT...UPDATE语句的INSERT部分引用列值。换句话说，如果没有发生重复关键字冲突，则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT...UPDATE语句中有意义，其它时候会返回NULL。
示例：
mysql>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
->ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
本语句与以下两个语句作用相同：
mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)
->ON DUPLICATE KEY UPDATE c=3;
mysql>INSERT INTO table (a,b,c) VALUES (4,5,6)
->ON DUPLICATE KEY UPDATE c=9;
SELECT…FROM
✓ 一致性读不加锁
✓ 在SERIALIZABLE隔离级别下，对于范围查找加next-key锁，对于唯一索引加记录锁
SELECT…LOCK IN SHARE MODE
✓ 加S锁，具体是record lock、gap lock或者next-key lock，依赖索引情况以及事务隔离级别等
SELECT … FOR UPDATE
✓ 加X锁，具体是record lock、gap lock或者next-key lock，依赖索引情况以及事务隔离级别等
DELETE…WHERE
✓ 加X锁，具体是record lock还是next-key lock，依赖索引情况以及事务隔离级别等
UPDATE…WHERE
✓ 加X锁，具体是record lock还是next-key lock，依赖索引情况以及事务隔离级别等
✓ 更新主键的时候，会对影响的二级唯一索引加上对应的记录S锁和X锁
AUTO_INCREMENT
✓ AUTO-INC锁
✓ 表级锁
FOREGIN KEY
✓ 对涉及的外键记录加S记录锁
LOCK TABLES
✓ 表锁
✓ 是MySQL server层，而不是Innodb引擎层的
INSERT
✓ 对插入行加X锁
✓ 如果存在唯一键冲突，会对这些唯一键的记录加S锁
INSERT…ON DUPLICATE KEY UPDATE
✓ 对存在的行加next-key Lock
✓ 对主键重复加X锁
✓ 对需要更新的数据加X锁
REPLACE
✓ 和INSERT相似
✓ 如果有唯一键冲突，会对这些唯一键的记录加X锁
INSERT INTO t SELECT … FROM s WHERE …
✓ 对t中的行记录加X记录锁
✓ 在RC模式下，一致性读不加锁
✓ 在RR模式下，加shared next-key 锁
Where条件在数据库中提取规则-三个关键点
• Index Key （First Key&Last Key）
对于确定SQL查询在索引中的连续范围（起始范围+结束范围）的查询条件。
• Index Filter
在完成Index Key提取之后，根据where条件固定索引的查询范围，但是此范围中的选项，并不都是满足查询条件的项。
• Table Filter
所有不属于索引列的查询条件，均归为Table Filter之中。
Index First Key
select * from t1 where b >= 2 and b < 8 and c > 1 and d != 4 and e != ‘a’
idx_t1_bcd (b, c, d)
✤ 对于确定索引查询的起始范围
✤ 提取规则：从索引的第一键值开始，检查其在where条件中是否存在，若
存在并且条件是=、>=则将对应的条件加入Index First Key之中，继续读
取索引的下一个键值
✤ 若存在并且条件是>，则将对应的条件加⼊到Index First Key中，同时终
止Index First Key的提取。
✤ 例子的Index First Key为（b>=2,c>1），由于c>1，提取结束，不包括d
Index Last Key
✤ 对于确定索引查询的终止范围
✤ 提取规则：从索引的第一键值开始，检查其在where条件中是否存在，
若存在并且条件是=、<=则将对应的条件加⼊Index Last Key之中，继
续读取索引的下一个键值
✤ 若存在并且条件是<，则将对应的条件加入到Index First Key中，同时
终止Index Last Key的提取。
✤ 例子的Index Last Key为（b<8），提取结束，不包括c和d
Index Filter
✤ Index Key提取之后，根据where条件固定索引的查询范围之后，但是
此范围中的项，并不都是满足查询的
✤ 提取规则：从索引第一列开始，若存在并且where条件仅为=，则跳过
继续检查下列，若为其他，则将where条件中索引相关全部加入到
Index Filter之中。（索引的前缀匹配规则）
✤ 示例：索引第一列只包含>=、<两个条件，因此第一列可跳过，将余下
c、d两列加入Index Filter中。此案例Index Filter为c>1 and d!=4
Table Filter
select * from t1 where b >= 2 and b < 8 and c > 1 and d != 4 and e != ‘a’
idx_t1_bcd (b, c, d)
✤ 所有不属于索引列查询条件，均归为Table Filter
✤ 示例：e != ‘a’ 为Table Filter
事务隔离级别
❖ Read Uncommitted
✓ 可以读取未提交的事务，此隔离级别不会使用。
❖ Read committed (RC)
✓ 针对当前读，RC隔离级别保证对读取到的记录加锁（行锁），存在幻读现象。
❖ Repeatable Read(RR)
✓ 针对当前读，RR保证对读取到的记录加锁（行锁），同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入（Next-Key Locks），不存在幻读现象。
❖ Serializable
✓ 从MVCC并发控制退化为基于锁的并发控制。所有的读操作都为当前读，读加读锁（S锁），写加写锁（X锁）。Serializable隔离级别下，读写冲突，并发度急剧下降。
快照读和当前读
❖ 快照读：简单select操作，不加锁
❖ select * from table where ?
❖ 当前读: 特殊读操作，插入/更新/删除，需要加锁
❖ select * from table where ? lock in share mode
❖ select * from table where ? for update
❖ insert into table values (…)
❖ delete from table where ?
❖ update table set ? where ?
网友评论