Hello everyone, meet again, I'm your friend Quanzhanjun.
Before MySQL 8.0, some metadata was usually obtained through the information_schema table, for example, the next auto_increment value of the table was obtained from the tables table, and the related information of the index was obtained from the indexes table.
However, when MySQL 8.0 queries this information, inaccuracy occurs. For example auto_increment,
--at this time test table auto_increment is 204
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=204 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
--from information_schema.tables find out test table auto_increment is 204, at this time tables table information is accurate
mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='test';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 204 |
+----------------+
1 row in set (0.01 sec)
--Will test table auto_increment Modified to 300
mysql> alter table test auto_increment=300;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
--Inquire tables table, found auto_increment still 204; in MySQL8.0 before, at this time tables table auto_increment It should show the latest value of 300
mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='test';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 204 |
+----------------+
1 row in set (0.00 sec)
--Towards test Insert data into table, apply latest auto_increment
mysql> insert into test values();
Query OK, 1 row affected (0.02 sec)
--an examination test The maximum value of the table is indeed 300
mysql> select max(id) from test;
+---------+
| max(id) |
+---------+
| 300 |
+---------+
1 row in set (0.00 sec)
--test After the table insertion operation, query again tables surface, auto_increment The value is still 204
mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='test';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 204 |
+----------------+
1 row in set (0.00 sec)Another example is the update time of a table
copy--from tables table see test The table was last updated in 2018-11-29 09:12:48 mysql> select update_time from information_schema.tables where table_schema='test' and table_name='test'; +---------------------+ | UPDATE_TIME | +---------------------+ | 2018-11-29 09:12:48 | +---------------------+ 1 row in set (0.00 sec) mysql> select sysdate(); +---------------------+ | sysdate() | +---------------------+ | 2018-11-29 09:21:49 | +---------------------+ 1 row in set (0.00 sec) --right test Insert data into the table, then test table update_time should be the current time mysql> insert into test values(); Query OK, 1 row affected (0.09 sec) mysql> select sysdate(); +---------------------+ | sysdate() | +---------------------+ | 2018-11-29 09:22:02 | +---------------------+ 1 row in set (0.00 sec) --but from tables table query to update_time still not updated mysql> select update_time from information_schema.tables where table_schema='test' and table_name='test'; +---------------------+ | UPDATE_TIME | +---------------------+ | 2018-11-29 09:12:48 | +---------------------+ 1 row in set (0.00 sec)
As can be seen from the above example, the tables table of MySQL 8.0 has become unreliable. As mentioned in the previous article, in MySQL 8.0, tables are no longer an engine table, but transformed into a view. Take a closer look at the definition of the tables view
copyselect `cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,if((`tbl`.`type` = 'BASE TABLE'),`tbl`.`engine`,NULL) AS `ENGINE`, if((`tbl`.`type` = 'VIEW'),NULL,10) AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`, internal_table_rows(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`table_rows`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `TABLE_ROWS`, internal_avg_row_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`avg_row_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `AVG_ROW_LENGTH`, internal_data_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `DATA_LENGTH`, internal_max_data_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`max_data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `MAX_DATA_LENGTH`, internal_index_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`index_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `INDEX_LENGTH`, internal_data_free(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_free`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `DATA_FREE`, internal_auto_increment(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`auto_increment`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0),`tbl`.`se_private_data`) AS `AUTO_INCREMENT`, `tbl`.`created` AS `CREATE_TIME`, internal_update_time(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`update_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `UPDATE_TIME`, internal_check_time(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`check_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `CHECK_TIME`, `col`.`name` AS `TABLE_COLLATION`,internal_checksum(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`checksum`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `CHECKSUM`, if((`tbl`.`type` = 'VIEW'),NULL,get_dd_create_options(`tbl`.`options`,if((ifnull(`tbl`.`partition_expression`,'NOT_PART_TBL') = 'NOT_PART_TBL'),0,1))) AS `CREATE_OPTIONS`, internal_get_comment_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`,`tbl`.`comment`) AS `TABLE_COMMENT` from (((((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) left join `mysql`.`collations` `col` on((`tbl`.`collation_id` = `col`.`id`))) left join `mysql`.`tablespaces` `ts` on((`tbl`.`tablespace_id` = `ts`.`id`))) left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`)))) where (can_access_table(`sch`.`name`,`tbl`.`name`) and is_visible_dd_object(`tbl`.`hidden`))
As you can see, the auto_increment and update_time columns are both referenced from the mysql.table_stats table. Then the information of the tables view is inaccurate. The root cause is that the statistics of the table_stats table are not updated in real time.
To solve the problem of outdated statistical information, from past experience, when the proportion of table data updates reaches a certain value, statistical information collection will be triggered. So I tried to insert and update the test table continuously, but the information of the tables view is still inaccurate, which means that the statistics of table_stats are not updated at all.
The ultimate method is of course to use the analyze table command to manually trigger table information collection, and the information in the tables view will be updated to the current accurate state.
But if you always have to manually update the analyze table command to get the real data, what's the point of the existence of the tables table?
Do some research on this.
It turns out that in MySQL 8.0, a lot of changes have been made to the data dictionary. This article will not describe all the knowledge points in detail, and will be described in subsequent articles. For inaccurate situations such as tables view, it is actually related to the data dictionary table and its data cache.
The data dictionary has many related tables, but these tables are not visible. Table data cannot be obtained through select, nor can it be seen through show tables, nor does it appear in the table_name category of information_schema.tables. For example, the data dictionary tables related to the library table (note that this tables and information_schema.tables are not the same thing) belong to the mysql library, but even if they have a keen eye, they cannot make it visible:
copymysql> use mysql; Database changed mysql> show tables like 'table'; Empty set (0.01 sec)
However, most data dictionary tables will have related views to obtain their data. For example, the related view of the tables table is information_schema.tables. Of course, from the definition of information_schema.tables, it is not a one-to-one relationship. It also contains data from other tables.
What is the data dictionary table used for? Remember the files .frm and db.opt? In MySQL 8.0, you will find that these files are gone. The metadata originally recorded in these files is now recorded in the data dictionary table, and there is a separate innodb tablespace in the data dictionary table set. The system file name is mysql.ibd, that is, the metadata is no longer It is directly read and written on files such as .frm, but stored on the storage engine.
To minimize disk IO, MySQL 8.0 adds a dictionary object cache. At the same time, in order to improve the query efficiency of information_schema, the data of the statistics and tables dictionary tables are cached in the dictionary object cache and have a certain retention time. If the retention time does not exceed, even if the instance is restarted, the information in the cache will not be updated. Only When the retention time is exceeded, the latest data will be fetched from the storage engine. At the same time, the dictionary object cache adopts the LRU method to manage the cache space.
So here, the question of the inaccuracy of the information_schema.tables view is solved. The reason is that the statistical information in the dictionary object cache has not been updated, so how to solve it? The dictionary object cache can be updated in real time by setting information_schema_stats_expiry to 0. The default value of this parameter is 86400, which is 24 hours.
The problem is solved, so let’s take a look. Under what circumstances will the statistics of indexes and tables in the dictionary cache not be automatically updated? 1. The statistical information in the cache has not expired; 2.information_schema_stats_expiry is not set to 0; 3. When the instance is running in read_only related mode; 4. When querying, the data of the performance schema is obtained at the same time.
For the first and second points, it can be solved by setting set global information_schema_stats_expiry=0, or it can be set only at the session level; for the above problems, except for the third point, it can be solved by the analyze table.
Copyright statement: The content of this article is contributed by Internet users, and the opinions of this article only represent the author himself. This site only provides information storage space services, does not own ownership, and does not assume relevant legal responsibilities. If you find any content suspected of infringing/violating laws and regulations on this site, please send an email to report. Once verified, this site will be deleted immediately.
Publisher: Full-stack programmer, please indicate the source: https://javaforall.cn/192798.html Original link: https://javaforall.cn