最近RDS FOR MYSQL5.6的统计信息有问题，一些表明明的数据，但统计信息里去显示为空表，导致执行计划出错，查询效率很低，所以查看下相关的信息。
-- 查看服务器系统变量，实际上使用的变量的值 show variables like '%innodb_stats_auto_recalc%'; -- 查看服务器状态变量，运行服务器的统计和状态指标 show status like '%Uptime%'; -- MySQL服务器已经运行的秒数 -- 刷新参数 flush status; -- 查看当前和打开过的表 show global status like 'open%_tables'; -- 清空表缓存 flush tables; -- 重启innodb_stats_auto_recalc set @innodb_stats_auto_recalc=OFF; set @innodb_stats_auto_recalc=ON;
MySQL [database_name]> select count(1) as cnt from database_name.dw_std_yf_roomtype_position; +---------+ | cnt | +---------+ | 1247196 | +---------+ 1 row in set (0.20 sec) MySQL [database_name]> select * from mysql.innodb_table_stats where table_name='dw_std_yf_roomtype_position'; +---------------------+-----------------------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------------+-----------------------------+---------------------+--------+----------------------+--------------------------+ | database_name | dw_std_yf_roomtype_position | 2019-08-01 02:07:24 | 0 | 1 | 3 | +---------------------+-----------------------------+---------------------+--------+----------------------+--------------------------+ 1 row in set (0.09 sec) MySQL [database_name]> select * from information_schema.tables where table_schema = 'database_name' and table_name = 'dw_std_yf_roomtype_position'; +---------------+---------------------+-----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------------------------+--------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | BLOCK_FORMAT | +---------------+---------------------+-----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------------------------+--------------+ | def | database_name | dw_std_yf_roomtype_position | BASE TABLE | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 49152 | 4194304 | NULL | 2018-01-25 16:55:06 | NULL | NULL | utf8_general_ci | NULL | | 801002002017_户型的检查部位 | Original | +---------------+---------------------+-----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------------------------+--------------+ 1 row in set (0.00 sec) MySQL [database_name]> ANALYZE table database_name.dw_std_yf_roomtype_position; +-------------------------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------------------------------------+---------+----------+----------+ | database_name.dw_std_yf_roomtype_position | analyze | status | OK | +-------------------------------------------------+---------+----------+----------+ 1 row in set (0.07 sec) MySQL [database_name]> select * from mysql.innodb_table_stats where table_name='dw_std_yf_roomtype_position'; +---------------------+-----------------------------+---------------------+---------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------------+-----------------------------+---------------------+---------+----------------------+--------------------------+ | database_name | dw_std_yf_roomtype_position | 2019-08-01 10:56:02 | 1157429 | 32640 | 40192 | +---------------------+-----------------------------+---------------------+---------+----------------------+--------------------------+ 1 row in set (0.10 sec) MySQL [database_name]> select * from information_schema.tables where table_schema = 'database_name' and table_name = 'dw_std_yf_roomtype_position'; +---------------+---------------------+-----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------------------------+--------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | BLOCK_FORMAT | +---------------+---------------------+-----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------------------------+--------------+ | def | database_name | dw_std_yf_roomtype_position | BASE TABLE | InnoDB | 10 | Compact | 1157429 | 462 | 534773760 | 0 | 658505728 | 4194304 | NULL | 2018-01-25 16:55:06 | NULL | NULL | utf8_general_ci | NULL | | 801002002017_户型的检查部位 | Original | +---------------+---------------------+-----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------------------------------+--------------+ 1 row in set (0.00 sec) MySQL [database_name]> show index from database_name.dw_std_yf_roomtype_position; +-----------------------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +-----------------------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | dw_std_yf_roomtype_position | 0 | PRIMARY | 1 | id | A | 1157429 | NULL | NULL | | BTREE | | | YES | | dw_std_yf_roomtype_position | 0 | PRIMARY | 2 | db_name | A | 1157429 | NULL | NULL | | BTREE | | | YES | | dw_std_yf_roomtype_position | 1 | ix_position_id | 1 | position_id | A | 52610 | NULL | NULL | YES | BTREE | | | YES | | dw_std_yf_roomtype_position | 1 | ix_tenant_roomtype | 1 | roomtype_id | A | 289357 | NULL | NULL | YES | BTREE | | | YES | | dw_std_yf_roomtype_position | 1 | ix_tenant_roomtype | 2 | db_name | A | 289357 | NULL | NULL | | BTREE | | | YES | | dw_std_yf_roomtype_position | 1 | ix_tenant_position | 1 | db_name | A | 12580 | NULL | NULL | | BTREE | | | YES | | dw_std_yf_roomtype_position | 1 | ix_tenant_position | 2 | position_id | A | 44516 | NULL | NULL | YES | BTREE | | | YES | +-----------------------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ MySQL [database_name]> select * from mysql.innodb_index_stats where database_name='database_name' and table_name='dw_std_yf_roomtype_position'; +---------------------+-----------------------------+--------------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------------+-----------------------------+--------------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | dw_std_yf_roomtype_position | PRIMARY | 2019-08-01 10:56:02 | n_diff_pfx01 | 1095171 | 20 | id | | database_name | dw_std_yf_roomtype_position | PRIMARY | 2019-08-01 10:56:02 | n_diff_pfx02 | 1157429 | 20 | id,db_name | | database_name | dw_std_yf_roomtype_position | PRIMARY | 2019-08-01 10:56:02 | n_leaf_pages | 28299 | NULL | Number of leaf pages in the index | | database_name | dw_std_yf_roomtype_position | PRIMARY | 2019-08-01 10:56:02 | size | 32640 | NULL | Number of pages in the index | | database_name | dw_std_yf_roomtype_position | ix_position_id | 2019-08-01 10:56:02 | n_diff_pfx01 | 25448 | 20 | position_id | | database_name | dw_std_yf_roomtype_position | ix_position_id | 2019-08-01 10:56:02 | n_diff_pfx02 | 1345353 | 20 | position_id,id | | database_name | dw_std_yf_roomtype_position | ix_position_id | 2019-08-01 10:56:02 | n_diff_pfx03 | 1239445 | 20 | position_id,id,db_name | | database_name | dw_std_yf_roomtype_position | ix_position_id | 2019-08-01 10:56:02 | n_leaf_pages | 10486 | NULL | Number of leaf pages in the index | | database_name | dw_std_yf_roomtype_position | ix_position_id | 2019-08-01 10:56:02 | size | 12160 | NULL | Number of pages in the index | | database_name | dw_std_yf_roomtype_position | ix_tenant_position | 2019-08-01 10:56:02 | n_diff_pfx01 | 6263 | 20 | db_name | | database_name | dw_std_yf_roomtype_position | ix_tenant_position | 2019-08-01 10:56:02 | n_diff_pfx02 | 21960 | 20 | db_name,position_id | | database_name | dw_std_yf_roomtype_position | ix_tenant_position | 2019-08-01 10:56:02 | n_diff_pfx03 | 1369816 | 20 | db_name,position_id,id | | database_name | dw_std_yf_roomtype_position | ix_tenant_position | 2019-08-01 10:56:02 | n_leaf_pages | 10311 | NULL | Number of leaf pages in the index | | database_name | dw_std_yf_roomtype_position | ix_tenant_position | 2019-08-01 10:56:02 | size | 11968 | NULL | Number of pages in the index | | database_name | dw_std_yf_roomtype_position | ix_tenant_roomtype | 2019-08-01 10:56:02 | n_diff_pfx01 | 131679 | 20 | roomtype_id | | database_name | dw_std_yf_roomtype_position | ix_tenant_roomtype | 2019-08-01 10:56:02 | n_diff_pfx02 | 125442 | 20 | roomtype_id,db_name | | database_name | dw_std_yf_roomtype_position | ix_tenant_roomtype | 2019-08-01 10:56:02 | n_diff_pfx03 | 1591935 | 20 | roomtype_id,db_name,id | | database_name | dw_std_yf_roomtype_position | ix_tenant_roomtype | 2019-08-01 10:56:02 | n_leaf_pages | 13861 | NULL | Number of leaf pages in the index | | database_name | dw_std_yf_roomtype_position | ix_tenant_roomtype | 2019-08-01 10:56:02 | size | 16064 | NULL | Number of pages in the index | +---------------------+-----------------------------+--------------------+---------------------+--------------+------------+-------------+-----------------------------------+
ref: https://www.cnblogs.com/zengkefu/p/5634858.html
ref: https://www.cnblogs.com/justfortaste/p/3276363.html