《MySQL 5.6新特性深入剖析——InnoDB引擎.pptx》由会员分享，可在线阅读，更多相关《MySQL 5.6新特性深入剖析——InnoDB引擎.pptx（36页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、MySQL 5.6新特性深入剖析新特性深入剖析 InnoDB引擎引擎网易杭研：何登成微博：何_登成网站：深入MySQL内核OutlineMySQL 5.6简介简介MySQL 5.6新特性新特性InnoDB层新特性层新特性性能优化功能增强Server层新特性层新特性性能优化功能增强MySQL5.6简介简介MySQL5.6版本，为MySQL最新的一个大版本，相对于MySQL5.1/5.5，无论是MySQLServer层面，还是InnoDBEngine层面，都做了大量的改进(性能改进 vs功能增强)。这些改进，无论是DBA，亦或是研发人员，都值得好好的学习、深入了解；版本发布情况MySQL5.6.2
2、(2011-04-11)：第一个发布版本MySQL5.6.7(2012-09-29)：第一个RC版本MySQL5.6.10(2013-02-05)：第一个GA版本(本本PPT使用版本使用版本)MySQL5.6.12(NotReleased)：最新研发版本详见：MySQL5.6ReleaseNotesMySQL5.6简介改进总览InnoDB Engine(本期内容本期内容)性能性能Read-OnlyTransaction,BufferPoolFlushing,PageCleaner,Purge,CRC32,SSD.功能功能OnlineDDL,MemcachedPlugin,Transportab
3、leTablespace,BufferPoolDump/Restore,FTS.MySQL Server(下期内容下期内容)OptimizerSemi-Join,BKA,MRR,ICP,Join,In,OptimizerTracing,Limit.ReplicationGTID,BinlogGroupCommit,Multi-ThreadedSlaves.OthersSecurity.InnoDB性能优化(总)InnoDB性能优化性能优化Read-OnlyTransactionsBufferPoolFlushingPageCleanerPurgeCRC32CompressionDataDict
4、ionaryLRUOthersssd,mutex,spinlock,memoryallocation,readahead,undologtablespace.InnoDB-ReadOnlyTransactionRead-Only Transactions(双层优化)第一第一层层瓶颈瓶颈快照读操作，需要创建ReadView：获取trx_sys-mutex后遍历活跃事务链表，存在并发性能瓶颈；InnoDB的MVCC策略(参考此处)；分析分析只读事务不会产生更新，也就不会产生历史版本；OLTP应用，读多写少；将活跃事务链表拆分为只读事务与更新事务两个链表，ReadView创建只需要遍历更新事务链表，
5、能够极大的降低ReadView创建的开销；优化优化新增SQL语法：starttransactionreadonly;事务上新增标识：trx-read_only维护两个活跃事务链表：ro_trx_listvsrw_trx_list注注：Autocommit=1时，快照读一定是Read-Only事务InnoDB-ReadOnlyTransactionRead-Only Transactions(双层优化)第二层第二层瓶颈将事务划分为只读/更新事务之后，InnoDB系统的并发效率并未有明显提升；分析在row0sel.cc:row_search_for_mysql()函数中，每读取一条记录，都会增加一
6、个count计数；多线程并发修改此count(srv_n_rows_read)，就会导致cachecoherence问题；优化重新设计计数器：N个计数对象，按照CACHE_LINE_SIZE对齐；每个事务，根据事务ID映射到不同的计数对象上，进行统计，减少碰撞；Typem_counter(N+1)*(CACHE_LINE_SIZE/sizeof(Type);InnoDB-BufferPoolFlushingBuffer Pool Flushing(Flush List Flush)InnoDB的FuzzyCheckpoint策略，按照内部脏页链表(FlushList)，逐步将最老的一部分脏页写
7、出磁盘，推进系统的检查点(CheckpoingLSN)；(参考此文)存在的问题InnoDB原生Flushing算法不够稳定(Percona提出了3种改进策略)新的算法系统平均刷脏页速度：avg_page_rate=过去innodb_flushing_avg_loops秒+.的速度系统平均日志速度：lsn_avg_rate根据系统脏页比率与日志年龄，计算本次应该Flush的脏页数量：npages根据平均刷脏页速度进行调整：npages=(npages+avg_page_rate)/2根据lsn_avg_rate，计算本次日志应该Flush到的位置：lsn_limit最后，根据npages与lsn
8、_limit，进行本次Flush；新引入的参数innodb_adaptive_flushing_lwminnodb_max_dirty_pages_pct_lwminnodb_max_io_capacityinnodb_flushing_avg_loopsInnoDB-PageCleaner两种两种Flush策略策略LRUListFlush写出LRU链表尾部的脏页，释放足够的页面，以满足前端用户的需求；原由用户线程触发，用户线程处理；FlushListFlush：将系统中最老的部分脏页写出，推进系统的检查点(CheckpointLSN)；根据CheckpointAge的不同，由不同的线程处理(
9、MasterThreadvsUserThread)；InnoDB-PageCleanerPage Cleaner流程流程将LRUListFlush与FlushListFlush全部移到PageCleaner后台线程中处理，减少MasterThread与UserThread的压力；PageCleaner线程，每秒启动一次；LRUListFlush从LRU链表尾部开始遍历：将未使用的CleanPage从LRU链表摘除；将未使用的DirtyPage写出，然后从LRU链表摘除；外部参数：innodb_lru_scan_depth(控制LRU链表尾部遍历的长度)；内部参数：PAGE_CLEANER_LR
10、U_BATCH_CHUNK_SIZE(默认100：控制一个处理批次的大小，防止长时间持有bufferpoolmutex，导致系统出现并发瓶颈)；FlushListFlush使用前页中介绍的NewAdaptiveFlush算法；InnoDB-PurgeThreadPurge ThreadPurge操作：InnoDB读取提交事务的Undo记录，然后将事务更新所产生的历史版本(标记为删除，并且对所有活跃事务不可见的版本)从数据文件(聚簇索引、辅助索引)中删除的操作；MySQL5.1，Purge操作在InnoDBMasterThread中完成；MySQL5.5，一个Purge后台线程；存在的问题Pur
11、ge操作不及时，导致Undo空间膨胀；数据文件中存在大量历史无用记录；Multi-PurgeThreadsMySQL5.6，多个Purge线程，并发回收历史版本；新增参数：innodb_purge_threads 默认1，取值1,32innodb_purge_batch_size默认300注意注意1：innodb_purge_threads只是规定了Purge线程的上限，InnoDB会根据事务负载自动调节 (详见srv0srv.cc:srv_do_purge()函数)；注意注意2：innodb_purge_batch_size可以理解为每次Purge，回收的提交事务数量；InnoDB-CRC3
12、2Page ChecksumInnoDB在其页面的头部与尾部，维护了两个Checksum(详见InnoDBPageStructure)，通过页面的内容计算而来，用于校验页面内容是否被破坏；脏页从内存写出时，需要重新计算Checksum(buf0flu.cc:buf_flush_init_for_writing()；页面从外存读取进内存时，需要计算页面Checksum，判断其与存储的Checksum是否相同，进而验证页面是否损坏(buf0buf.cc:buf_page_is_corrupted()；原有原有Checksum算法算法软件计算(buf0checksum.cc:buf_calc_pag
13、e_new_checksum()，逐个读取4字节int，然后做运算；一个InnoDBPage，默认为16K，软件计算Checksum，性能低下；CRC32 Checksum通过CPU指令(SSE4.2)计算CRC32Checksum，提升Checksum的计算性能(ut0crc32.cc:ut_crc32_sse42()新增参数：innodb_checksum_algoritmInnoDB-CompressionCompressionInnoDB以Page为单位进行压缩，采用zlib压缩工具；优化措施优化措施新增参数innodb_compression_level控制zlib压缩级别1.9；i
14、nnodb_compression_failure_threshold_pctInnoDB的压缩页面经过更新之后，再次压缩可能会导致压缩失败，需要分裂；此参数控制压缩失败的比率，超过此比率，压缩前的页面需要进行Padding；所谓Padding，就是在16K的页内填充一些无效内容，降低页面利用率，保证压缩成功率；innodb_compression_pad_pct_max非压缩页Padding的大小；默认：50%InnoDB-DataDictionaryLRUData Dictionary每一个用户表，在InnoDB的系统表(SYS_TABLES,SYS_COLUMNS,SYS_INDEXES
15、,.)中都存储着一些元数据信息；当前端SQL操作用户表时，此表的元数据信息会被读取出来，存放于InnoDBDataDictionaryCache之中；原有问题原有问题DataDictionaryCache，会占用大量的内存表打开时，会将元数据加载入DictionaryCache，但是表关闭时，并不会从DictionaryCache中删除元数据。大量表的情况下，DictionaryCache会占用大量内存(详见此文)；改进措施改进措施将DictionaryCache中的所有表元数据，维护为一个LRU链表；借用MySQLServer层的参数：table_definition_cache(默认400
16、，软限制)后台MasterThread，每SRV_MASTER_DICT_LRU_INTERVAL(47)秒，遍历一次DictionaryCache，清除LRU链表尾部不使用(refcount=0)的表(保证数量小于table_definition_cache即可)；InnoDB-OthersSSDCompression优化；4K、8KPage支持；Undologtablespace；.MutexCAS原子指令Spinlock根据innodb_sync_spin_loops参数，InnoDBSpinlock在无法获取锁时，会反复重试innodb_sync_spin_loops次；改进重试前，根
17、据innodb_spin_wait_delay参数，RelaxCPU的使用，通过PAUSE指令实现；关于PAUSE指令，可参考此文；memory allocation除了BufferPool之外，InnoDB需要使用一些其他的内存，原由内部实现内存的分配与释放；改进直接使用操作系统提供的tcmalloc，ptmalloc等更为高效的内存分配方法；InnoDB-OthersFile Extension5.5中，数据文件扩展时，需要锁住全局的filesystemmutex，数据文件扩展串行化，并且会堵塞前端用户操作；改进每个扩展中的文件，新增一个标识，无需长时间持有filesystemmutex，
18、问题解决；read aheadlinear read ahead每次从外存成功读取一个page之后，都判断是否需要进行linearreadahead(buf0rea.cc:buf_read_ahead_linear()；linear判断方法：遍历page所属extent，判断后一个page的访问时间是否大于前一个page；控制参数：innodb_read_ahead_threshold(默认56)random read ahead若page所属extent中，超过BUF_READ_AHEAD_RANDOM_THRESHOLD(13)数量的页面均在LRU链表的热端，并且参数innodb_rand
19、om_read_ahead(默认关闭)开启，则预读extent中的所有其他Pages；InnoDB功能增强(总)InnoDB功能增强功能增强OnlineDDLMemcachedPluginTransportableTablespaceBufferPoolDump/RestorePersistentStatisticsFullTextSearch(略)InnoDB-OnlineDDLDDL发展历程发展历程CopyTableMySQL最早的DDL操作方式，DDL通过CopyTable方式实现：新建TempTable；锁原表，原表可读不可写；将原表数据Copy到Temp表；删除原表，重命名Temp表
20、，解锁；缺点缺点：并发低；两倍存储空间；Inplace直接在原表上进行DDL(Add/DropIndex.)，锁表进行；缺点缺点：并发低；OnlineDDL操作过程中不长时间锁表，并发操作可读可写，提供高并发；两种方式InplaceOnlineDDL：Add/DropIndex,.CopyTableOnlineDDL：Add/DropColumn,.InnoDB-OnlineDDLInnoDB-OnlineDDL注意事项注意事项是否支持是否支持Online Add Unique Index？支持；在创建过程，以及RowLog回放过程中，都会进行Unique约束检查；Online操作，新的索引缺
21、乏版本信息，如何处理？操作，新的索引缺乏版本信息，如何处理？问题问题：读取最新记录构建新索引，因此新索引上缺乏版本信息；解决解决：索引字典上，新增一个trx_id属性，标识索引创建过程中系统的最大事务ID，所有小于此trx_id值的事务，均不可使用新索引；Online操作性能，是否可以优化？操作性能，是否可以优化？可通过增加innodb_sort_buffer_size参数，优化Online(AddIndex/Column)操作性能；创建索引，排序过程，使用内存大小为innodb_sort_buffer_size的3倍；RowLogBlock大小，等于innodb_sort_buffer_si
22、ze；InnoDB-OnlineDDLCopy Table Online DDL流程如何流程如何？Add/DropColumn，无法进行InplaceOnlineDDL，需要创建临时表(handler0alter.cc:prepare_inplace_alter_table()；原表聚簇索引原表聚簇索引，创建RowLog(handler0alter.cc:prepare_inplace_alter_table_dict()；读取原表记录，构建新表聚簇索引/辅助索引记录，排序并顺序插入；重放原表聚簇索引上的RowLog到新表之上；(同样，重放RowLog中间Block时不锁原表，重放最后一个Ro
23、wLogBlock时，锁住原表，禁止更新操作)删除原表，将临时表重命名为原表，更新部分持久化统计信息，在线加列操作完成(handler0alter.cc:commit_inplace_alter_table()；InnoDB-MemcachedPluginMemcachedPluginMySQL5.6，对外提供了通过Memcached接口直接访问InnoDB引擎中的记录的方式；InnoDB-MemcachedPluginInnoDB-MemcachedPlugin注意事项注意事项数据数据缓存策略缓存策略在innodb_memcache.cache_policies表中进行设置；innodb_o
24、nly(OnlyInnoDBBP)，cache_only(OnlyMemcached)，caching(Both)；可单独设置每个操作：set，get，delete，.事务操作策略事务操作策略daemon_memcached_r_batch_sizevsdaemon_memcached_w_batch_size;默认(1)同一连接同一连接：多少次read创建一个事务 vs多少次write提交一次事务；innodb_api_bk_commit_interval；默认(5S)MemcachedPlugin后台线程，5S定期清理Idle连接；Others参考此文；总结总结较为难用；与SQL(DDL/
25、DML)相互并发，存在一定的约束，较难理解；InnoDB-TransportableTablespacesTransportable TablespacesTransportableTablespaces的功能，就是将一个表数据文件从当前数据库拷贝出去，然后导入到另外一个数据库之中；原有约束原有约束MySQL5.6之前，无法通过拷贝数据文件的方式实现数据的转移；数据文件ibd中不包含最新纪录；数据文件的日志信息与其他数据库不符；Purge与ChangeBuffer存在影响；.改进改进通过提供新的命令，使得TransportableTablespaces成为可能；注意注意：innodb_file
26、_per_table参数必须开启；InnoDB-TransportableTablespacesInnoDB-TransportableTablespacesDiscard&Import Tablespace主要流程主要流程Discard TablespaceDiscard过程，上层并发由MDL锁保护；底层需要等待后台操作结束；删除ChangeBuffer中所有相关的缓存项；设置表元数据信息，标识Tablespace删除状态：重新生成表的ID，保证所有基于表ID的操作后续均会失败(Purge)；删除数据文件，Discard成功；Import Tablespace读取cfg文件：表定义；索引定义
27、；索引RootPage；列定义；.读取Import文件的每一个Page，检查完整性；根据读取的CFG文件，重新设置当前表的元数据信息；InnoDB-BPDump/RestoreBuffer Pool Dump/RestoreDump：将InnoDBBufferPool中的页面标识Dump到外存；Restore：读取Dump文件，根据其中保存的页面标识，读取对应的页面填充BufferPool；功能优势功能优势原有问题原有问题重启MySQL服务器，InnoDB的缓存预热，一直是系统较大的瓶颈。在预热过程中，I/O压力过大(随机I/O)，影响用户的使用；解决方案解决方案通过BPDump/Restor
28、e，Dump过程，将内存页面标识写出；Restore过程，读取Dump文件，将页面标识排序，顺序读取外存页面进入BufferPool；将预热的随机I/O转换为顺序I/O；InnoDB-BPDump/RestoreDump/Restore处理流程处理流程Dump流程流程遍历所有的BufferPoolInstances；获取当前BufferPoolInstance的Mutex(排他)，遍历LRU链表，将LRU链表中的每一个页面标识记录下来；页面标识：【space_id，page_no】释放BufferPoolMutex；将所有的页面标识写出到Dump文件，Dump完成；注意注意由于Dump需要长时
29、间持有BufferPoolMutex，因此会影响前端应用，低峰期进行；Restore流程流程读取Dump文件，获取所有页面标识；对页面标识进行排序(保证顺序保证顺序I/O)，然后顺序读取页面标识对应的数据页面；InnoDB-PersistentStatisticsPersistent Statistics统计信息持久化；原有问题原有问题InnoDB中的统计信息是不持久化的，在以下情况下会更新中的统计信息是不持久化的，在以下情况下会更新表打开时；表中的大量数据被修改时；(2000000000)or(stat_n_rows/16)AnalyzeTable；Showtable/indexStatus
30、；统计信息精准度不够统计信息精准度不够随机采集8个页面，估算全表的统计信息；Persistent Statistics优势优势更为精准的统计信息；更为固定的统计信息；InnoDB-PersistentStatistics持久化哪些统计信息？持久化哪些统计信息？(参考参考这个这个)Tablen_rows表记录数量clustered_index_size聚簇索引大小sum_of_other_index_sizes其他索引总大小Indexnumberofindexpages索引大小numberofindexleafpages索引叶页面数量n_diff索引前缀组合不同取值数量持久持久化统计信息存储在哪
31、？化统计信息存储在哪？mysql.innodb_table_stats;mysql.innodb_index_stats;持持久化统计信息如何修改？久化统计信息如何修改？AnalyzeTable;直接修改统计信息表；(可通过此操纵执行计划：见下页实例见下页实例)InnoDB-PersistentStatisticsMySQL5.6改进总结InnoDB Engine(本期内容本期内容)性能性能Read-OnlyTransaction,KernelMutexSplit,BufferPoolFlushing,PageCleaner,Purge,CRC32.功能功能OnlineDDL,Memcache
32、dPlugin,TransportableTablespace,BufferPoolDump/Restore,FTS.MySQL Server(下期内容下期内容)OptimizerSemi-Join,BKA,MRR,ICP,Join,In,OptimizerTracing,Limit.ReplicationGTID,BinlogGroupCommit,Multi-ThreadedSlaves.OthersSecurity.参考资料MySQL.MySQL5.6.10http:/ https:/ http:/ https:/ https:/ https:/ http:/ https:/ http:/