MySQL 实战指南

本文整合了 MySQL 数据库的常用命令、性能优化技巧和常见问题解决方案。

常用命令

创建 UTF-8 数据库

-- 创建支持完整 UTF-8 的数据库（推荐 utf8mb4）
CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 传统 UTF-8 创建方式
CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

UPDATE JOIN 技巧

使用 JOIN 进行批量更新：

-- 根据关联表更新数据
UPDATE train AS a, train AS b
SET a.next_temp = b.temp
WHERE b.timestamp = DATE_ADD(a.timestamp, INTERVAL 30 MINUTE);

-- 多字段更新示例
UPDATE amdashboard a, ASCNCOAClean b
SET a.ASCID = b.id,
    a.ASCFirst = b.firstname,
    a.ASCLast = b.lastname
WHERE a.actorsfirst = b.firstname;

复制表

-- 复制表结构（包括索引）
CREATE TABLE newtable LIKE oldtable;

-- 复制数据
INSERT newtable SELECT * FROM oldtable;

大小写问题

在 Linux 和 Windows 上，MySQL 对表名大小写的处理不同，这可能导致跨平台问题。

大小写规则

对象	Linux	Windows
数据库名	区分大小写	不区分
表名	区分大小写	不区分
列名/别名	不区分	不区分
变量名	区分大小写	区分大小写

解决方案

让 MySQL 不区分表名大小写：

# 1. 编辑配置文件
sudo vim /etc/my.cnf

# 2. 在 [mysqld] 下添加
lower_case_table_names = 1

# 3. 重启 MySQL
sudo systemctl restart mysql

注意：此设置需要在数据库初始化前配置，已有数据的情况下修改可能导致问题。

性能优化

存储引擎选择

引擎	适用场景	特点
InnoDB	事务处理、高并发	支持事务、行级锁、外键
MyISAM	读密集、全文搜索	表级锁、查询速度快

-- 查看默认存储引擎
SHOW VARIABLES LIKE '%storage_engine%';

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

查询缓存优化

对于重复查询，利用缓存可以显著提升性能。但要避免使用会导致缓存失效的函数：

-- 不好：使用动态函数，无法缓存
SELECT * FROM users WHERE created_at > NOW();
SELECT * FROM orders ORDER BY RAND() LIMIT 10;

-- 好：使用固定值，可以缓存
SELECT * FROM users WHERE created_at > '2024-01-01';

索引优化

为经常用于搜索、排序、连接的字段建立索引：

-- 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 创建复合索引（注意字段顺序）
CREATE INDEX idx_name_date ON orders(customer_name, order_date);

垂直分割

将不常用的大字段分离到单独的表：

-- 原表（字段过多）
-- users: id, name, email, avatar_blob, bio_text, ...

-- 分割后
-- users: id, name, email
-- user_profiles: user_id, avatar_blob, bio_text

注意：分割后避免频繁 JOIN，否则性能反而下降。

大型操作处理

拆分大型 DELETE/INSERT，避免长时间锁表：

-- 不好：一次删除大量数据，会锁表
DELETE FROM logs WHERE created_at < '2023-01-01';

-- 好：分批删除
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 10000;
-- 重复执行直到删除完成

避免性能陷阱

陷阱	问题	建议
`SELECT *`	读取不必要的字段	明确指定需要的字段
`ORDER BY RAND()`	全表扫描后排序	使用其他随机算法
缺少 `LIMIT`	返回过多数据	单条查询加 `LIMIT 1`
使用 `NULL`	额外的比较开销	尽可能使用 `NOT NULL`

查看表大小

基本查询

USE information_schema;

SELECT table_name, table_rows, data_length + index_length AS total_size
FROM tables
WHERE table_schema = 'your_database';

详细查询（格式化输出）

SELECT
    CONCAT(table_schema, '.', table_name) AS 'Table Name',
    CONCAT(ROUND(table_rows / 1000000, 4), 'M') AS 'Number of Rows',
    CONCAT(ROUND(data_length / (1024 * 1024 * 1024), 4), 'G') AS 'Data Size',
    CONCAT(ROUND(index_length / (1024 * 1024 * 1024), 4), 'G') AS 'Index Size',
    CONCAT(ROUND((data_length + index_length) / (1024 * 1024 * 1024), 4), 'G') AS 'Total'
FROM information_schema.TABLES
WHERE table_schema = 'your_database'
ORDER BY (data_length + index_length) DESC;

常见问题解决

#1071 - Key too long 错误

当索引键长度超过限制时会报此错误：

-- 解决方案：启用大前缀
SET @@global.innodb_large_prefix = 1;

-- 或者减少索引字段长度
CREATE INDEX idx_name ON users(name(191));

时间段查询

查询某个时间点前 30 分钟的数据：

-- 方法一：使用 DATE_ADD
SELECT * FROM tb WHERE col1 < DATE_ADD(NOW(), INTERVAL -30 MINUTE);

-- 方法二：使用 INTERVAL
SELECT * FROM tablename
WHERE column_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE;

生成随机数

-- 生成 0-100 之间的随机浮点数，保留 2 位小数
SELECT ROUND(RAND() * 100, 2);

用户登录问题

新建用户无法本地登录时，检查是否存在匿名用户：

-- 查看用户列表
SELECT host, user, password FROM mysql.user;

-- 如果存在 host=localhost, user='' 的匿名用户，需要删除
DELETE FROM mysql.user WHERE user = '' AND host = 'localhost';
FLUSH PRIVILEGES;

-- 或者为用户单独授权 localhost
GRANT SELECT, INSERT, UPDATE, DELETE ON test.*
TO appadmin@'localhost' IDENTIFIED BY 'password';

root 密码和远程访问

-- 设置 root 密码并允许远程访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
IDENTIFIED BY 'your_password' WITH GRANT OPTION;

FLUSH PRIVILEGES;