老男孩mysql dba6期课件第八课innodb内核

利用主键的聚簇索引(clusteredindex)在底层 C：consistency一致性：事务开始之前和事务，数据库的完整性I：isolation独立性：多个事务并发时，事务之间是的，一个事D：durability持续性：在事务完成以后，该对数据库所作的更改便该数据已经写入到磁盘中作为永久，不会再变化，除非有新的事务 |sid|course_id|score 1| 1| 1| 1|

1| 782| 693| 804| 63 9rowsinset(0.00mysql>updatescoresetscore=90where |sid|course_id|score 1 1 1 1

1| 902| 903| 904| 90mysql>mysql>select*fromscorewhere |sid|course_id|score

#####rollbck 1| 1| 1| 1|

1| 902| 903| 904| 90 | | mysql>startQueryOK,0rowsaffected(0.00mysql>updatescoresetscore=85wheresid=1;QueryOK,4rowsaffected(0.00sec)Rowsmatched:4Changed:4Warnings:mysql>select*fromscorewhere |sid|course_id|score 1| 1| 1| 1|

1| 852| 853| 854| 85 4rowsinset(0.00mysql>QueryOK,0rowsaffected(0.00mysql>select*fromscorewhere ##事务执行最后rollback |sid|course_id|score 1| 1| 1| 1|

1| 902| 903| 904| 90 初始数据行的情况，六个字段的值分别是 1：mysql>starttransaction;2：mysql>start1：mysql>updatescoresetscore=88where2：mysql>select*fromscorewhere 相同数 |sid|course_id|score+ 1 1 1 1

1 902 903 904 902：mysql>select*fromscorewhere |sid|course_id|score+ 1 1 1 1

1 902 903 904 902：mysql>2：mysql>select*fromscorewhere |sid|course_id|score+ 1 1 1 1

1 882 883 884 88 在MySQL实例中执行showengines命令查看 mysql>showengines; + |Support| +

|Transactions| ||||

| |MyISAMstorage| |CSVstorage

| | || | ||PERFORMANCE_SCHEMA||||| |||||

|Performance | | | itetoitdisappears)|NO|NO|itetoitdisappears)|NO|NO|| | ocking,andforeignkeys|YES| | | |ortemporarytables| |NO||NULL| | |CollectionofidenticalMyISAMtables||DEFAULT|Supportstransactions,row-level| |Archivestorage| |Hashbased,storedinmemory,useful| |FederatedMySQLstorage |+ +InnoDB表数据格主键值随机insertbufferpool缓存池是InnoDB在内存中开辟的用来缓存表数据和索引数据的区域，一般 Bufferpool以page页的格式组成，页之间组成list列表，并通过LRU算法（最近最少数据的读写需要经过缓存（缓存在bufferpool即在内存中） AdaptiveHashIndex（自适应哈希索引AdaptiveHashindex属性使得InnoDB更像是内存数据库。该属性通过 的提升，则建立哈希索引，所以称之为自适应（adaptive）的。自适应哈希索引 的频率和模式来为某些页建立哈希索引。 索引（AdaptiveHashIndex，AHI）。 wherea=wherea=xxxandb= RedologRedologbuffer是一块用来存放写入redolog文件内容的内存区域，内存的大小由innodb_log_buffer_size参数确定。该buffer的内容会定期刷新到磁盘的redolog文件中。参数mit决定了刷新到文件的方式，参数innodb_flush_log_at_timeout参数决定InnoDB的系统表空间用来存放表和索引数据，同时也是doublewriter缓存，change缓和回滚日志的 空间，系统表空间是被多个表共享的表空间。 innodbbufferpool中flush之后并写入到数据文件之前，所以当操作系统或者 原该页，再进行重做，这就是doublewrite内存中的doublewritebuffer,大小 的doublewritebuffer，之后通过doublewrite再分两次，每次1M顺序地写入共享表空间的物理磁盘上，在这个过程中，的写入后，再将doublewritebuffer中的页写入各个表空间文件中，此时的写入则是离散的。如果 到表空间文件，再应用重做日志。UndoLo的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先 File-per-tableFile-per-table表空间意味着innodb的数据表不是共 个系统表空间，而是每个表一个独立的表空间。可以通过设置innodb_file_per_table开启此属性。开启之后每个表数据和索引数据都会默认单独存放在数据文件夹下的.ibd数据文件中。mysql>showvariableslike | |Value |innodb_file_per_table| temporarytemporary临时表空间用来存放临时表，默认情况下是在数据文件夹下的ibtmp1数据文件动增长12MB大小，当然也可以设置innodbtemp_data_file_path临时表空间文件在正常的shutdown，但在crash发生时不会清除，这就需要DBAmysql>showvariableslike | | |innodb_temp_data_file_path|ibtmp1:12M:autoextend如果发现临时表空间数据文件比较大，可以考虑重启MySQLredoInnoDB对redologbuffer写入到redolog文件的方式提供了组提交(group innodb日志持久化相关参数mit MySQL实例启动需要依赖f配置文件，而配置文件可以存在于多个操作系统 下f文件 datafile_spec1=mysql>showvariableslike | | |innodb_data_file_path|ibdata1:12M:autoextend|innodb_data_home_dir r=/innodb_data_home_dir= mysql>showvariableslike | | | |innodb_log_files_in_group| ino_lgropom_r=innodb_log_files_in_group参数用来定义日志文件的个数，默认和推荐值都是2innodb_log_file_size参数定义了每个日志文件的大小，rp在 志文件里而不发生切换，当然文件大小也有最大限制，就是所有日志文件的总大小 过512G。 mysql>showvariableslike | |Value | | |innodb_undo_log_truncate| | | |innodb_undo_tablespaces| mysql>showvariableslike |+

| |innodb_temp_data_file_path|ibtmp1:12M:autoextend 磁盘的blocksize接近的大小.Innodb_log_buffer_size参数确定了redolog缓存的大小，默认值是16M，其大小取决于是否 InnoDBinnodb-read-mysql>updatetempsetERROR1015(HY000):Can'tlockfile(errno:165-TableisreadInnoDBbufferpoolInnoDBbufferpoolBfer的 。Bfero的 Bfer而 被 InnoDBbufferpoolInnoDBbufferpool的大小可以在启动时配置，也可以在启动之后配置。增加和减少bufferpool的大小都是以大块的方式，块的大小由参数innodb_buffer_pool_chunk_size决定，默认为128M。Innodb_buffer_pool_size的大小可则bufferpool会被调整成大于设定值且最接近的一个值，如下例： >mysqld--innodb_buffer_pool_size=9G--mysql>SELECT• |@@innodb_buffer_pool_size/1024/1024/1024• 10.• 少都要以M于bufferpool少都要以M于bufferpool 。InnoDBbufferpoolbufferpool的大小可以动态修改，用set语句直接修改，当语句发起时，会一直等到当前 bufferpool就mysql>SETGLOBAL 当执行online的调整大小时，可以通过errorlog或者innodb_buffer_pool_resize_statusmysql>SHOWSTATUSWHERE | | +rhash+rhashtables.+|Innodb_buffer_pool_resize_status|Resizingalso InnoDBbufferpool配置多个bufferpool当bufferpool的大小是GB级别时，将一个bufferpool有独立的bufferpool所有的特性。MakingtheBufferPoolScan 3/8的位置。当被放入bufferpool的页被第一次 到bufferpool后但没有移动 InnoDBbufferpoolInnoDBbufferpool预存取（read-Readahead是异步地预先获取多个数据页到bufferpool的IO线性readahead：预测哪些页会被顺序 innodb_read_ahead_threshold时，innodb会触发异步readahead操作将真个区都读到bufferpool中。该参数的默认值是56，取值范围是0~64。随机readahead：通过已经在bufferpool中的数据页来预测哪些页会被随 其它页 通过执行showengineinnodbstatus命令显示的三个参数判断read-ahead算法的有InnoDBbufferpoolInnoDBbufferpoolflushing 将bufferpool中的脏页(已经修改但没有写到数据文件)flush掉。当bufferpool中的脏页所占百分比达到innodb_max_dirty_pages_pct_lvm会触发flush，当针对数据修改操作频繁的系统，flush可能会严重滞后导致有大量的bufferpool，有一些参数专门针对修改繁忙的系统可以调整 ：为防止redolog被填满，此参数设置一个阈值，如果redolog的容量超过此阈值，则执行adaptiveflush操作。Innodb_max_drity_pages_pct_lwmInnoDBbufferpool重置bufferpool间（原先在bufferpool中的数据页要从磁盘再次加载到内存中）。在数据库运行期间动态配置bufferpool数据页保留占比的方式是：SETGLOBALSETGLOBALinnodb_buffer_pool_dump_at_shutdown=ON;当服务器开启时重新加载bufferpool的方法是：mysqld-- InnoDBbufferpool数据库运行期间保存和重新加载bufferpool的方法是：SETGLOBALinnodb_buffer_pool_dump_now=ON;SETGLOBAL查看bufferpoolmysql>SHOWSTATUSLIKE | | |Innodb_buffer_pool_dump_status|Bufferpool(s)dumpcompletedat1705190:16:28 1rowinset(0.00mysql>SHOWSTATUSLIKE+|170519+|1705190:14:05+| | |Innodb_buffer_pool_load_status|Bufferpool(s)loadcompleted 1rowinset(0.00InnoDBbufferpoolbufferpool通过showengineinnodbstatus命令可以查看bufferpool的运行情况BUFFERPOOLANDMEMORYTotallargememoryDictionaryallocatedBufferpoolbuffersDatabaseOlddatabaseModifieddbpagesPendingreads0Pendingwrites:LRU0,flushlist0,singlepage0Pagesmadeyoung4,notyoung00.10youngs/s,0.00nonsread197,created5523,written0.00reads/s,190.89creates/s,244.94Bufferpoolhitrate1000/1000,young-makingrate0/10000/Pagesreadahead0.00/s,evictedwithoutaccess0.00/s,Randomreadahead0.00/sLRUlen:5720,unzip_LRUlen:I/Osum[0]:cur[0],unzipInnoDBchangebufferchangebuffering是MySQL5.5加入的新特性，changebuffering是insertbuffer的加强，insertbuffer只针对insert有效，changebuffering对insert、delete、update(delete+insert)、purge都有效。当修改一个索引块(secondaryindex)时的数据时，索引块在buffterpool信息就会被cache在changebuffer中，当通过索引扫描把需要的索引块到bufferpool时，会和changebuffer中修改信息合并，再择机写回disk。Changebuffer是作为bufferpool中的一部分存在。all:默认值，缓存insert,delete,purges操作none:不缓存inserts:缓存insertdeletes:缓存delete操作changes:缓存insert和delete操作purges:缓 执行的物理删除操innodb_change_buffer_max_size参数配置changebuffer在bufferpool IO mysql>showengineinnodbstatus\GFILEI/OI/Othread0state:waitingforcompletedaiorequests(insertbufferthread)I/Othread1state:waitingforcompletedaiorequests(logthread)I/Othread2state:waitingforcompletedaiorequests(readthread)I/Othread3state:waitingforcompletedaiorequests(readthread)I/Othread4state:waitingforcompletedaiorequests(readthread)I/Othread5state:waitingforcompletedaiorequests(readthread)I/Othread6state:waitingforcompletedaiorequests(writethread)I/Othread7state:waitingforcompletedaiorequests(writethread)I/Othread8state:waitingforcompletedaiorequests(writethread)I/Othread9state:waitingforcompletedaiorequests(writethread)使用Linux异步InnoDB 相当 RPM的磁盘性能InnoDBpurge ，则可以考虑增加此值，最大可以设置为32InnoDBInnodb，这是可以执 yzetable语句来同步更新统计信息Createtable和altertable语句中的Stats_persistent,stats_auto_recalcstats_sample_pages yzetable命令来收集统计资料。 yzetable命令重新计算CREATETABLE`t1`(`id`int(8)NOTNULL`data``date`datetime,PRIMARYKEY(`id`),INDEX`DATE_IX`)ENGINE=InnoDB,mysql>select*frommysql.innodb_table_statswhere |database_name|table_name|last_updatesum_of_other_index_sizes|

|n_rows|clustered_index_size |

|

|2017-05-1810:48:39 0 1 1 1rowinset(0.00mysql>call+dex_size+97+mysql>select*frommysql.innodb_table_stats+dex_size+97+mysql>select*frommysql.innodb_table_statswhere |database_name|table_name|last_updatesum_of_other_index_sizes|+|n_rows|+|++|+++++|2017-05-1810:51:10|96096289++++mysql>select*frommysql.innodb_table_statswhere |database_name|table_name|last_updatesum_of_other_index_sizes|

|n_rows|clustered_index_size |

|

|2017-05-1810:51:10|96096

289 97 1rowinset(0.00++289 0|+mysql>select++289 0|+mysql>select*frommysql.innodb_table_statswhere |database_name|table_name|+|n_rows|+|++|+++++|2017-05-1810:56:22|90230++++ 数据之后，要执行flushtable表名命令来重新load此表的统计资料。innodb_table_stats表中每个目标表一行mysql>select*frommysql.innodb_table_statswhere***************************1.row***************************database_name:testtable_name:studentslast_update:2017-04-2117:12:07n_rows:mysql>select*frommysql.innodb_index_statswhere |database_name|table_name|index_name |last_update |stat_name |stat_value|sample_size|stat_description ||||||5|1||||||||1||Numberofleafpagestheindex|| ||2017-04-||1| |Numberofinthe|||||||5|1|||

| |

|2017-04-21 | ||Numberofleafpagesintheindex|| | |2017-04-1| |Numberofpagesinthe|| | 优化器统计信息会 在磁盘上，通过设置innodb_stats_persistent=ON参数（默认）yzetable yzetable语句手动刷新统计资料，或者在innodb_stats_on_metadata选项打开之后执行showtablestatus/showindex或查询information_schema.tables/statisticsmysql>altertablestudentsstats_persistent=0;QueryOK,0rowsaffected(0.00sec)Records:0Duplicates:0Warnings:mysql>truncatetablestudents; yzetable | | |Msg_type|Msg_text yze| | |database_name|table_name|index_name| ||

|stat_value|sample_size| ||courseindex|

||

||

|2017-05-1809:14:21|n_diff_pfx01|2017-05-1809:14:21|n_leaf_pages

97344NULL|NULL|NumberofleafpagesinULL|Numberofpagesintheindex 2|gender 20| ULL|Numberofleafpagesintheindex|LL|Numberofpagesintheindex

20| |

|

| |2017-05-1809:14:21|

289 ||

||

||

|2017-05-1809:15:30|n_diff_pfx01|2017-05-1809:15:30|n_diff_pfx02

0|100710| | | |2017-05-1809:15:30|n_leaf_pages 90 | | | |2017-05-1809:15:30| 161 mysql>altertablestudentsstats_persistent=1; yzetablestudents; | | |Msg_type|Msg_text |course.students yze| | 1rowinset(0.01mysql>select*frommysql.innodb_index_statswhere |database_name|table_name|index_name|last_update |stat_name |stat_value|sample_size| | | | |2017-05-1809:33:39|n_diff_pfx01 0 1| NULL NULL|Numberofpagesin 1| 1| NULL|NumberofleafpagesNULL|Numberofpagesin|course

|

| |2017-05-1809:33:39| ||||2017-05-1809:33:39|n_diff_pfx01||||2017-05-1809:33:39|n_diff_pfx02||||2017-05-1809:33:39|n_leaf_pagestheindex|course |||2017-05-1809:33:39| 1CREATETABLEt1(idKEYid_indexCREATETABLEt1(idKEYid_indexALTERTABLEt1COMMENT='MERGE_THRESHOLD=40';CREATETABLEt1(idKEYid_index(id)COMMENTCREATETABLEt1(idCREATEINDEXid_indexONt1(id)COMMENTmysql>SELECTNAME,COMMENTFROMWHERENAMElike | | | |Numberofindexpagemerge |||+++innodb_data_file_path=mysql>selecttable_namefrominformation_schema.tableswheretable_schema='mysql'and | | | | |slave_relay_log_info| 重置InnoDBredolog文件大小[root@vmware1bin]#/etc/init.d/mysql.serverTruncatetable通过定义createtable…datadirectory=绝对路径，可以将特定的表放在特定的磁盘或者createtable命令里显示的使用tablespace选项。SETGLOBALinnodb_file_per_table=1;ALTERTABLEtable_name_name.isl通过命令createtable…datadirectory=绝对路径可以将单表数据文件创建在另外的目_name.isl路径，相当于link文件mysql>USEDatabasemysql>SHOW+++| |Value |innodb_file_per_table| 1rowinset(0.00mysql>setglobalinnodb_file_per_table=0;QueryOK, rowsaffected(0.00sec)mysql>createtablestudents2(idint,namemysql>setglobalinnodb_file_per_table=1;QueryOK,0rowsaffected(0.00sec)mysql>altertablestudents2root@ip-172-31-18-152:/usr/local/mysql/data/course#lsstudents2.*students2.frmstudents2.ibdmysql>CREATETABLEt1(c1INTPRIMARYKEY)DATADIRECTORY='/alternative/directory';QueryOK,0rowsaffected(0.03sec)#MySQLcreatesa.ibdfileforthenewtableinasubdirectorythatcorresponding#tothedatabasenamedb_user@ubuntu:~/alternative/directory/test$ls#MySQLcreatesa.islfilecontainingthepathnameforthetableinadirectory#beneaththeMySQLdatadirectorydb_user@ubuntu:~/mysql/data/test$lsdb.optt1.frmt1.islCREATETABLEt2(c1INTPRIMARYKEY)TABLESPACE=innodb_file_per_taDATADIRECTORY= mysqlusemysql>CREATETABLEt(c1INT)engine=InnoDB;mysql>usemysql>CREATETABLEt(c1INT)engine=InnoDB;mysql>ALTERTABLEtDISCARDmysql>usemysql>FLUSHTABLEStFOR >scp/path/to/datadir/test/t.{ibd,cfg}destination-mysql>usetest;mysqlUNLOCKTABLES;useALTERt设置Undolog默认情况下undolog 在系统表空间里，我们也可以将其存放在一个或多个独立表mysql>showvariableslike | |Value | | |innodb_undo_log_truncate| | | |innodb_undo_tablespaces| mysql>SETGLOBALinnodb_undo_log_truncate=ON;mysql>SELECT++||++||mysql>SETGLOBALinnodb_max_undo_log_size= QueryOK,0rowsaffected(0.00sec)InnoDB通过createtablespace命令可以创建一个共享的InnoDB表空间，和系统表空间一样，多个 CREATETABLESPACEtablespace_nameADDDATAFILE'file_name'[FILE_BLOCK_SIZE=value][ENGINE[=]mysql>CREATETABLESPACE`ts1`ADDDATAFILE'ts1.ibd'Engine=InnoDB;##创建在 mysql>CREATETABLESPACE`ts1`ADDDATAFILE'/my/tablespace/directory/ts1.ibd'pace或者altertablepace或者altertable…mysql>CREATETABLEt1(c1INTPRIMARYKEY)TABLESPACEts1 mysql>ALTERTABLEt2TABLESPACEts1;通过altertable命令可以将InnoDB表在系统表空间、独立表空间和普通表空间之间转化：ALTERTABLEtbl_nameTABLESPACE[=]tablespace_name##从系统表空间或者独立表空ALTERTABLEtbl_name...TABLESPACE[=]innodb_system##从普通表空间或者独立表空ALTERTABLEtbl_name..