一零六九、MySQL回顾总结

索引下推：在联合查询的过程中，根据联合索引包含字段直接过滤掉不满足的记录，减少回表次数（能用索引就用索引）
覆盖索引：查询字段包含了索引的全部字段
聚集索引：将常用的字段作为主键或聚集索引

undolog redolog binlog的区别和联系？
undolog（回滚日志）:用来实现事务的原子性和MVCC
redolog(重做日志)：用来实现事务的持久化操作，异步刷盘到磁盘
binlog(二进制日志):用来进行数据的备份和恢复，记录数据库所有的变更记录

mysql为什么采用树做索引?
Hash,树，跳表可以做索引，但Hash不支持范围查询，跳表不适合磁盘场景，所以选树来做索引

为什么选B+树做索引，而不是平衡二叉树，红黑树，B-树？
平衡二叉树追求绝对平衡，实现麻烦
红黑树多用于内容排序，在内存中去实现的，每个节点存放一个数据，树深度高，磁盘IO大
B-树存 Key和data，B+树存放data,树深度大于B+树，效率没有B+树高

聚簇索引和二级索引的区别？
聚簇索引是主键索引，二级索引是非主键索引，都是基于B+树实现
在B+树上，通过这棵树可以找到全部的数据，这就是聚簇索引
二级索引叶子节点只存放索引、主键ID，而聚簇索引的叶子节点存放行数据，具体来讲，二级索引就是主键索引的索引

mysql锁分类？

行锁和表锁
共享锁和排他锁
间隙锁和意向锁

InnoDB和MyIsAm有什么区别？

InnoDB支持事务，MyIsAm不支持
InnoDB支持行锁，MyIsAm支持表锁
InnoDB支持外键，MyIsAm不支持

数据库范式？
第一范式：每个属性都是原子性的，不可分割
第二范式：非主键完全依赖于主键
第三范式：非主键属性不依赖于其他非主键属性

反范式？
为了提高查询性能而在设计过程中有意引入冗余数据

事务隔离级别?分别解决了什么问题？
读未提交：读取到了还没提交的数据解决脏读
读已提交：读取到其他事务提交的数据解决不可重复读
可重复读：同一事务，读取同一条数据得到同样的结果
串行化：事务排序，不可发生相互冲突，就是慢，没有什么问题解决幻读

什么是脏读，脏写，幻读，不可重复读？

脏读：A事务和B事务读取同一个数据，A事务回滚，B事务读取到的就是脏数据
脏写：A事务和B事务写同一份数据，A事务回滚，B事务做的修改将会丢失
幻读：A事务更新数据的过程中，B事务新添加了数据，导致A事务执行完还有剩余数据
不可重复读：A事务读取同一个数据时，读取到了不同的数据

如何理解MVCC？
多版本并发控制，在多个事务同时读取和写入数据时保证数据的一致性和隔离性
版本号：每更新一条数据分配一个唯一的版本号，表示该数据的生命周期和有效期
多版本存储：对于读的操作，MVCC不会直接读取最新的数据，而是根据当前事务的时间戳和版本进行读取数据，
如果其他事务正在修改该数据，并且其版本号比当前事务早，则当前事务无法读取该数据，从而避免了不可重复读的问题。

索引越多越好吗？
不是，索引自身是需要占用物理内存空间的，索引越多，占用空间越大，经过DML操作，索引也会发生改变，也需要人力资源去维护

Mysql有哪几种索引类型？
普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
主键索引：由主键组成的索引

什么是回表？如何减少回表次数？
在使用非聚集索引进行查询时，需要通过索引中的值再次去主键索引或聚集索引中查找对应的数据行的过程。
覆盖索引
聚集索引

索引失效场景？
!=
函数
运算符
or
模糊查询
not in、not exists

mysql索引一定遵循最左前缀匹配吗？
最左前缀匹配规则指的是，如果创建了一个由多个列组成的复合索引，查询时必须按照索引定义的顺序从左到右依次使用索引的列，
才能充分利用索引进行查找。也就是说，索引可以被用于匹配索引的第一个列（最左侧的列），或者是连续的前几个列。
如果查询中涉及到的列不满足最左前缀匹配规则，那么这个索引的使用将会受限或无法使用索引。

设计索引的时候有哪些原则？
选择适当的列作为索引
创建复合索引
考虑索引大小

SQL执行计划分析的时候要注意哪些信息？
type（索引类型）,key（Mysql在当前查询时真正使用到的索引），extra（额外信息）

索引是什么？
一种数据结构，对于mysql的高效运行是很重要的，需要占用物理空间
采用B+树的数据结构，叶子节点存取数据，双向链表进行链接，可以大幅度提高查询效率

索引有哪些优缺点？
优点：大大加快数据的检索速度
缺点：时间方面，创建索引和维护索引需要耗费时间，对表进行增删改操作，索引需要动态的维护，会降低执行效率
空间方面，索引需要占用物理空间

最左前缀原则？
最左优先，在创建多列索引时，根据业务需求，where子句中使用最频繁的一列放最左边

为什么官方建议使用自增长主键作为索引？
自增长主键是连续的，减少页分裂

SQL约束有哪几种?

NOT NULL:用于控制字段的内容一定不能为空（NULL)。
UNIQUE:控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY:也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY:用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK:用于控制字段的值范围。

Mysql char和varchar的区别？
char是定长字段，varchar是变长字段，效率上char大于varchar,定长属性尽量使用char

mysql 中 In和exists区别？
MySQL中的in语句是把外表和内表作hash连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。
not exists可以走索引，not in不走索引

事务的四个特征？
原子性：要么都做，要么都不做（undolog实现）
一致性：状态的改变必须是一个一致性状态到另一个一致性状态（事务数据日志实现）
隔离性：事务之间不能相互影响（MVCC实现）
持久化：更改后的永久性（redolog实现）

事务的实现原理？
基于undolog 和 redolog 实现的
undolog记录事务的增删改操作，主要用来解决事务提交错误进行回滚，反向操作
redolog记录数据页中的变更操作，将事务操作写入重做日志进行持久化（buffer pool-->刷盘）

mysql InnoDB在可重复读下完全解决幻读问题了吗?
没有，在快照读下通过MVCC解决了幻读问题，一直读取事务启动时的数据，即使中途有其他事务插入了数据也是读不到的，避免了幻读问题
在当前读下通过记录锁+间隙锁解决幻读，在这个间隙内插入的语句会被阻塞，无法插入，避免了幻读问题

mysql如何解决不可重复读？
通过MVCC解决，当数据被修改时，会生成新的版本号，保证了不同事务读取的是不同版本的数据，从而避免了重复读问题。
事务隔离级别

SQL调优有哪些办法？
1、避免使用select * ,而是具体的对应行
2、避免笛卡尔积，Join表不能过多
3、索引优化，使用适当的联合索引，覆盖索引
4、缓冲区大小，并发连接数
5、加入Redis缓存
6、主从复制，读写分离
7、通过explain分析SQL的执行计划，去适当的进行调整
数据量过大可以考虑分库分表