MySQL 特定语法

概述

MySQL 是世界上最流行的开源关系型数据库管理系统之一，广泛应用于Web应用程序开发。本章节详细介绍MySQL的特定语法、功能特性和最佳实践。

MySQL 数据类型

1. 数值类型

整数类型

-- 整数类型及其范围
CREATE TABLE numeric_types (
    tiny_col TINYINT,           -- -128 到 127
    tiny_unsigned TINYINT UNSIGNED,  -- 0 到 255
    small_col SMALLINT,         -- -32,768 到 32,767
    medium_col MEDIUMINT,       -- -8,388,608 到 8,388,607
    int_col INT,                -- -2,147,483,648 到 2,147,483,647
    big_col BIGINT,             -- -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807
    
    -- 自动填充零
    zero_fill_col INT(10) ZEROFILL,
    
    -- 自增主键
    id BIGINT AUTO_INCREMENT PRIMARY KEY
);

浮点和定点类型

CREATE TABLE decimal_types (
    float_col FLOAT(7,4),       -- 单精度浮点
    double_col DOUBLE(15,8),    -- 双精度浮点
    decimal_col DECIMAL(10,2),  -- 定点数，精确计算
    numeric_col NUMERIC(12,4)   -- 等同于DECIMAL
);

-- 示例数据
INSERT INTO decimal_types VALUES
(123.4567, 123.45678901, 12345.67, 12345.6789);

2. 字符串类型

CREATE TABLE string_types (
    -- 定长字符串
    char_col CHAR(10),          -- 固定长度，最大255字符
    
    -- 变长字符串
    varchar_col VARCHAR(255),   -- 可变长度，最大65,535字符
    
    -- 文本类型
    tiny_text TINYTEXT,         -- 最大255字符
    text_col TEXT,              -- 最大65,535字符
    medium_text MEDIUMTEXT,     -- 最大16,777,215字符
    long_text LONGTEXT,         -- 最大4,294,967,295字符
    
    -- 二进制类型
    binary_col BINARY(16),      -- 固定长度二进制
    varbinary_col VARBINARY(255), -- 可变长度二进制
    blob_col BLOB,              -- 二进制大对象
    
    -- 枚举和集合
    enum_col ENUM('small', 'medium', 'large'),
    set_col SET('read', 'write', 'execute')
);

-- 字符集和排序规则
CREATE TABLE utf8_table (
    id INT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. 日期时间类型

CREATE TABLE datetime_types (
    date_col DATE,              -- YYYY-MM-DD
    time_col TIME,              -- HH:MM:SS
    datetime_col DATETIME,      -- YYYY-MM-DD HH:MM:SS
    timestamp_col TIMESTAMP,    -- 时间戳，自动更新
    year_col YEAR,              -- YYYY
    
    -- 带微秒的时间类型
    datetime_micro DATETIME(6), -- 支持微秒精度
    timestamp_micro TIMESTAMP(6),
    
    -- 自动时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 时间函数示例
SELECT 
    NOW() as current_datetime,
    CURDATE() as current_date,
    CURTIME() as current_time,
    UNIX_TIMESTAMP() as unix_timestamp,
    FROM_UNIXTIME(1609459200) as from_unix,
    DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') as formatted_date;

4. JSON 类型

-- MySQL 5.7+ 支持原生JSON类型
CREATE TABLE json_example (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_profile JSON,
    settings JSON,
    metadata JSON
);

-- 插入JSON数据
INSERT INTO json_example (user_profile, settings) VALUES
('{"name": "张三", "age": 30, "city": "北京"}', 
 '{"theme": "dark", "language": "zh-CN", "notifications": true}');

-- JSON查询和操作
SELECT 
    id,
    JSON_EXTRACT(user_profile, '$.name') as user_name,
    user_profile->>'$.age' as user_age,
    JSON_EXTRACT(settings, '$.theme') as theme
FROM json_example;

-- JSON函数
SELECT 
    JSON_KEYS(user_profile) as profile_keys,
    JSON_LENGTH(settings) as settings_count,
    JSON_VALID(user_profile) as is_valid_json
FROM json_example;

-- 更新JSON字段
UPDATE json_example 
SET user_profile = JSON_SET(user_profile, '$.age', 31, '$.email', 'zhangsan@example.com')
WHERE id = 1;

-- JSON索引
ALTER TABLE json_example 
ADD COLUMN user_name VARCHAR(100) GENERATED ALWAYS AS (user_profile->>'$.name') STORED,
ADD INDEX idx_user_name (user_name);

MySQL 特有功能

1. 存储引擎

-- 查看可用存储引擎
SHOW ENGINES;

-- InnoDB 表（默认，支持事务、外键）
CREATE TABLE innodb_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- MyISAM 表（快速读取，不支持事务）
CREATE TABLE myisam_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT,
    INDEX idx_content (content(100))
) ENGINE=MyISAM;

-- Memory 表（内存存储，重启后数据丢失）
CREATE TABLE memory_table (
    session_id VARCHAR(32) PRIMARY KEY,
    user_id INT,
    last_activity TIMESTAMP,
    INDEX idx_user (user_id)
) ENGINE=MEMORY;

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'innodb_table';

2. 分区表

-- 范围分区
CREATE TABLE sales_range (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 哈希分区
CREATE TABLE user_hash (
    id INT NOT NULL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
)
PARTITION BY HASH(id)
PARTITIONS 4;

-- 列表分区
CREATE TABLE sales_region (
    id INT NOT NULL,
    region VARCHAR(20),
    amount DECIMAL(10,2),
    PRIMARY KEY (id, region)
)
PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('北京', '天津', '河北'),
    PARTITION p_south VALUES IN ('广东', '广西', '海南'),
    PARTITION p_east VALUES IN ('上海', '江苏', '浙江'),
    PARTITION p_west VALUES IN ('四川', '重庆', '云南')
);

-- 分区管理
-- 添加分区
ALTER TABLE sales_range ADD PARTITION (
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- 删除分区
ALTER TABLE sales_range DROP PARTITION p2020;

-- 查看分区信息
SELECT 
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_ORDINAL_POSITION,
    PARTITION_METHOD,
    PARTITION_EXPRESSION,
    PARTITION_DESCRIPTION,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'sales_range';

3. 全文索引

-- 创建全文索引表
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    author VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 全文索引
    FULLTEXT KEY ft_title_content (title, content),
    FULLTEXT KEY ft_content (content)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO articles (title, content, author) VALUES
('MySQL 全文搜索', 'MySQL 提供了强大的全文搜索功能，支持自然语言搜索和布尔搜索模式。', '张三'),
('数据库优化技巧', '数据库性能优化是一个复杂的主题，涉及索引、查询优化、硬件配置等多个方面。', '李四'),
('SQL 高级查询', 'SQL 高级查询包括子查询、连接查询、窗口函数等复杂操作。', '王五');

-- 自然语言全文搜索
SELECT id, title, 
       MATCH(title, content) AGAINST('MySQL 搜索') as relevance
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 搜索')
ORDER BY relevance DESC;

-- 布尔模式全文搜索
SELECT id, title
FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -优化' IN BOOLEAN MODE);

-- 查询扩展模式
SELECT id, title
FROM articles
WHERE MATCH(title, content) AGAINST('数据库' WITH QUERY EXPANSION);

-- 全文索引配置
-- 设置最小词长度
SET GLOBAL ft_min_word_len = 2;

-- 查看全文索引状态
SHOW VARIABLES LIKE 'ft_%';

4. 触发器

-- 创建审计表
CREATE TABLE user_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_values JSON,
    new_values JSON,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入触发器
DELIMITER //
CREATE TRIGGER user_insert_audit
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, new_values, changed_by)
    VALUES (NEW.id, 'INSERT', 
            JSON_OBJECT('username', NEW.username, 'email', NEW.email, 'status', NEW.status),
            USER());
END //
DELIMITER ;

-- 更新触发器
DELIMITER //
CREATE TRIGGER user_update_audit
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, old_values, new_values, changed_by)
    VALUES (NEW.id, 'UPDATE',
            JSON_OBJECT('username', OLD.username, 'email', OLD.email, 'status', OLD.status),
            JSON_OBJECT('username', NEW.username, 'email', NEW.email, 'status', NEW.status),
            USER());
END //
DELIMITER ;

-- 删除触发器
DELIMITER //
CREATE TRIGGER user_delete_audit
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, old_values, changed_by)
    VALUES (OLD.id, 'DELETE',
            JSON_OBJECT('username', OLD.username, 'email', OLD.email, 'status', OLD.status),
            USER());
END //
DELIMITER ;

-- 查看触发器
SHOW TRIGGERS LIKE 'users';

-- 删除触发器
-- DROP TRIGGER user_insert_audit;

5. 存储过程和函数

-- 存储过程示例
DELIMITER //
CREATE PROCEDURE GetUserStats(
    IN start_date DATE,
    IN end_date DATE,
    OUT total_users INT,
    OUT active_users INT,
    OUT new_users INT
)
BEGIN
    -- 总用户数
    SELECT COUNT(*) INTO total_users FROM users;
    
    -- 活跃用户数
    SELECT COUNT(*) INTO active_users 
    FROM users 
    WHERE status = 'active';
    
    -- 新注册用户数
    SELECT COUNT(*) INTO new_users
    FROM users
    WHERE DATE(created_at) BETWEEN start_date AND end_date;
END //
DELIMITER ;

-- 调用存储过程
CALL GetUserStats('2023-01-01', '2023-12-31', @total, @active, @new);
SELECT @total as total_users, @active as active_users, @new as new_users;

-- 函数示例
DELIMITER //
CREATE FUNCTION CalculateAge(birth_date DATE)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE age INT;
    SET age = TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
    RETURN age;
END //
DELIMITER ;

-- 使用函数
SELECT username, CalculateAge('1990-05-15') as age;

-- 递归函数（MySQL 8.0+）
DELIMITER //
CREATE FUNCTION Factorial(n INT)
RETURNS BIGINT
READS SQL DATA
DETERMINISTIC
BEGIN
    IF n <= 1 THEN
        RETURN 1;
    ELSE
        RETURN n * Factorial(n - 1);
    END IF;
END //
DELIMITER ;

SELECT Factorial(5) as result; -- 返回 120

MySQL 8.0 新特性

1. 窗口函数

-- 创建销售数据表
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    salesperson VARCHAR(50),
    region VARCHAR(50),
    sale_amount DECIMAL(10,2),
    sale_date DATE
);

-- 插入测试数据
INSERT INTO sales (salesperson, region, sale_amount, sale_date) VALUES
('张三', '北区', 1000.00, '2023-01-15'),
('李四', '南区', 1500.00, '2023-01-20'),
('王五', '北区', 1200.00, '2023-02-10'),
('张三', '北区', 800.00, '2023-02-15'),
('李四', '南区', 2000.00, '2023-03-05');

-- 窗口函数示例
SELECT 
    salesperson,
    region,
    sale_amount,
    sale_date,
    
    -- 排名函数
    ROW_NUMBER() OVER (ORDER BY sale_amount DESC) as row_num,
    RANK() OVER (ORDER BY sale_amount DESC) as rank_num,
    DENSE_RANK() OVER (ORDER BY sale_amount DESC) as dense_rank_num,
    
    -- 分区排名
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_amount DESC) as region_rank,
    
    -- 聚合窗口函数
    SUM(sale_amount) OVER (PARTITION BY region) as region_total,
    AVG(sale_amount) OVER (PARTITION BY salesperson) as person_avg,
    
    -- 移动窗口
    SUM(sale_amount) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as moving_sum,
    
    -- 累计求和
    SUM(sale_amount) OVER (
        ORDER BY sale_date 
        ROWS UNBOUNDED PRECEDING
    ) as cumulative_sum,
    
    -- 前后值比较
    LAG(sale_amount, 1) OVER (ORDER BY sale_date) as prev_amount,
    LEAD(sale_amount, 1) OVER (ORDER BY sale_date) as next_amount,
    
    -- 首尾值
    FIRST_VALUE(sale_amount) OVER (
        PARTITION BY region ORDER BY sale_date
    ) as first_sale,
    LAST_VALUE(sale_amount) OVER (
        PARTITION BY region ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as last_sale
    
FROM sales
ORDER BY sale_date;

2. 公共表表达式 (CTE)

-- 简单CTE
WITH regional_sales AS (
    SELECT 
        region,
        SUM(sale_amount) as total_sales,
        COUNT(*) as sale_count
    FROM sales
    GROUP BY region
)
SELECT 
    region,
    total_sales,
    sale_count,
    total_sales / sale_count as avg_sale
FROM regional_sales
WHERE total_sales > 2000;

-- 递归CTE - 组织结构
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

INSERT INTO employees VALUES
(1, '总经理', NULL),
(2, '销售总监', 1),
(3, '技术总监', 1),
(4, '销售经理', 2),
(5, '开发经理', 3),
(6, '销售员工', 4),
(7, '开发员工', 5);

-- 递归查询组织层级
WITH RECURSIVE employee_hierarchy AS (
    -- 锚点：顶级管理者
    SELECT id, name, manager_id, 0 as level, CAST(name AS CHAR(1000)) as path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归：下级员工
    SELECT e.id, e.name, e.manager_id, eh.level + 1, 
           CONCAT(eh.path, ' -> ', e.name)
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT 
    CONCAT(REPEAT('  ', level), name) as hierarchy,
    level,
    path
FROM employee_hierarchy
ORDER BY path;

3. 角色和权限管理

-- 创建角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 为角色分配权限
GRANT SELECT ON myapp.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_write';
GRANT ALL PRIVILEGES ON myapp.* TO 'app_admin';

-- 创建用户并分配角色
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'password123';
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password456';
CREATE USER 'admin_user'@'%' IDENTIFIED BY 'password789';

GRANT 'app_read' TO 'readonly_user'@'%';
GRANT 'app_write' TO 'app_user'@'%';
GRANT 'app_admin' TO 'admin_user'@'%';

-- 设置默认角色
SET DEFAULT ROLE 'app_read' TO 'readonly_user'@'%';
SET DEFAULT ROLE 'app_write' TO 'app_user'@'%';
SET DEFAULT ROLE 'app_admin' TO 'admin_user'@'%';

-- 查看角色和权限
SHOW GRANTS FOR 'app_read';
SELECT * FROM mysql.role_edges;
SELECT * FROM mysql.default_roles;

性能优化

1. 索引优化

-- 复合索引
CREATE INDEX idx_user_status_created ON users (status, created_at);

-- 前缀索引
CREATE INDEX idx_email_prefix ON users (email(10));

-- 函数索引（MySQL 8.0+）
CREATE INDEX idx_upper_username ON users ((UPPER(username)));

-- 不可见索引（MySQL 8.0+）
CREATE INDEX idx_temp ON users (email) INVISIBLE;

-- 降序索引（MySQL 8.0+）
CREATE INDEX idx_created_desc ON users (created_at DESC);

-- 查看索引使用情况
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    CARDINALITY
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- 分析索引效果
EXPLAIN FORMAT=JSON
SELECT * FROM users 
WHERE status = 'active' 
AND created_at > '2023-01-01'
ORDER BY created_at DESC;

2. 查询优化

-- 使用EXPLAIN分析查询
EXPLAIN 
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.username
HAVING order_count > 5;

-- 优化子查询为JOIN
-- 慢查询
SELECT * FROM users 
WHERE id IN (
    SELECT user_id FROM orders 
    WHERE order_date > '2023-01-01'
);

-- 优化后
SELECT DISTINCT u.* 
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.order_date > '2023-01-01';

-- 使用LIMIT优化大结果集
SELECT * FROM large_table 
WHERE condition = 'value'
ORDER BY id
LIMIT 1000;

-- 分页优化
-- 传统分页（性能差）
SELECT * FROM users ORDER BY id LIMIT 10000, 20;

-- 优化分页
SELECT * FROM users 
WHERE id > 10000 
ORDER BY id 
LIMIT 20;

3. 配置优化

-- 查看重要配置参数
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'query_cache%';
SHOW VARIABLES LIKE 'slow_query_log';

-- 查看状态信息
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Slow_queries';

-- 性能监控查询
SELECT 
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000 as total_time_sec,
    AVG_TIMER_WAIT/1000000000 as avg_time_sec
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

备份与恢复

1. 逻辑备份

# 完整数据库备份
mysqldump -u root -p --single-transaction --routines --triggers mydb > mydb_backup.sql

# 只备份结构
mysqldump -u root -p --no-data mydb > mydb_structure.sql

# 只备份数据
mysqldump -u root -p --no-create-info mydb > mydb_data.sql

# 备份特定表
mysqldump -u root -p mydb users orders > tables_backup.sql

# 压缩备份
mysqldump -u root -p --single-transaction mydb | gzip > mydb_backup.sql.gz

2. 物理备份

# 使用MySQL Enterprise Backup
mysqlbackup --user=root --password --backup-dir=/backup/full backup

# 增量备份
mysqlbackup --user=root --password --backup-dir=/backup/inc1 \
  --incremental --incremental-base=dir:/backup/full backup

# 恢复
mysqlbackup --backup-dir=/backup/full copy-back

3. 二进制日志

-- 启用二进制日志
-- 在my.cnf中添加：
-- log-bin=mysql-bin
-- server-id=1

-- 查看二进制日志
SHOW BINARY LOGS;
SHOW MASTER STATUS;

-- 查看二进制日志内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 基于时间点恢复
-- mysqlbinlog --start-datetime="2023-01-01 10:00:00" \
--            --stop-datetime="2023-01-01 11:00:00" \
--            mysql-bin.000001 | mysql -u root -p

-- 刷新日志
FLUSH LOGS;

-- 清理旧日志
PURGE BINARY LOGS BEFORE '2023-01-01';

监控与诊断

1. 慢查询日志

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 查看慢查询设置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 分析慢查询
-- 使用mysqldumpslow工具
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

2. Performance Schema

-- 启用Performance Schema
-- 在my.cnf中添加：performance_schema = ON

-- 查看最耗时的语句
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000 as total_time,
    AVG_TIMER_WAIT/1000000000 as avg_time,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看表访问统计
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_FETCH,
    SUM_TIMER_WAIT/1000000000 as total_time
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY SUM_TIMER_WAIT DESC;

-- 查看索引使用情况
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 NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY COUNT_FETCH DESC;

最佳实践

1. 表设计

• 选择合适的存储引擎（InnoDB用于事务，MyISAM用于只读）
• 使用适当的数据类型（避免过大的字段）
• 合理设计索引（不要过多，注意复合索引顺序）
• 使用外键约束保证数据完整性

2. 查询优化

• 避免SELECT *，只查询需要的字段
• 合理使用索引，避免全表扫描
• 优化JOIN查询，小表驱动大表
• 使用LIMIT限制结果集大小

3. 安全配置

• 定期更新MySQL版本
• 使用强密码策略
• 限制网络访问
• 定期备份数据
• 监控异常访问

4. 性能监控

• 监控慢查询日志
• 定期检查索引使用情况
• 监控连接数和缓冲池命中率
• 设置合理的配置参数

通过掌握这些MySQL特定的语法和功能，可以更好地利用MySQL的强大特性，构建高性能、可靠的数据库应用。