内容简介：INFORMATION_SCHEMA是MySQL提供的强大元数据查询工具，掌握其高级查询技巧对于DBA的日常工作至关重要。本文风哥教程参考MySQL官方文档，详细介绍INFORMATION_SCHEMA的各种查询技巧，包括数据库结构查询、性能监控查询、安全审计查询等，并提供丰富的生产环境实战案例。学习交流加群风哥QQ113257174
Part01-基础概念与理论知识
1.1 INFORMATION_SCHEMA概述
INFORMATION_SCHEMA是MySQL内置的系统数据库，包含了大量的元数据表，用于存储关于MySQL服务器的元数据信息。它提供了一个统一的方式来查询数据库的结构、状态和性能信息。
1.2 查询基础
查询INFORMATION_SCHEMA表的基本语法和注意事项：
- 使用标准的SELECT语句查询INFORMATION_SCHEMA表
- 所有INFORMATION_SCHEMA表都位于information_schema数据库中
- 查询时应始终使用TABLE_SCHEMA和TABLE_NAME等条件过滤
- INFORMATION_SCHEMA表是动态生成的视图，不是物理表
Part02-生产环境规划与建议
2.1 查询优化策略
查询INFORMATION_SCHEMA表时的优化策略：
- 使用具体的过滤条件：始终指定TABLE_SCHEMA和TABLE_NAME等条件
- 限制返回列：只查询需要的列，减少数据传输量
- 使用LIMIT：当只需要部分结果时，使用LIMIT限制返回行数
- 避免全表扫描：对于大型数据库，避免执行没有WHERE条件的查询
- 使用索引：INFORMATION_SCHEMA表本身也有索引，合理使用可以提高查询性能
2.2 性能注意事项
查询INFORMATION_SCHEMA表时的性能注意事项：
- INFORMATION_SCHEMA查询可能会影响服务器性能，特别是在大型数据库中
- 避免在业务高峰期执行复杂的INFORMATION_SCHEMA查询
- 对于频繁使用的查询，可以考虑使用缓存机制
- 结合Performance Schema使用，可以获取更全面的性能信息
Part03-生产环境项目实施方案
3.1 数据库结构查询
查询数据库结构的高级技巧。
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
t.TABLE_TYPE,
t.ENGINE,
t.TABLE_ROWS,
ROUND((t.DATA_LENGTH + t.INDEX_LENGTH) / 1024 / 1024, 2) AS TABLE_SIZE_MB,
t.CREATE_TIME,
t.UPDATE_TIME
FROM
information_schema.TABLES t
WHERE
t.TABLE_SCHEMA NOT IN (‘information_schema’, ‘mysql’, ‘performance_schema’, ‘sys’)
ORDER BY
t.TABLE_SCHEMA, t.TABLE_NAME;
# 输出示例（部分）
+————–+——————–+————+——–+————+————–+———————+———————+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_ROWS | TABLE_SIZE_MB | CREATE_TIME | UPDATE_TIME |
+————–+——————–+————+——–+————+————–+———————+———————+
| fgedudb | fgedu_categories | BASE TABLE | InnoDB | 100 | 0.02| 2026-01-01 10:00:00 | 2026-01-01 10:00:00 |
| fgedudb | fgedu_order_items | BASE TABLE | InnoDB | 100000 | 100.0 | 2026-01-01 10:00:00 | 2026-04-02 10:00:00 |
| fgedudb | fgedu_orders | BASE TABLE | InnoDB | 50000 | 50.0 | 2026-01-01 10:00:00 | 2026-04-02 10:00:00 |
| fgedudb | fgedu_products | BASE TABLE | InnoDB | 20000 | 20.0 | 2026-01-01 10:00:00 | 2026-04-02 10:00:00 |
| fgedudb | fgedu_users | BASE TABLE | InnoDB | 10000 | 10.0 | 2026-01-01 10:00:00 | 2026-04-02 10:00:00 |
+————–+——————–+————+——–+————+————–+———————+———————+
# 2. 查询表的完整结构信息（包含列、索引、约束）
SELECT
c.TABLE_NAME,
c.COLUMN_NAME,
c.DATA_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
c.IS_NULLABLE,
c.COLUMN_DEFAULT,
c.COLUMN_COMMENT,
s.INDEX_NAME,
s.NON_UNIQUE,
s.SEQ_IN_INDEX,
tc.CONSTRAINT_TYPE
FROM
information_schema.COLUMNS c
LEFT JOIN
information_schema.STATISTICS s ON c.TABLE_SCHEMA = s.TABLE_SCHEMA AND
c.TABLE_NAME = s.TABLE_NAME AND
c.COLUMN_NAME = s.COLUMN_NAME
LEFT JOIN
information_schema.KEY_COLUMN_USAGE kcu ON c.TABLE_SCHEMA = kcu.TABLE_SCHEMA AND
c.TABLE_NAME = kcu.TABLE_NAME AND
c.COLUMN_NAME = kcu.COLUMN_NAME
LEFT JOIN
information_schema.TABLE_CONSTRAINTS tc ON kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA AND
kcu.TABLE_NAME = tc.TABLE_NAME AND
kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE
c.TABLE_SCHEMA = ‘fgedudb’ AND
c.TABLE_NAME = ‘fgedu_users’
ORDER BY
c.ORDINAL_POSITION, s.SEQ_IN_INDEX;
# 输出示例（部分）
+————+—————-+———–+————————–+————-+—————-+—————-+—————-+————+————–+—————–+
| TABLE_NAME | COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE | COLUMN_DEFAULT | COLUMN_COMMENT | INDEX_NAME | NON_UNIQUE | SEQ_IN_INDEX | CONSTRAINT_TYPE |
+————+—————-+———–+————————–+————-+—————-+—————-+—————-+————+————–+—————–+
| fgedu_users| id | int | NULL | NO | NULL | 用户ID | PRIMARY | 0 | 1 | PRIMARY KEY |
| fgedu_users| username | varchar | 50 | NO | NULL | 用户名 | idx_username | 0 | 1 | NULL |
| fgedu_users| email | varchar | 100 | NO | NULL | 邮箱 | idx_email | 0 | 1 | NULL |
| fgedu_users| password | varchar | 255 | NO | NULL | 密码 | NULL | NULL | NULL | NULL |
| fgedu_users| full_name | varchar | 100 | YES | NULL | 姓名 | NULL | NULL | NULL | NULL |
| fgedu_users| created_at | datetime | NULL | YES | CURRENT_TIMESTAMP | 创建时间 | idx_created_at | 1 | 1 | NULL |
| fgedu_users| updated_at | datetime | NULL | YES | NULL | 更新时间 | NULL | NULL | NULL | NULL |
| fgedu_users| status | tinyint | NULL | YES | 1 | 状态 | idx_status | 1 | 1 | NULL |
+————+—————-+———–+————————–+————-+—————-+—————-+—————-+————+————–+—————–+
# 3. 查询数据库中所有的外键关系
SELECT
tc.CONSTRAINT_SCHEMA,
tc.TABLE_NAME,
kcu.COLUMN_NAME,
tc.CONSTRAINT_NAME,
tc.REFERENCED_TABLE_NAME,
kcu.REFERENCED_COLUMN_NAME
FROM
information_schema.TABLE_CONSTRAINTS tc
JOIN
information_schema.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA AND
tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE
tc.CONSTRAINT_TYPE = ‘FOREIGN KEY’ AND
tc.CONSTRAINT_SCHEMA NOT IN (‘information_schema’, ‘mysql’, ‘performance_schema’, ‘sys’)
ORDER BY
tc.CONSTRAINT_SCHEMA, tc.TABLE_NAME;
# 输出示例
+—————–+——————–+————-+———————–+——————-+————————+
| CONSTRAINT_SCHEMA | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+—————–+——————–+————-+———————–+——————-+————————+
| fgedudb | fgedu_order_items | order_id | fk_order_item_order_id| fgedu_orders | id |
| fgedudb | fgedu_order_items | product_id | fk_order_item_product_id| fgedu_products | id |
| fgedudb | fgedu_orders | user_id | fk_order_user_id | fgedu_users | id |
| fgedudb | fgedu_products | category_id | fk_product_category_id| fgedu_categories | id |
+—————–+——————–+————-+———————–+——————-+————————+
3.2 性能相关查询
查询性能相关信息的高级技巧。
SELECT
‘SELECT’ AS QUERY_TYPE,
VARIABLE_VALUE AS COUNT
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME = ‘Com_select’
UNION ALL
SELECT
‘INSERT’ AS QUERY_TYPE,
VARIABLE_VALUE AS COUNT
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME = ‘Com_insert’
UNION ALL
SELECT
‘UPDATE’ AS QUERY_TYPE,
VARIABLE_VALUE AS COUNT
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME = ‘Com_update’
UNION ALL
SELECT
‘DELETE’ AS QUERY_TYPE,
VARIABLE_VALUE AS COUNT
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME = ‘Com_delete’
ORDER BY
COUNT DESC;
# 输出示例
+————+——-+
| QUERY_TYPE | COUNT |
+————+——-+
| SELECT | 50000 |
| UPDATE | 19000 |
| INSERT | 10000 |
| DELETE | 1000 |
+————+——-+
# 2. 查询InnoDB缓冲池使用情况
SELECT
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_pages_total’) AS TOTAL_PAGES,
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_pages_data’) AS DATA_PAGES,
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_pages_free’) AS FREE_PAGES,
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_pages_misc’) AS MISC_PAGES,
ROUND((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_pages_data’) /
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_pages_total’) * 100, 2) AS DATA_PERCENTAGE,
ROUND((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_pages_free’) /
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_pages_total’) * 100, 2) AS FREE_PERCENTAGE;
# 输出示例
+————-+————+————+————+—————-+—————-+
| TOTAL_PAGES | DATA_PAGES | FREE_PAGES | MISC_PAGES | DATA_PERCENTAGE | FREE_PERCENTAGE |
+————-+————+————+————+—————-+—————-+
| 65536 | 64000 | 16 | 1584 | 97.66 | 0.02 |
+————-+————+————+————+—————-+—————-+
# 3. 查询长时间运行的事务
SELECT
trx_id,
trx_state,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_duration_seconds,
trx_mysql_thread_id,
trx_query
FROM
information_schema.INNODB_TRX
WHERE
TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY
trx_duration_seconds DESC;
# 输出示例
+——–+———–+———————+————————+——————+——————————————+
| trx_id | trx_state | trx_started | trx_duration_seconds | trx_mysql_thread_id | trx_query |
+——–+———–+———————+————————+——————+——————————————+
| 123458 | RUNNING | 2026-04-02 19:50:00 | 1200 | 131 | SELECT * FROM fgedu_order_items WHERE created_at > ‘2026-01-01’ |
+——–+———–+———————+————————+——————+——————————————+
3.3 安全相关查询
查询安全相关信息的高级技巧。
SELECT
GRANTEE,
TABLE_SCHEMA,
TABLE_NAME,
PRIVILEGE_TYPE,
IS_GRANTABLE
FROM
information_schema.TABLE_PRIVILEGES
WHERE
TABLE_SCHEMA NOT IN (‘information_schema’, ‘mysql’, ‘performance_schema’, ‘sys’)
ORDER BY
GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE;
# 输出示例
+——————+————–+——————–+————————-+————–+
| GRANTEE | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+——————+————–+——————–+————————-+————–+
| ‘app_user’@’%’ | fgedudb | fgedu_users | SELECT | NO |
| ‘app_user’@’%’ | fgedudb | fgedu_users | INSERT | NO |
| ‘app_user’@’%’ | fgedudb | fgedu_users | UPDATE | NO |
| ‘app_user’@’%’ | fgedudb | fgedu_orders | SELECT | NO |
| ‘app_user’@’%’ | fgedudb | fgedu_orders | INSERT | NO |
| ‘app_user’@’%’ | fgedudb | fgedu_orders | UPDATE | NO |
| ‘app_user’@’%’ | fgedudb | fgedu_orders | DELETE | NO |
+——————+————–+——————–+————————-+————–+
# 2. 查询具有超级权限的用户
SELECT
GRANTEE
FROM
information_schema.USER_PRIVILEGES
WHERE
PRIVILEGE_TYPE = ‘SUPER’
GROUP BY
GRANTEE;
# 输出示例
+——————+——————-+
| GRANTEE | VARIABLE_SOURCE |
+——————+——————-+
| ‘root’@’localhost’ | global |
+——————+——————-+
# 3. 查询允许远程访问的用户
SELECT
USER,
HOST
FROM
mysql.user
WHERE
HOST NOT IN (‘localhost’, ‘127.0.0.1’, ‘::1’)
ORDER BY
USER, HOST;
# 输出示例
+———-+———–+——————-+
| USER | HOST | VARIABLE_SOURCE |
+———-+———–+——————-+
| app_user | % | global |
| readonly | 192.168.% | global |
+———-+———–+——————-+
3.4 高级查询技巧
INFORMATION_SCHEMA的高级查询技巧。
mysql> SELECT
TABLE_NAME,
(SELECT COUNT(*) FROM information_schema.COLUMNS c WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME) AS COLUMN_COUNT,
(SELECT COUNT(*) FROM information_schema.STATISTICS s WHERE s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME AND s.INDEX_NAME != ‘PRIMARY’) AS INDEX_COUNT
FROM
information_schema.TABLES t
WHERE
t.TABLE_SCHEMA = ‘fgedudb’;
# 输出示例
+——————–+————-+————-+
| TABLE_NAME | COLUMN_COUNT | INDEX_COUNT |
+——————–+————-+————-+
| fgedu_categories | 4 | 1 |
| fgedu_order_items | 6 | 3 |
| fgedu_orders | 7 | 3 |
| fgedu_products | 8 | 2 |
| fgedu_users | 8 | 4 |
+——————–+————-+————-+
# 2. 使用GROUP_CONCAT合并结果
mysql> SELECT
TABLE_NAME,
GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION) AS COLUMNS,
GROUP_CONCAT(DATA_TYPE ORDER BY ORDINAL_POSITION) AS DATA_TYPES
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = ‘fgedudb’ AND TABLE_NAME = ‘fgedu_users’
GROUP BY
TABLE_NAME;
# 输出示例
+————+——————————————-+——————————————-+
| TABLE_NAME | COLUMNS | DATA_TYPES |
+————+——————————————-+——————————————-+
| fgedu_users| id,username,email,password,full_name,created_at,updated_at,status | int,varchar,varchar,varchar,varchar,datetime,datetime,tinyint |
+————+——————————————-+——————————————-+
# 3. 使用CASE语句进行条件查询
mysql> SELECT
TABLE_NAME,
CASE
WHEN TABLE_ROWS < 1000 THEN '小型表'
WHEN TABLE_ROWS < 10000 THEN '中型表'
WHEN TABLE_ROWS < 100000 THEN '大型表'
ELSE '超大型表'
END AS TABLE_SIZE_CATEGORY
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'fgedudb'
ORDER BY
TABLE_ROWS DESC;
# 输出示例
+--------------------+--------------------+
| TABLE_NAME | TABLE_SIZE_CATEGORY |
+--------------------+--------------------+
| fgedu_order_items | 超大型表 |
| fgedu_orders | 大型表 |
| fgedu_products | 大型表 |
| fgedu_users | 中型表 |
| fgedu_categories | 小型表 |
+--------------------+--------------------+
# 4. 使用JOIN查询多个表
mysql> SELECT
t.TABLE_NAME,
t.ENGINE,
COUNT(c.COLUMN_NAME) AS COLUMN_COUNT,
COUNT(DISTINCT s.INDEX_NAME) AS INDEX_COUNT,
t.TABLE_ROWS
FROM
information_schema.TABLES t
JOIN
information_schema.COLUMNS c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN
information_schema.STATISTICS s ON t.TABLE_SCHEMA = s.TABLE_SCHEMA AND t.TABLE_NAME = s.TABLE_NAME
WHERE
t.TABLE_SCHEMA = ‘fgedudb’
GROUP BY
t.TABLE_NAME, t.ENGINE, t.TABLE_ROWS
ORDER BY
t.TABLE_ROWS DESC;
# 输出示例
+——————–+——–+————-+————-+————+
| TABLE_NAME | ENGINE | COLUMN_COUNT | INDEX_COUNT | TABLE_ROWS |
+——————–+——–+————-+————-+————+
| fgedu_order_items | InnoDB | 30 | 4 | 100000 |
| fgedu_orders | InnoDB | 28 | 4 | 50000 |
| fgedu_products | InnoDB | 32 | 3 | 20000 |
| fgedu_users | InnoDB | 40 | 5 | 10000 |
| fgedu_categories | InnoDB | 16 | 2 | 100 |
+——————–+——–+————-+————-+————+
Part04-生产案例与实战讲解
4.1 数据库文档自动生成
使用INFORMATION_SCHEMA自动生成数据库文档的实战案例。
问题描述：需要定期生成数据库文档，便于开发和维护
解决方法：使用INFORMATION_SCHEMA查询表结构信息，生成Markdown格式文档
# generate_db_doc_md.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.md”
# 生成Markdown头部
cat > $OUTPUT_FILE << EOF
# 数据库结构文档 - $DB_NAME
生成时间: $(date '+%Y-%m-%d %H:%M:%S')
## 数据库概述
EOF
# 获取数据库基本信息
mysql -u $DB_USER -p"$DB_PASS" -e "SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '$DB_NAME'" > temp_schema.txt
# 生成数据库基本信息
echo “| 参数 | 值 |” >> $OUTPUT_FILE
echo “| — | — |” >> $OUTPUT_FILE
tail -n +2 temp_schema.txt | while read -r CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH DEFAULT_ENCRYPTION DEFAULT_TABLE_ENCRYPTION DEFAULT_SE_PRIVILEGES_ID;
do
echo “| 数据库名 | $SCHEMA_NAME |” >> $OUTPUT_FILE
echo “| 默认字符集 | $DEFAULT_CHARACTER_SET_NAME |” >> $OUTPUT_FILE
echo “| 默认排序规则 | $DEFAULT_COLLATION_NAME |” >> $OUTPUT_FILE
echo “| 默认加密 | $DEFAULT_ENCRYPTION |” >> $OUTPUT_FILE
done
echo “” >> $OUTPUT_FILE
# 获取所有表
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” >> $OUTPUT_FILE
echo “” >> $OUTPUT_FILE
echo “**描述：** $COMMENT” >> $OUTPUT_FILE
echo “” >> $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
# 生成表结构Markdown
echo “### 表结构” >> $OUTPUT_FILE
echo “” >> $OUTPUT_FILE
echo “| 字段名 | 数据类型 | 长度 | 是否可为空 | 默认值 | 描述 |” >> $OUTPUT_FILE
echo “| — | — | — | — | — | — |” >> $OUTPUT_FILE
# 跳过表头行
tail -n +2 temp_table.txt | while read -r COLUMN DATA_TYPE LENGTH NULLABLE DEFAULT COMMENT;
do
# 处理默认值中的换行符
DEFAULT=$(echo “$DEFAULT” | tr ‘\n’ ‘ ‘)
echo “| $COLUMN | $DATA_TYPE | $LENGTH | $NULLABLE | $DEFAULT | $COMMENT |” >> $OUTPUT_FILE
done
echo “” >> $OUTPUT_FILE
# 获取索引信息
echo “### 索引信息” >> $OUTPUT_FILE
echo “” >> $OUTPUT_FILE
mysql -u $DB_USER -p”$DB_PASS” -e “SELECT INDEX_NAME, NON_UNIQUE, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS INDEX_COLUMNS, CARDINALITY FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = ‘$DB_NAME’ AND TABLE_NAME = ‘$TABLE’ GROUP BY INDEX_NAME, NON_UNIQUE, CARDINALITY” > temp_index.txt
# 生成索引Markdown
echo “| 索引名 | 是否唯一 | 索引列 | 基数 |” >> $OUTPUT_FILE
echo “| — | — | — | — |” >> $OUTPUT_FILE
# 跳过表头行
tail -n +2 temp_index.txt | while read -r INDEX_NAME NON_UNIQUE INDEX_COLUMNS CARDINALITY;
do
UNIQUE=”是”
if [ “$NON_UNIQUE” = “1” ]; then
UNIQUE=”否”
fi
echo “| $INDEX_NAME | $UNIQUE | $INDEX_COLUMNS | $CARDINALITY |” >> $OUTPUT_FILE
done
echo “” >> $OUTPUT_FILE
done <<< "$TABLES"
# 清理临时文件
rm -f temp_schema.txt temp_table.txt temp_index.txt
echo "数据库文档已生成：$OUTPUT_FILE"
# chmod +x generate_db_doc_md.sh
# ./generate_db_doc_md.sh
数据库文档已生成：db_documentation.md
# 查看生成的文档
# cat db_documentation.md | head -50
# 数据库结构文档 – fgedudb
生成时间: 2026-04-02 21:00:00
## 数据库概述
| 参数 | 值 |
| — | — |
| 数据库名 | fgedudb |
| 默认字符集 | utf8mb4 |
| 默认排序规则 | utf8mb4_0900_ai_ci |
| 默认加密 | N |
## 表：fgedu_categories
**描述：** 分类表
### 表结构
| 字段名 | 数据类型 | 长度 | 是否可为空 | 默认值 | 描述 |
| — | — | — | — | — | — |
| id | int | NULL | NO | NULL | 分类ID |
| name | varchar | 100 | NO | NULL | 分类名称 |
| description | varchar | 255 | YES | NULL | 分类描述 |
| status | tinyint | NULL | YES | 1 | 状态 |
### 索引信息
| 索引名 | 是否唯一 | 索引列 | 基数 |
| — | — | — | — |
| PRIMARY | 是 | id | 100 |
| idx_name | 否 | name | 100 |
4.2 索引优化分析
使用INFORMATION_SCHEMA进行索引优化分析的实战案例。
问题描述：数据库查询性能下降，需要分析索引的使用情况和选择性
解决方法：使用INFORMATION_SCHEMA.STATISTICS表查询索引的选择性
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
s.INDEX_NAME,
s.COLUMN_NAME,
s.CARDINALITY,
t.TABLE_ROWS,
ROUND((s.CARDINALITY / t.TABLE_ROWS) * 100, 2) AS SELECTIVITY,
CASE
WHEN (s.CARDINALITY / t.TABLE_ROWS) * 100 > 90 THEN ‘高选择性’
WHEN (s.CARDINALITY / t.TABLE_ROWS) * 100 > 50 THEN ‘中选择性’
ELSE ‘低选择性’
END AS SELECTIVITY_CATEGORY
FROM
information_schema.STATISTICS s
JOIN
information_schema.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME
WHERE
s.TABLE_SCHEMA NOT IN (‘information_schema’, ‘mysql’, ‘performance_schema’, ‘sys’) AND
s.INDEX_NAME != ‘PRIMARY’ AND
t.TABLE_ROWS > 0
ORDER BY
t.TABLE_SCHEMA, t.TABLE_NAME, s.INDEX_NAME, s.SEQ_IN_INDEX;
# 输出示例（部分）
+————–+——————–+————————+————-+————-+————+————-+——————–+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | TABLE_ROWS | SELECTIVITY | SELECTIVITY_CATEGORY |
+————–+——————–+————————+————-+————-+————+————-+——————–+
| fgedudb | fgedu_orders | fk_order_user_id | user_id | 10000 | 50000 | 20.0 | 低选择性 |
| fgedudb | fgedu_orders | idx_order_date | order_date | 5000 | 50000 | 10.0 | 低选择性 |
| fgedudb | fgedu_orders | idx_status | status | 5 | 50000 | 0.01| 低选择性 |
| fgedudb | fgedu_users | idx_username | username | 10000 | 10000 | 100.0 | 高选择性 |
| fgedudb | fgedu_users | idx_email | email | 10000 | 10000 | 100.0 | 高选择性 |
| fgedudb | fgedu_users | idx_created_at | created_at | 5000 | 10000 | 50.0 | 中选择性 |
| fgedudb | fgedu_users | idx_status | status | 2 | 10000 | 0.02| 低选择性 |
+————–+——————–+————————+————-+————-+————+————-+——————–+
# 查询低选择性的索引（需要优化）
SELECT
TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
COLUMN_NAME,
SELECTIVITY
FROM (
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
s.INDEX_NAME,
s.COLUMN_NAME,
ROUND((s.CARDINALITY / t.TABLE_ROWS) * 100, 2) AS SELECTIVITY
FROM
information_schema.STATISTICS s
JOIN
information_schema.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME
WHERE
s.TABLE_SCHEMA NOT IN (‘information_schema’, ‘mysql’, ‘performance_schema’, ‘sys’) AND
s.INDEX_NAME != ‘PRIMARY’ AND
t.TABLE_ROWS > 0
) AS index_stats
WHERE
SELECTIVITY < 30;
# 输出示例
+--------------+------------+------------------------+-------------+-------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | SELECTIVITY |
+--------------+------------+------------------------+-------------+-------------+
| fgedudb | fgedu_orders| fk_order_user_id | user_id | 20.0 |
| fgedudb | fgedu_orders| idx_order_date | order_date | 10.0 |
| fgedudb | fgedu_orders| idx_status | status | 0.01|
| fgedudb | fgedu_users | idx_status | status | 0.02|
+--------------+------------+------------------------+-------------+-------------+
# 分析索引使用情况
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
OBJECT_SCHEMA = 'fgedudb'
ORDER BY
OBJECT_NAME, INDEX_NAME;
# 输出示例（部分）
+---------------+------------------+------------------------+-------------+--------------+--------------+--------------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+---------------+------------------+------------------------+-------------+--------------+--------------+--------------+
| fgedudb | fgedu_orders | PRIMARY | 2000 | 1000 | 300 | 100 |
| fgedudb | fgedu_orders | fk_order_user_id | 1500 | 0 | 0 | 0 |
| fgedudb | fgedu_orders | idx_order_date | 800 | 0 | 0 | 0 |
| fgedudb | fgedu_orders | idx_status | 0 | 0 | 0 | 0 |
| fgedudb | fgedu_users | PRIMARY | 1000 | 500 | 200 | 50 |
| fgedudb | fgedu_users | idx_username | 800 | 0 | 0 | 0 |
| fgedudb | fgedu_users | idx_email | 500 | 0 | 0 | 0 |
| fgedudb | fgedu_users | idx_created_at | 0 | 0 | 0 | 0 |
| fgedudb | fgedu_users | idx_status | 0 | 0 | 0 | 0 |
+---------------+------------------+------------------------+-------------+--------------+--------------+--------------+
# 删除未使用的索引
mysql> DROP INDEX idx_status ON fgedudb.fgedu_orders;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP INDEX idx_status ON fgedudb.fgedu_users;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP INDEX idx_created_at ON fgedudb.fgedu_users;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.3 权限审计
使用INFORMATION_SCHEMA进行权限审计的实战案例。
问题描述：需要定期审计数据库用户的权限，避免权限过大导致安全风险
解决方法：使用INFORMATION_SCHEMA查询用户权限信息
SELECT
GRANTEE,
GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE) AS GLOBAL_PRIVILEGES
FROM
information_schema.USER_PRIVILEGES
GROUP BY
GRANTEE
ORDER BY
GRANTEE;
# 输出示例
+——————+——————————————————————————————–+
| GRANTEE | GLOBAL_PRIVILEGES |
+——————+——————————————————————————————–+
| ‘app_user’@’%’ | SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,CREATE TEMPORARY TABLES,LOCK TABLES |
| ‘readonly’@’%’ | SELECT |
| ‘root’@’localhost’ | SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOW DATABASES,SUPER,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,EVENT,TRIGGER,CREATE TABLESPACE,CREATE ROLE,DROP ROLE |
+——————+——————————————————————————————–+
# 查询用户的数据库级权限
SELECT
GRANTEE,
TABLE_SCHEMA,
GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE) AS SCHEMA_PRIVILEGES
FROM
information_schema.SCHEMA_PRIVILEGES
GROUP BY
GRANTEE, TABLE_SCHEMA
ORDER BY
GRANTEE, TABLE_SCHEMA;
# 输出示例
+——————+————–+—————————-+
| GRANTEE | TABLE_SCHEMA | SCHEMA_PRIVILEGES |
+——————+————–+—————————-+
| ‘app_user’@’%’ | fgedudb | SELECT,INSERT,UPDATE,DELETE |
| ‘readonly’@’%’ | fgedudb | SELECT |
+——————+————–+—————————-+
# 查询用户的表级权限
SELECT
GRANTEE,
TABLE_SCHEMA,
TABLE_NAME,
GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE) AS TABLE_PRIVILEGES
FROM
information_schema.TABLE_PRIVILEGES
GROUP BY
GRANTEE, TABLE_SCHEMA, TABLE_NAME
ORDER BY
GRANTEE, TABLE_SCHEMA, TABLE_NAME;
# 输出示例
+——————+————–+——————–+—————————-+
| GRANTEE | TABLE_SCHEMA | TABLE_NAME | TABLE_PRIVILEGES |
+——————+————–+——————–+—————————-+
| ‘app_user’@’%’ | fgedudb | fgedu_users | SELECT,INSERT,UPDATE |
| ‘app_user’@’%’ | fgedudb | fgedu_orders | SELECT,INSERT,UPDATE,DELETE |
+——————+————–+——————–+—————————-+
# 审计具有危险权限的用户
SELECT
GRANTEE
FROM
information_schema.USER_PRIVILEGES
WHERE
PRIVILEGE_TYPE IN (‘SUPER’, ‘FILE’, ‘SHUTDOWN’, ‘PROCESS’, ‘CREATE USER’)
GROUP BY
GRANTEE
HAVING
COUNT(*) > 0;
# 输出示例
+——————+——————-+
| GRANTEE | VARIABLE_SOURCE |
+——————+——————-+
| ‘root’@’localhost’ | global |
+——————+——————-+
4.4 性能调优
使用INFORMATION_SCHEMA进行性能调优的实战案例。
问题描述：数据库响应时间变慢，需要找出性能瓶颈并进行调优
解决方法：使用INFORMATION_SCHEMA和Performance Schema查询性能数据
SELECT
‘QPS’ AS METRIC,
ROUND(VARIABLE_VALUE / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Uptime’), 2) AS VALUE
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME = ‘Queries’
UNION ALL
SELECT
‘TPS’ AS METRIC,
ROUND((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Com_commit’) +
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Com_rollback’)) /
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Uptime’) AS VALUE
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME = ‘Uptime’
UNION ALL
SELECT
‘Slow Query Rate’ AS METRIC,
ROUND(VARIABLE_VALUE / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Uptime’), 4) AS VALUE
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME = ‘Slow_queries’
UNION ALL
SELECT
‘Buffer Pool Hit Rate’ AS METRIC,
ROUND((1 – (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’) /
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_read_requests’)) * 100, 2) AS VALUE
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME = ‘Uptime’;
# 输出示例
+———————+——-+——————-+
| METRIC | VALUE | VARIABLE_SOURCE |
+———————+——-+——————-+
| QPS | 0.92 | global |
| TPS | 0.12 | global |
| Slow Query Rate | 0.0002| global |
| Buffer Pool Hit Rate| 99.90 | global |
+———————+——-+——————-+
# 查询I/O性能指标
SELECT
‘Innodb Data Reads’ AS METRIC,
VARIABLE_VALUE AS VALUE
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME = ‘Innodb_data_reads’
UNION ALL
SELECT
‘Innodb Data Writes’ AS METRIC,
VARIABLE_VALUE AS VALUE
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME = ‘Innodb_data_writes’
UNION ALL
SELECT
‘Innodb Data Read Bytes’ AS METRIC,
ROUND(VARIABLE_VALUE / 1024 / 1024, 2) AS VALUE_MB
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME = ‘Innodb_data_read’
UNION ALL
SELECT
‘Innodb Data Write Bytes’ AS METRIC,
ROUND(VARIABLE_VALUE / 1024 / 1024, 2) AS VALUE_MB
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME = ‘Innodb_data_written’;
# 输出示例
+————————+———-+——————-+
| METRIC | VALUE | VARIABLE_SOURCE |
+————————+———-+——————-+
| Innodb Data Reads | 1000 | global |
| Innodb Data Writes | 50000 | global |
| Innodb Data Read Bytes | 100.00| global |
| Innodb Data Write Bytes | 5000.00| global |
+————————+———-+——————-+
# 查询长时间运行的查询
SELECT
ID,
USER,
HOST,
DB,
COMMAND,
TIME,
STATE,
INFO
FROM
information_schema.PROCESSLIST
WHERE
TIME > 60 AND
COMMAND = ‘Query’
ORDER BY
TIME DESC;
# 输出示例
+—–+——+—————–+———+———+——+——————-+———————————————-+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+—–+——+—————–+———+———+——+——————-+———————————————-+
| 128 | app_user | 192.168.1.100:54325 | fgedudb | Query | 120 | Sending data | SELECT * FROM fgedu_order_items JOIN fgedu_products ON fgedu_order_items.product_id = fgedu_products.id WHERE fgedu_order_items.created_at > ‘2026-01-01’ |
+—–+——+—————–+———+———+——+——————-+———————————————-+
# 查看慢查询日志
# tail -n 50 /mysql/logs/slow.log
# Time: 2026-04-02T21:00:00.123456Z
# User@Host: app_user[app_user] @ 192.168.1.100 [192.168.1.100]
# Query_time: 120.000000 Lock_time: 0.000000 Rows_sent: 100000 Rows_examined: 200000
use fgedudb;
SET timestamp=1750000000;
SELECT * FROM fgedu_order_items JOIN fgedu_products ON fgedu_order_items.product_id = fgedu_products.id WHERE fgedu_order_items.created_at > ‘2026-01-01’;
# 优化查询，添加索引
mysql> EXPLAIN SELECT * FROM fgedu_order_items JOIN fgedu_products ON fgedu_order_items.product_id = fgedu_products.id WHERE fgedu_order_items.created_at > ‘2026-01-01’;
+—-+————-+——————–+————+——–+—————+———+———+———————————-+——–+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——————–+————+——–+—————+———+———+———————————-+——–+———-+————-+
| 1 | SIMPLE | fgedu_order_items | NULL | ALL | NULL | NULL | NULL | NULL | 100000 | 100.00 | Using where |
| 1 | SIMPLE | fgedu_products | NULL | eq_ref | PRIMARY | PRIMARY | 4 | fgedudb.fgedu_order_items.product_id | 1 | 100.00 | NULL |
+—-+————-+——————–+————+——–+—————+———+———+———————————-+——–+———-+————-+
# 添加索引
mysql> CREATE INDEX idx_order_items_created_at ON fgedudb.fgedu_order_items(created_at);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 再次查看执行计划
mysql> EXPLAIN SELECT * FROM fgedu_order_items JOIN fgedu_products ON fgedu_order_items.product_id = fgedu_products.id WHERE fgedu_order_items.created_at > ‘2026-01-01’;
+—-+————-+——————–+————+——–+———————————–+———————————–+———+———————————-+——-+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——————–+————+——–+———————————–+———————————–+———+———————————-+——-+———-+————-+
| 1 | SIMPLE | fgedu_order_items | NULL | range | idx_order_items_created_at | idx_order_items_created_at | 5 | NULL | 50000 | 100.00 | Using index condition |
| 1 | SIMPLE | fgedu_products | NULL | eq_ref | PRIMARY | PRIMARY | 4 | fgedudb.fgedu_order_items.product_id | 1 | 100.00 | NULL |
+—-+————-+——————–+————+——–+———————————–+———————————–+———+———————————-+——-+———-+————-+
Part05-风哥经验总结与分享
5.1 最佳实践
- 始终使用过滤条件：在查询INFORMATION_SCHEMA表时，始终使用TABLE_SCHEMA和TABLE_NAME等条件过滤，减少查询范围
- 限制返回数据：只查询需要的列，使用LIMIT限制返回行数
- 优化查询结构：合理使用JOIN、子查询和GROUP BY等语句，提高查询效率
- 定期更新统计信息：定期使用ANALYZE TABLE更新表统计信息，确保INFORMATION_SCHEMA中的统计数据准确
- 结合Performance Schema：将INFORMATION_SCHEMA与Performance Schema结合使用，可以获取更全面的性能信息
- 创建自定义视图：对于频繁使用的复杂查询，可以创建自定义视图简化查询
- 避免在高峰期查询：INFORMATION_SCHEMA查询可能影响服务器性能，应避免在业务高峰期执行
- 使用脚本自动化：编写脚本自动化常用的INFORMATION_SCHEMA查询任务，提高工作效率
5.2 常见错误与解决方案
| 问题现象 | 可能原因 | 解决方案 |
|---|---|---|
| 查询INFORMATION_SCHEMA表速度慢 | 查询的表数量过多，没有使用过滤条件 | 增加TABLE_SCHEMA和TABLE_NAME等过滤条件，减少查询范围 |
| 统计信息不准确 | 表统计信息过时，没有定期更新 | 使用ANALYZE TABLE定期更新表统计信息 |
| 查询结果与实际情况不符 | 缓存导致的结果不一致，或者统计信息不准确 | 刷新缓存，更新统计信息 |
| 在大型数据库中查询性能极差 | 数据库表数量过多，或者查询过于复杂 | 分批次查询，使用更具体的过滤条件，考虑使用缓存 |
| 无法查询到某些信息 | 用户权限不足，或者信息不存在 | 检查用户权限，确认信息是否存在 |
| JOIN查询性能差 | JOIN的表过多，或者没有使用合适的连接条件 | 减少JOIN的表数量，使用合适的连接条件，避免笛卡尔积 |
from MySQL:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
