mqsql底层实现及索引优化

数据结构可视化网站：
链接: https://www.cs.usfca.edu/~galles/visualization/Algorithms.html.

理解：查找数据时，通过索引批量的把大概某块区域的数据从磁盘读取到内存中，再在内存中查找，因为加载数据到内存是非常耗时的，即减少读取磁盘。

1.索引数据结构

（1）二叉树

（2）红黑树（二叉平衡树，会自动平衡数据结构）

（3）B-Tree

（4）B+Tree （mysql的底层索引 InnoDB）
<1>非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引。
<2>叶子节点包含所有索引字段。
<3>叶子节点用指针连接，提高区间访问的性能。
<4>叶子节点双向指针，指向前后节点的文件地址。
ps:MAX.Degree=4，节点中如果四个元素就会分裂
（5）Hash 索引
<1>对索引的key进行一次hash计算就可以定位出数据存储的位置。
<2>很多时候Hash索引要比B+Tree索引更高效。
<3>仅能满足“=”、“IN”，不支持范围查询(因此用的少)。
<4>hash冲突问题，计算后如果有相同的key，那么即在链表后面追加。

---

2.mysql的存储引擎（修饰数据库表）

（1）MyISAM(非聚集)：不支持事务。
<1>索引文件和数据文件是分离的，需要先查找索引，再查找数据，耗时。
<2>存储文件：.frm表结构文件、.MYD数据文件、MYI索引文件


（2）InnoDB(聚集)：
<1>表数据文件本身就是按B+Tree组织的一个索引结构文件。
<2>聚集索引-叶子节点包含了完整的数据记录。
<3>存储文件：.frm表结构文件、.ibd数据和索引。


<4>主键索引和非主键索引的区别

---

3.联合索引

排序的话是依次比较字段(name -> age -> position)，才进行排序，如果值相等的情况下，就存储主键。
（1）最左前缀优化原则：

4.读写分离

主从库:通过binlog文件，主库生成index&binlogs文件，从库有两个线程，一个i/o Thread，一个sql Thread，通过i/o线程从主库将index&binlogs文件拷贝到从库，sql Thread读取binlogs文件，写入到从库里面来。
实现读写分离的两种方式，
(1)proxy代理层分片
mycat、atlas、mysql-proxy在组件proxy配置读写表，设置新的端口。
(2)jdbc应用层分片
shardingsphere、TDDL

区别:
(1)jdbc应用层分片性能高，因为不需要经过代理。
(2)proxy可以跨语言通讯，jdbc不支持。
(3)proxy不支持跨数据库，jdbc支持。

强制路由：可能因为网络原因无法同步数据到从表，可以使用强制路由来进行查询。

// 强制路由
/*master*/select * from ****

5.分库分表

垂直:
每个库(表)的结构不一样，数据(至少有一列做了关联)，并集是全量数据。
(1)垂直分库:把不同业务的数据分至不同的库里面。
(2)垂直分表:用户信息，名字密码分一张表，别的附属信息分一张表。
优点:
(1)拆分后业务清晰。
(2)数据维护简单。
缺点:
(1)如果单表的数据量大，写读压力大。
(2)受到某种业务的限制，业务往往会影响到性能。
(3)部分业务不能进行关联，只能通过接口调用关联，代码变复杂了。

水平:
每个库(表)结构一样，数据不一样，并集是全量数据。
优点:
(1)单库(表)数据保持在一定量，提高性能。
(2)提高了系统的稳定性和负载能力。
(3)切分的表的结构相同，程序改造较小。
缺点:
(1)数据的扩容很有难度，维护量大。
(2)拆分规则很难抽象出来。
(3)分片事务的一致性的问题部分业务无法join、只能通过java程序接口去调。

6.隔离级别

innoDB默认是可重复读。

7.MVCC

Multi-Version Concurrency Control(多版本并发控制)，提高数据的并发性能。实现了读提交(RC)和可重复度(RR)的问题。
RC是每个select语句生成一个readview，所以没办法解决可重复度的问题。
RR是以事务为单位，每一个事务生成一个readview，
(1)undo log
(2)版本链，是由undo log和回滚指针roll_pointer连接起来的。
(3)readview

creator_trx_id默认为0

8.行锁表锁

行锁:一行数据被修改但未提交。
表锁:
(1)索引失效，从行锁变为表锁，例如where子查询用了or。
(2)间隙锁，范围查询时产生的间隙锁。

9.ACID

原子性(Atomicity):undo log记录事务修改之前的数据。事务失败时会回滚会用到undo log。
一致性(Consistency):数据执行前后都是合法的，主键唯一，大小长度。
隔离性(Isolation):MVCC，隔离级别。
持久性(Durability):事务一旦提交，对数据库的改变就是永久性的，innoDB提供了一个缓冲buffer，数据会先从buffer中取。先将修改的数据写入redo log，再写入buffer里面。

10.索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。
在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

面试问题：
<1>为什么建议InnoDB表必须建主键，并且推荐使用整形的自增主键？
答：如果没有主键，InnoDB会自动找一个不重样的列来建立索引，如果没有不重样数据的列，则会自动建立一个隐藏列，我们是看不见的，以此来维护表，所以会消耗mysql的资源。如果使用UUID字符串，可能会插入到节点中间的位置，可能还会导致树进行分裂和平衡树节点，所以肯定没有整形的自增id快，因为自增id是一直在树的末尾进行添加。
<2>为什么非主键索引结构叶子节点存储的是主键值？
答：一致性和节省存储看空间。
<3>B-Tree和B+Tree的区别？
答：I.B-Tree的叶子节点没有双向指针，范围查找的时候无法直接在叶子节点进行快速的联系查找，只能反复的从根节点进行查找，效率没B+Tree高。
II.B-Tree没有冗余索引，树的高度很高，导致查询时间升高，B+Tree高度很低，降低了查询时间。