MySQL 运维中的疑难问题解读.docx

【摘要】更多的企业将核心业务运行在MySQ1.之上，与此同时，大数据量大流量带来的性能问题也愈演愈烈，数据库的操作往往成为整个系统的瓶颈，如何使MySQ1.跑得更快已是一项迫在眉睫的任务。本文是针对相关问题，多位DBA进行的分享，主要分为几个方面：1 .性能问题排查；2 .优化方法；3 .高可用问题；4 .安全防范；5 .迁移问题；6.其他问题1、性能问题排查Q：MySQ1.如何排查CPU占用高的问题？问题描述：重点是关于通过哪些系统表或者常用的sql来确定导致问题的sql?感觉这方面的资料很少，不像OralCe的那些v$视图，网上资料很多，sql语句也很多。答：可以通过将系统线程号与SQ1.对应来查看top-H-p<mysqld进程id>PIDUSERPRNlVlRTRESSHRS%CPU%MEMTIME+COMMAND23974mysql2001658m358m12mR99.91.10:05.52mysqld12295mysql2001658m358m12mS0.31.10:02.44mysqld.SE1.ECTa.THREAD_OS_ID,b.user,b.host,b.db,mand,b.time,b.state,b.infoFROMperfbrmance_schema.threadsa,infbrmation_schema.processlistbWHEREb.id=a.processlist_id;THREAD-OsjDusERHoSTdbcommandTIMEstateinfo23974*root10.10.18.201:21466SySQUERY29SendingDATASE1.ECTa.*FROMtesta,testb,teste,testdORDERBYa.value1.IMITO,1000.Q：MySQ1.数据库内存使用率高，应该如何进行排查？问题描述：内存使用率，通过系统命令能定位到mysql占用的内存高，如何通过系统表或者相关的sql语句，定位到占用内存高的那部分sql?MysqlServerMemoryUsage=SumofGlobalBuffers+（numberofConnection*Perthreadmemoryvariables）a）单个mysql连接线程的内存消耗统计，这里只是统计分配值（具体驻留内存占用值统计不到）selectb.thd_id,b.user,current_count_used,current_allocated,current_avg_alloc,current_max_alloc,total_allocated,current_statementfrommemory_by_thread_by_current_bytesa,sessionbwherea.thread_id=b.thd_idlimit1;b）统计top10的bufferpool占用内存的表select*frominnodb_buffer_stats_by_tableorderbypagesdesclimitlO;Q：MySQ1.数据扇磁盘IO横用高，请问如何进行排查？问题描述：通过系统能确定是数据库的IO读写高，有哪些系统表或者sql联合起来可以把关键的sql定位出来？答：mysql5.7版本为例，结合PerfOrmance_schema来查看MySQ1.数据库的各种指标。相当于Oracle数据库中的各种性能视图，可以查看几乎所有的数据库状态。IO的话，可以查看这张表：performance_schema.file_instances：列出了文件I/O操作及其相关文件的工具实例排查思路：1、慢SQ1.排除2、硬件问题-RAlD降级，磁盘故障等排除3、innodbog、innodb_buffer_pOO1.Wait相关配置和等待4、IO相卖参数配置innodb_flush_method=O_DIRECTinnodb_file_per_table=1innodb_doublewrite=1delay_key_writeinnodb_read_io_threadsinnodb_read_io_threadsinnodb_io_capacityinnodb_flush_neighborssync_binlog主要关注：sync_binlog建议：最好部署相关的监控平台或者对比历史性能记录，结合业务以及负载来分析。2、优化方法Q：MySQ1.优化的常用方法有哪些？答：一、最常见是慢查询优化1、打开慢查询记录，设置记录SQ1.的最短时间2、使用Pt工具，分类统计慢查询语句3、针对执行次数多或者时间长的语句进行优化（索引优化、SQ1.改写、业务逻辑优化）ps:也可以在系统表中，查看全表扫描多的表等二、配置文件优化1、内存使用量2、各种方面写盘策略Q：MySQ1.中执行计划如何解读？问题描述：1：执行计划如何解读？db2中按照从下往上，从左到右的顺序来解读2：执行计划中需要关注的特殊标识有哪些？例如：UsingwhereusingfilesortUsingtemporary等等答：1、执行顺序，看ID列id值相同执行顺序从上到下。id值不同时id值大的先执行。2、关注的特殊标识SE1.EeT_TYPE-执行查询类型，不同类型对应的Type：访问类型，很重要possible_keys：索引使用关于explain输出参数，可参考官方文档：以MySQ1.5.7为例Q：MySQ1.中关于表维护的操作（提升性能相关的）有哪些？问题描述：MySQ1.中关于表维护的操作（提升性能相关的）有哪些？例如db2中的表重组，db2rbind绑定包等操作答：MySQ1.的表维护语句：ANA1.YZETAB1.E：更新表统计信息。执行该语句的时候innodb及myisam表会加上读锁,停止数据更新。该语句支持innodb,myisam及ndb表,针对myisam表，该语句等同myisamchkanalyzeOPTlMlZETAB1.E:整理数据,表碎片CHECKTAB1.E:用来检查数据库表和索引是否损坏REPAIRTAB1.E：CheCktabIe语句可以检查一个表中的的问题，若表或索引损坏，可以使用repairtable语句尝试修正它Q：有哪些工具可以帮助优化MySQ1.的？答1：SQ1.优化主要还是看经验和对慢查询梳理。配置文件优化，一般来说就几个参数需要优化，其他可以不动答2：以下工具可以参考：pt-mysql-summarypt-variable-advisorpt-duplicate-key-checkerpt-deadlock-logger或者tuning-primer.sh3、高可用问题Q：MySQ1.原厂有OraCle的ClUSter集群，有哪些主流的开源适合高并发集群呢？一、MySQ1.高可用方案MySQ1.以及各种开源数据库，也有自身的集群方案，但是大多需要和业务以及借助第三方工具来实现。或者通过分布式来均衡高并发。主要的高可用集群架构可以分为如下几种：1、基于共享存储的高可用方案-SAN基于共享存储的高可用，及使用传统的基于SAN共享存储，结合开源的KeePliVe做主从同步，可避免除存储外的组件损坏引起的宕机，部署相对简单，对应用透明，但是存储时单点，且存在性能瓶颈。2、基于磁盘复制的高可用方案-DRBD保证主备的数据一致性，不依赖共享存储，此方案处理failover的方式上依旧需要借助主机层面的高可用组件，如keeplive,Heaabeat等。不同的是，在数据共享方面，采用了基于块级别的数据同步软件DRBD来实现，但是可扩展性较差。它并不共享存储，而是通过服务器之间的网络复制数据。适用于数据库访问量不太大，短期内访问量增长不会太快，对数据库可用性要求非常高的场景。3、基于MySQ1.自身的主从复制-RePIiCation基于MySQ1.自身的主从复制，5.7以后的GTID,以及之前的replicationo主从复制，部署简单，但是只能有一个MaSter进行读写，其余都为备库，还需要结合业务。并发量不大的情况下，可采取主从，管理简单。4、MHA图可用方案MHA是一套MySQ1.高可用管理软件，除了检测MaSter宕机后，提升候选Slave为NewMaster之外（漂虚拟IP）,还会自动让其他Slave与NewMaster建立复制关系。MHAManager可以单独部署在一台独立的机器上，并管理多个master-slave集群。但是，只支持一主多从架构，集群中必须最少有三台数据库服务器，要保持切换对应用透明，依然依赖于VIP,不适用于大规模集群部署，配置比较复杂。且MHA管理节点本身的HA无法保证。MySQ1.5.7之前数据不丢的前提是Master服务器还可以被MHAManager进行SSH连接，通过应用保存的binlog的方式来保证。MySQ1.5.7之后通过无损复制，仅仅是减少了丢数据的可能性，假如此时的状态为切成异步的状态，那就和之前一样了（可以设置超时的时间很大）；当MaSter恢复的时候，最后一部分数据是否需要FIaShback,MHA也是不负责这个事情，需要人工介入。5、基于zookeeper/consul的高可用方案借助zookeeper组件，结合MHA或者其他高可用架构场景，实现强制一致性的高可用集群分布，可适应大规模高并发场景，需要一定的技术实力，引入zookeeper,架构复杂度上升，但是整体扩展性非常好，可以管理大规模集群。保证了整个系统的高可用，主从的强一致依赖于MySQ1.本身，比如半同步，或者外围工具的回补策略6、基于MMM高可用方案MMM提供了MySQ1.主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件。在MMM高可用方案中，典型的应用是双主多从架构，通过MySQ1.replication技术可以实现两个服务器互为主从，且在任何时候只有一个节点可以被写入，避免了多点写入的数据冲突。同时，当可写的主节点故障时，MMM套件可以立刻监控到，然后将服务自动切换到另一个主节点，继续提供服务，从而实现MySQ1.的高可用。可以灵活选择VlP方案或者全局目录数据库方案（更改MasterIP映射）来进行切换。MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip,同时它还可以备份数据，实现两节点之间的数据同步等。由于MMM无法完全的保证数据一致性，所以MMM适用于对数据的一致性要求不是很高,但是又想最大程度的保证业务可用性的场景。对于那些对数据的一致性要求很高的业务，非常不建议采用MMM这种高可用架构。7、基于中间件proxy高可用组件的集群方案中间件：阿里Cobar、MyCAT360Atlas淘宝Tddl网易CutusMySQ1.ProxyProxySQ1.（Percona）KingShardMaxScale（MariaDB）OneProxy切换对应用透明,可扩展性强，方便分片扩展,可以跨机房部署切换,但是需要有一定自研能力，或者选择有完整的后期技术支持的中间件，以及社区活跃度较高的，有一定能力，后期可自研或者自己优化开发相关的中间件。以适应自身的业务需求。二、集群/分布式基于集群或者分布式的HA包括：MysqlGroupReplicationMysqlInnoDBClusterPerconaXtraDBClusterMariaDBGaleraCluster1、MGR关于MGR原理，可参考可以参考阿里的数据库内核月报，关于mgr的文档。基于传统异步复制和半同步复制的缺陷数据的一致性问题无法保证，MySQ1.官方在5.7.17版本正式推出组复制（MysQ1.GroupReplication,简称MGR）o由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N/2+1）决议并通过，才能得以提交。如上图所示，由3个节点组成一个复制组，ConSenSUS层为一致性协议层，在事务提交过程中，发生组间通讯，由2个节点决议（Certify）通过这个事务，事务才能够最终得以提交并响应。引入组复制，主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。组复制依靠分布式一致性协议（PaXOS协议的变体），实现了分布式下数据的最终一致性，提供了真正的数据高可用方案。2、MysQ1.lnnoDBClusterCluster解决方案其实是由MySQ1.的几个不同产品和技术组成的，比如MySQ1.Shell,MySQ1.Router,GroupReplication.一组MySQ1.服务器可以配置为一个MySQ1.集群。在默认的单主节点模式下，集群服务器具有一个读写主节点和多个只读辅节点。辅助服务器是主服务器的副本。客户端应用程序通过MySQ1.Router连接到主服务程序。如果主服务连接失败，则次要的节点自动提升为主节点，MySQ1.Router请求到新的主节点。InnoDBClUSter不提供NDBClUSter支持3、PerconaXtraDBCluster官网地址：节点在接收Sql请求后，对于ddl操作，在commit之前，由WSREPAPI调用galera库进行集群内广播，所有其他节点验证成功后事务在集群所有节点进行提交，反之rollback。PXC保证整个集群所有数据的强一致性，满足CAP理论中满足：Consistency和AvailabilityoPXC提供的特性同步复制，事务要么在所有节点提交或不提交多主复制，可以在任意节点进行写操作在从服务器上并行应用事件，真正意义上的并行复制节点自动配置数藉一致性，不再是异步复制限制：只支持INNoDB表不允许大事务的产生（否则的话后果很严重）写性能取决于最差的节点不能解决热点更新问题乐观锁控制对于写密集型应用需要控制单个节点的大小，单个节点数据越大，新加节点如果采用自动添加可能产生很大抖动（添加节点建议用备份或者备份+binlog进行IST（InCrementalStateTranSfer）增量同步Q：目前银行主流的MySQ1.高可用采用哪种方式，MHA还是MGR?问题描述：目前银行主流的MySQ1.高可用采用哪种方式？MHA还是MGR,各自的优缺点是什么？各自有哪些坑需要注意？答：银行主流何种MySQ1.高可用方案不太了解。可能是主从+中间件+自研套件的模式。由于MGR技术相对较新，目前使用MHA更多。但个人认为，MGR或者基于此的innodbcluster架构（或替代方案）会成为未来主流。MHA：优点：成熟稳定，自动切换主从，主节点宕机后尽可能少丢失数据（自动抓取未复制的binlog）。缺点：管理节点单点、可能脑裂、可能有不必要切换、还是有丢数据风险、组件多维护相对麻烦MGR：优点：基于PaXOS的高可用架构，支持多主（不建议），强一致缺点：需要innodb引擎（丢业务有改造代价），应用端没有自动切换（可以通过中间件解决），技术太新可能有未知bug其他的话还有PXC但是因为性能问题不太建议。4、安全防范Q：如何做到数据库账号权限的精细化管理？答：一定是做到对权限的全方位掌控。根据账户的不同类型，以前缀区分。简单的分类，分为业务账户和实名账户。细分来讲，业务账号分为网站应用、手机应用、报表应用、服务应用、查询服务,实名账户可以跟踪到具体的员工。网站应用（Web业务简称）手机应用（mob业务简称）报表应用（rep_业务简称）服务应用（dae_业务简称）查询服务（sea_业务简称）实名查询（devj名拼音）业务账号权限最大到SE1.EeT、UPDATE、DE1.ETEWINSERT,查询服务和实名查询账户只能有查询权限。每个用户只有一个密码，授权时需要知悉此用户是否存在，如果存在，使用旧密码授权，如果不存在，生成随机密码进行授权。实名权限只能通过堡垒机或者跳板机进行查询，堡垒机有用户登录和执行SQ1.日志。线上IDe数据库只允许线上Web机连接，不允许测试机连接。员工申请权限需要工单申请,授权只能DBA操作。DBA需要做好权限控制，相关业务负责人可以申请较高权限，但需要邮件抄送上一级领导进行审批。DBA有一套完整的元数据库，里面记录了所有的用户相关信息，此数据库重要级别最高，做好安全控制。用户的密码需要足够复杂，而且有一套完整的随机密码生成规则。业务方通知业务账户存在异常，需要制定快速更改账户的流程。员工申请的临时高权账号，需要有备案，需要设置密码过期时间，而且需要制定回收流程。MySQ1.root密码只有DBA拥有,而且不允许将此密码保存在任何云笔记或者云存储上，只能保存到本地。另外，定期修改MySQ1.rOOt密码。通过终端进入MySQ1.,不允许将密码明文显示。用户授权操作建议在Web页面完成，需要做好安全控制。此项也就是DB运维管理平台，需要编码实现。做好数据备份，可以在误操作最快恢复数据。如有可能，在新业务上线MySQ1.审计方案，可以通过init-connect参数+access-log+binlog实现审计。关宇精细化，主要是各个权限分配细致，做到，不重复，其次是权限的定义明确，该给什么权限给什么权限，不存在模糊权限，最后是权限的记录，做到从权限开始，审批，授权，收回，删除等一整套的规章流程，最重要的是一个精细化的思想，做到心中有数。Q：如何做到数据库账号权限的精细化管理？答：开审计，监控软件商业的现在有很完善的，开源的也有免费的插件，没有最成熟，只有最适合。还想说一下，数据库的安全不止要从数据库方面考虑,还要考虑网络和系统,网络和系统如果在入侵的过程中防不住了，数据库层次的防御力也有限，在前边两个层次就要做到万无一失才对，数据库的安全只是针对数据，针对一些SqI注入等等进行一些安全配置。还要做好备份，主从，异地备，高可用等，其实这些都可以算在数据库的安全里边，特别是MySQ1.,作为一个DBA,也许我们做不到万无一失，但我们要用一万种方法来防止出问题，能考虑到的，能做到的，我们都要用上。需要确定你当前所想要达到的目标，其次是对业务的影响。目前常规是通过数据库防火墙的策略规则，进行告警处理，主要的还是事后审计的报表分析。5、迁移问题Q：使用MySQ1.替换Oracle20TB左右的库，如何设计才能确保性能和高可用？答1：建议分表分库，建立好相应的索引，使用多节点主从+heartbeatkeepalivedMHAMMM等等一类的方案来保证高可用。答2：首先需要明确ORAC1.E20TB!=#MYSQ1.20T其次MySQ1.替换Oracle首先需要探讨可行性还有就是如果使用MySQ1.对于这么大的量需要考虑的一点是冷热数据，这不是单出的分库分表就能解决的，需要根据事情去探讨。答3：首先要知道这是一个O1.AP还是一个OETP。是前者的话，数据再大一倍也无妨，毕竟数据的抽取方式要变化，甚至手工作业也能满足；如果是后者，并且业务很复杂，那做的工作可要多了，先说数据的迁移，数据库中的各种对象（比如函数和过程的改写），再说大一点的查询，分库分表要考虑进来（分区不建议考虑，MySQ1.的分区表处处受限）。答4：MySQ1.高可用架构可以参考MHAPXCMGR,根据自己的实际需要进行选择。数据安全性方面考虑增强半同步，数据库版本建议8.0以上。MySQ1.单实例承担20TB的数据量不是不可以，只不过负担太重了，你需要考虑的有以下几个方面：1、单实例TPS/QPS限制2、备份、恢复的影响3、磁盘容量的规划4、锁争用、单实例连接数等等如果全部迁移到MySQ1.,建议：1、先垂直拆分，由多个集群承担对应的业务2、水平拆分,确定分片键,需要多少分片承担压力（单表建议不要超过500w,单实例不要超过500张，单实例数据总容量不要超过IT）3、由多个从库承担读压力（需要考虑延迟，交易类型一律走主库）有了高可用，有了拆分，那么还需要中间访问层，目前比较好的开源proxy有：1、简单读写分离的：ProxySQ1.>DB1.E.CetlIS等2、具备分库分表的：DB1.E、Cetus、VitneSS等以上只是简单的一点看法Q：Db2迁移到MySQ1.,有什么好的工具与方法？答：Db2迁移到MySQ1.的工作难点、问题还真不在工具上！最大问题在Db2应用的迁移上，估计Db2应用重构工作量、难度，是你们没法想象的！也正因如此，想把OraCle这种迁移到国产平台，难度很大！首先，Db2中的数据即使迁移到MySQ1.中。但是，Db2中复杂的SQ1.、Xquery.SQ1.P1.存储过程等如何重写，业务程序中依赖Db2的部分如何重构，都是问题！Q：寻求TB级SQ1.server和MySQ1.数据高效迁移工具或方案？问题描述:数据量为TB级,分表分库,大概120个库左右,后台开发用的met,所以前期数据处理什么的都是在Sqlserver上进行，之后再迁移到mysql上，所以要经常性的在两种数据库间迁移数据，前面用kettle这类传统的ET1.工具来做数据抽取，但是速度慢，品牌多，配置起来也麻烦，想问下各位大佬有没有什么高效的数据迁移工具或者方案没有？答1：针对特定的规则逻辑还是自己开发工具吧，直接文本的导出导入会更快，对于你们经常性的迁移，没有什么太适合的工具。kettle速度慢，品牌多，配置起来也麻烦应该是你的使用姿势不对。答2：楼主明确一下每天的变化的数据有多大，如果只是I-IOG这样一个量级，数据抽取工具随便哪一个都能应付过来，不建议超过2个，太多了维护成本过高。6、其他问题Q：MySQ1.适用的场景是什么？问题描述：MySQ1.有很多特性，不容易掌控。请问下MySQ1.适用的场景是什么？数据数量级在什么区间内？运维团队需要什么规模？是否需要有开发专门配合？灾备使用什么模式和工具？答：不依赖存储过程、函数、触发器的传统OltP场景都适用数据量建议单实例T级或以内运维团队任何规模都可以，因为是一个相对轻量级的数据库需要开发专门配合，毕竟语法和特性每个库都不一样灾备使用MySQ1.各类的高可用方案即可，比如主从、mhamgr等Q：MySQ1.巡检应该怎么做？优先关注哪些参数？答提供一点思路：彳人认为就检温该获取的是相对静态的数据，而动态数据应该由监控系统去完成，那么巡检我认为应该可以从以下几个方面去做：1、服务器配置2、操作系统配置及重要参数3、MySQ1.层配置及重要参数4、MySQ1.对象（如表、索引、用户、权限等）5、MySQ1.运行时的重要状态（如历史锁等待等等）答2：优先查看日志中的报错信息。然后查看监控系统中的历史数据（各类峰值，骤变值），报警信息。然后查看慢查询日志。深入可以看各类锁记录、等待事件等（需要开启统计，并且没有重启过）。Q：MySQ1.索引的创建时间怎么看？答1：没有相关命令查看索引创建时间，最多在binlog等日志中查看可以在mysql.innodb_index_stats查看最后更新时间答2：SHOWINDEXFROMtable_nameFROMdb_name,这样子查看下，是不是有相关信息Q：MySQ1.数据库如何设计能保留历史数据信息？场景：mysql数据库作为系统数据存储，使用分库分表方式进行数据的分流。按照数据湖方式建设将数据归集到tidb（newsql数据库）。采集方式使用类主从同步近实时采集，省去etl（T+1）。存在的问题：由于mysql数据库作为系统数据存储，考虑到数据量问题对数据的修改、删除在原数据基础上进行，导致没有历史数据可用。数据分析对历史数据依赖性很强，有没有好的解决方案？答：一个方法就是区分冷热数据，常规而言业务数据提供实时查询的不会超过6个月，当然如果你的数据量太大，也可以考虑更短，数据的修改分两种，因为定义了冷热数据，热数据在mysql修改，冷数据在tidb修改，然后查询的问题统一从tidb这边提供接口的方式处理。关于数据分析对历史数据的依赖，可以把历史数据当初OdS数据，然后生成各种的dm数据，乃至于dw数据。几个常见而严重的MySQ1.问题分析I运维进阶踩坑不可怕，可怕的是重复踩坑，避免它是一位程序员的基本修养。很多时候发生数据库报错时，不一定就是数据库的问题，不一定非得急着呼叫数据库人员解决。我们要形成这样一种意识，我们不只是写应用的，我们是写金融系统的，我们理应具备一定的问题排查解决能力。本文将抽取几个常见而严重的MySQ1.问题进行分析，并给出深度解答。藉以大家帮助思考。1问题回顾和思考1.1SQ1.Exception:1.ockwaittimeoutexceeded;tryrestartingtransaction,pleaserollback!再发生这样的错误时，别很自豪的说数据库出问题了，呼叫DBA.（Uat多次出现）第一个问题目前发生的原因有：1 .磁盘空间满，事务无法提交成功。（磁盘满是一个很危险的操作，会引起binlog写坏，备库无法同步进而需要恢复备库）2 .更新事务未正常提交而产生排他锁，造成其他更新事务一直获取不到该锁而事务超时。1.2 条件查询卡住了，怎么重跑都通不过，怎么办，急死人了（迁移后比对实际出现）。Truncatetable过程中CTR1.+C终止了。有分片上存在truncate事务一直存在，进而对该表的所有操作均会超时。1.3 查询卡住，更新卡住殊不知，你前面的AItertable都没成功DBProXy的问题不在此文讨论，查询事务没有正常提交而占据共享锁时，同样会造成altertable获取不到MD1.锁，而造成一直等待。提示为：Waitingfortablemetadatalock（showprocesslist中可查）。2原理详细分析2.1 什么是MD1.锁？为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQ1.5.5版本开始引入了MD1.锁（metadatalock）,来保护表的元数据信息，用于解决或者保证DD1.操作与DM1.操作之间的一致性。对于引入MD1.,其主要解决了2个问题，一个是事务隔离问题，比如在可重复读隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter,再重做update时就会出现复制错误的现象。所以在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在Metadatalockwaito例如下面的这种情形：国话1回话2BEGIN;Selectfromact_A;Droptableact-A;Selectfromact.A;若没有MD1.锁的保护，则事务2可以直接执行DD1.操作，并且导致事务1出错，5.1版本即是如此。5.5版本加入MD1.锁就在于保护这种情况的发生，由于事务1开启了查询，那么获得了MD1.锁，锁的模式为SHARED_READ,事务2要执行DD1.,贝I需获得EXC1.USlVE锁，两者互斥，所以事务2需要等待。注：支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata1.ockWait等待现象。一旦出现Metadata1.ockWait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。MySQ1.的设计:在设置的autocommit=0;read_commited的时候,无论session的第一条语句是SeIeCt还是dml,都开始一个事务，然后直到COmmi3所持有的MD1.锁也一直维持到commit结束。Oracle的设计：在SeSSiOn的第一条更新语句发起时，才创建transaction,在读多的系统上，减少了阻塞的发生可能性。特别是在开发人员发起SeIeCt语句时，认为没有更新,就不再COmmit。但在MySQ1.上,发起SeleCt语句，而忘记commit,是非常危险的。2.2 常见MD1.锁场景和详细解释1)当前有执行DM1.操作时执行A1.TRE操作2)当前有对表的长时间查询或使用mysqldumpmysqlpump时;使用alter会被堵住3)显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，使用alter会被堵住4)表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住详细测试解释说明：1)当前有执行DM1.操作时执行A1.TRE操作#SESSlONAmysql>insertintoyetest2select*fromyetestl;#SESSIONBmysql>alteFtableyetestZaddyeColumnint;等待SESSIONA执行完;# SESSIC)NCmySqIAshowprocesslist;IdUserHostdbCommandTimeStateInfo267rootlocalhostsbtestQuery7Sendingdatainsertintoyetest2select*fromyetest127lrootlocalhostsbtestQuery3Waitingfbrtablemetadatalockaltertableyetest2addyeColumnint272rootlocalhostNU1.1.Query0startingshowprocesslist3rowsinset(0.00sec)#SESSlc)NDmySql>select*fromyetesimit10;/等待元数据锁;#SESSloNEmySqI>showprocesslist;IdUserHostdbCommandTimeStateInfo267rootlocalhostsbtestQuery20Sendingdatainsertintosbtest2select*fromsbtest1271IrootllocalhostIsbtestIQueryI13Waitingfbrtablemetadatalockaltertableyetest2addyeColumnint272rtlocalhostNU1.1.Query0startingshowprocesslist308rootlocalhostsbtestQuery3Waitingfbrtablemetadatalockselect*fromyetest21imitlO4rowsinset(0.00sec)由于事务1开启了查询,那么获得了MD1.锁,锁的模式为SHARED_READ,事务2要执行DD1.,则需获得EXC1.USlVE锁，两者互斥，所以事务2需要等待。查询都能卡住，是不是很郁闷？我们上次迁移就是这种场景，truncatetable属于DD1.,会Iocktablemetadata,甚至可以可以由锁表升级到锁库。3)显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，使用alter会被堵住#SESSIONAmysql>begin;mysql>select*fromtest2;#SESSIONBmysql>altertabetest2addtest3int花SESSIONA执行完；# SESSIONCmysql>showprocesslist;IdUserHostdbCommandTimeStatelnfo267rootlocalhostsbtestSleep36NU1.1.271rootlocalhostsbtestQuery30Waitingfbrtablemetadatalockaltertabletest2addtest3int272rootlocalhostNU1.1.Query0startingshowprocesslist3rowsinset(0.00sec)4)表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住#SESSIONAmysql>begin;mysql>selecterrorfromtest2;ERROR1054(42S22):Unknowncolumn'error,in,fieldlistWSESSIONBmysql>altertabletest2addtest3int;/等待SESSloNA提交或回滚；# SESSlC)NCmySqIAshowprocesslist;IdUserHostdbCommandTimeStateInfo267rootlocaltestSleep7NU1.1.271rootlocaltestQuery3Waitingfortablemetadatalockaltertabletest2addtest3int272rootlocalNU1.1.Query0startingshowprocesslist3llrootlocalNU1.1.Sleep413NU1.1.4rowsinset(O.OOsec)#SESSIONDmysql>select*frominformation_schema.innodb_trx;Emptyset(0.00sec)其实SESS10NA中的事务并未开启，但是由于select获取表元数据的语句，语法上是有效的，虽然执行失败了，但是任然不会释放元数据锁，故而导致SESSIONB的alter动作被阻塞。通过SESSIOND查看当前打开事务时，你会发现没有，从而找不到原因。所以当出现这种场景时，如何判断是哪个进程导致的呢，我们可以尝试查看表performance_schema.events_statements_current,分析进程状态来进行判断。mysql>select*fromperformance_schema.events_statements_currentG1.rowTHREADJD:293EVENT_ID:32END_EVENTJD:32EVENT_NAME:statement/sql/selectSOURCE:socket_connection.cc:211TIMER_START:2127217170