CREATE INDEX idx_user_age_phone ON tb_user(age ASC, phone DESC);
SELECT id, age, phone
FROM tb_user
ORDER BY age ASC, phone DESC;
4. 分组优化 (GROUP BY)
利用索引：
在 GROUP BY 查询中使用合适的索引，提高效率。
确保索引列满足最左前缀法则。
示例：
SELECT profession, COUNT(*) FROM tb_user GROUP BY profession ORDER BY profession;
5. 分页优化 (LIMIT)
优化大数据量分页：
避免高偏移量的 LIMIT 查询，减少不必要的数据排序。
使用覆盖索引加速：
通过主键索引排序查询，可以显著提高性能。
示例：
-- 慢查询示例
SELECT * FROM tb_sku LIMIT 5000000, 10;
-- 优化查询示例
SELECT id FROM tb_sku ORDER BY id LIMIT 5000000, 10;
-- 通过联表查询进行优化
SELECT *
FROM tb_sku AS s
JOIN (SELECT id FROM tb_sku ORDER BY id LIMIT 5000000, 10) AS a
ON s.id = a.id;
6. 计数优化 (COUNT)
性能差异：
COUNT(*) ≈ COUNT(1) 性能最好，InnoDB 默认的优化机制。
使用 COUNT(主键)、COUNT(字段) 的性能依次降低。
示例：
SELECT COUNT(*) FROM tb_user; # 性能最好
SELECT COUNT(1) FROM tb_user; # 和上面差不多
SELECT COUNT(id) FROM tb_user; # 次优
SELECT COUNT(name) FROM tb_user; # 低效（可能遍历全表）
建议：
尽量使用 COUNT(*)，并考虑使用 Redis 或其他方式缓存计数。
7. 更新优化 (UPDATE)
避免锁升级：
确保更新条件(WHERE之后)的字段上有索引，以避免行锁升级为表锁。
示例：
UPDATE student SET no = '042' WHERE id = 1; -- 行锁(主键索引)
UPDATE student SET no = '114514' WHERE name = 'test'; -- 表锁（name没有索引，需添加索引以优化）
视图
1. 语法
创建视图
CREATE [ OR REPLACE ]
VIEW 视图名称[（列名列表）]
AS
SELECT 语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];
显示视图
SHOW CREATE VIEW [ 视图名称 ]; #显示创建视图语句
SELECT [查询字段] FROM [ 视图名称 ] WHERE [.....]; #查看创建的视图中的数据
修改
#方式一
CREATE [OR REPLACE]
VIEW 视图名称[（列名列表)）]
AS
SELECT 语句[ WITH[ CASCADED | LOCAL ] CHECK OPTION ];
---------------------------------------------------------
#方式二
ALTER
VIEW 视图名称 [（列名列表)]
AS
SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION];
SET @自定义变量 := ?; # := 和 = 都可以
SELECT @自定义变量 := ?;
SELECT 字段 INTO @自定义变量 FROM ....; #将查询结果传递给自定义变量
在函数中的应用示例：
CREATE PROCEDURE getcount()
BEGIN
DECLARE cnt INT DEFAULT 0; #变量声明
SELECT COUNT(*) INTO cnt FROM goods; #给变量赋值
SELECT cnt; #输出结果
END;
CALL getcount();
3. 存储过程
3.1 IF语句示例：
CREATE PROCEDURE score()
BEGIN
DECLARE cnt INT DEFAULT 58;
DECLARE res VARCHAR(6);
IF cnt >= 80 THEN #条件之后接THEN
SET res := '优秀';
ELSEIF cnt >= 60 THEN #注意这里写为ELSEIF 没有空格
SET res := '及格';
ELSE #ELSE 表示最后一个分支
SET res := '重开算了';
END IF; #END IF;表示IF语句的结束
SELECT res;
END;
3.2 传参示例：
CREATE PROCEDURE p1(IN cnt INT, OUT res VARCHAR(10)) #'IN'表示传入参数,'OUT'表示输出
BEGIN #，'INOUT'表示传入也传出。相当于传入了指针
IF cnt > 80 THEN
SET res := '优秀';
ELSEIF cnt > 60 THEN
SET res := '及格';
ELSE
SET res := '重开算了';
END IF;
END;
CALL p1(99, @result);
SELECT @result;
3.3 CASE语句示例：
CREATE PROCEDURE p4(IN month INT)
BEGIN
DECLARE res VARCHAR(25);
CASE
WHEN month >= 1 AND month <= 3 THEN
SET res := '第一季度';
WHEN month >= 4 AND month <= 6 THEN
SET res := '第二季度';
WHEN month >= 7 AND month <= 9 THEN
SET res := '第三季度';
WHEN month >= 10 AND month <= 12 THEN
SET res := '第四季度';
ELSE
SET res := '非法参数';
END CASE;
SELECT res;
END;
CALL p4(6);
3.4 WHILE循环语句示例：
CREATE PROCEDURE wh(IN n INT)
BEGIN
WHILE n > 0 DO #满足条件，则干DO后面的事，END WHILE;表示于一次循环结束.
SET n = n - 1;
END WHILE;
SELECT n;
END;
CALL wh(5);
3.5 REPEAT语句示例(相当于do while)
CREATE PROCEDURE rep(IN n INT)
BEGIN
DECLARE res INT DEFAULT 0;
REPEAT
SET res = res + n;
SET n = n - 1;
UNTIL n <= 0
END REPEAT;
SELECT res;
END;
CALL rep(5);
3.6 LOOP语句示例(相当于无限循环但是带有continue和break的while语句)
CREATE PROCEDURE l2(IN n INT)
BEGIN
DECLARE res INT DEFAULT 0;
sum:LOOP #sum是给这个循环的一个标签
IF n <= 0 THEN
LEAVE sum; #ITERATE sum 则表示continue，继续该循环，LEAVE sum表示跳出该循环
END IF;
SET res = res + n;
SET n = n - 1;
END LOOP sum; #sum表示sum这个标签的循环语句结束
SELECT res;
END;
CALL l2(15);
3.7 CURSOR游标示例：
CREATE PROCEDURE p15(IN n DECIMAL(10,2))
BEGIN
DECLARE gname VARCHAR(255);
DECLARE gprice DECIMAL(10,2);
DECLARE cursor_goods CURSOR FOR SELECT price, goods_name FROM goods WHERE price < n; #声明游标
DECLARE exit HANDLER FOR NOT FOUND CLOSE cursor_goods; #创建一个退出条件并设置退出时执行的语句(关闭游标)
DROP TABLE IF EXISTS p12_test;
CREATE TABLE p12_test(
id INT PRIMARY KEY AUTO_INCREMENT,
price DECIMAL(10,2),
goods_name VARCHAR(255)
);
OPEN cursor_goods;#打开游标后循环遍历所有的数据
WHILE TRUE DO
FETCH cursor_goods INTO gprice, gname;
INSERT INTO p12_test(price, goods_name) VALUES(gprice, gname);
END WHILE;
END;
CALL p15(51.15);
3.8 条件处理程序
在 MySQL 存储过程中，条件处理程序用于处理运行时可能遇到的特定条件，如警告、未找到结果或其他 SQL 错误。根据不同的条件，可以选择继续执行程序、终止当前程序或执行其他清理操作。
语法
```sql
DECLARE handler_action HANDLER FOR condition_value
[statement]
```
handler_action：可以是 CONTINUE 或 EXIT。
CONTINUE：在遇到指定条件时，继续执行后续程序。
EXIT：在遇到指定条件时，终止当前程序的执行。
condition_value：指定的 SQL 条件，例如：
SQLSTATE 值，例如 02000（表示没有数据行被返回）。
SQLWARNING：所有以 01 开头的 SQLSTATE 代码的简写。
NOT FOUND：所有以 02 开头的 SQLSTATE 代码的简写。
SQLEXCEPTION：捕获所有未被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 代码。
执行示例可以看游标的示例语句
3.9 有返回值的函数
CREATE FUNCTION ad(n INT)
RETURNS INT DETERMINISTIC #定义返回值类型
BEGIN
DECLARE res INT DEFAULT 0;
WHILE n >= 0 DO
SET res = res + n;
SET n = n - 1;
END WHILE;
RETURN res;
END;
SELECT ad(15);
触发器
1. 基本语法
CREATE TRIGGER trigger_name
[ BEFORE | AFTER ] [ INSERT | UPDATE | DELETE ]
ON table_name
FOR EACH ROW
BEGIN
-- 触发器逻辑
END;