复习八股文--mysql
一、调优
调优时，先使用 EXPLAIN 查看mysql语句的执行计划,如
EXPLAIN select id from table；
显示如下：
调优从 type、key 和 Extra 三个字段入手；
1、type -- 查询的访问类型
常用查询的访问类型，由好至坏的循序如下：
system>const>ref>range>index>ALL
（1）system: 表只有一行数据（等于系统表),是const的特殊类型；
（2）const：表示通过索引一次就找到了；
（3）ref：使用了非唯一性索引进行数据的查找；
（4）range: 表示利用索引查询的时候限制了范围，在指定范围内查找，适用的操作符：=、<、>、is null、like、between、or等；
（5）index：表示对全表的全索引扫描；
（6）ALL：表示遍历全表。
一般来说，要保证查询达到range 级别，最好时到ref级别；
2、key
key表示使用的索引，如果为NUll，则表明没有使用索引。
3、Extra
出现的类型有：Using filesort、Using temporary、Using index、Using Index_condition、Using join buffer
（1）Using filesort： 无法利用索引进行排序，考虑对排序字段进行建索引或者更改排序字段。
（2）Using temporary： mysql 在查询结果排序时使用了临时表，常见于order by 和分组查询 group，建议对排序字段进行建索引或者更改排序字段进行优化。
（3）Using index：代表 select 操作中使用了覆盖索引，避免使用覆盖索引，就不会造成回表进行优化。
（4）Using index_condition：使用了索引下推，将数据在server层处理而非在存储引擎中处理进行优化。
（5）Using join buffer：使用了连接缓存。
二、索引
一、索引的基础知识点
索引的分类
聚簇索引：数据和索引存放在一起；
非聚簇索引：数据和索引分开存放；
（1）、索引并非越多越好，索引过多的情况下，1、会维护麻烦；2、占用的空间大，io增多。
（2）、一个索引有一课b+树，有多棵b+树，数据也只存放一份，其它索引的叶子结点存放跟数据绑定存储的索引列的值；
（3）、在innodb存储引擎中，数据进行插入的时候，需要跟某一索引进行绑定在一起，如果索引有主键，则使用主键；如果没有，有唯一键，那么使用唯一键，如果没有，那么使用6字节的rowId进行绑定。
二、为什么使用B+树？
回答：
一： mysql 存储的数据存在磁盘，索引也储存在磁盘中；
二：进行数据查询时，需要将数据读取至内存；
三：读取数据时，会以分块进行数据读取（读取页的整数倍）；
涉及 局部性原理和磁盘预读
（1）、局部性原理：
? ①时间局部性原理：被访问过的数据短时间内有可能，再次被访问；
? ②空间局部性原理：数据和程序都有聚集成群的倾向，具备某些特征的数据可以放在一起；
（2）、磁盘预读：内存跟磁盘进行交互时，有一个最基本的逻辑单位称之为页，也叫datapage，大小一般是4k或8k，进行数据读取的时候，读取的是页的整数倍。
四：使用的数据结构
b-树与b+树对比：
（1）b-树：
顺序记录数据，例：在下图中查找15
在磁盘块1中，p1指向存储比16的数值小的磁盘块，p2指向存储比16的数值大的磁盘块。当查找15时，根据p1的指向，来到磁盘块2进行查找，此时，p3指向存储比11的数值大的磁盘块，根据p3指向，来到磁盘块7进行查找，在这个块磁盘成功查找到15并返回；
优点：每个结点都包含key和data值，如果查找的值距离根节点近，则访问的速度更快；
缺点：每个结点都包含key和data值，需要消耗更多的存储空间；当查找的值距离根节点远时，则需要耗费较多的访问时间。
? 每个相邻的数值的元素在内存的存储位置可能不相邻。
（2）b+树
顺序记录数据，但是每个非叶子结点都不存储data数据，只有下标指向；结点数据data均存储在叶子节点中。
例：查询结点15
在磁盘块1中，p1指向存储比28的数值小的磁盘块，p2指向存储比28的数值大的磁盘块。当查找15时，根据p1的指向，来到磁盘块2进行查找，此时，p2指向存储比10的数值大和比17的数值小的磁盘块，根据p2指向，来到磁盘块5进行查找，在这个快磁盘成功查找到15并返回；注意，b+树叶子结点为链表相连。
优点：所有的叶子结点都以链表相连接，便于查找和遍历；只有叶子结点存储了数据data，可以节省更多的存储空间。
注意：（1）、mysql 的b+树，一般情况下，3~4层便足以支撑千万级别的数据量存储。
? （2）、建立数的key时，如果大于4字节事，使用varchar，小于4字节时，使用int；
? （3）、在满足系统的情况下，要让key尽可能的自增。
三、msql 常问名词
1、mysql结构
mysql 和 hive 的区别
名词解析
（1）回表： 例：select * from student where name = 'lin';
? 执行过程：先根据name 到b+树找到相对应的结点的id值，在根据id到id b+树读取整行记录，这样二次读表的行为即为回表；
（2）索引覆盖：例：select id，name from table where name = 'lin'；
? 执行过程：根据name的值到name B+树种获取相对应叶子结点种的数据，叶子结点种包含了所有要查询的字段，同时写出要查找的字段如例子种的 select id，name，这样的行为即为索引覆盖，索引覆盖的可以提高查询效率；
（3）最左匹配原则：建立联合索引时会遵循最左前缀匹配原则，即最左优先，在检索数据时，先联合索引的最左边开始匹配。
（4）主从复制
数据同步过程：
写入bin log 和relay log是顺序读写，sql thread 线程写入数据时的时候是随机读写，因此会造成延迟，mysql中使用MTS解决延迟问题；
（5）读写分离
数据量级别较大的情况下，一般会将数据存储在多个数据库中，其中，一个数据库负责存储数据并同步，另外的数据库负责查找数据，即为读写分离；
（6）分库分表
分库分表有水平分表和垂直分表俩种：
水平分表：根据数据库的数据条目进行分表存储；
垂直分表：拆分数据表，将不常用的字段列进行分表存储。
四、事务模块
四大属性：原子性，一致性，隔离性和持久性
1、原子性
原子性常用的总结就是：要么全部成功，要么全部失败。
实现原理：undo log（逻辑日志）
实现：
当delete一条记录时，undo log 记录一条相对应的insert语句；
当insert 一条记录时，undo log记录一条相对应的delete语句；
当update一条记录时，undo log 记录一条想放的updata记录
即，存储与当前操作相反的操作的sql语句
2、持久性
实现原理：redo log
实际的数据如果没有写入成功，只要日志存在，就可以根据日志来恢复数据；
注： 各日志归属的范围
bin log ---->mysql server
undo log 和 redo log ---->mysql innodb（存储引擎)
数据修改时，会同时在bin logn、undo log、redo log写入日志，bin log 用于数据同步，redo log和undo log 用于事务，数据恢复等功能。
3、隔离性
四种隔离级别：（1）读未提交；（2）读已提交（RC）;(3) 可重复读（RR)；（4)串行化；
隔离级别越高，效率越低
在进行并发操作时：
（1）读读操作：不会存在任何问题，不需要进行并发控制；
（2）读写操作：会有线程安全问题，需要进行并发控制，使用MVCC；
（3）写写操作：会有线程安全问题，有更新丢失问题（不同步）
名词解析：快照读、当前读
快照读：读取对应数据的历史版本（select操作）；
当前读：读取的最新数据结果（select lock in share mode；select for update/insert/delete 即增删改后进行数据查询
MVCC
MVCC： 多版本并发控制，是一个数据的多版本，使读写操作没有冲突，快照读时mysql为了实现MVCC的一个非阻塞功能。
实现原理： 隐藏字段+undo log+readview 三种工具组合实现
（1）隐藏字段
①DB_TRX_ID:最近修改事务id，记录创建当前记录或最后一个修改的事务的id；
②DB_ROLL_PIN:回滚指针，指向一条记录的上一个版本；
③DB_ROW_ID:隐藏主键
(2)undo log
回滚日，记录的时之前数据的历史版本
undo log 会形成一个链表，链首是最新的旧记录，链尾是最旧的旧记录。如下图
undo log 不会无限膨胀，会存在一个后台线程，当发现当前记录不需要回滚且不参与MVCC，旧会清理掉数据。
（3）readview
当事务在进行快照读时，会生成一个读视图来进行可见性判断，可见性判断是由可见性算法来确定的；
能否看到修改的数据，取决于可见性算法，可见性算法比较的时候，取决于readview中的结果值；
在不同的隔离级别的时候，生成的readview的时机是不同的：
RC：每次执行 快照读都会生成readview
RR：只有在当前事务第一次进行 快照读的时候会生成readview，之后的 快照读都会用当前的readview
如果当前的所有操作都是当前读，不会出现幻读问题，如果当前读和快照读混用，就会出现幻读问题，解决幻读的问题的方法是加锁；
一致性
即数据库中数据的一致
原子性，隔离性和持久性，都是为了保证一致性而做的工作。