Mysql parameter optimizer_switch

Optimizer_switch is introduced in mysql 5.1 to control the behavior of the mysql optimizer_switch. He has some result sets that enable and disable optimizer behavior through on and off control. The global validity period and session are used. The optional results of optimizer_swtich in mysql 5.5 are as follows. The results of optimizer_swtich vary with mysql versions. 5.1 and 5.6 refer to the official documentation. Mysql> select @ optimizer_switch; + centers + | @ optimizer_switch | + centers + | index_merge = on, index_merge_union = on, index_merge_sort_union = on, index_merge_intersection = on, engine_co Ndition_pushdown = on | + optimize + you can set optimizer_switch to control the optimization behavior during mysql optimization statements. In our production environment, one day the pressure on the mysql server was particularly high, and the load reached 100. The query found that the database had a large number of SQL statements in the state result sorting, result sorting consumes cpu and memory resources. Extract an SQL statement to view the execution plan. mysql> explain selectproduct_id, main_product_id, is_main_product, external_product_id from product where shop_id = '20170' AND status in ('0 ') AND main_product_id in ('0') AND product_type in ('0', '1') order by operator_datedesc limit 800,100; + ---- + ------------- + ---------------- + ------------- + ----------------------------------------------- + ------------------------------ + --------- + --- --- + ------ + Keys + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + -------------- + ------------- + keys + --------- + ------ + ----------------------------------------------------------------------- ------- + | 1 | SIMPLE | product_common | index_merge | main_product_id, shop_id, status, product_type | shop_id, main_product_id, status | 5, 5 | NULL | 810 | Usingintersect (shop_id, main_product_id, status); Using where; Using filesort type is index_merge (index merging and sorting ). If the business requires or requires no index, many indexes will be created on the data table. For this query, you can modify optimizer_switch to optimize the optimizer_switch behavior: set global optimizer_seitch = "index_merge = off". This method also has certain risks and affects other SQL statements, exercise caution when operating on the server side. If the index you create is unreasonable, you can create a composite index and delete these Single Column indexes, which improves the SQL efficiency.