数据库

时光倒流七十年
221 阅读32分钟

几种引擎

定义：MySQL中的数据用不同的技术存储在文件或者内存中，这些技术中每一种技术都使用不同的存储技巧和索引技巧，锁定水平并且最终提供广泛的不同的功能和能力。这些不同的技术及配套的相关功能在MySQL中被称为存储引擎（也被称作表类型）

几种常用引擎：有myisam，innodb，MySQL5.1版本前 默认myisam引擎，5.5以后默认innodb引擎

区别

特性myisaminnodb
事务不支持支持事务，事务安全
锁只有表锁支持行级锁
外键不支持支持外键
使用场景简单，速度快，小型应用，查询比较多插入更新的场景，因为myisam表级锁会很慢，innodb更快
存储结构3个文件，表定义文件，数据文件，索引文件2个文件，表定义文件，数据和索引文件是放在一起的

MySQL组成部分

组成：连接层、SQL 层、存储引擎层

1、连接层：客户端和服务器端建立连接，客户端发送 SQL 到服务器端。

2、SQL 层：对 SQL 语句查询处理。

3、存储引擎层：存储或读取数据库文件

image.png

SQL层执行流程：SQL 语句 -> 缓存查询 -> 解析器 -> 优化器 -> 执行器

1、缓存查询：Server 如果查找到 SQL 语句有缓存，则返回数据给客户端，否则就执行下一步。

注：MySQL 8.0 将抛弃缓存功能，因为查找缓存的效率不高，只有是在静态数据或数据变化很少的场景下有价值。

image.png

2、解析器：对 SQL 进行语句分析、语义分析。

3、优化器： 确认 SQL 语句执行路径，比如根据全表检索还是根据索引来检索。

4、执行器：执行前判断是否有权限，有则执行 SQL 语句返回结果。

image.png

存储引擎：采用插件的模式，可根据不同的数据处理需要选择不同的存储引擎，每个MySQL下面可能有几个存储引擎，想用哪个用哪个

（有时候，MySQL的优化器不是那么强大，所以需要我们自己取优化查询的SQL语句）

存储整形

1.mysql 里面有几种存储整形的类型，根据自己存储的数值大小确定要使用的类型

tinyint 1个字节8个bit，范围 有符号 -128～127 无符号 0～255，一般可以用来存储状态值

smallint 2个字节16个bit，有符号 -32768～32767 无符号 0～65535

mediumint 3个字节，有符号 -8388608～8388607 无符号 0～16777215

int 4个字节，有符号 -2147483648～2147483647 无符号 0～4294967295

bigint 8个字节，有符号 -9,223,372,036,854,775,808～9,223,372,036,854,775,807 无符号 0~2^64-1

溢出
一个int的字段
image.png 插入一个会溢出的数据

image.png 只会显示这个类型的最大值

image.png

展示宽度M
sql里类型后面跟的数字，跟在磁盘中的存储没有任何关系，各个类型占用的存储是固定的，int(M) tinyint(M)后面的M只是用来展示的宽度，如果实际数据超过这个宽度，按照实际宽度展示

修改room_id字段展示5位宽度

image.png 往里面插入三个数，展示都是按照实际位数展示，设置的M没有什么卵用，所以这里的M需要跟zerofill功能一起使用才会有区别，比如100 就会展示00100，其他超过的按照实际位数展示

image.png

另外，终于知道为啥业务里面都是写的int(11)，因为int加上符号正好是11位宽度，就是全都展示的意思，但是其实没啥用，估计前面的人写了，后面的人跟风抄的吧，比如我。。。

www.cnblogs.com/jiechn/p/44…

索引

类型

1.主键索引

键索引是一种特殊的唯一索引，不允许有空值

2.单列索引

3.复合索引

复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

4.唯一索引

上面是按照类型来说，还有种按照数据存储来说的聚簇索引，就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页，聚簇索引

索引的使用

整形索引 比 字符串索引好，原因

1.字符串比较涉及一些ASCII转码，展示也涉及编码转换，比较浪费时间

2.字符串比较大，浪费存储

为啥索引 要自增

1.新增数据的时候可以直接在后面插入数据即可，避免了中间插入，消耗时间，甚至可能页分裂

所以索引来说，整形比字符串好，自增比不自增好

like操作

如果like 以%开头，那么索引会失效，会变成全表查询，除非 查询的字段和模糊匹配的字段都在索引列上才能使用索引

索引优化规则

1.前导模糊查询不能使用索引。

2.union、in、or 都能够命中索引，建议使用 in。in会使用索引，但是范围过大的时候，可能会索引失效，全表查询，in后面的索引字段有可能用到索引

3.负向条件查询 not in不能使用索引，可以优化为 in 查询。

4.联合索引最左前缀原则(又叫最左侧查询)

5.范围列可以用到索引(联合索引必须是最左前缀)。但是范围列后面的列无法用到索引，如果一个查询包含两个范围列无法全部用到查询

6.把计算放到业务层而不是数据库层。

7.强制类型转换会全表扫描

8.更新十分频繁、数据区分度不高的字段上不宜建立索引。

9.利用覆盖索引来进行查询操作，避免回表。

10.如果有 order by、group by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能

11.使用短索引(又叫前缀索引)来优化索引。

12.建立索引的列，一般允许为 null。

13.利用延迟关联或者子查询优化超多分页场景。

14.业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

orderby使用索引

where a=? and b=? order by c;可以使用到索引（a_b_c）

下面条件可以用上该组合索引查询 A>5

A=5 AND B>6

A=5 AND B=6 AND C=7

A=5 AND B IN (2,3) AND C>5

下面条件将不能用上组合索引查询：

B>5 ——查询条件不包含组合索引首列字段

B=6 AND C=7 ——查询条件不包含组合索引首列字段

下面条件将能用上部分组合索引查询（范围查询的字段后面就用不到索引了）：

A>5 AND B=2 ——当范围查询使用第一列，查询条件仅仅能使用第一列

A=5 AND B>6 AND C=2 ——范围查询使用第二列，查询条件仅仅能使用前二列

组合索引排序的各种场景

有组合索引 Index(A,B)

下面条件可以用上组合索引排序：

ORDER BY A——首列排序

A=5 ORDER BY B——第一列过滤后第二列排序

ORDER BY A DESC, B DESC——注意，此时两列以相同顺序排序

A>5 ORDER BY A——数据检索和排序都在第一列

下面条件不能用上组合索引排序：

ORDER BY B ——排序在索引的第二列

A>5 ORDER BY B ——范围查询在第一列，排序在第二列

A IN(1,2) ORDER BY B ——理由同上

ORDER BY A ASC, B DESC ——注意，此时两列以不同顺序排序

主键索引和唯一索引

标题主键唯一索引
本质是一种约束是一种索引类型
空值不允许有空值，能够唯一识别某一行记录允许有空值，唯一索引会失效，两个空值的记录不会有唯一索引冲突报错，视作未知
场景一般用自增id做主键索引，但是主键并不一定要自增有单和复合唯一索引
数量一个表最多一个主键表可以有多个唯一索引

explain里面的信息

业务中遇到场景记录

创建带日期的表的时候，可能要一次性建很多，不想一个个建太麻烦，可以用

create table t2 like t1;

循环这句话就可以创建只有表名稍有区别的若干表 比如下面 表示某一周的小心心信息的表，用代码循环获取日期 可以获取建表语句里面的表名

CREATE TABLE `small_heart_20220509` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',

`uid` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户uid',

`ruid` bigint(20) NOT NULL DEFAULT '0' COMMENT '主播uid',

`count` bigint(20) NOT NULL DEFAULT '0' COMMENT '小心心数量',

`ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`mtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

PRIMARY KEY (`id`),

KEY `ix_mtime` (`mtime`),

UNIQUE KEY `uk_ruid_uid` (`ruid`,`uid`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='小心心数量表';

CREATE TABLE small_heart_20220516 like small_heart_20220509 ;

CREATE TABLE small_heart_20220523 like small_heart_20220509 ;

CREATE TABLE small_heart_20220530 like small_heart_20220509 ;

CREATE TABLE small_heart_20220606 like small_heart_20220509 ;

链接数过多怎么解决

原因

1业务错误用法，用完没有及时关闭，sleep时间又很大，一直占着链接不释放
2慢查询比较多，业务需要优化
3数据库最大链接数设置不合理，不能及时处理业务请求 \

解决

1.sleep时间合理缩短，这样及时没有主动关闭也能很快超时结束
2.processlist查看连接情况，找到异常链接的业务，如果慢查询优化服务；没有及时关闭就就是关闭
3.调大数据库最大连接数

order by

select city,name,age from t where city='杭州' order by name limit 1000,100 ;

第一步：根据where条件和统计信息生成执行计划，得到数据。

第二步：将得到的数据排序。

当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则排序操作。

第三步：返回排序后的数据。

实际上第二步中mysql的排序算法一共存在两种：

全字段排序：获取select的全部字段放入内存，并且利用内存和磁盘根据name进行排序，然后从排好序的结果中获取指定的条数。如果查询要返回的字段很多的话，那么内存里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

rowid排序：逐行获取满足条件的记录，但是此时仅仅获取id和name到内存中，并且进行排序，先获取满足条件记录对应的id，然后再根据id获取对应记录的city,name,age等具体信息；这种方式避免了大量数据在内存中排序的问题，但需要两次查表。

主键索引也被称为聚簇索引（clustered index），也叫作聚集索引。其余都称呼为非主键索引也被称为二级索引（secondary index），也叫作辅助索引

考点： 聚簇索引（即主键索引） 和 二级索引 查询数据需要几次io操作

这个文章写的挺好 blog.csdn.net/ccw_922/art…

面试题：主键索引查询2千万的数据量，经过几次io操作

一个3层的B+树就可以表示千万级的数据。而要访问到最终的数据，只需要3次IO，这在性能上是一个巨大的提升。其实，树的根节点往往在内存中，那么访问磁盘的次数就更少了

一般来说B+Tree的高度一般都在2-4层，MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作（根节点的那次不算磁盘I/O）

B+树层数与对应存储数据量大概估计

须知： MySQL的InnoDB存储引擎的最小存储单元是页,大小默认是16k,页有两个存储类型，一个是存放B+树叶节点数据，一个是存放B+树非叶节点的 “键 + 指针”。

假设一条记录占用1k内存，对于非叶子结点的页能够拥有的指针数是1170（见下图），所以高度为2的B+树数据量有1170（根节点最多能指向的指针数）* 16（假设一个记录1k，一页16k，所以一页有16条记录） = 18720，高度为3的B+树数据量有1170117016=21902400（其实就是多乘一个1170），2190w条记录，约2000w条记录 image.png

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限

这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。文本中这个图是 show processlist 的结果，其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。

image.png 客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接

最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符

（name，age）这个联合索引来分析。如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引

B树和B+树的区别

www.cnblogs.com/JCpeng/p/15…

select * from t1 where c=1 and d = 2 and b=4能用到联合索引ix_b_c_d吗？

能，最左前缀不是看书写的顺序，而是看最左的字段有没有，b有的话，是能用到的

select * from t1 where b = 1 and d = 2 会用到索引吗

会用到，是属于using index condition，using index condition的含义就是在用b=1搜索到对应范围之后直接根据这个范围在索引树上根据d=1找到对应的数据然后再回表找到所有的字段，而不是在索引树上获取所有符合b=1的拿到主键去回表，回表数据再根据d=2筛选，这样效率比较低，回表次数会比 using index condition多

如果数据库有8条数据，b的范围从【1-8】，有联合索引ix_b_c_d

select * from t1 where b>1 能用到索引吗？

不能，一共8条数据，b>1能筛选出7条数据，select是返回所有的字段，用索引的话还需要全部去回表，所以相比于 从索引先去找到b=1，然后对后面的所有数据都回表查找 还不如直接在表里扫描来的快，所以是用不到索引的

select * from t1 where b >5 能用到索引吗

能用到索引，b>5 能筛选出少数数据比较精确，然后在回表，比直接全表扫描快一点，就可以用到索引

总结：任何查询都会有直接全表扫描这一种方式可选，具体实际看，不一定非得用索引，有时候全表比索引更快

select b from t1 where b>1 能用到索引

会用到索引，并且是属于 using index（覆盖索引），返回的字段就在索引树上不用回表，速度会很快

select b from t1 没有where条件还能用到索引ix_b_c_d吗

能用，在主表上虽然有b字段，但是索引树上字段比较少，每页能存储的记录更多，找到所有的b字段需要查的页数更少，性能上更优

select c from t1 能用到索引ix_b_c_d吗

能用到，你可能疑惑这个不符合最左前缀原则，这里不适用最左原则，最左一般是从上到下筛选用最左边字段，这里只需要去按照顺序拿数据就可以了，所以不需要符合这个原则

select * from t1 order by b,c,d 会用到索引吗

不会，分析一下：有两种方式，1全表扫描，把所有的数据拿到内存排序，不需要回表 2.走索引，不需要排序，但是8条数据要回8次表 实际情况是全表扫描，因为数据量比较少，内存排序非常快，而索引需要回表这个性能相比就很慢了，所以走全表扫描更优

select b from t1 order by b,c,d 会用到

explain 返回的type代表什么含义

www.jianshu.com/p/a20b1a710…

关于extra里面的各种类型代表的含义

www.cnblogs.com/kerrycode/p…

using index -- 覆盖索引，不需要回表

using index condition --回表之前先用where里面的索引字段筛选数据避免多余的回表，具体看上面文章，可以确定的是这种情况肯定会回表

using filesort -- MySQL中无法利用索引完成的排序操作称为“文件排序”

using where --意味着通过索引或者表扫描的方式进程where条件的过滤，反过来说，也就是没有可用的索引查找

联合索引失效的一些场景 cloud.tencent.com/developer/a…

LEFT JOIN 是 SQL 中用于联接两个表的一种方式，它将返回左表的所有行，并与右表中匹配的行一起返回。如果没有匹配的行，右表的字段将会包含 NULL 值。这是一个常见的用法，用于检索左表中的记录及其与右表相关联的数据，如果没有匹配项，也会返回左表的数据。

下面是 LEFT JOIN 的一般语法：

SELECT *
FROM left_table
LEFT JOIN right_table ON left_table.column_name = right_table.column_name;
  • left_table：左表的名称。
  • right_table：右表的名称。
  • column_name：要匹配的列名称，通常是两个表中的相同或相关的列。

以下是一个示例，说明如何使用 LEFT JOIN：

假设我们有两个表：customers 和 orders，它们具有以下结构：

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

我们可以使用 LEFT JOIN 来检索所有客户及其相关的订单。以下是示例查询：

SELECT customers.customer_name, orders.order_date
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

这个查询将返回所有客户的名称以及他们的订单日期。如果某个客户没有订单，对应的订单日期将为 NULL。 如果客户有多个订单，LEFT JOIN 仍然会返回所有客户的记录，并将与之相关的多个订单一起返回。每个客户的多个订单将作为多行结果出现，保留了与客户相关的所有订单信息。

例如，如果一个客户有多个订单，查询结果可能如下所示：

customer_name | order_date
-------------------------
Customer A    | 2022-01-15
Customer A    | 2022-02-20
Customer B    | 2022-03-10
Customer C    | NULL

在这个示例中，客户 A 有两个订单，所以他出现在两行结果中。客户 B 有一个订单，客户 C 没有订单，因此它出现在结果中并且订单日期为 NULL。

LEFT JOIN 的作用是保留主表（左表）中的所有记录，即使它们没有匹配的附表（右表）记录。如果有多个匹配的附表记录，主表记录将出现多次，每次匹配都有一个新的行。这就是为什么在结果中可以看到多行具有相同客户名称但不同订单日期的原因。

对于group by，我希望能做到理解

1.他的上下游分别是什么，数据从哪里得到作用在哪里 2.他在一个SQL中的执行顺序

看完下面应该要清楚的

当使用 GROUP BY 子句时，通常会对数据按照某个列进行分组，并对每个分组应用聚合函数以计算汇总信息。以下是一个简单的示例，演示如何使用 GROUP BY：

假设我们有一个名为 "sales" 的表，其中包含每位销售员的销售数据，如下所示：

+----+-----------+--------+
| ID | Salesman  | Amount |
+----+-----------+--------+
| 1  | John      | 100    |
| 2  | Mary      | 150    |
| 3  | John      | 200    |
| 4  | Mary      | 75     |
| 5  | John      | 300    |
+----+-----------+--------+

现在，假设我们想计算每位销售员的总销售金额。我们可以使用 GROUP BY 子句来执行此操作：

SELECT Salesman, SUM(Amount) AS TotalSales
FROM sales
GROUP BY Salesman;

这个查询将按 "Salesman" 列分组，计算每位销售员的总销售金额。结果将如下所示：

+-----------+------------+
| Salesman  | TotalSales |
+-----------+------------+
| John      | 600        |
| Mary      | 225        |
+-----------+------------+

这是查询执行的步骤和解释：

  1. FROM 子句：我们从 "sales" 表中检索销售数据。

  2. GROUP BY 子句：我们按 "Salesman" 列分组。这将创建两个分组，一个是 John 的销售数据，另一个是 Mary 的销售数据。

  3. SELECT 子句：我们选择 "Salesman" 列和 SUM(Amount)，即每个分组的总销售金额。

  4. SUM(Amount)：对每个分组中的 "Amount" 列应用 SUM 聚合函数，计算每个销售员的总销售金额。

最终的查询结果显示了每位销售员的名字和总销售金额。

这个示例说明了 GROUP BY 子句的用途，用于将数据分组并计算分组的汇总信息。

-- 创建 "ad" 表

CREATE TABLE ad (

id INT PRIMARY KEY,

title VARCHAR(255)

);
-- 创建 "order" 表

CREATE TABLE ord (

id INT PRIMARY KEY,

ad_id INT,

cost DECIMAL(10, 2)

);

一个广告表，一个订单表，订单表ad_id关联广告表，返回订单数据量前10的广告的id title 总费用 订单数量，按照降序返回

1.首先肯定要对这两个表联表查询，看看一个广告有多少订单 ad left join ord on ad.id = ord.ad_id，这个返回多条记录，每条记录包含广告以及他的订单，广告对应多条订单就有多条记录

后记： 后来又试着写了一次， 1这里写成了ord left join ad on ad.id = ord.ad_id,左右表颠倒了，其实这样是错的，因为left join 是按照左表返回，如果ord 里面没有一些广告订单的记录，那这个广告就被漏掉了，所以应该用ad 做左表，这样确保所有的广告都会在1之后被返回，如果没有订单，ord对应的就是空，如果对应多个订单就有多个记录，这里的知识点其实就是 left join 的真实含义要搞清楚

如果搞清楚了 a left join b 和 b left join 不一样，下面可以不用看，举一个简单清晰的例子

通过一个简单的示例来展示两个 LEFT JOIN 在同一查询中的不同结果。

假设我们有两个表：表 A 和表 B，它们的数据如下：

表 A：

| id | name  |
|----|-------|
| 1  | Alice |
| 2  | Bob   |

表 B：

| id | age |
|----|-----|
| 1  | 25  |
| 3  | 30  |

现在，我们来分别执行两个 LEFT JOIN 操作：

1. a LEFT JOIN b：

SELECT * 
FROM A
LEFT JOIN B ON A.id = B.id;


结果将会是：

| A.id | A.name | B.id | B.age |
|------|--------|------|-------|
| 1    | Alice  | 1    | 25    |
| 2    | Bob    | NULL | NULL  |

2. b LEFT JOIN a：

SELECT * 
FROM B
LEFT JOIN A ON B.id = A.id;

结果将会是：

| B.id | B.age | A.id | A.name |
|------|-------|------|--------|
| 1    | 25    | 1    | Alice  |
| 3    | 30    | NULL | NULL   |

可以看到，通过不同的 LEFT JOIN 顺序，返回的结果是不同的。在第一个查询中，a LEFT JOIN b 返回了表 A 中的所有行，并且与表 B 进行了匹配；而在第二个查询中，b LEFT JOIN a 返回了表 B 中的所有行，并且与表 A 进行了匹配。

希望这个例子能够清楚地展示两个 LEFT JOIN 在同一查询中可能产生不同结果的情况。

2.对记录按照广告id分组处理，分成多个组，每个组对应一个广告 ad left join ord on ad.id = ord.ad_id group by ad.id

3.针对上面多个分组进行聚合计算，select ad.id, ad.title,sum(ord.cost)ad total_cost,count(ord.id) as total_cnt from ad left join ord on ad.id = ord.ad_id group by ad.id,这个时候返回的是所有广告的id,title,总费用，广告数量

4.对上面多个聚合记录按照数量排序，返回前10个 select ad.id, ad.title,sum(ord.cost)ad total_cost,count(ord.id) as total_cnt from ad left join ord on ad.id = ord.ad_id group by ad.id order by total_cnt desc limit 10

以上就是分析过程，要对一个SQL能够解剖出来这几个部分 select ad.id, ad.title,sum(ord.cost)ad total_cost,count(ord.id) as total_cnt from ad left join ord on ad.id = ord.ad_id group by ad.id order by total_cnt desc limit 10

HAVING 子句通常与 GROUP BY 子句一起使用，用于筛选分组后的结果。它允许你基于聚合函数的结果进行过滤。以下是一个示例，演示如何使用 HAVING 子句：

假设我们有一个名为 "orders" 的表，其中包含客户的订单数据，以及一个名为 "customers" 的表，包含客户信息。我们想找到每个客户的订单总金额，并筛选出订单总金额大于 100 的客户。

SELECT
    C.customer_name,
    SUM(O.order_amount) AS total_amount
FROM
    customers C
JOIN
    orders O ON C.customer_id = O.customer_id
GROUP BY
    C.customer_name
HAVING
    total_amount > 100;

这个查询执行以下操作：

  1. 使用 JOIN 将 "customers" 表和 "orders" 表联接，以获取客户名称和订单金额信息。

  2. 使用 GROUP BY 子句按客户名称分组，以计算每个客户的订单总金额。

  3. 使用 SUM(O.order_amount) 计算每个客户的订单总金额。

  4. 使用 HAVING 子句筛选出订单总金额大于 100 的客户。

最终的结果将包括客户名称和相应客户的订单总金额，但只包括订单总金额大于 100 的客户。

通常，HAVING 子句应该出现在 GROUP BY 子句之后，而 ORDER BY 子句应该出现在 HAVING 子句之后。这是一般的 SQL 查询的执行顺序，按照以下顺序：

  1. SELECT 子句：选择要包括在结果中的列。

  2. FROM 子句：指定从哪个表或表中检索数据。

  3. WHERE 子句：用于过滤数据，通常在 FROM 子句之后，但在 GROUP BY 子句之前。

  4. GROUP BY 子句：分组数据，以便进行聚合计算。

  5. HAVING 子句：筛选分组后的结果，通常在 GROUP BY 子句之后，但在 ORDER BY 子句之前。

  6. ORDER BY 子句：排序结果集。

所以，一般来说，HAVING 子句应该出现在 GROUP BY 子句之后，而 ORDER BY 子句应该出现在 HAVING 子句之后。这是 SQL 查询的常规执行顺序。

SELECT
    customer_name,
    SUM(order_amount) AS total_amount
FROM
    orders
GROUP BY
    customer_name
HAVING
    total_amount > 100
ORDER BY
    total_amount DESC;

事务

脏读、幻读和不可重复读是数据库事务并发时可能遇到的问题，它们分别指以下情况：

  1. 脏读（Dirty Read）：

    • 定义： 一个事务读取到了另一个事务未提交的数据。
    • 示例： 假设事务 A 正在修改某一行数据，而事务 B 在此时读取了事务 A 所做的未提交修改。
    • 解决方法： 提高隔离级别，使用 READ COMMITTED 或更高的隔离级别，使得一个事务只能读取到已经提交的数据。

  2. 不可重复读（Non-repeatable Read）：

    • 定义： 在一个事务内，同一条记录的读取结果在事务中间变化。
    • 示例： 事务 A 读取了一行数据，然后事务 B 修改了该行数据，接着事务 A 再次读取相同的行，但结果不同。
    • 解决方法： 使用 REPEATABLE READ 或更高的隔离级别，确保一个事务中多次读取同一数据的结果始终相同。

  3. 幻读（Phantom Read）：

    • 定义： 在一个事务内，同一查询条件下读取结果集的记录数不一致。
    • 示例： 事务 A 读取了符合某一条件的一组数据，然后事务 B 插入了符合相同条件的新数据，接着事务 A 再次读取相同的条件，结果集不同。
    • 解决方法： 使用 SERIALIZABLE 隔离级别，确保一个事务中多次查询相同条件的结果集始终相同。

解决方法主要是通过提高事务的隔离级别来避免这些问题。隔离级别越高，越能避免并发引起的问题，但也可能会降低系统的并发性能。在实际应用中，需要根据具体情况选择适当的隔离级别。

事务的4大特性，以及如何保证的 www.cnblogs.com/dawabigbaby…

mvcc

多版本并发控制（MVCC）  在一定程度上实现了读写并发，它只在 可重复读（REPEATABLE READ）  和 提交读（READ COMMITTED）  两个隔离级别下工作。其他两个隔离级别都和 MVCC 不兼容，因为 未提交读（READ UNCOMMITTED） ，总是读取最新的数据行，而不是符合当前事务版本的数据行。而 可串行化（SERIALIZABLE）  则会对所有读取的行都加锁。

可重复读隔离级别相比于串行化隔离级别会有幻读的问题，这个问题再可重复读的隔离级别解决不了

为什么选择可重复读作为默认隔离级别

在读提交的隔离级别下开启两个会话：按上面的顺序执行命令。最后得到的数据库中有一条记录。

image.png

在mysql5.1以前mysql的逻辑操作日志binlog默认的是statement模式，用于恢复和复制。主从复制的binlog的采用的事statement方式。主库就是将每次数据库的sql修改(增删改)在提交前以二进制编码的形式保存到日志文件中。从库定时从主库的日志文件复制到本地日志，从库根据本地日志(继中日志)的变化执行sql语句。

　　根据上面的情况，当命令提交前，才会向日志文件中写入。所以日志文件的sql语句顺序是先增加后删除，所以在从库中数据库是没有数据的。而主库中有数据，这就造成了主从不一致的问题。

只有将修改的语句串行化才能解决这个问题。将隔离级别提升为可重复读就能将写入binlog的语句串行化。从库是根据binlog日志执行了的。binlog保证了与主库相同的执行顺序，那么也就保证了主从的一致性。

当mysql默认隔离级别为可重复读时

开启两个会话，左边为session1，右边为session2，按数字所示顺序执行。

　　隔离级别设为可重复读(Repeatable Read),在该隔离级别下引入间隙锁。当Session 1执行delete语句时，会锁住间隙。那么，Ssession 2执行插入语句就会阻塞住，无法继续执行。只有到session1 提交(commit)了之后。才能执行。实现了写入binlog的语句串行化。解决了主从不一致的问题。

总结

MySQL使用可重复读来作为默认隔离级别的主要原因是语句级的Binlog。可重复读能提供SQL语句的写可串行化，保证了主从一致。

可重复读可以避免幻读吗

MySQL InnoDB 引擎的可重复读隔离级别（默认隔离级），根据不同的查询方式，分别提出了避免幻读的方案：

  • 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读。
  • 针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读。

我举例了两个发生幻读场景的例子。

第一个例子：对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。

第二个例子：对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。

innodb为什么选择B+ Tree而不是跳表，Redis为什么选择跳表而不是B+ Tree cloud.tencent.com/developer/a…

mvcc

image.png

可重复读和读已提交都会用到mvcc但是其中的细节不太一样，可重复读是在事务开始的时候创建一个readview ，后面不会在更新里面的信息，读已提交在实务中每次select都会去更新readview，里面的m_ids会更新，导致一些已经提交的事务结果在该事务中可见

image.png

各种锁

image.png

image.png 数据备份的时候设置全局锁 flush tables with read lock

image.png

image.png 表锁

image.png

image.png

表级锁的使用场景 image.png 什么命令会使用表锁

image.png 表锁的缺点

image.png

行锁 image.png

MySQL 的行级锁实际上是在索引级别上实现的。如果没有使用索引或使用了全表扫描，可能会升级为表级锁。因此，在设计表结构时，合理使用索引可以提高并发性，减少锁冲突的可能性。

行锁在事务中有效，在 MySQL 中，并不是所有的操作都默认开启事务。MySQL 遵循一个 "autocommit" 模式的原则。当 "autocommit" 处于打开状态时，每个 SQL 语句都会自动被视为一个事务，并在执行后立即被提交。

默认情况下，MySQL 是开启 "autocommit" 的。这意味着如果你执行一个单独的 SQL 语句（例如 SELECT、INSERT、UPDATE、DELETE），它将自动成为一个独立的事务，执行后立即被提交。这种模式对于简单的操作非常方便，但并不总是符合复杂事务的需求。

如果你希望显式地开启、提交或回滚事务，可以使用 BEGIN、COMMIT 和 ROLLBACK 等 SQL 命令。例如：

BEGIN;  -- 开启事务

-- 执行一系列 SQL 操作

COMMIT; -- 提交事务

通过显式地使用事务控制语句，你可以更灵活地管理事务的边界和一致性

行锁的使用场景 image.png

会产生行锁的命令

image.png

X就是排它锁，S是共享锁

行锁的风险 image.png

乐观锁

image.png

乐观锁使用场景 image.png

乐观锁的一个示例：先查出版本是0，更新的时候where条件中加入版本的条件，如果不是之前的版本就不更新了，更新的时候还要把版本号➕1

image.png

乐观锁的缺点

关于2补充一些，就是开始乐观的以为不会冲突，所以所有的线程都会去操作，然后提交发现冲突，还要所有的都回滚，这样就会浪费很多的资源，可以从一开始就冲突的 image.png

悲观锁

image.png

悲观锁的使用场景

image.png

悲观锁命令实现

image.png

悲观锁的缺点

image.png

意向锁

image.png

意向锁为什么是表级锁，他的作用是什么，这个文章写的很好 www.51cto.com/article/743…

juejin.cn/post/684490…

image.png

image.png

我对上面截图的理解：就比如下面这种例子，如果只针对一行加了拍他锁（底层同时加了意向排他锁），那后面如果来一个表级别写锁，是会互斥的，那是如果来了一个不是ID=6的行级别拍他锁，意向锁是不管的，不互斥，（甚至说会加一个行拍他锁和一个意向排他锁，然后和前面的意向锁之间又不互斥，所以是OK的），所以说意向锁跟其他的表级别的排他/共享锁互斥，和行级别不互斥

文章中截图，加一个锁之后会产生两把锁，数据库会自动加上意向锁，不需要人员写代码加入

image.png 表加共享锁

lock tables a read

表加上排他锁

lock tables b write

image.png

image.png 间隙锁使用场景

image.png

间隙锁示例

image.png

间隙锁缺点

image.png

临界锁

image.png 一个示例

image.png

image.png 下面会锁住，因为上面的事务货去了，24到32的临界锁

image.png

avatar
文章
阅读
粉丝