ps怎么做网站,群晖安装wordpress,wordpress小工具选项,大连建设局官网ICP
索引下推允许存储引擎直接利用索引中的字段值#xff0c;在遍历索引时就对查询条件进行初步筛选#xff0c;从而减少不必要的回表操作。 本人在一直使用的形象类比就是#xff1a;过滤生活污水#xff0c;污水处理站在处理污水的时候会先进行一次粗过滤#xff0c;再…ICP
索引下推允许存储引擎直接利用索引中的字段值在遍历索引时就对查询条件进行初步筛选从而减少不必要的回表操作。 本人在一直使用的形象类比就是过滤生活污水污水处理站在处理污水的时候会先进行一次粗过滤再进行后续的净化处理引申到索引下推的概念就是在居民排水时就使用滤网进行一次粗过滤污水站只需要进行细过滤这样可以大大减少污水站的负荷。 例如假设有一个联合索引 (name, age)查询条件为 name‘张三’ AND age18。在MySQL 5.6之前系统会先用索引找到所有 name‘张三’ 的记录然后回表获取完整数据行再检查 age18 的条件是否满足。而在启用索引下推后系统可以直接在索引中检查 age18 的条件只对符合条件的记录进行回表从而减少了回表次数
具体用例
select optimizer_switch LIKE %index_condition_pushdown%;
------------------------------------------------------
| optimizer_switch LIKE %index_condition_pushdown% |
------------------------------------------------------
| 1 |
------------------------------------------------------(rootlocalhost) [test] create index idx on t100w (k1,k2);
Query OK, 0 rows affected (6.21 sec)
Records: 0 Duplicates: 0 Warnings: 0(rootlocalhost) [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
(rootlocalhost) [test] desc formatjson select * from t100w where k1 Za and k2rsEF;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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
(rootlocalhost) [test] desc select * from t100w where k1 Za and k2rsEF;
-----------------------------------------------------------------------------------------------------------------------
| 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不到
[rootnode01 ~]# mysqlslap --defaults-file/etc/my.cnf --concurrency100 --iterations1 --create-schematest --queryselect * from test.t100w where k1 Za and k2rsEF engineinnodb --number-of-queries2000 -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(rootlocalhost) [(none)] SET global optimizer_switchindex_condition_pushdownOFF;
Query OK, 0 rows affected (0.00 sec)关闭ICP后可见运行时长增长巨大[rootnode01 ~]# mysqlslap --defaults-file/etc/my.cnf --concurrency100 --iterations1 --create-schematest --queryselect * from test.t100w where k1 Za and k2rsEF engineinnodb --number-of-queries2000 -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优化方式
删掉旧索引重新组织索引的排列顺序:
(rootlocalhost) [test] alter table test.t100w drop index idx;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0(rootlocalhost) [test] alter table test.t100w add index idx (k2,k1);
Query OK, 0 rows affected (4.58 sec)
Records: 0 Duplicates: 0 Warnings: 0从执行计划上可以看到其走到了两个字段的联合索引
(rootlocalhost) [test] desc formatjson select * from test.t100w where k1 Za and k2rsEF;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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))}}
} |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
尝试压力测试可以看到该种索引组织方式其性能更优越
[rootnode01 ~]# mysqlslap --defaults-file/etc/my.cnf --concurrency100 --iterations1 --create-schematest --queryselect * from test.t100w where k1 Za and k2rsEF engineinnodb --number-of-queries2000 -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打开后性能也有小幅的提升:
(rootlocalhost) [(none)] SET global optimizer_switchindex_condition_pushdownON;
Query OK, 0 rows affected (0.00 sec)(rootlocalhost) [(none)] exit
Bye
[rootnode01 ~]# mysqlslap --defaults-file/etc/my.cnf --concurrency100 --iterations1 --create-schematest --queryselect * from test.t100w where k1 Za and k2rsEF engineinnodb --number-of-queries2000 -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查看其执行计划
(rootlocalhost) [(none)] desc formatjson select * from test.t100w where k1 Za and k2rsEF;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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]}}
} |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
