索引原理

Mysql默认的存储引擎是InnoDB，InnoDB的数据结构是B+Tree，是一种分层的树形结构。B+ Tree中每一个父节点的每个元素，是其对应左子树的 “最大值”、右子树的 “最小值，相邻节点之间还有双向指针，最底层的叶子节点保持有序并存储数据。当我们通过索引查找数据的时候，只需要按照B+Tree的层级，向下查询即可，一般B+Tree就三层。所以效率非常快，只用三次IO，而索引又分聚簇索引和非聚簇索引，聚簇索引中的叶子节点就是存储的整行数据和键值，而非聚簇索引存储了聚簇索引的键值和索引列的值，所以使用非聚簇索引的时候，如果要查询整行数据，需要进行一次回表的操作，拿着索引列的值和键值再找一次对应的整行数据。

索引类型

每个表都会存在一个聚簇索引，如果没有设置聚簇索引，默认的主键就会当成聚簇索引，如果一个表没有主键，那么唯一索引就会是聚簇索引，如果都没有，Mysql会自动生成一个隐藏字段作为聚簇索引。当我们查询多个列的时候，添加过滤条件的时候，比如where，having，会根据是否有索引，判断是否走更高效的索引。Mysql有优化器的，比如select a,b,c from table。即使abc有覆盖索引，也不会走索引，因为没有筛选条件，不管走不走索引都至少会全表扫描，而且走索引是会走索引树，还要增加IO操作，效率更低，如果走覆盖索引还有可能会触发回表的操作。那其实Mysql中索引大概分的类型其实就是聚簇索引和非聚簇索引，一个表只有一个聚簇索引，通常情况下聚簇索引一般是主键，非聚簇索引的就比较多了，组合索引，哈希索引，全文索引，等等

为什么一般是三层

B+Tree 是多路平衡查找树，如果每个节点存储的键值越多，层级就会越少，同样查询消耗的IO也会越少，每个节点存储的键值由磁盘块决定，假如是最常见的4KB的磁盘块，假设索引关键字为Bigint 8字节，加上指针8字节，一共16字节，而一个磁盘块4KB，4096个字节，单个节点存储的关键字+指针就能存储256块，又因为子节点数为关键字数+1，所以假设一块根节点中，能存储257个子节点，257个子节点每一个又能存储257个子节点，那最后一层的每个叶子节点存储的关键字+指针又有256个。所以能存储的行数据有257*257*256大约1600万行，如果换成8KB的磁盘块，再乘2的三次方，就已经上亿了，就算数据量非常大，再增加一层就可以再乘512，将近700亿的数据，能应对99%的场景了。拿三层举例子，从根节点到非叶子节点再到叶子节点，一共三次IO，延迟也比较低，效率也非常高，除去上十亿级别以上的数据，三层结构完全够用，并且效率最好。

事务

对于事务来说不管是Spring还是Mysql中的事务都遵循ACID特性，原子性就是比如当A给B转账，A扣钱，B加钱，A扣钱了B没收到钱，或者B收到，A没扣，这种情况MySQL会将数据回滚。第二个是一致性，事务执行前后，保持一致性，数据不被破坏。第三个是隔离性，当多个事务并发执行的时候，事务之间互不影响。最后一个是持久性，当数据库出现断电崩溃等问题的时候，数据不会丢失。

事务隔离

之前说过事务的特性么，事务的隔离性其实就是在并发的情况下，事务之间不受影响。并发场景下会出现一些问题，首先是脏读，就是读到了一些脏数据。比如事务A读到了事务B未提交的数据，然后事务B又回滚了，导致事务A读到了无效数据。第二个是不可重复读，事务A读了两次数据，比如余额都是100，但是在两次读取数据之间，事务B修改了余额为50，并且提交了。这样事务A读到的是50。第三个就是幻读的问题。事务 A 按条件查询，期间事务 B 插入符合条件的新数据并提交，导致 A 再次查询时多了 “新记录”，行数变了。针对这三个问题，Mysql也有四种隔离级别，首先是读未提交，一个事务可以读到另一个事务未提交的数据，它并没有解决并发场景下事务的安全性，但是它基本不加锁，性能最高，但数据可靠性最差。第二个是读已提交，一个事务只能读取另一个事物提交的修改，它解决了幻读的问题，存在不可重复的问题。第三个是可重复读，它的定义就是一个事务多次读取同一个数据结果始终保持一致性，避免了脏读和不可重复读的问题，但是对于幻读的情况，在InnoDB通过临键锁解决了幻读的问题，最后是安全性最高的串行化，解决了所有的事务并发安全问题，因为他给全表加锁了，这样数据的读写都保证串行，但是效率比较差。 InnoDB 存储引擎中是通过MVCC完成的读已提交和可重复读的事务隔离级别。

MVCC

解决脏读，不可重复读和幻读，可以通过加锁的方式解决，但是写加锁，会阻塞读操作，效率低，InnoDB采用了MVCC这种无锁的机制来解决这个问题。MVCC 的实现依赖三个核心组件：隐藏列、undo 日志、Read View（读视图）。首先说一下隐藏列，InnoDB给每一行都添加了三列隐藏列，这三列都有不同的作用，首先是第一个记录最近一次修改该行的事务ID，第二个是回滚指针，指向该行数据存储在undo log的历史版本，形成一个版本链。第三个是DB_ROW_ID，这个就是如果某个表没有主键也没有唯一索引，自动将该列作为隐藏的主键。然后是undo log,当事务修改的时候，会更新隐藏列中的最近修改事务ID，和指针指向之前的事务数据，而旧版本的数据都写入了undo log，形成类似于单向链表的版本链，方便回滚。而Read View读视图，一般是和隐藏列中最近事务ID进行判断，read View中也有几个参数，当前活跃的事务列表，最小事务ID，下一个会分配的事务ID，这个比当前的事务ID都大。当前事务自己的ID。通过这四个参数和最近修改的事务ID，进行比较，从版本链中筛选出可见的版本。达到需要的事务隔离级别，之前说MVCC实现了RC和RR
RC是怎么做的？RC解决了脏读的问题，RC的事务隔离级别下每次select的时候都会生成一个read view，这样只能看到在当前select之前提交过的事务，这样未提交的事务数据就不可见。解决了脏读，但是还是会有不可重复读的问题，事务A第一次生成了view1视图，事务B修改了数据并提交，事务A第二次生成了view2视图，事务B的数据是可见的，这样查询到的就是事务B修改的数据。对于像银行这种要求可重复读的场景下就有问题了，但是我认为在电商这种场景下RC是可以的，并且符合业务。
RR解决了不可重复读的问题。事务启动后，第一次执行 SELECT 时生成 Read View，之后所有查询都复用这个 Read View，不再更新。同一事务中，无论其他事务是否提交，始终看到的是 “事务启动时的快照”。比如事务A第一次生成view1视图，事务B修改并提交了数据，但是对于版本链来说最近的一次视图依然是view1，事务B更新的数据对于它来说是不可见的。两次查询结果就会一致。刚刚说的快照读，对于 “当前读”，是保证 “数据一致性” 的关键：通过加锁读最新版本，确保后续修改的安全性，适合需要精确操作最新数据的场景（如订单支付、库存扣减）。通过临键锁阻止其他事务插入新记录，从物理上避免行数变化。解决了幻读的问题。
MVCC其实是读的时候不加锁，通过版本链和Read view实现的无锁读，而写的时候还是会加行锁的。

MySQL的锁

根据锁的范围，分为行锁和表锁，行锁中又有临键锁，记录锁和间隙锁，主要的使用用途是在排它锁，是否走索引时决定要不要退化。根据读写权限和并发的兼容性，分成共享锁和排他锁，共享锁只对读权限加锁，也称之为读锁，并且不同事务可以同时对不同行加读锁。而排他锁也称为写锁，会对读写权限都加锁，并且加锁后拒绝其他任何锁。对于读锁无论是否走了索引，都只会对某一行加读锁。其他事务就对该行不可读。对于排他锁来说又分为是否走索引，走的是聚簇还是非聚簇，上不一样的锁范围。如果没走索引，会直接上表锁。如果走了聚簇索引，又分为等值查询和范围查询，等值查询如果查到了就该行的记录锁，也就是对应的聚簇索引行。如果没查到，比如查询的聚簇索引主键id是3，就会id的上一条和下一条，加间隙锁，比如上一条是2下一条是4，上左开右开的开区间，（2，4）上锁。如果是范围查询，查询到了会对范围上记录锁和间隙锁，没查到的话上间隙锁。如果走非聚簇索引，等值查询命中时：会对非聚簇索引记录加临键锁，同时对对应的聚簇索引记录加记录锁，没查到会退化成间隙锁，而范围查询无论是否查到都不会退化。为了事务隔离性可以选择加锁。

MySQL索引结构

按照索引结构分可分为B+树索引、哈希索引、全文索引和空间索引。比较常用的就是B+树索引，在innodb存储引擎下，只要3-4层结构，就可以存储千万行数据。

哈希索引仅适合等值查询，不支持范围和排序
全文索引专为文本检索设计，依赖分词和倒排索引
空间索引用于地理数据，应用场景有限
MySQL索引下推
1
select * from tb_user where age > 20 and reward = 10000;
上面的语句会导致reward不走联合索引，age走联合索引。在mysql5.6之前不使用索引下推，该语句会执行过程是这样的，innodb会定位到二级索引中age大于20的一条数据，并拿到主键的值，然后进行回表操作找数据，判断该数据的reward是否等于10000，不等于就跳过，等于就返回客户端，如此往复。每次都会回表再判断reward是否满足条件。5.6以后引入的索引下推，会再联合索引中就进行判断，比如一条数据满足了范围查询的age，会直接判断联合索引中reward是否满足条件，不满足就跳过，满足了才会回表查询数据。大大减少了回表次数，减少IO，提高查询效率。
MySQL中查询执行过程

执行一条SQL会先通过连接器建立链接，检验用户身份管理连接，接着会查询缓存，命中缓存直接返回，查询缓存会将执行sql 的哈希值和结果集放入缓存中，但是有点鸡肋了，因为查询大小写保持一致，结果集超过了设置的缓存大小都会缓存失败，并且只要表更新就会自动清除缓存，所以在8.0以后就删除了。接着就会进入解析器，这一步会语法分析，判断是否写错了语句。下一步进入执行器，这里会判断表或者字段是否存在，并会经过mysql的优化器，索引优化就在这里，然后去存储引擎拿对应的数据。
varchar(n) 中 n 最大取值为多少？
首先mysql规定了除了text和blobs这种大对象类型以外，其他所有列加起来最多存储65535个字节。varchar中的n代表的是最大存储的字符数量，而不是字节数，所以根据你选择的字符集是有关系的，假如选择。ASCII作为字符集，一个字符占用一个字节。首先一行存储的数据不只有真实数据，mysql默认的dynamic行格式还有对应的null标识和真实数据占用的字节数。假如现在一行只有一列为varchar类型，没有设置not null，那么null标识会占用一个字节0x01，因为varchar为可变字符，要算变长字段长度列表长度，这个列表长度规定允许最大存储字节数大于255字节，会用2字节表示变长字段长度列表。小于用1字节表示，因为一行只有一列，所以1X2占用2字节，加上允许null，一共占用3字节，65535-3=65532。所以在使用ASCII字符集和未设置非空的前提下n最大取值65532。如果用urf-8，占用3字节，n最大为65532/3=21844。多字段计算如下：
行溢出
如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。不同的行格式处理行溢出的方式不同，compact会在真实数据处保存该列的部分数据，把剩下的数据放溢出页中，并在真实数据处记录20字节的溢出页的地址。而mysql5.7以后默认使用的dynamic行格式，在真实数据处就不会保存该列的数据而且直接通过20字节的地址指向溢出页。真实数据都在溢出页中。
Explain关键字参数

先看 type：若为 ALL 或 index → 未有效使用索引。
再看 key：若为具体索引名 → 索引生效；若为 NULL → 索引未生效。
key_len可以判断使用索引的字节数
结合 possible_keys：若为 NULL → 无可用索引，需新建索引。


取值	含义	是否使用索引
system	表中只有一行数据（如系统表），是 const 的特例	是（最优）
const	通过主键或唯一索引查询，最多返回一行数据（如 WHERE id = 1）	是
eq_ref	多表连接时，被连接表通过主键或唯一索引匹配，每行只匹配一次	是
ref	非唯一索引匹配，可能返回多行（如普通索引查询 WHERE name = ‘张三’）	是
range	索引范围扫描（如 WHERE id > 10 AND id < 100、IN、BETWEEN）	是
index	扫描整个索引树（全索引扫描，比全表扫描略好，但仍低效）	是（但低效）
ALL	全表扫描（遍历整个表找数据，无索引可用）	否（最差）