MySQL Index Condition Pushdown(ICP)性能优化方法实例

当关闭ICP时 查询在sending data环节时间消耗是 0.000735s

mysql> show profile cpu,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000045 | 0.000000 | 0.000000   | 0            | 0             |
| checking permissions | 0.000007 | 0.000000 | 0.000000   | 0            | 0             |
| Opening tables       | 0.000015 | 0.000000 | 0.000000   | 0            | 0             |
| init                 | 0.000024 | 0.000000 | 0.000000   | 0            | 0             |
| System lock          | 0.000009 | 0.000000 | 0.000000   | 0            | 0             |
| optimizing           | 0.000012 | 0.000000 | 0.000000   | 0            | 0             |
| statistics           | 0.000049 | 0.000000 | 0.000000   | 0            | 0             |
| preparing            | 0.000016 | 0.000000 | 0.000000   | 0            | 0             |
| executing            | 0.000005 | 0.000000 | 0.000000   | 0            | 0             |
| Sending data         | 0.000735 | 0.001000 | 0.000000   | 0            | 0             |
| end                  | 0.000008 | 0.000000 | 0.000000   | 0            | 0             |
| query end            | 0.000008 | 0.000000 | 0.000000   | 0            | 0             |
| closing tables       | 0.000009 | 0.000000 | 0.000000   | 0            | 0             |
| freeing items        | 0.000023 | 0.000000 | 0.000000   | 0            | 0             |
| cleaning up          | 0.000007 | 0.000000 | 0.000000   | 0            | 0             |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)