MYSQL日常练习试验

第1章一问一实

MYSQL日常练习试验

目录

1.1information_schema.columns表上做查询慢•怎么办？3

问3

实验4

整理7

1.2information_schema.columns表上做查询慢，为什么？8

实验8

1.3SIP漂移时，会影响正在使用的数据库连接么？10

问10

实验10

39与之类似：10

1.4MySQL的复制心跳说它不想跳了15

问15

实验15

1.5binlog说一个begin执行了5秒,是谁错了？23

实验23

1.5binlog说一个begin执行了5秒，是谁错了？24

1.6慢日志觉得一个SQL很慢，但binlog不这么觉得，怎么办？26

问26

实验26

原理28

1.SQL开始28

2.记录generallog28

3.SQL解析28

4.SQL执行过程中，生成binlogevent28

5.binlog刷盘28

6.记录慢日志28

1.7innodb_log_buffer_size至!J底有什么作用?29

问29

实验29

结论：33

1.8—张表只能在一个bufferpoolinstance中么？34

问题：34

实验：34

1.9我没有让SQL使用联合索引，但它不听37

问37

实验37

1.10InnoDB刷脏页慢,会影响我的业务么？40

问40

实验40

扩展知识45

第1章一问一实

1.11用mysqlslap压测的结果，为什么比用sysbench的结果平稳？46

问46

实验46

解读一下mysqlslap的结果：46

1.12自旋锁旋着旋着人就糊涂了49

问49

实验49

f著名的CPU问题52

1.13分区表到底要上多少锁53

问53

实验53

场景1:54

IX锁。55

场景2:55

场景3：56

场景4:57

场景5：58

思考题60

1.14如何编译MySQL的调试版本62

问62

实验62

1.15对进行中的DDL进行kill.到底多久能响应66

问66

实验66

第二个堆栈：69

第四个和第五个堆栈：70

1.从旧表中读取聚簇索引的过程71

2.向新表中写入索引的过程71

3.重建索引时，刷盘后进行检查71

4.将onlineDDLlog回放到新表的过程71

5.如果在这四个过程中，发生了kill,那DDL操作很快就会开始回滚71

1.16组提交是怎样提高性能的73

问73

实验73

1.16组提交是怎样提高性能的74

1.17MySQL8.0的临时表会让一片磁盘空间"消失"75

问题75

实验75

1.其分配的区域大小会逐步翻倍79

2.其对应了一个已经删除的文件79

1.18锁用得太多，为什么要调整BufferPool80

问80

实验80

1.19MySQL的内存消耗，有哪些不在performance_schema的统计范围84

问84

2

第1章一问一实

实验84

总结89

1.20MySQL的内存突增,该如何诊断89

问89

实验89

局限92

1.21MySQL使用的文件句柄突增，该如何诊断92

问92

实验92

赠送章节95

1.22Tabledefinitioncache有什么作用96

问96

实验96

运维建议98

1.23为什么MySQL运行时，不鼓励调整系统时间99

问99

实验99

1.先打印一个时间戳100

2.调整lock_wait_timeout100

3.访问test.a表100

思考题101

《一问一实验》专栏于2020年2月28日开始连载。每篇文章通过一组实验解答一个MySQL日常问

题。意在给读者带来问题排查的思路和方便的小工具等。内容简明易懂，实验过程清晰。已累计文章发布48

篇，本册从26问一直连载到48问。

1.1information_schema.columns表上做查询慢•怎么办？

问

我们有一个SQL,用于找到没有主键/唯一键的表，但是在MySQL5.7上运行特别慢，怎么办?

SELECTDISTINCTtable_name,

table_schema

FROMinformation_schema.columns

WHEREtable_schemaNOTIN('sys','information_schema','my.sql',

*performance_schema'

有主键/唯一键的表

ANDtable_name[NOTIN(SELECTDISTINCTtable_name

取反FROMinformation_schema.columns

WHEREcolumnkeyIN('PRI','UNI'));

实验

我们搭建一个MySQL5.7的环境，此处省略搭建步骤。

3

第1章一问一实

写个简单的脚本，制造一批带主键和不带主键的表:

root@ubuntu:~/sandboxes/msb_5_7_25#catmake_tables.sh

#!/bin/bash

foriin<1..1000}

do

~/sandboxes/msb_5_7_25/usetest-e"createtablewith_pk_${i}(pkintprimarykey)'*

-/sandboxes/msb_5_7_25/usetest-e"createtablewithout_pk_${i}(colint)"

done

root@ubuntu:-/sandboxes/msb_5_7_25#|

执行一下脚本:

root@ubuntu:~/sandboxes/msb_5_7_25#bashmake_tables.sh

root@ubuntu:~/sandboxes/msb_5_7_25#|

现在执行以下SQL看看效果:

4

第1章一问一实

mysql[localhost:5725]{msandbox}((none))>SELECTDISTINCTtable_name,

->table.schema

->FROMinformation_schema.columns

->WHEREtable_schemaNOTIN('sysT,'information.schema*,'mysql*,

->'performance_schema,

~>)

->ANDtable_nameNOTIN(SELECTDISTINCTtable_name

->FROMinformation_schema.columns

->WHEREcolumn.keyIN(,PRI,,'UNI'));

+++

Itable_nameItable_schemaI

+++

Iwithout_pk_992ItestI

Iwithout_pk_993ItestI

Iwithout_pk_994ItestI

Iwithout_pk_995ItestI

Iwithout_pk_996ItestI

Iwithout_pk_997ItestI

Iwithout_pk_998ItestI

Iwithout_pk_999ItestI

+++

1000rowsinset(16.80sec)

mysql[localhost:5725]{msandbox}C(none))>|

执行了16.80s,感觉是非常慢了。

现在用一下DBA三板斧，看看执行计划:

mysql[localhost:5725]{msandbox)((none))>explainSELECTDISTINCTtoble_nome,

->tGble_schema

->FROMinformotion_schema.columns

->WHEREtable_schemaNOTIN('sys','informotton.scheffla',,mysql',

->'performflnce.schema,

->)

->ANDtable.naneNOTIN(SELECTDISTINCTtable_name

->FROMinformation_schema.columns

->WHEREcolumn_keyIN('PRI','UNI'));

IidIselect.typeItableIpartitionsItypeIpossible_keysIkeyIkey_lenIrefIrowsIfilteredIExtra

I1IPRIMARYIcolumnsINULLIALLINULLINULLINULLINULLINULLINULLIUsingwhere;Open_frm_only;Scannedallda

tabases;UsingtemporaryI

I2IDEPENDENTSU8QUERYIcolumnsINULLIALLINULLINULLINULLINULLINULLINULLIUsingwhere;Open_frm_only;Scannedalldo

tobasesI

2rowsinset,1warning(0.00sec)

mysql[localbost:5725]{msandbox)((none))>|

感觉有点惨，由于information_schema.columns是元数据表，没有必要的统计信息。

那我们来showwarnings看看MySQL改写后的SQL：

mysql[1ocalhost:5725]{msandbox)((none))>showwarnings\G

*•••1.row

Level:Note

Code:1063

Message:/*select#1*/selectdistinctinformation.scheno.columns.TABLENAMEAStable_nome,information_schemo.'columns.TABLE.SCHEMAAStable^sc

hemofrominformation_scheraa'.columnswhere(Cinformation_$chemo'.columns.TABLE.SCHEMA'notin(,sys,,'mfonnatton_schejna,,'mysql,,'performance.schema

'))and(not(<in_optimizer>(xnformotion.schema'.columns'.TA8LE_NAME,<exists>(/,select«2,/select1from'xnfonnot-ion_schema'.columns'where(('informat

ion_schema'.'columns'.'COLUMN_KEY'in(TRI*,'UNI*))and(<cache>Cinformation_schema'.'columns'.,TABLE_NAME*)-"information_schema'."columns'.'TABLE_NAME'))

)))))

1rowinset(0.00sec)

mysqlnocalho$t:5725]{msandbox)((none))>|

我们格式化一下SQL：

5

第1章一问一实

1/*select#】/

2SELECTDISTINCT'information_schema'columns'table_name*AS'table_name',

3'information_schema'.'columns'.'table_schema'AS'table_schema'

FROM'information_schema'.'columns'

5WHERE((

6'information_schema'.'columns'.*table_schema'NOTIN(,sys',

'informationschema',

8'•nysqU,

9'performance_scheraa'))

10ANDnotin(selectinner)转换成了

11N0T(not(select1whereinner.x=outer.x)

12in_optimizer('information_schema'.'columns'.'table_name*,EXISTS

13

14/♦select#2

15SELECTf~~

16FROM'informationschema'.'columns'

17WHERE((

18'infomation_schema*.'columns'.'column_key*IN(•PRI',

19~UNI'))

20

21:cache,('information_schema'・'columns'・'tabljname')「'information_schema','coluens'・'tabljname'))))

可以看到MySQL将

selectfromAwhereA.xnotin(selectxfromB)〃非关联子查询

转换成了

selectfromAwherenotexists(select1fromBwhereB.x=a.x)〃关联子查询

如果我们自己是MySQL,在执行非关联子查询时，可以使用很简单的策略：

selectfromAwhereA.xnotin(selectxfromBwhere...)〃非关联子查询：

扫描B表中的所有记录，找到满足条件的记录，存放在临时表C中，建好索引

2.扫描A表中的记录，与临时表C中的记录进行比对，直接在索引里比对，

而关联子查询就需要循环迭代：

selectfromAwherenotexists(select1fromBwhereB.x=a.xand...)〃关联

子查

询扫描A表的每一条记录nA：

扫描B表，找到其中的第一条满足rA条件的记录。

显然，关联子查询的扫描成本会高于非关联子查询。

我们希望MySQL能先"缓存"子查询的结果(缓存这一步叫物化，MATERIALIZATION),但MySQL

认为不缓存更快，我们就需要给予MySQL一定指导。

mysql[localhost:5725]{msandbox)((none))>SELECTDISTINCTtable_name,

->table_schema

->FROMinformation.schema.columns

->WHEREtable.schemaNOTIN('sys',,information_schema,,*mysql,,

->,performance_schema,

->ANDNOTIN(SELECT(7^SUBOUERY(MATERIALIZATION)*/ID工STINCTtable_name

->FROMinformation.schema.columns

->WHEREcolumn.keyIN('PRI','UNI'));

+++

6

第1章一问一实

Iwithout_pk_993Itest

Iwithout_pk_994Itest

Iwithout_pk_995Itest

Iwithout_pk_996Itest

Iwithout_pk_997Itest

Iwithout_pk_998Itest

Iwithout_pk_999Itest

1000rowsinset(0.67sec)

mysql[localhost:5725]{msandbox)((none))>

可以看到执行时间变成了0.67s,

整理

我们诊断的关键点如下：

1.对于information_schema中的元数据表，执行计划不能提供有效信息。

2.通过查看MySQL改写后的SQL,我们猜测了优化器发生了误判。

3.我们增加了hint,指导MySQL正确进行优化判断。

但目前我们的实验仅限于猜测，猜中了万事大吉，猜不中就无法做出好的诊断。

7

第1章一问一实

1.2information_schema.columns表上做查询慢，为什么？

在上一问中，我们看到了如下SQL在MySQL5.7中跑得很慢:

SELECTDISTINCTtable_name,

table_schema

FROMinformation_schema.columns

WHEREtable_schemaNOTIN(',1information_schema',',

'performance_schema'

有主犍/唯一键的表

ANDtablenameNOTIN(SELECTDISTINCTtable_name

取反FROMinformationschema.columns

WHEREcolumnkeyIN('PRI','UNI'));

我们还分析了执行计划改写后的SQL,通过猜测，增加了hint来解决问题:

mysql[localhost:5725]{msandbox}((none))>SELECTDISTINCTtable_name,

->table_schema

->FROMinformation.schema.columns

->WHEREtable_schemaNOTIN(*sys*,,information_schema,,*mysql',

->,performance_schema,

->ANDtable_nameNOTIN(SELECTf7*+SUBQUERY(MATERIALIZATION)*71DISTINCT

->FROMinformation-schema.columns

->WHEREcolumn_keyIN(*PRI,,'UNI'));

这一期，我们通过工具来分析一下：MySQL为什么会使用一个低效的执行计划，以致于我们不得己用

hint来调优SQL?

实验

我们接着使用上一问中的环境，使用optimizertrace工具，观察MySQL对SQL的优化处理过程。

T>ysqlRocalhost:5725]{msandbox)((none))>setoptimizer_tracejnax_mem_size-1048576;

QueryOK,0rowsaffected(0.01sec)

开启optimizertrace

mysqlClocalhost:5725]{msandbox)((none))>setoptimizer_troce-"enabled-on";

QueryOK,0rowsaffected(0.00sec)

mysqln.ocalhost:5725]{msandbox)((none))>selectdistincttable_name,table_schemafrominformation_schema.columnswheretable_schena

一schema')

->andtable_namenotin(selectdistincttable_namefrominfonnation_schema.columnswherecolumn_keyin('PRI*,*UNI*));

Itable_nameItable-schemaI开始跑SQL

Iwithout_pk_lItest

Iwithout_pk.l0Itest

Iwithout_pk_100Itest

Iwithout_pk_1000Itest

我们先调大optimizertrace的内存容量（否则trace的输出会被截断），然后开启了optimizertrace功

能。

跑完SQL后，可以在INFORMATION_SCHEMA.OPTIMIZER_TRACE看到SQL的优化处理过程：

8

第1章一问一实

mysql[localhost:5725]{msandbox)((none))>SELECTTRACEFROMINFORMATION_SCHEMA.OPTIMIZER_TRACE\G

***************************1.row***************************

TRACE:{

"steps":[

{

Mcreating_tmp_table":{

"tmp_table_info":{

"row.length":6015,

"key_length":0,

"unique.constraint":false,

"location":"disk(InnoDB)",

"record_format":''packed"

)

)

},

{

"creating_tn^_tableM:{

"tmp_table_info,':{

"row_length":6015,

"key_length":0,

',unique.constraint',:false,

"locationM:"disk(InnoDB)M,

"record_fonnat":"packed"

)

"join_preparation":{

"select#":1,

"steps":[

{

______"IN_uses_bisection”:true

这会是个巨大的json,我们将其复制出来，找个json的可视化编辑器来分析一下。

小贴士

如果MySQL启动时有配置--secure-file-priv,那可以用

SELECTTRACEINTODUMPFILE<filename>FROMINFORMATION_SCHEMA.OPTIMIZER__TRACE;

将trace导出到文件里，会更方便一些。

这里我们选择了一个在线的json编辑器，使用起来会方便一点：

A不安全jsonvlewef.stack.hu

ViewerText

“}JSON

八]知3

0。

j(}creatngtmotao«e

心

”)2

心

opumtzMon

所opDfnizaMn

曲

tt{}|om«wcut>on

可以看到整个优化过程分为6个步骤，前两步都跟创建临时表相关，然后是join的准备工作，再是两

6

第1章一问一实

步join优化，最后是join的执行。

回忆一下上一问中，我们的子查询应使用物化方式，但实际使用了exists子句方式，我们猜测这个选择

是在join的优化阶段做出的。

仔细翻一翻，就会找到可疑的部分:

ViewerText

T{}JSON

d[]Steps

3。

3{}creating_tmptable

心

㈤(}creatmg_tmp_table

3{)2

出0jan_preparation

乂)3

国(}join_optimization

m1)4

id()joln_optimization

■select#:2

d[]steps

㈤0。

处

国02

㈤03

©04

田05

田。6优化可能发生物化的执行计划

._aQj?______________________.

[.C)executionpianforpoientiaLmaterializaUon]

＞决定:子查询是否要使用物化策略

J[]parent_fanouts

日0。

■select#:1

-subq_attached_to^tabte:true

■table:"informationschema'.coJumns"

■fanout:0

，cacheatte:true

■cost_to_create.and_ffll_matenalized_table:3.599

■cost_of_one_EX!STS:1399

■nwnberof_subquery_evaluations:0

■cost_o(_matenalizabon:3.599

■8St_of_EXISTS:0

-chosen:false决定不要物化

㈤。8

^()9

a(}io

35

田(}join_execution

上图中的中文，是从英文翻译过来的。看上去我们找对了位置。

接下来我们逐步看看这个决策的依据是什么：显然不物化的代价更小，那么优化器选择不物化是正确的选

择。

j[}execution_plan_forjx)tential_matenalization

U[]steps

□(}subq.matdecision

d[]parenLfanouts

3{)0

■select#:1

，subq_attached_to_table:true

■table:"'informationschema'columns"

■fanout:0

，cacheabie:true

■costJo_createand.fillmaterialized,table:3.599

■cost_of_one_EXISTS:1.399

■number,of.sutxjueryevaluations:0