1.SQL查询操作
select的“另类”用法
我们通常习惯select + from从数据表中读取数据，不过实际上select并不一定要去读取数据库中的内容。
比如：
select 1+1; 返回2
select now(); 返回当前时间
select本身代表要返回的内容，至于与数据库表中存的数据是否有关并不重要。
同时MySQL支持基本四则运算，所以可以利用这两个特性来实现：
统计A队列的案件数量比B队列的案件数量差异：
select
(select count(id) from assignment where queue='A' and `status`='ACTIVE' and active=1)
-
(select count(id) from assignment where queue='B' and `status`='ACTIVE' and active=1);
where和having
where和having都有对数据进行筛选的功能，通常的使用习惯是where跟在from后面，而having跟在group by后面，那么是不是非得这么用呢？二者的区别在哪里呢？
where表示基于数据表的列参数的限制条件，即where a=b语句中的a必须是数据表中的列。
having表示基于select语句返回的变量的限制条件，即having a=b语句中的a必须在select中出现。
来看下面几种场景：
select * from table where a=XXX;
select * from table having a=XXX;
// 二者均可使用，效果一样
select a,b,c from table where d=xxx; // ok
select a,b,c from table having d=xxx; // 错误，d不在select选择之中，无法用来having
select a, count(b) from table where count(b)>100; // 错误，where只能跟表中存在的列，无法对聚合、函数操作后的变量进行筛选
select a, count(b) from table group by a having count(b)>100; // ok
distinct和group by
distinct()函数和group by 语句都有用来去重的效果，但对应的使用场景有些差异。
distinct的作用就是单纯的去重，必须紧跟着select的后面，否则会报错。当select中涉及的到所有元素都重复时，只返回1条记录。有时和count连用，统计不重复的数量。
select distinct(a) from table; // 返回1，2，3，5，6，7，9...
select distinct(a),b from table; // 返回 (1,2),(1,3),(2,1),(3,5),(3,6)....
select a,distinct(b) from table; // error
select count(distinct(a)) from table; // 统计a不重复的记录数量
group by的作用是做分组汇总统计，必须配合聚合函数（count、sum、avg等）使用，否则无意义。返回结果中，被group by的参数每个只有1行。
select avg(b) from table group by a //对每一个a的条件查询b的平均值
select count(distinct(c)) from table group by a //对每个a的分组统计不同的c列值的数量
关联查询
当from语句选择了2张或以上的表时，返回内容是两张表的笛卡尔积，总数是二者数据量的乘积！
因此为了避免数据量的爆炸，关联查询多表时必须要加联结条件。
基本句式是：
select a ·联结方式· b on ·联结条件·
联结方式有内联结、外联结、左联结、右联结等，详见下图：
修改操作涉及自身查询
有时候在做update、delete操作时需要结合select子句确定修改的范围。当select子句查询涉及要修改的表格本身时，要特别注意：不能select出数据，再在同一个表中做update、delete操作。
来看一个例子：a表中现在出现了b列同一个值有多条ACTIVE的记录的错误情况，现在要将这些记录全部设为EXPIRE。
下面是错误的示范：
UPDATE a SET `status` = 'EXPIRE'
WHERE b IN (
SELECT b FROM a
WHERE `status` = 'ACTIVE'
GROUP BY b
HAVING count(*) > 1
);
执行报错： You can't specify target table 'a' for update in FROM clause
要正常实现该功能，需要在update和select操作直接加一层临时表，临时表里存放从原表select出来的数据，update原表时，子查询读取临时表的数据，从而避免报错。
正确写法：
UPDATE a SET `status` = 'EXPIRE'
WHERE b IN (
SELECT temp.b from (
SELECT b from a
WHERE `status` = 'ACTIVE'
GROUP BY b
HAVING count(*) > 1
) as temp
);
这里从a表中查询出来的b全部放进了temp表中，update的时候IN语句读取temp表中的值，避免了两个操作指向同一张表产生报错。
2.InnoDB引擎和索引的技巧
InnoDB引擎的b+树索引
如果留心过数据库表信息的话肯定能发现，我们用的数据库默认都是InnoDB引擎的。MySQL建立数据库表时，除非显示声明，否则建立的都是InnoDB引擎。InnoDB的优势在于事务安全性和更细粒度的行级锁，详见下一章。
InnoDB引擎的索引机制是b+树，简称b树索引。至于什么是b+树，请自行百度。
.
.
.
算了，还是讲下吧。。。。
查询一般有三类：顺序查询（慢）、二分查询（快）和hash查询（最快，但是费空间，除了内存机制的应用，一般不会使用）。顺序查询和二分查询有着数量级的性能差距，对于数据量大的情况差异尤其明显。
为了更快的定位查询到需要的数据，就要想办法把慢的顺序查询变为快的二分查询，这就是索引的本质。
InnoDB中的索引分为主键索引（聚簇索引）和二级索引，其中主键索引和其他数据是存放在一起的，二级索引有单独的索引树。
先来看主键索引。
为了实现二分查询，MySQL规定，主键索引必须唯一并按照升序排列（否则没法二分）。
数据存储的的基本单位是“页”，一页大小为16k，为连续的存储空间。
连续就好办了，由于规定了主键唯一升序，要在一页内查找某个主键，就可以先读取中间地址（更准确的说法是槽位）存储的数据，拿主键来比较，如果比中间的主键值大，那在后半空间里再去一半位置地址的数据比较，以此类推。
当一页存不下的时候，就需要开一个新的页，但页和页之间的地址未必是连续的，二者通过双向链表关联。所以当数据量大，不止一页的时候如何实现二分呢？
答案是建立一个目录页，目录页和存储数据的页结构上其实是一样一样的，存储的内容是主键和其对应的页的地址。这样查询的过程变成：先在目录页中二分查找主键，找到对应数据页的地址，进入数据页再次进行二分查找。
若数据量很大，目录页也不止一个怎么办？那就再建立一个指向目录页的二级目录页。
整个过程就像是根据页码查目录，先搜索章节、再搜索小节、再搜索小小节。
详见下图：
比如我要搜id=26的数据，首先<62找到目录页17，然后<50找到数据页37，在页37中最后二分法找到26的记录。
这个形状不就是树结构吗？来看一下这个树有什么特点：
- 首先，所有的具体数据都存在叶子节点
- 非叶子节点只存储Key值和通往下一级节点的地址
我们管具有这种特点的树叫做b+树。InnoDB引擎通过b+树实现索引的二分查询，从而提升查询性能。
那手动建立的其他索引呢？
答案是，每建立一个索引，就建一颗对应的b+树，并按照该索引字段升序排列（MySQL不止是数值可以排序，任何类型的值都可以，比如字符串是根据字母排列顺序来排序）。
在按索引查询时，就在对应的索引树上进行二分查询操作，和主键树一样。
除主键外的所有索引都是二级索引，其b+树和主键b+树唯一的区别在于叶子节点：主键树的叶子节点存放主键和具体记录的数据；二级索引树的叶子节点存放索引和主键。
还有一种比较特别的二级索引，里面包括多个列的值，称为联合索引。一个联合索引也是一个b+数，内部的顺序先按照建立索引的第一个字段排序，相同情况下再按照第二个字段排序，以此类推。
二级索引是怎么查询数据的呢？首先通过二级索引树二分查询找到对应的主键id，再拿这些主键id去主键索引树上查询对应的数据，这个过程称为“回表”。
所以，根据主键id查数据走一次索引，根据普通索引查数据要走两次索引。
如何使用索引
索引将顺序查询升级为二分查询，使得性能可以大大提升，但索引也不是万能的，要用索引之前首先得知道使用的代价：
- 每建一个索引都要建一个b+树，占空间是肯定的了，如果索引的值很大的话，更容易产生分页现象，b+树就需要更多的层，占空间就更大；
- 不能“管杀不管埋”，建了索引树还得维护，当表内插入、变更、删除数据时，对应的索引树可能也需要同时更新，这样会拖慢数据库操作的性能；
- 对于二级索引的使用，可能查出多个主键id，而这些主键id未必是连续的，所以回表查询时是随机I/O，比顺序I/O的性能会差很多，可能得不偿失；
所以，一个表上建的索引越多，占用空间就越大，增删改记录时的时间性能就越差。因此要建立索引，就要保证其能被充分的利用。
网上对于什么情况下可以命中索引，什么情况下不行的列举较为零碎，不好理解，但事实上只要理解了索引的b+树结构原理，判断能否命中索引其实不难。
先来看几个这辈子也别想命中索引的操作：
select * from table where a!=xxx // !=, not in 这种操作显然是没法通过二分查找来快速定位的
select * from table where a*2=xxx // 对索引变量做运算会导致mysql把a*2作为整体来筛选，无法使用b+树，函数处理同理，所以对于字符串类型的变量保存数字的列要特别小心。
select * from table order by a // 排序本身不是问题，问题在于没有where子句，是全部数据的排序，走索引只会增加回表操作开销，不如全表搜索。
select * from table order by a, b desc limit 10 // 如果有多个字段做排序，排序的方式需要一样，才能方便的从b+树上正着取或者倒着取数据。
select * from table where b like '%xxx%' // 字符串按照字母顺序来排序，前缀做通配会导致无法判断其大小范围。
联合索引（a,b,c）的情况：
select * from table where b=xxx // 联合索引按建立索引的顺序在b+树上进行升序排列，所以不使用a的情况下直接用b，由于a的值不确定，无法命中
select * from table where a=xxx and c=xxx // 只能命中索引a，原理同上
select * from table where a>xxx and b=xxx // 还是只能命中索引a，因为a进行的是范围操作，会筛选出多个值，无法在同一颗索引树上方便的匹配第二个字段，系统会把b字段的匹配变为顺序搜索
理解了索引基于b+树的搜索方式，就比较容易找正确的使用姿势了：
- 表本身的数据量要大，否则二分查询节约的时间不如回表操作费的时间；
- 索引尽量小，节约b+树空间和搜索层次数，降低维护成本；
- 索引的取值尽可能多（唯一就更好了），避免索引树上查到大量主键id，在回表过程中浪费时间；
- 基于二分查询的原理，表达式应该是等值匹配、IN匹配、大小判断或者用于排序、分组操作，如果是字符串匹配，前缀要准确；
- 索引列要单独出现，不要做任何算术、函数运算；
- 若有多个字段会同时使用，可以建立联合索引，一是减少b+树的数量节约空间；二是因为一次查询只会用一个索引树，联合索引效率更高；
- 对于联合索引，建立时要按照使用频率顺序来建立，避免出现前一个未使用而使用后一个的情况；
- 排序情况下一定要限制数量或者条件，不用全表排序；
- 如果可以，只搜索索引字段，避免select *，索引覆盖的情况下只需要搜索索引树，免去了回表操作；
- 对于IN操作，可以拆解成多个等值匹配，用UNION算符并连结果；
等等.....
MySQL自带的explain是分析查询性能的好工具。
这里主要看type和key这俩字段。
key表示这个查询用到的索引。
type表示这个查询的索引使用效率。type大概有以下几种：
- const 使用主键索引匹配，或者唯一索引匹配非null值。精确匹配一条，速度最快。
- ref 使用普通二级索引匹配。会匹配出多个主键进行回表查询。
- range 使用索引进行范围查找。
- index 使用了不带索引的字段进行条件筛选。会在索引匹配后顺序判断每一条是否符合条件。
- all 无法命中索引，全表扫描。
3.事务&数据库锁
事务和MVCC
数据库的变更操作是个“危险”的事情，尤其是对于需要几个语句共同合作完成的操作（例如案件的出催），可能受到硬件性故障例如断网断电、并发影响，导致执行一半中断或者中途数据错乱的问题。
所以我们要确保这样一个操作场景，要么一次性执行完成不受影响，要么干脆别执行。这种操作系列称之为事务。
事务具备四个基本特性，称为ACID：
- Atomicity 原子性，该操作不可分割打断，保障可以一次性执行完成
- Consistency 一致性，所有的操作要满足约束条件（唯一、not null等）
- Isolation 隔离性，事务之间不能相互干扰，引起数据错乱（脏写）
- Durability 持久性， 已经完成的事务所修改的数据不会因任何故障丢失
InnoDB引擎支持事务操作，以begin开始，以commit提交结束。
在高并发情况下，为了性能考虑，事务间的隔离性有时会做出一点取舍。引起数据错乱的脏写肯定是要杜绝的，但还有几种情况有时要求就没有那么严格：
- 脏读：事务A读到了事务B修改过的数据，但此时事务B未提交
- 不可重复读：事务A以同样的查询语句查询，而过程中有其他事务修改了数据，引起每次查询的结果不同
- 幻读：事务A以同样的查询语句查询，而过程中有其他事务插入了满足查询条件的新数据，导致A查出了“不该查出”的新数据
MySQL支持4种隔离性级别的设置：
隔离性最差的read uncommitted 会直接读取最新的版本，而serializable会使用加锁的方式。
剩下俩会通过MVCC（multi-version concurrency control）来实现，其主要依赖版本链和ReadView。
可以对比参照git的代码版本控制和tag，事务对数据的修改就可以看成是各种分支，每一个事务有自己对应的版本位置，ReadView是在某个时刻对版本链打的tag，两个级别的区别主要在于打tag的时机要求。
锁的性质：共享锁 pk 排他锁
加锁是隔离性最强的方法。对于读和写操作，我们对隔离性的要求又有一些不同：
由于读操作不改变数据，所以A在读的时候，并不排斥B也来读；
写操作会直接影响数据，所以A在写的时候，即不能让B来写，也不能让B来读。
所以，读操作的事务是可以同时进行的，而写操作事务之间、写操作和读操作则是互斥排他的。
我们把读操作加的锁称为共享锁（S），写操作加的锁称为排他锁（X）。（p.s 读操作也可以显示声明加排它锁，select.....for update，一般不会这么干）
加锁、解锁操作：
一个读操作事务开始时，先判断是否有X锁，如果有则进入S锁等待队列等待，没有则加上S锁并开始执行；
一个写操作事务开始是，先判断是否有锁，如有则进入X锁等待队列等待，没有则加上X锁并开始执行；
当前事务完成并释放锁后，先把X等待队列中的X锁出队列执行操作（防止写操作“饥饿”）。
锁的颗粒度：表级锁 pk 行级锁
顾名思义，表级锁是对整张表上锁，而行级锁是对一条记录上锁。一般情况下，行级锁对并发的支持会好于表级所，比如有两个写任务，针对不同的记录，行级锁可以并行而表级锁会阻塞。
不过说句公道话，表级锁并不是一无是处，因为其更省内存。尤其是一个事务需要访问表中大量数据或者做group by的时候，用表锁只要维护一个锁，而用行锁要维护N个锁。
此外，大量insert操作时，表锁性能也会优于行锁。InnoDB主键自增就是通过表级锁完成的。
InnoDB引擎同时支持表级锁和行级锁，而其他引擎如MyISAM只支持表级锁。
原因还是在于b+索引树，InnoDB引擎可以对索引上锁，当然同时也反过来要求使用行级锁必须要用索引。
有行锁和表锁共存时就会引出一个问题，如果要对表加锁，就需要判断表中的记录是否被加了行锁，总不能遍历查询判断每一行有没有加锁吧？
遍历是不可能遍历的，这辈子都不可能。为了解决这个问题，需要引入一个“意向”锁。
意向锁也分为共享和排他两类，简称IS和IX。有IS锁意味着表中有行级别S锁；有IX锁意味着表中有行级别X锁。
意向锁是表级别的，但其本身不锁定任何表或者行，只是用来快速判断表内是否有记录被锁着，所以意向锁之间是兼容的。有10个意向锁就说明里面有10个行级操作正在进行。
InnoDB表级别锁的兼容性：
完整版的上锁过程：
- 行级读操作，先判断是否有表级X锁或对应行的X锁，若有则等待；没有则给表加IS锁，给对应行加S锁并执行操作。
- 行级写操作，先判断是否有表锁或者对应行锁，若有则等待；没有则给表加IX锁，给对应行加X锁并执行操作。
- 行级操作完成后，释放行锁及其加上的表意向锁。
- 表级读操作，先判断是否有表级X锁或IX锁，若有则等待；没有则给表加S锁并执行操作。
- 表级写操作，先判断是否有表级锁或意向锁，若有则等待；没有则给表加X锁执行操作。
- 表级操作完成后，释放表级锁。
行级锁的功能：记录锁 pk 间隙锁
这二者还是比较好理解的。
记录锁锁定的是一条数据记录本身，对于脏读、不可重复读的情况，是某个特定的记录被其他事务修改引起的，所以只要锁定这提条数据防止其他事务来更新就可以。
对于幻读，是由于新插入的记录引起的，由于无法预测哪些记录会插入，所以依靠锁数据是无法避免的。这时需要再加一个间隙锁。
间隙锁的作用是锁定上锁行和主键前一条数据之间的空间，防止有插入操作。例如目前表中两条连续记录id分别是10和15，那在15的数据上加间隙锁，可以防止插入id在11-14范围内的数据。
supermum是mysql里的虚拟最大行记录，对于当前表里主键最大的记录，在supermum上加间隙锁可以防止后续更大主键的记录插入。
