Mysql索引

• InnoDB（聚集索引） B+树的叶子节点直接存储的数据，MyIsam（非聚集索引） B+ 树叶子节点存储的是数据的地址。
• 聚集索引：叶子节点包含了完整的数据记录（比如：InnoDB）。
• InnoDB（聚集索引）

• MyIsam（非聚集索引）

• 联合索引（索引最左前缀原理）

• Mysql数据库插入数据的时候就排序了，插入的时候排好的序。
• 如果存储引擎是InnoDB，Mysql数据库查询数据的时候，每次从磁盘加载到内存是16Kb，即为一页（页结构是InnoDB存储引擎里面的）。当一行数据不足16Kb的时候就会记载多行直到16Kb，这样就不用在查询一行数据读取一下磁盘（因为一次性加载了多行数据到内存，局部性原理），这样就会节省时间，提高效率。Mysql页图例如下：

如果主键ID不是自增的，因为ID是需要再插入的时候就要排好序的，此时会发生这种情况：第一页的大小已经达到16Kb，但是再次插入数据的时候主键ID的值要比第一页最后一个ID要小（插入的主键ID应该放在第一页），这时候就会把插入的这条数据放在第一页，并且把之前第一页最后的一条数据 "挤" 到第二页，这样会对性能造成损失，所以建议在创建主键的时候使用整型（int/big int），且主键自增，这样就不会出现 “挤数据” 这种情况。

两个页之间指针连接：

页目录：把 “用户数据区域” 几条数据合并成一个组，每一组里面的起始ID记录下来放在页目录里面，做为页目录里面的一个元素。这样我们在当前页查找数据的时候就不用在 “用户数据区域” 里面一条一条遍历了，直接先在页目录里面查找（二分查找） 我们需要的数据在哪一个组里面，然后在对应的组里面遍历就好了。

如果有很多页，但是我们要查询的数据在后面的页里面，我们此时要遍历页这个链表，这样会对性能造成影响。为了提高性能，我们可以模仿页目录，创建一个记录每一页起始ID的 “目录”，这样我们在查询的时候先在这个 “目录” 里面判断我们查找的数据是在哪一个页里面（二分查找），然后在遍历页里面数据。（这个整个结构就是B+树）

数据库B+树结构如下图：

• 如果每一页最多有两条数据，即如下图：

叶子节点（数据页）存的是整个表的数据。非叶子节点（索引页）起到加速查询的作用。

聚集索引：索引和数据放在一起。

查询的时候，如果是从第一个叶子节点向后轮询查找的叫做“全表扫描”。如果是从索引查找的叫做“走索引”。

• 联合索引 =>（最左前缀原则）

思考联合索引也把结构做成B+树（叶子节点也存放记录），怎么样？

答：虽然这样可以提高查询效率，但是这样会造成的不良结果是：1、数据的冗余（主键B+树叶子节点已经有记录数据了，这里的B+树叶子节点也有数据）。2、增加、更新数据的时候麻烦，因为每一个B+树叶子节点里面对应的数据都要添加或者更新。所以这种结构是不可取的。

优化联合索引：经过上面的分析，找到一个折中解决方法。我们可以把叶子节点里面的 数据记录 换成 主键ID。通过联合索引查找到对应的主键ID，然后再利用主键索引来查找对应的数据记录。（这种通过查找到主键ID，然后再拿着主键ID，在主键索引中查找数据的方法 叫做“回表”）。

• Innodb 中的 Buffer Pool缓冲池

Buffer Pool 缓冲池 是 Innodb中来缓存查询记录的一块区域（Buffer Pool是在内存中，默认是128M），Buffer Pool 缓冲池中的每一个页（16Kb）中包含一个控制块。

脏数据：当我们更新数据的时候，先更新Buffer Pool缓冲池页里面的数据，后续会有专门的线程定时去把新的数据同步到磁盘。

Buffer Pool缓冲池中的链表：

free链表：链表中的每个元素指向的是Buffer Pool缓冲池中空白的页

flush链表：链表中的每一个元素指向的是脏数据页

lru链表：最频繁使用的页在LRU链表的最前端，而最少使用的页在LRU链表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU链表尾端的页。LRU链表分为热数据区和冷数据区，冷数据区占了总链表的37%。冷数据区是不常访问的缓存页，热数据区是经常访问的缓存页。lru链表作用：用来淘汰不常被访问的缓存页。

在Innodb引擎中，有一个midpoint位置。 新读取到的页，虽然是最新访问的页，但并不直接放到LRU链表的首部，而是放到LRU链表的midpoint位置。midpoint位置是通过参数innodb_old_blocks_pct来控制的。表示新读取的页插入到LRU链表尾端的37%的位置。在Innodb中，把midpoint之后的链表称为LRU冷数据链表，之前的链表称为LRU热数据链表。可以简单的理解为LRU热数据链表中的页都是活跃的热点数据。

冷数据区域内的页如果被访问了两次，且两次访问的时间差大于1秒，这时候这个页就会从冷数据区域移动到热数据区域。限制两次访问的时间差大于1秒是因为：一个页里面可以放置很多条数据，当我们从磁盘中读数据写入当前页的时候，当前页在很短的时间内会被多次访问，此时我们需要给定一个时间限制。

• redolog（是InnoDB特有的）-》是物理日志，记录的是“在某个数据页上做了什么修改”

redolog作为异常宕机或者介质故障后的数据恢复使用

当我们更新数据的时候：

1、我们先修改buffer pool里面的数据 --》脏页

2、update语句生成 redo log

3、redo log持久化 (事务提交的时候持久化)

4、binlog 和 undolog持久化

4、修改成功

当我们在更新完数据以后，还没来得及把更新的数据持久化到磁盘，如果Mysql挂掉了；我们再次重启之后，如果我们查询之前没持久化的那条数据的时候，Mysql会把磁盘里面的老数据 和 redo log合并起来返回给我们。

• binlog(Mysql本身拥有的)-》是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这的c字段加1 ”（可以简单理解为记录的SQL语句）

binlog可以作为恢复数据使用，主从复制搭建

• undolog(是InnoDB特有的)

数据库事务开始之前，会将要修改的记录放到Undo日志里，当事务回滚时或者数据库崩溃时，可以利用UndoLog撤销未提交事务对数据库产生的影响。

在MySQL中，undo log日志的作用主要有两个：

1、提供回滚操作【undo log实现事务的原子性】

2、提供多版本控制(MVCC)【undo log实现多版本并发控制（MVCC）】

• 分库分表

分库分表中间件

proxy代理层：mysql-proxy、mycat、atlas

分库分表带来的问题：

1、ACID 分布式事务

2、维护成本

3、跨库查询问题

4、分布式全局唯一ID

--》解决方法--》（可以使用分库分表中间件：proxy代理层：mysql-proxy、mycat、atlas）

垂直拆分的特点：（结构拆分）

1、每个库（表）的结构都不一样

2、每个库（表）的数据都（至少有一列）一样

3、每个库（表）的并集是全量数据

优点：

1、拆分后业务清晰(专库专用按业务拆分);

2、数据维护简单、按业务不同放到不同机器上

缺点：

1、如界单表的数据量大、写读压力大

2、受某种业务来决定、或者被限制。也就是说一个业务往往会影响到数据库的瓶颈（性能问题)

3、部分业务无法关联 join、只能通过程序接口去调用，提供了开发复杂度。(商品信息、订单信息、会员信息)

水平拆分的特点：（内容拆分）

1、每个库（表）的结构都一样

2、每个库（表）的数据都不一样

3、每个库（表）的并集是全量数据

优点：

1、单库（表）的数据保持在一定的量（减少），有助于性能提高

2、提高了系统的稳定性和负载能力

3、切分的表的结构相同、程序改造较少

缺点：

1、数据的扩容很有难度维护量大

2、拆分规则很难抽象出来

3、分片事务的一致性的问题部分业务无法关联 join、只能通过程序接口去调用

分库

垂直拆分

水平拆分

分表

垂直拆分

水平拆分