Mysql 的不准确

Mysql Mysql 的不准确

Mysql的统计信息不准确

即使做了表分析，information_schema.tables 中的 DATA_LENGTH,TABLE_ROWS,AVG_ROW_LENGTH,INDEX_LENGTH 也都不是准确值

mysql> select table_schema,table_name,DATA_LENGTH,TABLE_ROWS,AVG_ROW_LENGTH,INDEX_LENGTH from information_schema.tables where table_name='isc_message1';
+--------------+--------------+-------------+------------+----------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | DATA_LENGTH | TABLE_ROWS | AVG_ROW_LENGTH | INDEX_LENGTH |
+--------------+--------------+-------------+------------+----------------+--------------+
| test         | isc_message1 |  2086649856 |    3844654 |            542 |     62488576 |
+--------------+--------------+-------------+------------+----------------+--------------+
1 row in set (0.00 sec)

insert into isc_message1(channel_code,create_time) values('2020030701','20200307');
insert into isc_message1(channel_code,create_time) values('2020030702','20200307');

mysql> select table_schema,table_name,DATA_LENGTH,TABLE_ROWS,AVG_ROW_LENGTH,INDEX_LENGTH from information_schema.tables where table_name='isc_message1';
+--------------+--------------+-------------+------------+----------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | DATA_LENGTH | TABLE_ROWS | AVG_ROW_LENGTH | INDEX_LENGTH |
+--------------+--------------+-------------+------------+----------------+--------------+
| test         | isc_message1 |  2086649856 |    3844654 |            542 |     62488576 |
+--------------+--------------+-------------+------------+----------------+--------------+
1 row in set (0.00 sec)
mysql> analyze table isc_message1;
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| test.isc_message1 | analyze | status   | OK       |
+--------------------------+---------+----------+----------+
1 row in set (0.30 sec)
mysql> select table_schema,table_name,DATA_LENGTH,TABLE_ROWS,AVG_ROW_LENGTH,INDEX_LENGTH from information_schema.tables where table_name='isc_message1';
+--------------+--------------+-------------+------------+----------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | DATA_LENGTH | TABLE_ROWS | AVG_ROW_LENGTH | INDEX_LENGTH |
+--------------+--------------+-------------+------------+----------------+--------------+
| test         | isc_message1 |  2086649856 |    4021903 |            518 |     62488576 | 
+--------------+--------------+-------------+------------+----------------+--------------+
1 row in set (0.00 sec)
mysql> select count(1) from isc_message1;
+----------+
| count(1) |
+----------+
|  3607910 |
+----------+
1 row in set (1.43 sec)
mysql> optimize table  isc_message1;
+-------------------+----------+----------+-------------------------------------------------------------------+
| Table             | Op       | Msg_type | Msg_text                                                          |
+-------------------+----------+----------+-------------------------------------------------------------------+
| test.isc_message1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.isc_message1 | optimize | status   | OK                                                                |
+-------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 min 10.41 sec)
mysql> select table_schema,table_name,DATA_LENGTH,TABLE_ROWS,AVG_ROW_LENGTH,INDEX_LENGTH from information_schema.tables where table_name='isc_message1';
+--------------+--------------+-------------+------------+----------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | DATA_LENGTH | TABLE_ROWS | AVG_ROW_LENGTH | INDEX_LENGTH |
+--------------+--------------+-------------+------------+----------------+--------------+
| test         | isc_message1 |  2086649856 |    4606909 |            452 |     62488576 |
+--------------+--------------+-------------+------------+----------------+--------------+
1 row in set (0.00 sec)

mysql> select 3844654*542,4021903*518,4606909*452;
+-------------+-------------+-------------+
| 3844654*542 | 4021903*518 | 4606909*452 |
+-------------+-------------+-------------+
|  2083802468 |  2083345754 |  2082322868 |
+-------------+-------------+-------------+
1 row in set (0.00 sec)

注意：
1.DATA_LENGTH，INDEX_LENGTH一直没变化，即使收集了统计信息。说明Mysql统计信息收集也不是很准确，毕竟分析表完成的很快（300w行0.3s就完成了分析），也可能数据变化太小。
2.TABLE_ROWS，AVG_ROW_LENGTH 变化比较夸张，但肯定不符合实际情况，而他们的乘积却相对稳定些，上面显示变化在1M 以内

Mysql 执行计划的不靠谱

ID 是主键
create_time 可为空辅助索引

mysql> desc select count(1) from isc_message1;
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key         | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | isc_message1 | NULL       | index | NULL          | create_time | 6       | NULL | 3772852 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>  desc select count(*) from isc_message1;
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key         | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | isc_message1 | NULL       | index | NULL          | create_time | 6       | NULL | 3772852 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select count(id) from isc_message1;
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key         | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | isc_message1 | NULL       | index | NULL          | create_time | 6       | NULL | 3772852 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select count(create_time) from isc_message1;
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key         | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | isc_message1 | NULL       | index | NULL          | create_time | 6       | NULL | 3772852 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc format=json select count(create_time) from isc_message1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "504644.20"
    },
    "table": {
      "table_name": "isc_message1",
      "access_type": "index",
      "key": "create_time",
      "used_key_parts": [
        "create_time"
      ],
      "key_length": "6",
      "rows_examined_per_scan": 3772852,
      "rows_produced_per_join": 3772852,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "127359.00",
        "eval_cost": "377285.20",
        "prefix_cost": "504644.20",
        "data_read_per_join": "101G"
      },
      "used_columns": [
        "create_time"
      ]
    }
  }
} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc format=json select count(1) from isc_message1;           
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "504644.20"
    },
    "table": {
      "table_name": "isc_message1",
      "access_type": "index",
      "key": "create_time",
      "used_key_parts": [
        "create_time"
      ],
      "key_length": "6",
      "rows_examined_per_scan": 3772852,
      "rows_produced_per_join": 3772852,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "127359.00",
        "eval_cost": "377285.20",
        "prefix_cost": "504644.20",
        "data_read_per_join": "101G"
      }
    }
  }
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select count(create_time) from isc_message1;
+--------------------+
| count(create_time) |
+--------------------+
|            3157925 |
+--------------------+
1 row in set (0.47 sec)

mysql> select count(*) from isc_message1;           
+----------+
| count(*) |
+----------+
|  3607910 |
+----------+
1 row in set (0.28 sec)

mysql> select count(id) from isc_message1; 
+-----------+
| count(id) |
+-----------+
|   3607910 |
+-----------+
1 row in set (0.33 sec)

mysql> select count(1) from isc_message1;  
+----------+
| count(1) |
+----------+
|  3607910 |
+----------+
1 row in set (0.28 sec)

mysql> select count(1) from isc_message1;
+----------+
| count(1) |
+----------+
|  3607910 |
+----------+
1 row in set (0.29 sec)

mysql> select count(create_time) from isc_message1; 
+--------------------+
| count(create_time) |
+--------------------+
|            3157925 |
+--------------------+
1 row in set (0.45 sec)

mysql> select count(id) from isc_message1;           
+-----------+
| count(id) |
+-----------+
|   3607910 |
+-----------+
1 row in set (0.34 sec)

注意：
通过以上可知，做count统计时
count(1)和count(*)的效率差不多，都比较高，而且准确
count(主键) 效率稍低，结果准确
count(可为空辅助索引) 效率低，结果不准确
而这几个count的执行计划，都不准确。