总结面试问题回答技巧.zip

## mysql结构 1.server层：由连接器、查询缓存、分析器、优化器、执行器组成 2.存储引擎层：InnoDB、memory、myisam ## 语句执行流程 1.首先通过连接器连接到mysql实例 2.如果有缓存就先查缓存。缓存实际弊大于利，新版本mysql甚至彻底去掉了该功能，因为表更新后就失效清空 3.经过词法分析、语法分析，优化后得到最终的命令 4.执行器执行，调用引擎提供的接口 ## redolog 用来存储执行命令的日志文件，mysql使用wal（write-ahead logging，预写日志）技术，命令先写到这个日志，在内存中更新就直接返回，由mysql引擎自己读取redolog并更新磁盘上的数据库文件。 两个关键概念：checkpoint，在此之前都已经持久化到磁盘；write pos，指下一次在wal日志中写入的位置 ## binlog binlog是server层的日志文件，记录所有更新操作，用来数据恢复、备份、审计。一般恢复是用全量备份+重放binlog来实现 ## 两阶段提交 存储引擎更新内存数据，并且redolog进入prepare阶段后，才开始更新binlog。随后redolog 进入commit阶段。 目的是避免崩溃后redolog和binlog不一致。 崩溃恢复时： 关键是binlog不能动（保持主备一致），redolog要恢复成一致状态 1.binlog+redolog都commit，不需恢复操作 2.binlog有，redolog prepare，说明事务并没有实际更新到磁盘里，redolog提交并进行恢复操作 3.binlog没有，redolog prepare，回滚redolog ## 事务隔离级别 1.读未提交，指的是事务还没提交就可见。引起脏读 2.读提交，指事务提交后才可见。解决脏读，但是存在不可重复读问题，即事务过程中数据可能发生变化 3.可重复读，InnoDB默认级别，指的是事务执行过程中，看到的数据不会发生变化，通过创建视图快照实现。解决了不可重复读问题，但是存在幻读问题，原因有二:1.事务自己更新数据只能使用当前读（如果被占了就等锁），进而导致视图更新，因此mvcc无法完全避免幻读2.无法对不存在的行加行锁，避免数据插入。在可重复读级别下引入next-key locks（行锁+间隙锁）来避免当前读场景下的幻读，但是可能引起死锁，降低并发性能 4.串行化，通过读写锁避免出现事务冲突，读的时候禁止任何写 ## mvcc----- 多版本并发控制，用于实现读提交和可重复读。引入几个概念：视图、事务id、版本号（按行区分，row trx_id）。 通过对比当前事务id和当前版本号来决定视图使用什么数据。 对于读提交级别，每次查询前都会用最新版本号更新视图；对于可重复读，单纯的读只会使用事务id前的版本号生成视图，但是如果要更新，则会使用“当前读”，用当前版本号生成视图，因此就有幻读问题 ## 索引 索引即是表的目录，目的是快速找到表中的数据，我们常说的索引有hash表、有序数组、搜索树这几种模型， hash只适用于等值查询，范围查询效率太低 有序数组查询性能没问题，但是修改起来开销较大 ## InnoDB中的索引------ InnoDB中使用的是B+树。 普通二叉树不能自平衡，就算采用平衡二叉树也可能深度过高，访问磁盘次数过多时延迟太大 多叉树（B树）有效降低了树的高度，一次性能读取多个索引。而B+树把数据都放到叶子节点中，让非叶子节点能存更多的索引信息，同时叶子节点形成链表，支持范围查询，方便动态扩展 ## 聚簇索引 由主键自动创建的索引即聚簇索引，索引和数据在一起；反之为非聚簇索引，需要回表二次查询来找数据 ## 联合索引 包含多个字段的索引 ## 覆盖索引 使用索引或者联合索引的最左字段，查询结果是主键或者联合索引的字段，那么便不用回表再次查询，直接返回结果 ## 最左前缀 最左前缀指联合索引的最左N个字段或者字符串索引的最左N个字符，只要满足就可以利用索引来加速 ## 索引下推 指的是原本server层要做的事交给下层（引擎层）来做，例如（name，age）这样一个联合索引，可以将age的判断放到引擎层筛选，避免回表 ## 索引选择错误 优化器有时候会预估扫描错行数，并考虑普通索引的回表，导致使用了代价更大的索引。通过强制指定索引、改写sql、删除可能导致异常的索引来解决。 ## 为什么有时候没走索引 注意不要对索引字段做函数操作，破坏索引有序性，这样会让优化器放弃走树搜索 ## 全局锁 对整个数据库实例加锁，应用场景：全库备份 ## 表级锁 包括表锁和元数据锁，元数据锁（mdl）在访问表时自动加上，避免访问表时，表的结构（元数据）被其他事物更改 ## 行锁 需要时才加上，commit时释放，如果事物中需要锁多个行，有可能造成死锁。需要调整语句顺序、死锁检测（检测到死锁后回滚） ## 慢sql的原因 1.表的数据量过大 2.数据库负载太大，在flush redolog到磁盘 3.sql语句效率低 4.出现锁被占用 5.索引没设计好或者优化器选错了索引 ## 第一次sql执行慢的原因 需要加载数据和索引到缓存，后面就可以直接使用缓存数据 ## 备库落后的原因 1.大事务引起的慢sql 2.主库并发执行多个事务，但是到备库如果不开启并行复制（分库、分表）就只能一个个执行，本身速度就赶不上 3.硬件上的区别，备库一般硬件性能要差 ## 半同步复制 主库提交事务前至少有一个从节点接收事务并写入日志 ## ps：表的主键选择自增id还是uuid 自增id优势在于节省空间，提升查询和插入的性能，缺点在于可能达到上限，需要及时分库分表。 ## 分库 将多张数据表，存储到多个数据库实例中，比如按照业务逻辑划分 ## 分表 垂直分表：将某些字段从表中拆分出去，比如不常用的字段 水平分表：横向切分，分片策略包括连续分片、取模分片等 ## 其他问题 ### char和varchar 前者定长，后者不固定，节省空间，但是需要动态计算位置导致查询速度较慢 ### like中的“%” 与 “_" 多个和单个的区别 ### 常用配置 transaction_isolation 隔离级别 max_connections 最大连接数 interactive_timeout 交互式连接超时时间 wait_timeout 非交互式超时 slow_query_log 是否开始慢日志 expire_logs_days binlog 过期时间