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%';