1、MySQL执行计划
执行计划——一条sql语句的执行过程
explain
官网
2、索引
数据结构
设计原则
优化
失效
回表
索引覆盖
最左匹配
索引下推
聚簇索引
用途：提高sql语句执行效率
索引和实际的数据都是存储在磁盘的，只不过在进行数据读取的时候会优先把索引加载到内存中
存储引擎：不同的数据文件在磁盘的不同组织形式
3、索引储存数据的格式
格式——>K-V——>数据结构——>hash表——>树——>为什么是B+树？
当索引非常大的时候，分块读取
分而治之
IO问题：减少IO量/减少IO次数
操作系统
局部性原理：
- 时间局部性：之前被访问过的数据很有可能再次被访问
-
空间局部性：数据和程序都有聚集成群的倾向，具备某些特征的数据可以放在一起
磁盘预读：内存跟磁盘在进行交互的时候有一个最小的逻辑单元，这个单位称之为页，或者datapage，大小一般是4k或者8k，由操作系统决定，我们在进行数据读取的时候，一般会读取页的整数倍，也就是4k、8k、16k，innodb存储引擎在进行数据加载的时候读取的是16kb的数据
hash表
需要比较好的hash算法，如果算法不好的话，会导致hash碰撞，hash冲突，导致数据散列不均匀
当需要进行范围查找的时候需要挨个遍历，效率比较低
memory的存储引擎支持的就是hash索引，同时注意innodb存储引擎支持自适应hash
二叉树
BST
AVL
红黑树
劣势：当需要向这些树中插入更多数据的时候，会导致当前树变得非常高，加大读取的次数，影响查询效率
MySQL索引数据结构——B+Tree
B+Tree是在BTree的基础之上做的一种优化，变化如下：
1、B+Tree每个节点可以包含更多的节点，这个做的原因有两个，第一个原因是为了降低树的高度，第二个原因是将数据范围变为多个区间，区间越多，数据检索越快
2、非叶子节点存储key，叶子节点存储key和数据
3、叶子节点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高
注意：在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构，因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找
一般情况下，三到四层的B+Tree足以支持千万级别的数据存储
key要尽可能少的占用存储空间
4、聚簇索引和非聚簇索引
索引的分类：
主键索引——>主键字段
唯一索引——>唯一字段
普通索引——>非主键非唯一
全文索引——>全文检索——>lucene、solr、es
组合索引——>表中的多个字段值可以共同构成索引
索引并不是越多越好：
1、索引的维护会非常麻烦
2、占用的存储空间变大，会导致io增多
1、一个表中只能有一个索引吗？
可以按照需求来创建多个索引，但不要太多
2、每一个索引是一棵B+树，还是所有的索引共用一棵B+树？
一个索引一棵B+树
3、如果有多棵B+树的话，那么数据存储几份？
1份
4、那么其他索引的叶子节点放什么呢？
主键（不太精准）跟数据绑定存储的索引列的值
聚簇索引：数据跟索引绑定在一起的叫做聚簇索引
非聚簇索引：数据跟索引分开存储的叫做非聚簇索引
数据跟索引存储在一起的叫做聚簇索引，没有存储在一起的叫做非聚簇索引
innodb存储引擎在进行数据插入的时候，数据必须要跟某一个索引列存储在一起，这个索引列可以是主键，如果没有主键，选择唯一键，如果没有唯一键，选择6字节的rowid来进行存储
数据必定是跟某一个索引绑定在一起的，绑定数据的索引叫做聚簇索引
其他索引的叶子节点中存储的数据不再是整行的记录，而是聚簇索引的id值
innodb中既有聚簇索引也有非聚簇索引
myisam中只有非聚簇索引
存储引擎：不同的数据文件在磁盘的不同组织形式
索引的选择性——>基数Distinct Value——>dv个数/count——>>80%适合创建索引
id,name,age,gender
id是主键，name为普通索引
id是聚簇索引，name对应的索引的B+树上的叶子节点存储的就是id值
回表
id,name,age,gender
id主键，name普通索引
select * from table where name=‘zhangsan’;
先根据nameB+树匹配到对应的叶子节点，查询到对应行记录的id值，再根据id去id的B+树中检索整行记录，这个记录就称为回表，要尽量避免回表操作
索引覆盖
id,name,age,gender
id主键，name普通索引
select id,name from table where name=‘zhangsan’;
根据name的值去nameB+树检索对应的记录，能获取到id的属性值，索引的叶子节点中包含了查询的所有列，此时不需要回表，这个过程叫做索引覆盖，using index的提示信息，推荐使用，在某些场景中，可以考虑将要查询的所有列都变成组合索引，此时会使用索引覆盖，加快查询效率
最左匹配
创建索引的时候可以选择多个列来共同组成索引，此时叫做组合索引或者联合索引，要遵守最左匹配原则
id,name,age,gender
id主键，name,age组合索引
select * from table where name='zhangsan' and age=12;
select * from table where name='zhangsan';
select * from table where age=12;
select * from table where age=12 and name='zhangsan';
索引下推
没有索引下推之前：
先根据name从存储引擎中拉取数据到server层，然后在server层中对age进行数据过滤
有了索引下推之后：
根据name和age两个条件来做数据筛选，将筛选之后的结果返回给server层
client（最上面）
server（中间）
存储引擎（最下面）
索引下推默认开启
架构：
客户端：jdbc、navicat、sqlyog
Server端：连接器——>管理连接、验证权限
分析器——>词法分析、语法分析——>AST——>antlr、calcite
优化器——>RBO规则、CBO成本
执行器——>执行具体的sql语句
存储引擎
针对特定场景，进行索引优化
表：cityName(Varchar) 5 20——>前缀——>前3,4,5,6,7
alter table citydemo add key(city(7)
hive
mysql
OLAP——>联机分析处理——>数据仓库——>hive——>对历史数据做分析，产生决策性的影响
OLTP——>联机事务处理——>数据库——>mysql、oracle、db2——>支撑业务系统，在很短时间内返回结果
kylin——>cube，将所有可能预先存在的结果都计算出来，直接去拿结果，而不是临时计算。measure、daimond
5、如何回答面试中问到的优化问题？
技术+表达方式
工作中做过很多sql的优化，一般的优化我们并不是出现了问题才进行优化的，在进行数据库建模和数据库设计的时候会预先考虑到一些优化问题，比如表字段的类型、长度等等，包括创建合适的索引等方式，但是这种方式只是提前的预防，并不一定能解决所有的问题，所以当我们生产环境中已经出现sql问题之后我会从数据库的性能监控，索引的创建和维护、sql语句的调整，参数的设置、架构的调整等多个方面去进行综合考虑，性能监控会选择show profiles,performance_schema来进行监控，索引。。。参数。。。在我最近做的一个XX项目中，出现了XXX问题，我通过分析执行计划以及XXX的方式顺利解决了这个问题，并且在公司做了技术分享，详细了解对应数据的知识，在另外的项目中，。。。。
6、事务、锁、MVCC基本概念
ACID——四个特性实现的原理
锁——乐观锁、悲观锁、间隙锁、行锁、表锁、记录锁、自增锁、意向锁
MVCC——多版本并发控制——提高读写效率
锁
按粒度来分：行锁、表锁
按读写来分：共享锁、排他锁
意向锁（表锁的一类）
乐观锁、悲观锁
记录锁、间隙锁、临键锁
当前读：读取的是数据的最新版本，总是读取到最新的数据——select … lock in share mode，select … for update，update，delete，insert
快照读：读取的是历史版本的记录——select
四个隔离级别
读未提交
读已提交（RC）
可重复读（RR）——默认隔离级别
串行化
读读：不会存在任何问题的，也不需要并发控制
读写：会有线程安全问题，会造成脏读、幻读、不可重复读，需要进行并发控制（锁）MVCC
写写：会有线程安全问题，存在更新丢失问题
MVCC：多版本并发控制，解决数据并发读写问题
能否读取到刚刚修改的最新的结果值？
RC：可以读取到最新的结果记录
RR：不可以读取到最新的结果记录
⬇
MVCC
⬇
可见性算法
7、MVCC是如何实现的？
MVCC多版本并发控制指的是维持一个数据的多个版本，使得读写操作没有冲突，快照读是mysql为了实现MVCC的一个非阻塞读功能
第一部分：隐藏字段
每一行记录上都会包含几个用户不可见的字段
DB_TRX_ID：创建或者最后一次修改该记录的事务id
DB_ROW_ID：隐藏主键
DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本——undolog
第二部分：undolog——回滚日志（保存数据的历史版本状态）
当不同的事务对同一条记录做修改的时候，会导致该记录的undolog形成一个线性表，也就是链表，链表的链首是最新的历史记录，而链尾是最早的历史记录
现在有了事务4，那么事务4读取到的数据是哪一个版本的数据？一定有对应的规则，需要按照规则来进行判断读取
第三部分：readview——>事务在进行快照读的时候产生的读视图，来进行可见性判断，可见性判断是由可见性算法来确定的
trx_list——当前系统活跃的事务id
up_limit_id——活跃列表中事务最小的id
low_limit_id——当前系统尚未分配的下一个事务id
能否读取到刚刚修改的记录值？
可见性算法
1、首先比较DB_TRX_ID<up_limit_id，如果小于，则当前事务能看到DB_TRX_ID所在的记录，如果大于等于进入下一个判断
2、接下来判断DB_TRX_ID>=low_limit_id，如果大于等于则代表DB_TRX_ID所在的记录在Read View生成后才出现的，那么对于当前事务肯定不可见，如果小于，则进入下一步判断
3、判断DB_TRX_ID是否在活跃事务中，如果在，则代表在Read View生成时刻，这个事务还是活跃状态，还没有commit，修改的数据，当前事务也是看不到，如果不在，则说明这个事务在Read View生成之前已经开始commit，那么修改的结果是能够看见的
经过当前可见性算法的判断之后，可以得到结论，能看到修改的记录
readview生成的时机是不同的
RC：每次在进行快照读的时候都会生成新的readview
RR：只有在第一次进行快照读的时候才会生成readview，之后的读操作都会用第一次生成的readview
如果当前的所有操作都是当前读，那么是不会产生幻读问题，只有当前读和快照读一起使用的时候才会产生幻读问题
加锁
select * from user where age = 20 for update
8、四个特点
原子性——>要么全部成功，要么全部失败——>它是通过undolog来实现的
原子性实现原理：undolog
- undolog是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用undo log来实现多版本并发控制（简称MVCC）
- 在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为undo log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用undolog中的备份将数据恢复到事务开始之前的状态
- 注意：undolog是逻辑日志，可以理解为：
- 当delete一条记录时，undolog中会记录一条对应的insert记录
- 当insert一条记录时，undolog中会记录一条对应的delete记录
-
当update一条记录时，它记录一条对应相反的update记录
undolog会形成一个链表，链首存储的是最新的旧纪录，链尾存放的是最旧的旧记录
undolog不会无限膨胀下去，会存在一个后台线程，purge线程，当发现当前记录不需要回滚且不需要参与MVCC的时候，就会把数据给清理掉
隔离性——>MVCC
持久性——>redolog——>两阶段提交——>WAL(write ahead log)——>先写日志，再写数据
一致性
随机读写——>慢
顺序读写——>快
因为随机读写的效率要低于顺序读写，为了保证数据的一致性，可以先将数据通过顺序读写的方式写到日志文件中，然后再将数据写入到对应的磁盘文件中，这个过程顺序的效率要远远高于随机的效率，换句话说，如果实际的数据没有写入到磁盘，只要日志文件保存成功了，那么数据就不会丢失，可以根据日志来进行数据的恢复
binlog（mysql server）
innodb（插件引擎）redolog、undolog
因为两种日志属于不同的组件，所以为了保证数据的一致性，要保证binlog和redolog一致，所以有了二阶段提交的概念
数据更新的流程
执行流程：
1、执行器先从引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回
2、执行器拿到数据之后会先修改数据，然后调用引擎接口重新写入数据
3、引擎将数据更新到内存，同时写数据到redo中，此时处于prepare阶段，并通知执行器执行完成，随时可以操作
4、执行器生成这个操作的binlog
5、执行器调用引擎的事务提交接口，引擎把刚刚写完的redo改成commit状态，更新完成
Redo log的两阶段提交
- 先写redolog后写binlog：假设在redolog写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redolog写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1.但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行的c的值就是0，与原库的值不同
- 先写biinlog后写redolog：如果在binlog写完之后crash，由于redolog还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了”把c从0改成1“这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。
9、MySQL分布式集群
主从复制
Master当发生data change之后，会写入binlog中去，I/O thread线程会读取我们的日志，放入Relaylog中去，进行SQL thread重放之后，同步到slave中
1、master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）
2、slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了
3、mysql复制至少需要两个mysql的服务，当然mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务
4、mysql复制最好确保master和slave服务器上的mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）
5、master和slave两节点间的时间需同步
MTS——>组提交
读写分离
客户端只能感知到只有一个数据库
这时候需要一个代理
mycat、shardingsphere
分库分表
垂直分表、水平分表
分片键
雪花算法