MySQL命令与高效使用指南

发布时间：2025-03-20 04:21:43
本文热度：浏览 49 赞 0 评论 0
文章标签： MySQL SQL 数据库管理
全文共1字，阅读约需1分钟

一、数据库生命周期管理

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;

正文到此结束

所属分类：Mysql

本文链接： https://refblogs.com/article/1022
版权声明： 本文由老牛原创发布，转载或复制请以超链接形式转载,并注明出处搬砖的码农。

MySQL命令与高效使用指南

一、数据库生命周期管理

1.1 数据库创建规范

1.2 数据库删除防护

1.3 数据库切换策略

二、表结构设计实践

2.1 表创建模板

2.2 表结构变更规范

2.3 表维护操作

三、数据操作全指南

3.1 插入数据技巧

3.2 更新数据策略

3.3 删除数据安全方案

四、查询优化技巧

4.1 基础查询增强

4.2 复杂查询模式

五、索引深度优化

5.1 索引创建策略

5.2 索引分析工具

六、事务与锁机制

6.1 事务控制实例

6.2 锁机制应用

七、存储引擎对比

7.1 InnoDB最佳实践

7.2 MyISAM适用场景

八、备份与恢复方案

8.1 逻辑备份

8.2 物理备份

九、安全加固措施

9.1 用户权限管理

9.2 审计与监控

十、高级功能应用

10.1 存储过程开发

10.2 触发器应用场景

十一、性能调优实战

11.1 查询优化案例

11.2 配置参数调整

十二、云数据库实践

12.1 连接管理

12.2 只读副本使用

相关文章

热门推荐

标签云

本文目录