# 查看information_schema数据库中的表
mysql> SHOW TABLES FROM information_schema;
+—————————————+
| Tables_in_information_schema |
+—————————————+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_LOCKS |
| INNODB_LOCK_WAITS |
| INNODB_TRX |
+—————————————+
34 rows in set (0.01 sec)

# 1. 查询数据库列表
mysql> SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;
+——————–+
| SCHEMA_NAME |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| fgedudb |
+——————–+
5 rows in set (0.00 sec)

# 2. 查询表列表
mysql> SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘fgedudb’;
+——————–+
| TABLE_NAME |
+——————–+
| fgedu_users |
| fgedu_orders |
| fgedu_products |
| fgedu_categories |
| fgedu_order_items |
+——————–+
5 rows in set (0.01 sec)

# 3. 查询表结构
mysql> SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ‘fgedudb’ AND TABLE_NAME = ‘fgedu_users’;
+—————-+———–+————————–+————-+—————-+
| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE | COLUMN_DEFAULT |
+—————-+———–+————————–+————-+—————-+
| id | int | NULL | NO | NULL |
| username | varchar | 50 | NO | NULL |
| email | varchar | 100 | NO | NULL |
| password | varchar | 255 | NO | NULL |
| created_at | datetime | NULL | YES | CURRENT_TIMESTAMP |
| updated_at | datetime | NULL | YES | NULL |
+—————-+———–+————————–+————-+—————-+
6 rows in set (0.01 sec)

# 4. 查询索引信息
mysql> SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = ‘fgedudb’ AND TABLE_NAME = ‘fgedu_users’;
+————+—————-+————-+————+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | NON_UNIQUE |
+————+—————-+————-+————+
| fgedu_users| PRIMARY | id | 0 |
| fgedu_users| idx_username | username | 0 |
| fgedu_users| idx_email | email | 0 |
+————+—————-+————-+————+
3 rows in set (0.01 sec)

# 5. 查询表约束
mysql> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = ‘fgedudb’;
+———————–+—————–+——————–+
| CONSTRAINT_NAME | CONSTRAINT_TYPE | TABLE_NAME |
+———————–+—————–+——————–+
| PRIMARY | PRIMARY KEY | fgedu_users |
| PRIMARY | PRIMARY KEY | fgedu_orders |
| PRIMARY | PRIMARY KEY | fgedu_products |
| PRIMARY | PRIMARY KEY | fgedu_categories |
| PRIMARY | PRIMARY KEY | fgedu_order_items |
| fk_order_user_id | FOREIGN KEY | fgedu_orders |
| fk_order_item_order_id| FOREIGN KEY | fgedu_order_items |
| fk_order_item_product_id | FOREIGN KEY | fgedu_order_items |
+———————–+—————–+——————–+
8 rows in set (0.01 sec)

# 1. 更新表统计信息
mysql> ANALYZE TABLE fgedudb.fgedu_users;
+——————-+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+——————-+———+———-+———-+
| fgedudb.fgedu_users| analyze | status | OK |
+——————-+———+———-+———-+
1 row in set (0.01 sec)

# 2. 更新所有表的统计信息
# mysqlcheck -u root -p –analyze fgedudb
Enter password:
fgedudb.fgedu_users OK
fgedudb.fgedu_orders OK
fgedudb.fgedu_products OK
fgedudb.fgedu_categories OK
fgedudb.fgedu_order_items OK

# 3. 查看表统计信息
mysql> SELECT TABLE_NAME, ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ‘fgedudb’;
+——————–+——-+————-+————–+
| TABLE_NAME | ROWS | DATA_LENGTH | INDEX_LENGTH |
+——————–+——-+————-+————–+
| fgedu_users | 10000 | 8388608 | 2097152 |
| fgedu_orders | 50000 | 41943040 | 10485760 |
| fgedu_products | 20000 | 16777216 | 4194304 |
| fgedu_categories | 100 | 16384 | 8192 |
| fgedu_order_items | 100000| 83886080 | 20971520 |
+——————–+——-+————-+————–+
5 rows in set (0.01 sec)

# 4. 查看索引使用情况
mysql> SELECT TABLE_NAME, INDEX_NAME, COUNT(*) as usage_count
FROM information_schema.PROCESSLIST
WHERE INFO LIKE ‘%fgedudb%’ AND INFO LIKE ‘%INDEX%’
GROUP BY TABLE_NAME, INDEX_NAME;
+————+—————-+————-+
| TABLE_NAME | INDEX_NAME | usage_count |
+————+—————-+————-+
| fgedu_users| idx_username | 10 |
| fgedu_orders| idx_order_date | 5 |
+————+—————-+————-+
2 rows in set (0.01 sec)