基础层：库、表、字段、增删改查、多表查询、事务
原理层：存储引擎、索引、执行计划、锁、InnoDB、MVCC
运维层：日志、主从复制、分库分表、MyCat、读写分离

SQL 怎么写 -> SQL 怎么查得快 -> 并发下怎么保证正确 -> 数据量大后怎么扩展

数据存储在二维表中。
表和表之间可以通过字段建立关系。
SQL 是操作关系型数据库的统一标准。

默认端口：3306
超级用户：root
本地连接：mysql -u root -p
远程连接：mysql -h 主机IP -P 3306 -u 用户名 -p

-P 大写：端口
-p 小写：密码
SQL 语句通常以 ; 结尾

DDL 建结构，DML 改数据，DQL 查数据，DCL 管权限。

-- 单行注释
# 单行注释
/* 多行注释 */

SHOW DATABASES;
SELECT DATABASE();
CREATE DATABASE 数据库名;
CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8mb4;
USE 数据库名;
DROP DATABASE IF EXISTS 数据库名;

CREATE DATABASE IF NOT EXISTS db01 DEFAULT CHARSET utf8mb4;

SHOW TABLES;
DESC 表名;
SHOW CREATE TABLE 表名;

CREATE TABLE 表名 (
    字段名 字段类型 [约束] [COMMENT '注释'],
    字段名 字段类型 [约束] [COMMENT '注释']
) COMMENT '表注释';

CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    age INT COMMENT '年龄',
    entrydate DATE COMMENT '入职日期'
) COMMENT '员工表';

DROP TABLE IF EXISTS 表名;
TRUNCATE TABLE 表名;

DROP：删除整张表，包括结构。
TRUNCATE：清空数据，保留表结构。
DELETE：删除数据，可带 WHERE。

姓名：VARCHAR
年龄：INT
金额：DECIMAL
创建时间：DATETIME
状态：TINYINT

ALTER TABLE 表名 ADD 字段名 类型 [约束] [COMMENT '注释'];

ALTER TABLE 表名 MODIFY 字段名 新类型;

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新类型;

ALTER TABLE 表名 DROP 字段名;

ALTER TABLE 表名 RENAME TO 新表名;

INSERT INTO 表名(字段1, 字段2) VALUES(值1, 值2);

INSERT INTO 表名 VALUES(值1, 值2, 值3);

INSERT INTO 表名(字段1, 字段2)
VALUES
(值1, 值2),
(值1, 值2),
(值1, 值2);

字符串和日期要加引号。
字段数量和值数量要一致。
自增主键可以写 NULL 或不写。

UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2 WHERE 条件;

UPDATE emp SET age = 20 WHERE id = 1;

UPDATE emp SET age = 20;

DELETE FROM 表名 WHERE 条件;

DELETE FROM emp WHERE id = 1;

DELETE FROM emp;

SELECT 字段列表
FROM 表名
WHERE 条件
GROUP BY 分组字段
HAVING 分组后条件
ORDER BY 排序字段 ASC|DESC
LIMIT 起始索引, 查询条数;

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

WHERE 是分组前过滤。
HAVING 是分组后过滤。

SELECT * FROM 表名;

SELECT 字段1, 字段2 FROM 表名;

SELECT DISTINCT 字段 FROM 表名;

SELECT 字段 AS 别名 FROM 表名;
SELECT 字段 别名 FROM 表名;

=       等于
<> 或 != 不等于
> >= < <=
BETWEEN 值1 AND 值2
IN (...)
IS NULL
IS NOT NULL

AND
OR
NOT

LIKE
_  匹配单个字符
%  匹配任意多个字符

SELECT * FROM emp WHERE age BETWEEN 18 AND 30;
SELECT * FROM emp WHERE name LIKE '张%';
SELECT * FROM emp WHERE name LIKE '_三';
SELECT * FROM emp WHERE dept_id IN (1, 2, 3);
SELECT * FROM emp WHERE id IS NOT NULL;

SELECT COUNT(*) FROM emp;
SELECT AVG(age) FROM emp;
SELECT MAX(salary), MIN(salary) FROM emp;

聚合函数一般会忽略 NULL。
COUNT(*) 常用于统计总行数。

SELECT 分组字段, 聚合函数
FROM 表名
WHERE 分组前条件
GROUP BY 分组字段
HAVING 分组后条件;

SELECT dept_id, COUNT(*)
FROM emp
GROUP BY dept_id;

SELECT dept_id, AVG(salary)
FROM emp
WHERE age > 20
GROUP BY dept_id
HAVING AVG(salary) > 5000;

WHERE 后面不能直接写聚合函数条件。
聚合函数条件要写在 HAVING 后面。

SELECT * FROM emp ORDER BY age ASC;
SELECT * FROM emp ORDER BY age DESC;
SELECT * FROM emp ORDER BY age DESC, entrydate ASC;

SELECT * FROM 表名 LIMIT 起始索引, 查询条数;

SELECT * FROM emp LIMIT 0, 10;

起始索引 = (页码 - 1) * 每页条数

SELECT * FROM emp LIMIT (page - 1) * size, size;

SELECT * FROM emp LIMIT 10;

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

DROP USER '用户名'@'主机名';

SHOW GRANTS FOR '用户名'@'主机名';

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

GRANT ALL ON db01.* TO 'test'@'%';

% 表示任意主机都可以连接。
*.* 表示所有数据库的所有表。

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) UNIQUE,
    age INT CHECK(age > 0 AND age <= 120),
    status TINYINT DEFAULT 1
);

ALTER TABLE 子表
ADD CONSTRAINT 外键名
FOREIGN KEY (子表字段) REFERENCES 主表(主表字段);

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

SELECT * FROM emp, dept;

SELECT *
FROM emp, dept
WHERE emp.dept_id = dept.id;

SELECT 字段
FROM 表1, 表2
WHERE 表1.字段 = 表2.字段;

SELECT 字段
FROM 表1
INNER JOIN 表2 ON 表1.字段 = 表2.字段;

只查询两张表都匹配上的数据。

SELECT 字段
FROM 左表
LEFT JOIN 右表 ON 条件;

SELECT 字段
FROM 左表
RIGHT JOIN 右表 ON 条件;

LEFT JOIN：保留左表全部数据。
RIGHT JOIN：保留右表全部数据。

SELECT e.name 员工, m.name 领导
FROM emp e
LEFT JOIN emp m ON e.manager_id = m.id;

SELECT 字段 FROM 表1
UNION
SELECT 字段 FROM 表2;

SELECT 字段 FROM 表1
UNION ALL
SELECT 字段 FROM 表2;

UNION：去重。
UNION ALL：不去重，效率更高。

SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name LIKE '%研发%');

SELECT * FROM emp
WHERE (salary, manager_id) = (SELECT salary, manager_id FROM emp WHERE name = '张三');

SELECT *
FROM (SELECT * FROM emp WHERE entrydate > '2020-01-01') t;

A 扣钱成功，B 加钱也必须成功。
中间任意一步失败，都要回滚。

START TRANSACTION;
-- SQL操作
COMMIT;
ROLLBACK;

SELECT @@autocommit;
SET @@autocommit = 0;
COMMIT;
ROLLBACK;

REPEATABLE READ，可重复读。

SELECT @@TRANSACTION_ISOLATION;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

连接层 -> 服务层 -> 引擎层 -> 存储层

索引是在存储引擎层实现的。
存储引擎是基于表的，不是基于库的。

SHOW ENGINES;

CREATE TABLE 表名 (...) ENGINE = InnoDB;

1. InnoDB 支持事务，MyISAM 不支持。
2. InnoDB 支持行锁，MyISAM 主要支持表锁。
3. InnoDB 支持外键，MyISAM 不支持。
4. InnoDB 更适合高并发读写，MyISAM 更偏读多写少。

表空间 -> 段 -> 区 -> 页 -> 行

InnoDB 页默认 16KB。
区默认 1MB。
数据最终按行存储。

提高查询效率，降低 IO 成本。
可降低排序成本。

占用磁盘空间。
降低 INSERT、UPDATE、DELETE 效率。

平时说 MySQL 索引，一般指 B+Tree 索引。

1. 非叶子节点只存索引，不存真实数据。
2. 所有数据都在叶子节点。
3. 叶子节点之间形成双向链表。
4. 树高度低，磁盘 IO 少。
5. 适合范围查询和排序。

数据量大时树太高，查询磁盘 IO 多。
顺序插入时普通二叉树可能退化成链表。

B-Tree：非叶子节点和叶子节点都可能存数据。
B+Tree：数据都在叶子节点，非叶子节点只做索引。

单列索引：一个字段。
联合索引：多个字段组合。

InnoDB 表一定有且只有一个聚集索引。
主键索引通常就是聚集索引。
二级索引查不到完整数据时，需要通过主键再查一次，这叫回表。

CREATE INDEX 索引名 ON 表名(字段名);

CREATE UNIQUE INDEX 索引名 ON 表名(字段名);

CREATE INDEX idx_name_age ON user(name, age);

SHOW INDEX FROM 表名;

DROP INDEX 索引名 ON 表名;

CREATE INDEX idx_user_name_age_status ON user(name, age, status);

WHERE name = '张三'
WHERE name = '张三' AND age = 20
WHERE name = '张三' AND age = 20 AND status = 1

WHERE age = 20
WHERE status = 1
WHERE age = 20 AND status = 1

联合索引从最左边字段开始连续使用，中间不能断。

WHERE name = '张三' AND age > 18 AND status = 1

name、age 用到索引，status 不一定充分利用。

联合索引中，等值查询字段尽量放前面，范围查询字段尽量放后面。

WHERE SUBSTRING(phone, 10, 2) = '15'
WHERE age + 1 = 20

WHERE phone = 13800000000

WHERE phone = '13800000000'

WHERE name LIKE '%三'
WHERE name LIKE '%三%'

WHERE name LIKE '张%'

WHERE id = 1 OR name = '张三'

如果 MySQL 判断全表扫描更快，也可能不走索引。

CREATE INDEX idx_user_name_age ON user(name, age);

SELECT name, age FROM user WHERE name = '张三';

SELECT * FROM user WHERE name = '张三';

尽量避免 SELECT *。
能查需要字段，就不要查全部字段。

1. 经常出现在 WHERE、ORDER BY、GROUP BY 的字段。
2. 区分度高的字段，如手机号、身份证号。
3. 多表连接的关联字段。
4. 查询频繁的大表字段。

1. 数据量很小的表。
2. 经常更新的字段。
3. 区分度低的字段，如性别、状态。
4. 很少用于查询条件的字段。

SHOW GLOBAL STATUS LIKE 'Com_______';

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

slow_query_log=1
long_query_time=2

EXPLAIN SELECT * FROM user WHERE id = 1;

system > const > eq_ref > ref > range > index > all

ALL 表示全表扫描，通常需要重点优化。

批量插入优于一条条插入。
手动提交事务优于每条自动提交。
主键顺序插入优于乱序插入。
大批量导入可用 LOAD DATA。

INSERT INTO user(name, age) VALUES
('a', 18), ('b', 19), ('c', 20);

主键长度尽量短。
主键尽量顺序插入。
尽量不要用无序 UUID 当主键。

InnoDB 聚集索引按主键组织数据，无序主键容易导致页分裂，影响性能。

让排序字段尽量走索引。
避免出现 Using filesort。

GROUP BY 字段可以建立索引。
分组前尽量用 WHERE 先过滤，减少参与分组的数据量。

SELECT * FROM user LIMIT 1000000, 10;

SELECT * FROM user
WHERE id > 1000000
LIMIT 10;

SELECT u.*
FROM user u
JOIN (
    SELECT id FROM user ORDER BY id LIMIT 1000000, 10
) t ON u.id = t.id;

SELECT COUNT(*) FROM 表名;

COUNT(*) 一般是推荐写法。
COUNT(字段) 不统计 NULL。

UPDATE 时 WHERE 条件字段尽量有索引。
否则可能从行锁升级成更多范围的锁，影响并发。

UPDATE user SET name = '张三' WHERE id = 1;

CREATE VIEW 视图名 AS SELECT 语句;

SELECT * FROM 视图名;

DROP VIEW 视图名;

简化复杂 SQL。
隐藏敏感字段。
统一查询口径。

DELIMITER $
CREATE PROCEDURE p1()
BEGIN
    SELECT COUNT(*) FROM emp;
END$
DELIMITER ;

CALL p1();

CREATE TRIGGER 触发器名
BEFORE INSERT ON 表名
FOR EACH ROW
BEGIN
    -- SQL
END;

日志记录、数据校验、同步冗余字段。

FLUSH TABLES WITH READ LOCK;

UNLOCK TABLES;

让整个库处于只读状态，常用于一致性备份。

LOCK TABLES 表名 READ;
LOCK TABLES 表名 WRITE;
UNLOCK TABLES;

对表进行增删改查时自动加 MDL 读锁。
修改表结构时自动加 MDL 写锁。
作用是防止查询过程中表结构被别人改掉。

用于协调行锁和表锁，表示表中某些行将要被加锁。

行锁是加在索引上的。
如果查询条件没有走索引，可能扫描更多数据，锁范围变大。

Buffer Pool：缓存数据页和索引页。
Change Buffer：缓存普通二级索引变更。
Adaptive Hash Index：自适应哈希索引。
Log Buffer：redo log 缓冲。

Buffer Pool 是 InnoDB 性能的核心缓存区域。

保证事务持久性。
记录“做了什么修改”。
崩溃恢复时使用。

保证事务原子性。
记录“修改前的数据”。
回滚和 MVCC 都依赖 undo log。

在不加锁或少加锁的情况下，实现并发读写，提高性能。

隐藏字段 + undo log 版本链 + ReadView

用于判断当前事务能看到哪个版本的数据。

READ COMMITTED：每次 SELECT 都生成新的 ReadView。
REPEATABLE READ：事务第一次 SELECT 生成 ReadView，后续复用。

mysql -h 127.0.0.1 -P 3306 -u root -p

mysqladmin -u root -p version
mysqladmin -u root -p variables

mysqlbinlog binlog.000001

mysqldump -u root -p 数据库名 > db.sql
mysqldump -u root -p --all-databases > all.sql

mysql -u root -p 数据库名 < db.sql

SOURCE /path/db.sql;

数据库启动失败、运行异常，优先看错误日志。

SHOW VARIABLES LIKE '%log_error%';

1. 数据恢复。
2. 主从复制。

SHOW VARIABLES LIKE '%log_bin%';

RESET MASTER;
PURGE MASTER LOGS TO 'binlog.000010';
PURGE MASTER LOGS BEFORE '2024-01-01 00:00:00';

slow_query_log=1
long_query_time=2
log_queries_not_using_indexes=1

慢查询日志用于定位慢 SQL，是 SQL 优化的入口。

1. 主库故障时，从库可顶上。
2. 实现读写分离，减轻主库压力。
3. 可以在从库备份，减少对主库影响。

1. Master 提交事务时，把变更写入 binlog。
2. Slave IO 线程读取 Master binlog，写入 relay log。
3. Slave SQL 线程重放 relay log，使从库数据追上主库。

server-id=1
read-only=0

CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';
SHOW MASTER STATUS;

server-id=2
read-only=1

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='主库IP',
SOURCE_USER='复制用户',
SOURCE_PASSWORD='密码',
SOURCE_LOG_FILE='binlog文件名',
SOURCE_LOG_POS=位置;

START REPLICA;
SHOW REPLICA STATUS;

START SLAVE;
SHOW SLAVE STATUS;

单库单表数据量过大后，会出现磁盘 IO、网络 IO、CPU 瓶颈。
分库分表的核心是：把数据分散存储，降低单库单表压力。

垂直分库：用户库、订单库、商品库分开。
垂直分表：商品基本信息和商品详情分开。
水平分库：订单按用户ID取模分到不同库。
水平分表：订单表按月份或ID范围拆成多张表。

ShardingJDBC：应用层分库分表组件。
MyCat：数据库中间件，对应用伪装成一个 MySQL。

8066：MyCat 数据访问端口。
9066：MyCat 管理端口。

写操作走主库。
读操作走从库。
目的：降低主库压力，提高系统整体读能力。

Master 负责写。
Slave 负责读。
Master 通过 binlog 同步数据到 Slave。

两个主库互为主从，下面再挂从库。
可以提高可用性，但配置更复杂。

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...;

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

SELECT dept_id, COUNT(*)
FROM emp
WHERE age > 18
GROUP BY dept_id
HAVING COUNT(*) > 5;

内连接：只要两边匹配上的数据。
左外连接：左表全部保留，右表匹配不上补 NULL。
右外连接：右表全部保留，左表匹配不上补 NULL。

大表高频查，考虑建索引。
where、order by、group by、join 字段优先。
联合索引看最左，范围之后要小心。
函数运算会失效，字符串记得加引号。
LIKE 百分号别放左，SELECT 星号少使用。
索引不是越多越好，增删改也要付成本。

事务解决一组操作的一致性问题。
锁解决并发访问时的数据安全问题。
事务隔离性通常依赖锁和 MVCC 实现。

InnoDB 是 MySQL 默认存储引擎，支持事务、行级锁和外键，适合高并发业务场景。
它使用 B+Tree 组织索引，主键索引是聚集索引，叶子节点保存整行数据；二级索引叶子节点保存主键值，必要时需要回表。
InnoDB 通过 redo log 保证持久性，通过 undo log 支持回滚和 MVCC，通过 MVCC 在读写并发场景下提高性能。

MySQL 主从复制基于 binlog。主库提交事务后把 DDL 和 DML 变更写入 binlog；从库 IO 线程读取主库 binlog 写入 relay log；从库 SQL 线程重放 relay log，从而保持主从数据同步。主从复制常用于读写分离、故障切换和备份。

当单库单表数据量过大，出现 IO、CPU 或连接压力时，可以通过分库分表把数据分散存储。垂直拆分按业务或字段拆，水平拆分按某个字段规则拆。垂直拆分解决业务耦合和宽表问题，水平拆分主要解决单表数据量过大的问题。

基础 SQL：DDL建表，DML改数，DQL查询，DCL管权。
查询顺序：from where group having select order limit。
多表查询：先想表关系，再写连接条件，避免笛卡尔积。
事务重点：ACID、并发问题、隔离级别。
索引重点：B+Tree、最左前缀、回表、覆盖索引、索引失效。
优化重点：慢查询日志 + EXPLAIN + 合理建索引。
InnoDB重点：事务、行锁、外键、redo、undo、MVCC。
运维重点：binlog、主从复制、读写分离、分库分表。