MySql

索引

定义：一个排好序的数据结构。

聚簇索引和非聚簇索引
InnoDB用的就是聚簇索引，聚簇索引默认是主键，数据和索引连在一起，在同一个文件里。
MyISAM用的非聚簇，数据和索引分别在两张表，找到索引后还要根据索引去找数据的物理地址，这样查询效率要低一些，因为还要回表。

MySql用的是B+树，为什么不用普通二叉树、红黑树、B树？
二叉树在数据量很大的情况下树的高度会变的很高，查询效率就低，红黑树和AVL树虽然有自平衡的优点，但是它们每个节点只能存一个索引，但是一次磁盘IO是可以读16K的数据，所以引入了B树。B树每个节点有多个索引，数据和索引连在一起，大大降低了树的高度。那么为什么B树也不是最佳选择呢？B+树把数据都放到叶子节点上了，相比于B树，查找数据的时间更加稳定，B树是离根节点越近查找越快，B+树都一样，都得走到叶子节点，而且B+树非叶子节点不存放数据了，那就说明一次磁盘IO可以读取更多的索引，也减少了磁盘IO的次数。还有一个关键点：B+树的叶子结点之间用双指针连接，这样就有利于区间查找。

为什么推荐主键自增

用了主键自增，那么每次插入新的记录，就会顺序添加到当前索引节点的后续位置，一页写满后，自动开辟新的一页，如果用UUID，每次都是随机插入，就会要频繁移动数据的位置，浪费时间，不过自增id也有它的坏处，在高并发的时候，多个插入操作之间的竞争就会激烈。

InnoDB和MyISAM区别

InnoDB支持事务和外键，行锁（适合高并发场景），数据和索引都需要缓存，对内存要求较高。
MyISAM表锁，不支持事务和外键，对内存要求小。更偏向与读数据。

什么情况下需要创建索引

Creat index 联合索引名称 on 表名 （字段1，字段2，字段3）；

CREAT INDEX  idx_seller_name_sta_add ON tb_seller(name,status,address);

1.主键会自动建立唯一索引
2.表中频繁拿来查询的字段应该创建索引
3.高并发情况下，倾向于创建组合索引
4.要排序的字段，创建索引大大提高效率。

什么情况下不需要创建索引

1.表中内容少
2.字段经常修改
3.重复数据多，创建索引还不如全表扫描

EXPLAIN

id
操作表的顺序，数值越大，执行优先级越高，一般来说子查询的表id要大于主查询的表。

select_type
表的类型，simple就是一张表的简单查询，primary就是主表，subquery就是子表，union就是union语句后面的那张表。

table表名

type

与查询性能相关。从好到差依次为：
system 要查的表就一行记录
const 用唯一索引去匹配一个常量where id=‘1’，返回结果是一行记录

eq_ref 唯一索引去匹配变量，返回也是一行记录。
ref 非唯一索引返回多行记录
range 走索引，返回一个区间，只需要开始索引的某一点，而结束于另一点，不用扫描全部索引
index 也是全表走了一遍，但是只是走了索引

ALL 没走索引的全表扫描

key possible_key key_length rows
实际用到的key 可能用到的key key的长度 扫描的行数

extra

using filesort 没有走索引给的排序，自己单独走了文件排序
using temporary 对查询结果排序的时候使用了临时表
using index 走了覆盖索引，好

避免索引失效

先创建一个联合索引

creat index idx_seller_name_sta_add on tb_seller(name,status,address);

1.全值匹配把联合索引都用上，这样最好了，三个const


2.最左前缀原则
巧记：带头大哥不能死，中间兄弟不能断
不能跳过第一个索引 name 直接走后面的索引

中间兄弟status断了，那么后面的address也不走索引了。

注意key_len的变化。

name字段必须要有，至于写SQL的时候放在左边还是右边没关系。

3.索引列上少计算，范围之后全失效
索引上不要有什么运算操作，不然索引就会失效。

这里address就没有用到索引了。

4LIKE百分写最右，覆盖索引不写星
模糊匹配的时候

Explain select * from tb_seller where name like ‘%科技%’;

但是我如果把*替换成覆盖索引，

Select sellerid,name,status,address 
from tb_seller 
where name like’%科技%’;

5一些特殊情况
a.比如用到了 OR 如果OR后面的字段不走索引，那么OR前面的字段就算有索引也不走了。
b.另外如果表中某个创建了索引的字段有大量重复数据，及时创建了索引也会去走全表扫描，因为反正效率也差不多了，索引对内存还有要求。
c.全职匹配的时候不加单引号，也不会走索引。
d.is not null 、 not in、 ！= 都不走索引
总结：

SQL优化

工作时：
1开启慢查询日志捕获慢SQL
2explain分析慢SQL
3show profile看SQL执行细节
4DBA去进行SQL数据库服务器调优

具体优化

1.尽量少用select * 替换成select id 用覆盖索引替换*
2.in 和not in不走索引，用between and
例如SELECT * FROM t WHERE id IN (2,3)
替换成SELECT * FROM t WHERE id BETWEEN 2 AND 3
3.避免使用or ，or前后字段都必须是单值索引才会走索引，我们用UNION去替换OR
SELECT * FROM t WHERE id = 1 OR id = 3
优化方式：可以用union代替or。如下：
SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3
不过用了UNION就会增加temporary，空间换时间。
4.少于子查询，尽量用jion的多表连接去替换子查询
Select * from t_user where id in(select uer_id from user_role);
替换为
Select * from t_user t,user_role ur where t.id=ur.uer_id;
5.order by优化
select 会导致order by走filesort，用覆盖索引的字段去替换
如果避免不了走filesort（两次扫描算法（先在排序区排序指针，再回表）和一次扫描算法（一次性在排序区排完，内存开销大，但是效率高）），所以针对filesort优化，我们可以适当提高排序区的大小，排序区够大就会去选择一次扫描算法。
多个字段排序的话，必须同时升序或者降序才会都走索引
order by如果后面跟了多个字段，同样需要满足最左前缀原则。
6group by优化
group by的实质是先排序后分组，所以我们可以加上order by null来禁止group by 的自动排序。

Select age,count(*) from emp group by age order by null;

6数据量很大分页查询的优化

Select * from tb_item limit 10; 查询第一页的10条记录

Select * from tb_item limit 10,10;查询第二页的10条记录

Select * from tb_item limit 20,10;查询第三页的10条记录

Select * from tb_item limit 2000000,10;查询第200001页的10条记录

分页操作的时候，会自动对主键进行排序操作，这里就相当于对20000010个记录进行排序后（代价很大），我们只取了最后十条记录，如何优化？
1.可以利用主键索引先完成排序分页操作

select id from tb_item order by id limit 20000,10

select * from 
tb_item  t,(select id from tb_item order by id limit 20000,10)  a
where
t.id=a.id;

2.主键自增且不能出现断层的表，把limit查询转成某个位置的查询

select * from tb_item where id>20000 limit 10;

主（master）从（slave）复制

Slave拷贝master的二进制日志到自己的中继日志中，然后在重做中继日志中的事件。一个master可以对应多个slave，返过来不行。
主从复制的好处
1主库挂了，可以快速切换到从库来提供服务
2实现读写分离，主库更新数据复制给从库，从从库读数据
3备份的任务可以交给从库，分担主库的压力

MySQL锁

事务特性

原子性（Atomicity）原子操作单元，同时成功或失败（undolog实现，undolog保存事物发生前的一个数据版本，用于事物之行失败后全部回滚到老数据）
隔离性（isolation）事务处理过程中的状态对外不可见，各个事务独立 （通过加锁的当前读&MVCC下的快照读实现）
持久性（durable）事务完成后，对数据的修改是永久性的（redolog来保证，当系统宕机的时候，尚有数据未写入磁盘，在mysql重启的时候 根据redolog进行重做，确保事物的持久性）
一致性（consistent）数据在事务前后总量不变 （通过前三个实现）

更新丢失：前面事务的更新操作被后面事务给覆盖了
脏读：事务中已修改但未提交的数据被另一个事务读到了
不可重复读：在一个事务中两个时间点读以前读过的数据发现已经被改变了
即事务A读到了事务B已经提交的修改过的数据，不符合隔离性。
幻读：和不可重复读类型，事务A读到了事务B提交的新增的数据，不符合隔离性。

事务隔离级别：

读未提交 只能保证更新不丢失 允许读事物未提交的内容，脏读、幻读都可能发生。
读已提交 能保证不脏读
可重复读 能避免脏读和不可重复读
可序列化 最高级别了，直接不让你并发了。

Innodb默认是行锁
通常情况下，事务A对表的其中一行进行写操作
不会阻塞事务B对表的其他行进行写操作
但是如果事务A写操作的时候没有走索引（比如忘了加‘’导致索引失效），行锁就会升级成表锁，把整张表锁住，另一个事务B整个时候想要写某一行就会被阻塞，只能等事务Acommit后才能写。

间隙锁（id不连续的范围查找）
范围查找 比如 id<5 但是id只有1345没有2 但是对2也会加锁，这个锁就叫间隙锁。
事务A：
Update tbl set sex=0 where id<5; 未提交
事务B：
Insert into tbl values（2，0）； 就会被阻塞 因为id=2的位置加间隙锁

查看行锁争用情况
show status like‘innodb_row_lock%’;

MVCC（Mutil-Version Concurrency Control）

多版本并发控制
就是每次修改某行数据，都会在版本链中做记录，替代行锁去实现并发的读写。
当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加悲观锁，保证读取的记录都是最新的。

快照读
不加锁的select操作就是快照读
快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
MVCC使用的就是快照读。

版本链的形成
聚簇索引记录中每行都有三个隐藏的列，一个是事务id，一个是回滚指针，还有一个是唯一主键（就是在没有唯一主键的时候自动生成的那个，这里我们用不到它）。
某个事务要修改某行的数据，此时改行数据的事务id就生成了，这个事务id是自增的，每当有新的事务要修改该行数据的时候，事务id就会+1，然后回滚指针会指向该行记录上一个版本的位置，老版本的记录会写入到undo日志中，每当有一个事务开启修改该行数据，老数据就会头插入undo日志，形成版本链。

ReadView
Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID。
可见性算法
ReadView中主要就是有个列表专门存了当前活跃着的事务（已经开始但是还没提交的事务）id。例如当前列表里存的事务id有80,90，100

如果你要访问的记录版本的事务id（当前事务id）是50,50小于这个列表中的最小值，那么说明你要访问的这个事务早就已经提交过了，所以可以安心访问。

如果你要访问的记录版本的事务id在80到100之间，先判断在列表内有没有这个id，有的话说明该事务还没有提交，那就不能被访问，如果没有说明该事物已经被提交过了，就可以访问。

如果当前事务id位110大于100，那就说明这个事务在ReadView之后，那肯定不让访问。

这些记录都是去版本链里面找的，先找最近记录，如果最近这一条记录事务id不符合条件，不可见（就是不能访问）的话，再去找上一个版本再比较当前事务的id和这个版本事务id看能不能访问，以此类推直到返回可见的版本或者结束。

举个例子 ，在已提交读隔离级别下：

比如此时有一个事务id为100的事务，修改了name,使得的name等于小明2，但是事务还没提交。则此时的版本链是

那此时另一个事务发起了select 语句要查询id为1的记录，那此时生成的ReadView 列表只有[100]。那就去版本链去找了，首先肯定找最近的一条，发现trx_id是100,也就是name为小明2的那条记录，发现在列表内，所以不能访问。

这时候就通过指针继续找下一条，name为小明1的记录，发现trx_id是60，小于列表中的最小id,所以可以访问，直接访问结果为小明1。

那这时候我们把事务id为100的事务提交了，并且新建了一个事务id为110也修改id为1的记录，并且不提交事务

这时候版本链就是

这时候之前那个select事务又执行了一次查询,要查询id为1的记录。

这个时候关键的地方来了

如果你是已提交读隔离级别，这时候你会重新形成一个ReadView，那你的活动事务列表中的值就变了，变成了[110]。

按照上的说法，你去版本链通过trx_id对比查找到合适的结果就是小明2。

如果你是可重复读隔离级别，这时候你的ReadView还是第一次select时候生成的ReadView,也就是列表的值还是[100]。所以select的结果是小明1。所以第二次select结果和第一次一样，所以叫可重复读！

也就说可重复读不会去更新ReadView中活跃着的事务。

也就是说已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

这就是Mysql的MVCC,通过版本链，实现多版本，可并发读-写，写-读。通过ReadView生成策略的不同实现不同的隔离级别。

总结mvcc如何实现RC（读已提交）和RR（可重复读）的隔离级别
（1）RC的隔离级别下，每个快照读都会生成并获取最新的readview。

（2）RR的隔离级别下，只有在同一个事务的第一个快照读才会创建readview，之后的每次快照读都使用的同一个readview，所以每次的查询结果都是一样的。

5.幻读问题
快照读：通过mvcc，RR的隔离级别解决了幻读问题，因为每次使用的都是同一个readview。
当前读：通过next-key锁（行锁+gap锁），RR隔离级别并不能解决幻读问题。