Site logo
Loading...
Tác giả
  • avatar Nguyễn Đức Xinh
    Name
    Nguyễn Đức Xinh
    Twitter
Ngày xuất bản
Ngày xuất bản

MySQL INFORMATION_SCHEMA: Công Cụ Không Thể Thiếu Khi Audit Database

Khi làm việc với MySQL, chúng ta thường sử dụng các câu lệnh như:

SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM users;
SHOW CREATE TABLE users;
SHOW INDEX FROM users;

Những câu lệnh này rất tiện khi kiểm tra nhanh một table.

Tuy nhiên, khi cần investigate một database lớn, tìm kiếm trên hàng trăm table, kiểm tra relationship, foreign key, index, charset/collation, thống kê số lượng record hoặc audit schema, INFORMATION_SCHEMA trở thành một công cụ cực kỳ hữu ích.

INFORMATION_SCHEMA là một metadata database được MySQL cung cấp, chứa thông tin mô tả về database, table, column, index, constraint, privilege, partition...

Nói đơn giản:

Data nằm trong table của application. Metadata về những table đó nằm trong INFORMATION_SCHEMA.

INFORMATION_SCHEMA là gì?

INFORMATION_SCHEMA là một database đặc biệt của MySQL, cung cấp thông tin siêu dữ liệu (metadata) về cấu trúc của cơ sở dữ liệu

Ví dụ:

SELECT *
FROM INFORMATION_SCHEMA.TABLES;

hoặc:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS;

Các bảng trong INFORMATION_SCHEMA không phải là application tables thông thường.

Chúng cung cấp thông tin về cấu trúc database.

Một số bảng quan trọng:

INFORMATION_SCHEMA Dùng để
SCHEMATA Database / schema
TABLES Table và thông tin table
COLUMNS Column / field
STATISTICS Index
KEY_COLUMN_USAGE Key và constraint
TABLE_CONSTRAINTS Constraint
REFERENTIAL_CONSTRAINTS Foreign key relationship
VIEWS View
ROUTINES Procedure / Function
TRIGGERS Trigger
PARTITIONS Partition

Trong thực tế, 5 nhóm thường dùng nhất là:

SCHEMATA
TABLES
COLUMNS
STATISTICS
KEY_COLUMN_USAGE

Xem danh sách Database

Có thể sử dụng:

SHOW DATABASES;

Nhưng nếu muốn query metadata:

SELECT
    SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE '%app%';
ORDER BY SCHEMA_NAME;

Xem danh sách Table

SHOW TABLES;

Nhưng với INFORMATION_SCHEMA:

SELECT
    TABLE_NAME,
    TABLE_TYPE,
    ENGINE,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    CREATE_TIME,
    UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME;
  • INFORMATION_SCHEMA.TABLES chứa khá nhiều thông tin hữu ích như: table type, storage engine, estimated row count, data size, index size,..
  • TABLE_SCHEMA = DATABASE() để chỉ lấy table của database hiện tại, bạn có thể chỉ định database cụ thể.

Lưu ý:

  • Đối với InnoDB, TABLE_ROWS thường là Approximate/ estimated row count chứ không phải absolute count. Nếu cần số lượng chính xác bạn cần dùng SELECT COUNT(*) FROM users; Tuy nhiên, COUNT(*) trên table rất lớn có thể tốn resource và thời gian.

Tìm Table có nhiều record nhất

SELECT
    TABLE_NAME,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_ROWS DESC
LIMIT 20;

Rất hữu ích khi cần:

  • tìm table lớn
  • migration planning
  • backup analysis
  • performance investigation
  • data cleanup
  • estimate migration time

Xem Columns

Đây là một trong những tác vụ phổ biến nhất.

SELECT
    TABLE_NAME,
    ORDINAL_POSITION,
    COLUMN_NAME,
    DATA_TYPE,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND COLUMN_NAME LIKE '%flag%'
ORDER BY
    TABLE_NAME,
    ORDINAL_POSITION;

Thêm COLUMN_NAME LIKE '%flag%' để tìm tất cả field có tên chứa flag:

Đây là kỹ thuật rất hữu ích khi:

  • legacy investigation
  • migration
  • tìm field liên quan đến status
  • tìm soft delete
  • tìm flag logic
  • mapping database cũ → database mới

Ngoài ra có thể tìm column theo: DATA_TYPE, CHARACTER_MAXIMUM_LENGTH

Charset và Collation

Đây là một trong những use case rất quan trọng khi làm việc với Japanese data.

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
-- Điều kiện hữu ích để kiểm tra COLLATION
-- AND DATA_TYPE IN ('varchar', 'char', 'text') AND COLLATION_NAME <> 'utf8mb4_general_ci';
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Điều này đặc biệt hữu ích khi investigate:

  • Japanese text
  • kana
  • full-width / half-width
  • dakuten / handakuten
  • string comparison
  • JOIN không match như mong đợi
  • migration giữa database
  • collation conflict

Ví dụ một JOIN có thể gặp vấn đề khi hai column sử dụng collation khác nhau:

utf8mb4_unicode_ci
        VS
utf8mb4_general_ci

Kiểm tra Charset / Collation của Table

Không chỉ column, có thể kiểm tra default charset/collation của table:

SELECT
    TABLE_NAME,
    TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME;

Tìm Table có Foreign Key tới một Table

Đây là một trong những use case cực kỳ hữu ích.

Ví dụ muốn biết:

Table nào đang reference users?

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND REFERENCED_TABLE_NAME = 'users';

Kết quả có thể như:

orders       user_id       fk_orders_user
comments     user_id       fk_comments_user
user_roles   user_id       fk_user_roles_user

Từ đó có thể biết:

users
 ├── orders
 ├── comments
 └── user_roles

Foreign Key

muốn biết:

orders đang reference những table nào?

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'orders'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

Ví dụ:

orders.user_id
    → users.id

orders.shop_id
    → shops.id

Table nào đang reference users?

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND REFERENCED_TABLE_NAME = 'users';

Đây là query rất hữu ích khi cần biết impact/dependency trước khi thay đổi hoặc xoá một table.

Tìm toàn bộ Foreign Key Relationship

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY
    TABLE_NAME,
    COLUMN_NAME;

Đây gần như là một database relationship map dạng text.

Rất hữu ích khi:

  • đọc legacy database
  • migration
  • reverse engineering
  • tìm dependency
  • chuẩn bị DROP table
  • phân tích impact khi thay đổi column

Primary Key

Có thể dùng để kiểm tra nhanh cấu trúc Primary Key của toàn database.

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND CONSTRAINT_NAME = 'PRIMARY'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Tìm Table không có Primary Key: Đây là một query rất hữu ích trong database audit.

SELECT
    t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    ON tc.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND tc.TABLE_NAME = t.TABLE_NAME
    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_TYPE = 'BASE TABLE'
  AND tc.TABLE_NAME IS NULL
ORDER BY t.TABLE_NAME;

Index

Một trong những metadata quan trọng nhất là:

INFORMATION_SCHEMA.STATISTICS

Xem tất cả index hoặc index của 1 table :

SELECT
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    NON_UNIQUE,
    SEQ_IN_INDEX,
    INDEX_TYPE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
-- AND TABLE_NAME = 'orders'
---- Tìm Index chứa một Column, hữu ích khi muốn biết Field này đang được index ở những table nào?
ORDER BY
    TABLE_NAME,
    INDEX_NAME,
    SEQ_IN_INDEX;
  • NON_UNIQUE = 0: nghĩa là index không cho phép duplicate values.
  • COLUMN_NAME = 'user_id';: Ví dụ tìm tất cả index có user_id:

Table Size

Có thể estimate kích thước data và index:

SELECT
    TABLE_NAME,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS DATA_MB,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS INDEX_MB,
    ROUND(
        (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
        2
    ) AS TOTAL_MB
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TOTAL_MB DESC

Rất hữu ích khi:

  • migration
  • backup
  • RDS sizing
  • disk planning
  • performance investigation

View / Procedure / Function / Trigger

Tìm View

SELECT
    TABLE_NAME,
    VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME;

Stored Procedure / Function

SELECT
    ROUTINE_NAME,
    ROUTINE_TYPE,
    DATA_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
-- -- Có thể tìm riêng procedure:
-- WHERE ROUTINE_TYPE = 'PROCEDURE';
-- -- Có thể tìm riêng function:
-- WHERE ROUTINE_TYPE = 'FUNCTION';
ORDER BY ROUTINE_TYPE, ROUTINE_NAME;

Tìm Trigger

SELECT
    TRIGGER_NAME,
    EVENT_OBJECT_TABLE,
    EVENT_MANIPULATION,
    ACTION_TIMING
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
ORDER BY EVENT_OBJECT_TABLE;

Kiểm tra Partition

Nếu database sử dụng partition:

SELECT
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_METHOD,
    PARTITION_EXPRESSION,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND PARTITION_NAME IS NOT NULL
ORDER BY TABLE_NAME, PARTITION_ORDINAL_POSITION;

Phát hiện Column cùng tên nhưng khác Data Type

Đây là một kỹ thuật khá hữu ích khi audit legacy database.
Ví dụ:

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    COLUMN_TYPE,
    COUNT(*) AS CNT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
GROUP BY
    COLUMN_NAME,
    DATA_TYPE,
    COLUMN_TYPE
ORDER BY
    COLUMN_NAME,
    CNT DESC;

Ví dụ phát hiện:

update_userid

varchar(20)
varchar(50)
int
bigint

Điều này có thể là dấu hiệu database chưa được standardize.

Tìm Column có cùng tên nhưng khác Collation

Đặc biệt hữu ích với Japanese system:

SELECT
    COLUMN_NAME,
    CHARACTER_SET_NAME,
    COLLATION_NAME,
    COUNT(*) AS CNT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND CHARACTER_SET_NAME IS NOT NULL
GROUP BY
    COLUMN_NAME,
    CHARACTER_SET_NAME,
    COLLATION_NAME
ORDER BY COLUMN_NAME;

Có thể phát hiện:

name
 ├── utf8mb4_unicode_ci
 └── utf8mb4_general_ci

Đây là dấu hiệu cần review nếu những column này thường xuyên được JOIN hoặc compare với nhau.

INFORMATION_SCHEMA vs SHOW

Hai cách tiếp cận đều hữu ích.

SHOW

SHOW TABLES;

SHOW COLUMNS FROM users;

SHOW INDEX FROM users;

SHOW CREATE TABLE users;

Phù hợp khi:

  • kiểm tra nhanh
  • làm việc thủ công
  • debug một table cụ thể

INFORMATION_SCHEMA

SELECT ...
FROM INFORMATION_SCHEMA.TABLES;

SELECT ...
FROM INFORMATION_SCHEMA.COLUMNS;

SELECT ...
FROM INFORMATION_SCHEMA.STATISTICS;

Phù hợp khi:

  • cần search toàn database
  • cần filter
  • cần JOIN metadata
  • cần report
  • cần audit
  • cần automation
  • cần migration analysis

Kết luận

INFORMATION_SCHEMA đặc biệt hữu ích khi không biết chính xác database đang được thiết kế như thế nào.

Thay vì mở từng table và kiểm tra thủ công, chúng ta có thể query metadata để trả lời nhanh các câu hỏi như:

Có những table nào?

Table này có những column nào?

Field này xuất hiện ở những table nào?

Table nào đang reference table này?

Column này có index không?

Database đang dùng charset/collation nào?

Table nào lớn nhất?

Table nào không có Primary Key?

Đặc biệt trong legacy investigation, database migration, schema audit và performance investigation, INFORMATION_SCHEMA gần như là một trong những công cụ đầu tiên nên sử dụng.