mysql主從庫執行計劃不同

SQL:

SELECT COUNT(l.`id_xx_cluster`) FROM app_xx_xx l where 1=1 AND l.id_xx_cluster IN (SELECT a.applicationName FROM axxxx_info a WHERE a.`axxxxStatus`!='02' GROUP BY a.applicationName);

主庫執行計劃：

+----+--------------+-------------+------------+--------+----------------------------------------------------------+---------------+---------+------------------------------------+---------+----------+--------------------------+
| id | select_type  | table	 | partitions | type   | possible_keys							  | key	     | key_len | ref					  | rows    | filtered | Extra			  |
+----+--------------+-------------+------------+--------+----------------------------------------------------------+---------------+---------+------------------------------------+---------+----------+--------------------------+
|  1 | SIMPLE	 | l	     | NULL	 | index  | PRIMARY								  | cluster_index | 603     | NULL					 |   47577 |   100.00 | Using where; Using index |
|  1 | SIMPLE	 | <subquery2> | NULL	 | eq_ref | <auto_key>							     | <auto_key>    | 603     | pinpoint_web_mysql.l.id_mw_cluster |	 1 |   100.00 | NULL			   |
|  2 | MATERIALIZED | a	     | NULL	 | ALL    | app_axxxxid_index,inx_axxxx_status,inx_app_name_axxxx_id | NULL	    | NULL    | NULL					 | 1748342 |    50.00 | Using where		  |
+----+--------------+-------------+------------+--------+----------------------------------------------------------+---------------+---------+------------------------------------+---------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

從庫執行計劃：

+----+-------------+-------+------------+-------+----------------------------------------------------------+-------------------+---------+------------------------------------+-------+----------+----------------------------+
| id | select_type | table | partitions | type  | possible_keys							  | key		   | key_len | ref					  | rows  | filtered | Extra			    |
+----+-------------+-------+------------+-------+----------------------------------------------------------+-------------------+---------+------------------------------------+-------+----------+----------------------------+
|  1 | SIMPLE	| l     | NULL	 | index | PRIMARY								  | cluster_index     | 603     | NULL					 | 44734 |   100.00 | Using index		    |
|  1 | SIMPLE	| a     | NULL	 | ref   | app_axxxxid_index,inx_axxxx_status,inx_app_name_axxxx_id | app_axxxxid_index | 603     | pinpoint_web_mysql.l.id_xx_cluster |    15 |    50.00 | Using where; FirstMatch(l) |
+----+-------------+-------+------------+-------+----------------------------------------------------------+-------------------+---------+------------------------------------+-------+----------+----------------------------+
2 rows in set, 1 warning (0.00 sec)

透過trace optimizer發現對a 表的app_axxxxid_index索引的cost值評估不同，從而導致在選擇半連線方式時選擇不同的方式，主庫透過Materlalizedlookup,而從庫選擇了FirstMatch。前者的效率差一點。

                     主     "access_type": "ref",
                          "index": "app_axxxxid_index",
                          "rows": 43.863,
                          "cost": 2.
                          5e6,
                          "chosen": true
						  
						  
                     從   "access_type": "ref",
                          "index": "app_axxxxid_index",
                          "rows": 15.94,
                          "cost": 855670,
                          "chosen": true						  						

透過收集統計資訊後，主庫還是無法選擇更優執行計劃，其實，這裡主庫應該是預期行為，因為 app_axxxxid_index索引的applicationName基數其實並不高，而從庫中的基數反而更高。

透過手動修改索引的統計資訊能夠干預其執行計劃選擇：

root@myxxxxxxx 15:00:43 [dxxxxxxbu]> show indexes from dxxxxxxbu.axxxx_info;
+------------+------------+-----------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name              | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-----------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| axxxx_info |          0 | PRIMARY               |            1 | id                  | A         |     1501312 |     NULL | NULL   |      | BTREE      |         |               |
...
| axxxx_info |          1 | app_axxxxid_index     |            1 | applicationName     | A         |       23760 |     NULL | NULL   | YES  | BTREE      |         |               |
| axxxx_info |          1 | app_axxxxid_index     |            2 | axxxxId             | A         |     1501526 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-----------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
12 rows in set (0.00 sec)
root@myxxxxxxx 15:07:17 [dxxxxxxbu]> update mysql.innodb_index_stats set stat_value=100000 where stat_description='applicationName' and table_name='axxxx_info' and database_name='dxxxxxxbu' and index_name='app_axxxxid_index';  
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
root@myxxxxxxx 15:07:18 [dxxxxxxbu]> select * from mysql.innodb_index_stats where stat_description='applicationName' and table_name='axxxx_info' and database_name='dxxxxxxbu' and index_name='app_axxxxid_index';  
+---------------+------------+-------------------+---------------------+--------------+------------+-------------+------------------+
| database_name | table_name | index_name        | last_update         | stat_name    | stat_value | sample_size | stat_description |
+---------------+------------+-------------------+---------------------+--------------+------------+-------------+------------------+
| dxxxxxxbu    | axxxx_info | app_axxxxid_index | 2023-11-13 15:07:18 | n_diff_pfx01 |     100000 |          20 | applicationName  |
+---------------+------------+-------------------+---------------------+--------------+------------+-------------+------------------+
1 row in set (0.00 sec)
root@myxxxxxxx 15:08:32 [dxxxxxxbu]> flush table dxxxxxxbu.axxxx_info;
Query OK, 0 rows affected (0.01 sec)
root@myxxxxxxx 15:12:53 [dxxxxxxbu]> explain SELECT COUNT(l.`id_xx_cluster`)FROM app_xx_xx l where 1=1 AND l.id_xx_cluster IN (SELECT a.applicationName FROM axxxx_info a WHERE a.`axxxxStatus`!='02' GROUP BY a.applicationName);
+----+-------------+-------+------------+-------+-------------------------------------------------+-------------------+---------+----------------------------+-------+----------+----------------------------+
| id | select_type | table | partitions | type  | possible_keys                                   | key               | key_len | ref                        | rows  | filtered | Extra                      |
+----+-------------+-------+------------+-------+-------------------------------------------------+-------------------+---------+----------------------------+-------+----------+----------------------------+
|  1 | SIMPLE      | l     | NULL       | index | PRIMARY                                         | PRIMARY           | 602     | NULL                       | 19491 |   100.00 | Using index                |
|  1 | SIMPLE      | a     | NULL       | ref   | inx_axxxx_status,inx_app_name,app_axxxxid_index | app_axxxxid_index | 603     | dxxxxxxbu.l.id_xx_cluster |    14 |    21.84 | Using where; FirstMatch(l) |
+----+-------------+-------+------------+-------+-------------------------------------------------+-------------------+---------+----------------------------+-------+----------+----------------------------+
2 rows in set, 1 warning (0.01 sec)
root@myxxxxxxx 15:12:57 [dxxxxxxbu]> analyze table dxxxxxxbu.axxxx_info;
+-----------------------+---------+----------+----------+
| Table                 | Op      | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| dxxxxxxbu.axxxx_info | analyze | status   | OK       |
+-----------------------+---------+----------+----------+
1 row in set (0.04 sec)
root@myxxxxxxx 15:22:25 [dxxxxxxbu]> show indexes from dxxxxxxbu.axxxx_info;
+------------+------------+-----------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name              | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-----------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| axxxx_info |          0 | PRIMARY               |            1 | id                  | A         |     1453728 |     NULL | NULL   |      | BTREE      |         |               |
....
| axxxx_info |          1 | app_axxxxid_index     |            1 | applicationName     | A         |       33572 |     NULL | NULL   | YES  | BTREE      |         |               |
| axxxx_info |          1 | app_axxxxid_index     |            2 | axxxxId             | A         |     1453730 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-----------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
12 rows in set (0.00 sec)
root@myxxxxxxx 15:22:33 [dxxxxxxbu]> select * from mysql.innodb_index_stats where stat_description='applicationName' and table_name='axxxx_info' and database_name='dxxxxxxbu' and index_name='app_axxxxid_index'; 
+---------------+------------+-------------------+---------------------+--------------+------------+-------------+------------------+
| database_name | table_name | index_name        | last_update         | stat_name    | stat_value | sample_size | stat_description |
+---------------+------------+-------------------+---------------------+--------------+------------+-------------+------------------+
| dxxxxxxbu    | axxxx_info | app_axxxxid_index | 2023-11-13 15:22:25 | n_diff_pfx01 |      33572 |          20 | applicationName  |
+---------------+------------+-------------------+---------------------+--------------+------------+-------------+------------------+
1 row in set (0.00 sec)
root@myxxxxxxx 15:22:51 [dxxxxxxbu]>  explain SELECT COUNT(l.`id_xx_cluster`)FROM app_xx_xx l where 1=1 AND l.id_xx_cluster IN (SELECT a.applicationName FROM axxxx_info a WHERE a.`axxxxStatus`!='02' GROUP BY a.applicationName);
+----+--------------+-------------+------------+--------+-------------------------------------------------+------------+---------+----------------------------+---------+----------+--------------------------+
| id | select_type  | table       | partitions | type   | possible_keys                                   | key        | key_len | ref                        | rows    | filtered | Extra                    |
+----+--------------+-------------+------------+--------+-------------------------------------------------+------------+---------+----------------------------+---------+----------+--------------------------+
|  1 | SIMPLE       | l           | NULL       | index  | PRIMARY                                         | PRIMARY    | 602     | NULL                       |   19492 |   100.00 | Using where; Using index |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>                                      | <auto_key> | 603     | dxxxxxxbu.l.id_xx_cluster |       1 |   100.00 | NULL                     |
|  2 | MATERIALIZED | a           | NULL       | ALL    | inx_axxxx_status,inx_app_name,app_axxxxid_index | NULL       | NULL    | NULL                       | 1453736 |    22.22 | Using where              |
+----+--------------+-------------+------------+--------+-------------------------------------------------+------------+---------+----------------------------+---------+----------+--------------------------+
3 rows in set, 1 warning (0.01 sec)