MySQL查询优化分享.pdf

MySQL查询优化EN MOTECH百和富里9 ENMOTECH 只查询一条数据为什么执行这么慢？同样的逻辑，不同SQL执行时间差这么多？业务代码没有变更，SQL返回时间怎么突然增加了？数据库服务器负载居高不下，要优化，这么多SQL从哪下手？数/据/驱/动 成/就/未/来MySQL逻辑架构数/据/驱/动 成/就/未/来9 ENMOTECH百和富里9 ENMOTECH目录01怎么发现和定位慢SQLQ MySQL是如何访问数据的Q MySQL的优修04行计划数/据/驱/动成/就/未/来05tips怎么发现慢SQL数/据/驱/动 成/就/未/来数/据/驱/动 成/就/未/来9 ENMOTECH 从当前查询中获取问题SQL 从慢日志中获取问题SQL sys schema相关视图中获取问题SQL百和富里9 ENMOTECH 查看MySQL服务器内部当前线程正在执行的操作 除非具有process权限，否则只能看到自己发起的线程信息 Select*from information_Schema.processlist;Show full processlist;不使用full则只显示info字段前100个字符instance_name 10:44:41show processlist;+-+-+-+-+-+-+-+-+I Id I User I Host I db I Command I Time I State I Info I+-+-+-+-+-+-+-+-+I 5 I event_scheduler I localhost I NULL I Daemon I 568 I Waiting on empty queue I NULL II 8 I root I localhost I performance_schema I Query I 0 I starting I show processlist I2 rows in set(0.00 sec)instance_name 10:44:58show full processlist;+-+-+-+-+-+-+-!-+I Id I User I Host I db I Command I Time I State I Info II 5 I event_scheduler I localhost I NULL I Daemon I 581 I Waiting on empty queue I NULL II 8 I root I localhost I performance_schema I Query I 0 I starting I show full processlist I+-+-+-+-+-+-+-+2 rows in set(0.00 sec)instance_name 10:45:llselect*from information_Schema.processlist;I ID I USER I HOST I DB I COMMAND I TIME I STATE I INFO I+-+-+-+-+-+-+-+I 8 I root I localhost I performance_schema I Query I 0 I executing I select*from information_Schema.processlist II 5 I event.scheduler I localhost I NULL I Daemon I 587 I Waiting on empty queue I NULL I+-+-+-+-+-+-+-+-+2 rows in set(0.00 sec)数/据/驱/动 成/就/未/来百和富里9 ENMOTECH ID:连接标识。等于performance_schema.threads表的PROCESSLISTJD字段，与CONNECTIOND（）函数返 回值相同；如果需要kill一个查询需要用到它；USER:执行该操作的用户；HOST:发出该语句的客户端主机名或者IP:PORT;Db:该线程连接的数据库；Command:该连接当前执行的命令或状态；包括sleep（休眠），query（查询）等等https:dev.mysql.eom/doc/refman/8.0/en/threadcommnnds.html Time:线程处于当前状态的时间，单位是秒；State:当前线程正在执行的动作、事件和状态；一个SQL可能包含多个状态，大多数状态都是非常快速的，如果持续数秒则需要重点关注；这是分析问题最重要的一项指标；https:dev.mysql.eom/doc/refman/8.0/en/general-threadTtates.html Info:线程正在执行的语句或Null;instance_name 11:52:47show processlist;+-+-+-+-+-+-+-+-+I Id I User I Host I db I Command I Time I State I Info I+-+-+-+-+-+-+-+-+I 5 I event_scheduler I localhost I NULL I Daemon I 4638 I Waiting on empty queue I NULL II 8 I root I localhost I test I Query I 0 I starting I show processlist II 10 I root I localhost I test I Sleep I 60 I I NULL II 11 I root I localhost I test I Query I 3 I Waiting for table metadata lock I alter table t2 add c3 varchar(255)I+-+-+-+-+-+-+-+-+4 rows in set(0.00 sec)百和富里9 ENMOTECH Slow query log记录执行时间超过long_query_time的SQL,并且至少需要检查min_examined_row_limit行O slow_query_log参数决定慢查询日志是否打开；long_query_time支持微秒级精度；log_slow_admin_statements参数开启后，执行较慢的管理语句将会被记录在慢查询日志中，包括ALTER TABLE,ANALYZE TABLE,CHECK TABLE,CREATE INDEX,DROP INDEX,OPTIMIZE TABLE,and REPAIR TABLE;l?g_queries_not_using/ndexes参数开启后，所有（表数据大于2行）不使用索引的查询会被记录。如果这 类查询较多，可以使用参数log_throttle_queries_not_using_indexes限制；log_throttle_queries_not_usingndexes每分钟有多少不使用索引的查询可被记录在慢查询日志中；mysql show variables like%slow%；Variable_name1 Valuelog_slow_admin_statements+1 OFFlog_slow_extra1 OFFlog_slow_slave_statements1 OFFslowaunch_time1 2slow_query_log1 ONslow-query_log_file1/data/mysql/data/nodel-slow.log6 rows in set(0.02 sec)数/据/驱/动 成/就/未/来mysql show variables like llong_query_time,;+-+-+I Variable_name I Value I+-+-+I long_query_time I 1.000000 I+-+-+1 row in set(0.01 sec)百和富里w EN MOTE CHI og s I。w_extra二。仟#Time:2021-01-21T15:56:01.996117+08:00件 UserHost:rootroot localhost Id:8力 Query.time:0.000242 Lock_time:0.000072 Rows.sent:206 Rows_examined:206SET timestamp=1611215761;select*from test.tl;log slow extra=on(8,0.14+)#Time:2021-01-21T15:55:14.037382+08:00#UserHost:rootroot localhost Id:8#Query_time:0.001477 Lock_time:0.000080 Rows_sent:206 Rows_examined:206 Thread_id:8 Errno:0 Killed:0 Bytes_rec eived:0 Bytes_sent:7792 Read_first:1 Read.last:0 Read_key:1 Read_next:0 Read_prev:0 Read_rnd:0 Read_rnd_next:20 7 Sort.merge-passes:0 Sort-range_count:0 Sort_rows:0 Sort_scan_count:0 Created_tmp_disk_tables:0 Created_tmp_tables:0 Start:2021-01-21T15:55:14.035905+08:00 End:2021-01-21T15:55:14.037382+08:00 use test;SET timestamp=1611215714;select*from test.tl;数/据/驱/动 成/就/未/来百和富里9 ENMOTECH Time:log记录的时间；UserHost:SQL执行的用户以及主机；id:连接的标识；Query_time:SQL执行的时间；Lock_time:获取锁的时间；Rows_sent:返回客户端的结果行数；Rows_examined:server层检查的行数；Thread_id:连接的标识；Errno:SQL错误号，0表示没有错误；Killed:语句终止的错误号，0表示正常终止；Bytes_received/sent:收到和发送的字节数；Read_last:读取索引最后一个key的次数；Readkey:基于key读取行的请求数，较大说明使用正确的索引 Sort_range_count:使用范围完成的排序次数；Sort_rows:排序的行数；Sort_scan_count:通过扫描表完成的排序次数；Start/End:语句开始和结束时间 Read_next:按顺序取下一行数据的次数，索引范围查找和索引扫描时该值会增大；Read_prev:按顺序读取上一行的请求数，order by desc查询较优时该值较大；Read_first:读取索引中第一条数据的记录，该值反映全索引扫描的次数；Read_rnd:按固定位置读取行的请求数，大量的回表、没有索引的连接和对结果集排序时会增加；Read_rnd_next:读取数据文件下一行的次数，大量表扫描、未创建或合理使用索引时会增加；Sort_merge_passes:排序算法合并的次数，如该值较大考虑增加sort_buffer_size的值 Created_tmp_disk_tabIes:创建内部磁盘临时表的数量；Created_tmp_tabIes:创建内部临时表的数量；数/据/驱/动 成/就/未/来百和富里9 ENMOTECH使用工具分析慢查询日志 mysqldumpslow,mysql自 带慢查询 日 志分析工具;常用参数：倒序排列-n只显示前n个记录 a不对数字和字符串进行抽象 g字符串过滤-s al平均锁等待时间排序 at平均查询时间排序 ar平均返回行数排序 c出现总次数排序 I等待锁的时间排序 r返回总行数排序 t累计查询耗时排序rootnodel bin#./mysqldumpslow-h)ption h requires an argumentERROR:bad optionJsage:mysqldumpslow OPTS.LOGS.5arse and summarize the MySQL slow query log.Options are-verbose-debug-helpverbosedebugwrite this text to standard output-vverbose-ddebug-s ORDERwhat to sort by(al,at,ar,c,I,r,t),*afis defaultal:average lock timear:average rows sentat:average query timec:countI:lock timer:rows sentt:query time-rreverse the sort order(largest last instead of first)-t NUMjust show the top n queries-adont abstract all numbers to N and strings to,s,-n NUMabstract numbers with at least n digits withinnames-g PATTERNgrep:only consider stmts that include this string-h HOSTNAMEhostname of db server for*-slow.log filename(can be wildcard),default is*,i.e.match all-i NAMEname of server instance(if using mysql.serverstartup script)-1dont subtract lock time from total time数/据/驱/动 成/就/未/来百和富里9 ENMOTECH使用mysqldumpslow分别对慢查询日志按执行次数、定时间、返回行数和执行时间分析排序后输出rootnodel bin#mysqldumpslow-s c-t 2/data/mysql/data/nodel-slow.logReading mysql slow query log from/data/mysql/data/nodel-slow.logCount:7 Time=0.00s(0s)Lock=0.00s(0s)Rows=0.4(3),rootrootZhosts#Count:7 Time=0.04s(0s)Lock=0.00s(0s)Rows=0.0(0),rootrootZhosts insert into tl(name)values(sleep(N.N)rootenodel bin#rootgnodel bin#mysqldumpslow-s I-t 2/data/mysql/data/nodel-slow.logReading mysql slow query log from/data/mysql/data/nodel-slow.logCount:3 Time=0.00s(0s)Lock=0.00s(0s)Rows=5.0(15),rootCroot2hosts show databasesCount:3 Time=0.00s(0s)Lock=0.00s(0s)Rows=2.0(6),rootrootZhosts show tablesrootnodel bin#rootnodel bin#mysqldumpslow-s r-t 2/data/mysql/data/nodel-slow.logReading mysql slow query log from/data/mysql/data/nodel-slow.logCount:4 Time=0.00s(0s)Lock=0.00s(0s)Rows=206.0(824),rootrootlocalhost select*from test.tlCount:1 Time=206.03s(206s)Lock=0.00s(0s)Rows=206.0(206),rootrootlocalhost select,sleep(N)from test.tlrootnodel bin#rootnodel bin#mysqldumpslow-s t-t 2/data/mysql/data/nodel-slow.logReading mysql slow query log from/data/mysql/data/nodel-slow.logCount:1 Time=206.03s(206s)Lock=0.00s(0s)Rows=206.0(206),rootrootlocalhost select,sleepN)from test.tlCount:2 Time=86.83s(173s)Lock=0.00s(0s)Rows=1.0(2),rootrootlocalhost select,sleep(N)from test.tl数/据/驱/动 成/就/未/来百和富里9 ENMOTECH pt-query-digest Perconatoolkit中的工具可以从 普通日志、慢查询日志、二进制日志以及show processlist和 tcpdump中对SQL进行分析；默认type分析慢查询日志:pt-query-digest/data/mysq 1/data/node I-slow.log 输出分三大部分：整体概要：对当前实例各项 指标进行统计和初步的分析；包括总的查询次数、SQL 数量、QPS以及并发；对执 行时间、锁、检查返回行数 等指标进行统计；雪圮 pt-query-digest/data/mysql/data/nodel-slow.!160ms user time,10ms system time,26.75M rss,221.27M vsz,Current date:Mon Jan 25 11:04:27 2021 Hostname:nodel:Files:/data/mysql/data/nodel-slow.logJ Overall:66 total,26 unique,000 QPS,0.00 x concurrency!Time range:2021-01-04T15:58:47 to 2021-01-22T11:12:57 Attributetotalminmaxavg95%stddevmedian!Exec time602s15us206s9s19s33s3ms!Lock time23ms03ms342us2ms595us108us Rows sent1.13k020617.48202.4053.580.995 Rows examine2.39k058337.06202.40107.560.99!Bytes sent23.55k118.04k3.93k7.68k3.67k7.31k Query size2.05k66931.7659.7713.6928.75!Bytes receiv0000000!Created tmp0000000，Created tmp1010.170.990.370 Errno0000000?Read first3010.500.990.500.99!Read key9071.506.982.490.99!Read last0000000，Read next0000000，Read prev0000000!Read rnd0000000!Read rnd nex1.75k01.14k298.171.09k381.70202.40J Sort merge p0000000，Sort range c0000000 Sort rows0000000 Sort scan co0000000数/据/驱/动 成/就/未/来百和富里9 ENMOTECH Profile:对重要或较慢查询进一步分析 Rank:排名 Response time”语句”的响应时间以及整体占比情况。Calls该“语句”的执行次数。R/Call每次执行的平均响应时间。V/M响应时间的方差均值比（VMR）（值越大这类SQL响应时间越趋于不同）#Profile#Rank Query ID Response time Calls R/Call V/#10X2A60E950668C2A9E6E3FE8E7DE07DC85466.674177.5%858.334391.30SELECT test.t?#20XC54312D080D0904F754521EF548E3A4391.604515.2%330.534855.75SELECT t?#30X59A74D08D407B5EDF9A57DD5A41825CA30.00395.0%310.00130.00SELECT#MISC0XMISC14.05112.3%520.27020.0数/据/驱/动 成/就/未/来百和富里w EN MOTE CH详细信息：Profile中各SQL的详细信息Exec Time降序输出,默认按总的数/据/驱/动 成/就/未/来#*#Query 1:0.00 QPS,0.00X concurrency,ID 0 xZA60E950668c2A9E6E3FE8E7DE07DC85 at byte 16890This item is included in the report because it matches-limit.Scores:V/M=91.30Time range:2021-01-06T09:56:31 to 2021-01-21T15:50:31Attribute pct total min max avg 95%stddev medianCount Exec Locktimetime1277238 467s 5ms6s 118us206s2ms58s675us202s 2ms73s670us32s570usRows sent Rows examine Bytes sent Query size Bytes receiv Created tmp Created tmp Errno188341800002132208.04k394 00 00128.04k310 000206 2068.04k 600 00 026.6227.508.04k49.250 000202.40202.408.04k59.7700066.6166.29 01360 0 0 0 00.99 1.968.04k59.77 0 00 0Read first 33Read key 11Read last 0Read next 0Read prev 0Read rnd 0Read rnd nex 11Sort Sort Sort Sortmerge p range c rowsscan co0 000String:Databasestest11 00 002071 10 00 020711 00002071 1000020700 00 00020700000 00 00 00 000000 00 000000 00 0End 20Z1-01-Z1T15:50:31.859840钝8:00Hosts LocalhostStart 2021-01-21T15:47:05.831307+08:00Users rootQuery_time lusdistribution10US100USIms10ms100msIS ff1 rf frWfrrftFtFffTrtftfff ftTfffffftTffrffffrttf frvrtr rfff If ff Ififlfffltiflt*1 du i H Y/h“r h“”“卅弁”分开 Xvwi ffiT TTTT it TT TT fT TT tv if Trfv TT TT Yr ttTT rvfr rf rr n rrr rf rTTvrr Tr ff TT Tr fr Tv rf rf rr n n rf TT fv TrfT iv IT ntr TT rr irnlvyj rr fitt n if Trfr Tr百和富里9 ENMOTECH pt-query-digest:提供较丰富的参数 type:指定输入文件的类型，默认是slowlog slowlog输入是MySQL慢查询日志 binlog输入是mysqlbinlog转换后的二进制日志 genlog 输入是MySQL general log tcpdump分析tcpdump抓包内容，建议以x-n-q格式化抓包输出 rawlog输入不是MySQL日志，而是换行分隔的SQL语句；Group-by:指定分类的属性，默认是fingerprint order-by:才旨定再、序方式，默认是Query_time:sumo 聚合方式包括sum、min、max、ent数/据/驱/动 成/就/未/来.EN MOTECH Sys schema:MySQL5.7开始支持，由表、视图、存储过程和函数等一系列对象组成，本身不存储数据,而是将performance_schema和information_schema中的数据已更容易理解的形式组织和呈现，可用于 调优和诊断。sys schema的对象包括：将性能模式数据汇总为更易于理解的形式的视图。执行诸如Performance Schema配置和生成诊断报告之类的操作的存储过程。查询Performance Schema配置并提供格式化服务的存储函数。由于sys schema提供的是performance schema的另一种访问方式，要使用sys schema需要启用performance schema;performance_schema=ON 必须启用某些performance schema的instruments和consumers后才育自充分禾U用sys的功育日;CALL sys.ps_setup_enable_instrumentfwait,);CALL sys.ps_setup_enable_instrumentfstage,);CALL sys.ps_setup_enable_instrumentfstatement,);CALL sys.ps_setup_enable_consumerfcurrent,);CALL sys.ps_setup_enable_consumer(y.wox t.by.latency latest.file-io memory_by_ho5Vby.currertbytes fnemory_ T_threod_by_current_bytes mefnory.byuser.bycurrent.bytes ECTory_gi obal_by_current_bytes memory_global.total metrics processlist pscheck.lost.i nstrumentotion schemo_outo_increment-columns schema.indexstatisties schenra.object.overview scheina.redundant.indexes schetna.tabl e-lock_wo i ts schema-table-statistics schema.tablestatisti cs_wxth_buffer schema.tables.with.full.table.scons schewa.unused.indexes sessionsession.ssLstot us statement.anolysis statements.with.errors.or.warnings statements_with_full_table_scans stat einents_with_runtimes_in_95th-percentile stat ements.wi tlvsort i ng statements_，ith_temp_tcrtles sysconfiguser-summaryusersurnmaryby.file.io user5unmary_by_file_io_typB user.summary.by.stages user.sunmary.tstatenent.latency user_suninary.by-statement_type versionwait-classes.glotxjl.by.avg_latency数/据/驱/动 成/就/未/来百和富里9 ENMOTECH schema_table_statistics视图查看table的增删查改以及I。等情况 schema_tables_with_full_table_scans视图查看全表扫描情况，可作为优化的重点 schema_auto_increment_columns视图查看自增主键的情况 schema_index_statistics视图查看索引的增删改查情况 schema_redundant_indexesnschema_unused_indexes视图分别查看冗余索引和未被使用索引 的情况 statements_with_full_table_scans 视图查看全表扫描的 SQL 信息 statement_analysis 视 图查看 SQL 汇总统计信息、(数据来源 events_statements_summary_by_digest)statements_with_errors_or_warnings视图查看出现 error 和 warning 的SQL statements_with_sortingDstatements_with_temp_tables 视图查看使用序和临时表的SQL statements_with_runtimes_in_95th_percentile 视图查看runtime在95%的SQL,视图数据默认按 avg I ate n cy倒序排列数/据/驱/动 成/就/未/来百和富里9 ENMOTECH 我们通过视图statement_analysis查看总执行时间最长的SQL:select*from sys.statement_analysis order by total_latency desc limit lGquery:抽象后的SQLDb:语句默认数据库full_scan:全表扫描的话*,否则”total/max/avg/lock_latency:总时间、最大时间、平均时间、总锁等待时间rowsrows;_sent/rows_sent_avg:总返回行数、平均返回行数;_examined/rows_examined_avg:总检查行数、平均检查行数hysql select*from sys.statement_onalysis order by total_latency desc limit 1G 卜*i.row*tmp_tables:创建临时表数量 exec_count:执行次数 err_count/warn_count:错误或警告次数 tmp_disk_tables:创建磁盘临时表数量 rows_sorted:排序次数 sort_merge_passes:合并排序次数query db full_scan exec_count errecount warn_count total_latency max.latency avg_latency lock_latency rows_sent rows_sent_avg rows.examined rows_examined_avg rows_affected rows_affected_avg tmptables tmp_disk_tables rows.sortedsort_merge_passes digest first.seen last_seenSELECT*FROM sbtestl WHERE c BETWEEN?AND?sysbench.l10100000965.65 ms965.65 ms965.65 ms1.38 ms001000001000000000004eab01aeebflfl44a2d8a5c2146092af03a6b7c5ef0e8be3cl4ed002772fea8f2021-02-18 11:27:29,9483252021-02-18 11:27:29.948325row in set(0.00 sec)数/据/驱/动 成/就/未/来百和恩里EN MOTECH查看innodb_buffer_page相关视图会导致innodb buffer pool的扫描，非常影响性能，不要在生产环境访 问；应在测欣环琉董现问题，并在测试环境查看；开启performance schema采集指标信息会对性能造成一定影响，请谨慎选择；（特别是打开所有instruments和consumers 后)：Is：2s 1：3s：4s：5s 1：6s 1：7s：8s：9s thds:thds:thds:thds:thds:thds:thds:thds:thds:26701271.56295.85281.88308.10285.11310.02303.84310.25qps:2504.68 qps:2473.1Z qps:2622.63 qps:2568.91 qps:2743.91 qps:2589.96 qps:2770.19 qps:2748.59 qps:2789.25(r/w/o:564.90/1385.84/553.94)lotCr/w/o:548.13/1382.86/542,12)lot(r/w/o：586.69/1445.25/590.69)lot(r/w/o:(r/w/o:(r/w/o:Cr/w/o:(r/w/o:(r/w/o:570.76/1433.39/564.76)lot 615.20/1512.50/616.20)lot570.2V1449.54/570.21)lot 614.04/1537.10/619.04)lot 612.68/1526.2V609.69)lot 622.50/1546.25/620.50)lot(ms.95%):(ms,95%):(ms,95%):Cms,95%):Cms,95%):(ms,95%):(ms,95%):Cms.95%):(ms,95%):118.92 err/s:116.80 err/s:114.72 err/s:108.68 err/s:108.68 err/s:106.75 err/st H2.67 err/s:112.67 err/s:102.97 err/s:0.000.000.000.000.000.000.000.000.00reconn/s:reconn/s:reconn/s:reconn/s:reconn/s:reconn/s:reconn/s:reconn/s:reconn/s:0.00 0.00 0.00 0.00 0.000.前 0.00 0.00 0.0011 1 Jthds:thds:thds:thds:thds:thds:thds:thds:thds:202020202020202020tPS：tps：tps:tps：tps:tps：tp