MySQL数据库收集表的统计信息

MySQL收集表的统计信息可通过遍历information_schema.tables，例如收集ray数据库中的ray_t表。
1、遍历information_schema.tables表

mysql> select * from information_schema.tables where table_name='ray_t'\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: ray
TABLE_NAME: ray_t
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 327540
AVG_ROW_LENGTH: 36
DATA_LENGTH: 12075008
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 6832128
DATA_FREE: 2097152
AUTO_INCREMENT: NULL
CREATE_TIME: 2022-12-01 16:58:03
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_unicode_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.00 sec)

2、重启MySQL实例
[root@ray1 ~]# mysqladmin -uroot -prootroot shutdown
[root@ray1 ~]# systemctl start mysqld

3、查看表信息
[root@ray1 ~]# mysql -uroot -prootroot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.38-log MySQL Community Server (GPL)

Copyright © 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use ray;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables status like ‘%ray_t%’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘status like ‘%ray_t%’’ at line 1
mysql> show table status like ‘%ray_t%’;
±------±-------±--------±-----------±-------±---------------±------------±----------------±-------------±----------±---------------±--------------------±------------±-----------±-------------------±---------±---------------±--------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
±------±-------±--------±-----------±-------±---------------±------------±----------------±-------------±----------±---------------±--------------------±------------±-----------±-------------------±---------±---------------±--------+
| ray_t | InnoDB | 10 | Dynamic | 262135 | 34 | 8929280 | 0 | 4734976 | 4194304 | NULL | 2022-12-08 14:39:47 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
±------±-------±--------±-----------±-------±---------------±------------±----------------±-------------±----------±---------------±--------------------±------------±-----------±-------------------±---------±---------------±--------+
1 row in set (0.00 sec)