mysql数据库

举报
yd_234424112 发表于 2026/07/19 08:33:26 2026/07/19
【摘要】 # MySQL 核心知识点速查手册（可直接复制）> 适用版本：MySQL 5.7 / 8.0 > 风格：简洁、结构化、代码可直接运行---## 一、基础概念| 概念 | 说明 ||------|------|| **数据库（Database）** | 数据的容器，包含表、视图、存储过程等 || **表（Table）** | 由行（记录）和列（字段）组成的二维结构 || **字段（Colum... 
# MySQL 核心知识点速查手册（可直接复制）

> 适用版本：MySQL 5.7 / 8.0  
> 风格：简洁、结构化、代码可直接运行

---

## 一、基础概念

| 概念 | 说明 |
|------|------|
| **数据库（Database）** | 数据的容器，包含表、视图、存储过程等 |
| **表（Table）** | 由行（记录）和列（字段）组成的二维结构 |
| **字段（Column）** | 表的列，有特定数据类型 |
| **记录（Row）** | 表的一行数据 |
| **主键（PRIMARY KEY）** | 唯一标识一条记录，非空且唯一 |
| **外键（FOREIGN KEY）** | 关联其他表的主键，维护参照完整性 |
| **索引（INDEX）** | 加速查询的数据结构（B+Tree / Hash） |
| **视图（VIEW）** | 虚拟表，基于 SQL 查询结果 |
| **存储过程（Procedure）** | 预编译的 SQL 代码块 |
| **触发器（Trigger）** | 在表事件（INSERT/UPDATE/DELETE）时自动执行 |

---

## 二、数据类型（常用）

### 数值类型
| 类型 | 大小 | 范围（有符号） | 用途 |
|------|------|---------------|------|
| `TINYINT` | 1字节 | -128 ~ 127 | 小整数 |
| `SMALLINT` | 2字节 | -32768 ~ 32767 | 中等整数 |
| `INT` | 4字节 | -2^31 ~ 2^31-1 | 标准整数 |
| `BIGINT` | 8字节 | -2^63 ~ 2^63-1 | 大整数 |
| `FLOAT` | 4字节 | 单精度浮点数 | 小数 |
| `DOUBLE` | 8字节 | 双精度浮点数 | 高精度小数 |
| `DECIMAL(M,D)` | 变长 | 精确小数（M总位数，D小数位） | 金额等 |

### 字符串类型
| 类型 | 说明 |
|------|------|
| `CHAR(n)` | 固定长度，最多 255 字符 |
| `VARCHAR(n)` | 可变长度，最多 65535 字符（实际受行大小限制） |
| `TEXT` | 长文本，最大 65535 字符 |
| `LONGTEXT` | 极大文本，最大 4GB |
| `BLOB` | 二进制大对象（图片、文件等） |

### 日期时间
| 类型 | 格式 | 范围 |
|------|------|------|
| `DATE` | YYYY-MM-DD | 1000-01-01 ~ 9999-12-31 |
| `TIME` | HH:MM:SS | -838:59:59 ~ 838:59:59 |
| `DATETIME` | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
| `TIMESTAMP` | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01 UTC ~ 2038-01-19 |
| `YEAR` | YYYY | 1901 ~ 2155 |

### JSON（MySQL 5.7+）
```sql
JSON 类型，支持 JSON 函数操作

三、DDL（数据定义语言）

1. 操作数据库

-- 创建数据库
CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE mydb;

-- 删除数据库
DROP DATABASE IF EXISTS mydb;

-- 查看所有数据库
SHOW DATABASES;

2. 操作表

-- 创建表
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
    salary DECIMAL(10,2) COMMENT '薪资',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    status TINYINT DEFAULT 1 COMMENT '状态 1-启用 0-禁用',
    INDEX idx_email (email),          -- 普通索引
    UNIQUE KEY uk_username (username) -- 唯一索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 修改表（添加列）
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- 修改列
ALTER TABLE users MODIFY COLUMN phone VARCHAR(15) NOT NULL;

-- 重命名列
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(15);

-- 删除列
ALTER TABLE users DROP COLUMN mobile;

-- 添加索引
CREATE INDEX idx_age ON users(age);

-- 删除索引
DROP INDEX idx_age ON users;

-- 重命名表
RENAME TABLE users TO user_info;

-- 删除表
DROP TABLE IF EXISTS user_info;

-- 查看表结构
DESC users;
SHOW CREATE TABLE users;

四、DML（数据操作语言）

1. INSERT（插入）

-- 插入单条
INSERT INTO users (username, email, age, salary) 
VALUES ('zhangsan', 'zhangsan@example.com', 25, 8000.50);

-- 插入多条
INSERT INTO users (username, email, age) VALUES 
('lisi', 'lisi@test.com', 30),
('wangwu', 'wangwu@test.com', 28);

-- 插入并返回自增ID（MySQL 8.0）
INSERT INTO users (username, email) VALUES ('zhaoliu', 'zhao@xx.com') RETURNING id;

-- 从另一张表插入
INSERT INTO users_backup SELECT * FROM users WHERE age > 30;

2. UPDATE（更新）

-- 更新单条
UPDATE users SET age = 26, salary = 9000 WHERE username = 'zhangsan';

-- 条件更新
UPDATE users SET status = 0 WHERE age < 18;

-- 更新时使用原值计算
UPDATE users SET salary = salary * 1.1 WHERE age > 30;

-- 多表更新（关联）
UPDATE users u JOIN orders o ON u.id = o.user_id 
SET u.status = 2 WHERE o.total > 10000;

3. DELETE（删除）

-- 删除满足条件的行
DELETE FROM users WHERE age > 60;

-- 清空表（保留结构，重置自增）
TRUNCATE TABLE users;  -- 比 DELETE 快，不可回滚

-- 删除所有行（可回滚）
DELETE FROM users;

-- 多表删除
DELETE u, o FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE u.status = 0;

五、DQL（数据查询语言）⭐

1. SELECT 基础

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT id, username, email FROM users;

-- 去重
SELECT DISTINCT age FROM users;

-- 别名
SELECT username AS 用户名, age AS 年龄 FROM users;

-- 限制行数
SELECT * FROM users LIMIT 10;          -- 前10条
SELECT * FROM users LIMIT 10 OFFSET 5; -- 跳过5条取10条（分页）
-- 等价于
SELECT * FROM users LIMIT 5, 10;       -- MySQL 专用语法

2. WHERE 条件过滤

-- 比较运算符
SELECT * FROM users WHERE age >= 18 AND age <= 30;
SELECT * FROM users WHERE salary BETWEEN 5000 AND 10000;
SELECT * FROM users WHERE username = 'zhangsan';
SELECT * FROM users WHERE age IN (18, 22, 25);
SELECT * FROM users WHERE age NOT IN (18, 22, 25);

-- 模糊查询（LIKE）
SELECT * FROM users WHERE username LIKE 'zhang%';   -- 以zhang开头
SELECT * FROM users WHERE username LIKE '%san%';    -- 包含san
SELECT * FROM users WHERE username LIKE '____';     -- 四个字符（一个_代表一个字符）

-- NULL 判断
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- 逻辑运算符：AND / OR / NOT
SELECT * FROM users WHERE (age > 20 AND age < 30) OR status = 1;

-- 正则表达式（MySQL 8.0 推荐 REGEXP_LIKE）
SELECT * FROM users WHERE username REGEXP '^[a-z]{3,}$';

3. 排序 ORDER BY

-- 单列排序
SELECT * FROM users ORDER BY age DESC;   -- 降序
SELECT * FROM users ORDER BY age ASC;    -- 升序（默认）

-- 多列排序
SELECT * FROM users ORDER BY age DESC, created_at ASC;

-- 按表达式排序
SELECT * FROM users ORDER BY salary * 12 DESC;

4. 分组与聚合 GROUP BY + HAVING

-- 常用聚合函数：COUNT, SUM, AVG, MAX, MIN
SELECT 
    status,
    COUNT(*) AS total,
    AVG(age) AS avg_age,
    MAX(salary) AS max_salary
FROM users
GROUP BY status;

-- 分组后过滤（HAVING）
SELECT 
    status,
    COUNT(*) AS cnt
FROM users
GROUP BY status
HAVING cnt > 10;   -- 不能用 WHERE 过滤聚合结果

-- ROLLUP（分组小计）
SELECT status, age, COUNT(*)
FROM users
GROUP BY status, age WITH ROLLUP;

5. 连接查询（JOIN）

-- INNER JOIN（内连接，取交集）
SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN（左连接，左表全量，右表匹配不上为NULL）
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN（右连接，右表全量）
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN（MySQL 不支持，可用 UNION 模拟）
SELECT u.username, o.order_id
FROM users u LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.username, o.order_id
FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

-- 自连接（同一张表关联）
SELECT a.username AS 员工, b.username AS 领导
FROM users a
LEFT JOIN users b ON a.manager_id = b.id;

-- 笛卡尔积（谨慎使用）
SELECT * FROM users, orders;

6. 子查询

-- WHERE 子查询（标量/列/行）
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);

-- 使用 IN / EXISTS
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);

-- FROM 子查询（派生表）
SELECT t.avg_age, COUNT(*)
FROM (SELECT AVG(age) AS avg_age FROM users) t;

7. 联合查询 UNION

-- UNION（去重）
SELECT username FROM users WHERE age > 30
UNION
SELECT username FROM users WHERE status = 1;

-- UNION ALL（不去重，效率更高）
SELECT username FROM users WHERE age > 30
UNION ALL
SELECT username FROM users WHERE status = 1;

8. 窗口函数（MySQL 8.0+）⭐

-- ROW_NUMBER() 排名（不并列）
SELECT 
    username,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_num
FROM users;

-- RANK() / DENSE_RANK()（并列排名）
SELECT 
    username,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM users;

-- 分区排名（按部门）
SELECT 
    username,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM users;

-- 聚合窗口函数
SELECT 
    username,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM users;

-- LAG / LEAD（前后行）
SELECT 
    username,
    salary,
    LAG(salary, 1, 0) OVER (ORDER BY id) AS prev_salary,
    LEAD(salary, 1, 0) OVER (ORDER BY id) AS next_salary
FROM users;

六、索引

1. 索引类型

类型

说明

PRIMARY KEY

主键索引，唯一且非空

UNIQUE INDEX

唯一索引，列值必须唯一

INDEX

普通索引，加速查询

FULLTEXT

全文索引（MyISAM / InnoDB 5.6+），支持文本搜索

SPATIAL

空间索引（GIS）

复合索引

多列组合索引，遵循最左前缀原则

2. 索引操作

-- 创建索引
CREATE INDEX idx_name ON table_name(column1, column2);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_uniq_email ON users(email);

-- 创建全文索引
CREATE FULLTEXT INDEX idx_fulltext_content ON articles(content);

-- 删除索引
DROP INDEX idx_name ON table_name;

-- 查看索引
SHOW INDEX FROM table_name;

3. 索引使用原则

  • 最左前缀：复合索引 (a,b,c) 可有效用于 a、a,b、a,b,c，但 b,c 无效。
  • 避免函数/计算在索引列上：WHERE YEAR(date)=2023 不会走索引，应改为 date BETWEEN '2023-01-01' AND '2023-12-31'。
  • 覆盖索引：查询列全部包含在索引中，可避免回表（EXPLAIN 的 Extra 显示 Using index）。
  • 选择性高：索引列重复值越少越好（如主键、唯一键）。
  • 不要过度索引：维护索引有开销（增删改慢）。

4. 执行计划（EXPLAIN）

EXPLAIN SELECT * FROM users WHERE username = 'zhangsan';

关键列：

  • type：性能从好到坏：system > const > eq_ref > ref > range > index > ALL
  • possible_keys：可能使用的索引
  • key：实际使用的索引
  • rows：预估扫描行数
  • Extra：Using index（覆盖索引）、Using where、Using filesort（需要排序优化）、Using temporary（用了临时表）

七、事务（ACID）

事务特性

  • 原子性（Atomicity）：事务中的操作要么全部成功，要么全部回滚。
  • 一致性（Consistency）：事务前后数据库状态一致（约束、触发器等）。
  • 隔离性（Isolation）：并发事务互不干扰（由隔离级别控制）。
  • 持久性（Durability）：事务提交后数据永久保存（持久化到磁盘）。

事务操作

-- 开启事务
START TRANSACTION;  -- 或 BEGIN;

-- 执行 SQL
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交
COMMIT;

-- 回滚
ROLLBACK;

-- 设置保存点
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;

-- 自动提交（默认开启）
SET autocommit = 0;   -- 关闭自动提交
SET autocommit = 1;   -- 开启

隔离级别（从低到高）

隔离级别

脏读

不可重复读

幻读

READ UNCOMMITTED

✅

✅

✅

READ COMMITTED

❌

✅

✅

REPEATABLE READ（默认）

❌

❌

✅（间隙锁可防止部分）

SERIALIZABLE

❌

❌

❌

 
-- 查看当前隔离级别
SELECT @@transaction_isolation;  -- MySQL 8.0
SELECT @@tx_isolation;           -- MySQL 5.7

-- 设置隔离级别（会话级）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 全局
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

八、锁机制

1. 锁粒度

  • 表级锁（Table Lock）：MyISAM、MEMORY 引擎，开销小，锁冲突高。
  • 行级锁（Row Lock）：InnoDB，开销大，支持并发高。
  • 页级锁（Page Lock）：BDB 引擎（很少用）。

2. InnoDB 锁类型

  • 共享锁（S锁）：读锁，允许其他事务读，不允许写。
  • 排他锁（X锁）：写锁，其他事务不能读也不能写。
-- 手动加锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;        -- 共享锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;                -- 排他锁（行锁）

-- 表锁（一般不用）
LOCK TABLES users READ;   -- 读锁
LOCK TABLES users WRITE;  -- 写锁
UNLOCK TABLES;

3. 间隙锁（Gap Lock）

  • 在 REPEATABLE READ 隔离级别下，为了防止幻读，InnoDB 会在索引记录之间的间隙加锁。
  • 间隙锁锁定一个范围，但不包括记录本身。

4. 死锁

  • 两个或多个事务互相持有对方需要的锁。
  • MySQL 自动检测死锁并回滚其中一个事务（通常回滚代价较小者）。
  • 查看死锁信息：SHOW ENGINE INNODB STATUS;

九、视图

-- 创建视图
CREATE VIEW v_active_users AS
SELECT id, username, email, age FROM users WHERE status = 1;

-- 使用视图（与表相同）
SELECT * FROM v_active_users WHERE age > 20;

-- 更新视图（有限制，不推荐）
UPDATE v_active_users SET age = 30 WHERE id = 1;

-- 查看视图定义
SHOW CREATE VIEW v_active_users;

-- 删除视图
DROP VIEW IF EXISTS v_active_users;

十、存储过程与函数

存储过程

-- 创建（无参数）
DELIMITER //
CREATE PROCEDURE get_users()
BEGIN
    SELECT * FROM users WHERE status = 1;
END //
DELIMITER ;

-- 调用
CALL get_users();

-- 带参数（IN/OUT/INOUT）
DELIMITER //
CREATE PROCEDURE get_user_by_id(IN uid INT, OUT uname VARCHAR(50))
BEGIN
    SELECT username INTO uname FROM users WHERE id = uid;
END //
DELIMITER ;

-- 调用
CALL get_user_by_id(1, @name);
SELECT @name;

-- 删除
DROP PROCEDURE IF EXISTS get_users;

函数（自定义函数）

-- 创建函数（必须返回一个值）
DELIMITER //
CREATE FUNCTION get_user_count() RETURNS INT DETERMINISTIC
BEGIN
    DECLARE cnt INT;
    SELECT COUNT(*) INTO cnt FROM users;
    RETURN cnt;
END //
DELIMITER ;

-- 使用
SELECT get_user_count();

-- 删除
DROP FUNCTION IF EXISTS get_user_count;

流程控制

-- IF
IF age > 18 THEN
    SET status = '成年';
ELSEIF age > 60 THEN
    SET status = '老年';
ELSE
    SET status = '未成年';
END IF;

-- CASE
CASE age
    WHEN 18 THEN SET desc = '刚成年';
    WHEN 30 THEN SET desc = '而立';
    ELSE SET desc = '其他';
END CASE;

-- LOOP / WHILE / REPEAT 略

十一、触发器（Trigger）

-- 创建触发器（BEFORE / AFTER）
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

-- 查看触发器
SHOW TRIGGERS;

-- 删除
DROP TRIGGER IF EXISTS before_user_insert;

十二、常用函数

字符串函数

CONCAT('Hello', ' ', 'World')          -- 拼接
LENGTH('abc')                          -- 字节长度
CHAR_LENGTH('中文')                    -- 字符长度
SUBSTRING('abcdef', 2, 3)              -- 从第2位取3个字符 -> 'bcd'
LEFT('abcde', 3)                       -- 'abc'
RIGHT('abcde', 2)                      -- 'de'
UPPER('abc')                           -- 'ABC'
LOWER('ABC')                           -- 'abc'
TRIM('  abc  ')                        -- 去首尾空格
REPLACE('abcabc', 'a', 'x')            -- 'xbcxbc'
INSTR('abcabc', 'b')                   -- 返回位置 2
LPAD('123', 5, '0')                    -- '00123'
RPAD('123', 5, '0')                    -- '12300'

日期时间函数

NOW()                                  -- 当前日期时间
CURDATE()                              -- 当前日期
CURTIME()                              -- 当前时间
DATE_ADD(NOW(), INTERVAL 1 DAY)        -- 加一天
DATE_SUB(NOW(), INTERVAL 1 MONTH)      -- 减一个月
DATEDIFF('2026-07-19', '2026-01-01')   -- 相差天数
TIMESTAMPDIFF(YEAR, '2000-01-01', NOW()) -- 相差年数
YEAR(NOW()), MONTH(NOW()), DAY(NOW())
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') -- 格式化
STR_TO_DATE('2026-07-19', '%Y-%m-%d')  -- 字符串转日期
UNIX_TIMESTAMP(NOW())                  -- 转时间戳
FROM_UNIXTIME(1710000000)              -- 时间戳转日期

数学函数

ABS(-10)                               -- 10
ROUND(3.14159, 2)                      -- 3.14
CEIL(3.14)                             -- 4
FLOOR(3.99)                            -- 3
RAND()                                 -- 0~1 随机数
MOD(10, 3)                             -- 1
POWER(2, 3)                            -- 8

聚合函数

COUNT(*) / COUNT(column)               -- 计数
SUM(column)                            -- 求和
AVG(column)                            -- 平均值
MAX(column)                            -- 最大值
MIN(column)                            -- 最小值
GROUP_CONCAT(username SEPARATOR ',')   -- 分组拼接字符串

类型转换

CAST('123' AS SIGNED)                  -- 转为整数
CAST('2026-07-19' AS DATE)             -- 转为日期
CONVERT('123', SIGNED)                 -- 同上

JSON 函数（MySQL 5.7+）

-- 创建 JSON 列
CREATE TABLE test_json (data JSON);

-- 插入
INSERT INTO test_json VALUES ('{"name":"zhang","age":25}');

-- 查询
SELECT JSON_EXTRACT(data, '$.name') FROM test_json;       -- "zhang"
SELECT data->>'$.name' FROM test_json;                    -- zhang（去引号）
SELECT JSON_CONTAINS(data, '25', '$.age') FROM test_json;

-- 修改
UPDATE test_json SET data = JSON_SET(data, '$.age', 26);

-- 数组
SELECT JSON_ARRAY(1, 2, 3);                               -- [1,2,3]
SELECT JSON_OBJECT('key', 'value');                       -- {"key":"value"}

十三、用户与权限管理

-- 创建用户（MySQL 8.0 语法）
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password';

-- 修改密码（8.0）
ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'newpassword';

-- 授权
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'myuser'@'localhost';

-- 授予全部权限
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' WITH GRANT OPTION;

-- 撤销权限
REVOKE DELETE ON mydb.* FROM 'myuser'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'myuser'@'localhost';

-- 删除用户
DROP USER 'myuser'@'localhost';

-- 刷新权限（通常 GRANT 后自动生效，手动执行）
FLUSH PRIVILEGES;

十四、备份与恢复

使用 mysqldump（命令行）

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql

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

# 备份结构不带数据
mysqldump -u root -p --no-data mydb > structure.sql

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

# 恢复（mysql 命令）
mysql -u root -p mydb < mydb_backup.sql

使用 source 命令（在 mysql 客户端内）

SOURCE /path/to/backup.sql;

十五、性能优化常用技巧

  1. 使用 EXPLAIN 分析慢查询。
  2. 避免 SELECT *，只取所需列。
  3. 合理使用索引，注意最左前缀。
  4. 避免在 WHERE 中使用函数或运算（如 LEFT(name,3)='abc'）。
  5. 优化分页查询：大偏移量用子查询或游标。
-- 传统方式（扫描很多行）
SELECT * FROM users ORDER BY id LIMIT 100000, 10;

-- 优化方式（走索引）
SELECT * FROM users WHERE id > (SELECT id FROM users ORDER BY id LIMIT 100000, 1) LIMIT 10;
  1. 使用连接（JOIN）替代子查询（在可读性和性能间权衡）。
  2. 适当使用冗余字段减少关联查询。
  3. 读写分离、分库分表（水平/垂直）。
  4. 使用缓存（Redis / Memcached）减少数据库压力。
  5. 开启慢查询日志，定位性能瓶颈。
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录

十六、常见问题与排查

问题

解决方法

忘记 root 密码

跳过授权表启动，--skip-grant-tables 重置

死锁

查看 SHOW ENGINE INNODB STATUS，优化事务顺序

连接数过多

调整 max_connections，检查长连接

表损坏

CHECK TABLE / REPAIR TABLE（仅 MyISAM）

数据导入慢

关闭唯一约束、外键检查：SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0;

SQL 不走索引

使用 FORCE INDEX 或优化查询条件

十七、系统信息查询

-- 版本
SELECT VERSION();

-- 当前数据库
SELECT DATABASE();

-- 当前用户
SELECT USER();

-- 所有连接
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 查看状态变量
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Questions';

-- 查看系统变量
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 查看存储引擎
SHOW ENGINES;

-- 查看表状态
SHOW TABLE STATUS FROM mydb LIKE 'users';

十八、常用技巧速记

1. 插入或更新（UPSERT）

-- 如果存在则更新，不存在则插入（需要唯一键）
INSERT INTO users (id, username, age) 
VALUES (1, 'zhangsan', 30) 
ON DUPLICATE KEY UPDATE age = VALUES(age), username = VALUES(username);

-- 或使用 REPLACE（先删除后插入，有风险）
REPLACE INTO users (id, username, age) VALUES (1, 'zhangsan', 30);

2. 随机获取一条记录

SELECT * FROM users ORDER BY RAND() LIMIT 1;  -- 全表扫描，效率低
-- 优化（假设 id 连续且自增）
SELECT * FROM users WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users))) LIMIT 1;

3. 复制表结构（不含数据）

CREATE TABLE users_copy LIKE users;

4. 复制表结构和数据

CREATE TABLE users_copy AS SELECT * FROM users;
-- 注意：不会复制索引、触发器、约束（除主键外）

5. 批量插入测试数据

INSERT INTO users (username, email, age)
SELECT CONCAT('user', t.id), CONCAT('user', t.id, '@test.com'), FLOOR(RAND()*50)+18
FROM (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 ... ) t;  -- 可递归生成

6. 生成连续数字序列（MySQL 8.0 用递归 CTE）

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n+1 FROM seq WHERE n < 100
)
SELECT * FROM seq;

附录：MySQL 8.0 新增特性要点

  • 窗口函数（ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD 等）
  • 公共表表达式 CTE（WITH 子句，递归支持）
  • 降序索引（INDEX idx_desc (col DESC)）
  • JSON 增强（JSON_TABLE, JSON_ARRAYAGG 等）
  • 隐藏索引（ALTER TABLE ... ALTER INDEX ... INVISIBLE/VISIBLE）
  • 原子 DDL（CREATE/DROP 语句可回滚）
  • 角色管理（CREATE ROLE, GRANT 角色）
  • 自增列持久化（重启不丢失）

📌 本文档涵盖 MySQL 日常开发与管理 90% 以上场景，可直接用于复习、面试或开发速查。
持续更新中... 建议收藏。
```

【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入