際際滷

際際滷Share a Scribd company logo

What's New In MySQL 5.6

Download as PPTX, PDF
Abdul Manaf
Abdul Manaf

This document summarizes several performance improvements in MySQL 5.6 including index condition pushdown, multi-range read, batched key access, and persistent optimizer statistics. It provides examples of query execution with and without these optimizations, showing significant performance gains in MySQL 5.6 for queries that push index conditions down to the storage engine, read multiple index ranges sequentially, and access keys in batches for joins.

1 of 28
Download to read offline
What's New in MySQL 5.6 By Abdul Manaf
Some improvements in MySQL 5.6 Basic configuration changes EXPLAIN for DML queries Performance Improvements Index Condition Pushdown Multi-Range Read File Sort Optimization Persistent Optimizer Stats Partitioning Improvements
Some basic configuration changes InnoDB File Per Table is enabled by default Larger Buffer Pool and Transaction Log file Optimized Row-Based Replication Multi-Threaded Slaves Performance Schema overhead
EXPLAIN for DML queries Explain for DML queries (INSERT/UPDATE/DELETE) is available with this version of MySQL. EXPLAIN DELETE FROM couponG ** 1. row ** id: 1 select_type: SIMPLE table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1548305 Extra: Deleting all rows 1 row in set (0.00 sec)
Index Condition Pushdown Optimization Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP ,if parts of the WHERE condition can be evaluated by using only fields from the index, the MySQL pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. Index Condition Pushdown optimization is used for the range, ref, eq_ref, and ref_or_null access methods when there is a need to access full table rows Can be used for InnoDB and MyISAM tables. Not supported with partitioned tables in MySQL 5.6
ICP Lets say we want to execute below query, we will be comparing query execution in MySQL 5.5 and MySQL 5.6. SELECT * FROM coupon WHERE store_id = 1525 AND name LIKE '%Memorial%' ; Index is on (`store_id`,`name`)
Without ICP (5.5) mysql> EXPLAIN SELECT * FROM coupon -> WHERE store_id = 1525 AND -> name LIKE '%Memorial%' G * 1. row * id: 1 select_type: SIMPLE table: coupon type: ref possible_keys: idx_test_icp,idx_test_icp_2 key: idx_test_icp key_len: 4 ref: const rows: 638280 Extra: Using where 1 row in set (0.00 sec) SHOW STATUS LIKE 'Hand%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 316312 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 84 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 82 | +----------------------------+--------+
With ICP (5.6) mysql> EXPLAIN SELECT * FROM coupon -> WHERE store_id = 1525 -> AND name LIKE '%Memorial%'G ** 1. row **** id: 1 select_type: SIMPLE table: coupon type: ref possible_keys: idx_test_icp,idx_test_icp_2 key: idx_test_icp key_len: 4 ref: const rows: 633466 Extra: Using index condition 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Hand%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 312 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 65 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 63 | +----------------------------+-------+
Comparison of ICP Execution Execution time for this example: MySQL 5.5: 12.76 sec MySQL 5.6: 0.15 sec The Results are consistent across multiple executions
Multi-Range Read (MRR) Read data sequentially from disk. For secondary indexes, the order for the index entries on disk is different than the order of disk blocks for the full rows. Instead of retrieving the full rows using a sequence of small out-of-order reads, MRR scans one or more index ranges used in a query, sorts the associated disk blocks for the row data, then reads those disk blocks using larger sequential I/O requests. The speedup benefits operations such as range index scans and equi-joins on indexed columns. In below Example the index is as follows KEY `idx_test_icp_2` (`store_id`,`custom_sort_order_rank_goupd_id`),
Without MRR (5.5) mysql> EXPLAIN SELECT * FROM coupon -> WHERE ( store_id > 1023 AND store_id < 1525 ) -> AND ( custom_sort_order_rank_goupd_id = 14 ) G 1. row * id: 1 select_type: SIMPLE table: coupon type: range possible_keys: idx_test_icp,idx_test_icp_2 key: idx_test_icp key_len: 4 ref: NULL rows: 208034 Extra: Using where 1 row in set (0.00 sec) mysql> show status like 'Hand%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 113488 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 84 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 82 | +----------------------------+--------+
With MRR (5.6) mysql> EXPLAIN SELECT * FROM coupon -> WHERE ( store_id > 1023 AND store_id < 1525 ) -> AND (custom_sort_order_rank_goupd_id = 14)G ** 1. row ** id: 1 select_type: SIMPLE table: coupon type: range possible_keys: idx_test_icp,idx_test_icp_2 key: idx_test_icp_2 key_len: 4 ref: NULL rows: 209650 Extra: Using index condition; Using MRR 1 row in set (0.00 sec) mysql> show status like 'Hand%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 4 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 2721 | | Handler_read_last | 0 | | Handler_read_next | 2720 | | Handler_read_prev | 0 | | Handler_read_rnd | 2720 | | Handler_read_rnd_next | 65 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 63 | +----------------------------+-------+
Comparison of MRR Execution Execution time for this example: MySQL 5.5: (1.82 sec) MySQL 5.6 (w/MRR, wo/ICP): (0.09 Sec) The results are consistent between executions
Batched Key Access (BKA) It retrieves keys in batches and allows MRR usage for JOINs, as an alternative to standard Nested Loop Join execution Not enabled by default we need to set like below SET optimizer_switch='mrr=on,mrr_cost_based=of f,batched_key_access=on';
Without BKA (5.5) EXPLAIN SELECT c.coupon_id as c_id, `c` . *,`st`.`name` AS `store` FROM `coupon` AS `c` JOIN `store` AS `st` ON st.store_id = c.store_id WHERE (st.store_id > 50 AND st.store_id < 1000)G **** 1. row *** id: 1 select_type: SIMPLE table: st type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 1210 Extra: Using where ****v**** 2. row *** id: 1 select_type: SIMPLE table: c type: ref possible_keys: idx_test_icp,idx_test_icp_2 key: idx_test_icp key_len: 4 ref: sonicsave.st.store_id rows: 46 Extra: +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 941 | | Handler_read_last | 0 | | Handler_read_next | 573892 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 84 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 82 | +----------------------------+--------+
With BKA (5.6) EXPLAIN SELECT c.coupon_id as c_id, `c` . *,`st`.`name` AS `store` FROM `coupon` AS `c` JOIN `store` AS `st` ON st.store_id = c.store_id WHERE (st.store_id > 50 AND st.store_id < 1000)G * 1. row id: 1 select_type: SIMPLE table: st type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 1210 Extra: Using index condition; Using MRR * 2. row id: 1 select_type: SIMPLE table: c type: ref possible_keys: idx_test_icp,idx_test_icp_2,idx_store key: idx_test_icp key_len: 4 ref: sonicsave.st.store_id rows: 103 Extra: Using join buffer (Batched Key Access) 2 rows in set (0.00 sec) mysql> SHOW STATUS LIKE 'Hand%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 4 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 941 | | Handler_read_last | 0 | | Handler_read_next | 573892 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 65 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 63 | +----------------------------+--------+
Comparison of BKA Execution Execution time for this example: MySQL 5.5: (13.78 sec) MySQL 5.6: (9.73 sec) The results are consistent between executions We can also gain some performance improvement by increasing join_buffer_size, join_buffer_size does not affect execution time in the 5.5 version In example above I have set join_buffer_size to 50MB
Extended Secondary Keys Implicit primary keys inside secondary keys can be used for filtering (ref, range, etc), not only for covering index or sorting. use_index_extensions should be on , which is by default enabled in 5.6 In example below index in as KEY `idx_name` (`name`(30))
Extended Secondary Keys mysql> EXPLAIN SELECT * FROM coupon -> WHERE name = '25% off and Free Shipping on $150+ order.' -> AND coupon_id > 100000 AND coupon_id < 500000G ** 1. row ** id: 1 select_type: SIMPLE table: coupon type: range possible_keys: PRIMARY,idx_name key: idx_name key_len: 36 ref: NULL rows: 41 Extra: Using index condition; Using where 1 row in set (0.00 sec)
Duplicate Key Check In MySQL 5.6, If you create a duplicate index it will show a warning Example : I have already a index on column name as KEY `idx_name` (`name`(30)). Create another one with same definition CREATE INDEX `idx_duplicate_name` ON coupon(name(30)); Query OK, 0 rows affected, 1 warning (23.34 sec) Records: 0 Duplicates: 0 Warnings: 1 show warningsG ** 1. row ** Level: Note Code: 1831 Message: Duplicate index 'idx_duplicate_name' defined on the table 'coupon'. This is deprecated and will be disallowed in a future release. 1 row in set (0.01 sec)
Filesort with Short LIMIT For queries that combine ORDER BY non_indexed_column and a LIMIT x clause, this feature speeds up the sort when the contents of X rows can fit into the sort buffer. Works with all storage engines.
Filesort with Short LIMIT EXPLAIN SELECT * FROM coupon ORDER BY page_title LIMIT 100G ** 1. row ** id: 1 select_type: SIMPLE table: coupon type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1548305 Extra: Using filesort 1 row in set (0.00 sec)
Filesort with Short LIMIT Comparision Query : SELECT * FROM coupon ORDER BY page_title LIMIT 100; MySQL 5.6 : 3.56 Sec MySQL 5.5 : 10.25 Sec The results are consistent between executions
Join Order Table order algorithm has been optimized, which leads to better query plans when joining many tables
Persistent Optimizer Stats Provides improved accuracy of InnoDB index statistics, and consistency across MySQL restarts. This is Controlled by variable innodb_stats_persistent which is enabled by default.
Partitioning Improvements Explicit Partition Selection With partitioned tables, MySQL can restrict processing to only the relevant portions of a big data set. you can directly define which partitions are used in a query, DML, or data load operation, rather than repeating all the partitioning criteria in each statement
Partition Selection Examples SELECT * FROM coupon PARTITION (p0, p2); DELETE FROM coupon PARTITION (p0, p1); UPDATE coupon PARTITION (p0) SET store_id = 2 WHERE name = 'Jill'; SELECT e.id, s.city FROM employees AS e JOIN stores PARTITION (p1) AS s ...;
Replication Improvement Multi-Threaded Slaves Using multiple execution threads to apply replication events to slave servers. The multi-threaded slave splits work between worker threads based on the database name, allowing updates to be applied in parallel rather than sequentially.

More Related Content

What's New In MySQL 5.6

  • 1. What's New in MySQL 5.6 By Abdul Manaf
  • 2. Some improvements in MySQL 5.6 Basic configuration changes EXPLAIN for DML queries Performance Improvements Index Condition Pushdown Multi-Range Read File Sort Optimization Persistent Optimizer Stats Partitioning Improvements
  • 3. Some basic configuration changes InnoDB File Per Table is enabled by default Larger Buffer Pool and Transaction Log file Optimized Row-Based Replication Multi-Threaded Slaves Performance Schema overhead
  • 4. EXPLAIN for DML queries Explain for DML queries (INSERT/UPDATE/DELETE) is available with this version of MySQL. EXPLAIN DELETE FROM couponG ** 1. row ** id: 1 select_type: SIMPLE table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1548305 Extra: Deleting all rows 1 row in set (0.00 sec)
  • 5. Index Condition Pushdown Optimization Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP ,if parts of the WHERE condition can be evaluated by using only fields from the index, the MySQL pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. Index Condition Pushdown optimization is used for the range, ref, eq_ref, and ref_or_null access methods when there is a need to access full table rows Can be used for InnoDB and MyISAM tables. Not supported with partitioned tables in MySQL 5.6
  • 6. ICP Lets say we want to execute below query, we will be comparing query execution in MySQL 5.5 and MySQL 5.6. SELECT * FROM coupon WHERE store_id = 1525 AND name LIKE '%Memorial%' ; Index is on (`store_id`,`name`)
  • 7. Without ICP (5.5) mysql> EXPLAIN SELECT * FROM coupon -> WHERE store_id = 1525 AND -> name LIKE '%Memorial%' G * 1. row * id: 1 select_type: SIMPLE table: coupon type: ref possible_keys: idx_test_icp,idx_test_icp_2 key: idx_test_icp key_len: 4 ref: const rows: 638280 Extra: Using where 1 row in set (0.00 sec) SHOW STATUS LIKE 'Hand%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 316312 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 84 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 82 | +----------------------------+--------+
  • 8. With ICP (5.6) mysql> EXPLAIN SELECT * FROM coupon -> WHERE store_id = 1525 -> AND name LIKE '%Memorial%'G ** 1. row **** id: 1 select_type: SIMPLE table: coupon type: ref possible_keys: idx_test_icp,idx_test_icp_2 key: idx_test_icp key_len: 4 ref: const rows: 633466 Extra: Using index condition 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Hand%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 312 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 65 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 63 | +----------------------------+-------+
  • 9. Comparison of ICP Execution Execution time for this example: MySQL 5.5: 12.76 sec MySQL 5.6: 0.15 sec The Results are consistent across multiple executions
  • 10. Multi-Range Read (MRR) Read data sequentially from disk. For secondary indexes, the order for the index entries on disk is different than the order of disk blocks for the full rows. Instead of retrieving the full rows using a sequence of small out-of-order reads, MRR scans one or more index ranges used in a query, sorts the associated disk blocks for the row data, then reads those disk blocks using larger sequential I/O requests. The speedup benefits operations such as range index scans and equi-joins on indexed columns. In below Example the index is as follows KEY `idx_test_icp_2` (`store_id`,`custom_sort_order_rank_goupd_id`),
  • 11. Without MRR (5.5) mysql> EXPLAIN SELECT * FROM coupon -> WHERE ( store_id > 1023 AND store_id < 1525 ) -> AND ( custom_sort_order_rank_goupd_id = 14 ) G 1. row * id: 1 select_type: SIMPLE table: coupon type: range possible_keys: idx_test_icp,idx_test_icp_2 key: idx_test_icp key_len: 4 ref: NULL rows: 208034 Extra: Using where 1 row in set (0.00 sec) mysql> show status like 'Hand%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 113488 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 84 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 82 | +----------------------------+--------+
  • 12. With MRR (5.6) mysql> EXPLAIN SELECT * FROM coupon -> WHERE ( store_id > 1023 AND store_id < 1525 ) -> AND (custom_sort_order_rank_goupd_id = 14)G ** 1. row ** id: 1 select_type: SIMPLE table: coupon type: range possible_keys: idx_test_icp,idx_test_icp_2 key: idx_test_icp_2 key_len: 4 ref: NULL rows: 209650 Extra: Using index condition; Using MRR 1 row in set (0.00 sec) mysql> show status like 'Hand%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 4 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 2721 | | Handler_read_last | 0 | | Handler_read_next | 2720 | | Handler_read_prev | 0 | | Handler_read_rnd | 2720 | | Handler_read_rnd_next | 65 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 63 | +----------------------------+-------+
  • 13. Comparison of MRR Execution Execution time for this example: MySQL 5.5: (1.82 sec) MySQL 5.6 (w/MRR, wo/ICP): (0.09 Sec) The results are consistent between executions
  • 14. Batched Key Access (BKA) It retrieves keys in batches and allows MRR usage for JOINs, as an alternative to standard Nested Loop Join execution Not enabled by default we need to set like below SET optimizer_switch='mrr=on,mrr_cost_based=of f,batched_key_access=on';
  • 15. Without BKA (5.5) EXPLAIN SELECT c.coupon_id as c_id, `c` . *,`st`.`name` AS `store` FROM `coupon` AS `c` JOIN `store` AS `st` ON st.store_id = c.store_id WHERE (st.store_id > 50 AND st.store_id < 1000)G **** 1. row *** id: 1 select_type: SIMPLE table: st type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 1210 Extra: Using where ****v**** 2. row *** id: 1 select_type: SIMPLE table: c type: ref possible_keys: idx_test_icp,idx_test_icp_2 key: idx_test_icp key_len: 4 ref: sonicsave.st.store_id rows: 46 Extra: +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 941 | | Handler_read_last | 0 | | Handler_read_next | 573892 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 84 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 82 | +----------------------------+--------+
  • 16. With BKA (5.6) EXPLAIN SELECT c.coupon_id as c_id, `c` . *,`st`.`name` AS `store` FROM `coupon` AS `c` JOIN `store` AS `st` ON st.store_id = c.store_id WHERE (st.store_id > 50 AND st.store_id < 1000)G * 1. row id: 1 select_type: SIMPLE table: st type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 1210 Extra: Using index condition; Using MRR * 2. row id: 1 select_type: SIMPLE table: c type: ref possible_keys: idx_test_icp,idx_test_icp_2,idx_store key: idx_test_icp key_len: 4 ref: sonicsave.st.store_id rows: 103 Extra: Using join buffer (Batched Key Access) 2 rows in set (0.00 sec) mysql> SHOW STATUS LIKE 'Hand%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 4 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 941 | | Handler_read_last | 0 | | Handler_read_next | 573892 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 65 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 63 | +----------------------------+--------+
  • 17. Comparison of BKA Execution Execution time for this example: MySQL 5.5: (13.78 sec) MySQL 5.6: (9.73 sec) The results are consistent between executions We can also gain some performance improvement by increasing join_buffer_size, join_buffer_size does not affect execution time in the 5.5 version In example above I have set join_buffer_size to 50MB
  • 18. Extended Secondary Keys Implicit primary keys inside secondary keys can be used for filtering (ref, range, etc), not only for covering index or sorting. use_index_extensions should be on , which is by default enabled in 5.6 In example below index in as KEY `idx_name` (`name`(30))
  • 19. Extended Secondary Keys mysql> EXPLAIN SELECT * FROM coupon -> WHERE name = '25% off and Free Shipping on $150+ order.' -> AND coupon_id > 100000 AND coupon_id < 500000G ** 1. row ** id: 1 select_type: SIMPLE table: coupon type: range possible_keys: PRIMARY,idx_name key: idx_name key_len: 36 ref: NULL rows: 41 Extra: Using index condition; Using where 1 row in set (0.00 sec)
  • 20. Duplicate Key Check In MySQL 5.6, If you create a duplicate index it will show a warning Example : I have already a index on column name as KEY `idx_name` (`name`(30)). Create another one with same definition CREATE INDEX `idx_duplicate_name` ON coupon(name(30)); Query OK, 0 rows affected, 1 warning (23.34 sec) Records: 0 Duplicates: 0 Warnings: 1 show warningsG ** 1. row ** Level: Note Code: 1831 Message: Duplicate index 'idx_duplicate_name' defined on the table 'coupon'. This is deprecated and will be disallowed in a future release. 1 row in set (0.01 sec)
  • 21. Filesort with Short LIMIT For queries that combine ORDER BY non_indexed_column and a LIMIT x clause, this feature speeds up the sort when the contents of X rows can fit into the sort buffer. Works with all storage engines.
  • 22. Filesort with Short LIMIT EXPLAIN SELECT * FROM coupon ORDER BY page_title LIMIT 100G ** 1. row ** id: 1 select_type: SIMPLE table: coupon type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1548305 Extra: Using filesort 1 row in set (0.00 sec)
  • 23. Filesort with Short LIMIT Comparision Query : SELECT * FROM coupon ORDER BY page_title LIMIT 100; MySQL 5.6 : 3.56 Sec MySQL 5.5 : 10.25 Sec The results are consistent between executions
  • 24. Join Order Table order algorithm has been optimized, which leads to better query plans when joining many tables
  • 25. Persistent Optimizer Stats Provides improved accuracy of InnoDB index statistics, and consistency across MySQL restarts. This is Controlled by variable innodb_stats_persistent which is enabled by default.
  • 26. Partitioning Improvements Explicit Partition Selection With partitioned tables, MySQL can restrict processing to only the relevant portions of a big data set. you can directly define which partitions are used in a query, DML, or data load operation, rather than repeating all the partitioning criteria in each statement
  • 27. Partition Selection Examples SELECT * FROM coupon PARTITION (p0, p2); DELETE FROM coupon PARTITION (p0, p1); UPDATE coupon PARTITION (p0) SET store_id = 2 WHERE name = 'Jill'; SELECT e.id, s.city FROM employees AS e JOIN stores PARTITION (p1) AS s ...;
  • 28. Replication Improvement Multi-Threaded Slaves Using multiple execution threads to apply replication events to slave servers. The multi-threaded slave splits work between worker threads based on the database name, allowing updates to be applied in parallel rather than sequentially.