mysql笔记

读锁（共享锁，s锁）：是一种共享锁，一个会话持有表a的读锁时，不会阻塞其它会话请求表a的读锁和当前会话请求表a的写锁，会阻塞其它会话请求表a的写锁。
当前会话对表a写数据会报错，其他会话对表a写数据会阻塞；
写锁（排它锁，X锁）：是一种排他锁，一个会话持有表a的写锁时，会阻塞其它会话请求表a的读/写锁，不阻塞当前会话请求表a的读/写锁，如果当前会话请求读锁则释放写锁加上读锁。
一个会话对表a加了读锁/写锁，对表b进行增删改查会报错。

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放
锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的
读写外，也限定了本线程接下来的操作对象。
举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写
t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、
写 t2 的操作。连读 t2 都不允许，自然也不能访问其他表。(测试发现线程 A可以读t2，也可以给t2上读锁，但不能访问其他表)

lock table category read; //给category加读锁
lock table category write; //给category加写锁
show OPEN TABLES where In_use > 0; //查看哪些表被锁了，包括其他会话锁着的表
unlock tables; //释放当前会话持有的任何锁

会话1和会话2关闭自动提交，会话1更新表a id=6的数据，不提交，会话2也试图更新表a id=6的数据会被阻塞，试图更新表a其他的数据会不阻塞。
会话1rollback/commit会释放锁，不再阻塞。

---

可重复读时
更新条件where的列如果没有索引，行锁变成表锁。
比如表a id=1190的列name =‘二手车_面包车’，name列没有索引，会话1执行
SET SQL_SAFE_UPDATES = 0;
update category set page_count=page_count+1 where name =‘二手车_面包车’;不提交，
会话2执行update category set page_count=page_count+1 where id=1190;会阻塞，
执行update category set page_count=page_count+1 where id=11;也会阻塞。
如果给name加上单列索引，会话2更新id=1190会阻塞，更新id=11不会阻塞；
如果给name,url加上复合索引，会话2更新id=1190会阻塞，更新id=11不会阻塞，
如果给url,name加上复合索引，会话2更新id=1190会阻塞，更新id=11也阻塞。

行锁变表锁的情况，其他会话新插入行或者对已有的行进行修改会阻塞，修改不存在的行不会阻塞。
阻塞其他会话读取此表的共享锁和排它锁，不阻塞其他会话的读取数据。

以上行为读已提交时不会发生，即更新条件where的列如果没有索引，行锁不会变成表锁。

---

类型转换导致行锁升级为表锁：
数据库里page_count类型int，不管会话1执行update category set name= ‘ccc’ where page_count =‘1111’;
还是执行update category set name= ‘ccc’ where page_count =1111;，
会话2执行page_count ='1111’或page_count =1111都阻塞，执行page_count =‘2222’或page_count =2222都不阻塞；
说明类型转换没有导致行锁升级为表锁：百度后发现有人说：
而且类型不同导致行锁变表锁，只发生在原数据类型是string，而update/delete时，数据类型是int，而当原数据类型是int，
update/delete的数据类型是string时，依旧使用索引（若字段添加了索引），并不会导致行锁变表锁。
修改成char后测试，会话1page_count =‘1111’;会阻塞会话2的1111/‘1111’/2222,不阻塞’2222’；
会话1page_count =1111;会阻塞会话2的1111/‘1111’/2222/‘2222’；
这说明对于加了索引了string类型，where用其他类型发生类型转换后会锁表，
用string会锁行，但如果其他会话用其他类型修改数据，会因为类型转换发生阻塞。

---

测试事务隔离级别，在会话1修改会话2读取，只需修改会话2的的级别即可，如
show variables like ‘%iso%’;
set session transaction isolation level repeatable read;
set session transaction isolation level read uncommitted;

---

select里使用子查询：
SELECT ,(SELECT count() FROM stu where class=category.name) FROM category order by id desc limit 5;

查询出每个部门的编号，名称，位置，部门人数，平均工资
SELECT d.deptno,d.dname,d.loc,
(SELECT COUNT(empno) FROM emp WHERE emp.deptno=d.deptno GROUP BY deptno) con,
(SELECT AVG(sal) FROM emp WHERE emp.deptno=d.deptno GROUP BY deptno) avgsal
FROM dept d

---

Using temporary：使用临时表;
Using filesort：没有使用表内的索引进行读取，而是使用外部索引进行排序

create index index_id on category(url,name);
drop index index_id on category;

#因为经过url筛选后的数据并非按name排序，所以group by name需要重新排
#extra：‘Using index condition; Using temporary’，key：‘index_id’，
#url in (‘3’,‘1’,‘2’)使用索引，group by name需要临时表重新排序
explain SELECT * FROM category where url in (‘3’,‘1’,‘2’) group by name;

#extra：Using where，key：‘index_id’，筛选排序都用索引
explain SELECT * FROM category where url in (‘3’,‘1’,‘2’) group by url,name;

#extra：Using where; Using temporary，key：null，原因是发生了类型转换。。。。
explain SELECT * FROM category where url=3 group by name;

#extra：Using index condition，key：使用索引
explain SELECT * FROM category where url=‘3’ group by name;

---

create index index_id on stu(a,b,c);
#key: ‘index_id’
#extra:‘Using where; Using index; Using filesort’
#b>1后查询出来的数据并不按c排序，所以索引失效后Using filesort
explain SELECT * FROM jd.stu where a=1 and b>1 order by c;
drop index index_id on stu;

create index index_id on stu(a,c);
#key: ‘index_id’
#extra:‘Using index condition; Using where’
##a=1查询出来的数据是按c排序的，筛选b>1后顺序不变
explain SELECT * FROM jd.stu where a=1 and b>1 order by c;

---

为什么要小表驱动大表呢
类似循环嵌套

for(int i=5;…)
{
for(int j=1000;…)
{}
}
如果小的循环在外层，对于数据库连接来说就只连接5次，进行5000次操作，如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。
另一种说法：主要原因是小结果集驱动的号，查询的次数更少吧！比如小表中20个数据，大表中10000个数据，以小表的形式进行驱动的话，通过hashmap只需要20log10000次查询，二大表驱动需要10000log20次

---

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
例如：表A（小表），表B（大表）

select * from A where cc in (select cc from B) ;// 效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc) ;// 效率高，用到了B表上cc列的索引。
相反的

select * from B where cc in (select cc from A) ; //效率高，用到了B表上cc列的索引；
select * from B where exists(select cc from A where cc=B.cc) ;//效率低，用到了A表上cc列的索引。
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
in先执行子查询，所以后表驱动前表；exists后执行子查询，反过来。同理join时也是小表写左边。

explain select * from s1 left join s2 on s1.a=s2.a;
需要在被驱动表(不一定是右表)s2的a字段创建索引。
因为s1的a列所有数据都要在s2里查询一次，而s1本身只查询一次。

---

连接查询的驱动关系(category是大表)：
explain select * from s1 left join s2 on s1.a=s2.a and s1.a=6; s1->s2
explain select * from s1 right join s2 on s1.a=s2.a and s1.a=6; s2->s1
explain select * from s1 inner join s2 on s1.a=s2.a and s1.a=6; s1->s2
explain select * from s1 ,category; s1->category
explain select * from category ,s1; s1->category

结论：left join和inner join左驱动右，right join右驱动左，不写join自动小驱动大。

---

s1表a列有索引，
explain select * from s1 where a=6; extra:null
explain select a,b from s1 where a=6; extra:null
explain select a from s1 where a=6; extra:‘Using index’

s1表a,b,c列有复合索引，
explain select * from s2 where a=6; extra:‘Using index’
explain select c,b from s2 where a=6; extra:‘Using index’
结论：select字段被索引覆盖时由extra:‘Using index’，顺序可以不同，不需要左前缀匹配。

is null和 is not null都走了索引：
explain select a from s1 where a is null;
explain select a from s1 where a is not null;

like "%a%"索引失效，like "a%"索引不失效，因为%a%查出来的数据不连续，a%的数据连续。

索引的使用跟查询的and的前后顺序没有关系。如对user(a,b)创建索引，
SELECT * FROM user WHERE a = 1 AND b = 2
和
SELECT * FROM user WHERE b = 2 AND a = 1都走索引。最好写第一种。
如对user(a,b,c,d)创建索引，
SELECT * FROM user WHERE a = 1 AND b = 2 AND c > 2 AND d = 2也会自动调整顺序，4个索引都有效。

---

ref 列列出具体哪些列或常数被使用了。和前方表连接的字段,const代表是常量值连接。
复合索引可能被使用但不是所有字段都用了，可以用ref来判断用了哪些字段。

s2有a,b的复合索引，排序时必须是a->b的顺序，且都正序或者都逆序。
否则下面两个extra：‘Using filesort’
explain select a from s2 order by b asc,a asc;
explain select a from s2 order by a asc,b desc;

create index ab on s2(a,b);
#type:range,key:ab,extra:‘Using where; Using index’
explain select id,b from s2 where a like ‘1%’;

#type:index,key:ab,extra:‘Using where; Using index’
#type:index表示一条一条遍历索引，并不是常说的走索引(比如通过b+树查找)
#个人理解是下面3个sql查询的所有数据都从索引里能拿到，所以type:index。
explain select id,b from s2 where a like ‘%1%’;
explain select a from s2 where b like ‘1%’;
explain select a from s2 where b like ‘%1%’;

#这个例子c拿不到，所以不是type:index。
#type:all,key:null,extra:‘Using where’
explain select c from s2 where a like ‘%1%’;

---

事务a对表t加读锁，事务b再对表t加读锁没有问题；
如果事务c对表t加写锁会被阻塞，此时事务b再对表t加读锁也会被(事务c)阻塞。

---

表s有字段a,b,c，主键(a,b)，索引c，经常执行下面的语句，
select * from s order by c,a;
select * from s order by c,b;
是否需要为(c,a)和(c,b)加上索引？
索引(c,a)按c排序，c相同的地方再按a排序，和索引c效果一样，不需要；索引(c,b)不需要；

---

s2里有数据(id=1,a=x11)
事务a执行set autocommit=0;
select * from s2;
然后事务b执行update s2 set a= 123 where id=1;并提交，
事务a再执行update s2 set b= 123 where a=‘x11’;
会更新0条数据，再执行select * from s2;会发现数据没有任何变化。
提交事务a后会发现事务b更新的数据。

自增id不连续的原因：
1，申请id后插入失败，如回滚；
2，s2表里有id和a字段，id用来1/2/3/4，
SELECT * FROM jd.s2;
insert s2(a) select id from s2 limit 4;
insert s2 value (null,6);
使用select进行批量插入，每次申请id个数是上次是的两倍，比如这里依次申请1/2/4个，
,5,6-7,8-11，但是10-11不使用，insert s2 value (null,6)插入的id变成了12.

不把id的间隙去掉的原因是每次需要确定id是否重复，影响性能。

---

1.加锁的基本单位是（next-key lock）,他是前开后闭原则
2.插叙过程中访问的对象会增加锁
3.索引上的等值查询–给唯一索引加锁的时候，next-key lock升级为行锁
4.索引上的等值查询–向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
5.唯一索引上的范围查询会访问到不满足条件的第一个值为止

RR模式下：
s2有id和a(int，普通索引)字段，有(5,5),(10,10),(15,15),(20,20),(25,25),(30,30)的数据。

1，事务a：select * from s2 where id=10 for update;
事务b：更新id=10和插入10阻塞，其他都不阻塞，说明锁id=10
解释：id=10所以锁id(5,10]，升级为行锁锁id=10

2，事务a：select * from s2 where id=12 for update;
事务b：更新id=5和id=15不阻塞，插入11到14阻塞，其他不阻塞，说明锁id(10,15)
解释：id=12所以锁id(10,15]，12！=15，所以退化成间隙锁锁id(10,15)

3，事务a：select * from s2 where id>=10 and id<11 for update;
事务b：更新id=10和id=15阻塞，插入11到14阻塞，其他不阻塞，说明锁id[10,15]
解释：id=10锁id=10，10<id<11锁id(10,15]，不是等值查询不退化成间隙锁，所以锁id[10,15]

3.5，事务a：select * from s2 where id=10 and id<11 for update;锁id(10,15]

4，事务a：select * from s2 where id=10 or id=16 for update;
锁id=10和id(15,20)，取并集即可。

注意上面插入用insert s2 value (需要测试的id,6);
下面插入用insert s2 value (null,需要测试的a);

5，事务a：select * from s2 where a=10 for update;
事务b：更新a=10阻塞，插入5到14阻塞，其他不阻塞，说明插入锁a[5,15)，更新只锁a=10
解释：a=10锁a(5,10]，a非唯一索引所以继续扫描并锁扫描的行，锁定a(10,15]，
由于是等值查询所以退化成间隙锁锁a(10,15)，所以锁a(5,15)。但是插入时为何锁a=5不明。

6，事务a：select * from s2 where a=12 for update;
事务b：更新都不阻塞，插入10到14阻塞，其他不阻塞，说明插入锁a[10,15)，更新不锁
解释：a=12锁a(10,15]，12！=15，所以退化成间隙锁锁a(10,15)。但是插入时为何锁a=10不明。

7，事务a：select * from s2 where a>=10 and a<11 for update;
事务b：插入a=5到14阻塞，其他不阻塞，说明锁a[5,15)；
更新a=6到15阻塞，其他不阻塞，说明锁a(5,15]
解释：a=10锁a(5,15)，且插入时锁a=5。
10<a<11锁a(10,15]，不是等值查询不退化成间隙锁，所以锁a(5,15](然而并不是，见7.5)。
即锁a(5,15]，为何插入额外锁a=5，却不锁a=15，原因不明。
把数据(20,20)改成(20,10)后测试效果一样。

7.5，事务a：select * from s2 where a>10 and a<11 for update;
插入锁a[10,15)，更新锁a=15

8，按顺序执行以下sql
事务a：select * from s2 where id=11 for update;
事务b：select * from s2 where id=12 for update;
事务b：insert into s2 value(12,1) #会阻塞
事务a：insert into s2 value(12,1) #会提示死锁
间隙锁不会互相阻塞，但b的插入会被a的间隙锁阻塞，a再插入被b阻塞导致死锁。

9，事务a：select * from s2 where a=10 limit 1 for update;
事务b：更新a=10阻塞，其他(包括插入10)不阻塞，原因不明。

10，事务a：select * from s2 where a=10 limit 2 for update;
事务b：更新a=10阻塞，插入a[5,9)阻塞，其他(包括插入10)不阻塞，原因不明。

似乎非唯一索引最终插入都会锁左边界，且不锁右边界。

---

不同事务隔离级别下，快照读的区别：
READ COMMITTED隔离级别下，每次读取都会重新生成一个快照，所以每次快照都是最新的，也因此事务中每次SELECT也可以看到其它已commit事务所作的更改；
REPEATED READ隔离级别下，快照会在事务中第一次SELECT语句执行时生成，只有在本事务中对数据进行更改才会更新快照，因此，只有第一次SELECT之前其它已提交事务所作的更改你可以看到，但是如果已执行了SELECT，那么其它事务commit数据，你SELECT是看不到的。
————————————————
1.第一次快照查id=1，别人修改id=1，我能否修改id=1？
回答1:如果别人已经提交我能修改id1，否则我的修改会被阻塞；且修改前查询不到别人的修改，修改后查询得到，说明快照更新了
2.第一次快照查id=1，别人修改id=1，我修改id=2，查id=1的数据是否会更新
回答2:不更新，别人修改id1，我也必须修改id1再查询才能看到别人的修改，我修改id2查询id1数据不变

而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：
在可重复读隔离级别下，只需要在事务开始的时候找到那个 up_limit_id，之后事务里的其他
查询都共用这个 up_limit_id；
在读提交隔离级别下，每一个语句执行前都会重新算一次 up_limit_id 的值。

总结就是：
版本未提交，不可见；
版本已提交，但是是在快照创建后提交的，不可见；
版本已提交，而且是在快照创建前提交的，可见。
这其实就是可重复读的想要实现的效果。

---

快照读不会出现幻读，当前读会出现幻读。下面的例子s2(id,a)，a有普通索引。
事务a：set autocommit=0;
事务a：select * from s2 where a=10; #(10,10),(20,20)
事务b：update s2 set a= 10 where id=20; #(10,10),(20,10)
事务a：update s2 set a= 11 where a=10; #(10,11),(20,11)

而如果事务a：set autocommit=0;
事务a：select * from s2 where a=10 for update; #(10,10),(20,20)
事务b：update s2 set a= 10 where id=20; 会阻塞，从而防止了幻读。

---

left join的on和where的区别在于：
1，on在where前执行
2，on即使不匹配，也一定会返回左表的所有数据；where不匹配就不返回

SELECT * FROM jd.s1; #2条数据
SELECT * FROM jd.s2; #6条数据

#21+5=7条数据，笛卡尔积拼接成12条数据，a=5能找到两条，其他找不到的只返回一条。
SELECT * FROM s2 left join s1 on s2.a=5;
SELECT * FROM s2 left join s1 on 1=1; #26=12条数据
SELECT * FROM s2 left join s1 on 1=1 where s2.a=5; #12条筛选出2条数据返回

---

表t中有x条数据，name=a有y条，name=a且age=b有z条。explain select * from t where name=a的rows是x，即全表查询。如果给name创建索引，rows就是y。如果给name和age创建复合索引(注意顺序)，rows=y。如果给age和name创建索引，rows=x。explain select * from t where name=a and age=b如果无索引rows=x，给name创建索引row=y，给name和age复合索引rows=z

mysql执行顺序：
from->join->on->where->group by->having->select->distinct->order by->limit
也有说on在join之前执行的，不确定哪个对

---

索引增加查询速度，降低增删改速度；不适合增删改频繁的数据和少量数据。
优化索引时逐步优化，需要把以前的索引删除，避免干扰。
对于复合索引，如果左侧失效，右侧全部失效。不能跨列，使用时必须和索引顺序一致。

---

category有普通索引index(url,name)，测试结果如下：

#type：ref，key：i1，rows：1，说明走了索引
explain select url from category where url is null and name =‘a’;

#type：index，key：i1，rows：1200，说明扫描了全部索引
explain select url from category where url is not null and name =‘a’;

#type：index，key：i1，rows：1200，说明扫描了全部索引，原因当然是name ='a’不走索引
explain select url from category where url =‘a’ or name =‘a’;

#type：range，key：i1，rows：2，说明走了索引，因为就是普通的范围查询走索引
explain select url from category where url =‘a’ or url =‘1a’;

#type：range，key：i1，rows：1，说明走了索引
explain select url from category where url >‘a’ and name =‘1a’;
#type：index，key：i1，rows：1200，说明扫描了全部索引
explain select url from category where url >’’ and name =‘1a’;
#type：index，key：i1，rows：1200，说明扫描了全部索引
explain select url from category where url <>‘a’ and name =‘1a’;

总结：
1，is not null和!=不走索引，is null走索引
2，>和<走不走索引要看能找到多少条数据
3，like 'a%'走索引like '%a’不走

---

#下一句只返回id=1这一行，它把c1每一行的数据代入exists里，exists语句不为空则返回c1的该行
select * from category c1 where exists(select 1 from category c2 where c1.id=1);
#下一句返回c1的所有数据，只要表里有id=1这一行，因为exists始终返回这一行
select * from category c1 where exists(select 1 from category c2 where c2.id=1);

---

order by测试：

#type:ref,rows:1，url等值查询的数据一定是按name排序的
explain select url from category where url=‘a’ order by name;
explain select url from category where url=‘a’ order by url,name;

#extra:‘Using where; Using index; Using filesort’
#order by name比order by url,name多了Using filesort，因为范围查询后的数据不按name排序
explain select url from category where url like ‘a%’ order by name;
explain select url from category where url like ‘a%’ order by url,name;

#type:index,rows:1200，虽然数据按url,name排序，但是要遍历所有索引所以type:index
explain select url from category order by url asc,name asc;
explain select url from category order by url desc ,name desc;

#type:range,rows:1,extra:‘Using where; Using index; Using filesort’
#必须都是asc或者都是desc，否则要重新排序。
explain select url from category where url like ‘a%’ order by url asc,name desc;

---

select sleep(1);睡1s
select a,b where ***是select a inner join b ***的简写。

---

mysql的+只能数字计算，不能拼接字符串，遇到字符串会转成数字参加计算，
如’2a’转成2，'a’不能转成数字就转成0。null参加计算都返回null
select concat(1,2,null); #null
select 1+‘a’; #1
select 1+‘2a’; #3
select 1+null; #null

sum和avg一般用于处理数值型，count，min，max可以处理任何类型，它们都忽略null。
可以和distinct搭配计算。统计行数时count(1)=count(*)>count(其他)。
sum和avg处理数值型时也是把字符串尝试转成数字。
avg=sum/非null的行数。sum(distinct a)先去重再求sum。
比如b字段数据是(1,2,1,null,null,null)，avg(b)=(1+2+1)/3,avg(distinct b)=(1+2)/2

---

set @a=1;
select @a,@b; #@b不存在，返回null

select 3 into @a;
select name into @b from s2 where id=1; #只能返回一个值给变量

---

当子查询的返回值只有一个时，可以使用比较运算符如=、<、>、>=、<=、!=等将富查询和子查询连接起来。
如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY、SOME或ALL。其中等值关系可以用IN操作符。
在一个SELECT 语句的WHERE 子句或HAVING 子句中嵌套另一个SELECT 语句的查询称为嵌套查询，又称子查询。

嵌套查询的工作方式是：先处理内查询，由内向外处理，外层查询利用内层查询的结果嵌套查询不仅仅可以用于父查询select语句使用。还可以用于insert、update、delete语句或其他子查询中。

怎么删除表的前 10000 行。比较多的留言都选择了第
二种方式，即：在一个连接中循环执行 20 次 delete from T limit 500。
确实是这样的，第二种方式是相对较好的。
第一种方式（即：直接执行 delete from T limit 10000）里面，单个语句占用时间长，锁的时
间也比较长；而且大事务还会导致主从延迟。
第三种方式（即：在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。

---

数据库：drop、truncate、delete三者删除的区别
drop：drop table 表名
删除内容和定义，并释放空间。执行drop语句，将使此表的结构一起删除。
truncate (清空表中的数据)：truncate table 表名 删除内容、释放空间但不删除定义(也就是保留表的数据结构)。与drop不同的是,只是清空表数据而已。
truncate不能删除行数据，虽然只删除数据，但是比delete彻底，它只删除表数据。
delete：delete from 表名 （where 列名 = 值） 与truncate类似，delete也只删除内容、释放空间但不删除定义；但是delete即可以对行数据进行删除，也可以对整表数据进行删除。
事务里delete能回滚，truncate不能回滚

索引类型分为主键索引和非主键索引。
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered
index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引
（secondary index）。

根据上面的索引结构说明，我们来讨论一个问题：基于主键索引和普通索引的查询有什么区别？
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+
树；

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，
得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主
键查询。

当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，
可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
所以现在你知道了，这段开头的问题里，我们要为高频请求创建 (身份证号，姓名）这个联合索
引，并用这个索引支持“根据身份证号查询地址”的需求。
那么，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法
使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护
(a,b)、(b) 这两个索引。
这时候，我们要考虑的原则就是空间了。比如上面这个市民表的情况，name 字段是比 age 字
段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释
放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
知道了这个设定，对我们使用事务有什么帮助呢？那就是，如果你的事务中需要锁多个行，要把
最可能造成锁冲突、最可能影响并发度的锁尽量往后放

InnoDB 代码实现上，一个事务只需要在启动的时候，找到所有已经提交的事务 ID 的最
大值，记为 up_limit_id(测试发现应该是在第一次快照读的时候记录)；然后声明说，“如果一个数据版本的 row trx_id 大于 up_limit_id，我就不认，我必须要找到它的上一个版本”。当然，如果一个事务自己更新的数据，它自己还是要认的。
你看，有了这个声明后，系统里面随后发生的更新，是不是就跟这个事务看到的内容无关了呢？
因为之后的更新，产生的新的数据版本的 row trx_id 都会大于 up_limit_id，而对它来说，这些
新的数据版本是不存在的，所以这个事务的快照，就是“静态”的了。

innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快

速发现并进行处理的，但是它也是有额外负担的。每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是

O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个

量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会

看到 CPU 利用率很高，但是每秒却执行不了几个事务。

字符串字段创建索引的方式有：

1. 直接创建完整索引，这样可能比较占用空间；
2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不
   支持范围扫描。

---

前缀索引：
如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：

1. 从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
2. 到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这
   行记录丢弃；
3. 取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID
   索引上取整行然后判断，这次值对了，将这行记录加入结果集；
4. 重复上一步，直到在 index2（即 上取到的值不是’zhangs’时，循环结束。
   在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。
   通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变
   多。
   但是，对于这个查询语句来说，如果你定义的 index2 不是 email(6) 而是 email(7），也就是说
   取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能
   够直接查到 ID2，只扫描一行就结束了。
   也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成
   本。

如果使用 index1（即 email 整个字符串的索引结构）的话，可以利用覆盖索引，从
index1 查到结果后直接就返回了，不需要回到 ID 索引再去查一次。而如果使用 index2（即
email(6) 索引结构）的话，就不得不回到 ID 索引再去判断 email 字段的值。
即使你将 index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所有的
信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了
完整信息。
也就是说，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀
索引时需要考虑的一个因素

间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，
就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设
置为 row。这，也是现在不少公司使用的配置组合。

1. 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
2. 如果使用 join 语句的话，需要让小表做驱动表。
   但是，你需要注意，这个结论的前提是“可以使用被驱动表的索引”。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，
过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小
表”，应该作为驱动表。

能否用上被驱动表的索引，对 join 语句的性能影响很大。
通过对 Index Nested-Loop Join 和 Block Nested-Loop Join 两个算法执行过程的分
析，我们也得到了文章开头两个问题的答案：

1. 如果可以使用被驱动表的索引，join 语句还是有其优势的；
2. 不能使用被驱动表的索引，只能使用 Block Nested-Loop Join 算法，这样的语句就尽
   量不要使用；
3. 在使用 join 的时候，应该让小表做驱动表。

---

---

case when then的用法：
如果不设置else默认else是null而不是else不操作，
下句sql表示b=6的行的b更新成null，否则不变；a=11的行的a更新成null，否则不变。
update t1 set b= (case when b =6 then null else t1.b end ),
a= (case when a =11 then null else t1.a end ) ;
下句sql表示查询t1的所有行，b=1的行显示b=1，否则显示b=null。
select id,a,(case when b=1 then 1 end) b from t1;

---

---

---

---

---

---

---

---

---

---

---

---

---