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的结果
920
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
