内容简介：INFORMATION_SCHEMA是MySQL内置的系统数据库，包含了大量的元数据表，用于存储数据库的结构信息。本文风哥教程参考MySQL官方文档MySQL Server Administration部分，详细介绍INFORMATION_SCHEMA表的分类、用途和使用方法，帮助DBA高效查询和管理数据库元数据。学习交流加群风哥QQ113257174
Part01-基础概念与理论知识
1.1 INFORMATION_SCHEMA概述
INFORMATION_SCHEMA是MySQL提供的一个只读数据库，用于存储关于MySQL服务器的元数据信息。它是SQL标准的一部分，提供了一个统一的方式来查询数据库的结构和状态信息。
1.2 INFORMATION_SCHEMA的作用
INFORMATION_SCHEMA的主要作用包括：
- 元数据查询：查询数据库、表、列、索引等结构信息
- 性能分析：查看表统计信息、索引使用情况等
- 权限管理：查询用户权限、角色等信息
- 系统状态：查看服务器状态变量、系统变量等
- 迁移支持：获取数据库结构定义，用于数据迁移
Part02-生产环境规划与建议
2.1 INFORMATION_SCHEMA表分类
INFORMATION_SCHEMA表可以分为以下几类：
- 数据库和表信息：SCHEMATA、TABLES、VIEWS等
- 列和索引信息：COLUMNS、STATISTICS、KEY_COLUMN_USAGE等
- 约束信息：TABLE_CONSTRAINTS、REFERENTIAL_CONSTRAINTS等
- 权限信息：USER_PRIVILEGES、TABLE_PRIVILEGES等
- 性能和状态：GLOBAL_STATUS、GLOBAL_VARIABLES等
- 存储引擎信息：ENGINES、FILES等
- 进程和锁信息：PROCESSLIST、INNODB_LOCKS等
2.2 INFORMATION_SCHEMA性能优化
查询INFORMATION_SCHEMA表时的性能优化建议：
- 使用WHERE条件：始终在查询中使用WHERE条件过滤，避免全表扫描
- 限制返回列：只查询需要的列，减少数据传输量
- 使用LIMIT：当只需要部分结果时，使用LIMIT限制返回行数
- 避免在高峰期查询：INFORMATION_SCHEMA查询可能影响服务器性能，应避免在业务高峰期执行
- 使用缓存：对于频繁查询的元数据，可以考虑使用缓存机制
Part03-生产环境项目实施方案
3.1 数据库和表信息表
查询数据库和表的基本信息。
mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA;
+——————–+—————————-+————————+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+——————–+—————————-+————————+
| information_schema | utf8mb3 | utf8mb3_general_ci |
| mysql | utf8mb4 | utf8mb4_0900_ai_ci |
| performance_schema | utf8mb4 | utf8mb4_0900_ai_ci |
| sys | utf8mb4 | utf8mb4_0900_ai_ci |
| fgedudb | utf8mb4 | utf8mb4_0900_ai_ci |
+——————–+—————————-+————————+
5 rows in set (0.01 sec)
# 2. 查询指定数据库的所有表
mysql> SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, DATA_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ‘fgedudb’;
+——————–+————+——–+————+————-+
| TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_ROWS | DATA_LENGTH |
+——————–+————+——–+————+————-+
| fgedu_users | BASE TABLE | InnoDB | 10000 | 8388608 |
| fgedu_orders | BASE TABLE | InnoDB | 50000 | 41943040 |
| fgedu_products | BASE TABLE | InnoDB | 20000 | 16777216 |
| fgedu_categories | BASE TABLE | InnoDB | 100 | 16384 |
| fgedu_order_items | BASE TABLE | InnoDB | 100000 | 83886080 |
| fgedu_logs | BASE TABLE | MyISAM | 200000 | 10485760 |
+——————–+————+——–+————+————-+
6 rows in set (0.01 sec)
# 3. 查询数据库中的视图
mysql> SELECT TABLE_NAME, VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = ‘fgedudb’;
+——————+———————————————————————–+
| TABLE_NAME | VIEW_DEFINITION |
+——————+———————————————————————–+
| v_order_summary | SELECT `fgedudb`.`fgedu_orders`.`id` AS `order_id`,
`fgedudb`.`fgedu_users`.`username` AS `username`,
COUNT(`fgedudb`.`fgedu_order_items`.`id`) AS `item_count`,
SUM(`fgedudb`.`fgedu_order_items`.`quantity` *
`fgedudb`.`fgedu_order_items`.`price`) AS `total_amount`
FROM (((`fgedudb`.`fgedu_orders` JOIN `fgedudb`.`fgedu_users`
ON ((`fgedudb`.`fgedu_orders`.`user_id` = `fgedudb`.`fgedu_users`.`id`)))
JOIN `fgedudb`.`fgedu_order_items`
ON ((`fgedudb`.`fgedu_orders`.`id` = `fgedudb`.`fgedu_order_items`.`order_id`))))
GROUP BY `fgedudb`.`fgedu_orders`.`id`, `fgedudb`.`fgedu_users`.`username` |
+——————+———————————————————————–+
1 row in set (0.01 sec)
3.2 列和索引信息表
查询表的列和索引信息。
mysql> SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ‘fgedudb’ AND TABLE_NAME = ‘fgedu_users’;
+—————-+———–+————————–+————-+—————-+—————-+
| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE | COLUMN_DEFAULT | COLUMN_COMMENT |
+—————-+———–+————————–+————-+—————-+—————-+
| id | int | NULL | NO | NULL | 用户ID |
| username | varchar | 50 | NO | NULL | 用户名 |
| email | varchar | 100 | NO | NULL | 邮箱 |
| password | varchar | 255 | NO | NULL | 密码 |
| full_name | varchar | 100 | YES | NULL | 姓名 |
| created_at | datetime | NULL | YES | CURRENT_TIMESTAMP | 创建时间 |
| updated_at | datetime | NULL | YES | NULL | 更新时间 |
| status | tinyint | NULL | YES | 1 | 状态 |
+—————-+———–+————————–+————-+—————-+—————-+
8 rows in set (0.01 sec)
# 2. 查询表的索引信息
mysql> SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX, COLUMN_NAME,
CARDINALITY, SUB_PART, INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = ‘fgedudb’ AND TABLE_NAME = ‘fgedu_users’;
+————+—————-+————+————–+————-+————-+———-+————+
| TABLE_NAME | INDEX_NAME | NON_UNIQUE | SEQ_IN_INDEX | COLUMN_NAME | CARDINALITY | SUB_PART | INDEX_TYPE |
+————+—————-+————+————–+————-+————-+———-+————+
| fgedu_users| PRIMARY | 0 | 1 | id | 10000 | NULL | BTREE |
| fgedu_users| idx_username | 0 | 1 | username | 10000 | NULL | BTREE |
| fgedu_users| idx_email | 0 | 1 | email | 10000 | NULL | BTREE |
| fgedu_users| idx_created_at | 1 | 1 | created_at | 5000 | NULL | BTREE |
| fgedu_users| idx_status | 1 | 1 | status | 2 | NULL | BTREE |
+————+—————-+————+————–+————-+————-+———-+————+
5 rows in set (0.01 sec)
# 3. 查询表的外键信息
mysql> SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = ‘fgedudb’ AND
REFERENCED_TABLE_NAME IS NOT NULL AND
TABLE_NAME = ‘fgedu_orders’;
+————+————-+——————-+————————+
| TABLE_NAME | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+————+————-+——————-+————————+
| fgedu_orders| user_id | fgedu_users | id |
+————+————-+——————-+————————+
1 row in set (0.01 sec)
3.3 约束和权限信息表
查询表的约束和用户权限信息。
mysql> SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = ‘fgedudb’;
+——————–+———————–+—————–+
| TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE |
+——————–+———————–+—————–+
| fgedu_categories | PRIMARY | PRIMARY KEY |
| fgedu_order_items | PRIMARY | PRIMARY KEY |
| fgedu_order_items | fk_order_item_order_id| FOREIGN KEY |
| fgedu_order_items | fk_order_item_product_id | FOREIGN KEY |
| fgedu_orders | PRIMARY | PRIMARY KEY |
| fgedu_orders | fk_order_user_id | FOREIGN KEY |
| fgedu_products | PRIMARY | PRIMARY KEY |
| fgedu_products | fk_product_category_id | FOREIGN KEY |
| fgedu_users | PRIMARY | PRIMARY KEY |
+——————–+———————–+—————–+
9 rows in set (0.01 sec)
# 2. 查询外键约束详情
mysql> SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME,
DELETE_RULE, UPDATE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = ‘fgedudb’;
+———————–+——————–+——————-+————-+————-+
| CONSTRAINT_NAME | TABLE_NAME | REFERENCED_TABLE_NAME | DELETE_RULE | UPDATE_RULE |
+———————–+——————–+——————-+————-+————-+
| fk_order_item_order_id| fgedu_order_items | fgedu_orders | CASCADE | CASCADE |
| fk_order_item_product_id | fgedu_order_items| fgedu_products | CASCADE | CASCADE |
| fk_order_user_id | fgedu_orders | fgedu_users | RESTRICT | RESTRICT |
| fk_product_category_id | fgedu_products | fgedu_categories | RESTRICT | RESTRICT |
+———————–+——————–+——————-+————-+————-+
4 rows in set (0.01 sec)
# 3. 查询用户权限信息
mysql> SELECT GRANTEE, TABLE_CATALOG, PRIVILEGE_TYPE, IS_GRANTABLE
FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE LIKE ‘%root%’;
+——————+—————+————————-+————–+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+——————+—————+————————-+————–+
| ‘root’@’localhost’ | def | SELECT | YES |
| ‘root’@’localhost’ | def | INSERT | YES |
| ‘root’@’localhost’ | def | UPDATE | YES |
| ‘root’@’localhost’ | def | DELETE | YES |
| ‘root’@’localhost’ | def | CREATE | YES |
| ‘root’@’localhost’ | def | DROP | YES |
| ‘root’@’localhost’ | def | RELOAD | YES |
| ‘root’@’localhost’ | def | SHUTDOWN | YES |
| ‘root’@’localhost’ | def | PROCESS | YES |
| ‘root’@’localhost’ | def | FILE | YES |
| ‘root’@’localhost’ | def | REFERENCES | YES |
| ‘root’@’localhost’ | def | INDEX | YES |
| ‘root’@’localhost’ | def | ALTER | YES |
| ‘root’@’localhost’ | def | SHOW DATABASES | YES |
| ‘root’@’localhost’ | def | SUPER | YES |
| ‘root’@’localhost’ | def | CREATE TEMPORARY TABLES | YES |
| ‘root’@’localhost’ | def | LOCK TABLES | YES |
| ‘root’@’localhost’ | def | EXECUTE | YES |
| ‘root’@’localhost’ | def | REPLICATION SLAVE | YES |
| ‘root’@’localhost’ | def | REPLICATION CLIENT | YES |
| ‘root’@’localhost’ | def | CREATE VIEW | YES |
| ‘root’@’localhost’ | def | SHOW VIEW | YES |
| ‘root’@’localhost’ | def | CREATE ROUTINE | YES |
| ‘root’@’localhost’ | def | ALTER ROUTINE | YES |
| ‘root’@’localhost’ | def | CREATE USER | YES |
| ‘root’@’localhost’ | def | EVENT | YES |
| ‘root’@’localhost’ | def | TRIGGER | YES |
| ‘root’@’localhost’ | def | CREATE TABLESPACE | YES |
| ‘root’@’localhost’ | def | CREATE ROLE | YES |
| ‘root’@’localhost’ | def | DROP ROLE | YES |
+——————+—————+————————-+————–+
30 rows in set (0.01 sec)
3.4 性能和状态信息表
查询服务器性能和状态信息。
mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME IN (‘Uptime’, ‘Connections’, ‘Slow_queries’, ‘Queries’);
+—————+—————-+——————-+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_SOURCE |
+—————+—————-+——————-+
| Connections | 1000 | global |
| Queries | 50000 | global |
| Slow_queries | 10 | global |
| Uptime | 36000 | global |
+—————+—————-+——————-+
4 rows in set (0.01 sec)
# 2. 查询全局系统变量
mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.GLOBAL_VARIABLES
WHERE VARIABLE_NAME IN (‘max_connections’, ‘innodb_buffer_pool_size’,
‘slow_query_log’, ‘long_query_time’);
+———————-+—————-+——————-+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_SOURCE |
+———————-+—————-+——————-+
| innodb_buffer_pool_size | 1073741824 | my.cnf |
| long_query_time | 1.000000 | my.cnf |
| max_connections | 1000 | my.cnf |
| slow_query_log | ON | my.cnf |
+———————-+—————-+——————-+
4 rows in set (0.01 sec)
# 3. 查询存储引擎信息
mysql> SELECT ENGINE, SUPPORT, COMMENT, TRANSACTIONS, XA, SAVEPOINTS
FROM information_schema.ENGINES;
+——————–+———+—————————————————————-+————–+——+————+
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+——————–+———+—————————————————————-+————–+——+————+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+——————–+———+—————————————————————-+————–+——+————+
9 rows in set (0.01 sec)
Part04-生产案例与实战讲解
4.1 数据库文档生成
使用INFORMATION_SCHEMA生成数据库文档的实战案例。
问题描述：需要定期生成数据库表结构文档，便于开发和维护
解决方法：使用INFORMATION_SCHEMA查询表结构信息，生成HTML格式文档
# generate_db_doc.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
DB_NAME=”fgedudb”
DB_USER=”root”
DB_PASS=”password”
OUTPUT_FILE=”db_documentation.html”
# 生成HTML头部
cat > $OUTPUT_FILE << EOF
数据库结构文档 – $DB_NAME
生成时间: $(date ‘+%Y-%m-%d %H:%M:%S’)
EOF
# 获取所有表
TABLES=$(mysql -u $DB_USER -p”$DB_PASS” -e “SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘$DB_NAME'” | tail -n +2)
# 循环处理每个表
while read -r TABLE COMMENT; do
# 生成表标题
echo ”
$TABLE – $COMMENT
” >> $OUTPUT_FILE
# 获取表结构
mysql -u $DB_USER -p”$DB_PASS” -e “SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ‘$DB_NAME’ AND TABLE_NAME = ‘$TABLE’ ORDER BY ORDINAL_POSITION” > temp_table.txt
# 生成表结构HTML
echo ”
| 字段名 | 数据类型 | 长度 | 是否可为空 | 默认值 | 描述 |
|---|---|---|---|---|---|
| $COLUMN | $DATA_TYPE | $LENGTH | $NULLABLE | $DEFAULT | $COMMENT |
” >> $OUTPUT_FILE
# 获取索引信息
echo ”
$TABLE – 索引信息
” >> $OUTPUT_FILE
mysql -u $DB_USER -p”$DB_PASS” -e “SELECT INDEX_NAME, NON_UNIQUE, COLUMN_NAME, CARDINALITY FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = ‘$DB_NAME’ AND TABLE_NAME = ‘$TABLE’ ORDER BY INDEX_NAME, SEQ_IN_INDEX” > temp_index.txt
# 生成索引HTML
echo ”
| 索引名 | 是否唯一 | 字段名 | 基数 |
|---|---|---|---|
| $INDEX_NAME | $UNIQUE | $COLUMN_NAME | $CARDINALITY |
” >> $OUTPUT_FILE
done <<< "$TABLES" # 生成HTML尾部 cat >> $OUTPUT_FILE << EOF
EOF
# 清理临时文件
rm -f temp_table.txt temp_index.txt
echo “数据库文档已生成：$OUTPUT_FILE”
# chmod +x generate_db_doc.sh
# ./generate_db_doc.sh
数据库文档已生成：db_documentation.html
# 查看生成的文档
# ls -l db_documentation.html
-rw-r–r– 1 root root 123456 Apr 2 19:00 db_documentation.html
4.2 索引优化分析
使用INFORMATION_SCHEMA进行索引优化分析的实战案例。
问题描述：数据库查询性能下降，需要分析索引使用情况
解决方法：结合INFORMATION_SCHEMA和Performance Schema查询索引使用情况
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = ‘YES’ WHERE NAME LIKE ‘wait/io/file/innodb/innodb_data_file’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = ‘YES’ WHERE NAME LIKE ‘events_waits_current’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 2. 查询索引使用情况
SELECT
i.TABLE_NAME,
i.INDEX_NAME,
i.COLUMN_NAME,
i.CARDINALITY,
t.TABLE_ROWS,
ROUND((i.CARDINALITY / t.TABLE_ROWS) * 100, 2) AS SELECTIVITY,
i.NON_UNIQUE,
p.COUNT_FETCH,
p.COUNT_INSERT,
p.COUNT_UPDATE,
p.COUNT_DELETE
FROM
information_schema.STATISTICS i
JOIN
information_schema.TABLES t ON i.TABLE_SCHEMA = t.TABLE_SCHEMA AND i.TABLE_NAME = t.TABLE_NAME
LEFT JOIN
performance_schema.table_io_waits_summary_by_index_usage p ON
i.TABLE_SCHEMA = p.OBJECT_SCHEMA AND
i.TABLE_NAME = p.OBJECT_NAME AND
i.INDEX_NAME = p.INDEX_NAME
WHERE
i.TABLE_SCHEMA = ‘fgedudb’
ORDER BY
i.TABLE_NAME, i.INDEX_NAME;
# 输出示例（部分）
+——————–+————————+————-+————-+————+————-+————+————-+————–+————–+————–+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | TABLE_ROWS | SELECTIVITY | NON_UNIQUE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+——————–+————————+————-+————-+————+————-+————+————-+————–+————–+————–+
| fgedu_orders | PRIMARY | id | 50000 | 50000 | 100.0 | 0 | 2000 | 1000 | 300 | 100 |
| fgedu_orders | fk_order_user_id | user_id | 10000 | 50000 | 20.0 | 1 | 1500 | 0 | 0 | 0 |
| fgedu_orders | idx_order_date | order_date | 5000 | 50000 | 10.0 | 1 | 800 | 0 | 0 | 0 |
| fgedu_orders | idx_status | status | 5 | 50000 | 0.01| 1 | 0 | 0 | 0 | 0 |
| fgedu_users | PRIMARY | id | 10000 | 10000 | 100.0 | 0 | 1000 | 500 | 200 | 50 |
| fgedu_users | idx_username | username | 10000 | 10000 | 100.0 | 0 | 800 | 0 | 0 | 0 |
| fgedu_users | idx_email | email | 10000 | 10000 | 100.0 | 0 | 500 | 0 | 0 | 0 |
| fgedu_users | idx_created_at | created_at | 5000 | 10000 | 50.0 | 1 | 0 | 0 | 0 | 0 |
+——————–+————————+————-+————-+————+————-+————+————-+————–+————–+————–+
# 3. 找出选择性低的索引（需要优化）
SELECT
TABLE_NAME,
INDEX_NAME,
COLUMN_NAME,
SELECTIVITY
FROM (
SELECT
i.TABLE_NAME,
i.INDEX_NAME,
i.COLUMN_NAME,
ROUND((i.CARDINALITY / t.TABLE_ROWS) * 100, 2) AS SELECTIVITY
FROM
information_schema.STATISTICS i
JOIN
information_schema.TABLES t ON i.TABLE_SCHEMA = t.TABLE_SCHEMA AND i.TABLE_NAME = t.TABLE_NAME
WHERE
i.TABLE_SCHEMA = ‘fgedudb’ AND
i.INDEX_NAME != ‘PRIMARY’
) AS index_stats
WHERE
SELECTIVITY < 10;
# 输出示例
+------------+------------+-------------+-------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | SELECTIVITY |
+------------+------------+-------------+-------------+
| fgedu_orders| idx_status | status | 0.01|
+------------+------------+-------------+
# 4. 找出未使用的索引
SELECT
i.TABLE_NAME,
i.INDEX_NAME,
i.COLUMN_NAME
FROM
information_schema.STATISTICS i
LEFT JOIN
performance_schema.table_io_waits_summary_by_index_usage p ON
i.TABLE_SCHEMA = p.OBJECT_SCHEMA AND
i.TABLE_NAME = p.OBJECT_NAME AND
i.INDEX_NAME = p.INDEX_NAME
WHERE
i.TABLE_SCHEMA = 'fgedudb' AND
i.INDEX_NAME != 'PRIMARY' AND
(p.COUNT_FETCH IS NULL OR p.COUNT_FETCH = 0) AND
(p.COUNT_INSERT IS NULL OR p.COUNT_INSERT = 0) AND
(p.COUNT_UPDATE IS NULL OR p.COUNT_UPDATE = 0) AND
(p.COUNT_DELETE IS NULL OR p.COUNT_DELETE = 0);
# 输出示例
+------------+----------------+-------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME |
+------------+----------------+-------------+
| fgedu_users| idx_created_at | created_at |
| fgedu_orders| idx_status | status |
+------------+----------------+-------------+
# 5. 删除未使用的索引
mysql> DROP INDEX idx_created_at ON fgedudb.fgedu_users;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP INDEX idx_status ON fgedudb.fgedu_orders;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.3 权限审计
使用INFORMATION_SCHEMA进行权限审计的实战案例。
问题描述：需要定期审计数据库用户的权限，避免权限过大导致安全风险
解决方法：使用INFORMATION_SCHEMA查询用户权限信息
mysql> SELECT GRANTEE, PRIVILEGE_TYPE
FROM information_schema.USER_PRIVILEGES
ORDER BY GRANTEE, PRIVILEGE_TYPE;
+——————+————————-+
| GRANTEE | PRIVILEGE_TYPE |
+——————+————————-+
| ‘root’@’localhost’ | SELECT |
| ‘root’@’localhost’ | INSERT |
| ‘root’@’localhost’ | UPDATE |
| ‘root’@’localhost’ | DELETE |
…
| ‘app_user’@’%’ | SELECT |
| ‘app_user’@’%’ | INSERT |
| ‘app_user’@’%’ | UPDATE |
| ‘app_user’@’%’ | DELETE |
| ‘readonly_user’@’%’ | SELECT |
+——————+————————-+
# 2. 查询用户的数据库级权限
mysql> SELECT GRANTEE, TABLE_SCHEMA, PRIVILEGE_TYPE
FROM information_schema.SCHEMA_PRIVILEGES
ORDER BY GRANTEE, TABLE_SCHEMA, PRIVILEGE_TYPE;
+——————+————–+————————-+
| GRANTEE | TABLE_SCHEMA | PRIVILEGE_TYPE |
+——————+————–+————————-+
| ‘app_user’@’%’ | fgedudb | SELECT |
| ‘app_user’@’%’ | fgedudb | INSERT |
| ‘app_user’@’%’ | fgedudb | UPDATE |
| ‘app_user’@’%’ | fgedudb | DELETE |
| ‘readonly_user’@’%’ | fgedudb | SELECT |
+——————+————–+————————-+
# 3. 查询用户的表级权限
mysql> SELECT GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE
FROM information_schema.TABLE_PRIVILEGES
ORDER BY GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE;
+——————+————–+——————–+————————-+
| GRANTEE | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE |
+——————+————–+——————–+————————-+
| ‘app_user’@’%’ | fgedudb | fgedu_users | SELECT |
| ‘app_user’@’%’ | fgedudb | fgedu_users | INSERT |
| ‘app_user’@’%’ | fgedudb | fgedu_users | UPDATE |
| ‘app_user’@’%’ | fgedudb | fgedu_orders | SELECT |
| ‘app_user’@’%’ | fgedudb | fgedu_orders | INSERT |
| ‘app_user’@’%’ | fgedudb | fgedu_orders | UPDATE |
| ‘app_user’@’%’ | fgedudb | fgedu_orders | DELETE |
+——————+————–+——————–+————————-+
# 4. 审计权限，找出权限过大的用户
SELECT
GRANTEE,
COUNT(*) AS privilege_count
FROM
information_schema.USER_PRIVILEGES
WHERE
PRIVILEGE_TYPE IN (‘SUPER’, ‘FILE’, ‘SHUTDOWN’, ‘PROCESS’, ‘CREATE USER’)
GROUP BY
GRANTEE
HAVING
COUNT(*) > 0;
# 输出示例
+——————+——————+
| GRANTEE | privilege_count |
+——————+——————+
| ‘app_user’@’%’ | 2 |
| ‘root’@’localhost’ | 5 |
+——————+——————+
# 5. 回收不必要的权限
mysql> REVOKE SUPER, FILE ON *.* FROM ‘app_user’@’%’;
Query OK, 0 rows affected (0.01 sec)
# 验证权限回收
mysql> SELECT GRANTEE, PRIVILEGE_TYPE
FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE = ”’app_user”@”%”’ AND
PRIVILEGE_TYPE IN (‘SUPER’, ‘FILE’);
Empty set (0.00 sec)
Part05-风哥经验总结与分享
5.1 INFORMATION_SCHEMA最佳实践
- 合理使用WHERE条件：在查询INFORMATION_SCHEMA表时，始终使用TABLE_SCHEMA和TABLE_NAME等条件过滤，避免全表扫描
- 只查询必要的列：只查询需要的列，减少数据传输量和查询时间
- 避免在高峰期查询：INFORMATION_SCHEMA查询可能影响服务器性能，应避免在业务高峰期执行
- 结合Performance Schema：将INFORMATION_SCHEMA与Performance Schema结合使用，可以获取更全面的性能和使用情况信息
- 使用视图简化查询：创建自定义视图简化复杂的元数据查询
- 定期更新统计信息：定期更新表统计信息，确保INFORMATION_SCHEMA中的统计数据准确
- 备份元数据：定期备份数据库元数据，用于恢复和迁移
5.2 常见问题与解决方案
| 问题现象 | 可能原因 | 解决方案 |
|---|---|---|
| 查询INFORMATION_SCHEMA表速度慢 | 没有使用WHERE条件过滤，查询的表数量过多 | 增加WHERE条件，只查询需要的数据库和表 |
| 统计信息不准确 | 表统计信息过时，没有定期更新 | 使用ANALYZE TABLE定期更新表统计信息 |
| 无法查询到某些表或列 | 用户权限不足，或者表名/列名拼写错误 | 检查用户权限，确认表名和列名的正确性 |
| 查询结果与实际情况不符 | 缓存导致的结果不一致，或者统计信息不准确 | 刷新缓存，更新统计信息 |
| 在大型数据库中查询性能极差 | 数据库表数量过多，或者查询过于复杂 | 分批次查询，使用更具体的过滤条件，考虑使用缓存 |
from MySQL:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
