mysql基础总结

仅做一个大纲类的总结，详情后续会持续更新

mysql索引

索引策略
基本原则：
1>索引不是越多越好，不要为所有列都创建索引
2>尽量避免冗余和重复索引
3>考虑删除不常使用的索引
4>尽量扩展索引，不要新建索引
5>频繁作为 where 过滤条件的列，应该考虑添加索引
覆盖索引：
使用索引来做排序
最左前缀匹配原则（创建多列索引时，将查询最频繁的列放到最左边）
独立的列
"=" 和 "in" 可以乱序

索引适用场景
1、读多写少
2、数据量大
3、列经常被查询

索引优缺点
优点：
1、减少扫描数据量，加快检索速度
2、避免排序和临时表
3、将随即I/O变为顺序I/O

缺点：
1、占用额外的物理空间
2、创建和维护索引要耗费时间，这回随着数据量的增加而增加
3、写操作时可能需要更新索引，导致数据库的写操作性能降低（就是读的快，但写入的慢了）

索引数据结构
1、哈希
2、B树
   1>非叶子结点包含索引和数据
   2>聚簇索引
   3>非聚簇索引--回表

索引类型
1、PRIMARY（主键索引）
2、UNIQUE（唯一索引）
3、INDEX（普通索引）
4、FULLTEXT（全文索引）
5、联合索引

一些索引对应的sql
ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
ALTER TABLE table_name ADD FULLTEXT (column);

mysql锁

间隙锁（Next-key）
1、使用MVCC+Next-key锁 可以解决幻读问题
使用步骤：
1、行锁： 行锁对索引项加锁，若没有索引则使用表锁
2、间隙锁：对索引项之间的间隙加锁
3、Next-key锁：不仅锁定一个行记录上的索引，也锁定索引之间的间隙

MVCC
1、用于实现提交读和可重复读这两种隔离级别
2、在MVCC中事物的写操作（DELETE、INSERT、UPDATE）会为数据行新增一个板块快照
3、写操作更新最新的版本快照，而读操作去读旧版快照，没有互斥关系

意向锁
1、意向锁是InnoDB自动加的，不需要用户干预
2、IX/IS是表锁
3、X/S是行锁
4、获得某个数据行的S锁之前，必须先获得表的IS锁或者更强的锁
5、获得某个数据行的X锁之前，必须先获得表的IX锁

乐观锁和悲观锁
乐观锁：通过version的方式进行锁定
悲观锁：使用数据库中的锁机制

行级锁和表级锁
表级锁：锁定整张表（很少用到表级锁）
行级锁：
1>锁定指定的行记录
2>在InnoDB中，行锁是通过给索引上的索引项加锁来实现的    
3>如果没有索引，InnoDB将会通过隐藏的聚簇索引来对记录加锁

读写锁
独享锁：SELECT ... FOR UPDATE
共享锁：SELECT ... LOCK IN SHARE MODE
注：独享锁存在，其他事物就不能做任何操作，InnoDB    下的行锁、间隙锁、next-key锁统统属于独享锁

mysql 事物

ACID
原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

事物用法
START TRANSACTION
SAVEPOINT
ROLLBACK TO
COMMIT
AUTOCOMMIT

事物应用
应该尽量缩小事物范围，减少锁的时间，提高并发
死锁：
1、死锁检测
   1>开启 innodb_deadlock_detect
   2>InnoDB将持有最少行级排它锁的事务进行回滚
2、超时机制
设置 innodb_lock_wait_timeout

分布式事务
1、两阶段提交（2PC）
2、三阶段提交（3PC）
3、补偿事务（TCC）
4、本地消息表
5、MQ事物
6、SAGA
注：
1、2 不适合高并发场景
3 适合对实时性、数据一致性要求高的场景；对业务的入侵性很高
4、5 实现最终一致性（柔性事物）
6 不保证隔离性，需要在业务层控制并发，适合于并发较少的情况

隔离级别
读未提交--解决丢失修改
读已提交--解决不可脏读
可重复读--解决不可重复读，基于MVCC实现
串行化--解决幻读，基于MVCC+间隙锁实现