《Mysql5.6新特性介绍.ppt》由会员分享，可在线阅读，更多相关《Mysql5.6新特性介绍.ppt（50页珍藏版）》请在三一办公上搜索。
1、网易DBA 王洪权2012-06-06,Mysql 5.6 新特性介绍,主要内容,mysql 5.6 查询优化InnoDB performancemysql replication,Index Condition Pushdown MySQL 5.6+,MariaDB 5.3+,Mysql 5.6 新特性-index condition pushdown,为什么需要index condition pushdown,A non-index only read is atwo-step process:1.读索引 2.读记录3.检查where条件，做过滤,index condition pushd
2、own原理,1.读索引2.检查索引的条件3.读整行记录4.检查where条件，过滤数据,Mysql 5.6 新特性-index condition pushdown,注意：mysql 在复合索引中，第一列是范围查询，第二列通常是无法利用索引的，建议第一列的查询为=,or IS NULL,#基础表,#早期版本，5.6 以下 MariaDB 5.3 以下,using where VS index condition pushdown,#MariaDB 5.3+,MySQL5.6+,using where VS index condition pushdown,#MariaDB 5.3+,MySQL
3、5.6+,#早期版本，5.6 以下 MariaDB 5.3 以下,Mysql 5.6 新特性-index condition pushdown,Mysql 5.6 新特性-index condition pushdown,index condition pushdown 性能上的提升,一方面提升了查询性能，使的联合索引的范围查询速度得到很大提升令一方面，节省了BP中的内存空间。,Mysql 5.6 新特性-Multi-Range-Read,Multi-Range-Read MySQL 5.6+,MariaDB 5.3+,Mysql 5.6 新特性-Multi-Range-Read,随机读，接近
4、转换成顺序读,Mysql 5.6 新特性-Multi-Range-Read,Multi-Range-Read 在IO密集型情况测试数据,Multi-Range-Read,小结在数据量很大的情况下，使用MRR,变成顺序读，性能提高还是很大的，顺序读是非常快，因为：1 磁头寻道变得顺序，不会再来回寻道。2 mysql 有线性预读功能。3 每个数据页将只被读取一次，避免了多次对同一个页的读但是也有一种除外：如果你的表很小的话，填充在OS cache中，采用MRR会有一定的CPU开销,Mysql 5.6 新特性-Batched Key Access,Batched Key Access MySQL 5
5、.6+,MariaDB 5.3+,Mysql 5.6 新特性-Batched Key Access,依赖MRR,随机读，接近转换成顺序读,Batched Key Access,Batched Key Access 在IO密集型情况测试数据,Batched Key Access,小结,InnoDB performance,Page cleaner 线程的引入(before master thread)1 减轻了主线程的工作,脏页的刷新由page cleaner线程进行。2 page cleaner线程，处理dirty page的flush动作(包括LRU list flush与flush lis
6、t flush)，降低page flush对于用户的影响.page_cleaner 这个线程每秒都会被唤醒一次,InnoDB performance,死锁检测增强 set global innodb_print_all_deadlocks=on 5.6 中引入参数innodb_print_all_deadlocks，这个参数是全局设置的，可以把所有的死锁状况打印到error日志中，如果应用程序不具有相应的错误处理逻辑检测回滚操作，这个参数将对你在进行故障诊断的时候很有帮助 先前检测锁状况show engine innodb statusG;在mysql库下创建表create table inn
7、odb_lock_monitor(a int)将信息打印到error log,InnoDB performance,Undo 从系统表空间分离涉及到分离出undo表空间的参数1.innodb_undo_directory 只读变量，是不能动态修改的，在启动的时候设置,注意这个不能指定多个undo表空间到多个位置2.innodb_undo_tablespaces 设置undo 表空间的个数.默认单个undo_tabkespace大小10M大小3.inodb_undo_logs（代替了先前的参数innodb_rollback_segments）控制着回滚段的数量（注意范围是0-128）默认不指定的
8、时候是128个回滚段。（注意要想增加回滚段的时候必须要重启mysql)官方建议最好是放在SSD上,有待改进：动态的添加和删除undo表空间,InnoDB performance,Innodb page size 增强Innodb_page_size 设置4k,8k,16k灵活设置 1.小的内存页以为着每个页中存储的数据就更少，对于SSD设备，它没有寻道花费的开销，这个时候就会读更多的页到内存，而每个页中有效的记录更多，这样的话，整体的会使内存更有效的利用.2.更多的数据页，肯定会造成BP管理上的开销实施：导出你现有库的数据，通过逻辑导出（mysqldump）,移动或者直接删除系统表空间ibda
9、ta1和日志文件（ib_logfile0&ib_logfile1），重新再f 中设置innodb-page-size 到 4k 或者 8K,导入。,InnoDB performance,Page checksum 增强innodb_checksum_algorithm 值innodb,strict_innodb,crc32,strict_crc32,none strict_none好处：保护数据损坏 硬件损坏,软件的Bugs,Innodb 自身的Bugs 并不能完全代替文件系统的ChecksumsChecksum在什么时候发生呢？当页从硬盘读到BP的时候 当页更新的后刷新到磁盘 可能会有一定的
10、开销注意：在crc32之前，对页头和页尾的校验算法是不一样，因此页头和页尾存储的值是不一样的。crc32的校验算法页头和页尾的校验方法是是一样的,所以页头和页尾的值是一样的。,InnoDB performance,统计信息持久化5.6 引入innodb_analyze_is_persistent 默认OFF,innodb_stats_persistent_sample_pages 默认是20，可动态调整的作用范围是全局innodb_stats_sample_pages 废弃默认8innodb_stats_transient_sample_pages 新版本默认8innodb_stats_on_
11、metadata 默认ONinnodb_stats_on_metadat 该参数控制着以下操作是否自动收集统计信息*当表打开的时候*当表有太多的改变(由于插入更新或者删除操作，表的1/16数据已经发生变化)*当运行 ANALYZE TABLE 分析具体的表的时候*当执行SHOW TABLE STATUS 或者 SHOW INDEX FROM*当访问 INFORMATION_SCHEMA.TABLES 或者INFORMATION_SCHEMA.STATISTICS.优缺点：可以让sql 的执行计划更稳定。需要定期analyze table 以保证执行计划的信息部过期,InnoDB perform
12、ance,innodb_purge_threads 可以设置大于1，有效的进行purgeInnoDB REDO log size up to 512 Gbyte(日志大小达到了512G，有效的提高了检查点的写入),InnoDB performance,优化器跟踪 这个特性默认是关闭的，全局开启优化器跟踪会造成大约20%的性能开销,可以查看join顺序，执行计划中的详细信息，花费的成本。最终为什么选择该执行计划设置跟踪信息占用内存大小，开启跟踪SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;SET end_markers_in_json=true;set opt
13、imizer_trace=enabled=on,one_line=off;SQL语句查看跟踪信息select*FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;dump跟踪信息SELECT TRACE INTO DUMPFILE/tmp/trace22.json FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;,InnoDB performance,Explain 增强 在MySQL 5.6.3 中 EXPLAIN 命令可以用于数据修改执行计划可以应用于 INSERT,REPLACE,UPDATE and DELETE和SELECT
14、 EXPLAIN FORMAT=JSON SQL;可以更加清晰的看到执行计划的一些额外信息。infomation_schema增强 MySQL 5.6:添加11个New INFORMATION_SCHEMA 表 7 数据字典相关的表 3 个 Buffer Pool 相关的表 1 个全局统计相关的表 PERFORMANCE_SCHEMA库下添加一些用于诊断数据库性能的表，可以通过这些表监控系统的整个状况,mysql replication,slave使用表来保存复制信息复制事件checksum多线程slave延时复制优化了基于行的复制查询日志中的内容写入到binlog远程备份binlog全局事务
15、标识IDS,MySQL 5.6事务数据在表里复制信息在文件里,slave使用表来保存复制信息,MySQL 5.6事务数据在表里复制信息在表里,slave使用表来保存复制信息,系统表:mysql.slave_master_info(master.info)mysql.slave_relay_log_info(relay-log.info)启动的时候设置或天添加参数到f-master-info-repository=TABLE-relay-log-info-repository=TABLE启动后设置SET GLOBAL master_info_repository=TABLE;SET GLOBAL
16、 relay_log_info_repository=TABLE;start slave;,slave使用表来保存复制信息,Slave Tables for Replication Informationselect*from slave_master_info,复制事件checksum,当事件被应用之前，检测复制事件中的错误。Guards against bugs and disk or network corruptionsCRC-32 校验,比 ISO-3309 更精准(支持zlib算法)mysqld 设置:binlog-checksum=NONE or CRC32 每个 session
17、都会产生checksum值，并且写入到binlog SET GLOBAL binlog_checksum=1;master-verify-checksum=0 or 1 Master 当从binlog dump事件的时候会校验checksum值 SET GLOBAL master_verify_checksum=1;slave-sql-verify-checksum=0 or 1 SQL线程当从relay log读取事件应用到slave之前会校验checksum 值 mysql SET GLOBAL slave_sql_verify_checksum=1;,复制事件checksum,binlo
18、g-checksum对应图中1 master-verify-checksum对应图中2 slave-sql-verify-checksum对应图中5,Multi-Threaded Slaves,Multi-Threaded Slaves,mysql SET GLOBAL slave_parallel_workers=2;(root:testdb:Wed May 30 11:21:19 2012)(none)SELECT slave_parallel_workers;+-+|slave_parallel_workers|+-+|2|+-+1 row in set(0.00 sec),(root:
19、testdb:Wed May 30 11:21:50 2012)(none)show processlist;+-+-+-+-+-+-+-+-+|Id|User|Host|db|Command|Time|State|Info|+-+-+-+-+-+-+-+-+|12|system user|NULL|Connect|190|Waiting for master to send event|NULL|13|system user|NULL|Connect|190|Slave has read all relay log;waiting for the slave I/O thread to up
20、date it|NULL|14|system user|NULL|Connect|190|Waiting for an event from Coordinator|NULL|15|system user|NULL|Connect|190|Waiting for an event from Coordinator|NULL|+-+-+-+-+-+-+-+-+,slave_parallel_workers 线程数量 0 禁用 最大 1024,Multi-Threaded Slaves,(root:testdb:Wed May 30 11:19:16 2012)(none)SELECT*FROM
21、mysql.slave_worker_infoG*1.row*Master_id:30082 Worker_id:0 Relay_log_name:Relay_log_pos:0 Master_log_name:Master_log_pos:0 Checkpoint_relay_log_name:Checkpoint_relay_log_pos:0Checkpoint_master_log_name:Checkpoint_master_log_pos:0 Checkpoint_seqno:0 Checkpoint_group_size:64 Checkpoint_group_bitmap:*2
22、.row*Master_id:30082 Worker_id:1 Relay_log_name:Relay_log_pos:0 Master_log_name:Master_log_pos:0 Checkpoint_relay_log_name:Checkpoint_relay_log_pos:0Checkpoint_master_log_name:Checkpoint_master_log_pos:0 Checkpoint_seqno:0 Checkpoint_group_size:64 Checkpoint_group_bitmap:,Multi-Threaded Slaves,影响单个s
23、lave 的worker线程的队列的参数为slave_pending_jobs_size_max,Multi-Threaded Slaves,Multi-Threaded Slaves,性能上的改进：提高了slave 性能 slave端的sql应用在不同数据库间变成并行:减少了slave的滞后,增加了slave的吞吐量 变成数据库（schema）级别的复制，每个数据库的更改将会被应用，注意这里每个库的应用还是串行的，并且提交变得相互独立 在重启恢复的时候是自动完成的（串行）Slave段所有的事务日志都已经写入到了relay log保证了数据部丢失。最糟糕的情况是所有的操作都是对同一个库进行操作
24、。,延时slave,在每个slave sql线程执行的时候都要等延迟时间到后进行下一个操作。最大支持 68 年。在一定程度上防止了误操作，比如说删表等等。可以一定程度上作为有效的数据库备份，无需再另行备份。,延时slave,当发生问题的时候我们要做的是，在主库上查找删除表的位置，也可以用 mysqlbinlog查找。master SHOW BINLOG EVENTSG*12.row*Log_name:ws2-bin.000001 Pos:984 Event_type:Query Server_id:1End_log_pos:1096 Info:use test;DROP TABLE test1
25、/*generated by server*/在从库上slave2 STOP SLAVE;slave2 START SLAVE UNTIL-MASTER_LOG_FILE=ws2-bin.000001,-MASTER_LOG_POS=984;,案例,操作人员上午误删除了test库的test1 表，数据库没有备份,只有延时slave，延时slave配置延时1天,全局唯一server IDs,在主库上：获得主库的uuid(root:testdb:Thu May 31 13:18:58 2012)(none)show variables like%uuid%;+-+-+|Variable_name|
26、Value|+-+-+|server_uuid|dad8f1f6-18af-11e1-992c-000c29d4b063|+-+-+1 row in set(0.00 sec)获得从库的uuid(root:testdb:Thu May 31 13:19:01 2012)(none)show slave hosts;+-+-+-+-+-+|Server_id|Host|Port|Master_id|Slave_UUID|+-+-+-+-+-+|30082|3306|30081|9fe4cfec-a971-11e1-891c-000c299499ff|+-+-+-+-+-+,在从库上:(root:
27、testdb:Thu May 31 13:16:32 2012)(none)show slave statusG;Master_Server_Id:30081 Master_UUID:dad8f1f6-18af-11e1-992c-000c29d4b063 Master_Info_File:mysql.slave_master_info SQL_Delay:60 SQL_Remaining_Delay:NULL Slave_SQL_Running_State:Slave has read all relay log;waiting for the slave I/O thread to upd
28、ate it Master_Retry_Count:86400 Master_Bind:获得主库的uuid另外这连个在mysql启动时候会在数据目录下生成一个f,uuid存储在 f 文件中rootmaster mysql#cat f autoserver-uuid=dad8f1f6-18af-11e1-992c-000c29d4b063rootsave mysql#cat f autoserver-uuid=9fe4cfec-a971-11e1-891c-000c299499ff,Global Transaction Identifier,Global Transaction Identifi
29、er,Global Transaction Identifier(全局事务表示符，实际上是以一个事件的形式存在mysql 中)在每个事务提交时会立刻记录到binlog 中,在binlog中每次的提交都会有GTD标示符。GTD结构如下：GTID=SID目前，它是一个128位的数字，用于标识提交的事务/（组提交事务）事件。SID通常是服务器的UUID，但可能不同，如果事务是由innodb以外的存储引擎可能不同。例如，对于NDB的，它将表示整个集群。GNO是一个64位序列号：1代表第一次改变记录的SID，2代表第二变化，等等。没有变化，可以表示为：GNO0。,Global Transaction I
30、dentifier,GTD配置要求mysql 5.6 在master 和 slave都要做#修改配置文件 f 在msater和slave 上#(master and slaves)mysqldlog-binlog-slave-updatesgtid-mode=ONdisable-gtid-unsafe-statements#重启 serversDisable-gtid-unsafe-statements 将会使下述情况发生时，向用户返回错误s不能在以下的情况下工作：1 非事务性更改,例如mysam，因为非事务性数据部能回滚操作。2 在一个事物中使用了临时表3 使用create table.se
31、lect,Global Transaction Identifier,Slave2 具体改变语句如下：Stop slave;CHANGE MASTER TO MASTER_HOST=.,MASTER_PORT=SOME_PORT,MASTER_USER=.,MASTER_AUTO_POSITION=1;START SLAVE;SET global.read_only=ON;MASTER_AUTO_POSITION=1 将会代替以前手动使用的MASTER_LOG_FILE 和MASTER_LOG_POS,告诉服务器使用GTD(GTD协议会使得中从实现握手操作),这个时候，当slave启动的时候，master和slave 将会自动相互通信，然后slave从正确的时间点向后开始复制，这个切换过程只需要slave上操作MASTER_AUTO_POSITION=1，以后的整个复制过程将是自动的。注意这里从的binlog里记录的 GTD是主的GTD。,Thank you谢谢,
