导图社区 MySql实战45讲
这是一篇关于MySql实战45讲的思维导图,通过不断学习和实践,你可以更好地利用MySQL来满足业务需求并提高数据存储和处理效率。
编辑于2023-12-21 17:35:18MySql实战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='zhangssxyz@xxx.com';
如果 索引字段 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 利用率很高,但是每秒却执行不了几个事务。
怎么解决由这种热点行更新导致的性能问题呢?
控制对同一行的并发数
关闭死锁检查(存在非常严重的安全问题,将会导致大量的业务超时)
将对一行的修改改成多行的修改