MySQL分析步

MySQL分析

-- 库名
set @dbName = 'bsa_crmeb_bak';
-- 表名
set @tableName = 'bsa_crmeb_bak';-- 查看bsa_crmeb_bak数据库基本信息
SELECTSCHEMA_NAME AS '数据库名',DEFAULT_CHARACTER_SET_NAME AS '字符集',DEFAULT_COLLATION_NAME AS '排序规则'
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = @dbName;-- 查看数据库中所有表的信息
SELECTTABLE_NAME AS '表名',TABLE_ROWS AS '预估记录数',DATA_LENGTH/1024/1024 AS '数据大小(MB)',INDEX_LENGTH/1024/1024 AS '索引大小(MB)',(DATA_LENGTH + INDEX_LENGTH)/1024/1024 AS '总大小(MB)',(DATA_LENGTH + INDEX_LENGTH)/1024/1024/1024 AS '总大小(GB)',TABLE_COMMENT AS '表注释'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @dbName
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;-- 重新计算表的统计信息
ANALYZE TABLE @tableName;-- 查看数据库中所有表的信息
SELECTTABLE_NAME AS '表名',TABLE_ROWS AS '预估记录数',DATA_LENGTH/1024/1024 AS '数据大小(MB)',INDEX_LENGTH/1024/1024 AS '索引大小(MB)',(DATA_LENGTH + INDEX_LENGTH)/1024/1024 AS '总大小(MB)',sum((DATA_LENGTH + INDEX_LENGTH)/1024/1024/1024) AS '总大小(GB)',TABLE_COMMENT AS '表注释'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @dbName
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;-- 查看特定表的字段结构
SELECTCOLUMN_NAME AS '字段名',COLUMN_TYPE AS '数据类型',IS_NULLABLE AS '是否可空',COLUMN_DEFAULT AS '默认值',COLUMN_COMMENT AS '字段说明',COLUMN_KEY AS '索引信息'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @dbNameAND TABLE_NAME = @tableName
ORDER BY ORDINAL_POSITION;-- 查看表中数据的基本统计信息（以用户为例）
SELECTCOUNT(*) AS 总记录数,COUNT(DISTINCT user_id) AS 唯一用户数,MIN(create_time) AS 最早创建时间,MAX(create_time) AS 最新创建时间
FROM @tableName;-- 查看表中数据的基本统计信息（以1655用户为例）
SELECTCOUNT(*) AS 总记录数,COUNT(DISTINCT user_id) AS 唯一用户数,MIN(create_time) AS 最早创建时间,MAX(create_time) AS 最新创建时间
FROM @tableName where user_id = 1655;-- 查看数据分布情况
SELECTuser_id,COUNT(*) as 数量,ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM @tableName), 2) as 百分比
FROM @tableName
GROUP BY user_id
ORDER BY 数量 DESC;-- 查看表的索引信息
SHOW INDEX FROM @tableName;-- 或者通过information_schema查询
SELECTINDEX_NAME AS '索引名',COLUMN_NAME AS '字段名',NON_UNIQUE AS '是否非唯一',SEQ_IN_INDEX AS '索引顺序',INDEX_TYPE AS '索引类型'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = @dbName
AND TABLE_NAME = @tableName
ORDER BY INDEX_NAME, SEQ_IN_INDEX;-- 开启查询分析
SET profiling = 1;-- 执行你的查询语句
SELECT * FROM @tableName WHERE user_id = 1655;-- 查看查询分析结果
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1; -- 查看第一个查询的详细信息-- 查看当前数据库连接状态
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';-- 查看数据库最大连接数设置
SHOW VARIABLES LIKE 'max_connections';-- 查看慢查询相关设置
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';-- 查看表使用的存储引擎
SELECTTABLE_NAME,ENGINE,TABLE_ROWS,DATA_LENGTH/1024/1024 AS '数据大小(MB)'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @dbName
ORDER BY DATA_LENGTH DESC;-- 查看数据库中的外键关系
SELECTTABLE_NAME AS '表名',COLUMN_NAME AS '字段名',CONSTRAINT_NAME AS '约束名',REFERENCED_TABLE_NAME AS '引用表',REFERENCED_COLUMN_NAME AS '引用字段'
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = @dbName
AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME;-- 查看数据库中的视图
SELECTTABLE_NAME AS '视图名',VIEW_DEFINITION AS '视图定义'
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = @dbName;-- 查看存储过程和函数
SELECTROUTINE_NAME AS '名称',ROUTINE_TYPE AS '类型',CREATED AS '创建时间',LAST_ALTERED AS '最后修改时间'
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = @dbName;-- 查看数据库相关的性能参数
SHOW VARIABLES LIKE '%buffer_pool%';
SHOW VARIABLES LIKE '%innodb%';
SHOW STATUS LIKE '%innodb%';