行业门户网站建设方案个人网站的制作模板
ICP
索引下推允许存储引擎直接利用索引中的字段值，在遍历索引时就对查询条件进行初步筛选，从而减少不必要的回表操作。
本人在一直使用的形象类比就是：过滤生活污水，污水处理站在处理污水的时候会先进行一次粗过滤，再进行后续的净化处理，引申到索引下推的概念就是，在居民排水时就使用滤网进行一次粗过滤，污水站只需要进行细过滤，这样可以大大减少污水站的负荷。
例如，假设有一个联合索引 (name, age)，查询条件为 name=‘张三’ AND age=18。在MySQL 5.6之前，系统会先用索引找到所有 name=‘张三’ 的记录，然后回表获取完整数据行，再检查 age=18 的条件是否满足。而在启用索引下推后，系统可以直接在索引中检查 age=18 的条件，只对符合条件的记录进行回表，从而减少了回表次数
具体用例：
select @@optimizer_switch LIKE '%index_condition_pushdown%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%index_condition_pushdown%' |
+------------------------------------------------------+
| 1 |
+------------------------------------------------------+(root@localhost) [test]> create index idx on t100w (k1,k2);
Query OK, 0 rows affected (6.21 sec)
Records: 0 Duplicates: 0 Warnings: 0(root@localhost) [test]> show index from t100w;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t100w | 1 | idx | 1 | k1 | A | 1173 | NULL | NULL | YES | BTREE | | | YES | NULL |
| t100w | 1 | idx | 2 | k2 | A | 161727 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)从执行计划中看到只使用到了K1索引的rang scan，但是有使用到ICP
(root@localhost) [test]> desc format=json select * from t100w where k1 > 'Za' and k2='rsEF';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"query_block": {"select_id": 1,"cost_info": {"query_cost": "32077.70"},"table": {"table_name": "t100w","access_type": "range","possible_keys": ["idx"],"key": "idx","used_key_parts": [ <<=="k1" <<==],"key_length": "9","rows_examined_per_scan": 52502,"rows_produced_per_join": 5250,"filtered": "10.00","index_condition": "((`test`.`t100w`.`k2` = 'rsEF') and (`test`.`t100w`.`k1` > 'Za'))", <<=="cost_info": {"read_cost": "31552.68","eval_cost": "525.02","prefix_cost": "32077.70","data_read_per_join": "205K"},"used_columns": ["id","num","k1","k2","dt"]}}
} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)range && Using index condition
(root@localhost) [test]> desc select * from t100w where k1 > 'Za' and k2='rsEF';
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
| 1 | SIMPLE | t100w | NULL | range | idx | idx | 9 | NULL | 52502 | 10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)尝试压力测试可见运行时长3s不到
[root@node01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -proot -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
BenchmarkRunning for engine rboseAverage number of seconds to run all queries: 2.496 secondsMinimum number of seconds to run all queries: 2.496 secondsMaximum number of seconds to run all queries: 2.496 secondsNumber of clients running queries: 100Average number of queries per client: 20(root@localhost) [(none)]> SET global optimizer_switch='index_condition_pushdown=OFF';
Query OK, 0 rows affected (0.00 sec)关闭ICP后可见运行时长增长巨大[root@node01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -proot -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
BenchmarkRunning for engine rboseAverage number of seconds to run all queries: 220.215 secondsMinimum number of seconds to run all queries: 220.215 secondsMaximum number of seconds to run all queries: 220.215 secondsNumber of clients running queries: 100Average number of queries per client: 20
优化方式
删掉旧索引，重新组织索引的排列顺序:
(root@localhost) [test]> alter table test.t100w drop index idx;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0(root@localhost) [test]> alter table test.t100w add index idx (k2,k1);
Query OK, 0 rows affected (4.58 sec)
Records: 0 Duplicates: 0 Warnings: 0从执行计划上可以看到，其走到了两个字段的联合索引
(root@localhost) [test]> desc format=json select * from test.t100w where k1 > 'Za' and k2='rsEF';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"query_block": {"select_id": 1,"cost_info": {"query_cost": "12.03"},"table": {"table_name": "t100w","access_type": "range","possible_keys": ["idx"],"key": "idx","used_key_parts": ["k2","k1"],"key_length": "26","rows_examined_per_scan": 19,"rows_produced_per_join": 19,"filtered": "100.00","cost_info": {"read_cost": "10.13","eval_cost": "1.90","prefix_cost": "12.03","data_read_per_join": "760"},"used_columns": ["id","num","k1","k2","dt"],"attached_condition": "((`test`.`t100w`.`k2` = 'rsEF') and (`test`.`t100w`.`k1` > 'Za'))"}}
} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
尝试压力测试，可以看到该种索引组织方式其性能更优越
[root@node01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -proot -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
BenchmarkRunning for engine rboseAverage number of seconds to run all queries: 0.201 secondsMinimum number of seconds to run all queries: 0.201 secondsMaximum number of seconds to run all queries: 0.201 secondsNumber of clients running queries: 100Average number of queries per client: 20将ICP打开后性能也有小幅的提升:
(root@localhost) [(none)]> SET global optimizer_switch='index_condition_pushdown=ON';
Query OK, 0 rows affected (0.00 sec)(root@localhost) [(none)]> exit
Bye
[root@node01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -proot -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
BenchmarkRunning for engine rboseAverage number of seconds to run all queries: 0.178 secondsMinimum number of seconds to run all queries: 0.178 secondsMaximum number of seconds to run all queries: 0.178 secondsNumber of clients running queries: 100Average number of queries per client: 20查看其执行计划
(root@localhost) [(none)]> desc format=json select * from test.t100w where k1 > 'Za' and k2='rsEF';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"query_block": {"select_id": 1,"cost_info": {"query_cost": "12.03"},"table": {"table_name": "t100w","access_type": "range","possible_keys": ["idx"],"key": "idx","used_key_parts": ["k2","k1"],"key_length": "26","rows_examined_per_scan": 19,"rows_produced_per_join": 19,"filtered": "100.00","index_condition": "((`test`.`t100w`.`k2` = 'rsEF') and (`test`.`t100w`.`k1` > 'Za'))","cost_info": {"read_cost": "10.13","eval_cost": "1.90","prefix_cost": "12.03","data_read_per_join": "760"},"used_columns": ["id","num","k1","k2","dt"]}}
} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+