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)
