2012-06 网易DBA 王洪权：Mysql 5.6新特性介绍

2020-03-01 218浏览

1.Mysql 5.6 新特性介绍网易DBA 王洪权 mydbalife@gmail.com 2012-06-06
2.主要内容 mysql 5.6 查询优化 InnoDB performance mysql replication
3.Mysql 5.6 新特性--index condition pushdown Index Condition Pushdown MySQL 5.6+, MariaDB 5.3+
4.为什么需要index condition pushdown A non-index only read is a two-stepprocess:1.读索引 2.读记录 3.检查where条件，做过滤
5.index condition pushdown原理 1.读索引 2.检查索引的条件 3.读整行记录 4.检查where条件，过滤数据
6.Mysql 5.6 新特性--index condition pushdown #基础表注意： mysql 在复合索引中，第一列是范围查询，第二列通常是无法利用索引的，建议第一列的查询为=, <=>, or IS NULL
7.using where VS index condition pushdown # 早期版本，5.6 以下 MariaDB 5.3 以下 # MariaDB 5.3+, MySQL5.6+
8.using where VS index condition pushdown # 早期版本，5.6 以下 MariaDB 5.3 以下 # MariaDB 5.3+, MySQL5.6+
9.Mysql 5.6 新特性-- index condition pushdown
10.Mysql 5.6 新特性--index condition pushdown Counter Name MySQL5.5 MySQL5.6 Created_tmp_disk_tables 0 0 Handler_read_key 491 495 Handler_read_next 14667 113 Handler_read_rnd_next 200761 200693 Innodb_buffer_pool_read_ahead 1913 1912 Innodb_buffer_pool_read_requests 103626 37440 Innodb_buffer_pool_reads 13909 1265 Innodb_data_read 261410816（249M） 54235136(51M) Innodb_data_reads 15834 3189 Innodb_pages_read 15821 3176 Innodb_rows_read 214667 200113 Select_scan 4 4 Sort_scan 0 0 Innodb_buffer_pool_pages_data 239.13M 44.8906M 查询耗时 1m13.334s 0m9.907s
11.index condition pushdown 性能上的提升一方面提升了查询性能，使的联合索引的范围查询速度得到很大提升令一方面，节省了BP中的内存空间。
12.Mysql 5.6 新特性--Multi-Range-Read Multi-Range-Read MySQL 5.6+, MariaDB 5.3+
13.Mysql 5.6 新特性--Multi-Range-Read 随机读，接近转换成顺序读
14.Mysql 5.6 新特性--Multi-Range-Read
15.Multi-Range-Read 在IO密集型情况测试数据 MySQL5.5 MySQL5.6 MySQL5.5 read_rnd_bufer_size=4M MySQL5.6 read_rnd_bufer_size=4M Created_tmp_disk _tables 0 0 0 0 Handler_read_key 283752 340796 283752 340796 Handler_read_nex t 286093 286093 286093 286093 Handler_read_rnd _next 37944 37944 37944 37944 Innodb_buffer_po ol_read_ahead 0 17776 0 17776 Innodb_buffer_po ol_read_requests 1551628 1461964 1551384 1461847 Innodb_buffer_po ol_reads 77336 40371 77347 40370 Innodb_data_read 1269256192（1.2G） 954863616(910M) 1269436416(1.2G) 954847232(910M) Innodb_data_read s 77350 58161 77361 58160 Innodb_pages_rea d 77335 58146 77346 58145 Innodb_rows_rea d 398157 455197 398157 455197 Select_scan 1 1 1 1 Sort_scan 1 1 1 1 查询耗时 10m57.584s 2m18.757s 6m31.233s 2m43.835s Counter Name
16.Multi-Range-Read 小结在数据量很大的情况下，使用MRR,变成顺序读，性能提高还是很大的，顺序读是非常快，因为： 1 磁头寻道变得顺序，不会再来回寻道。 2 mysql 有线性预读功能。 3 每个数据页将只被读取一次，避免了多次对同一个页的读但是也有一种除外：如果你的表很小的话，填充在OS cache中，采用MRR会有一定的CPU开销
17.Mysql 5.6 新特性--Batched Key Access Batched Key Access MySQL 5.6+, MariaDB 5.3+
18.Mysql 5.6 新特性--Batched Key Access 依赖MRR,随机读，接近转换成顺序读
19.Batched Key Access
20.Batched Key Access 在IO密集型情况测试数据 Counter Name MySQL5.5默认配置（join_buffer_size=128K read_rnd_bufer_size=128K） MySQL5.6默认 join_buffer_size=128K read_rnd_bufer_size=256K MySQL5.5 join_buffer_size=6M read_rnd_bufer_size=6M MySQL5.6 join_buffer_size=6M read_rnd_bufer_size=6M Created_tmp_disk_tables 0 0 0 0 Handler_read_key 203944 1076746 203944 1076746 Handler_read_next 872798 872798 872798 872798 Handler_read_rnd_next 161500 164237 161500 161500 Innodb_buffer_pool_read _ahead 1726 1663 1728 65826 Innodb_buffer_pool_read _requests 4141420 3898204 4141212 3360574 Innodb_buffer_pool_read s 164888 180491 164838 13584 Innodb_data_read 2731986944（2.5G） 2986594304(2.7G) 2731200512（2.5G） 1303236608(1.2G) Innodb_data_reads 166627 182167 166579 79423 Innodb_pages_read 166613 182153 166565 79409 Innodb_rows_read 1022798 1895596 1022798 1895596 Select_scan 2 2 2 2 Sort_scan 1 1 1 1 查询耗时 20m10.271s 19m55.038s 18m39.165s 6m16.147s
21.Batched Key Access 小结
22.InnoDB performance Page cleaner 线程的引入 (before master thread) 1 减轻了主线程的工作,脏页的刷新由page cleaner线程进行。 2 page cleaner线程，处理dirty page的flush动作(包括LRU list flush与 flush list flush)，降低page flush对于用户的影响. page_cleaner 这个线程每秒都会被唤醒一次
23.InnoDB performance 死锁检测增强 set global innodb_print_all_deadlocks=on 5.6 中引入参数innodb_print_all_deadlocks，这个参数是全局设置的，可以把所有的死锁状况打印到error日志中，如果应用程序不具有相应的错误处理逻辑检测回滚操作，这个参数将对你在进行故障诊断的时候很有帮助先前检测锁状况show engine innodb status\G; 在mysql库下创建表create table innodb_lock_monitor(a int)将信息打印到error log
24.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）默认不指定的时候是128个回滚段。（注意要想增加回滚段的时候必须要重启mysql) 官方建议最好是放在SSD上,有待改进：动态的添加和删除undo表空间
25.InnoDB performance Innodb page size 增强 Innodb_page_size 设置4k,8k,16k灵活设置 1.小的内存页以为着每个页中存储的数据就更少，对于SSD设备，它没有寻道花费的开销，这个时候就会读更多的页到内存，而每个页中有效的记录更多，这样的话，整体的会使内存更有效的利用. 2.更多的数据页，肯定会造成BP管理上的开销实施：导出你现有库的数据，通过逻辑导出（mysqldump）,移动或者直接删除系统表空间ibdata1和日志文件（ib_logfile0 & ib_logfile1），重新再 my.cnf 中设置innodb-page-size 到 4k 或者 8K,导入。
26.InnoDB performance Page checksum 增强 innodb_checksum_algorithm 值innodb ,strict_innodb, crc32 ,strict_crc32,none strict_none 好处：保护数据损坏硬件损坏, 软件的Bugs, Innodb 自身的Bugs 并不能完全代替文件系统的Checksums Checksum在什么时候发生呢？当页从硬盘读到BP的时候当页更新的后刷新到磁盘可能会有一定的开销注意：在crc32之前，对页头和页尾的校验算法是不一样，因此页头和页尾存储的值是不一样的。crc32的校验算法页头和页尾的校验方法是是一样的, 所以页头和页尾的值是一样的。
27.InnoDB performance 统计信息持久化 5.6 引入innodb_analyze_is_persistent 默认 OFF,innodb_stats_persistent_sample_pages 默认是20，可动态调整的作用范围是全局 innodb_stats_sample_pages 废弃默认8 innodb_stats_transient_sample_pages 新版本默认8 innodb_stats_on_metadata 默认ON innodb_stats_on_metadat 该参数控制着以下操作是否自动收集统计信息 * 当表打开的时候 * 当表有太多的改变(由于插入更新或者删除操作，表的1/16数据已经发生变化) * 当运行 ANALYZE TABLE 分析具体的表的时候 * 当执行SHOW TABLE STATUS 或者 SHOW INDEX FROM *当访问 INFORMATION_SCHEMA.TABLES 或者INFORMATION_SCHEMA.STATISTICS. 优缺点：可以让sql 的执行计划更稳定。需要定期analyze table 以保证执行计划的信息部过期
28.InnoDB performance innodb_purge_threads 可以设置大于1，有效的进行purge InnoDB REDO log size up to 512 Gbyte (日志大小达到了512G，有效的提高了检查点的写入)
29.InnoDB performance 优化器跟踪这个特性默认是关闭的，全局开启优化器跟踪会造成大约20%的性能开销, 可以查看join顺序，执行计划中的详细信息，花费的成本。最终为什么选择该执行计划设置跟踪信息占用内存大小，开启跟踪 SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000; SET end_markers_in_json=true; set optimizer_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;
30.InnoDB performance Explain 增强在MySQL 5.6.3 中 EXPLAIN 命令可以用于数据修改执行计划可以应用于 INSERT, REPLACE, UPDATE and DELETE和SELECT EXPLAIN FORMAT=JSON SQL; 可以更加清晰的看到执行计划的一些额外信息。 infomation_schema增强 MySQL 5.6:添加11个New INFORMATION_SCHEMA 表 7 数据字典相关的表 3 个 Buffer Pool 相关的表 1 个全局统计相关的表 PERFORMANCE_SCHEMA库下添加一些用于诊断数据库性能的表，可以通过这些表监控系统的整个状况
31.mysql replication slave使用表来保存复制信息复制事件checksum 多线程slave 延时复制优化了基于行的复制查询日志中的内容写入到binlog 远程备份binlog 全局事务标识IDS
32.slave使用表来保存复制信息 MySQL <5.6 事务数据在表里复制信息在文件里 MySQL 5.6 事务数据在表里复制信息在表里
33.slave使用表来保存复制信息 • 系统表: – mysql.slave_master_info (master.info) – mysql.slave_relay_log_info (relay-log.info) 启动的时候设置或天添加参数到my.cnf --master-info-repository=TABLE --relay-log-info-repository=TABLE 启动后设置 SET GLOBAL master_info_repository = 'TABLE'; SET GLOBAL relay_log_info_repository = 'TABLE'; start slave;
34.slave使用表来保存复制信息 Slave Tables for Replication Information select * from slave_master_info
35.复制事件checksum 当事件被应用之前，检测复制事件中的错误。 Guards against bugs and disk or network corruptions CRC-32 校验, 比 ISO-3309 更精准(支持zlib算法) mysqld 设置: binlog-checksum= NONE or CRC32 每个 session都会产生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;
36.复制事件checksum binlog-checksum对应图中1 master-verify-checksum对应图中2 slave-sql-verify-checksum对应图中5http://mysqlmusings.blogspot.com/2011/04/replication-eventchecksum.html
37.Multi-Threaded Slaves
38.Multi-Threaded Slaves mysql> SET GLOBAL slave_parallel_workers=2; (root:testdb:Wed'>root:testdb:Wed