MySQL命令大全

发布时间：2025-03-20 09:53:09 (有修改)
本文热度：浏览 38 赞 0 评论 0
文章标签： MySQL 数据库 SQL
全文共1字，阅读约需1分钟

一、数据库生命周期管理

1.1 创建数据库

CREATE DATABASE shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

字符集选择建议：

utf8mb4支持完整Unicode（包括emoji）
校对规则根据业务需求选择：
- utf8mb4_general_ci：通用校对（性能优先）
- utf8mb4_unicode_ci：准确校对（准确性优先）
- utf8mb4_bin：二进制校对（区分大小写）

1.2 数据库修改

ALTER DATABASE shop CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

修改注意事项：

仅影响后续新建对象
已有表需单独修改
可能引起索引重建

1.3 数据库删除

DROP DATABASE IF EXISTS shop;

安全删除流程：

确认无活跃连接
执行备份检查
使用IF EXISTS避免错误
检查用户权限

1.4 数据库信息查询

SHOW DATABASES LIKE 'shop%';
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA;

二、表结构管理

2.1 创建表进阶示例

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL COMMENT '商品名称',
    price DECIMAL(10,2) CHECK (price > 0),
    stock INT UNSIGNED DEFAULT 0,
    category ENUM('电子','服装','食品') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_category (category),
    FULLTEXT INDEX ft_name (name),
    CONSTRAINT chk_stock CHECK (stock >= 0)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

关键设计要素：

主键选择：自增 vs UUID vs 业务主键
存储引擎选择：InnoDB vs MyISAM
字段约束：NOT NULL优先原则
注释规范管理
默认值设置策略

2.2 表结构修改实战

添加字段：

ALTER TABLE products 
ADD COLUMN description TEXT AFTER name,
ALGORITHM=INPLACE, LOCK=NONE;

修改字段：

ALTER TABLE products 
MODIFY COLUMN price DECIMAL(12,2) NOT NULL,
CHANGE COLUMN stock inventory INT UNSIGNED,
COMMENT '商品库存量';

在线DDL注意事项：

使用ALGORITHM和LOCK子句控制锁行为
大表修改建议使用pt-online-schema-change
字段顺序对存储的影响

2.3 表维护操作

优化表：

OPTIMIZE TABLE products;

分析表：

ANALYZE TABLE products;

碎片整理策略：

何时需要优化表
InnoDB的碎片管理机制
自动碎片整理配置

三、数据操作语言（DML）

3.1 插入数据最佳实践

批量插入：

INSERT INTO products (name, price, category)
VALUES 
('手机', 2999.00, '电子'),
('衬衫', 199.00, '服装'),
('蛋糕', 89.00, '食品')
ON DUPLICATE KEY UPDATE 
price = VALUES(price),
updated_at = NOW();

插入优化技巧：

使用LOAD DATA INFILE加速大数据导入
合理设置事务提交频率
禁用索引提高插入速度

3.2 更新操作深度优化

条件更新：

UPDATE products 
SET stock = stock - 1,
    updated_at = NOW()
WHERE id = 100
AND stock > 0;

安全更新策略：

启用safe-updates模式
使用LIMIT子句控制影响行数
基于版本的乐观锁实现

3.3 删除操作全解析

级联删除示例：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    product_id INT,
    FOREIGN KEY (product_id)
        REFERENCES products(id)
        ON DELETE CASCADE
);

DELETE FROM products WHERE id = 100;

删除优化方案：

分批删除大表数据
使用软删除模式
归档历史数据策略

四、查询优化大全

4.1 基础查询增强

SELECT 
    p.id AS product_id,
    p.name,
    FORMAT(p.price, 2) AS price,
    CASE 
        WHEN p.stock < 10 THEN '紧缺'
        ELSE '充足' 
    END AS stock_status
FROM products p
WHERE p.category = '电子'
    AND p.created_at BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY p.price DESC
LIMIT 10 OFFSET 0;

4.2 关联查询进阶

LEFT JOIN优化：

EXPLAIN 
SELECT o.order_no, p.name
FROM orders o
LEFT JOIN products p 
    ON o.product_id = p.id
WHERE o.status = 'paid'
    AND p.price > 1000;

连接算法分析：

Nested-Loop Join
Block Nested-Loop Join
Hash Join（MySQL 8.0+）

4.3 聚合查询优化

SELECT 
    category,
    COUNT(*) AS total,
    AVG(price) AS avg_price,
    MAX(price) AS max_price
FROM products
GROUP BY category WITH ROLLUP
HAVING avg_price > 500;

聚合索引设计：

覆盖索引优化
松散索引扫描
中间表预聚合

五、索引深度解析

5.1 索引创建策略

组合索引：

ALTER TABLE products 
ADD INDEX idx_price_category (price, category);

索引设计原则：

最左前缀原则
基数高的列优先
避免过度索引

5.2 索引性能分析

执行计划解读：

EXPLAIN FORMAT=JSON
SELECT * FROM products 
WHERE category = '电子' 
ORDER BY price DESC 
LIMIT 10;

关键指标分析：

possible_keys vs key
rows vs filtered
Extra信息解读

5.3 全文索引实战

ALTER TABLE products 
ADD FULLTEXT INDEX ft_desc (description);

SELECT 
    id, 
    name,
    MATCH(description) AGAINST('+智能手机 -苹果' IN BOOLEAN MODE) AS score
FROM products
WHERE MATCH(description) AGAINST('+智能手机 -苹果' IN BOOLEAN MODE)
ORDER BY score DESC;

全文检索优化：

停用词配置
ngram解析器使用
相关性评分优化

六、事务与锁机制

6.1 事务控制实例

START TRANSACTION;

UPDATE account SET balance = balance - 100 WHERE user_id = 1;
UPDATE account SET balance = balance + 100 WHERE user_id = 2;

COMMIT;

事务设计要点：

ACID特性保障
保存点(SAVEPOINT)使用
隐式提交场景

6.2 锁机制解析

行锁演示：

SELECT * FROM products 
WHERE id = 1 FOR UPDATE;

UPDATE products SET stock = stock - 1 WHERE id = 1;

锁类型对比：

共享锁 vs 排他锁
记录锁 vs 间隙锁
意向锁的作用

七、存储引擎对比

7.1 InnoDB核心特性

SHOW ENGINE INNODB STATUS;

关键特性：

MVCC实现
聚簇索引结构
缓冲池管理

7.2 MyISAM适用场景

典型使用案例：

CREATE TABLE log_archive (
    id INT,
    log_data TEXT,
    created_at DATETIME
) ENGINE=MyISAM;

使用限制：

不支持事务
表级锁定
崩溃恢复风险

八、性能调优实战

8.1 参数优化示例

[mysqld]
innodb_buffer_pool_size = 16G
innodb_log_file_size = 2G
max_connections = 500
query_cache_type = 0

调优要点：

内存分配策略
IO优化配置
连接管理参数

8.2 慢查询分析

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

SELECT * FROM mysql.slow_log;

优化流程：

定位慢查询
分析执行计划
索引优化
查询重写
架构调整

九、备份与恢复

9.1 逻辑备份实战

mysqldump --single-transaction --routines --triggers shop > shop_backup.sql

备份策略：

全量备份频率
增量备份方案
二进制日志管理

9.2 物理备份方案

mysqlbackup --backup-image=/backups/shop.mbi --backup-dir=/tmp backup-to-image

备份类型对比：

热备 vs 冷备
本地备份 vs 远程备份
加密备份配置

十、安全管理规范

10.1 用户权限管理

CREATE USER 'web_user'@'192.168.1.%' IDENTIFIED BY 'SecureP@ss123!';

GRANT SELECT, INSERT, UPDATE ON shop.* TO 'web_user'@'192.168.1.%';

REVOKE DELETE ON shop.* FROM 'web_user'@'192.168.1.%';

权限管理原则：

最小权限原则
定期权限审计
密码策略配置

10.2 审计与监控

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

SET GLOBAL audit_log_format = JSON;
SET GLOBAL audit_log_policy = ALL;

监控指标：

QPS/TPS
连接数
缓冲池命中率
锁等待时间

正文到此结束

所属分类：Mysql

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