分布式MySQL运维关键点总结.docx

《分布式MySQL运维关键点总结.docx》由会员分享，可在线阅读，更多相关《分布式MySQL运维关键点总结.docx（10页珍藏版）》请在第一文库网上搜索。

1、分布式MySQ1运维关键点总结一分布式MySQ1本身问题/要注意问题1Bin1ogpurge成磁盘IO压力过大（因bin1og数量大大）进而引起业务Bin1og的PUrge策略为时间到点并且有新bin1og发生切搅（ratate）时清理bin1op。如果一段时间内产生大量bin1og（连格.跑枇处），然后业务整续很一，-7-.：-.1.1-一二一-二1二.一二二心-：据？1 .巳实现的COPytab1e方案,公表（f1ushtab1eexport.会强制脏页），拷贝ibd文件到目标表，import到tab1espaceo2 .新方案可使源表继续变更。1）.强制混页完目的在于保证静态表持续拷贝过

2、程中，bufferpoo1会有充足的内存页空间来谡存写入更新之类操作,就不会触发剧脏而造成ibd数据文件受更（如果COPytab1e执行完之前bufferPoo1再次触发砌脏，则CCPytab1e失败）；2）因为redo1og写满了，耍f1ush脏页二这种情况本身就是Inn。DB要与量避免的。因为出现这种情玩的时候,整个系所就不包再接殳更新了,二有的更新都必须堵住。所以redo1og的CheCkPOin1是不能控制的，redo1og还是要允许持续写入。3）.拷贝ibd文件到目标表完成后.刷脏更正常进行就To4）目标表importtab1espace3K）?connecting或IO线程，SQ1

3、线程状态YES且是数据没同步主库线程du叩出问奥4Truncatetab1e/copytab1e（孽理类II.-J/DD1时对烫潭账户跑批时经常因为CQPytab1e操作引起磁筠I。100%进而影响在线交易业务5TruncatetabIe的缓存锁对全局有影6-一标代码中合入），尤其存在大量客户端（或PrCXy/用户）情况下MySq1开源的一拨接一线程方式太翳了。早已普及的IO复用却没有采用二.MySQ1中各种日志详细介绍2.1redo1og物理日志，innodb层产生，记录数据页的物理修改，而不是某一行或几行的修改怎样，它用来恢复提交后的物理数据页(CheCkPOint开始恢复)。1.事务重做

4、，数据库故障恢复使用。当事务(TranSaetion)需要修改某条记录(row)时，InnoDB需要将该数据所在的Page从disk读到bufferpoo1中，事务提交后，InnODB修改Page中的记录(row)o这时bufferpoo1中的Page就已经和disk中的不一样了，我们称bufferpoo1中的page为dirtypageoDirtypage等待f1ush到disk上。系统突然断电DirtyPage中的数据尚未f1ush到磁盘中，bufferpoo1数据全部消失。redoIog在每次事务COmmit的时候，就立刻将事务更改操作记录到redo1ogoInnoDB在启动时，仍然会根

5、据redo1og中的记录完成数据恢复。2.通过延迟dirtypage的f1ush最小化磁盘的randomwriteso(redo1og会合并一段时间内TRX对某个page的修改。1SN是日志空间中每条日志的结束点，用字节偏移量来表示。在CheCkPc）int和恢复时使用）。二进制日志先于redo1og被记录。RedoIog是并发写入的，不同事务之间的不同版本的记录会穿插吸入到redoIog文件中，可能为TIT,T1-2,T2-1,T1TRedo1ogfi1e大小对innodb性能影响非常大，设置太大，恢复时就会时间较长，设置太小，会导致写redoIOg时循环切换redo1ogfi1eo2.2u

6、ndo1og：用来回滚行记录到某个版本，undoIog一般为逻辑日志，根据每行记录进行记录。UndO1og有2个作用：提供回滚和多个行版本控制（MVCC）O当de1ete一条记录时，undoIog中会记录一条对应的insert记录。反之亦然，当UPdate一条记录时，它记录一条对应相反的UPdate记录。进行ro11back时，可以从UndOIOg中的逻辑记录读取到相对应的内容并进行回滚。应用到多版本控制时，也是通过UndoIog来实现的：当读取的某一行被其他事务锁定时，它可以从UndoIog中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。UndoIOg采用

7、SegnIent的方式来记录。每个UndO操作在记录时占用一个Undo1ogsegmento另外UndoIOg也会产生redo1og,因为UndO1og也要实现持久化保护。事务提交时，innodb不会立即删除Undo1og,而是将de1ete对象打个de1etef1ag。因为后续还可能会用到。如隔离级别为repeatab1eread时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即UndoIOg不能删除。但是在事务提交时，会将该事务对应的UndOIOg放入待删除列表，未来通过PUrge删除。对于InnODB来说，无论是更新，删除，都只是设置行记录上的de1e

8、tedversion标记位，而不是真正的删除记录，后续这些记录的清理，是通过PUrge后台进程实现的。二进制日志是Diysq1上层的日志，先于存储引擎的事务日志被写入。GaP1oCkS不适用于唯一索引（uniqueindex）,在唯一索引记录上，只会使用index-recordIockCrashRecovery（nobin1og）由于未提交的事务和已回滚的事务也会记录到redoIog中，因此在进行恢复的时候，这些事务要进行特殊的处理innodb的处理策略是：进行恢复时，从CheCkPOint开始，重做所有事务（包括未提交的事务和已回滚的事务），然后通过UndOIog回滚那些未提交的事务XACr

9、ashRecovery1、扫描最后一个bin1og,提取Xid（标识bin1og中的第几个event）2、xid也会写到redo中，将redo中PrePare状态的xid,去跟最后一个bin1og中的Xid比较，如果bin1og中存在，则提交（即前滚），否则回滚为什么只扫描最后一个bin1og?因为bin1ogrotate的时候会把前面的bin1og都刷盘，而且事务是不会跨bin1og的MySQ1为了兼容其它非事物引擎的复制，在server层面引入了bin1og,它可以记录所有引擎中的修改操作，因而可以对所有的引擎使用复制功能。MySqI在4.X后引入了bin1og来取代redo1og的复制策

10、略。但引入了新的bi1og和redo1og的一致性问题（一个事务的提交必须写redo1og和bin1og,mysq1就是通过XA来解决这一问题的。然后又通过bin1og的组提交来解决了Write/syncbin1og带来的性能问题）。2.3行锁的实现方式InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQ1与OraCIe不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，IrmoDB才使用行级锁，否则，IrmoDB将使用表锁！在实际应用中，要特别注意I1moDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并

11、发性能。由于MySQ1的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，I1mODB都会使用行锁来对数据加锁。如果不同的索引碰巧都落到了同一个行上，那么同样会阻塞。即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQ1通过判断不同执行计划的代价来决定的，如果MySQ1认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnODB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQ1

12、的执行计划，以确认是否真正使用了索引。2.4B+树存放数据计算B+树作为INNODB的存储结构能存放多少数据？怎么计算的？先计算单个叶子节点的大小为16k,假如一条记录为IK大小，那么记录数为16条。然后计算非叶子节点能存放多少指针，假如ID为bigint类型，长度为8字节，那么指针大小在innoDB中为6个字节，加起来为14字节。那么通过页大小，即16384/(8+6)=1170个指针，所以一颗高度为2的B+树可以存放117016=18720条记录。高度为3的B+树可以存放11701170X16=21902400条记录，所以主键设置不大，记录也不大很大的情况下，一般高度为3的B+树，就能满足

13、千万级的数据存储。具体计算可为16K(主键大小+6(innodb的指针)X二次方X(16K/每条记录大小(innodb的B+和myISAM的B不一样的，它的聚簇索引的数据和主键索引是一起存储的)2.5为什么说表的数据量很大了以后会变慢很多？索引原理，为什么使用索引后会变得很快？索引做了些什么可以让我们查询加快速度呢？其实就是将无序的数据变成有序（相对）#b+树的查找过程如图所示，如果要查找数据项29,那么首先会把褴盘块1由磁盘加载到内存，此时发生一次10,在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的10）可以忽略不计，通过磁盘块1的P2指针

14、的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次10,29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生笫三次10,同时内存中做二分查找找到29,结束查询，总计三次10。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只常要三次10,性能提高将是巨大的，如果没有索引，每个数据项都要发生一次10,那么总共需要百万次的10,显然成本非常非常高。聚簇索引和非聚簇索引分析了MySQ1的索引结构的实现原理，然后我们来看看具体的存储引擎怎么实现索引结构的,MySQ1中最常见的两种存储引擎分别是My1SAM和I1moDB,分别实现了非聚簇索引和聚簇索引。聚簇索

15、引的解释是：聚簇索引的顺序就是数据的物理存储顺序非聚簇索引的解释是：索引顺序与数据物理排列顺序无关（这样说起来并不好理解，让人摸不着头脑，清继续看下文，在插图下方对上述两句话有解释）首先要介绍几个概念，在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引“，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。MyISAM非聚簇索引My1SAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。非聚簇索引的数据表

16、和索引表是分开存储的。非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。只有在MyISAM中才能使用FU11TEXT索引。（mysq15.6以后irmoDB也支持全文索引）最开始我一直不懂既然非聚簇索引的主索引和辅助索引指向相同的内容，为什么还要辅助索引这个东西呢，后来才明白索引不就是用来查询的吗，用在那些地方呢，不就是WHERE和ORDERBY语句后面吗，那么如果查询的条件不是主键怎么办呢，这个时候就需要辅助索引了。InnoDB聚簇索引聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。聚簇索引的数据和主键索引存储在一起。聚簇索引的数据是根据主键的