Mysql相关问题
为什么 B+ 树比 B 树更适合应用于数据库索引
查找一步步优化
哈希索引
自适应哈希索引就是用哈希表实现的，用哈希表实现索引的好处是非常明显的，查找单个指定数据只需要 O(1) 的时间复杂度
但是哈希表本身是无序的，不利于范围查询
二分查找优化
数据存有序数组，基于二分查找
二分查找o(logn),范围查询可以通过二分查找实现，但是不利于插入删除
二叉搜索树
为了优化插入删除，引入二叉搜索树
1.若任意结点的左子树不空，则左子树上所有结点的值均不大于它的根结点的值。
2.若任意结点的右子树不空，则右子树上所有结点的值均不小于它的根结点的值。
3.任意结点的左、右子树也分别为二叉搜索树
但是当树退化，高度差过大，会导致查询变慢，二叉搜索平衡树，AVL树
AVL树
自平衡二叉搜索树
维护AVL 树也是需要一定开销的，即当树插入/更新/删除新的数据时假设破坏了树的平衡性，那么需要通过左旋和右旋来维护树的平衡
AVL 树的查找效率为 O(log n)，也就是说，当树过高时，查找效率会下降
索引文件并不小，所以是存储在磁盘上的。
文件系统需要从磁盘读取数据时，一般以页为单位进行读取，假设一个页内的数据过少，
那么操作系统就需要读取更多的页，涉及磁盘随机 I/O 访问的次数就更多。
树越高，每一页读取的数据就越少
将数据从磁盘读入内存涉及随机 I/O 的访问，是数据库里面成本最高的操作之一
AVL树既有链表的快速插入与删除操作的特点，又有数组快速查找的优势，但是这并不是最符合磁盘读写特征的数据结构
B树
为了优化树高，将AVL树变为B树，二叉树变为m叉树，B树，多路平衡查找树
1.排序方式：所有节点关键字是按递增次序排列，并遵循左小右大原则；
2.子节点数：非叶节点的子节点数>1，且<=M ，且M>=2，空树除外（注：M阶代表一个树节点最多有多少个查找路径，M=M路,当M=2则是2叉树,M=3则是3叉）；
3.关键字数：枝节点的关键字数量大于等于ceil(m/2)-1个且小于等于M-1个（注：ceil()是个朝正无穷方向取整的函数 如ceil(1.1)结果为2);
4.所有叶子节点均在同一层、叶子节点除了包含了关键字和关键字记录的指针外也有指向其子节点的指针只不过其指针地址都为null对应下图最后一层节点的空格子;
关键字数量大于等于ceil(m/2)-1个且小于等于M-1个
二叉树变成 m 叉树，这个 m 的大小可以根据单个页的大小做对应调整，从而使得一个页可以存储更多的数据，从磁盘中读取一个页可以读到的数据就更多，随机 IO 次数变少，大大提升效率
B树相对于平衡二叉树的不同是，每个节点包含的关键字增多了，特别是在B树应用到数据库中的时候，数据库充分利用了磁盘块的原理（磁盘数据存储是采用块的形式存储的，每个块的大小为4K，每次IO进行数据读取时，同一个磁盘块的数据可以一次性读取出来）把节点大小限制和充分使用在磁盘快大小范围；把树的节点关键字增多后树的层级比原来的二叉树少了，减少数据查找的次数和复杂度;
但是只能通过中序遍历查询全表，当进行范围查询时，可能会需要中序回溯
B+树
B+树在B树的基础上加了以下优化：
1.叶子结点增加了指针进行连接，即叶子结点间形成了链表；
2.非叶子结点只存关键字 key，不再存储数据，只在叶子结点存储数据；
说明：叶子之间用双向链表连接比单向链表连接多出的好处是通过链表中任一结点都可以通过往前或者往后遍历找到链表中指定的其他结点。
范围查询时可以通过访问叶子节点的链表进行有序遍历，而不再需要中序回溯访问结点。
非叶子结点只存储关键字key，一方面这种结构相当于划分出了更多的范围，加快了查询速度，另一方面相当于单个索引值大小变小，同一个页可以存储更多的关键字，读取单个页就可以得到更多的关键字，可检索的范围变大了，相对 IO 读写次数就降低了。
由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当
B树与B+树区别
1、B 树非叶子结点和叶子结点都存储数据,因此查询数据时，时间复杂度最好为 O(1),最坏为 O(log n)，B+树只在叶子结点存储数据，非叶子结点存储关键字，且不同非叶子结点的关键字可能重复，因此查询数据时，时间复杂度固定为 O(log n)
2、B+ 树叶子结点之间用链表相互连接，因而只需扫描叶子结点的链表就可以完成一次遍历操作，B树只能通过中序遍历
为什么更适合B+树
1、B+ 树相比 B 树减少了 磁盘I/O 读写的次数。由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了
2、B+树查询效率更加稳定，只查叶子节点，稳定ologn
3、范围查询，扫表，因为叶子节点是双向链表连接，方便范围查询，B树只能中序遍历
B* 树
优化了B+树的子节点关键字个数，2/3m
B+树节点满的时候会分裂，B* 树，存有兄弟节点的指针，B* 树节点满时会检查兄弟节点是否满， 不满就放到兄弟节点，都满了，就各拿出1/3得到新的节点
从平衡二叉树、B树、B+树、B* 树总体来看它们的贯彻的思想是相同的，都是采用二分法和数据平衡策略来提升查找数据的速度
不同点是他们一个一个在演变的过程中通过IO从磁盘读取数据的原理进行一步步的演变，每一次演变都是为了让节点的空间更合理的运用起来，从而使树的层级减少达到快速查找数据的目的
索引相关
索引
快速访问数据表中的特定信息，提高检索速度
创建唯一性索引，保证数据库表中每一行数据的唯一性
创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度
在最频繁使用的、用以缩小查询范围的字段上建立索引。
在频繁使用的、需要排序的字段上建立索引
对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引
特殊字段text，不宜建立索引
索引优点
大大减少了服务器需要扫描的数据行数。
帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）
索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件
聚集索引，非聚集索引，覆盖索引
索引创建的辅助索引，非主键索引，辅助索引的索引树存储的是主键ID，key是该字段，辅助索引（也成二级索引）存储的数据是主键值，称为非聚集索引
主键索引，存储的是一整行的数据，key是主键ID，叫做聚集索引，即叶子节点中，存储的是整行数据
如果通过辅助索引查询的数据不是主键值，要查的是整行数据，就需要通过辅助索引查到主键，再根据主键去主键索引查整行数据
这个回到主键索引树上去查找的过程，叫做回表
需要经过再一次的逻辑IO访问
减少回表，覆盖索引
不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询.
再辅助索引树上完成查询就是覆盖索引，不需要回表
覆盖索引不需要回表，查询操作在辅助索引树上就可以完成，减少了回表次数，因此可以减少大量的IO次数
对于统计而言，假设操作在覆盖索引上可以完成，也可以减少大量的IO操作
联合索引，最左匹配原则
创建了一个联合索引indext(a,b,c)，实际上是创建了三个索引
MySQL建立联合索引时会遵循最左前缀匹配的原则，在检索数据时从联合索引的最左边开始匹配
索引生效，失效条件
Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效
创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处
(1) select * from myTest where a=3 and b=5 and c=4; —- abc顺序
abc三个索引都在where条件里面用到了，而且都发挥了作用
(2) select * from myTest where c=4 and b=6 and a=3;
where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样
(3) select * from myTest where a=3 and c=7;
a用到索引，b没有用，所以c是没有用到索引效果的
(4) select * from myTest where a=3 and b>7 and c=3; —- b范围值，断点，阻塞了c的索引
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
(5) select * from myTest where b=3 and c=4; — 联合索引必须按照顺序使用，并且需要全部使用
因为a索引没有使用，所以这里 bc都没有用上索引效果
(6) select * from myTest where a>4 and b=7 and c=9;
a用到了 b没有使用，c没有使用
(7) select * from myTest where a=3 order by b;
a用到了索引，b在结果排序中也用到了索引的效果，a下面任意一段的b是排好序的
(8) select * from myTest where a=3 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort
(9) select * from mytable where b=3 order by a;
b没有用到索引，排序中a也没有发挥索引效果
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引范围条件右边的列
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
is null,is not null也无法使用索引 —- 此处存在疑问，经测试确实可以使用，ref和const等级，并不是all
like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作。
所以设计索引，范围查询要放在最后面，列过滤的频繁程度越高，越放在前面
大库DDL操作
修改表结构，字段，创建表等
1、不要在业务高峰期执行
2、执行DDL前，先看一下库中是否有未提交的事务，注意查看事务information_schema.innodb_trx表
3、随时关注服务器日志状况，已有问题要先行解决。show processlist也可以发现一些问题
4、特别危险的操作一定先在预生产环境或测试环境先行模拟，评估风险。
5、尽量避免 kill 会话进程，可能会在某些情况造成数据问题。
6、研发时，预计表就会比较大的时候，要多评审几次，多留一些预置字段，避免DDL操作。
大表优化
1、分库分表，能不分就不分
MySQL 是关系数据库，数据库表之间的关系从一定的角度上映射了业务逻辑。任何分库分表的行为都会在某种程度上提升业务逻辑的复杂度
分库分表会带来数据的合并，查询或者更新条件的分离，事务的分离等等多种后果，业务实现的复杂程度往往会翻倍或者指数级上升。所以，在分库分表之前，不要为分而分，去做其他力所能及的事情吧，例如升级硬件，升级，升级网络，升级数据库版本，读写分离，负载均衡等等。所有分库分表的前提是，这些你已经尽力了
2、某些字段垂直拆分
频繁更新的字段，拆分出去，减少update压力
3、日志表，大增长表，水平拆分
4、慢sql调优，慢sql记录，然后区分子服务拆分
超大分页问题
offset并不是跳过N行，而是都查出来，舍弃前面的跳过行，当跳过很大时，就会比较慢，效率比较低
先锁定id，然后关联根据id查，或者先预估数据，缓存
纵向分表与横向分表
纵向分表：把不经常使用的，按列拆分出去，单独查询
横向分表：按行分表
分布式解决
1、单数据库可以用本地事务搞定，使用多数据库就只能通过分布式事务解决了。
常用解决方案有：基于可靠消息（MQ）的解决方案、两阶段事务提交、柔性事务
2、在使用 SQL 时 order by， limit 等关键字需要特殊处理，一般来说采用分片的思路：
先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终得到结果
3、分库分表id问题：UUid, 号段模式
mysql隔离级别
mysql事务的隔离性
脏读
读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，读到了最终不一定存在的数据
可重复度、不可重复读
可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的
不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响
针对更新
幻读
幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用
隔离级别
读未提交（READ UNCOMMITTED）
读提交 （READ COMMITTED）
可重复读 （REPEATABLE READ）
串行化 （SERIALIZABLE）
隔离强度逐渐增强，性能逐渐变差
查看隔离级别
show variables like ‘tx_isolation’
设置隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
MySQL事务隔离其实是依靠锁来实现的，加锁自然会带来性能的损失。而读未提交隔离级别是不加锁的，所以它的性能是最好的，但同时也是安全系数最低的
mysql默认可重复读
读未提交
不加锁，没有隔离
可以读到其他事务未提交的数据，但没有办法保证你读到的数据最终一定是提交后的数据，如果中间发生回滚，那就会出现脏数据问题
读提交
读提交就是一个事务只能读到其他事务已经提交过的数据，也就是其他事务调用 commit 命令之后的数据。但是在同一事务中，事务的不同时刻同样的查询条件，查询出来的记录内容是不一样的，事务A的提交影响了事务B的查询结果，这就是不可重复读
可重复度
实现可重复度：MVVC版本控制
一行记录可能实际上有多个版本，每个版本的记录除了有数据本身外，还要有一个表示版本的字段，记为 row trx_id，也就是事务id
可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读提交则是每次执行语句的时候都重新生成一次快照，并符合以下规则：
1、当前事务内的更新，可以读到
2、版本未提交，不能读到
3、版本已提交，但是却在快照创建后提交的，不能读到
4、版本已提交，且是在快照创建前提交的，可以读到
并发写问题：
事务A执行 update 操作， update 的时候要对所修改的行加行锁，这个行锁会在提交之后才释放。而在事务A提交之前，事务B也想 update 这行数据，于是申请行锁，但是由于已经被事务A占有，事务B是申请不到的，此时，事务B就会一直处于等待状态，直到事务A提交，事务B才能继续执行，如果事务A的时间太长，那么事务B很有可能出现超时异常
加锁分为有索引和无索引：有索引可以直接拿到id对该行加锁，无索引，mysql会把所有行都加锁，然后再逐一比对释放锁，开销极大，所以要合理设计索引
解决幻读：
使用间隙锁，mysql的底层是b+树，叶子节点是双向链表，会把索引拆分几个区间，对于操作的记录加行锁，操作记录的两边加间隙锁（Next-Key），防止其他事务插入
事务不会读到其他事务对已有数据的修改，及时其他事务已提交，也就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。但是，对于其他事务新插入的数据是可以读到的，这也就引发了幻读问题
串行化
读的时候加共享锁，也就是其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读
串行化是4种事务隔离级别中隔离效果最好的，解决了脏读、可重复读、幻读的问题，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束
一条sql语句是如何执行的
查询语句：
1、检查该语句是否有权限，没有权限直接返回错误
2、有权限直接查询缓存，有缓存直接返回
3、分析器提取语句的关键词，确认是什么操作
4、选择优化方案
5、权限校验，通过则调引擎接口查询结果
更新语句：
先查询，然后更新，记录日志，会有两阶段记录日志，redolog， binlog两个日志 模块，解决数据一致性问题
判断redo log 是否完整，如果判断是完整的，就立即提交。
如果redo log 只是预提交但不是commit状态，这个时候就会去判断binlog是否完整，如果完整就提交 redo log, 不完整就回滚事务
主从复制与一致性问题
异步复制，不关心从库，直接返回
同步复制，等待从库同步完毕，事务执行时间会延长，强一致性
半同步复制：等待从库同步一个，写到redolog后，然后主的应答，从库剩余的写入
主从复制原理
1、mysql主库事件记录在binlog
2、主库将日志推到从库relog
3、从库根据relog做变更操作
乐观锁，悲观锁
乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量
悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁
explain分析查询语句
explain是否使用索引，是否使用子查询，可选择索引 ，实际使用索引，扫描的行数
索引查询类型：
system 表只有一行
const 主键，索引查询 只有一行匹配
eq_ref 连接查询，只有一行
ref 使用非唯一索引
range 范围查询
index 只扫描索引树
all全表扫描
优化数据访问
不要select * ，只返回需要的数据
只返回需要的行 limit
切分大查询
查询缓存的问题QC
同样的sql语句可以缓存，频繁更新的那种不适合缓存
缓存失效比较频繁的原因就是，只要我们一对表进行更新，那这个表所有的缓存都会被清空
优化like查询
优化1：icp
like查询不能使用索引，优化：mysql ICP特性，5.6开启了
关闭ICP特性情况下：
storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层
server 层：对返回的数据，使用后面的where条件过滤，直至返回
打开情况下：
storage层：
首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤
将满足的index filter条件的索引记录才去回表取出整行记录返回server层
不满足index filter条件的索引记录丢弃，不回表、也不会返回server层
server 层：对返回的数据，使用table filter条件做最后的过滤。
使用ICP后，直接就去掉了不满足index filter条件的记录，
省去了他们回表和传递到server层的成本
优化：配合复合索引使用，利用icp特性，减少不必要的数据扫描。然而对于单列like，无法利用icp特性，但是可以覆盖索引减少回表
优化2：全文索引
MySQL 5.6开始支持全文索引，可以在变长的字符串类型上创建全文索引，来加速模糊匹配业务场景的DML操作
优化查找字符串的方式
改写为全文索引的匹配方式：写SQL为全文索引匹配的方式：match(nickname) against(‘123’)
优化3：生成列
利用MySQL 5.7的生成列模拟函数索引的方式解决，具体步骤如下：
虚拟列：当行记录被访问的时候自动计算得到，不占用存储空间。支持此列上创建二级索引。
物理列：当行记录被插入或更新时候自动计算并保存起来，占用存储空间。支持此列上创建主键和二级索引
利用内置reverse函数将like ‘%风云’反转为like ‘云风%’，基于此函数添加虚拟生成列。
在虚拟生成列上创建索引。
将SQL改写成通过生成列like reverse(‘%风云’)去过滤，走生成列上的索引
数据库基础
客户端 - 连接器 - 查询缓存 - 分析器 - 优化器 - 执行器 - 引擎
查询空闲链接 show processlist
Command列显示为Sleep的这一行，就表示现在系统里面有一个空闲连接，超过超时时间，连接会断开
不要delete删除数据
尤其是大表，delete物理删除数据会产生空间碎片，最好采用逻辑删除
elete物理删除既不能释放磁盘空间，而且会产生大量的碎片，聚集索引非线性增加，导致索引频繁分裂，影响SQL执行计划的稳定性；
同时在碎片回收时，会耗用大量的CPU，磁盘空间，影响表上正常的DML操作
比较耗时，也会阻塞表上正常的DML运行，同时需要占用额外更多的磁盘空间，对于RDS来说，可能会导致磁盘空间瞬间爆满，实例瞬间被锁定，应用无法做DML操作，所以禁止在线上环境去执行
主键达到上限
自增ID达到上限用完了之后，分为两种情况：
如果设置了主键，那么将会报错主键冲突，改为bigint。
如果没有设置主键，数据库则会帮我们自动生成一个全局的row_id，新数据会覆盖老数据
解决方案：
表尽可能都要设置主键，主键尽量使用bigint类型，21亿的上限还是有可能达到的，比如魔兽，虽然说row_id上限高达281万亿，但是覆盖数据显然是不可接受的
count * 和count 1不是全表扫描
MySQL 都会用成本最小的辅助索引查询方式来计数
binlog redolog undolog
MVCC
快照读与当前读
MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作
MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作
undo日志
MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来