MySQL学习笔记5【SQL优化/视图/存储过程/触发器】

MySQL学习笔记

SQL优化

1. 插入数据优化

普通插入：

  • 采用批量插入：

    • 每次插入不建议超过1000条记录，这样可以减少事务开销，提高性能。
    • 示例：
    INSERT INTO tb_user (name, age) VALUES ('Alice', 25), ('Bob', 30), ...;

  • 手动提交事务：

    • 在插入过程中手动控制事务的开始与提交，以减少自动提交的次数。
    • 示例：
    START TRANSACTION;
INSERT INTO tb_user (name, age) VALUES ('Alice', 25);
... 
COMMIT;

  • 顺序插入主键：

    • 使用自增主键，避免随机插入造成的页分裂，提升插入速度。

大批量插入：

  • 使用 LOAD DATA INFILE：

    • 当需要插入大量数据时，通过文件导入的方式提升性能。
    • 客户端连接时加上参数 --local-infile：
    mysql --local-infile -u root -p
    • 设置全局参数：
    SET GLOBAL local_infile = 1;
    • 使用示例：
    LOAD DATA LOCAL INFILE '/path/to/file.sql' INTO TABLE tb_user 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
2. 主键优化

  • 设计原则：

    • 在满足业务需求的情况下，尽量降低主键的长度。
    • 尽量选择顺序插入，使用 AUTO_INCREMENT 自增主键。
    • 避免使用 UUID 或其他自然主键（如身份证号），以降低索引效率。

  • 存储与索引维护：

    • 在 InnoDB 中，数据根据主键顺序组织。
    • 页分裂：页可以为空，也可以填充部分，也可以填充100%，每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。
    • 页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录到达 MERGE_THRESHOLD（默认为页的50%，可自定义，可以在建表，创建索引的时候指定），InnoDB会开始寻找最靠近的页（前后）看看是否可以将这两个页合并以优化空间使用。
3. 排序优化 (ORDER BY)

  • 使用索引优化排序：

    • 使用索引按顺序读取数据，避免 Using filesort。
    • 如果排序字段全部为升序或降序，可直接利用索引。
    • 如果有多字段排序（升序与降序），需创建联合索引。

  • 示例：

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];

  • 删除

    DROP VIEW [IF EXISTS] 视图名称 [视图名称]
2. 检查

如果对视图进行插入操作，那么数据将会插入原表之中，如果在创建视图时使用了WITH CHECK OPTION字段，那么在进行插入或更新时将会受到创建视图时设置的检查的限制。

两个检查选项：CASCADED 和 LOCAL ，默认值为 CASCADED。

  • CASCADED：会检查本视图以及递归检查本视图创建时所依赖的视图设置的限制。
  • LOCAL：在空的基础上会检查本视图的限制条件，并向上递归检查本视图所依赖的并且设置了检查选项的视图的限制条件
  • 空(即不加检查选项)：不会检查本视图的条件，但是会向上检查依赖的并设置了检查选项的视图的限制条件。
3. 更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新

  1. 聚合函数或窗口函数 ( SUM()、MIN()、MAX()、COUNT() 等 )
  2. DISTINCT
  3. GROUP BY
  4. HAVING
  5. UNION 或者UNION ALL
#插入失败的例子
REATE stu_v_count AS SELECT COUNT(*) FROM student;

INSERT INTO stu_v_count VALUES(10);

作用

  • 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件，只需要用户针对于视图进行操作。

  • 安全 数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据，能够屏蔽一些比较敏感的信息，比如密码，身份证号。

  • 数据独立，视图可帮助用户屏蔽真实表结构变化带来的影响

  • 总而言之 类似于给表加上了一个外壳，通过这个外壳访问表的时候，只能按照所设计的方式进行访问与更新。

存储过程

简而言之，就是数据库中的函数。

1. 语法

  • 创建

    CREATE PROCEDURE 存储过程名称( [参数] ) 

BEGIN
	 SQL 语句 
END;

  • 调用

    CALL [存储过程名称](传入参数);

  • 删除

    DROP PROCEDURE [ IFEXISTS ] [存储过程名称];

    tips: 在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter来指定SQL语句的结束符。默认是以分号作为结束符。delimiter $$，则$$符作为结束符。

2. 用户自定义变量

  • 定义方法：

    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 错误。根据不同的条件，可以选择继续执行程序、终止当前程序或执行其他清理操作。

  • 语法

    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;
2. 作用

触发器（Trigger）用于在对表进行插入（INSERT）、更新（UPDATE）或删除（DELETE）操作时，自动执行某一特定操作。触发器可以帮助我们维持数据完整性、自动记录审计日志、实现复杂的业务逻辑等。

3. 注意事项
  • 触发器会在每一行影响的情况下执行，因此在高并发或大批量数据处理时会对性能有影响。
  • 触发器不能被直接调用，并且不能修改调用触发器的表。
  • 在设计触发器时，要注意避免在触发器内进行无限递归调用。

结语

关于这部分，我主要给的并不是语法示例而是一段语句示例，这样应该更有助于理解存储过程那些语法知识，感觉这部分有点奇怪的感觉了，不知道开发过程实际会怎么使用呢。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/953771.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

初识JVM HotSopt 的发展历程

初识JVM HotSopt 的发展历程

目录 导学 目前企业对程序员的基本要求 面向的对象 实战 学习目标 JVM 是什么 JVM 的三大核心功能 各大 JVM look 看一下虚拟机 HotSopt 的发展历程 总结 导学 目前企业对程序员的基本要求 面向的对象 实战 学习目标 JVM 是什么 JVM 的三大核心功能 即时编译 主要是…
阅读更多...
【pytorch】注意力机制-1

【pytorch】注意力机制-1

1 注意力提示 1.1 自主性的与非自主性的注意力提示 非自主性提示&#xff1a; 可以简单地使用参数化的全连接层&#xff0c;甚至是非参数化的最大汇聚层或平均汇聚层。 自主性提示 注意力机制与全连接层或汇聚层区别开来。在注意力机制的背景下&#xff0c;自主性提示被称为查…
阅读更多...
大数据技术Kafka详解 ⑤ | Kafka中的CAP机制

大数据技术Kafka详解 ⑤ | Kafka中的CAP机制

目录 1、分布式系统当中的CAP理论 1.1、CAP理论 1.2、Partitiontolerance 1.3、Consistency 1.4、Availability 2、Kafka中的CAP机制 C软件异常排查从入门到精通系列教程&#xff08;核心精品专栏&#xff0c;订阅量已达600多个&#xff0c;欢迎订阅&#xff0c;持续更新…
阅读更多...
ESP-IDF学习记录（5） 画一块esp32-c3 PCB板

ESP-IDF学习记录（5） 画一块esp32-c3 PCB板

最近看了半个多月&#xff0c;趁着嘉立创官方活动&#xff0c;研究esp32-c3规格书&#xff0c;白嫖PCB 和元器件。原本计划按照官方推荐的搞个四层板&#xff0c;结果打样太贵&#xff0c;火速改成双层板&#xff0c;用了官方的券。小于10*10,也可以使用嘉立创的免费打样。 下面…
阅读更多...
nginx 实现 正向代理、反向代理 、SSL（证书配置）、负载均衡 、虚拟域名 ，使用其他中间件监控

nginx 实现 正向代理、反向代理 、SSL（证书配置）、负载均衡 、虚拟域名 ，使用其他中间件监控

我们可以详细地配置 Nginx 来实现正向代理、反向代理、SSL、负载均衡和虚拟域名。同时&#xff0c;我会介绍如何使用一些中间件来监控 Nginx 的状态和性能。 1. 安装 Nginx 如果你还没有安装 Nginx&#xff0c;可以通过以下命令进行安装&#xff08;以 Ubuntu 为例&#xff0…
阅读更多...
Netty 入门学习

Netty 入门学习

前言 学习Spark源码绕不开通信&#xff0c;Spark通信是基于Netty实现的&#xff0c;所以先简单学习总结一下Netty。 Spark 通信历史 最开始: Akka Spark 1.3&#xff1a; 开始引入Netty&#xff0c;为了解决大块数据&#xff08;如Shuffle&#xff09;的传输问题 Spark 1.6&…
阅读更多...
鸿蒙报错Init keystore failed: keystore password was incorrect

鸿蒙报错Init keystore failed: keystore password was incorrect

报错如下&#xff1a; > hvigor ERROR: Failed :entry:defaultSignHap... > hvigor ERROR: Tools execution failed. 01-13 16:35:55 ERROR - hap-sign-tool: error: Init keystore failed: keystore password was incorrect * Try the following: > The key stor…
阅读更多...
IDEA的Git界面(ALT+9)log选项不显示问题小记

IDEA的Git界面(ALT+9)log选项不显示问题小记

IDEA的Git界面ALT9 log选项不显示问题 当前问题idea中log界面什么都不显示其他选项界面正常通过命令查询git日志正常 预期效果解决办法1. 检查 IDEA 的 Git 设置2. 刷新 Git Log (什么都没有大概率是刷新不了)3. 检查分支和日志是否存在4. 清理 IDEA 缓存 (我用这个成功解决)✅…
阅读更多...
ffmpeg硬件编码

ffmpeg硬件编码

使用FFmpeg进行硬件编码可以显著提高视频编码的性能&#xff0c;尤其是在处理高分辨率视频时。硬件编码利用GPU或其他专用硬件&#xff08;如Intel QSV、NVIDIA NVENC、AMD AMF等&#xff09;来加速编码过程。以下是使用FFmpeg进行硬件编码的详细说明和示例代码。 1. 硬件编码支…
阅读更多...
65.在 Vue 3 中使用 OpenLayers 绘制带有箭头的线条

65.在 Vue 3 中使用 OpenLayers 绘制带有箭头的线条

前言 在现代的前端开发中&#xff0c;地图已经成为许多项目的核心功能之一。OpenLayers 是一个强大的开源地图库&#xff0c;它提供了丰富的功能和高度的定制化支持。在本篇文章中&#xff0c;我将向大家展示如何在 Vue 3 中使用 OpenLayers 绘制带有箭头的线条。 我们将实现…
阅读更多...
C++内存泄露排查

C++内存泄露排查

内存泄漏是指程序动态分配的内存未能及时释放&#xff0c;导致系统内存逐渐耗尽&#xff0c;最终可能造成程序崩溃或性能下降。在C中&#xff0c;内存泄漏通常发生在使用new或malloc等分配内存的操作时&#xff0c;但没有正确地使用delete或free来释放这块内存。 在日常开发过程…
阅读更多...
Ubuntu上，ffmpeg如何使用cuda硬件解码、编码、转码加速

Ubuntu上，ffmpeg如何使用cuda硬件解码、编码、转码加速

本文使用 Ubuntu 环境。Ubuntu 直接使用 APT 安装的就支持 CUDA 加速。本文使用这样下载的版本进行演示&#xff0c;你自己编译或者其他源的版本可能会不同。 ffmpeg 的一些介绍&#xff0c;以及 macOS 版本的 ffmpeg 硬件加速请见《macOS上如何安装&#xff08;不需要编译安装…
阅读更多...
linux: 文本编辑器vim

linux: 文本编辑器vim

文本编辑器 vi的工作模式 (vim和vi一致) 进入vim的方法 方法一:输入 vim 文件名 此时左下角有 "文件名" 文件行数,字符数量 方法一: 输入 vim 新文件名 此时新建了一个文件并进入vim,左下角有 "文件名"[New File] 灰色的长方形就是光标,输入文字,左下…
阅读更多...
调用企业微信新建日程 API 报 api forbidden 的解决方案

调用企业微信新建日程 API 报 api forbidden 的解决方案

报错详细信息&#xff1a; {"errcode":48002,"errmsg":"api forbidden, hint: [1266719663513970651415782], from ip: xxx.xxx.xxx.xxx, more info at https://open.work.weixin.qq.com/devtool/query?e48002" } 解决方案&#xff1a; 1. 登…
阅读更多...
rtthread学习笔记系列(4/5/6/7/15/16)

rtthread学习笔记系列(4/5/6/7/15/16)

文章目录 4. 杂项4.1 检查是否否是2的幂 5. 预编译命令void类型和rt_noreturn类型的区别 6.map文件分析7.汇编.s文件7.1 汇编指令7.1.1 BX7.1.2 LR链接寄存器7.1.4 []的作用7.1.4 简单的指令 7.2 MSR7.3 PRIMASK寄存器7.4.中断启用禁用7.3 HardFault_Handler 15 ARM指针寄存器1…
阅读更多...
微软与腾讯技术交锋，TRELLIS引领3D生成领域多格式支持新方向

微软与腾讯技术交锋，TRELLIS引领3D生成领域多格式支持新方向

去年 11 月&#xff0c;腾讯推出 Hunyuan3D 生成模型&#xff0c;是业界首个同时支持文字和图像生成 3D 的开源大模型。紧接着不到一个月&#xff0c;微软便发布了全新框架 TRELLIS&#xff0c;加入 3D 资产生成领域的竞争中。TRELLIS 支持多格式输出&#xff0c;包括辐射场、3…
阅读更多...
【C++】类与对象（中上）(难点部分)

【C++】类与对象（中上）(难点部分)

目录 &#x1f495;1.类的默认成员函数 &#x1f495;2.构造函数 &#x1f495;3.析构函数 &#x1f495;4.缺省值 &#x1f495;5.拷贝构造函数 &#xff08;最新更新时间——2025.1.14&#xff09; 这世间没有绝境 只有对处境绝望的人 &#x1f495;1.类的默认成员函数 默…
阅读更多...
Apache Hop从入门到精通 第三课 Apache Hop下载安装

Apache Hop从入门到精通 第三课 Apache Hop下载安装

1、下载 官方下载地址&#xff1a;https://hop.apache.org/download/&#xff0c;本教程是基于apache-hop-client-2.11.0.zip进行解压&#xff0c;需要jdk17&#xff0c;小伙伴们可以根据自己的需求下载相应的版本。如下图所示 2、下载jdk17&#xff08;https://www.microsoft…
阅读更多...
springboot房屋租赁管理系统

springboot房屋租赁管理系统

Spring Boot房屋租赁管理系统是一种基于Spring Boot框架构建的&#xff0c;旨在解决传统租房市场中房源信息更新不及时、虚假信息泛滥、交易流程繁琐等问题的信息化解决方案。 一、系统背景与目的 随着城市化进程的加快和人口流动性的增强&#xff0c;租房市场需求急剧增长。…
阅读更多...
计算机网络 （35）TCP报文段的首部格式

计算机网络 （35）TCP报文段的首部格式

前言 计算机网络中的TCP&#xff08;传输控制协议&#xff09;报文段的首部格式是TCP协议的核心组成部分&#xff0c;它包含了控制TCP连接的各种信息和参数。 一、TCP报文段的结构 TCP报文段由首部和数据两部分组成。其中&#xff0c;首部包含了控制TCP连接的各种字段&#xff…
阅读更多...
最新文章

Copyright @ 2022~2023