当前位置：首页 > news >正文

MySQL知识点（第一部分）

news 来源：原创 2025/8/23 12:20:33

MySQL

基础：

1、SQL语句的分类：

DDL：用于控制数据库的操作
DML：用于控制表结构的字段，增、删、修
DQL：用于查询语句
DCL：用于管理数据库，用户，数据库的访问权限。

2、MySQL的DQL语句的执行顺序：

先执行 from where group by select order by limit

3、事务

1、事务的实现方式：

方式一：

查询当前系统是否为自动提交事务，如果是 0 是手动提交，1 是自动提交

select @@autocommit;

设置为手动提交

set @@autocommit = 0;

手动提交事务

commit;

回滚事务

rollback;

方式二：

手动开启事务，表示下面的操作要手动控制事务

start transaction;

2、事务的四大特性(ACID)：

1、原子性：是最小分割的操作单元，要么全部成功，要么全部失败。

2、一致性：事务完成时，必须使所有的数据都保持一致。

3、隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

4、持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

3、并发事物的问题：

1、脏读：一个事务读取到另一个事务还没有提交的数据。

2、不可重复读：一个事务先后读取同一条记录，但是两次读取的数据不同。

3、幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了一个幻影，再次查询又查

不到，因为已经解决了不可重读的问题，因此会出现幻读的问题。

4、事务的隔离级别(解决并发事务的问题)：

1、Read uncommitted ：什么都不能解决。

2、Read committed ：只能解决脏读。

3、Repeatable Read(默认) ：只解决脏读、不可重复读。

4、Serializable ：所有的都能解决。

-- 查看事务的隔离级别
select @@transaction_isolation;
-- 修改事务的隔离级别
-- session 表示仅在当前窗口有效，global 表示全局有效。
set session transaction isolation level serializable;

进阶：

1、储存引擎：

1、MySQL的体系结构：

1、连接层：与客户端的链接，密码的校验等

2、服务层：所有的查询接口，优化器，部分函数的执行等

3、引擎层：可插拔式的存储引擎，不同的引擎具有不同的功能，默认InnoDB

4、存储层：磁盘文件，日志等

2、存储引擎的介绍：

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎可以被称为表类型。

-- 查看数据库所支持的存储引擎
show engines;

3、存储引擎的特点：

1、InnoDB：

特点
- DML操作遵循ACID模型，支持事务。
- 支持行级锁，提高并发性。
- 支持外键约束，保证数据的完整性和正确性。
文件
- xxx.ibd ：xxx 代表的是表名，innoDB 引擎的每张表都会对应这样一个表空间，存储该表的表结构、数据、索引。
- 参数：innodb_file_per_table: 表示是否开启每张表都对应一个表空间文件，默认是开启的。
逻辑空间结构：
表空间 —> 段 —> 区 —> 页 —> 行

2、MySAM

特点
- 不支持事务，不支持外键
- 支持表锁，不支持行锁
- 访问速度快
文件
- xxx.sdi 存储表结构信息
- xxx.MYD 存储数据
- xxx.MYI 存储索引

3、Memory（了解）：

特点
- 内存存放
- hash索引
文件
- xxx.sdi 存储表结构信息

2、索引

1、索引概述：

索引是一种有序的数据结构，用于高效的获取数据。

**优点：**提高检索效率，降低IO成本。通过索引排序，降低CPU的消耗。

**缺点：**索引也会占用存储空间，并且再插入和删除数据时，需要维护索引结构。

2、索引结构：

1、B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引。

二叉树的缺点：顺序插入时，会形成一个链表，查询性能大大降低，大数据量的情况下，层级越深，检索速度越慢。
红黑树的缺点：大数据量的情况下，层级越深，检索速度越慢。
B-Tree：不论是叶子节点还是非叶子节点，都会保存数据，导致保存大量数据时，只能增加树的高度，导致性能降低。
MySQL的B+Tree：所有数据都出现在叶子节点，叶子节点也形成一个双向链表。

2、Hash索引：底层数据结构使用hash表实现的，只有精确匹配索引类的查询才有效，不支持范围查询，排序操作。

先计算每行的hash值，再根据hash的字段，通过hash算法计算在hash表中的槽位，并存储这一行所计算出来的hash值来定位。

3、R-Tree(空间索引)：空间索引是MySAM引擎的一个特殊索引，只要用于地理空间数据类型，通常使用较少

4、Full-text(全文索引)：是一种通过建立倒排索引，快速匹配文档的方式，雷素与ES

3、索引分类：

1、在innoDB中储存引擎中可分为两种：注意：底层结构都是B+Tree，只不过是叶子节点的数据不同

唯一索引（了解）：不可重复
全文索引（了解）：类似于ES的倒排索引
聚集索引：将数据存储与索引放到一块，索引结构的叶子节点保存行数据。必须有，而且只能有一个。
- 聚集索引的选取规则：
  - 如果存在主键，主键索引就是聚集索引。
  - 如果不存在主键，将使用第一个唯一索引作为聚集索引。
  - 如果表中没有主键，或者没有适合的唯一索引，则innoDB会自动生成一个rowid作为隐藏的聚集索引。
二级索引：将数据与索引分开存储，索引结构的叶子节点关联的时对应的主键。可以存在多个。
查询流程：
由于二级索储存的是索引数据和该条数据的主键，对于非覆盖索引查询，都需要回表查询。

4、索引语法：

-- 创建索引：[唯一索引|全文索引] index 索引名 on 表名 (字段名1,字段2,...)
-- 索引表的名称一般为：idx_表明_字段名
create [UNIQUE|FULLTEXT] index index_name on table_name (index_col_name,...);
-- 查看索引
show index from table_name
-- 删除索引
drop index index_name on table_name

5、SQL性能分析

-- 查看数据库所有语句的执行频次：session表示当前会话的，global表示全局
show [session|global] status like 'Com_______';
-- 慢查日志-- 查看慢查询日志是否开启show variables like 'slow_query_log';-- 开启MySQL慢日志查询开关slow_query_log=1-- 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会被视为慢查询，记录慢查询的日志。long_query_time=2
-- profiling-- 查看当前数据库是否支持select @@have_profiling-- 是否开启select @@profiling-- 开启set profiling=1-- 查看show profiles
-- explain 查看搜索的执行情况explain select * from user;

6、索引的使用

1、最左前缀法则：

如果索引了多列，（联合索引），要遵循最左前缀法则，指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳过了某一列，索引将部分失效（后面的字段索引也失效）

就是如果一个索引表关联了多个字段，那末，查询时必须包含最左边的字段时，索引才生效，否则，索引不生效。如果关联的是三个字段，如：A B C 那么，在查询时，如果where A=’‘，B= ’‘，C= ’‘ ；索引生效，如果B = ’‘，C=’‘。索引失效，如果：A=’‘，C=’‘；A索引生效，C索引失效；只与存不存在有关，跟位置无关：C B A 也走索引，而且全部生效；

2、范围查询

如果使用了> 或 < 的字段，则其后面的索引失效，但是，如果使用的是 >= 或 <= 则不失效。

3、索引运算

如果索引字段进行函数运算，则索引失效

4、字符串不加单引号

字符串不加 ‘ ’ 则索引失效

5、模糊查询

尾部模糊（后面加%）查询走索引，后面模糊（前面加%）不走索引

6、or

or连接的索引，如果前面有索引，后面没索引，那么索引失效。解决：给字段建立索引。

7、数据分布

如果Mysql判断，走全表扫描快还是走索引快，那个快用哪个。

8、SQL提示

1、use index(索引名) ：可以用哪个索引

2、ignore index(索引名) ：忽略哪个索引

3、force index(索引名) ：必须使用哪个索引

9、覆盖索引

覆盖索引就是只查询二级索引，就能查出来需要的字段，不需要回表查询

select 后不要写 * 要写需要的字段

10、前缀索引

在处理比较长的索引的时候。

语法：create index idx_xxxx on table_name(column(n))表示我要将字符串的一部分前缀建立索引从而节省索引的空间。

通过数据的字段不重复的记录数 / 总记录数的值，如果越接近 1 ，就越好。

3、SQL优化

1、插入数据

insert优化
- 批量插入
- 手动提交事务
- 主键顺序插入
大批量插入数据
- 使用load指令
- 连接服务端时，加上参数：mysql --local-infile -u root -p
- 开启本地加载目录的开关：set global local_infile=1
- 执行load指令，将准备好的数据加载到表结构中：load data local infile '文件路径' into table '表名' fields terminated by ',' lines terminated by '\n'

2、主键优化

1、数据组织方式

在innoDB引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。

2、页分裂

在主键乱序插入的情况下，由于要保证在每一页中的主键是有序的。因此，当一个主键的位置被占用时，就会开辟一块新的页，将这个中一半的数据都移动到新的页上面，然后再将当前数据插入到对应的位置，最后修改页指针，确保每一页之间也是有序的。

3、页合并

当删除一行记录时，并没有被物理的删除，只是被标记为删除，当页中删除的记录数道道MERGE_THRESHOLD（阈值默认为 50%），就会寻找最靠近的页看看是否能将两个页合并以优化空间的使用。阈值可以在创建表时指定。

4、主键的设计原则

尽量降低主键的长度
插入时，尽量使用AUTO_INCREMENT主键自增
避免对主键的修改

3、order by 优化

① Using filesort ：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序

② Using index ：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

尽量使用覆盖排序，也可以调整排序缓冲区，默认是(256K)

4、group by 的优化

通过建立索引，并满足最左前缀原则。

5、limit 的优化

一般通过覆盖索引 + 子查询的方式。

select * from user u,(select id from user order by id limit 2000000,10) a where u.id = a.id;

也就等于

select * from user u right join (select id from user order by id limit 2000000,10) a on a.id = u.id;

6、count 的优化

目前没有好的优化记录，但是可以自己维护总数据数。

count(主键)：直接把每一行的id取出来，返回服务层后累加。
count(字段)：会判断字段是否为空，为空则不记录数，如果用not null 约束后，则不用判断
count(1)：遍历整张表，不取值，每一行放一个 1 进去，后累加
count(*)：由于Mysql专门做了优化，因此不取值，直接按行累加（建议使用）

3、Update 语句

执行update语句时，要根据索引进行更新，否则会将行锁升级为表锁，锁住整张表，提交事务之前，其他的事务不能对这张表做修改操作。

4、视图

1、概括

视图是一种虚拟存在的表。只是保存了查询SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就是创建SQL语句上。

2、创建/修改/删除视图

-- create or replace view 视图名 as 查询语句
create or replace view test as select * from user;-- 检查选项：with [cascaded（默认）,local] check option
create or replace view test as select * from user where id <= 30 with [cascaded,local] check option;
-- 当向视图中插入、更新、删除时 id > 30 的数据时，就会报错失败。
-- cascaded：由于视图可以依赖其他的视图创建，这个可以检查这个视图所依赖的视图，
-- 注意：如果创建的视图没有检查选项，那么即便是他所依赖的视图有检查选项，也不会检查，检查选项只能向上传递，不能向下传递。
--  	同时，如果上一层视图没有定义检查选项，那末也会检查（区别）-- local：表示，检查当前视图的的条件，同时也会递归去找上一层，但是，如果上一层没有定义检查选项，就不检查上一层。
-- 删除视图
drop view 视图名

3、查询/修改视图

-- 查询视图的创建语句
show create view 视图名
-- 由于试图是一张虚拟的表，也可以通过查询表的方式，查询视图
select * from 视图名 where ....-- 修改就是正常表的修改，但是，如果视图是定义的聚合函数，那末视图就不可进行插入，删除等操作。

5、存储过程

1、概括：

存储过程是事先金经过编译并储存在数据库中的一段SQL集合，调用存储存储过程可简化开发，减少数据库和应用服务器之间的传输，提高效率。简单来说：就是数据库SQL语言层面的代码封装和重用。

2、特点：

封装、重用。
可以接收参数，返回数据
减少网络交互，减少网络的开销

3、创建、调用存储过程：

创建

create procedure 储存过程名([参数列表])
begin
-- SQL语句
end：

调用
```
call 名称([参数])
```
查看创建命令、删除
```
show procedure 储存过程名
```

MySQL知识点（第一部分）

MySQL 基础： 1、SQL语句的分类： DDL：用于控制数据库的操作DML：用于控制表结构的字段，增、删、修DQL：用于查询语句DCL：用于管理数据库，用户，数据库的访问权限。 2、M…...

编程日记 2025/8/23 12:20:33

Lua怎么学？Lua编程实战：从基础语法到高级特性

朋友们，大家好，我是袁庭新。我的《Lua编程实战：从基础语法到高级特性》教程上线了！这是一套从零掌握Lua编程，涵盖基础到高级，以实战驱动，快速上手Lua开发的精品教程！ 我为什么要研发…...

编程日记 2025/8/18 8:06:23

Qt常用控件之纵向列表QListWidget

纵向列表QListWidget QListWidget 是一个纵向列表控件。 QListWidget属性属性说明currentRow当前被选中的是第几行。count一共有多少行。sortingEnabled是否允许排序。isWrapping是否允许换行。itemAlignment元素的对齐方式。selectRectVisible被选中的元素矩形是否可见。s…...

编程日记 2025/8/23 12:19:23

Manus无需邀请码即可使用的平替方案-OpenManus实测

文章目录 Manus 简介核心定位技术架构核心特点应用场景性能表现用户体验发展计划OpenManus技术架构与设计理念核心功能特性应用场景案例与闭源Manus的差异对比安装使用与实战演示执行过程记录简单案例-快速写一个helloworld的java程序复杂案例-分析特斯拉汽车近三年财务数据并生…...

编程日记 2025/8/23 12:07:29

git如何解除远程仓库改变远程仓库地址

这三个命令通常一起使用来更改项目的远程仓库地址： 查看当前远程仓库配置：git remote -v移除旧的远程仓库配置：git remote remove origin添加新的远程仓库配置：git remote add origin 新的远程仓库地址要解除当前项目的远程仓库…...

编程日记 2025/8/23 11:25:10

VSCode 配置优化指南：打造高效的 uni-app、Vue2/3、JS/TS 开发环境

VSCode 配置优化指南，适用于 uni-app、Vue2、Vue3、JavaScript、TypeScript 开发，包括插件推荐、设置优化、代码片段、调试配置等，确保你的开发体验更加流畅高效。 1. 安装 VSCode 如果你还未安装 VSCode，可前往 VSCode 官网下载最新版并安装。 2. 安装推荐插件 (1) Vue…...

编程日记 2025/8/23 12:15:49

云计算：虚拟化、容器化与云存储技术详解

在上一篇中，我们深入探讨了网络安全的核心技术，包括加密、认证和防火墙，并通过实际案例和细节帮助读者全面理解这些技术的应用和重要性。今天，我们将转向一个近年来迅速发展的领域——云计算。云计算通过提供按需访问的计算资源，彻底改变了IT基础设施的构建和管理方式。本…...

编程日记 2025/8/23 12:19:22

oracle 数据导出方案

工作中有遇到需要将oracle 数据库表全部导出，还需要去除表数据中的换行符。方案 shell 设计封装函数 1 function con_oracle() 用于连接oracle 2 function send_file() 用于发送文件 3 主程序使用循环将所有表导出并发送到数据服务器主程序程序代码 #!…...

编程日记 2025/8/21 20:04:44

电商项目-秒杀系统(四)秒杀异步下单防止重复秒杀

一、防止恶意刷单解决在生产场景下，可能会有一些人会恶意访问当前网站，来进行恶意的刷单。这样会造成当前系统出现一些业务上的业务混乱，出现脏数据，或者造成后端访问压力大等问题。一般要解决这个问题的话，前端可…...

编程日记 2025/8/23 7:47:14

Express 中 get 参数获取

1. 使用 req.query 获取 URL 查询字符串参数在 GET 请求中，参数通常以查询字符串的形式附加在 URL 后面，格式为 ?参数名1值1&参数名2值2 。Express 里可通过 req.query 对象获取这些参数。 const express require("express"); const …...

编程日记 2025/8/23 12:18:00

《Python实战进阶》No17: 数据库连接与 ORM（SQLAlchemy 实战）

No17: 数据库连接与 ORM（SQLAlchemy 实战） 摘要本文深入探讨SQLAlchemy在复杂场景下的高级应用，涵盖四大核心主题： 会话生命周期管理：通过事件钩子实现事务监控与审计追踪混合继承映射：结合单表/连接表继…...

编程日记 2025/8/23 2:14:14

运行OpenManus项目（使用Conda）

部署本项目需要具备一定的基础：Linux基础、需要安装好Anaconda/Miniforge（Python可以不装好，直接新建虚拟环境的时候装好即可），如果不装Anaconda或者Miniforge，只装过Python，需要确保Python是3.…...

编程日记 2025/8/23 11:31:12

Linux一键美化命令行，一键安装zsh终端插件

zsh应该是很多人第一个用的Linux终端美化软件但是其安装略微复杂，让人有些困扰所以我花了两天写了一键安装脚本，实测运行后直接安装好适用于Ubuntu、Debian、Red Hat、macOS等系统直接安装好zsh 以及常用插件 autojump 跳转插件 zsh-syntax-highlig…...

编程日记 2025/8/21 8:35:39

OpenManus介绍及本地部署体验

1.OpenManus介绍 OpenManus，由 MetaGPT 团队精心打造的开源项目，于2025年3月发布。它致力于模仿并改进 Manus 这一封闭式商业 AI Agent 的核心功能，为用户提供无需邀请码、可本地化部署的智能体解决方案。换句话说，OpenManus 就像…...

编程日记 2025/8/21 20:14:21

2025-03-09 学习记录--C/C++-PTA 习题10-7 十进制转换二进制

合抱之木，生于毫末；九层之台，起于累土；千里之行，始于足下。💪🏻 一、题目描述 ⭐️ 裁判测试程序样例： #include <stdio.h>void dectobin( int n );int main() {int n;scanf(…...

编程日记 2025/8/23 11:24:01

计算机网络----主要内容简介

这里写目录标题章节概览每章的大体结构结构功能与服务的关系一些概念概念一概念二传统的网络层的工作方式（路由IP）现代的网络层的工作方式（SDN） 章节概览其中，网络层分为了两章下面的红色部分是部分选修每章的大…...

编程日记 2025/8/21 20:19:28

基于python大数据的招聘数据可视化与推荐系统

博主介绍：资深开发工程师，从事互联网行业多年，熟悉各种主流语言，精通java、python、php、爬虫、web开发，已经做了多年的设计程序开发，开发过上千套设计程序，没有什么华丽的语言，只有…...

编程日记 2025/8/18 14:43:44

VBA高级应用30例Excel中ListObject对象：提取表内单元格的格式元素

《VBA高级应用30例》（版权10178985），是我推出的第十套教程，教程是专门针对高级学员在学习VBA过程中提高路途上的案例展开，这套教程案例与理论结合，紧贴“实战”，并做“战术总结”，以…...

编程日记 2025/8/20 18:39:05

数据结构基础之《（19）—矩阵处理》

一、zigzag打印矩阵 Z字形打印矩阵 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 打印顺序：1,2,7,13,8,3,4,9,14... 核心技巧：找到coding上的宏观调度左上角有A、B两个点，A往右一步一步走，B往下一步一步走写一个…...

编程日记 2025/8/20 5:23:54

用Python写一个算24点的小程序

一、运行界面二、显示答案——递归介绍工作流程： 1. 基本情况：函数首先检查输入的数字列表 nums 的长度。如果列表中只剩下一个数字，它会判断这个数字是否接近 24（使用 abs(nums[0] - 24) < 1e-10 来处理浮点数精度问题&…...

编程日记 2025/8/19 4:26:09

基于深度学习的网络摄像头图像实时分类实践：从理论到完整实现

引言：智能视觉感知的新可能在人工智能技术蓬勃发展的今天，实时图像分类作为计算机视觉的基础任务之一，正在深刻改变着我们的生活。从智能手机的人脸解锁到无人超市的自动结算系统，从工业质检的缺陷检测到医疗影像的辅助诊断&…...

编程日记 2025/8/19 12:36:40

C++ 算法竞赛STL以及常见模板

目录 STL /*═══════════════ Vector ═══════════════*/ /*════════════════ Pair ════════════════*/ /*══════════════ String ════════════════*/ /*══════════…...

编程日记 2025/8/23 2:14:15

【资料分享】wireshark解析脚本omci.lua文件20250306版本发布（独家分享）

引言 omci.lua文件是Wireshark的OMCI协议解析插件的核心组件。它配合BinDecHex.lua，可以解析OMCI协议的数据包，提取出消息类型、受管实体标识、受管实体属性等关键信息，并以人类可读的形式显示在Wireshark的解码视图中，方便研发人…...

编程日记 2025/8/18 3:04:07

(dfs 单词搜索)leetcode 79

核心思路用双重循环以所有的位置都作为起始点开始遍历设置边界条件上下左右都搜一次，不合适就回来，二叉树思想经过的结点设置"#避免重复搜索导致数据混乱递归完后要还原原字符 #include<iostream> #include<vector> #include&l…...

编程日记 2025/8/18 15:32:45

Java常用集合与映射的线程安全问题深度解析

Java常用集合与映射的线程安全问题深度解析一、线程安全基础认知在并发编程环境下，当多个线程同时操作同一集合对象时，若未采取同步措施，可能导致以下典型问题： 数据竞争：多个线程同时修改数据导致结果不可预测状…...

编程日记 2025/8/23 11:00:18

【实战ES】实战 Elasticsearch：快速上手与深度实践-5.1.1热点分片识别与均衡策略

👉 点击关注不迷路 👉 点击关注不迷路 👉 点击关注不迷路文章大纲 5.1.1 Filebeat Logstash ES Kibana 全链路配置实1. 架构设计与组件选型1.1 技术栈对比分析1.2 硬件配置推荐 2. Filebeat 高级配置2.1 多输入源配置2.2 性能优化参数 3.…...

编程日记 2025/8/16 10:03:09

服务端 using System; using System.Collections.Generic; using System.Linq; using System.Net; using System.Net.Sockets; using System.Text; using System.Threading.Tasks;namespace TeachTcpServer {class Program{static void Main(string[] args){#region 知识点一 …...

编程日记 2025/8/22 5:55:22