Optimization
Mariadb 從 10.2 升級到 10.4 後查詢時間大大增加
從 Mariadb 10.2 升級到 10.4 後，查詢時間大大增加。
在 10.2 上查詢時間為 0.5s。
在 10.4 上查詢時間是 14s。查詢計劃也發生了變化。
10.2:
-------------------------------+---------------------------------+---------+---------------------------------+------+---------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+-------------+-------------+--------------------------------------------------+---------------------------------+---------+---------------------------------+------+---------------------------------------------------------------+ | 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 6938 | Using temporary; Using filesort | | 1 | PRIMARY | a4 | eq_ref | PRIMARY,category_id_idx | PRIMARY | 8 | production.ptpv.product_id,const | 1 | Using index | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | production.ptpv.product_id | 1 | Using where | | 1 | PRIMARY | a2 | ref | PRIMARY,category_id_idx | PRIMARY | 4 | production.ptpv.product_id | 1 | Using index | | 1 | PRIMARY | t | ref | product_option_value_id_idx,sup_id_idx,is_active | product_option_value_id_idx | 5 | production.ptpv.product_id | 6 | Using where | | 1 | PRIMARY | t2 | eq_ref | PRIMARY,tbl_sup_type_idx | PRIMARY | 8 | production.t.sup_id | 1 | Using where | | 1 | PRIMARY | a3 | eq_ref | PRIMARY | PRIMARY | 4 | production.a2.category_id | 1 | | | 2 | MATERIALIZED | ptpv | index_merge | param_id_idx,param_value_id_idx,product_id_idx | param_id_idx,param_value_id_idx | 152,606 | NULL | 9250 | Using intersect(param_id_idx,param_value_id_idx); Using where |10.4
+------+--------------+-------------+-------------+--------------------------------------------------+---------------------------------+---------+---------------------------------+------+---------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+-------------+-------------+--------------------------------------------------+---------------------------------+---------+---------------------------------+------+---------------------------------------------------------------+ | 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 8404 | Using temporary; Using filesort | | 1 | PRIMARY | t | ref | product_option_value_id_idx,sup_id_idx,is_active | product_option_value_id_idx | 5 | production.ptpv.product_id | 11 | Using where | | 1 | PRIMARY | a4 | eq_ref | PRIMARY,category_id_idx | PRIMARY | 8 | production.ptpv.product_id,const | 1 | Using index | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | production.ptpv.product_id | 1 | Using where | | 1 | PRIMARY | a2 | ref | PRIMARY,category_id_idx | PRIMARY | 4 | production.ptpv.product_id | 1 | Using index | | 1 | PRIMARY | t2 | ALL | PRIMARY,tbl_sup_type_idx | NULL | NULL | NULL | 8978 | Using where; Using join buffer (flat, BNL join) | | 1 | PRIMARY | a3 | eq_ref | PRIMARY | PRIMARY | 4 | production.a2.category_id | 1 | | | 2 | MATERIALIZED | ptpv | index_merge | param_id_idx,param_value_id_idx,product_id_idx | param_id_idx,param_value_id_idx | 152,606 | NULL | 8404 | Using intersect(param_id_idx,param_value_id_idx); Using where | +------+--------------+-------------+-------------+--------------------------------------------------+---------------------------------+---------+---------------------------------+------+---------------------------------------------------------------+是否可以返回到以前的查詢計劃？
optimizer_switch 在兩台伺服器上都有預設值。
幾乎總是
index merge intersect可以通過使用複合索引來改進。添加索引可以在不處理 ORM 的情況下完成。