2022mysql运维操作手册

第第页Mysql运维操作手册2022目录TOC\o"1-3"\h\z\u1、简介 71.1目的 71.2适用范围 71.3引用文件 71.4特别声明 72、操作系统参数 72.1内核参数sysconf 72.1.1somaxconn 72.1.2tcp_max_syn_backlog 72.1.3netdev_max_backlog 72.1.4tcp_fin_timeout 82.1.5tcp_tw_reuse 82.1.6tcp_tw_recycle 82.1.7网络相关 82.1.8心跳检测 82.1.9内存页分配 82.1.10交换内存 92.2资源限制 92.2.1参考样例 92.2.2其它配置 93、常用参数 93.1三种方式 93.1.1全局方式 93.1.2会话方式 103.1.3配置方式 103.2connection相关 103.2.1max_connections 103.2.2max_user_connections 103.2.3Threads 103.2.4back_log 113.2.5超时设置 113.2.6查看连接 113.3查询缓存 123.3.1表缓存 123.3.2查询缓存状态 133.3.3设置缓存参数 143.4innodb相关 153.4.1innodb_buffer_pool_% 153.4.2其它参数 163.5日志优化 183.5.1binlog日志 183.5.2事务区日志 193.5.3复制优化 193.6事务隔离 203.6.1READUNCOMMITTED 213.6.2READCOMMITTED 213.6.3REPEATABLEREAD 213.6.4SERIALIZABLE 213.6.5综合建议 223.7会话参数 223.7.1sort_buffer_size 223.8其它参数 224、常用性能监控 224.1查询总视图 224.2查询连接 234.2.1列表查询 234.2.2查询长时间连接 244.3查询慢SQL 244.3.1开启慢查询 244.3.2统计排名 254.4查询分析 254.4.1开启查询 254.5查看各表统计信息 254.6分析表执行计划 264.7查询当前进程 264.7.1查询进程 264.7.2主要状态 264.8状态查询 284.9锁监控查询 294.9.1查询锁表情况 294.9.2查询当前事务锁 294.9.3查询当前锁表 294.9.4查询被锁事务 294.9.5查询到锁源 304.9.6查询锁源sql语句 314.9.7快速方法 314.10performance_schema 334.10.1开启说明 334.10.2表分类说明 334.10.3简单使用 344.10.4系统变量 344.10.5启动选项 354.10.6重要配置表 364.10.7常用监控 394.11Profiler(诊断) 434.11.1开启 434.11.2查看最近 444.11.3显示明细 444.11.4显示cpu|block 455、内存评估 465.1内存参数 465.2mysql内存公式 475.3mysql内存计算 475.4mysql磁盘参数优化 486、参考文章 48简介目的本文档为xxx信息有限公司针mysql运维操作手册。编写此操作手册的目的在于帮助现研发、测试和实施人员安装部署系统，提高工作效率。适用范围实施人员、项目经理、项目成员。引用文件无特别声明操作系统参数内核参数sysconfsomaxconn#系统允许同时发起的TCP连接数。在许多的主流操作系统上这个值都默认是128。net.core.somaxconn=65535tcp_max_syn_backlog#系统允许的半连接(SYN)同步包上限，显然tcp_max_syn_backlog>=somaxconnnet.ipv4.tcp_max_syn_backlog=65536netdev_max_backlog#每个网络端口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目dev_max_backlog=65536tcp_fin_timeout#这个参数是用来设置保持在FIN_WAIT_2状态的时间。tcp4次挥手，正常的处理流程就是在FIN_WAIT_2情况下接收到FIN进入到TIME_WAIT的情况，tcp_fin_timeout参数对处于TIME_WAIT状态的时间没有任何影响。但是如果这个参数设的比较小，会缩短从FIN_WAIT_2到TIME_WAIT的时间，从而使连接更早地进入TIME_WAIT状态。状态开始的早，等待相同的时间，结束的也早，客观上也加速了TIME_WAIT状态套接字的清理速度。net.ipv4.tcp_fin_timeout=10tcp_tw_reuse#TIME-WAIT套接字是否允许重用于新的TCP连接,了解网络建议开启=1,提高连接速度,net.ipv4.tcp_tw_reuse=1tcp_tw_recycle##表示开启TCP连接中TIME-WAITsockets的快速回收，默认为0，表示关闭。net.ipv4.tcp_tw_recycle=0网络相关#发送与接收数据的缓存值net.core.wmem_default=87380

net.core.wmem_max=16777216

net.core.rmem_default=87380

net.core.rmem_max=16777216心跳检测#长连接的心跳包机制#设置心跳包开始时机为,发送数据包后半小时开始心跳检查net.ipv4.tcp_keepalive_time=1800#心跳包发送间隔时间10秒net.ipv4.tcp_keepalive_intvl=10#超过3次没有应答,则认为连接无效,将其丢弃net.ipv4.tcp_keepalive_probes=3内存页分配#kernel.shmall=_PHYS_PAGES/2#参考物理内存页的一半,主机32G内存，参考如下kernel.shmall=4194304#kernel.shmmax=kernel.shmall*PAGE_SIZE#主机32G内存，参考如下kernel.shmmaxernel.shmmni=4096#kernel.shmmax必须大于INNODB_POOL_SIZE与QUERY_CACHE及其他MySQL占用内存总和，#建议总内存的60%左右kernel.shmmax=2147483648交换内存vm.swappiness=1vm.dirty_background_bytes=536870912资源限制参考样例vim

/etc/security/limits.conf*softnofile524288*hardnofile524288*softnprocunlimited*hardnprocunlimited*softmemlockunlimited*hardmemlockunlimited*softstackunlimited*hardstackunlimited*softcoreunlimited*hardcoreunlimited其它配置cat/etc/security/limits.d/20-nproc.conf常用参数三种方式全局方式showVARIABLESlike'%connection%'#setGlobal在Mysql服务器运行过程中会一直生效,直到mysql关闭#值得注意的是:部分参数在setglobal并不会立即生效,需要重新建立连接后才有效setGLOBALmax_connections=200;会话方式#setsession代表在当前会话(窗口/连接)才有效,关闭会话后自动失效#参数设置的优先级session>global>配置文件setxxx=xxx;session可以去掉，默认就是会话方式配置方式在f中设置connection相关max_connections#max_connections代表数据库同时允许的最大允许连接数#连接有两种常见状态:sleep/query#sleep代表连接处于闲置状态#query代表连接正处于处理任务的状态#sleep+query连接的总量不能超过max_connections的设置值#否则会出现经典错误:"ERROR1040：Toomanyconnetcions"showVARIABLESlike'max_connections';setglobalmax_connections=300;max_user_connections每个用户允许的最大连接数Threadsshowstatuslike'Threads%';#Threads_connected代表当前已经有多少连接(Sleep+Query)#Threads_created代表历史总共创建过多少个数据库连接#Threads_running代表有几个连接正处于"工作"状态,也是目前的并发数#Threads_cached共缓存过多少连接.如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。setglobalthread_cache_size=80;#MySQL历史运行过程中最大连接数的数量及时点showstatuslike'Max_used_connections%';根据Connections和Threads_created这两个系统状态值，我们还可以计算出系统新建连接连接的ThreadCache命中率，也就是通过ThreadCache池中取得连接线程的次数与系统接收的总连接次数的比率，如下：Threads_Cache_Hit=(Connections-Threads_created)/Connections*100%back_log#back_log设置保存多少数据库请求到堆栈(缓冲区)中.#也就是说，如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报：unauthenticateduser|xxx.xxx.xxx.xxx|NULL|Connect|NULL|login|NULL的待连接进程时.showVARIABLESLIKE'back_log'超时设置#wait_timeout和interactive_timeout#这两个参数都是至超过一段时间后,数据库连接自动关闭(默认28800秒,即8小时)#interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。#说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。showVARIABLESLIKE'wait_timeout';showVARIABLESLIKE'interactive_timeout';查看连接#查看当前数据库连接详细状况showprocesslist;查询缓存表缓存table_definition_cache全局参数，默认值为-1，最小是为400，描述的是.frm文件在定义换成里面存储的总量，如果你创建一个比较大的值，会加快你打开表的速度。这个表定义缓存会占据一些空间，它不同于常规的表缓存，它不会用文件描述符。它的值，最小400，之后一般依据下面的简单计算来定：400+(table_open_cache/2)table_open_cache这也是全局参数，默认是2000，最小值1最大值524288，这是mysql服务器当前所有线程打开的表的数据量，增加这个值会增加mysqld需要的文件描述符的数量。可以通过检查Opened_tables状态变量来检查是否需要增加表缓存。FLUSHTABLES命令会强迫所有的表去关闭然后重新打开。但是table_open_cache一般是可以调整增加的，但是不能超过shell文件的上限，就是-ulimit-a的那个值table_open_cache_instances全局参数，默认16，最小1，最大64，打开的表缓存实例的数量。为了通过减少会话间的争用来提高可伸缩性，可以将打开的表缓存划分为几个大小为table_open_cache/table_open_cache_instances的较小缓存实例。一个会话只需要锁定一个实例就可以访问DML语句从下面这个图我们可以看出表缓存需要设置的大小，一般命中率太低，我们建议适当增加该值查询缓存状态showstatuslike'Qcache%';#Qcache_free_memory:QueryCache中目前剩余的内存大小。通过这个参数我们可以较为准确的观察当前系统中的QueryCache内存大小是否足够，是需要增多还是过多了。#Qcache_lowmen_prunes：多少条Query因为内存不足而被清除出QueryCache，通过Qcache_lowmem_prunes和Qcache_free_memory相互结合，能够更清楚的了解到我们系统中QueryCache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换出。这个数字最好是长时间来看，如果这个数字在不断增长，就表示可能碎片化非常严重，或者内存很少。#Qcache_total_blocks：当前QueryCache中block的数量#Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示过大，则说明QueryCache中的内存碎片较多了。#查询缓存碎片率：Qcache_free_block/Qcache_total_block*100%#如果查询缓存碎片率超过20%，可以用flushquerycache整理缓存碎片#Block默认是4KB，设置值大对大数据查询有好处，但是如果你查询的都是小数据查询，就容易造成内存碎片和浪费。#Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询能缓存的效果。数字越大缓存效果越理想。#Qcache_inserts：表示多少次未命中而插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert带查询缓存中。这样的情况次数越多，表示查询缓存应用到的比较少，效果也就不理想。#Qcache_queries_in_cache:当前缓存中缓存的查询数量。#Qcache_not_cached未进入查询缓存的select个数设置缓存参数showglobalvariableslike'query_cache_%';#query_cache_size：查询缓存大小（注：QC存储的单位最小是1024byte，所以如果你设定的一个不是1024的倍数的值。这个值会被四舍五入到最接近当前值的等于1024的倍数的值。）#query_cache_limit：超出此大小的查询将不被缓存#1mb,超过1mb的结果将不缓存showVARIABLESlike'query_cache_limit';setglobalquery_cache_limit=10485760;#query_cache_type：缓存类型，决定缓存什么样子的查询，注意这个值不能随便设置必须设置为数字，可选值以及说明如下：0：OFF相当于禁用了1：ON将缓存所有结果，除非你的select语句使用了SQL_NO_CACHE禁用了查询缓存2：DENAND则只缓存select语句中通过SQL_CACHE指定需要缓存的查询。#5.7中默认禁用QC,需要在my.ini中进行开启showVARIABLESlike'query_cache_type';setglobalquery_cache_type=1;#query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄双刃剑，默认是4KB，设置值大对大数据查询有好处，但是如果你查询的都是小数据查询，就容易造成内存碎片和浪费。#每个查询占用缓存的平均值=（query_cache_size-Qcache_free_memory）/Qcache_queries_in_cache#查询缓存利用率：（query_cache_size-Qcache_free_memory)/query_cache_size*100%查询缓存利用率在25%以下的话说明query_cache_size设置过大，可以适当减小：查询缓存利用率在80%以上而且Qcache_lowmem_prunes>50的话说明query_cache_size可能有点小，要不就是碎片太多#查询缓存命中率：Qcache_hits/(Qcache_hits+Qcache_inserts)*100% innodb相关innodb_buffer_pool_%showstatuslike'Innodb_buffer_pool_%';innodb_buffer_pool_size#134217728=128M(默认值)innodb_buffer_pool_size参数用来设置Innodb最主要的Buffer(Innodb_Buffer_Pool)的大小，也就是缓存用户表及索引数据的最主要缓存空间，对Innodb整体性能影响也最大，在条件允许的情况下，我们尽可能将它设大Innodb_buffer_pool_pages_dataInnodb_buffer_pool_pages_data#Innodb已使用的缓存"页Page"数量Innodb_buffer_pool_pages_totalInnodb全部缓存页数量Innodb_buffer_pool_pages_free剩余缓存数量#页面使用率计算公式result=Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%val>95%则考虑增大innodb_buffer_pool_size，建议使用物理内存的75%val<95%则考虑减小innodb_buffer_pool_size，建议设置为：Innodb_buffer_pool_pages_data*Innodb_page_size*1.05/(1024*1024*1024)Innodb_buffer_pool_pages_miscinnodbbufferpool缓存池中当前已经被用作管理用途或hashindex而不能用作为普通数据页的数目。单位是pageInnodb_buffer_pool_pages_dirtyinnodbbufferpool缓存池中脏页的数目。单位是pageInnodb_buffer_pool_pages_flushedinnodbbufferpool缓存池中刷新页请求的数目。单位是page。Innodb_buffer_pool_read_requests发生读的总次数，也可以理解成逻辑读Innodb_buffer_pool_reads发生从磁盘读的次数InnodbBufferPool的Read命中率计算：(Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests*100%Innodb_buffer_pool_read_ahead后端预读线程读取到innodbbufferpool的页的数目。单位是page。Innodb_buffer_pool_read_ahead_evicted预读的页数，但是没有被读取就从缓冲池中被替换的页的数量，一般用来判断预读的效率。其它参数innodb_thread_concurrency#innodb_thread_concurrency#设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍。showglobalVARIABLESlike'innodb_thread_concurrency';innodb_write_io_threadsinnodb_read_io_threadsinnodb_purge_threads#innodb_purge_threads当事务成功提交后，事务所关联的undolog已经不再需要，故需要使用回收线程去回收，回收线程的数量默认为1个，建议修改为cpu核数innodb_purge_threads=8innodb_flush_log_at_trx_commit#innodb_flush_log_at_trx_commit#在事务控制中,存在"事务区"来保证事务完整性,在事务提交以后,这些事务区的数据会写入到硬盘上,同时事务操作日志(log)也需要向硬盘中写入.这个参数就是用来控制何时写日志数据的.#0：logbuffer将每秒一次地写入logfile中，并且logfile的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。#1：每次事务提交时MySQL都会把logbuffer的数据写入logfile，并且flush(刷到磁盘)中去，该模式为系统默认。#2：每次事务提交时MySQL都会把logbuffer的数据写入logfile，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次flush(刷到磁盘)操作。#三者比较#当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。#当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld服务崩溃或者服务器主机crash的情况下，binarylog只有可能丢失最多一个语句或者一个事务。。#当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。#实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要两秒，设置为0时只需要一秒，设置为1时，则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅度提高速度。innodb_lock_wait_timeout#innodb_lock_wait_timeout锁等待超时innodb_lock_wait_timeout=30sinnodb_file_per_table#innodb_file_per_table=1#设置独立表空间文件xxx.ibdinnodb_doublewrite#innodb_doublewrite双写操作#同一份数据写入两次,保证数据存在一个副本,预防数据因为介质问题产生丢失showglobalVARIABLESlike'innodb_doublewrite';日志优化binlog日志showvariableslike'%binlog%';binlog_cache_size“binlog_cache_size"：在事务过程中容纳二进制日志SQL语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin选项)的前提下为每个客户端分配的内存，注意，是每个Client都可以分配设置大小的binlogcache空间。如果读者朋友的系统中经常会出现多语句事务的话，可以尝试增加该值的大小，以获得更有的性能。当然，我们可以通过MySQL的以下两个状态变量来判断当前的binlog_cache_size的状况：Binlog_cache_use和Binlog_cache_disk_use。max_binlog_cache_size“max_binlog_cache_size”：和"binlog_cache_size"相对应，但是所代表的是binlog能够使用的最大cache内存大小。当我们执行多语句事务的时候，max_binlog_cache_size如果不够大的话，系统可能会报出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage”的错误。max_binlog_size“max_binlog_size”：Binlog日志最大值，一般来说设置为512M或者1G，但不能超过1G。该大小并不能非常严格控制Binlog大小，尤其是当到达Binlog比较靠近尾部而又遇到一个较大事务的时候，系统为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进入当前日志，直到该事务结束。这一点和Oracle的Redo日志有点不一样，因为Oracle的Redo日志所记录的是数据文件的物理位置的变化，而且里面同时记录了Redo和Undo相关的信息，所以同一个事务是否在一个日志中对Oracle来说并不关键。而MySQL在Binlog中所记录的是数据库逻辑变化信息，MySQL称之为Event，实际上就是带来数据库变化的DML之类的Query语句。sync_binlog“sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：●sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。●sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。事务区日志我们可以通过如下查询来检测mysql中的buffer写情况showstatuslike'innodb_log%';该值往往与innodb_flush_log_at_trx_commit设置有关，通常我们认为物理写越少越好复制优化MySQL中Binlog的产生量是没办法改变的，只要我们的Query改变了数据库中的数据，那么就必须将该Query所对应的Event记录到Binlog中。那我们是不是就没有办法优化复制了呢？当然不是，在MySQL复制环境中，实际上是是有8个参数可以让我们控制需要复制或者需要忽略而不进行复制的DB或者Table的，分别为：●Binlog_Do_DB：设定哪些数据库（Schema）需要记录Binlog；●Binlog_Ignore_DB：设定哪些数据库（Schema）不要记录Binlog；●Replicate_Do_DB：设定需要复制的数据库（Schema），多个DB用逗号（“,”）分隔；●Replicate_Ignore_DB：设定可以忽略的数据库（Schema）；●Replicate_Do_Table：设定需要复制的Table；●Replicate_Ignore_Table：设定可以忽略的Table；●Replicate_Wild_Do_Table：功能同Replicate_Do_Table，但可以带通配符来进行设置；●Replicate_Wild_Ignore_Table：功能同Replicate_Ignore_Table，可带通配符设置；实际上，上面这八个参数中的前面两个是设置在Master端的，而后面六个参数则是设置在Slave端的。虽然前面两个参数和后面六个参数在功能上并没有非常直接的关系，但是对于优化MySQL的Replication来说都可以启到相似的功能。当然也有一定的区别，其主要区别如下：●如果在Master端设置前面两个参数，不仅仅会让Master端的Binlog记录所带来的IO量减少，还会让Master端的IO线程就可以减少Binlog的读取量，传递给Slave端的IO线程的Binlog量自然就会较少。这样做的好处是可以减少网络IO，减少Slave端IO线程的IO量，减少Slave端的SQL线程的工作量，从而最大幅度的优化复制性能。当然，在Master端设置也存在一定的弊端，因为MySQL的判断是否需要复制某个Event不是根据产生该Event的Query所更改的数据所在的DB，而是根据执行Query时刻所在的默认Schema，也就是我们登录时候指定的DB或者运行“USEDATABASE”中所指定的DB。只有当前默认DB和配置中所设定的DB完全吻合的时候IO线程才会将该Event读取给Slave的IO线程。所以如果在系统中出现在默认DB和设定需要复制的DB不一样的情况下改变了需要复制的DB中某个Table的数据的时候，该Event是不会被复制到Slave中去的，这样就会造成Slave端的数据和Master的数据不一致的情况出现。同样，如果在默认Schema下更改了不需要复制的Schema中的数据，则会被复制到Slave端，当Slave端并没有该Schema的时候，则会造成复制出错而停止；●而如果是在Slave端设置后面的六个参数，在性能优化方面可能比在Master端要稍微逊色一点，因为不管是需要还是不需要复制的Event都被会被IO线程读取到Slave端，这样不仅仅增加了网络IO量，也给Slave端的IO线程增加了RelayLog的写入量。但是仍然可以减少Slave的SQL线程在Slave端的日志应用量。虽然性能方面稍有逊色，但是在Slave端设置复制过滤机制，可以保证不会出现因为默认Schema的问题而造成Slave和Master数据不一致或者复制出错的问题。事务隔离READUNCOMMITTED常被成为DirtyReads（脏读），可以说是事务上的最低隔离级别：在普通的非锁定模式下SELECT的执行使我们看到的数据可能并不是查询发起时间点的数据，因而在这个隔离度下是非ConsistentReads（一致性读）；READCOMMITTED这个事务隔离级别有些类似Oracle数据库默认的隔离级。属于语句级别的隔离，如通过SELECT...FORUPDATE和SELECT...LOCKINSHAREMODE来执行的请求仅仅锁定索引记录，而不锁定之前的间隙，因而允许在锁定的记录后自由地插入新记录。当然，这与Innodb的锁定实现机制有关。如果我们的Query可以很准确的通过索引定位到需要锁定的记录，则仅仅只需要锁定相关的索引记录，而不需要锁定该索引之前的间隙。但如果我们的Query通过索引检索的时候无法通过索引准确定位到需要锁定的记录，或者是一个基于范围的查询，InnoDB就必须设置next-key或gaplocks来阻塞其它用户对范围内的空隙插入。ConsistentReads的实现机制与Oracle基本类似：每一个ConsistentRead，甚至是同一个事务中的，均设置并作为它自己的最新快照。这一隔离级别下，不会出现DirtyRead，但是可能出现Non-RepeatableReads(不可重复读)和PhantomReads（幻读）。REPEATABLEREADREPEATABLEREAD隔离级别是InnoDB默认的事务隔离级。SELECT...FORUPDATE,SELECT...LOCKINSHAREMODE,UPDATE,和DELETE，这些以唯一条件搜索唯一索引的，只锁定所找到的索引记录，而不锁定该索引之前的间隙。否则这些操作将使用next-key锁定，以next-key和gaplocks锁定找到的索引范围，并阻塞其它用户的新建插入。在ConsistentReads中，与前一个隔离级相比这是一个重要的差别：在这一级中，同一事务中所有的ConsistentReads均读取第一次读取时已确定的快照。这个约定就意味着如果在同一事务中发出几个无格式(plain)的SELECTs，这些SELECT的相互关系是一致的。在REPEATABLEREAD隔离级别下，不会出现DirtyReads，也不会出现Non-RepeatableReads，但是仍然存在PhantomReads的可能性。SERIALIZABLESERIALIZABLE隔离级别是标准事务隔离级别中的最高级别。设置为SERIALIZABLE隔离级别之后，在事务中的任何时候所看到的数据都是事务启动时刻的状态，不论在这期间有没有其他事务已经修改了某些数据并提交。所以，SERIALIZABLE事务隔离级别下，PhantomReads也不会出现。综合建议以上四种事务隔离级别实际上就是ANSI/ISOSQL92标准所定义的四种隔离级别，Innodb全部都为我们实现了。对于高并发应用来说，为了尽可能保证数据的一致性，避免并发可能带来的数据不一致问题，自然是事务隔离级别越高越好。但是，对于Innodb来说，所使用的事务隔离级别越高，实现复杂度自然就会更高，所需要做的事情也会更多，整体性能也就会更差。所以，我们需要分析自己应用系统的逻辑，选择可以接受的最低事务隔离级别。以在保证数据安全一致性的同时达到最高的性能。虽然Innodb存储引擎默认的事务隔离级别是REPEATABLEREAD，但实际上在我们大部分的应用场景下，都只需要READCOMMITED的事务隔离级别就可以满足需求了。会话参数以下为64G内存时，连接参数设置参考read_buffer_size=16Mread_rnd_buffer_size=32Msort_buffer_size=32Mjoin_buffer_size=128Mtmp_table_size=64Msort_buffer_size#sort_buffer_size每个需要排序的线程分配该大小的一个缓冲区。增加这值加速ORDERBY或GROUPBY操作sort_buffer_size是一个connection级的参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。sort_buffer_size：并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统的内存资源。例如：500个连接将会消耗500*sort_buffer_size(2M)=1G 其它参数参考后面的样例提供常用性能监控查询总视图showglobalstatuslike'Com_select%';showglobalstatuslike'Com_insert%';showglobalstatuslike'Com_update%';showglobalstatuslike'Com_delete%';showglobalstatuslike'Connections%';showglobalstatuslike'Uptime%';showglobalstatuslike'Slow_queries%';通过如上参数我们可以了解到当前数据库主要是以插入更新为主还是查找为主，以及各个SQL语句执行的比例，知道这些情况后，可以对当前数据库设计做一个大概的了解，方便后续对数据进行一个优化查询连接列表查询select*from`performance_schema`.hosts也可以通过当前进程查询：select SUBSTRING_INDEX(host,':',1)ashostip, count(*)numfrom information_cesslistgroupby hostip;查询长时间连接select *from information_cesslistwhere Command!='Sleep'orderby Timedesc;查询慢SQL开启慢查询showvariableslike'slow_query%';showvariableslike'long_query_time';该命令主要查看系统慢查询的资料，什么是慢查询，慢查询就是mysql可以设置一个时间数，当一个sql语句执行时间超过这个时间段，则认为该条sql语句是慢查询语句，然后我们可以针对该sql进行优化。setglobalslow_query_log='ON';setglobalslow_query_log_file='/project/slow.log';setgloballong_query_time=1;注意：以上命令只对新连接生效统计排名mysqldumpslow-st-t100/home/teledb/data/data_8801/mysql_data8801/logs/73_8801/slow_query_2021_12_30_00_00_03.log参数说明：-s:排序方式按锁的时间l、返回的记录数r、查询的时间t、记录的次数c,倒序的话可以加r-t:查询前多少条记录-g:支持正则表达式,以及忽略大小写在这里顺便说下explain吧explain用来分析mysql查询结构的主要关注四个参数值：type、key、rows、extras访问类型type:al最差,ref,eq_ref居中,null最好all->index->range->ref->eq_ref->const或system->null有无使用索引key:key为空没有使用索引找到所需记录要读取的行数：rows,rows值越小越好查询分析开启查询showvariableslike'general_log';showvariableslike'general_log_file';showvariableslike'log_output';setglobalgeneral_log=on;setglobalgeneral_log_file='tmp/general.lg';setgloballog_output='table';setgloballog_output='file';查看各表统计信息SELECT table_name, table_rowsFROM information_schema.tablesawhere a.TABLE_SCHEMA='custdb_ah_1'orderby table_rowsdesc分析表执行计划explainselect*fromcustomer;查询当前进程查询进程showfullprocesslist主要状态这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：Checkingtable正在检查数据表（这是自动的）。Closingtables正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。ConnectOut复制从服务器正在连接主服务器。Copyingtotmptableondisk由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。Creatingtmptable正在创建临时表以存放部分查询结果。deletingfrommaintable服务器正在执行多表删除中的第一部分，刚删除第一个表。deletingfromreferencetables服务器正在执行多表删除中的第二部分，正在删除其他表的记录。Flushingtables正在执行FLUSHTABLES，等待其他线程关闭数据表。Killed发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。Locked被其他查询锁住了。Sendingdata正在处理SELECT查询的记录，同时正在把结果发送给客户端。Sortingforgroup正在为GROUPBY做排序。Sortingfororder正在为ORDERBY做排序。Openingtables这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTERTABLE或LOCKTABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。Removingduplicates正在执行一个SELECTDISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。Reopentable获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。Repairbysorting修复指令正在排序以创建索引。Repairwithkeycache修复指令正在利用索引缓存一个一个地创建新索引。它会比Repairbysorting慢些。Searchingrowsforupdate正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。Sleeping正在等待客户端发送新请求.Systemlock正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。UpgradinglockINSERTDELAYED正在尝试取得一个锁表以插入新记录。Updating正在搜索匹配的记录，并且修改它们。UserLock正在等待GET_LOCK()。Waitingfortables该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSHTABLEStbl_name,ALTERTABLE,RENAMETABLE,REPAIRTABLE,ANALYZETABLE,或OPTIMIZETABLE。waitingforhandlerinsertINSERTDELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。状态查询showstatus下面我们看几个常用的带选项的命令查询当前MySQL本次启动后的运行统计时间showstatuslike'uptime';查看本次MySQL启动后执行的select语句的次数showstatuslike'com_select';查看本次MySQL启动后执行insert语句的次数show[global]statuslike'com_insert';查看本次MySQL启动后执行update语句的次数show[global]statuslike'com_update';查看本次MySQL启动后执行delete语句的次数show[global]statuslike'com_delete';查看MySQL服务器的线程信息showstatuslike'Thread_%';查看试图连接到MySQL(不管是否连接成功)的连接数showstatuslike'connections';查看线程缓存内的线程的数量showstatuslike'threads_cached';查看立即获得的表的锁的次数showstatuslike'table_locks_immediate';查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制showstatuslike'table_locks_waited';查看查询时间超过long_query_time秒的查询的个数showstatuslike'slow_queries';锁监控查询查询锁表情况showstatuslike'Table_locks_%';Table_locks_immediate指的是能够立即获得表级锁的次数Table_locks_waited指的是不能立即获取表级锁而需要等待的次数，如果数量大，说明锁等待多，有锁争用情况查询当前事务锁查询当前锁表showOPENTABLESwhereIn_use>0;查询被锁事务SELECT*FROMinformation_schema.INNODB_TRXWHEREtrx_state='LOCKWAIT';查询到锁源select c.*from information_schema.INNODB_LOCK_WAITSa, information_schema.innodb_trxb,information_schema.`PROCESSLIST`cwhere a.blocking_trx_id=b.trx_id andb.trx_mysql_thread_id=c.id anda.requesting_trx_id=21734679查询锁源sql语句SELECT*FROMperformance_schema.`events_statements_current`WHEREthread_idin(selectm.THREAD_IDfrom`performance_schema`.threadsmwherem.PROCESSLIST_ID=24);SELECT*FROMperformance_schema.`events_statements_history`WHEREthread_idin(selectm.THREAD_IDfrom`performance_schema`.threadsmwherem.PROCESSLIST_ID=24);快速方法查看事务锁SHOWSTATUSLIKE'innodb_row_lock%';快速查询锁select r.trx_isolation_level, r.trx_idwaiting_trx_id, r.trx_mysql_thread_idwaiting_trx_thread, r.trx_statewaiting_trx_state, lr.lock_modewaiting_trx_lock_mode, lr.lock_typewaiting_trx_lock_type, lr.lock_tablewaiting_trx_lock_table, lr.lock_indexwaiting_trx_lock_index, r.trx_querywaiting_trx_query, b.trx_idblocking_trx_id, b.trx_mysql_thread_idblocking_trx_thread, b.trx_stateblocking_trx_state, lb.lock_modeblocking_trx_lock_mode, lb.lock_typeblocking_trx_lock_type, lb.lock_tableblocking_trx_lock_table, lb.lock_indexblocking_trx_lock_index, b.trx_queryblocking_queryfrom information_schema.innodb_lock_waitsw innerjoininformation_schema.innodb_trxbonb.trx_id=w.blocking_trx_id innerjoininformation_schema.innodb_trxronr.trx_id=w.requesting_trx_id innerjoininformation_schema.innodb_lockslbonlb.lock_trx_id=w.blocking_trx_id innerjoininformation_schema.innodb_lockslronlr.lock_trx_id=w.requesting_trx_idperformance_schema开启说明--查看performance_schema的属性，ON表示开启SHOWVARIABLESLIKE'performance_schema';如果要关闭性能模式，不能用命令关闭，需要修改配置文件mysql.iniperformance_schema=ON表分类说明-语句事件记录表，这些表记录了语句事件信息，当前语句事件表events_statements_current、历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分)showtableslike'%statement%';--等待事件记录表，与语句事件类型的相关记录表类似：showtableslike'%wait%';--阶段事件记录表，记录语句执行的阶段事件的表showtableslike'%stage%';--事务事件记录表，记录事务相关的事件的表showtableslike'%transaction%';--监控文件系统层调用的表showtableslike'%file%';--监视内存使用的表showtableslike'%memory%';--动态对performance_schema进行配置的配置表showtableslike'%setup%';简单使用数据信息采集和保存性能数据项目都是需要设置的，默认不会全部打开。列1：打开等待事件的采集器配置项开关，需要修改setup_instruments配置表中对应的采集器配置项UPDATEsetup_instrumentsSETENABLED='YES',TIMED='YES'wherenamelike'wait%';列2：打开等待事件的保存表配置开关，修改setup_consumers配置表中对应的配置项UPDATEsetup_consumersSETENABLED='YES'wherenamelike'%wait%';当配置完成之后可以查看当前server正在做什么，可以通过查询events_waits_current表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件系统变量showvariableslike'%performance_schema%';--重要的属性解释performance_schema=ON/*控制performance_schema功能的开关，要使用MySQL的performance_schema，需要在mysqld启动时启用，以启用事件收集功能该参数在5.7.x之前支持performance_schema的版本中默认关闭，5.7.x版本开始默认开启注意：如果mysqld在初始化performance_schema时发现无法分配任何相关的内部缓冲区，则performance_schema将自动禁用，并将performance_schema设置为OFF*/performance_schema_digests_size=10000/*控制events_statements_summary_by_digest表中的最大行数。如果产生的语句摘要信息超过此最大值，便无法继续存入该表，此时performance_schema会增加状态变量*/performance_schema_events_statements_history_long_size=10000/*控制events_statements_history_long表中的最大行数，该参数控制所有会话在events_statements_history_long表中能够存放的总事件记录数，超过这个限制之后，最早的记录将被覆盖全局变量，只读变量，整型值，5.6.3版本引入*5.6.x版本中，5.6.5及其之前的版本默认为10000，5.6.6及其之后的版本默认值为-1，通常情况下，自动计算的值都是10000*5.7.x版本中，默认值为-1，通常情况下，自动计算的值都是10000*/performance_schema_events_statements_history_size=10/*控制events_statements_history表中单个线程（会话）的最大行数，该参数控制单个会话在events_statements_history表中能够存放的事件记录数，超过这个限制之后，单个会话最早的记录将被覆盖全局变量，只读变量，整型值，5.6.3版本引入*5.6.x版本中，5.6.5及其之前的版本默认为10，5.6.6及其之后的版本默认值为-1，通常情况下，自动计算的值都是10*5.7.x版本中，默认值为-1，通常情况下，自动计算的值都是10除了statement(语句)事件之外，wait(等待)事件、state(阶段)事件、transaction(事务)事件，他们与statement事件一样都有三个参数分别进行存储限制配置，有兴趣的同学自行研究，这里不再赘述*/performance_schema_max_digest_length=1024/*用于控制标准化形式的SQL语句文本在存入performance_schema时的限制长度，该变量与max_digest_length变量相关(max_digest_length变量含义请自行查阅相关资料)全局变量，只读变量，默认值1024字节，整型值，取值范围0~1048576*/performance_schema_max_sql_text_length=1024/*控制存入events_statements_current，events_statements_history和events_statements_history_long语句事件表中的SQL_TEXT列的最大SQL长度字节数。超出系统变量performance_schema_max_sql_text_length的部分将被丢弃，不会记录，一般情况下不需要调整该参数，除非被截断的部分与其他SQL比起来有很大差异全局变量，只读变量，整型值，默认值为1024字节，取值范围为0~1048576，5.7.6版本引入降低系统变量performance_schema_max_sql_text_length值可以减少内存使用，但如果汇总的SQL中，被截断部分有较大差异，会导致没有办法再对这些有较大差异的SQL进行区分。增加该系统变量值会增加内存使用，但对于汇总SQL来讲可以更精准地区分不同的部分。*/启动选项performance_schema_consumer_events_statements_current=TRUE是否在mysqlserver启动时就开启events_statements_current表的记录功能(该表记录当前的语句事件信息)，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新setup_consumers配置表中的events_statements_current配置项，默认值为TRUEperformance_schema_consumer_events_statements_history=TRUE与performance_schema_consumer_events_statements_current选项类似，但该选项是用于配置是否记录语句事件短历史信息，默认为TRUEperformance_schema_consumer_events_stages_history_long=FALSE与performance_schema_consumer_events_statements_current选项类似，但该选项是用于配置是否记录语句事件长历史信息，默认为FALSE除了statement(语句)事件之外，还支持：wait(等待)事件、state(阶段)事件、transaction(事务)事件，他们与statement事件一样都有三个启动项分别进行配置，但这些等待事件默认未启用，如果需要在MySQLServer启动时一同启动，则通常需要写进f配置文件中performance_schema_consumer_global_instrumentation=TRUE是否在MySQLServer启动时就开启全局表（如：mutex_instances、rwlock_instances、cond_instances、file_instances、users、hostsaccounts、socket_summary_by_event_name、file_summary_by_instance等大部分的全局对象计数统计和事件汇总统计信息表）的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新全局配置项默认值为TRUEperformance_schema_consumer_statements_digest=TRUE是否在MySQLServer启动时就开启events_statements_summary_by_digest表的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新digest配置项默认值为TRUEperformance_schema_consumer_thread_instrumentation=TRUE是否在MySQLServer启动时就开启events_xxx_summary_by_yyy_by_event_name表的记录功能，启动之后也可以在setup_consumers表中使用UPDATE语句进行动态更新线程配置项默认值为TRUEperformance_schema_instrument[=name]是否在MySQLServer启动时就启用某些采集器，由于instruments配置项多达数千个，所以该配置项支持key-value模式，还支持%号进行通配等，如下:重要配置表performance_timers/*performance_timers表中记录了server中有哪些可用的事件计时器字段解释： timer_name:表示可用计时器名称，CYCLE是基于CPU周期计数器的定时器 timer_frequency:表示每秒钟对应的计时器单位的数量,CYCLE计时器的换算值与CPU的频率相关、 timer_resolution:计时器精度值，表示在每个计时器被调用时额外增加的值 timer_overhead:表示在使用定时器获取事件时开销的最小周期值*/select*fromperformance_timers;setup_timers/*setup_timers表中记录当前使用的事件计时器信息字段解释： name:计时器类型，对应某个事件类别 timer_name:计时器类型名称*/select*fromsetup_timers;setup_consumers/*setup_consumers表中列出了consumers可配置列表项字段解释： NAME：consumers配置名称 ENABLED：consumers是否启用，有效值为YES或NO，此列可以使用UPDATE语句修改。*/select*fromsetup_consumers;setup_instruments/*setup_instruments表列出了instruments列表配置项，即代表了哪些事件支持被收集：字段解释： NAME：instruments名称，instruments名称可能具有多个部分并形成层次结构 ENABLED：instrumetns是否启用，有效值为YES或NO，此列可以使用UPDATE语句修改。如果设置为NO，则这个instruments不会被执行，不会产生任何的事件信息 TIMED：instruments是否收集时间信息，有效值为YES或NO，此列可以使用UPDATE语句修改，如果设置为NO，则这个instruments不会收集时间信息*/SELECT*FROMsetup_instruments;setup_actor/*setup_actors表的初始内容是匹配任何用户和主机，因此对于所有前台线程，默认情况下启用监视和历史事件收集功能字段解释： HOST：与grant语句类似的主机名，一个具体的字符串名字，或使用“％”表示“任何主机” USER：一个具体的字符串名称，或使用“％”表示“任何用户” ROLE：当前未使用，MySQL8.0中才启用角色功能 ENABLED：是否启用与HOST，USER，ROLE匹配的前台线程的监控功能，有效值为：YES或NO HISTORY：是否启用与HOST，USER，ROLE匹配的前台线程的历史事件记录功能，有效值为：YES或NO*/SELE