MySQL命令与高效使用指南

一、数据库生命周期管理

1.1 数据库创建规范

-- 基础创建语句
CREATE DATABASE shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 带条件判断的创建
CREATE DATABASE IF NOT EXISTS shop 
DEFAULT CHARSET=utf8mb4 
DEFAULT COLLATE utf8mb4_unicode_ci;

字符集选择建议：优先使用utf8mb4（支持emoji和更多unicode字符），排序规则根据业务需求选择通用型（如unicode_ci）或精确型（如bin）

1.2 数据库删除防护

-- 安全删除方式
DROP DATABASE IF EXISTS old_shop;

-- 高危操作防护（需配合权限控制）
REVOKE DROP ON *.* FROM 'dev_user'@'%';

1.3 数据库切换策略

-- 显式指定数据库
USE production_db;

-- 临时跨库查询
SELECT * FROM shop.orders 
JOIN inventory.products 
ON orders.product_id = products.id;

二、表结构设计实践

2.1 表创建模板

CREATE TABLE employees (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    emp_no VARCHAR(10) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    department ENUM('IT','HR','Finance') NOT NULL,
    salary DECIMAL(10,2) CHECK (salary > 0),
    join_date DATE DEFAULT (CURRENT_DATE),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_department (department),
    FULLTEXT INDEX idx_fullname (full_name)
) ENGINE=InnoDB 
ROW_FORMAT=DYNAMIC
COMMENT '员工信息主表';

2.2 表结构变更规范

-- 添加字段（带位置控制）
ALTER TABLE employees 
ADD COLUMN mobile VARCHAR(15) 
AFTER full_name;

-- 修改字段（类型变更防护）
ALTER TABLE employees 
MODIFY COLUMN salary DECIMAL(12,2) 
CHECK (salary BETWEEN 3000 AND 100000);

-- 删除字段（安全检查）
ALTER TABLE employees 
DROP COLUMN obsolete_field,
ALGORITHM=INPLACE, LOCK=NONE;

2.3 表维护操作

-- 表优化（碎片整理）
OPTIMIZE TABLE large_table;

-- 表修复（异常恢复）
REPAIR TABLE corrupted_table;

-- 表分析（统计信息更新）
ANALYZE TABLE important_table;

三、数据操作全指南

3.1 插入数据技巧

-- 多值插入
INSERT INTO products (name, price) VALUES 
('Keyboard', 99.9),
('Mouse', 59.9),
('Monitor', 899.0);

-- 条件插入
INSERT INTO archive_orders 
SELECT * FROM current_orders 
WHERE order_date < '2020-01-01';

-- 忽略重复
INSERT IGNORE INTO unique_users (email) 
VALUES ('test@example.com');

3.2 更新数据策略

-- 关联更新
UPDATE orders o
JOIN products p ON o.product_id = p.id
SET o.total_price = o.quantity * p.price
WHERE o.status = 'pending';

-- 条件限制更新
UPDATE user_balance 
SET balance = balance + 100 
WHERE user_id = 123 
LIMIT 1;

-- 带子查询的更新
UPDATE employees 
SET salary = salary * 1.05 
WHERE department IN (
    SELECT department 
    FROM high_performance_depts
);

3.3 删除数据安全方案

-- 软删除模式
UPDATE customers 
SET deleted_at = NOW() 
WHERE id = 456;

-- 分批次删除
DELETE FROM log_records 
WHERE created_at < '2020-01-01' 
LIMIT 1000;

-- 级联删除（需外键支持）
DELETE FROM departments 
WHERE id = 5 
CASCADE;

四、查询优化技巧

4.1 基础查询增强

-- 分页优化（避免OFFSET）
SELECT * FROM articles 
WHERE id > 1000 
ORDER BY id ASC 
LIMIT 10;

-- JSON数据处理
SELECT user_id, 
       JSON_EXTRACT(profile, '$.address.city') AS city 
FROM user_profiles 
WHERE JSON_CONTAINS(profile, '"Beijing"', '$.address.city');

4.2 复杂查询模式

-- 递归查询（CTE）
WITH RECURSIVE org_tree AS (
    SELECT id, name, parent_id 
    FROM departments 
    WHERE id = 1
    UNION ALL
    SELECT d.id, d.name, d.parent_id 
    FROM departments d
    INNER JOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree;

-- 窗口函数应用
SELECT 
    employee_id,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    AVG(salary) OVER (PARTITION BY job_title) AS avg_job_salary
FROM employees;

五、索引深度优化

5.1 索引创建策略

-- 组合索引优化
ALTER TABLE orders 
ADD INDEX idx_status_date (status, order_date);

-- 前缀索引
ALTER TABLE products 
ADD INDEX idx_name (name(20));

-- 函数索引（MySQL 8.0+）
ALTER TABLE users 
ADD INDEX idx_email_lower ((LOWER(email)));

5.2 索引分析工具

-- 执行计划解读
EXPLAIN FORMAT=JSON 
SELECT * FROM orders 
WHERE user_id = 123 
AND total_price > 1000;

-- 索引使用统计
SELECT 
    object_type,
    object_schema,
    object_name,
    index_name,
    count_read,
    count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage;

六、事务与锁机制

6.1 事务控制实例

START TRANSACTION;

UPDATE accounts 
SET balance = balance - 100 
WHERE user_id = 1;

UPDATE accounts 
SET balance = balance + 100 
WHERE user_id = 2;

COMMIT;

-- 异常处理
BEGIN;
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
    ROLLBACK;
    RESIGNAL;
END;

-- 业务逻辑操作
COMMIT;

6.2 锁机制应用

-- 行级锁选择
SELECT * FROM products 
WHERE id = 10 
FOR UPDATE;

-- 锁等待设置
SET SESSION innodb_lock_wait_timeout = 30;

-- 死锁分析
SHOW ENGINE INNODB STATUS;

七、存储引擎对比

7.1 InnoDB最佳实践

-- 表空间管理
SET GLOBAL innodb_file_per_table = ON;

-- 缓冲池优化
SET GLOBAL innodb_buffer_pool_size = 8G;

-- 日志配置
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

7.2 MyISAM适用场景

-- 全文索引使用
SELECT * FROM documents 
WHERE MATCH(content) AGAINST('MySQL optimization' IN NATURAL LANGUAGE MODE);

-- 修复表操作
REPAIR TABLE myisam_table;

八、备份与恢复方案

8.1 逻辑备份

# 全库备份
mysqldump --single-transaction -uroot -p shop > shop_full.sql

# 增量备份（配合binlog）
mysqlbinlog --start-datetime="2024-01-01 00:00:00" binlog.00001 > incr.sql

8.2 物理备份

# XtraBackup使用
innobackupex --user=root --password=secret /backup/

九、安全加固措施

9.1 用户权限管理

-- 最小权限原则
CREATE USER 'reports'@'192.168.1.%' IDENTIFIED BY 'SecurePass123!';

GRANT SELECT ON shop.sales_data TO 'reports'@'192.168.1.%';

-- 定期权限审查
SELECT * FROM mysql.user WHERE User='reports';

9.2 审计与监控

-- 开启通用日志
SET GLOBAL general_log = 'ON';

-- 性能监控
SELECT * FROM sys.session 
WHERE command != 'Sleep';

十、高级功能应用

10.1 存储过程开发

DELIMITER $$
CREATE PROCEDURE ProcessOrder(IN order_id INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        SELECT 'Error occurred' AS result;
    END;

    START TRANSACTION;
    
    UPDATE inventory 
    SET stock = stock - 1 
    WHERE product_id = (
        SELECT product_id 
        FROM orders 
        WHERE id = order_id
    );
    
    UPDATE orders 
    SET status = 'completed' 
    WHERE id = order_id;
    
    COMMIT;
    
    SELECT 'Order processed' AS result;
END$$
DELIMITER ;

10.2 触发器应用场景

CREATE TRIGGER update_product_stats 
AFTER INSERT ON order_items 
FOR EACH ROW 
BEGIN
    UPDATE product_stats 
    SET total_sold = total_sold + NEW.quantity,
        last_sold = NOW() 
    WHERE product_id = NEW.product_id;
END;

十一、性能调优实战

11.1 查询优化案例

-- 优化前
SELECT * FROM orders 
WHERE YEAR(order_date) = 2023 
AND status = 'shipped';

-- 优化后
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31' 
AND status = 'shipped';

11.2 配置参数调整

# my.cnf优化示例
[mysqld]
innodb_buffer_pool_size = 12G
innodb_log_file_size = 2G
max_connections = 500
thread_cache_size = 100
query_cache_type = 0

十二、云数据库实践

12.1 连接管理

# SSL连接示例
mysql -h rds-instance.region.rds.amazonaws.com \
-u admin -p \
--ssl-ca=global-bundle.pem

12.2 只读副本使用

-- 读写分离配置
/* 主库 */
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

/* 从库 */
SELECT balance FROM accounts WHERE id = 1;