MySql实战45讲

这是一篇关于MySql实战45讲的思维导图,通过不断学习和实践，你可以更好地利用MySQL来满足业务需求并提高数据存储和处理效率。

MySql实战45讲

• 8.事务到底是隔离的还是不隔离的？
  • MySql中视图的概念
    • 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样
    • 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。
  • InnoDB 利用了“所有数据都有多个版本” (MVCC) 的这个特性，实现了“秒级创建快照”的能力。
  • 1.更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读” 2.select 会生成快照读
  • RC(读已提交),RR(可重复读)
    • 在读已提交的隔离级别下,每个语句执行前都会生成一个视图,查询只承认在语句启动前就已经提交完成的数据；
    • 在可重复读的隔离级别下,事务开始时就会生成一个视图,之后一直沿用,只有执行DML语句时,会生成当前读,查询只承认在事务启动前就已经提交完成的数据
• 10.MySQL为什么有时候会选错索引？
  • 原因
    • 优化器错误分析了需要扫描的行数
  • 索引统计信息不准确导致的问题，可以用 analyze table,重新分析表
  • 索引选择异常和处理
    • 查询语句
      • select * from mdm_company where a between 1 and 100 and b between 1000 and 50000 order by b limit 1;
        • 优化器会觉得 对 索引b 进行了排序那么 选择使用索引b将避免排序,按顺序取值即可.所以即使扫描行数多，也判定为代价更小
    • 采用 force index 强行选择一个索引
      • select * from mdm_company force index (a) where a between 1 and 100 and b between 1000 and 50000 order by b limit 1;
      • 假设 a,b都有索引,那么走a索引只需要扫描100行,然后通过索引下推 b 筛选数据
      • 缺点:
        • 如果索引名发送了变化需要变化SQL
    • 修改语句，引导 MySQL 使用我们期望的索引
    • 直接删除误用的索引
• 12 . 为什么我的MySQL会“抖”一下？
  • innoDB中的 Buffer Pool 缓冲池中的内存页有三种状态
    • 第一种:未使用的
    • 第二种:已经使用了并且已经写入磁盘的干净页
    • 第三种:已经使用了但是未写入磁盘的脏页
  • 什么是脏页?
    • 当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”
  • 数据库抖动的时候都是在刷脏页 而数据库刷脏页有四种情况
    • 情况一:
      • redo log日志写满了,此时系统将停止更新操作,执行redo log落盘
        • 这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0
    • 情况二:
      • 系统内存不足,需要淘汰内存,此时就需要将脏页的数据写入磁盘中
    • 情况三:
      • 系统空闲时也会进行刷脏页操作
    • 情况四:
      • Mysql正常关闭时,也会进行刷脏页操作
• 14.count(*)这么慢，我该怎么办？
  • count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值
  • 按照效率排序 count(字段)<count(主键 id)<count(1)≈count(*)
  • Mysql的实现方式
    • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
    • 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
  • 为什么InnoDB 不像 MyISAM一样使用 Count(*) 统计记录数
    • 首先 MyISAM 存储引擎是不支持事务的,而InnoDB是支持事务的,InnoDB使用MVCC多版本并发控制,不同事务获取的记录数应该是不一样的,因此不能使用像MyISAM一样使用变量记录 记录数
  • InnoDB统计总记录数的优化
    • 非聚蔟索引的是不存储数据的,一次页IO能获取更多记录数,因此Mysql优化器会找到最小的那棵树来遍历
  • 能用show tables 来代替 count(*) 吗?
    • 不能,因为show tables 命令虽然有个参数TABLE_ROWS 用于显示这个表当前有多少行,但是这个命令时统计索引的,而索引的统计是存在误差的
  • 把计数放在 Redis 里面，不能够保证计数和 MySQL 表里的数据精确一致的原因，是这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图
• 13.为什么表数据删掉一半，表文件大小不变？
  • 为了避免索引重排,删除数据时,并不会真正的将数据从磁盘上删除,只会标记这个空间可以复用。所以执行delete命令把整张表都删除时,所有的数据页都会标记可复用,但是磁盘上文件不会变小
    • 页分裂现象(乱序插入)
      • 执行alter table A engine=InnoDB 命令来重建表. 此命令将会完成创建临时表,完成转存数据、交换表名、删除旧表的操作 此命令在5.5之前的版本会阻塞DML语句,而在5.6之后不会
        • 显然，花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的
    • 页合并现象
• 11.怎么给字符串字段加索引？
  • 字符长度很长的字段 如邮箱 该如何建立索引
    • 前缀索引
      • 存在的问题
        • 将会增加需要扫描的行数,影响性能
          • 如果区分度不高,会增加需要扫描的行数 . 索引树上匹配到结果后 需回主键索引获取记录,结果发现不相等,则需要再次回到索引数上读取下一条记录
          • 解决办法
            • select count(distinct left(email,4)）as L4, count(distinct left(email,5)）as L5, count(distinct left(email,6)）as L6, count(distinct left(email,7)）as L7, from SUser;
            • 计算区分度,区分度不少于 L*95% 的,适合建立前缀索引
        • 影响索引覆盖
          • select id,email from SUser where email='[e-mail]';
          • 如果 索引字段 email 使用的是 整个字符串的索引结构,那么只需要在 email的索引树上查询结果,利用索引覆盖就能直接返回结果集 而如果 emila字段 使用了前缀索引,则必须回表判断email字段的值
    • 直接创建完整索引，这样可能比较占用空间； 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引； 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题； 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
• 9.普通索引和唯一索引，应该怎么选择？
  • 更新区别
    • 对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录
    • 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
    • 效率差微乎其微,可以忽略 InnoDB 引擎一次读取一页数据,每一页16KB,如果k=5 恰好在当前页的最后一条记录,那么需要再进行一次IO操作
  • 插入语句区别
    • 什么是Change Buffer
      • 当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
      • 只能作用于普通索引
      • Change Buffer 适合于 写多读少的场景 , 常见的如日志系统,记账系统 如果是 写入后立马要读 那么维护Change Buffer的成本太大 不停的merge
      • Change Buffer 的操作也会被记录到 redo log 中 所以即时 数据库出现异常重启,Change Buffer中的数据还没来得及执行,也不会丢失数据
    • 唯一索引和普通索引更新操作的区别
      • 如果要更新的数据恰好在当前内存中
        • 普通索引直接在指定位置插入数据,而唯一索引需先判断是否有索引冲突,再插入数据
      • 如果要更新的数据不在当前页中
        • 普通索引将记录存入Change Buffer中语句结束,而唯一索引需判断是否有索引冲突,需先将数据页读入内存中,判断没有冲突后再插入值
• 7.怎么减少行锁对性能的影响？
  • 在InnoDB事务中,行锁是在需要的时候才加上,但并不是不需要了就立马释放,而是需要等到事务提交后才释放
    • 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放 ( 例如 update 更新个人余额 与 update 更新公司余额 在同一个事务中执行,那么应将 update 更新公司余额放在最后执行 , 因为对公司这一行记录加行锁 的影响会比 对个人余额加行锁的影响大 )
  • InnoDB出现死锁怎么解决?
    • 出现死锁的原因
      • 两个事务互相占用对方需要的资源
    • 解决办法
      • 直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置 。 InnoDB 默认的超时时间时 50s
        • 如果出现了死锁问题需要等待50s 那么对业务的影响将非常巨大, 如果我们将超时时间 设置的很短,那么如果如果是其他正常需要加锁的业务又会在短时间内丢掉锁,对业务造成影响
      • 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑 一致性读并不会死锁检测
        • 每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。
  • 怎么解决由这种热点行更新导致的性能问题呢？
    • 控制对同一行的并发数
    • 关闭死锁检查(存在非常严重的安全问题,将会导致大量的业务超时)
    • 将对一行的修改改成多行的修改