MYSQL日常練習(xí)試驗(yàn)

第1章一問一實(shí)

MYSQL日常練習(xí)試驗(yàn)

目錄

1.1information_schema.columns表上做查詢慢?怎么辦？3

問3

實(shí)驗(yàn)4

整理7

1.2information_schema.columns表上做查詢慢，為什么？8

實(shí)驗(yàn)8

1.3SIP漂移時(shí)，會(huì)影響正在使用的數(shù)據(jù)庫連接么？10

問10

實(shí)驗(yàn)10

39與之類似：10

1.4MySQL的復(fù)制心跳說它不想跳了15

問15

實(shí)驗(yàn)15

1.5binlog說一個(gè)begin執(zhí)行了5秒,是誰錯(cuò)了？23

實(shí)驗(yàn)23

1.5binlog說一個(gè)begin執(zhí)行了5秒，是誰錯(cuò)了？24

1.6慢日志覺得一個(gè)SQL很慢，但binlog不這么覺得，怎么辦？26

問26

實(shí)驗(yàn)26

原理28

1.SQL開始28

2.記錄generallog28

3.SQL解析28

4.SQL執(zhí)行過程中，生成binlogevent28

5.binlog刷盤28

6.記錄慢日志28

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

問29

實(shí)驗(yàn)29

結(jié)論：33

1.8—張表只能在一個(gè)bufferpoolinstance中么？34

問題：34

實(shí)驗(yàn)：34

1.9我沒有讓SQL使用聯(lián)合索引，但它不聽37

問37

實(shí)驗(yàn)37

1.10InnoDB刷臟頁慢,會(huì)影響我的業(yè)務(wù)么？40

問40

實(shí)驗(yàn)40

擴(kuò)展知識(shí)45

第1章一問一實(shí)

1.11用mysqlslap壓測(cè)的結(jié)果，為什么比用sysbench的結(jié)果平穩(wěn)？46

問46

實(shí)驗(yàn)46

解讀一下mysqlslap的結(jié)果：46

1.12自旋鎖旋著旋著人就糊涂了49

問49

實(shí)驗(yàn)49

f著名的CPU問題52

1.13分區(qū)表到底要上多少鎖53

問53

實(shí)驗(yàn)53

場(chǎng)景1:54

IX鎖。55

場(chǎng)景2:55

場(chǎng)景3：56

場(chǎng)景4:57

場(chǎng)景5：58

思考題60

1.14如何編譯MySQL的調(diào)試版本62

問62

實(shí)驗(yàn)62

1.15對(duì)進(jìn)行中的DDL進(jìn)行kill.到底多久能響應(yīng)66

問66

實(shí)驗(yàn)66

第二個(gè)堆棧：69

第四個(gè)和第五個(gè)堆棧：70

1.從舊表中讀取聚簇索引的過程71

2.向新表中寫入索引的過程71

3.重建索引時(shí)，刷盤后進(jìn)行檢查71

4.將onlineDDLlog回放到新表的過程71

5.如果在這四個(gè)過程中，發(fā)生了kill,那DDL操作很快就會(huì)開始回滾71

1.16組提交是怎樣提高性能的73

問73

實(shí)驗(yàn)73

1.16組提交是怎樣提高性能的74

1.17MySQL8.0的臨時(shí)表會(huì)讓一片磁盤空間"消失"75

問題75

實(shí)驗(yàn)75

1.其分配的區(qū)域大小會(huì)逐步翻倍79

2.其對(duì)應(yīng)了一個(gè)已經(jīng)刪除的文件79

1.18鎖用得太多，為什么要調(diào)整BufferPool80

問80

實(shí)驗(yàn)80

1.19MySQL的內(nèi)存消耗，有哪些不在performance_schema的統(tǒng)計(jì)范圍84

問84

2

第1章一問一實(shí)

實(shí)驗(yàn)84

總結(jié)89

1.20MySQL的內(nèi)存突增,該如何診斷89

問89

實(shí)驗(yàn)89

局限92

1.21MySQL使用的文件句柄突增，該如何診斷92

問92

實(shí)驗(yàn)92

贈(zèng)送章節(jié)95

1.22Tabledefinitioncache有什么作用96

問96

實(shí)驗(yàn)96

運(yùn)維建議98

1.23為什么MySQL運(yùn)行時(shí)，不鼓勵(lì)調(diào)整系統(tǒng)時(shí)間99

問99

實(shí)驗(yàn)99

1.先打印一個(gè)時(shí)間戳100

2.調(diào)整lock_wait_timeout100

3.訪問test.a表100

思考題101

《一問一實(shí)驗(yàn)》專欄于2020年2月28日開始連載。每篇文章通過一組實(shí)驗(yàn)解答一個(gè)MySQL日常問

題。意在給讀者帶來問題排查的思路和方便的小工具等。內(nèi)容簡(jiǎn)明易懂，實(shí)驗(yàn)過程清晰。已累計(jì)文章發(fā)布48

篇，本冊(cè)從26問一直連載到48問。

1.1information_schema.columns表上做查詢慢?怎么辦？

問

我們有一個(gè)SQL,用于找到?jīng)]有主鍵/唯一鍵的表，但是在MySQL5.7上運(yùn)行特別慢，怎么辦?

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'));

實(shí)驗(yàn)

我們搭建一個(gè)MySQL5.7的環(huán)境，此處省略搭建步驟。

3

第1章一問一實(shí)

寫個(gè)簡(jiǎn)單的腳本，制造一批帶主鍵和不帶主鍵的表:

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#|

執(zhí)行一下腳本:

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

root@ubuntu:~/sandboxes/msb_5_7_25#|

現(xiàn)在執(zhí)行以下SQL看看效果:

4

第1章一問一實(shí)

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))>|

執(zhí)行了16.80s,感覺是非常慢了。

現(xiàn)在用一下DBA三板斧，看看執(zhí)行計(jì)劃:

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))>|

感覺有點(diǎn)慘，由于information_schema.columns是元數(shù)據(jù)表，沒有必要的統(tǒng)計(jì)信息。

那我們來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章一問一實(shí)

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)轉(zhuǎn)換成了

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)〃非關(guān)聯(lián)子查詢

轉(zhuǎn)換成了

selectfromAwherenotexists(select1fromBwhereB.x=a.x)〃關(guān)聯(lián)子查詢

如果我們自己是MySQL,在執(zhí)行非關(guān)聯(lián)子查詢時(shí)，可以使用很簡(jiǎn)單的策略：

selectfromAwhereA.xnotin(selectxfromBwhere...)〃非關(guān)聯(lián)子查詢：

掃描B表中的所有記錄，找到滿足條件的記錄，存放在臨時(shí)表C中，建好索引

2.掃描A表中的記錄，與臨時(shí)表C中的記錄進(jìn)行比對(duì)，直接在索引里比對(duì)，

而關(guān)聯(lián)子查詢就需要循環(huán)迭代：

selectfromAwherenotexists(select1fromBwhereB.x=a.xand...)〃關(guān)聯(lián)

子查

詢掃描A表的每一條記錄nA：

掃描B表，找到其中的第一條滿足rA條件的記錄。

顯然，關(guān)聯(lián)子查詢的掃描成本會(huì)高于非關(guān)聯(lián)子查詢。

我們希望MySQL能先"緩存"子查詢的結(jié)果(緩存這一步叫物化，MATERIALIZATION),但MySQL

認(rèn)為不緩存更快，我們就需要給予MySQL一定指導(dǎo)。

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章一問一實(shí)

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))>

可以看到執(zhí)行時(shí)間變成了0.67s,

整理

我們?cè)\斷的關(guān)鍵點(diǎn)如下：

1.對(duì)于information_schema中的元數(shù)據(jù)表，執(zhí)行計(jì)劃不能提供有效信息。

2.通過查看MySQL改寫后的SQL,我們猜測(cè)了優(yōu)化器發(fā)生了誤判。

3.我們?cè)黾恿薶int,指導(dǎo)MySQL正確進(jìn)行優(yōu)化判斷。

但目前我們的實(shí)驗(yàn)僅限于猜測(cè)，猜中了萬事大吉，猜不中就無法做出好的診斷。

7

第1章一問一實(shí)

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'));

我們還分析了執(zhí)行計(jì)劃改寫后的SQL,通過猜測(cè)，增加了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為什么會(huì)使用一個(gè)低效的執(zhí)行計(jì)劃，以致于我們不得己用

hint來調(diào)優(yōu)SQL?

實(shí)驗(yàn)

我們接著使用上一問中的環(huán)境，使用optimizertrace工具，觀察MySQL對(duì)SQL的優(yōu)化處理過程。

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

我們先調(diào)大optimizertrace的內(nèi)存容量（否則trace的輸出會(huì)被截?cái)啵?，然后開啟了optimizertrace功

能。

跑完SQL后，可以在INFORMATION_SCHEMA.OPTIMIZER_TRACE看到SQL的優(yōu)化處理過程：

8

第1章一問一實(shí)

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

這會(huì)是個(gè)巨大的json,我們將其復(fù)制出來，找個(gè)json的可視化編輯器來分析一下。

小貼士

如果MySQL啟動(dòng)時(shí)有配置--secure-file-priv,那可以用

SELECTTRACEINTODUMPFILE<filename>FROMINFORMATION_SCHEMA.OPTIMIZER__TRACE;

將trace導(dǎo)出到文件里，會(huì)更方便一些。

這里我們選擇了一個(gè)在線的json編輯器，使用起來會(huì)方便一點(diǎn)：

A不安全jsonvlewef.stack.hu

ViewerText

“}JSON

八]知3

0。

j(}creatngtmotao?e

心

”)2

心

opumtzMon

所opDfnizaMn

曲

tt{}|om?wcut>on

可以看到整個(gè)優(yōu)化過程分為6個(gè)步驟，前兩步都跟創(chuàng)建臨時(shí)表相關(guān)，然后是join的準(zhǔn)備工作，再是兩

6

第1章一問一實(shí)

步j(luò)oin優(yōu)化，最后是join的執(zhí)行。

回憶一下上一問中，我們的子查詢應(yīng)使用物化方式，但實(shí)際使用了exists子句方式，我們猜測(cè)這個(gè)選擇

是在join的優(yōu)化階段做出的。

仔細(xì)翻一翻，就會(huì)找到可疑的部分:

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優(yōu)化可能發(fā)生物化的執(zhí)行計(jì)劃

._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

上圖中的中文，是從英文翻譯過來的?？瓷先ノ覀冋覍?duì)了位置。

接下來我們逐步看看這個(gè)決策的依據(jù)是什么：顯然不物化的代價(jià)更小，那么優(yōu)化器選擇不物化是正確的選

擇。

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