Interview preparation -- MySql

已于 2023-05-05 09:32:52 修改
阅读量176 收藏
点赞数
分类专栏： mysql 文章标签： mysql 数据库 interview
于 2023-03-22 19:04:41 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/liaojiamin0102/article/details/129709970
版权
MySql 索引

  • InnoDB存储引擎下支持：B+树索引，全文索引，哈希索引

  • B+树索引分为聚簇索引 和 非聚簇索引

聚簇索引 / 聚集索引
  • InnoDB使用了聚簇索引，就是将表的主键构建一颗B+树， 并且将整张表的行记录数据放在B+树的叶子节点上，这也就是所谓的索引即数据，数据即索引。每一张表只能有一个主键，因此也只能有一个聚簇索引
  • 聚簇索引优点：
    • 能通过聚簇索引直接获取完整的整行数据
    • 对于主键的排序查找，范围查找非常快
  • 当没有定义主键的时候，MySql会使用唯一性索引作为构建B+树，如果两者都没有 MySql也会创建一个隐含的列作为主键，利用这个主键来建立聚簇索引。
辅助索引/非聚簇索引
  • 聚簇索引只能搜索的条件是主键的时候才用处大。因为B+树中数据都是按照主键进行排序的。
  • 对于其他列数据，一般建立多个非聚簇索引，每建立一个非聚簇索引都是一颗B+树
  • 非聚簇索引的叶子节点上不包含行记录的全部数据。叶子结点除了包括该索引定义的几个键值以外，每个叶子结点中还包含了一个书签（bookmark）。该书签用来高速InnoDB存储引擎哪里可以找到这个索引相对于的行数据，因此InnoDB存储引擎的非聚簇索引存储的书签就是对应行数据的聚簇索引的索引键，也就是对应表主键信息。
回表查询
  • 当通过非聚簇索引 查询数据时候，InnoDB先遍历非聚簇索引 通过叶子结点指针获取只想主键索引的主键，然后通过聚簇索引来找到一个完整的行记录。这个过程被称为回表。也就是根据辅助索引的值查询一条完整的记录需要用到两颗B+树（一次非聚簇索引 ，一次聚簇索引）
非聚簇索引为啥不放整个行数据
  • 非聚簇索引可以键多个，如果每一个非聚簇索引 都构建一个全量数据的B+树，太占用内存
  • 并且一次写操作我们必须更新所有包含数据的索引，这样性能也会降低。
联合索引
  • 建立联合索引也就是多个列组合起来进行索引，一个联合索引只会建立一个B+树，例如index(node, b) 构建了两个字的的联合索引，包含两个意思
    • 先吧各个记录安note列进行排序
    • 排序时如果note相同情况下，采用b列进行排序
哈希索引
  • InnoDB 支持自适应哈希索引。B+树查找次数取决于B+树高度，生产环境B+树一般3～4 层，也就是3～4次IO，当InnoDB内部检测到有一个索引经常用到，那么任务是热点数据。InnoDB内部会自己创建一个hash索引，又称为自适应哈希索引（Adaptive Hash index，AH）。创建后，下次明中这个索引直接通过hash算法推倒出数据地址。就能通过近似O(1)时间复杂度查询到数据。
  • InnoDB存储引擎使用哈希函数采用除法散列方式， 冲突机制采用链表方式。
  • 自适应哈希索引是InnoDB内部自身创建，DBA不能干预
高性能索引创建策略
  • 索引的列类型尽量设计的最小话：在业务允许的范围内，我们尽可能用更小类型的字段，能用int 就不用bigint。因为数据类型越小，查询时候进行的比较操作越快（高速缓存存储的数据就多一点），索引占用空间少，一个数据页就可以放入更多记录减少磁盘I/O次数。加快读写速度。
  • 创建索引应该选择离散性高的列： 离散性指 不重复索引值/数据总数 的比值，例如主键的离散性就是1 ，也就是每一行都不一样，在比如性别离散性差，即使用了这个索引理论上也只能筛选出一半
  • 前缀索引：针对blob，text，很长的varchar 这种很长的字段，mysql不支持索引全部长度，我们可以用前缀索引
  • 索引创建只为搜索，排序，或者分组的列创建，除非你要使用覆盖索引
三星索引
  • 三星索引概念就是最好的索引设计
    • 索引相关的记录叶子结点在相邻位置，也就是扫描结点最少情况（25%）
    • 索引中数据顺序于查询顺序一致（25%）
    • 索引中列包含查询所有列（50%）
字符串加索引
  • 现在几乎所有系统都支持邮箱登陆，那么在邮箱这个字段上建立合理的索引是比较重要的。
  • 一般比较长的字符串字段创建索引，我们使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
    • 选定具体的长度，利用Sql语句统计每个不同前缀长度的数量
select count(distinct left(email, 3)) as l4, count(distinct left(email, 4)) as l5 from suser;

- 如上就能看出具体那个长度的最多了

  • 前缀索引定义之后，覆盖索引将不会生效，因为即使incex(k)中的值k比整个email还长也不会走覆盖，还是需要回表，因为系统无法判断前缀的定义是否截断了完整信息。

  • 其他情况，身份证情况，没法用前缀索引，我们有如下两种方法：

    • 倒叙存储，这种情况有很好区分度，但是无法做到范围查询
    • 利用hash值多存储一个身份证自动的hash值这一列并创建索引，利用hash值这一列的索引查询，同样不能范围查询。
查询语句执行流程

在这里插入图片描述

  • MySql包括Server层和存储引擎层
  • Server层包括，连接器，查询缓存，分析器，优化器，执行器
    • 连接器：负责和客户端建立连接
    • 查询缓存：MySql获取到查询请求后，会先查询缓存，如果之前已经执行过一样的语句结果会以Key-value的形式存储到内存中，key是查询语句，value是查询结果。缓存明中的话可以很快完成查询，但是大多是情况不能明中，不建议用缓存，因为缓存失效非常频繁，任何对表的更新都会让缓存晴空，所以对一个进程更改的表而言，查询缓存基本不可用，除非是一张配置表。可以通过配置来决定释放开启查询缓存，并且MySql8.0 之间删除了查询缓存功能
    • 分析器：词法分析，识别语句中表名，列名，语法分析，判断Sql是否满足MySql语法
    • 优化器：在有多个索引的情况下，决定使用哪个索引，或者多表联合查询的时候，表的连接顺序这么执行等
    • 执行器：执行器先判断权限，有权限才会去调用存储引擎对应的查询接口，默认InnoDB
更新语句执行流程
  • 更新语句同样要经过查询语句的整个过程，连接器，缓存，分析器，优化器，执行器，执行引擎这步骤。但是于查询不一样的是，更新语句需要涉及到两个重要的日志模块：redo log （重做日志），binlog（归档日志）
重要日志：redo log
  • MySql对写操作的一个优化，在写操作时，不会立刻刷到磁盘中，而是利用WAL技术Write-Ahead Logging，先写日志在写磁盘
  • 记录更新时候，InnoDB先将记录写入到redo log，并更新缓存，到此更新就算完成，同时InnoDB会在适当时候将缓存中的数据更新到磁盘，一般是系统空闲时候刷。
  • 异常情况下，因为InnoDB中redo log固定大小，一般是4个文件，每个1G，总共4G，类似一个环形数组，从头开始写，写到末位后再覆盖写。

在这里插入图片描述

  • 如上维护两个指针，一个write pos，表示单前写位置，check point 表示要擦除位置，当write pos 追上checkpoint时候，那么InnoDB就不得不进行一次缓存刷新
  • redo log作用是在数据库异常的时候，之前一段时间的提交也可以不丢失，crash-safe
重要日志：bin log
  • redo log是属于InnoDB引擎层的主要负责存储相关，bing log是MySql 中Server层的，主要做mySql功能上的事情。
  • 两种日志不同：
    • redo log是InnoDB特有的，bingLog是mySql Server自己实现，所有存储引擎都可以用
    • redo log存储的是物理日志，记录的是做什么修改，binlog是逻辑日志，记录的就是这条sql
    • redo log 是循环写的，binlog是追加写，问题到一定大小会重开一个新文件写。
更新操作执行流程

在这里插入图片描述

  • 更新操作总共三个大步骤

    • 找数据：先找内存中是否存在数据，不存在则从磁盘读入内存
    • 修改数据：拿到数据后安sql要求修改数据，得到新数据调用引擎接口写入到新行（缓存）
    • 两阶段提交写日志：
      • 引擎将数据跟新到内存，同时记录redo log，此时redo log 属于prepare阶段，返回已经执行成功可以提交事物
      • 执行器生成操作的binlog，并将binlog写磁盘
      • 执行器调用引擎提交事物接口，将redo log中prepare状态改为 提交（commit）

  • 两阶段提交

    • 因为InnoDB 于mySql server 可以看出是两个系统不同操作，两阶段提交的方式保证binlog 于redolog逻辑保持一致

  • 日志写磁盘

    • redo log 用于保证crash-safe，可以通过参数设置每次事物的redo log 都持久化到磁盘，这样抱着mysql异常情况数据不丢
    • binlog也可以通过配置形式设置每次事物binlog都持久化到磁盘，这样抱着异常重启后binlog不丢
MySql 事物隔离级别

  • 数据库经常存在多个事物同时执行情况，不同隔离级别下执行结果可能就不同，会存在 脏读（dirty read），不可重复度（non-repeatable read），幻读（phantom read）问题。为解决这些问题才提出了“隔离级别”概念

  • 隔离级别越严格，性能越差：

    • 读未提交：指一个事务还没有提交时，他做的变更就能被别的事务看到
    • 读提交：一个事务提交之后，他做的变更才能被其他事务看到
    • 可重复读：一个事务执行过程中看到的数据总是跟这个事务启动时候看到的数据是一致的
    • 串行化：：对于同一行记录，写操作会加写锁，读加读锁，当读写冲突，后访问的就等待前面执行完在执行。

  • 用以下案例说明每个隔离级别：
    在这里插入图片描述

  • 读未提交情况下：即使B没有提交，也能被读到，因此V1，V2，V3 都是2

  • 读提交：因为B提交后才能读到，因此V1 =1，V2，V3 都是2

  • 可重复度：因为需要事物执行之后看到的和启动一致，因此，V1，V2都是1 ，V3 是2

  • 串行化：事物B在修改期间会被阻塞，一直到A提交才能继续执行，因此V1，V2，都是1， V3 是2

事务隔离级别实现
  • MySql每条记录更新都同时记录一个回滚操作。修改后的值通过回滚日志可以得到上一个状态。如下

在这里插入图片描述

  • 当前值是4，但是在视图A，B，C中，分别记录的值是1，2，4，也就是同一个字段，在系统不同时间段可能存在N多个版本，这就是数据库的多版本并发控制（MVCC）

  • 对于read-view-A，要想得到1 的值，需要用当前值4 依次执行每一个回滚操作得到

  • 同时，即使有另外的事务B存在，对A与C也不影响

  • 回滚日志删除逻辑：

    • 只有在不需要的时候才删除，系统会判断当前所有事务，如果有事务的开始时间是在回滚日志之前，那么表示这个日志不能被删除，当早开始的一个事务是在日志之后，表示已经没有事务在用当前日志，那么就可以删除。

  • 长事务问题：

    • 由上可知，如果一个事务执行时间过长，期间如果写操作频繁，会造成期间累计很多的回滚日志，而这些日志在长事务提交之前都不能被删除。因此占用大量存储空间，有可能比本数据库中数据文件还大。
    • 在MySql 5.5 版本以前，回滚日志存储在ibdata文件中，即使事务最终提交，回滚段被清理，文件也不会变小。
MySql 中锁
全局锁
  • 全局锁就是对整个数据库实例加锁，这样整库都只读，以下操作都会被阻塞：数据更新（增删改），数据定义语句（建标，修改表），更新类事务提交语句
  • 全局锁使用场景：做全库逻辑备份。加锁全局读锁命令 Flush table with read lock（FTWRL）
  • 备份时候加锁的目的是为了让库中的所有表的逻辑时间点是在同一个点上，视图是同一个时间的视图。
  • 备份的其他方式：要想获取同一个逻辑点的视图，可以利用可重复读隔离级别下开启的事务。会在事务开启的时候新键一个视图，利用这个视图做备份，用命令mysqldump 参数 -single-transaction。但是只能是支持事务的存储引擎InnoDB，MyISAM不支持。
表级锁

  • 表级别锁有两种：表锁， 一种是元数据锁（meta data lock， MDL）

  • 表锁 通过lock table 执行， unlock tables 主动释放

  • 另外一种表级别锁MDL。不是显示使用，在访问一张表时候会自动加MDL锁。

  • MDL锁作用是保证读写的正确性。

  • 例如读时候，有个语句将表一列删了，那么查询拿到的就是错误的结构。因此MySql5.5引入了MDL

    • 对表做CURD时候加MDL读锁，读锁之间不互斥，可以多线程对同一个表CURD
    • 当对表结构修改时候加MDL写锁，读写锁，写锁之间都互斥。因此两个线程都改表结构时候，必然有一个阻塞。
行锁

  • 行锁针对的是表中的行记录的锁，比如事务A更新了一行，而事务B也要更新数据，如果是同一行，那么A，B只能排队执行，如果不是同一行数据则可以同时执行。

  • MySql的行锁是引擎层自己实现的，不如InnoDB支持行锁，MyISAM不支持，因此MyISAM 只能通过表锁更新并发效率差

  • 关键点：

    • InnoDB事务中，行锁是在需要的时候才加的。但并不是不需要了就立刻释放，而是要等到事务结束的时候才释放。这个就是两阶段锁协议。
    • 由此我们得出结论，如果事务中需要锁多个行，我们应该将最有可能造成锁冲突，最有可能影响并发度的锁往后放，这样才能尽可能缩短锁住的时间。
间隙锁
  • 当我们在 可重复度的事物模式下 用当前读方式取读数据，就很有可能出现幻读
A事务B事务
begin
select * from a where b = k for update;
insert into a values(k,k,k);
select * from a where b = k for update;
commit;
  • 如上事务A，B，事务A第二次读 比第一次读能多读到新添加到的 数据。
  • 幻读的定义：
    • 在可重复读隔离级别下，普通查询是快照读，不看别的事务插入数据，因此幻读诗当前读才出现
    • 幻读仅仅指 “新插入的行”
  • 幻读产生的原因是，我们读取的时候，只是添加了行锁，但是新增加的记录操作的是行之间的间隙，因此我们需要对间隙加锁
  • 和间隙锁冲突的关系是：往这个间隙中添加一个记录这个操作，间隙锁自己和自己不存在冲突关系。
  • 因此间隙锁很好的解决了幻读的问题。
死锁和死锁检测

  • 当并发系统中不同线程之间出现资源循环依赖，涉及的线程都在等别的线程释放资源时候，就会导致这几个线程进入无限等待状态，称为死锁

  • 出现死锁后有两种策略：

    • 一种直接进入等待，直到超时，超时时间可以设置参数：innodb_lock_wait_timeout 来设置，默认50s
    • 另一种策略：发起死锁检测，发现死锁后，主动回滚死锁链条中某个事务，让其他事务得以继续执行，设置参数是innodb_deadlock_detect 设置为on，表示开启这个逻辑

  • 死锁检测会耗费大量CPU，例如1000 个线程并发一个行，会导致每个线程检测其他999 个线程，时间复杂度O(n)，热点行导致的性能问题：

    • 如果能确保业务一定不会出现死锁，就关掉：风险在于业务上死锁不是一个错误，遇到了大不了回滚，对业务来说是无损的，但是关掉的话只能等锁超时了，如果是超时情况那就是业务有损的。
    • 控制并发度，增加队列等措施不过这个需要对Mysql客户端，或者中间件，或者mysql服务端二次开发，难度较大
    • 通过业务解决，比如1000个线程对同一个账户操作，我们讲账户数据拆分成200个或者更多，这样每个线程可以在其中某个散列出的行数据中操作，但是我们在处理汇总或者某个账户清零时候的逻辑需要额外处理，增加了复杂度
    • 因此对于热点行问题我们需要依据自己的业务来进行甄别解决方案。
change buffer
  • 更新过程涉及到change buffer，当需要更新一个数据页时，如果数据页在内存则直接更新，如果不在内存中，在不影响数据一致性的前提下。InnoDB会将这些更新操作缓存在change buffer中，这样就不用读磁盘中数据页了。在下次查询到这个数据页的时候，才会将数据页读入内存，然后执行change buffer 与这个页面的merge操作。通过这种方式保证数据的正确性。
  • change buffer 的数据应用到远数据页过程称为merge的过程，除了查询触发merge，mysq后台线程会定期执行merge，数据库正常关闭过程会执行merge
  • 因此在写操作时候，如果能先记录在change buffer，减少了磁盘的读，语句执行会很快。而且数据读入内存是会占用buffer poll容量大，能提升内存利用率。
change buffer使用场景
  • change buffer 的主要目的是将记录先缓存下来，之后等待merge操作才真正执行。因此在merge操作执行之前，change buffer 累计的越多收益就越大
  • 对于写多读少的业务，页面写完后被访问的概率小，此时change buffer 的使用效果更高
  • 对与读多写少的业务，页面写完后被访问的概率大，此时change buffer才记录完，立刻就读，马上触发merge操作，这样随机IO次数就不会减少了，反而增加了change buffer 的维护代价。所以这种情况change buffer 反而起负作用了。
普通索引与唯一索引
  • 先说结论，在业务允许的范围内，能用普通索引就用普通索引，原因在于，普通索引写入能利用chang buffer，唯一所以写入不能用chang buffer
查询过程

  • 查询语句在索引树上查找过程是B+树根开始，安层搜索叶子结点二分查询。

    • 对于普通索引来说，查找到第一条满足条件的数据后，还需要查找下一个记录，直到碰到一个不满足添加的数据为止
    • 对于唯一索引来说，由于索引具有唯一性，因此找到第一个满足条件的记录后就停止继续搜索了。

  • 原因在于，InnoDB数据是按页为单位读写的，当读一条数据时候，并不是直接读磁盘，而是以页为单位读入内存中，然后基本都在内存中我内存读操作，而且每个页大小16KB，对于普通索引多做的那一次操作而言仅仅只是多了一次指针的移动而已。在极端情况，下一条数据正好在下一个页的话这种情况概率也太小了，综合来看平均新能是差不多的

更新过程
什么时候用change buffer
  • 说回之前索引的问题，唯一索引来说，必须先判断操作十分违反唯一性约束，那么久必须将数据读入内存才能判断。如果已经在内存直接更新内存。那就没必要用change buffer，因此唯一索引不能用change buffer，普通索引能用change buffer
  • 我们来分析写入的情况：
    • 第一种情况，记录页在内存中，此时，InnoDB处理流程，
      • 对于唯一索引，找到数据，判断没有冲突，插入值，结束
      • 对于普通索引，找到数据，插入值，结束
    • 第二中情况，记录页不在内存中
      • 对于唯一索引，需要将数据页读入内存，判断没有冲突，插入值，结束
      • 对于普通索引，记录更新到change buffer，结束
  • 由上看出，不在内存情况，多了一次磁盘的随机IO访问，这是数据库成本最高的操作之一。因此change buffer减少了磁盘的随机访问，对更新性能能提升很明显。
脏叶导致的sql语句变慢了
  • 当内存数据页跟磁盘数据面内容不一致的时候，我们称这个内存页为脏页。内存数据写入到磁盘后，内存和磁盘上的数据页内容就一致了，称为 干净页。
  • 当我们语句执行变慢了可能就是在执行刷脏页（flush），引发这个过程的可能有如下几个情况：
    • 第一种： InnoDB的redo log 写满了，这个时候系统停止所有更新操作，将checkpoint往前移动同时将对应部分的脏页都flush到磁盘，给redo log留出空间继续写。
    • 第二种：系统总内存不足。此时新的查询需要新的内存页，内存不够用需要淘汰一些数据页，空出来给新的数据页。如果淘汰的页面是脏页，那么久需要写磁盘
    • 第三种：MySql空闲的时候，会对当前存在的脏页flush到磁盘
    • 第四种：MySql正常关闭的流程中会把内存中脏页flush到磁盘
count(*) 实现方式
  • 不同引擎执行方式不同
    • MyISAM中存储了一个表总行数在磁盘中。因此count（*） 直接返回就行。
    • InnoDB执行count（*） 需要将数据一行一行从引擎中读出来统计累加。
  • InnoDB无法存储count(*) 总数是因为多版本并发控制（MVCC）的原因，在不同事务中可能的行都是不一样的。因此只能一个一个统计。
  • InnoDB对count(*) 做了优化：
    • 因为InnoDB是索引组织表，主键索引叶子节点是数据，普通索引叶子阶段是主键值，所以普通所以比主键索引树小很多。对于count（*）操作，遍历哪个索引都是一样的统计，因此MySql优化器会找到最小的那颗树来遍历。在保证逻辑正确前提下，尽量减少扫描数据量，是数据库系统设计的通用法则
不同的count用法

  • count(主键id)：InnoDB引擎会遍历整张表，并把每一行的id值取出来，返回给server层，并且server层拿到id后判断是否为空（此处不可能为空），在按行累加

  • count(1)：InnoDB遍历整张表，但是不取值，而是每一行放入一个1，然后返回给server层，安行累加

  • count(字段)：分为两种情况

    • 字段定义为not null： 扫描整个非聚簇索引，一行一行取数据，判断定义为不为null，安行累加
    • 字段定义允许null：扫描整个索引，取出数据，判断定义可以为null，还要将具体值取出来判断不为null在统计

  • 因此count(*)的优化最有利：扫描全表每一行数据，并不会将全部字段取出，而是专门做了优化，不取值。直接安行累加

  • 最终结果 count(字段)<count(主键 id)<count(1)≈count(*)

MySql保证数据不丢

  • binlog机制：事务执行过程中先将日志写入binlog cache，事务提交时候binlog cache 写入到binlog文件

  • redo log 机制：事务执行过程中生成的redo log 要先写入redo log buffer此时事务处于prepare阶段没提交，并且通过两阶段提交保证 redo log 与binlog 同时成功

  • binlog写磁盘配置：

    • sync_binlog=0时候，每次事物提交都值write到pagecache 不fsync到磁盘
    • sync_binlog=1时候，每次提交事物都会执行fsync到磁盘
    • sync_binlog=N(N>1)时候，表示每次提交事物都write到pagecache，但累积N个事物后才fsync到磁盘

  • redo log写磁盘配置：

    • 配置0，直接写redo log buffer完成
    • 配置1，每次提交都直接将redo log持久化到磁盘
    • 配置2，每次提交值吧redos写到 page cache，此时还没有调用操作系统sync写磁盘
Buffer Pool
  • Buffer Pool是MySql内部的一个缓冲池，用来缓存表数据和索引数据，当执行MySql查询语句的时候，MySql会先去查询Buffer pool中的内存页Page，如果命中则直接返回，如果没有命中则会去读磁盘中的信息，并且将当前数据所在的数据页Page以及后续的几个数据页加载到缓冲池Buffer Pool中。
  • 因此 BufferPool 是由缓存数据页Page （每一个16KB） 和控制块组成，控制块约占用5%空间
InnoDB 对Page页的管理

  • Page页的分类

  • Buffer Pool底层采用链表的结构管理Page页面，存在三种状态的Page页面：

    • free page ：空闲page，未被使用的
    • dirty page：脏页，被使用的page，并且数据被修改过，Page页中的数据和磁盘数据不一致
    • clean page：干净页，数据被使用查询但是数据没有被修改过

  • 针对上述三种Page页，InnoDB用三种链表来管理和维护

    • free list：空闲缓冲区，管理 free page，将所有空闲缓冲页用List链表的形式串联，链表有一个基础节点用来存储头节点，尾节点，以及数据节点的数量
      请添加图片描述

    • flush list：表示要执行merge操作的磁盘缓冲区，管理dirty page，内部page按修改时间排序，InnoDB为了提升写入效率，每次update操作并不会直接写磁盘，如果缓存命中则先写入缓存中，在之后的某个时间点在进行Merge到磁盘的操作。因此需要flush单独管理以及被修改的dirty page页面
      请添加图片描述

    • lru list： 表示正在使用的缓冲区，管理clean page 与dirty page，缓冲区以midpoint基点，前面是new列表区域，存放经常访问数据占63%，后面存old区，存放较少访问数据 占37%
      请添加图片描述

lru list 改进型LRU算法

  • 普通LRU算法：末尾淘汰，新数据放链表头，每次访问数据后，将访问过的数据放链表头

    • 优点：所有最近使用的都可以在表头找到，默认的数据是很久不用的数据
    • 缺点：MySql中发生全表扫描，很大可能导致热点数据被淘汰掉，并且MySql中有很多预读取机制，如果这部分数据页放头部而没有被访问也会导致热点数据被淘汰

  • 改进型lru list：

    • 新进入的数据放入在mid位置，也就是热数据 与 冷数据之间，如果数据很快被访问久移动到头部，否则会逐步移动到尾部等待被淘汰
    • 如果新进入的数据在链中存活时间超过1s 那么将会移动到链表头部
    • 如果新进入数据在链表中存活时间短于1s，那么久保持现有位置（全表扫描对某个page页的访问时间低于1s）
    • 1s 的时间是可以通过参数 innodb_old_blocks_time控制的。
索引一定提升效率？
  • 优点：提升查询检索效率，降低IO成本，通过索引排序降低CPU消耗
  • 缺点：
    • 创建索引，维护索引需要耗费时间，而且随着数据量增加而增加
    • 索引占用物理空间
    • 对表中数据的修改需要额外对索引进行修改，增加了数据维护成本
最佳左前缀法则
  • 如果创建的索引是联合索引，使用索引的时候，where后面的子条件需要从索引的最左边开始使用，并且不能跳过索引中列的使用，比如说索引是（A，B），那么查询条件必须也是A,B，如果where条件只有B则不会使用该索引
  • 底层原理：因为MySql创建联合索引时候，构建索引的时候，会先更具左边第一个字段进行排序，如果需要排序的字段值一样才会更具第二个进行排序，依次类推，
索引下推
  • MySql 5.6 推出的功能，在执行查询语句中，如果命中联合索引，在对索引中条件判断的时候，会先去本索引中查看十分有对于的字段，有则直接安本索引中对应的字段进行判断而不会回表进行查询判断。
like 以% 开头索引无效
  • B+ 树构建的索引顺序，是按照字符串的首字母大小来排序，首字母一样才安后续字母大小排序，因此有如下三种情况
    • % 在最右边类似“myName%” 此时我们是查询以myName开头的字符串，所以我们可以安B+树的排序规则安顺序查找
    • % 在最左边"%myName"，此时我们无法得知最左边的数据，因此也无法在B+树种找到你需要的值，只能逐个遍历匹配
    • % 在左右"%myName%"， 此时我们无法得知最左边的数据，因此也无法在B+树种找到你需要的值，只能逐个遍历匹配
explain 主要字段
  • 使用Explain关键字可以模拟优化器来执行SQL查询，从而得知mySql怎么处理我们的SQL语句
  • MySql种Explain返回的值：
    • type：表示查询类型，例如const 主键查询，eq_ref 唯一索引查询，ref 非唯一索引，range 普通范围查询 ，all 全表扫描
    • possible_keys 可以被使用的索引
    • key本次使用的索引
    • rows 本次查询扫描的行数
写失效 & 双写
  • 因为MySql中一个页的大小是16Kb，但是在操作系统中一个页的大小是4KB，所以一次脏页的刷新需要四次操作系统的写入，如果在写入了某个4KB后，系统挂了，就会造成数据丢失。
  • 解决方案：双写缓冲区 Doublewrite Buffer
    • Buffer pool 的page页面刷新到磁盘之前，不会直接写磁盘，而是先执行double write
    • 通过mmemcpy文件映射的方式，先将脏页复制到double write buffer
    • double write buffer 分两次，每次1MB顺序写入共享表空间的物理磁盘 第一次写
    • 完成double write buffer的写入后，再将double write buffer 中页写入表的磁盘文件中
  • 为啥两次：
    • double write 页面是连续存储空间，追加模式的顺序写，速度快性能高
    • 之后的写表空间是随机写，数据离散的，性能低
分库分表
 mail_history_0${0..9}.MyMailHistory_${(0..249).collect(){it.toString().padLeft(3,'0')}},
 mail_history_1${0..9}.MyMailHistory_${(250..499).collect(){it.toString().padLeft(3,'0')}},
 mail_history_2${0..9}.MyMailHistory_${(500..749).collect(){it.toString().padLeft(3,'0')}},
 mail_history_3${0..9}.MyMailHistory_${(750..999).collect(){it.toString().padLeft(3,'0')}}

  • 例如用memebrId作为片键来做分库分表：1611568618

  • 截断后面四位数字8618，那么他所处的数据库以及表应该是 mail_history_2_8.MyMailHistory_618

    • 分片说明：后三位数618 ， 618> 500 & 618 < 749因此被散列到 mail_history_2 中，同时截取数据中第一位是8，因此 数据定位到mail_history_2_8
    • 分表就是安后三位分表即可，因此分表：MyMailHistory_618
    • 最终：mail_history_2_8.MyMailHistory_618

  • 分库分表的方式有：垂直 & 水平

    • 垂直分表：将表安字段分成多表，每一个表存储其中一部分字段
    • 水平分表：将表数据安一定规则，分配到同一个库中的多张表里面，每张表只有部分数据
    • 垂直分库：根据业务不同，分别存放在不同的数据库中，这些库分别部署在不同的服务器。
    • 水平分库：将表中的数据安一定规则，分配到不同数据库，每一个库都只有这张表的部分数据

  • 因此上面案例中采用的是，水平分库+ 水平分表的方式 ，同时在业务层看 用的是垂直分库不同业务对应不同库。

水平分库分表缺点
  • 对数据库扩容非常不友好，因为扩容后需要对数据进行重新散列的过程，需要一个庞大工程
  • 只能通过分片键进行查询，对其他查询条件不友好，其他查询条件只能n张表全表扫
  • 无法支持复杂查询，例如join 夸库查询，只能安分片键join，其他字段都不行
  • 全局的一致性时间点备份&恢复几乎不可实现，因为每个库都有自己的一个redolog和binlog，没有全局统一的快照处理恢复机制
  • 事务一致性问题：水平分库分表后，事务需要跨多个库表来执行，需要额外的开销保障事务的一致性增加复杂度，当前情况是利用sharding jdbc来实现
MySql优化 insert on duplicate key update 导致的AUTO_INCREMENT 耗尽问题

  • 业务场景：

    • 橙子建站是头条一个头发平台，用户注册信息留在头条，广告商通过定时任务的方式定时去拉前面一段时间的用户信息
    • 因此这种业务情况下很大可能导致拉重复数据，因为我们无法得知什么时候拉数据，只能尽可能频繁的获取，因此为了减少mySql中重复数据，用了insert on duplicate key update

  • 在insert on duplicate key update语句中mysql会看尝试insert，此时MySql会用加锁的方式申请自增id，获取到ID后，执行insert，如果失败，才会去执行Update，因此在整个过程中，即使最终执行的是update语句他也会去获取一次自增id，从而导致自增id的跳跃增长。

  • 解决方案有两种：

    • 通过逻辑判断的方式，先update，如果update的返回值> 0 ，那么久说明已经存在这条唯一键的数据，否则执行insert
    • 通过BoomFilter 在数据拉取的时候就做一次判定重复操作，将已经处理过的数据从源头上就筛选选掉

  • 最后结局方案：

    • 第一步，将当前表中已经达到最大上线的自增id表主键int 改为 bigInt
    • 将 insert on duplicate key update 语句改为直接insert语句，并且增加try- cache，普获异常后处理方式中用update的方式
    • 增加BoomFilter 在列表获取的时候对重复数据进行一个筛选，exception 的update逻辑降低到最低

  • 其实用其中一个就行，但是因为try-cache语句对性能有一定的影响，我们通过BoomFilter拦截的方式让当天的exception流程降到最低，减少性能损耗，同时因为还存在补数据的问题，例如，昨天或者前天的数据存在一定的问题或者漏穿的问题，需要重新同步，因此在同步历史数据时候，还是需要通过try cache操作来完成update。

MySql中多个单列索引导致的死锁问题
  • 整个问题是由于多个单列索引的表，存在并发修改的情况，导致的一个死锁问题，
  • 首先我们要知道的是MySql的加锁机制，安索引不同有三种情况：
    • 根据主键进更新：那么只需要通过B+树找到对应的主键记录所在的行数据上加X锁即可
    • 根据唯一键加锁：则有两把锁
      • 第一把锁，通过唯一键的B+树找到唯一键对应的数据，并且在唯一索引上加锁
      • 第二把锁，通过唯一键找到的数据中的主键id，找到聚簇索引上对应的数据行并加锁
    • 普通索引加锁：可能有n把锁，n>=2
      • 前面普通索引上的锁，是因为我们可以一个sql匹配多条语句，因此直接在普通索引上找到对应的数据并加锁在B+树对应的所有找到的数据上
      • 接着是聚簇索引上的锁，同样，根据普通索引上找到的数据对应主键id找到聚簇索引上的数据行加锁
业务中的死锁 & 排查
  • 业务场景是在一个直播卖课的场景，在每个直播间中都有自己的一个课程库存，在用户购买课程后，会扣当前这个直播间的课程库存信息，有如下表结构
CREATE TABLE `course_store` (

`id` int(10) AUTO_INCREMENT COMMENT '主键',

`emotion_course_id` bigint(10) COMMENT '课程id',

`live_id` bigint(10) COMMENT '直播间id',

`course_count` int(10) COMMENT '课程数量',

PRIMARY KEY (`id`),

KEY `idx_emotion_course_id` (`emotion_course_id`),

KEY `idx_live_id` (`live_id`)

) ENGINE=InnoDB COMMENT='直播间课程库存表'
  • 在如上数据表中有两个单字段的索引，在每次下单跟新库存的时候，会执行如下sql语句：
update course_store set course_count = course_count - 1 
where emotion_course_id = #{s} and live_id = #{k} and (course_count - 1) >= 0;
  • 出现的死锁问题也是非常偶现的问题，是在运维这边给的死锁日志中找到的，在MySql终端中执行如下指令可以拿到最后一次的死锁日志，死锁日志中有对应的sql：
update course_store set update_time = now(), course_count = course_count-1 where emotion_course_id='698' and live_id = '7889' and (course_count-1) >= 0

update course_store set update_time = now(), course_count = course_count-1 where emotion_course_id='875' and live_id = '7889' and (course_count-1) >= 0

  • 如上，他修改的是同一个直播间里面不同的课程，但是却死锁了，本来应该锁的不同行的，安道理是不会锁的因为mysql应该只会用其中一个索引

  • 与想的不一样的地方在于InnoDB使用了index_merge，mySql5.1 之后引入的索引合并优化，允许同一个表同时用多个索引查询，并且对多个索引查询结果进行合并（去交集），并集，后返回

  • 在来看如上两个sql就能得出结论，如下图：
    请添加图片描述

  • 如上，首先对于 idx_emotion_course_id 索引：

    • 对689 ，879 分表加锁，接着给主键对应的4， 5 加锁

  • 对于 idx_live_id索引，因为在同一个直播间，因此有n条同一个id的数据

    • 因此会给每一条7889 加锁，接着给7889对应主键4， 5 分别加锁
    • 当S1 给5 加锁时候，就与S2 冲突了，同样S2 在给4 加锁过程中与S1 冲突了

  • 死锁条件很清楚了。解决方案如下：

  • 第一中方式，直接强制走某一个索引，利用 force（idx_emotion_course_id）即可解决

  • 第二中方式，直接建一个联合索引 idx_course_live ，这种方式会让InnoDb就只会走这个联合索引了

MySql连接池关键信息配置
  • 使用druid数据库连接池
initialSize: 50. //初始化连接数50
maxActive：300 //最大连接数300 表示不设置上线
maxWait：2000 //获取连接池中连接的最大等待时间 2000毫秒
timeBetweenEvictionRunsMillis: 5000
minEvictableIdleTimeMillis: 300000
// 每5000 毫秒对连接池进行一次监测，如果有连接空闲时间超过300000 毫秒久回收连接
数据库设计范式
  • 数据库设计三大范式：
    • 第一范式：对于属性的原子性约束，要求属性是不可再分割的
    • 第二范式：对记录的唯一性约束，要求记录具有唯一标示
    • 第三范式：对字段的冗余性约束，即字段不能由其他字段派生出来，要求字段没有冗余
  • 范式化设计的优点&缺点：
    • 优点：尽可能的减少冗余数据，能够让更新更快，数据量更小
    • 缺点：对于查询需要多个表进行关联，更难以进行索引优化。
  • 反范式化：
    • 优点：可以减少表关联，可以更好的进行索引优化
    • 缺点：数据冗余造成数据量变多，修改需要更大成本，常见的反范式化设计有缓存，冗余等
MySql中BufferPool 与Change Buffer的关系
  • BufferPool 与Change Buffer是两个独立的缓存区域，没有明确的关联，Buffer Pool满了只会影响数据页的缓存，不影响Change buffer写入
  • 但是如果Change buffer空间页满了，在进行修改就会触发purge过程，也就是将Change buffer中的记录写入磁盘中对应数据页，这个过程会消耗I/O与CPU资源，对系统有一定影响
MySql中BufferPool满，此时写操作，刷脏，缓存淘汰顺序流程

  • 首先知道，Buffer Pool 与Change Buffer是两个独立的内存空间

  • BufferPool满，与写入操作没有直接关系，因为是写入到change buffer。

  • BufferPool满了写入时候，MySql会启动缓存淘汰，通过LRU，淘汰最近最少使用的页，此时如果淘汰的是脏页，需要执行merge过程

  • MySql中Merge是由后台线程完成，MySql会主动执行，间隔时间由innodb_io_capacity和 innodb_io_capacity_max决定。

  • 如果脏页列表达到一定阈值，或者后天时间间隔到一定时间，MySql会启动刷脏，merge之前先将脏页写入磁盘redo log，在merge，让后移除pageCache，释放BufferPool空间

  • 因此整个写入流程如下：

  1. 要写入的数据所在数据页如果已经在BufferPool中，则直接写入PageCache
  2. 如果写入的数据所在数据页不在BufferPool则我们需要写入到Change Buffer中
  3. 如果Change Buffer 已经满了那么久可能阻塞写入，MySql需要将Change buffer 中的数据purge到对应的索引上去，这个过程涉及到大量的磁盘随机IO操作，操作完成后释放Change Buffer，才能继续写入，对系统的性能有一定的影响。
  4. 采用LRU算法对 LRU_LIST中pageCache进行淘汰释放BufferPool空间
  5. 如果5中被淘汰的是脏页，则执行Merge，先顺序写redolog，在merge，然后才释放pageCache，如果不是脏页，直接释放pageCache空出BufferPool
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值