mysql ICP和MRR性能优化测试

基本信息：

(product)root@localhost [(none)]> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.29-log |
+------------+
1 row in set (0.00 sec)

(product)root@localhost [(none)]> select count(*) from sakila.payment;
+----------+
| count(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.35 sec)

(product)root@localhost [(none)]> show create table sakila.payment\G

*************************** 1. row ***************************
       Table: payment
Create Table: CREATE TABLE `payment` (
  `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` smallint(5) unsigned NOT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `rental_id` int(11) DEFAULT NULL,
  `amount` decimal(5,2) NOT NULL,
  `payment_date` datetime NOT NULL,
  `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `fk_payment_rental` (`rental_id`),
  KEY `payment_date_ix1` (`payment_date`,`amount`),
  CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8

场景一：index_condition_pushdown=on,mrr=on,mrr_cost_based=on参数下性能测试

----查看优化参数设置，三个参数都有打开
(product)root@localhost [(none)]> show variables like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)----执行SQL查询，所需时间为0.81s
(product)root@localhost [(none)]> select * from sakila.payment where payment_date between date_format('2005-07-01 00:00:00','%Y-%m-%d %H:%i:%S') and date_format('2005-07-10 23:59:59','%Y-%m-%d
    '>
    '> %H:%i:%S') and amount=9.99;
---返回数据显示略---
42 rows in set (0.81 sec)

----划看执行计划显示为Using index condition，只使用到了ICP

(product)root@localhost [(none)]> explain select * from sakila.payment where payment_date between date_format('2005-07-01 00:00:00','%Y-%m-%d %H:%i:%S') and date_format('2005-07-10 23:59:59','%Y-%m-%d %H:%i:%S') and amount=9.99;
+----+-------------+---------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table   | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+---------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | payment | range | payment_date_ix1 | payment_date_ix1 | 8       | NULL | 2496 | Using index condition |
+----+-------------+---------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
重启mysql

小结：执行计划中没用出现MRR，是因为基于成本的算法来确定是否需要开启mrr特性。在MySQL5.6以及5.7版本中，基于成本的算法都过于保守，导致大部分情况下优化器都不会选择mrr特性。执行计划实际有启用ICP，SQL执行时间为0.81秒,

场景二：index_condition_pushdown=on,mrr=on,mrr_cost_based=off参数下性能测试

----设置优化参数，关闭mrr_cost_based
(product)root@localhost [(none)]> set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_cost_based=off';
Query OK, 0 rows affected (0.00 sec)

----查看优化参数，确认mrr_cost_based有关闭

(product)root@localhost [(none)]>  show variables like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

----执行SQL查询，所需时间为0.03s
(product)root@localhost [(none)]> select * from sakila.payment where payment_date between date_format('2005-07-01 00:00:00','%Y-%m-%d %H:%i:%S') and date_format('2005-07-10 23:59:59','%Y-%m-%d
    '>
    '> %H:%i:%S') and amount=9.99;
---返回数据显示略---
42 rows in set (0.03 sec)

----划看执行计划显示为Using index condition和Using MRR，使用到了ICP和MRR

(product)root@localhost [(none)]> explain select * from sakila.payment where payment_date between date_format('2005-07-01 00:00:00','%Y-%m-%d %H:%i:%S') and date_format('2005-07-10 23:59:59','%Y-%m-%d
    '>
    '> %H:%i:%S') and amount=9.99;
+----+-------------+---------+-------+------------------+------------------+---------+------+------+----------------------------------+
| id | select_type | table   | type  | possible_keys    | key              | key_len | ref  | rows | Extra                            |
+----+-------------+---------+-------+------------------+------------------+---------+------+------+----------------------------------+
|  1 | SIMPLE      | payment | range | payment_date_ix1 | payment_date_ix1 | 8       | NULL | 2496 | Using index condition; Using MRR |
+----+-------------+---------+-------+------------------+------------------+---------+------+------+----------------------------------+
1 row in set (0.01 sec)

重启mysql。

小结：在关闭基于成本的算法来确定是否需要开启mrr特性后，执行计划同时有出现ICP和MRR，SQL执行时间为0.03秒。

 

场景三：index_condition_pushdown=off,mrr=on,mrr_cost_based=on参数下性能测试

----设置优化参数，关闭ICP
(product)root@localhost [(none)]> set optimizer_switch='index_condition_pushdown=off,mrr=on,mrr_cost_based=on';
Query OK, 0 rows affected (0.00 sec)

----查看优化参数，确认ICP有关闭

(product)root@localhost [(none)]>  show variables like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

----执行SQL查询，所需时间为1.53s
(product)root@localhost [(none)]> select * from sakila.payment where payment_date between date_format('2005-07-01 00:00:00','%Y-%m-%d %H:%i:%S') and date_format('2005-07-10 23:59:59','%Y-%m-%d
    '>
    '> %H:%i:%S') and amount=9.99;
---返回数据显示略---
42 rows in set (1.53 sec)

----查看执行计划显示为Using where，未使用ICP

(product)root@localhost [(none)]> explain select * from sakila.payment where payment_date between date_format('2005-07-01 00:00:00','%Y-%m-%d %H:%i:%S') and date_format('2005-07-10 23:59:59','%Y-%m-%d
    '>
    '> %H:%i:%S') and amount=9.99;
+----+-------------+---------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys    | key              | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | payment | range | payment_date_ix1 | payment_date_ix1 | 8       | NULL | 2496 | Using where |
+----+-------------+---------+-------+------------------+------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

小结：在关闭ICP特性后，执行计划只是选择走范围索引扫描，SQL执行时间为1.53秒。

总结：
 1.建议关闭基于成本的算法来确定是否需要开启MRR特性，以保证有效开启MRR;
 2.ICP+MRR的性能最好，其次是ICP;
 3.ICP的性能比传统技术提高了47%，再开启MRR后，性能比传统技术提高了98%。