02-MySQL 高级（函数与索引）
对应原始资料：
数据库_day03、mysql索引公开课笔记
一、常用函数
1. 字符串
sql
LENGTH(s) / CHAR_LENGTH(s)
CONCAT(a, b) -- 拼接
SUBSTRING(s, start, len) -- 截取，索引从 1 开始
UPPER(s) / LOWER(s)
TRIM(s) -- 去两端空格
REPLACE(s, a, b)
LPAD(s, len, pad) / RPAD -- 填充
INSTR(s, sub) -- 子串位置2. 数值
sql
ROUND(x, d) -- 四舍五入
CEIL(x) / FLOOR(x)
TRUNCATE(x, d)
MOD(a, b)
RAND()3. 日期
sql
NOW() / CURDATE() / CURTIME()
YEAR(date) / MONTH / DAY / HOUR ...
DATE_FORMAT(date, '%Y-%m-%d')
STR_TO_DATE('2024-01-01', '%Y-%m-%d')
DATEDIFF(d1, d2)
DATE_ADD(date, INTERVAL 1 DAY)4. 流程函数
sql
IF(expr, t, f)
IFNULL(expr, val)
CASE
WHEN 条件 THEN 结果
WHEN 条件 THEN 结果
ELSE 结果
END案例：
sql
SELECT name,
CASE
WHEN score >= 90 THEN '优秀'
WHEN score >= 60 THEN '及格'
ELSE '不及格'
END 等级
FROM student;5. 聚合 + 窗口函数（8.0+）
sql
-- 每个部门薪资排名
SELECT name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) rnk
FROM emp;
-- ROW_NUMBER()、DENSE_RANK()、LAG()、LEAD()、SUM() OVER(...)二、视图
虚表，保存一条 SQL 的查询结果，简化复杂查询。
sql
CREATE VIEW v_emp_dept AS
SELECT e.id, e.name, d.name dname FROM emp e JOIN dept d ON e.dept_id = d.id;
SELECT * FROM v_emp_dept;
DROP VIEW v_emp_dept;视图不存真实数据，查询时仍走原始表。可用于权限隔离、简化复杂查询。不推荐用视图做写操作。
三、存储过程与函数（了解）
预编译存于数据库的 SQL 集合。互联网项目用得少（业务逻辑放代码层）。
sql
DELIMITER $
CREATE PROCEDURE pro_test(IN n INT, OUT result INT)
BEGIN
SELECT COUNT(*) INTO result FROM emp WHERE dept_id = n;
END $
CALL pro_test(1, @r);
SELECT @r;四、索引（重点中的重点）
1. 为什么需要索引
- 没有索引：全表扫描，数据量大时慢。
- 有索引：像书的目录，快速定位，查询效率从 O(n) → O(log n)。
2. 索引结构（InnoDB）
- 默认 B+ 树索引：
- 只有叶子节点存数据，非叶子节点只存索引。
- 叶子节点形成有序双向链表，范围查询极快。
- 聚簇索引：数据和主键索引在一起（主键就是聚簇索引）。
- 二级索引：叶子节点存主键值，需要"回表"。
3. 索引分类
| 类型 | 说明 |
|---|---|
| 主键索引 | PRIMARY KEY，唯一且非空 |
| 唯一索引 | UNIQUE |
| 普通索引 | INDEX / KEY |
| 联合索引 | 多列组合 |
| 全文索引 | FULLTEXT，文本搜索 |
4. 创建索引
sql
CREATE INDEX idx_name ON emp(name);
ALTER TABLE emp ADD INDEX idx_age (age);
ALTER TABLE emp ADD UNIQUE INDEX uk_phone (phone);
CREATE INDEX idx_dept_salary ON emp(dept_id, salary); -- 联合索引5. 最左前缀法则
联合索引 (a, b, c) 可以用于：a / a,b / a,b,c，不能跳过 a。 范围查询右侧的列失效（如 a > 1 and b = 2，b 用不上索引）。
6. 索引失效的常见场景
- 对索引列做运算或函数：
WHERE salary * 2 > 10000。 - 隐式类型转换：
WHERE phone = 13800000000（phone 是 varchar）。 LIKE '%xxx'（左侧 % 不走索引）。OR两侧不全有索引。- 不符合最左前缀。
- 数据分布（优化器认为全表更快）。
7. explain 执行计划
sql
EXPLAIN SELECT * FROM emp WHERE name = '张三';关注列：
type：system > const > eq_ref > ref > range > index > ALL，至少要达到range/ref，避免ALL。key：实际使用的索引。rows：预估扫描行数。Extra：Using index（覆盖索引，好）、Using filesort（额外排序，差）、Using temporary（用临时表，差）。
8. 索引使用建议
- 主键、外键、唯一约束自动建索引。
- 查询频繁、区分度高、数据量大的列建索引。
- 增删改频繁、数据少的表、
WHERE用不到的列，不建。 - 联合索引把区分度高、常用的列放前面。
- 单表索引数建议不超过 5 个。
五、数据库备份与还原
bash
mysqldump -u root -p db1 > db1.sql # 备份单个库
mysqldump -u root -p db1 t1 t2 > tables.sql # 备份表
mysql -u root -p db1 < db1.sql # 还原或在 DataGrip / Navicat 中图形化导出导入。
练习建议
- 用聚合函数统计各部门人数、平均薪资、最高薪资。
- 用 CASE 把成绩转成"优秀/及格/不及格"。
- 给
emp表的name、dept_id, salary建索引，用 explain 观察前后的type和rows。 - 设计一个 SQL 让索引失效（如
LIKE '%xx'、列运算），对比执行计划。 - 用 mysqldump 备份一个库，删表后再还原。