黑马面试专题-MySQL
框架
MySql优化
在MySQL中，怎么定位慢查询？
- 介绍一下当时产生问题的场景（我们当时的一个接口测试非常的慢，大概5秒钟）
- 我们系统中当时采用了运维公寓（Skywalking），可以检测出哪个接口，最终因为sql问题
- 在mysql中开启了慢查询，我们设置的值就是2s，一旦sql执行超过了2秒就会记录到日志中（调试阶段）
那这条SQL语句执行很慢，如何解决的？
- 聚合查询
- 多表查询
- 表数据量过大
- 深度分页查询
可以采用MySQL自带的分析工具EXPLAIN
- 通过key和key_len检查是否命中索引（索引本身存在是否失效的情况）
- 通过type字段查看sql是否有进一步的优化空间，是否存在全索引查询或全盘查询
- 通过extra建议判断，是否出现了回标情况，如果出现了，可以查士添加索引或者修改返回字段来修复
了解过索引吗？（什么是索引）
不加索引会遍历整张表（即使找到了也不会停止）
索引是mysql高校获取获取的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树）
回答：
- 索引是帮助MySql高校获取数据的数据结构
- 提高数据检索效率，降低数据库IO成本（不需要全表搜索）
- 通过索引列对数据进行排序，降低数据排序成本，降低了CPU消耗
索引的地则会那个数据结构了解过吗？
二叉树：
红黑树：数据量大的时候会高
B树：矮胖
B+树：只在叶子节点存储数据
B树与B＋树对于：
- 磁盘读写代价比B＋更低：假如查询6，B树会查询出38，16，29的数据才能找到，B＋树不需要查询38，16，29
- 查询效率B＋树更稳定：数据存在叶子节点中，每次查询路径差不多
- B＋树便于扫库和区间查询：叶子节点使用指针链接，如果查找6~34，只需要找到6所有的叶节点都能找到
回答：
Mysql的innoDB引擎采用的B＋树的数据结构来存储索引
- 阶数更多，路径更短（矮胖树）
- 磁盘读写代价B＋树更低，非叶子节点只存储指针，叶子节点存储与数据
- B＋树更适用于扫库和区间查询，叶子节点是一个双向链表
什么是聚簇（聚集）索引什么是二级索引（非聚集索引/非聚簇索引）/什么是回表查询？
聚簇索引（聚集索引）：数据与所以放到一块，B＋树的叶子节点保存了整行数据，有且只有一个，主要是主键或者唯一索引
非聚簇索引（二级索引）：数据与索引分开，B＋树的叶子界节点保存对应的主键，可以有多个
知道什么是回表查询吗？
通过二级索引找到对应主键值，再通过聚簇索引找到对应的行的数据
知道什么是覆盖索引吗？
覆盖索引是指查询使用了索引，并且需要返回的列，该索引中已全部能够找到
回答：
覆盖索引是指查询使用了索引，返回的列，必须在索引中全部能够找到
- 使用id查询，直接走聚集索引查询，一次性全部查询，返回数据
- 如果返回的列中没有创建的索引，有可能会回表，尽量避免selest *
MySQL超大分页怎么解决？
问题：在数据量非常大的时候，limit分页查询，需要对数据进行排序，效率低
解决方案：覆盖查询+子查询
我们先获取表中的id，并对表的id进行排序，获取分页之后的id集合，因为id是覆盖查询，效率高，在和原表进行关联查询。
索引创建的原则有哪些？
- 针对数据量大，且查询比较频繁的表建立索引**（单表查过10万数据）** 重要
- 针对常作为查询条件（where）、排序（order by）、分组（group by） 重要
- 尽量选择区分度高的列表作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
- 如果是字符串类型的字段，字段长度较长 ，可以针对字段的特点，建立前缀索引
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，减少存储空间，避免回表 重要
- 要控制索引数量。索引并不是多多益善，索引越多，位数索引结构的代价也就越大，会影响增删改的效率 重要
- 如果索引行列不能存储NULL的值，请在创建表的时候使用NOT NULL约束。当优化器知道每列是否包含BULL值时，他可以更好的确定哪个索引最有效地用于查询
什么情况下索引会失效？
答案：
- 违反最左前缀法则
- 范围查询最右边的列，不能使用索引
- 不要再索引列上进行运算操作
- 字符串不加单引号，造成索引失效
- 以%开头的Like模糊查询，索引失效
谈一谈你对sql优化的经验
- 表的设计优化
- 比如设置合适的数值（tinyint int bigint），要根据实际情况选择
- 比如设置合适的字符串类型（varchar char）char定高度效率高，varchar可变长度，效率低
- 索引优化 参考优化创建原则和索引失效
- SQL语句优化
-
SELECT语句务必指明名字段名称（避免使用select * ，防止回表）
-
SQL语句避免造成索引失效的写法
-
尽量用union all代替union union会多过滤一次，效率低
union all会把两次过滤的结果全部展示出来
union 会把两次重复的结果过滤一次
4. 避免在where语句中对字段进行操作表达式，可能导致索引失效
-
Join优化 能用innerjoin 就不用left join right join ，如必须使用一定要以小表作为驱动
内连接会对两个表进行优化，由于先把小表放在外面，把大表放在里面。left join 或 right join，不会重新调整顺序
三次连接数据库
-
主从复制，读写分离
如果数据库的使用场景的操作比较多，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询效率
-
分库分表 后面讲
答案：
)
事务相关
**事务的特性是什么？可以详细说一下吗？**ACID
- 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
- 一致性（Consistency）：事务完成时，必须所有的数据都保持一致
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作的独立环境下运作
- 持久性（Druability）：事务一旦回滚或提交，他对数据库中的数据的改变就是永久的
答案：
- 原子性
- 一致性
- 隔离性
- 持久性
结合转账的案例
并发事务带来哪些问题？怎么解决这些问题？MySQL的默认隔离级别是？
并发事务问题
答案：
并发事务的问题：
- 脏读：一个事务读到另一个未提交的数据
- 不可重复读：一个事务先后读取同一条数据，结果不同
- 幻读：一个事务按照条件查询数据时候，没有对应的数据行，但是当插入数据时候，有发现这行数据存在
隔离级别：
- 未提交读 脏读、不可重复读、幻读
- 读已提交 不可重复读、幻读
- 可重复读（默认） 幻读
- 串行化
undo log和redo log区别
- redo log：记录的是数据页的物理变换，服务宕机可用来同步数据
- undo log：纪律的是逻辑日志，当事务回滚时，通过逆操作恢复和原来的数据
- redo log保证了事务的持久性，undo log保证了事务的原子性和一致性
事务中的隔离性如何保证的（解释一下MVCC）？
记录中的隐藏词
undo log版本链
不同事务或者相同的事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的最新的旧纪录，链表尾部是最早的旧纪录
- readview
Readview（读视图）是快照读SQL执行时候MVCC提取数据的依据，记录并维持当前活跃事务（未提交的）id
- 当前读
读取的记录是最新版本，读取时还要保证其他事物不能和修改当前记录，会对读取的记录进行加锁。对于我们的日常操作，如：select ... lock in share mode(共享锁)，select ... for update、insert、delect（排他锁）都是一种当前读。
- 快照读
简单的select查询（不加锁）都是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，非阻塞
Read Committed：每次select，都会生成一个快照读
Repeatable Read：开启事务后第一个select语句才是快照读的地方!
回答：
MySQL中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突
- 隐藏字段
trx_id（事务id）：记录每次操作的事务id模式自增的
roll_pointer（回滚指针）：指向上一次版本的事务版本记录地址
- undo log
回滚日志，存储老数据
版本链：多个事务并行操作某一行数据，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表
- readView解决是一个事务查询选择版本的问题
根据readview的匹配规则和当前的一些事务id判断该访问哪个版本的数据
不同隔离级别快照读是不一样的，最终的访问结果不一样
RC：每一次执行快照读生成的Readview
RR：尽在事务中第一次执行快照读生成的ReadView，后续复用
MySQL主从同步原理
回答：
MySQL主从复制的核心就是二进制日志binlog（DDL（数据定义语言）语句和DML（数据操纵语言）语句）
- 在主库事务提交时后，会把数据变更记录记录在二进制日志binlog
- 从库获取主库的二进制文件Binlog，写入从库的中继日志Relaylog
- 从库做中继日志中的事件，将改变反应他的数据
你们项目用过分库分表吗
垂直拆分：
垂直分库
以表为依据，根据业务将不同表拆分到不同库中
特点：
- 按业务对数据分级管理、监控、扩展
- 在高并发下，提高IO和数据量连接数
垂直分表
以字段为依据，根据字段属性将不同字段拆分到不同表中。
拆分规则：
- 把不常用的字段单独放在一张表
- 把text，bolb等大字段拆分出来放在附表
特点：
- 冷热数据分离
- 减少IO过渡争抢，两表互不影响
水平拆分：
水平分库
将一个库的数据拆分到多个库中
路由规则
- 根据id取模
- 按照id也就是范围路由，节点1（1-100万）....
- ....
特点：
- 解决了单库大数量，高并发的性能瓶颈问题
- 截稿了系统的稳定性和可用性
水平分表
将一个表的数据拆分到多个表中（可以在同一个库内）
特点：
- 优化单一表数据量过大而产生的性能问题
- 避免了ID争抢并减少了缩表的几率
答案：
业务介绍
- 根据自己简历上的项目，像一个数据量较大业务
- 达到了什么样的量级
