基础篇

执行一条sql经历了那些步骤

  • 连接器-》查询缓存-》词法分析-〉语法分析-》优化器-〉执行器

更新一条sql涉及了那些模块

  • redolog： 保证了更新sql的性能和mysql异常重启数据不会丢失， 这里使用了WAL技术
  • binlog: 用来做主从同步， 属于server层， 任何引擎层都有这个能力
  • undolog: 为了做事务的回滚
  • 两阶段提交： 保证了上述两个步骤的逻辑一致性

事务的隔离性

  • 解决问题： 脏读， 不可重复读， 幻读
  • 四大隔离程度
  • 隔离是怎么实现的： mvcc, 同一条记录同时存在多个版本，通过回滚日志联系在一起， 不同时刻启动不同的read view
  • 快照： 秒级创建，可重复读隔离下创建一致性视图

  • 当前读： update操作不是采用快照读而是当前读，不然会丢失数据

    索引

  • 索引下推： 本质上就是通过索引字段条件过滤时， 引擎层直接过滤， 不用再让服务层再去过滤。

  • 唯一索引和普通索引怎么选？ 从性能角度看：选普通索引（change buffer特性）， 这里可以引出change buffer和redo log的差异， redolog是在随机写磁盘做了优化， 而change buffer是在随机读磁盘做了优化，

    1. 从业务角度上看， 如果业务可以保证唯一性，那么首选普通索引

锁机制

  • 全局锁： 为了做全局逻辑备份， dml,ddl都会被锁住
  • 表锁： lock table: 在行锁出现之前盛行, 另一种是mdl锁： 解决一个线程正在查数据， 一个线程正在给表加字段， 默认会加。
  • 行锁： 两阶段锁协议
  • 注意： 行锁针对的是某一条记录， 对于快照读而言， 加锁的之前老版本的记录， 但是对于当前读而言， 加锁的是最新的记录

幻读

  • 问题引入： 对于更新非索引的记录， 会扫全表， 除了会对更新的行加锁外， 其他无关的记录会加锁吗？
  • 分析： 如果只对更新的行加锁， 会引入两个问题 1: 做不到可重复读 2: 数据不一致，指的是数据和逻辑在日志上的一致性， 通过binlog同步从库和主库不能做到一致
  • 解决问题： 对所有记录加锁 + 间隙锁
  • 虽然间隙锁解决了幻读的问题， 但是导致了并发性的不足
  • 解决替代的方式： 读提交+binglog改成row模式

  • 幻读的定义： 同一事务中， 后查找的记录比前一次查找到的记录多。 两个特点： 幻读只在当前读中出现；幻读专门指出现了新插入的行。

    加锁规则

  • 只对访问的记录进行加锁： 比如：由于覆盖索引的优化， 如果只slect 主键 for shard mode where 索引=1， 只会对索引进行加锁， 不会对主键进行加锁，这时候另外一个事务去更新主键值是可以的

  • 加锁的力度是next-key（前开右闭）
  • 优化1: 等值查询时, 当遇到唯一索引时next-key会退化成纪录锁。

  • 优化2: 等值查询时， 最后一个不满足条件时， next-key退化成间隙锁。

    实战篇

    存储过程

  • 有待实战练习， 这样实战不用写shell脚本， 增强效率

    索引选择

  • 选择依据： 扫描行数（analyze table z）， 是否排序， 是否需要回表

  • 选错索引应该如何引导： force语法；修改语句；删除或者增加索引
  • 常用命令：慢查询语句分析， explain分析

  • 如何给字符串字段加索引： 使用前缀索引， 缺点：不支持范围查找， 不能使用覆盖索引的特性 优点：节省索引空间； 前缀索引的关键之处是构造出区分度：使用hash，或者倒序存储等手段来实现

    mysql抖动

  • 可能是在把脏页刷到磁盘中： 内存不够用， 需要淘汰脏页

  • redo-log满了，要flush到磁盘： 记录checkpoint， 把redo-log flush到磁盘后再把checkpoint向前推进
  • 相关参数： innodb_flush_neighbors，是否要刷附近的脏页，innodb_max_dirty_pages_pct控制脏页百分比， innodb_io_capacity磁盘能力

表数据删除过程

  • 为什么表数据被删除了，但是表大小没变？
  • 表数据删除—并非真正删除， 只是做一个标记：空间可复用；
  • 插入表数据，可能会导致叶分裂， 导致空间利用率不高， 也会导致表大小上升

  • 如何解决？ 重建表， alter table t engine=InnoDB; 有几个概念： online ddl只有在启动的时候才会获取mdl写锁， 在真正拷贝数据的时候会降级为mdl读锁， 不会影响在其期间做dml; online原理是：通过把中途产生的增量数据写到文件里， 然后等重建完成之后再重放操作应用到新表中

    count(*)计数原理

  • 一行一行加起来， 为什么innobe不记录表的大小， 然后直接返回呢？ mvcc多版本的原因， 每个事务可能看到的行数是不一样的。 所以不能直接记录一个表的大小。

  • 怎么加速计算count(*)? 用redis不可取： 原因是mysql和redis是不同的存储系统，做不到数据的一致性。

直接存储到count表中。

  • count(*)=count(1)>count(主键》count(字段)

  • count(字段)最大区别是， 如果不是NULL，才会计数+1

    order by优化

  • explain查看，是否使用了filesort, index等

  • 优化方向：对字段加索引 -》 使用覆盖索引
  • 全字段排序 vs 单字段排序： 从性能角度最好使用全字段排序， 因为单字段排序会进行回表操作。
  • 学会查看是否使用了文件排序， 而且查看临时文件的个数， 尽量减少文件排序，涉及到磁盘的成本较高
  • 原则：尽量使用索引让msyql不进行排序过程， 但是有时候mysql必须要有排序过程， 例如： id =a or id =b limit 10; 可以转换成 id=a limit10 + id =b limit 10;然后在业务层使用归并排序即可； 这种返回个业务层的数据量会翻倍， 可以只select主键即可


  • mysql保证数据不丢失

  • binlog保证不丢失： 依赖于写入策略： write和fsync；write是写入打page cache中， mysql进程重启不会有影响， 但是主机异常启动会有问题。 fsync是写入到磁盘中， 就算主机异常重启也不会丢数据。

    1.   由sync_binlog参数控制

  • redolog写入策略： 由innodb_flush_log_at_trx_commit控制。 三种状态： 1:在进程内存中2:在page cache 3:在磁盘中

  • 问题： wal的作用是减少磁盘写， 但是每次提交事务 的时候都要进行redolog和binlog， 并没有减少磁盘写？ 1: 随机写转换成顺序写 2: 组提交， 减少磁盘iops;

  • 问题： 性能发生在io上， 如何应对？ 1: innodb_flush_log_at_trx_commit设置成2 

    1.              2: sync_binlog设置成100； 风险在于主机异常重启会丢日志

    mysql保证主从一致

  • binlog的三种模式， row,statment, mixed; 最好选row, statment是原生sql可能会造成主从不一致

  • 双m架构的循环复制问题
  • mysql主备切换流程

  • binlog数据的用途： 需要row模式来数据恢复，比如你删除数据， 修改binlog文件，把delete改成insert等

    mysql保证高可用

  • 一主多从， 有异常进行主备切换

  • 主备切换有哪些风险？ 主备延迟
  • 主备延迟导致了有两种切换方式； 1: 可靠性切换： 整个系统会有sem时间不可用写， 这段时间主要是用来保持从库和主库数据一致； 2: 可用性切换：缺失了数据可靠性，选择了优先恢复服务； 主要用于场景：主库掉电，没办法短时间恢复，这时候如果采用可靠性切换， 整个sem时间会很长，可能一直无法同步主库的数据， 导致服务完全不可用，就只能选择可用性切换的方式。

  • 引入主备延迟的途径。 1: 备库的机器配置不行， 服务压力过大。 2: 存在大事务（大表 DDL；delete过多数据） 3: 网络原因

    一主多从同步的策略：

  • 策略1: 找同步位点， 举个例子， 有a（主节点）, a’（a的备节点）,b（从节点1）,c（从节点2）,d（从节点3）， 之前b,c,d都是从a同步binlog的， 现在由a切换到a’,那么a’需要知道a当时同步binlog到那个点了， 然后a’需要找到那个位点， 继续做同步工作。

  • 策略2: gtid方法， 全局事务id, 每个节点保留自己执行事务的gtid集合， 如果接受到主库的binlog的gtid在自己的集合中， 忽略执行， 如果没有的话， 执行并且加入集合中

读写分离：

  • 架构：客户端做路由； 有个proxy代理做路由
  • 如何处理过期读？
  • 背景：主从同步存在延迟， 所以到从库读到的数据是过期的

  • 解决： 1: 强制从主库读。 根据业务特点， 看是否需要保证读到的是最新的

    1.               2: sleep方案<br />                     3: 判断主备是否存在延迟， 存在走主库，不存在走从库<br />                     4: semi-sync半同步复制方案：一旦返回给客户端，意味着已经同步到从库了， 也有问题， 可能你放到的从库没有同步， 毕竟是半同步， 之后保证有一个节点同步到就好了<br />                     5: 等主库位点方案：<br />                      6：等GTID方案

    判断mysql是否出现问题

  • select 1； 不能发现并发查询数已经超限

  • select * from t;： 不能发现磁盘已经满， 利用率高等异常信息
  • update t set a = b;： 大部分采用这个， 缺点是：不能及时发现io利用率高等场景
  • 借助内部的统计数据， 比如io总时间

获取很多数据， 会把msyql内存打爆吗？

  • mysql是边读边发送的模型， 不是一次性发送所有的结果
  • 客户端有两种接受模型（参考quick参数），1: 采用本地缓存接受， 2: 不使用缓存； 如果是第2中的话， 客户端必须及时读取之后处理很快然后继续读取下一行， 不然很容易导致网络协议栈满， 造成服务端处理慢的现象。
  • 两种状态： sending to client等待客户端接受结果 。 sending data是指正在执行中
  • 获取数据大多数走的是buffer pool， 直接从内存查询，而不是读磁盘+应用redolog, 通过查看内存命中率可以了解。 采用的lru算法， 为了应对全表扫描，分了一个新生代+老年代，因为全表扫描不会超过1秒中，很快就会被踢除掉， 所以如果能在缓存中停留超过1秒就留下来。 —这个是专门为了应对全表扫描做的定制算法。

应该使用join吗？

  • 使用join的风险？ 被驱动表没有建索引， 那么扫描的行数会变得很大
  • 有哪些优化手段针对join ： 用小表做驱动表， 并且被驱动表要有索引； 如果没有索引可以用临时表优化；mysql不支持hash join, 所以只能一个一个去对比数据是否一样， 可以从数据库查到数据之后在业务层用hash查找。
  • 不使用join有哪些替换手段： 自己在业务上实现join命令， 但是性能明显没有join好。 实现流程：a join b, 先遍历a表的每一行， 然后针对每一行去在b表中中记录。

  • 要不要使用join? explain 出现Block Nested Loop就不要使用了

    误删数据如何恢复

  • delete删除行： 恢复方案： 修改biglog重新回放； 预防方案：1: sql_safe_updates设置为on, 如果delete没有加where条件的话，会失败； 2:delete时开始一个事务， 如果写错了可以rollback

    1.  3: 可以对关键字段rename， 等没有业务失败的话， 再删除掉

  • 删除表： 全量备份+增量日志。 预防：1: 账号分离， 严格定义不同角色的权限； 2: 删除表之前先rename下，等没有影响业务时， 再删除表

    临时表和内存表

  • 内存表： 数据在内存中， 重启会丢失。 创建时需要指定memory, 其他和表别无二异

  • 临时表： 可以使用各种引擎；只能被创建它的session可见, 因此session结束的时候会自动删除临时表， 因此比较适合优化join语句； 线程退出会自动删除数据；
  • 不同session的临时表可以重名，因为命名规则底层是用带sessionid的
  • 应用： 跨库查询： 比如数据分散到数据库的不同实例上， 需要select并做排序， 可以将每个实例的数据插入到临时表中（可以在汇总库中创建），然后再借助mysql的能力进行排序
  • 临时表和主备复制的联动： 假设不同步关于临时表的binlog， 有何问题？ insert into t_normal select * from temp; 如果temp表不同步， 那么这个语句在备库中就会执行失败。
  • 什么时候用到临时表？ 1: union， 需要做结果做去重， 需要在临时表中建立索引保证唯一性。 2: group by的时候， 需要对另外一个字段做计数
  • 优化 group by方案？ 方案是generated column尽量使用索引避免使用临时表和排序 ，原理是想办法消除临时表（用explain验证），消除排序
  • 临时表分为磁盘临时表和内存临时表？ 有个参数控制， 可以尽量调大参数的值， 使其尽量使用内存临时表
  • ORDER BY NULL， 是对group by的优化， 因为group by默认会排序， 浪费性能， 用order by null可以指定不排序， 从而不用用到临时表等额外特性。
  • 什么时候使用memory引擎？ 优点： 1: 在内存中， 速度快 缺点： 1: 重启会丢失，影响主备同步 2: 锁粒度为表锁，影响并发性 3: 底层是hash索引，范围查找性能低。 但是内存临时表可以天然无视这些缺点， 其他正常业务数据使用innobe引擎， 因为有buffer pool，性能还可以

自增字段的坑

  • 设置自增字段可以保证递增，但是不能保证连续递增
  • 理由：为了性能， 不支持自增id回退。
  • 复现方法 1: 开启两个事务， 然后回退其中一个事务， 再插入数据，发现不会连续。 2: 插入数据发现冲突，也不会连续
  • 自增值保存在哪儿？ 5.7之前保存在内存中， 没有可持久化， 到时每次mysql重启之后， 会重新使用max()计算auto_increment的值， 如果不重启的话自增可能为大一点， 因为在重启前删除最大的值， auto_increment并不会减少
  • 自增值修改机制？ 1: 不指定自增字段，则mysql会自动计算自增值； 2: 指定自增字段大小， 如果指定的大小》自增值， 则修改自增值， 否则不变 3: 批量插入数据时， 由于不知道需要插入多少条数据， 所以无法一次性申请完自增id， 因此会按照倍数递增的方法去申请id， 这样会导致多申请一些id， 出现浪费现象。
  • 自增字段避免出现叶分裂， 提高性能
  • 对业务上的思考： 业务不能依赖mysql自增字段连续递增

insert into .. select

  • replace into…重复，删除重复数据，insert新数据；
    insert…on duplicate key update…重复，update；

  • insert select如果操作的是同一张表， 可能会操作循环写入， 可以引入临时表解决

    kill的原理

  • 标记事务的状态为kill ； 给事务发信息， 让其退出等待（可能线程正在处于锁等待）

  • 可以看出实现事务中通过在代码中做埋点，以便于执行终止逻辑： 判断事物状态如果为kill，然后就开始执行终止操作
  • kill不掉的场景： 1: 终止逻辑太长，比如存在超大事务。 2: 由于io压力过大导致无法返回， 没有机会执行判断事物状态的逻辑
  • 误解1: 库里面的表特别多时， 就会链接变得很慢 。 原因： 不是链接变慢了， 而且客户端为了实现自动补全表名， 会执行show tables， 并且会构建本地hash表， 所以变慢了。 加上-A就好了

  • 误解2: -quick参数是可以让客户端变🉐️更多， 但是会让服务器变慢的指令。

    切忌注意以下问题：

  • 不要使用长事务： 会占用锁资源， 还有会滚记录也会占用磁盘

  • 给表安全加字段： 不要出现长事务； 设定等待时间
  • 事务中把最可能影响并发度， 最有可能锁住的代码放在最后面
  • 如何解决死锁检测导致的cpu飙升问题？ 1: 在服务端控制并发度 2: 将数据库的一行改成逻辑上的多行， 例如将账号分散到多个账号记录，然后随机找一个账号做add,sub等
  • 注意启动时机， 比如事务启动和事务结束， 还有加锁开始和解锁完毕时机， 一般开始的时机是执行第一个语句， 而结束是commit之后
  • sync_binlog和innodb_flush_log_at_trx_commit参数设置为0， 可以使得插入性能飞快， 做实验的时候会用到, 或者尽量放到一个事务中， Redo log 和 binlog刷盘次数少了就会变快
  • 如何随机显示某一行？ limit N,1(N是每次随机出来的数字， 最大是总行数)， order by rand() limit 1;采用的是单字段排序；有几个需要关注的点： 临时表使用内存还是磁盘； 关注扫描行数; 关注排序算法， 归并还是优先队列； 优先队列节省内存，只需要一次放入limit的量， 但是归并不行；重要参数：sort_buffer_size，optimizer_trace
  • mysql如何定位一行记录？ 通过主键， 如果定义表的时候没有主键，系统会自动生成，叫做rowid
  • 不能对查询的字段做函数操作，否则会导致索引失效， 因为会破坏索引的有序性， 例如：隐式类型转换， 隐式编码类型转换