1.SQL语句为什么变“慢”了
当内存数据页跟磁盘数据页内容不一致的时候, 我们称这个内存页为“脏页”。 内存数据写入到磁盘后, 内存和磁盘上的数据页的内容就一致了, 称为“干净页” , 平时执行很快的更新操作, 其实就是在写内存和日志, 而MySQL偶尔“抖”一下的那个瞬间, 可能就是在刷脏页(flush) 。
2. 什么情况会引发数据库的flush过程呢?
- InnoDB的redo log写满了。 这时候系统会停止所有更新操作, 把checkpoint往前推进, redo log留出空间可以继续写。
- 系统内存不足。 当需要新的内存页, 而内存不够用的时候, 就要淘汰一些数据页, 空出内存给别的数据页使用。 如果淘汰的是“脏页”, 就要先将脏页写到磁盘。
- MySQL认为系统“空闲”的时候。 当然, MySQL“这家酒店”的生意好起来可是会很快就能把粉板记满的, 所以“掌柜”要合理地安排时间, 即使是“生意好”的时候, 也要见缝插针地找时间, 只要有机会就刷一点“脏页”
- MySQL正常关闭的情况。 这时候, MySQL会把内存的脏页都flush到磁盘上, 这样下次MySQL启动的时候, 就可以直接从磁盘上读数据, 启动速度会很快
3. 分析一下上面四种场景对性能的影响。
第三种情况是属于MySQL空闲时的操作, 这时系统没什么压力, 而第四种场景是数据库本来就要关闭了。 这两种情况下, 你不会太关注“性能”问题。 .
-
第一种是“redo log写满了, 要flush脏页”, 这种情况是InnoDB要尽量避免的。 因为出现这种情况
的时候, 整个系统就不能再接受更新了, 所有的更新都必须堵住。 如果你从监控上看, 这时候更
新数会跌为0 -
第二种是“内存不够用了, 要先将脏页写到磁盘”, 这种情况其实是常态。 InnoDB用缓冲池
(buffer pool) 管理内存, 缓冲池中的内存页有三种状态:- 第一种是, 还没有使用的
- 第二种是, 使用了并且是干净页
- 第三种是, 使用了并且是脏页
如果要淘汰的是一个干净页, 就直接释放出来复用; 但如果是 脏页呢, 就必须将脏页先刷到磁盘, 变成干净页后才能复用。
4 刷脏页出现以下这两种情况
- 一个查询要淘汰的脏页个数太多, 会导致查询的响应时间明显变长
- 日志写满, 更新全部堵住, 写性能跌为0, 这种情况对敏感业务来说, 是不能接受的。
所以, InnoDB需要有控制脏页比例的机制, 来尽量避免上面的这两种情况
5 InnoDB刷脏页的控制策略
InnoDB会在后台刷脏页, 而刷脏页的过程是要将内存页写入磁盘。 所以, 无论是你的查询语句在需要内存时候可能要求淘汰一个脏页, 还是由于刷脏页的逻辑会占用IO资源并可能影响到了你的更新语句, 都可能是造成你从业务端感知到MySQL“抖”了一下的原因。
要解决就要用到innodb_io_capacity这个参数了, 它会告诉InnoDB你的磁盘能力 , 这个值设置成磁盘的IOPS。 磁盘的IOPS可以通过fio这个工具来测试 , 没能正确地设置innodb_io_capacity参数, 而导致的性能问题也比比皆是
6 count(*)的实现方式
- MyISAM引擎把一个表的总行数存在了磁盘上, 因此执行count(*)的时候会直接返回这个数,效率很高
- InnoDB引擎就麻烦了, 它执行count(*)的时候, 需要把数据一行一行地从引擎里面读出来, 然后累积计数 由于多版本并发控制(MVCC) 的原因, InnoDB表“应该返回多少行”也是不确定的
count(*)操作的时候还是做了优化的
7 不同的count用法
- count(主键id)来说, InnoDB引擎会遍历整张表, 把每一行的id值都取出来, 返回给server
层。 server层拿到id后, 判断是不可能为空的, 就按行累加 - count(1)来说, InnoDB引擎遍历整张表, 但不取值。 server层对于返回的每一行, 放一个
数字“1”进去, 判断是不可能为空的, 按行累加 - count(字段) 判断到有可能是null, 还要把值取出来再判断一下, 不是null才累加。
- count()是例外, 并不会把全部字段取出来, 而是专门做了优化, 不取值。 count()肯定不是null, 按行累加
count(字段)<count(主键id)<count(1)≈count(*)
8 “order by”是怎么工作的?
- 全字段排序 , 对于InnoDB表来说, 执行全字段排序会减少磁盘访问, 因此会被优先选择
执行流程 :
- rowid排序 ,MySQL认为排序的单行长度太大就使用 rowid排序, rowid排序多访问了一次表t的主键索引
9 全字段排序 VS rowid排序 是怎么选择的?
- 如果MySQL实在是担心排序内存太小, 会影响排序效率, 才会采用rowid排序算法, 这样排序过程中一次可以排序更多行, 但是需要再回到原表去取数据。
- 如果MySQL认为内存足够大, 会优先选择全字段排序, 把需要的字段都放到sort_buffer中, 这
样排序后就会直接从内存里面返回查询结果了, 不用再回到原表去取数据。 - 不是所有的order by语句, 都需要排序操作的。 从上面分析的执行过程,MySQL之所以需要生成临时表, 并且在临时表上做排序操作, 其原因是原来的数据都是无序的
总结:如果内存够, 就要多利用内存, 尽量减少磁盘访问 , 如果内存够, 就要多利用内存, 尽量减少磁盘访问
10 优化排序字段可以采用的方法有哪些?
MySQL之所以需要生成临时表, 并且在临时表上做排序操作, 其原因是原来的数据都是无序的 ,我们可以保证 ,从索引上取出来的行, 天然就是按照name递增排序的话
可以采用 联合索引 , 覆盖索引
11 SQL平时遇到的坑
对索引字段做函数操作, 可能会破坏索引值的有序性, 因此优化器就决定放弃走树搜索功能
例子:
- 平时查询时间create_time ='2018-7-1’是可以用到索引的,假如使用到了计算month()函数的话就放弃索引了
- 隐式类型转换
假如有个查询条件字段类型是varchar(32), 而输入的参数却是整型, 需要做类型转换
mysql> select * from tradelog where tradeid=110717;
对于优化器来说, 这个语句相当于:
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
//对索引字段做函数操作, 优化器会放弃走树搜索功能。
-
隐式字符编码转换
假设A表的字符编码是 utf8mb4 ,B表示utf8时,做连表查询
- select * from a.x=b.x where b.xxx=‘x’
select * from a.age=b.age where b.name='x' --这条语句会先去B表里面查询name=x的数据,然后在取出AGE,再A表 select * from A where age='$B.age.value' --这时候$R4.tradeid.value的字符集是utf8, 按照字符集转换规则, 要转成utf8mb4, select * from A where age= CONVERT($R4.tradeid.value USING utf8mb4) --能正常用到索引
-
select * from a.x=b.x where a.xxx=‘x’
select * from a.age=b.age where a.name='x' --这条语句会先去a表里面查询name=x的数据,然后在取出AGE,再B表 select * from B where age='$A.age.value' --这时候$A.age.value的字符集是utf8, 按照字符集转换规则, 要转成utf8mb4, select * from B where CONVERT(age USING utf8mb4)=$A.age.value --**对索引字段做函数操作,不能正常用到索引
12使用join的过程
有索引数据库是怎么做操作的
- 对驱动表t1做了全表扫描, 这个过程需要扫描100行;
- 而对于每一行R, 根据a字段去表t2查找, 走的是树搜索过程。 由于我们构造的数据都是一一
对应的, 因此每次的搜索过程都只扫描一行, 也是总共扫描100行; - 所以, 整个执行流程, 总扫描行数是200。
如果没有索引数据库是怎么做操作的
- 把表t1的数据读入线程内存join_buffer中, 由于我们这个语句中写的是select *, 因此是把整
个表t1放入了内存 - 扫描表t2, 把表t2中的每一行取出来, 跟join_buffer中的数据做对比, 满足join条件的, 作为
结果集的一部分返回。
13 到底可不可以使用join?
使用join前提是“可以使用被驱动表的索引。
- 使用join语句, 性能比强行拆成多个单表执行SQL语句的性能要好
- 如果使用join语句的话, 需要让小表做驱动表。
总结
判断要不要使用join语句时, 就是看explain结果里面, Extra字段里面有没有出现“BlockNested Loop”字样。
-
如果可以使用被驱动表的索引, join语句还是有其优势的;
-
不能使用被驱动表的索引, 只能使用Block Nested-Loop Join算法, 这样的语句就尽量不要使用
-
使用小表做驱动表。
14 如果要使用join, 应该选择大表做驱动表还是选择小表做驱动表?
使用小表做驱动表。
假如大表有1W行,小表有100行,这时候有索引的时候会先去小表扫描100行,再根据条件去B表查询,扫描行数少了
15 group by 执行流程
select id%10 as m, count(*) as c from t1 group by m;
这个语句的执行流程是这样的:
- 创建内存临时表, 表里有两个字段m和c, 主键是m;
- 扫描表t1的索引a, 依次取出叶子节点上的id值, 计算id%10的结果, 记为x;
如果临时表中没有主键为x的行, 就插入一个记录(x,1);
如果表中有主键为x的行, 就将x这一行的c值加1; - 遍历完成后, 再根据字段m做排序, 得到结果集返回给客户端
16 执行过程中会发现内存临时表大小到达了上限 怎么办?
内存临时表的大小是有限制的,参数tmp_table_size就是控制这个内存大小的, 默认是16M , 执行过程中会发现内存临时表大小到达了上限。那么, 这时候就会把内存临时表转成磁盘临时表, 磁盘临时表默认使用的引擎是InnoDB。
17 group by 优化方法 (两种)
要解决group by语句的优化问题, 你可以先想一下这个问题: 执行group by语句为什么需要临时表?
group by的语义逻辑, 是统计不同的值出现的个数。 但是, 由于每一行的id%100的结果是无序
的, 所以我们就需要有一个临时表, 来记录并统计结果。 其实跟order by优化原理和运行流程是一样的
我们只要保证 扫描过程中可以保证出现的数据是有序的。InnoDB的索引, 就可以满足这个输入有序的条件 。
解决原理是什么?
如果可以确保输入的数据是有序的, 那么计算group by的时候, 就只需要从左到右,顺序扫描, 依次累加。 也就是下面这个过程:
- 当碰到第一个1的时候, 已经知道累积了X个0, 结果集里的第一行就是(0,X);
- 当碰到第一个2的时候, 已经知道累积了Y个1, 结果集里的第一行就是(1,Y); 按照这个逻辑执行的话, 扫描到整个输入的数据结束, 就可以拿到group by的结果, 不需要临时表, 也不需要再额外排序。
优化方法?
- 索引
- 直接排序
如果碰上不适合创建索引的场景, 直接走磁盘临时表的方法 ,在group by语句中加入SQL_BIG_RESULT这个提示(hint) , 就可以告诉优化器: 这个语句涉及的数据量很大, 请直接用磁盘临时表 , 从磁盘空间考虑, 还是直接用数组来存吧。
18. union 执行流程
(select 1000 as f) union (select id from t1 order by id desc limit 2);
--这条语句用到了union, 它的语义是, 取这两个子查询结果的并集。 并集的意思就是这两个集合加起来, 重复的行只保留一行。
这个语句的执行流程是这样的:
- 创建一个内存临时表, 这个临时表只有一个整型字段f, 并且f是主键字段
- 执行第一个子查询, 得到1000这个值, 并存入临时表中。
- 执行第二个子查询:
拿到第一行id=1000, 试图插入临时表中。 但由于1000这个值已经存在于临时表了, 违
反了唯一性约束, 所以插入失败, 然后继续执行;
取到第二行id=999, 插入临时表成功。 - 从临时表中按行取出数据, 返回结果, 并删除临时表, 结果中包含两行数据分别是1000和999。
把上面这个语句中的union改成union all的话, 就没有了“去重”的语义。 这样执行的时候, 就依次执行子查询, 得到的结果直接作为结果集的一部分, 发给客户端。 因此也就不需要临时表了
18 MySQL什么时候会使用内部临时表?
union、 union all和group by语句
19 group by 优化建议
- 如果对group by语句的结果没有排序要求, 要在语句后面加 order bynull;
- 尽量让group by过程用上表的索引, 确认方法是explain结果里没有Using temporary和 Using filesort;
- 如果group by需要统计的数据量不大, 尽量只使用内存临时表; 也可以通过适当调大tmp_table_size参数, 来避免用到磁盘临时表;
- 如果数据量实在太大, 使用SQL_BIG_RESULT这个提示, 来告诉优化器直接使用排序算法
得到group by的结果