索引

索引失效的情况

如果WHERE条件中还有OR 除了OR前后使用了索引列

如果mysql判断全表扫描比使用索引查询快，不会使用索引

执行LIKE模糊查询并以%开头

索引出现了隐式的类型转换。比如数据类型是varchar，我们输入的是一个数值。那么不会使用索引

explain执行计划

可以模拟优化器执行SQL查询语句，用来分析sql语句的性能

Id:标识符
Select_type:查询的类型
type:表的连接类型
- const：通过主键或唯一键查询，并且结果只有1行（也就是用等号查询）。因为仅有一行，所以优化器的其余部分可以将这一行中的列值视为常量。
- eq_ref：通常出现于两表关联查询时，使用主键或者非空唯一键关联，并且查询条件不是主键或唯一键的等号查询。
- ref：通过普通索引查询，并且使用的等号查询。
- range：索引的范围查找（>=、<、in 等）。
- index：全索引扫描。
- All：全表扫描
possible_keys 预测用的索引
key:实际使用的索引
key_len 使用索引的长度
ref: 表之间的引用
rows:要检查的行数

索引的数据结构

常见的索引类型有hash、b树和b+树

Hash:底层是hash表，查找时根据key获取对应的hashcode，然后根据hashcode获取对应的数据行地址，根据地址拿到对应的数据

B树:是一种多路搜索树，每个节点存储key，指向key数据记录的地址和指向下一层节点的指针。查询时，从根节点向下查找，知道找到对应的key

B+树:是B树的一种变种。主要区别是B+树的非叶子节点只存储key和指向下一层节点的指针。B+树的叶子节点之间通过指针来连接。构成一个有序链表，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。

那么为什么选用b+树作为索引呢？

不使用红黑树是因为红黑树的I/O操作比B树多得多。比如我们的数据量特别大。红黑树层数很高。从树的根节点每向下一层，就相当于一次I/O操作

不使用hash索引。对于单个数据，hash是很快的。但是hash不支持范围查询。不支持索引值的排序操作以及不支持联合索引的最左匹配

B树:因为B树相对于B+树，查询的时候会做局部中部遍历。也会有多余的I/O操作。并且相对于B+树的链表，B树的非叶子节点多存储了一个指向key的地址元素。导致了层数变高，效率没有B+树

事务和锁

事务的隔离级别

隔离级别	脏读	不可重复读	幻读
读未提交	有	有	有
读已提交	无	有	有
可重复读	无	无	有(InnoDB无)
串行化	无	无	无

脏读：一个事务对某一个数据进行了修改，但是并没有提交到数据库中。然后另外一个事务读取到了这个没提交的数据并使用。但这个数据我们并没有提交，所以是一个”脏数据”，可能会产生一连串的后果

不可重复读：在一个事务中多次读取同一个数据时,结果不一致。比如事务A第一次读取到的成绩是100.然后这个时候事务B将成绩修改为50.那么下次事务A再次读取发现成绩变为了50.这个时候数据一致性就被破坏了。也就是不可重复读问题

幻读：和不可重复读很像，只不过幻读是针对增加操作的。在一个事务中使用相同的SQL查询，第二次读到了其他事务刚插入的行

不可重复读注重数据的修改，而幻读注重于数据的插入

行锁、表锁和页锁

行锁:锁粒度最细的一个锁。对当前行进行加锁。因为加锁粒度小，所以加锁的开销大。会出现死锁。但是发生冲突的概率最低，并发度也最高。

行锁有三种锁定方式：

记录锁（Record Locks）：锁的是单个记录

间隙锁（Gap Locks）：锁定一个范围，不包含记录本身

临键锁（Next-Key Locks）：锁定一个范围，包含记录本身，是以上两种锁的结合。不仅锁定记录，也锁定间隙

表锁: 锁粒度最粗的一种锁。当整个表进行加锁。消耗的资源少。开销小，加锁快。不会出现死锁。但是发生冲突的概率高。并发度低。

页锁: 页锁是介于行锁和表锁中间的一种锁。锁的是相邻的一组数据。开销和加锁粒度介于表锁和行锁之间。会出现死锁。并发一般。

间隙锁

mysql在可重复读的情况下和innoDB的双重条件下。是会解决幻读的。因为InnoDB中使用了间隙锁来保证。

间隙锁只有在可重复读的情况下才会生效。

比如我们的一个SQL语句是 SELECT * FROM A WHERE ID BETWEEN 10 AND 20,这个时候阻碍所有想在10-20之间插入的SQL语句。

共享锁和排他锁

无论是行级锁还是表级锁都存在2种锁机制：共享锁和排他锁

共享锁(S锁)：也就是读锁，允许多个事务同时读取一个数据，但不能进行更新操作

排他锁(X锁)：也叫写锁或者独占锁。一次只允许一个事务获取锁并对其做读写操作。

意向锁

这个东西比较抽象。意向锁可以更容易地支持多粒度的锁。由数据引擎维护，用户无法手动操作。当我们手动的给数据行加共享锁或者排他锁的时候。InnoDB引擎会先获取该数据行所在数据表的意向锁。

为什么要使用意向锁呢？假设我们对一个表加排他锁，就要去判断表里的记录有没有行锁（排他锁不兼容任何锁，也就是说不能和任何锁冲突），详细的关系如下

锁名	X（排他锁）	S（共享锁）
X（排他锁）	✖	✖
S（共享锁）	✖	✔

我们一行一行排查太慢。可以借助意向锁来实现。意向锁是表级锁，有两种：

意向共享锁（IS）：有意向加共享锁，加共享锁之前必须先获取该表的IS锁

意向共享锁（IX）：有意向加排他锁，加排他锁之前必须先获取该表的IX锁

值得一提的是，意向锁互相兼容。因为他们只是意想加锁，并不是真正意义上的加锁

分库分表

常用的工具：阿里巴巴的TDDL(JDBC直连) mycat(proxy代理) sharding-JDBC(当当网)

拆分策略：

水平拆分: 水平分表水平分库分表

垂直拆分垂直分表，垂直分库

水平分表: 将一个表的数据分开存储。比如数据库1存储用户id结尾为偶数的数据库2存储id为奇数的。每个表的结构是一致的。有2种解决方案。一种是范围拆分。也就是1-10000一个表 10001-20000一个表但这样会造成热点数据不均匀，访问压力还是不平衡。所以我们可以选择hash拆分，根据id取模决定存到哪个表中。数据是分散的存储。但是未来扩容比较麻烦，涉及到数据迁移扩展能力差点。还有一些地理位置分片和时间分片

水平分库分表：将单个表的数据分到多个数据库中，继续分表

分库分表是个双刃剑，有利有弊👇

分布式事务的问题：因为我们设计到了跨库跨表的业务操作。所以需要使用分布式事务的解决方案。我们使用seata来解决。在一个就是分布式的主键ID冲突问题。我们使用推特的Snowwake雪花id来保证。记得确保我们每台机器上的时间(雪花ID=符号位(总是0) +时间戳(41bit)+机器码+流水号)

跨库join问题：我们在拆分的时候就尽量的将有关系的表放在一个库里面。使用全局表，每个数据库中都保存一份

垂直分表一般就是大表拆小表，以字段为依据根据字段将不同的字段拆分到不同表中每个表的结构都不一样(比如用户表拆分为专门登录的和用户详细信息表可以将表里面不常用的数据给他拆出来)

垂直分库:按照业务模块切分，将不同模块的表切分到不同的数据库中

一般来说数据量大我们选择水平拆分，拆出更多的小表。而表太多我们可以选择垂直拆分。可以按照模块进行切分到不同的数据库中

MVCC

MVCC是多版本并发控制,可以做到读写之间不冲突。读取数据通过一种类似快照的方式将数据保存下来。之后的查询就去读取快照。这样读锁和写锁就不冲突了。不同事务看到自己特定版本的数据。只在RC和RR两个隔离级别下工作。包含两个组成部分 undolog和readview。

undolog版本链中有3个隐藏列。

trx_id：行id

db_roll_ptr trx_id：事务版本号

db_roll_ptr：指向上一条数据的指针

undo_log版本链:MVCC使用undo_log来确保隔离性。undo_log主要用来记录数据被修改之前的日志，在表信息修改之前会把数据拷贝到undo_log中当事务回滚时通过undo_log来还原。

ReadView：是一个数据结构，记录了当前事务的事务列表

包含4个字段

m_ids(当前活跃的事务编号集合)

min_trx_id(最小活跃事务编号)

max_trx_id(预分配事务编号,当前事务编号+1)

create_trx_id(创建者的事务编号)

快照读(只有快照读才会使用MVCC)。快照读就是select查询SQL语句也就是select

RC:每一次查询都生成快照读 RR:仅在第一次执行查询时生成快照读，后面复用第一次的快照读

当前读指的是insert，update，delete。还有select后跟for update， lock in share mode

存储引擎

InnoDB和MyISAM的区别

InnoDB支持事务，MyISAM不支持事务
InnoDB支持外键，MyISAM不支持
InnoDB是聚集索引，MyISAM是非聚集索引
InnoDB不支持全文索引，MyISAM支持全文索引
InnoDB支持表、行锁，MyISAM支持表级锁

存储引擎的类型

MyISAM引擎的结构:

.frm存储表定义

.myd存储数据文件

.myi存储索引文件

InnoDB只有ibd文件存放了索引和文件

其他

五大范式

第一范式:数据库表中每一列都是不可分割的原子数据
第二范式:表里的非主键字段，必须完全依赖主键，不能只依赖主键的一部分
第三范式:确保表里的每一列数据都和主键直接相关，不能间接相关。也就是非主键列互不依赖如下图班主任性别和班主任年龄就是多余的，不符合第三范式

第四范式(巴斯-科德范式):禁止主键列和非主键列一对多关系不受约束
第五范式(完美范式):将表分割成尽可能小的块，为了排序在表中所有的冗余

MySQL的7大日志系统

1.重做日志(read log)

用来保证mysql宕机情况下保存不完整的事务执行数据。记录的是事务执行后的状态

read log流程分为4步

先将原始数据从磁盘读到内存里。修改数据的内存拷贝

生成一条重做日志并且写入到read log buffer 记录的是数据被修改后的值

(read log buffer) 因为IO的的读取性能很低，所以引入BufferPool缓冲池来进行性能优化

当事务提交的时候。将readlogbuffer中的内容刷新到read log file

就这样定期的将内存中修改的数据刷新到磁盘里

readlog就是为了恢复由于宕机，将那些没有被刷入磁盘的数据持久化到数据库。

2.回滚日志(undo log)

保存数据的原子性，保存了事务发生之前的数据版本。比我我们执行了一条delete语句。那么undolog日志会生产出一条对应的insert语句。事务回滚时或者数据库崩溃时，可以利用undo log来进行回滚

比如用户读取某一行记录的时候。这个记录已经被其他事务占用了。我们不可能让用户这么一直等着。于是我们就可以通过undolog日志来读取之前的版本。也就是多版本并发控制器MVCC。

undo log的存储由InnoDB引擎实现。并使用sement分段的方式存储。undolog日志的结构主要是三个字段。第一个是行id。第二个是事务id，第三个是指向上一条undolog日志的回滚指针。这样一旦并发上来。就形成了一条完整的回滚链。很方便就找到了对应记录的历史版本。

3.二进制日志(bin log)

主要用来实现主从复制，记录数据库的一些更新操作。

4.错误日志(error log)

记录错误的日志，帮助我们排查错误

5.慢查询日志(slow query log)

慢查询用来记录超过指定时间阈值的SQL语句

通过**show variables like “%slow_query%”**来查看是否开启