1、事务的特性ACID
原子性(Atomicity)
事务被视为不可分割的最小单元,事务的所有操作要么全部提交成功,要么全部失败回滚。
回滚可以
用回滚日志(Undo Log)
来实现,回滚日志记录着事务所执行的修改操作,在回滚时反向执行这些修改操作即可。
一致性(Consistency)
数据库在事务执行前后都保持一致性状态。在一致性状态下,所有事务对同一个数据的读取结果都是相同的。
隔离性(Isolation)
一个事务所做的修改在最终提交以前,对其它事务是不可见的。
持久性(Durability)
一旦事务提交,则其所做的修改将会永远保存到数据库中。即使系统发生崩溃,事务执行的结果也不能丢失。
2、与事务的四种隔离级别相关的问题
四种数据并发问题
脏写( Dirty Write )
丢失修改指一个事务的更新操作被另外一个事务的更新操作替换。 一般在现实生活中常会遇到,例如:T1 和 T2 两个事务都对一个数据进行修改,T1 先修改并提交生效,T2 随后修改,T2 的修改覆盖了 T1 的修改。
脏读( Dirty Read )
读脏数据指在不同的事务下,当前事务可以读到另外事务未提交的数据。 例如:T1 修改一个数据但未提交,T2 随后读取这个数据。如果 T1 撤销了这次修改,那么 T2 读取的数据是脏数据。
不可重复读( Non-Repeatable Read )
不可重复读指在一个事务内多次读取同一数据集合。 在这一事务还未结束前,另一事务也访问了该同一数据集合并做了修改,由于第二个事务的修改,第一次事务的两次读取的数据可能不一致。例如:T2 读取一个数据,T1 对该数据做了修改。如果 T2 再次读取这个数据,此时读取的结果和第一次读取的结果不同。
不可重复读和脏读的区别:是几次读
,脏读是一次读,不可重复读是两次读
幻读( Phantom )
幻读本质上也属于不可重复读的情况,T1 读取某个范围的数据,T2 在这个范围内插入新的数据,T1 再次读取这个范围的数据,此时读取的结果和和第一次读取的结果不同。
幻读和不可重复读的区别:是修改
还是插入
。
四种隔离级别
上面介绍了几种并发事务执行过程中可能遇到的一些问题,这些问题有轻重缓急之分,我们给这些问题按照严重性来排一下序:
脏写 > 脏读 > 不可重复读 > 幻读
读未提交
READ UNCOMMITTED :读未提交,在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。
不能避免脏读、不可重复读、幻读。
读已提交
READ COMMITTED :读已提交,它满足了隔离的简单定义:一个事务只能看见已经提交
事务所做的改变,换句话说,一个事务所做的修改在提交之前对其它事务是不可见的。 这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。
可以避免脏读,但不可重复读、幻读问题仍然存在。
可重复读
REPEATABLE READ :可重复读,事务A在读到一条数据之后,此时事务B对该数据进行了修改并提交,那么事务A再读该数据,读到的还是原来的内容。也就是保证在同一个事务中多次读取同一数据的结果是一样的。
可以避免脏读、不可重复读,但幻读问题仍然存在。 这是MySQL的默认隔离级别。
可串行化
SERIALIZABLE :可串行化,确保事务可以从一个表中读取相同的行。在这个事务持续期间,禁止其他事务对该表执行插入、更新和删除操作。强制事务串行执行,这样多个事务互不干扰,不会出现并发一致性问题。
所有的并发问题都可以避免,但性能十分低下。 能避免脏读、不可重复读和幻读。
并发问题的解决方案
怎么解决 脏读 、 不可重复读 、 幻读 这些问题呢?其实有两种可选的解决方案:
方案一:读操作利用多版本并发控制( MVCC ,下章讲解),写操作进行 加锁
。
方案二:读、写操作都采用 加锁
的方式。
对比:
采用 MVCC
方式的话, 读-写
操作彼此并不冲突,性能更高
。
采用 加锁
方式的话, 读-写
操作彼此需要 排队执行
,影响性能。
一般情况下我们当然愿意采用 MVCC
来解决 读-写
操作并发执行的问题,但是业务在某些特殊情况下,要求必须采用 加锁
的方式执行。下面就讲解下MySQL中不同类别的锁。
锁
封锁粒度
MySQL 中提供了两种封锁粒度:行级锁
以及表级锁
。
应该尽量只锁定需要修改的那部分数据,而不是所有的资源。锁定的数据量越少,发生锁争用的可能就越小,系统的并发程度就越高。
但是加锁需要消耗资源,锁的各种操作(包括获取锁、释放锁、以及检查锁状态)都会增加系统开销。因此封锁粒度越小,系统开销就越大。
在选择封锁粒度时,需要在锁开销和并发程度之间做一个权衡。
封锁类型
读写锁
互斥锁(Exclusive),简写为 X
锁,又称写锁
。
共享锁(Shared),简写为 S
锁,又称读锁
。
有以下两个规定:
(1)一个事务对数据对象 A 加了 X
锁,就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。
(2)一个事务对数据对象 A 加了 S
锁,可以对 A 进行读取操作,但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁,但是不能加 X 锁。
锁的兼容关系如下:
意向锁
使用意向锁
(Intention Locks)可以更容易地支持多粒度封锁。
在存在行级锁
和表级锁
的情况下,事务 T 想要对表 A 加 X
锁,就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁,那么就需要对表 A 的每一行都检测一次,这是非常耗时的。
意向锁在原来的 X/S
锁之上引入了 IX/IS
,IX/IS
都是表锁,用来表示一个事务想要
在表中的某个数据行上加 X
锁或 S
锁。有以下两个规定:
(1)一个事务在获得某个数据行对象的 S
锁之前,必须先获得表的 IS
锁或者更强的锁;
(2)一个事务在获得某个数据行对象的 X
锁之前,必须先获得表的 IX
锁。
通过引入意向锁
,事务 T 想要对表 A 加 X
锁,只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS
锁,如果加了就表示有其它事务正在使用这个表或者表中某一行的锁,因此事务 T 加 X
锁失败。
各种锁的兼容关系如下:
解释如下:
(1)任意 IS/IX
锁之间都是兼容的,因为它们只表示想要对表加锁,而不是真正加锁;
(2)这里兼容关系针对的是表级锁,而表级的 IX
锁和行级的 X
锁兼容,两个事务可以对两个数据行加 X
锁。(事务 T1 想要对数据行 R1 加 X
锁,事务 T2 想要对同一个表的数据行 R2 加 X
锁,两个事务都需要对该表加 IX
锁,但是 IX
锁是兼容的,并且 IX
锁与行级的 X
锁也是兼容的,因此两个事务都能加锁成功,对同一个表中的两个数据行做修改。)
三级封锁协议
一级封锁协议(解决脏写问题)
事务 T 要修改数据 A 时必须加 X
锁,直到 T 结束才释放锁。
可以解决脏写
问题,因为不能同时有两个事务对同一个数据进行修改,那么事务的修改就不会被覆盖。
二级封锁协议(解决脏读问题)
在一级的基础上,要求读取数据 A 时必须加 S
锁,读取完马上释放 S
锁。
可以解决读脏
数据问题,因为如果一个事务在对数据 A 进行修改,根据 1 级封锁协议,会加 X
锁,那么就不能再加 S
锁了,也就是不会读入数据。
三级封锁协议(解决不可重复读问题)
在二级的基础上,要求读取数据 A 时必须加 S
锁,直到事务结束了才能释放 S
锁。
可以解决不可重复读
的问题,因为读 A 时,其它事务不能对 A 加 X
锁,从而避免了在读的期间数据发生改变。
两段锁协议(可串行化)
加锁
和解锁
分为两个阶段进行。
可串行化调度
是指,通过并发控制,使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰,不会出现并发一致性问题。
事务遵循两段锁协议
是保证可串行化调度的充分条件
。例如以下操作满足两段锁协议,它是可串行化调度。
lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)
但不是必要条件,例如以下操作不满足两段锁协议,但它还是可串行化调度。
lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)
MySQL 隐式与显式锁定
MySQL 的 InnoDB 存储引擎采用两段锁协议
,会根据隔离级别在需要的时候自动加锁,并且所有的锁都是在同一时刻被释放,这被称为隐式锁定
。
InnoDB 也可以使用特定的语句进行显示锁定:
SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;
多版本并发控制
多版本并发控制(Multi-Version Concurrency Control, MVCC)是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式,用于实现读已提交
和可重复读
这两种隔离级别。而读未提交
隔离级别总是读取最新的数据行,要求很低,无需使用 MVCC。可串行化
隔离级别需要对所有读取的行都加锁,单纯使用 MVCC 无法实现。
基本思想
在封锁一节中提到,加锁能解决多个事务同时执行时出现的并发一致性问题。在实际场景中读操作往往多于写操作
,因此又引入了读写锁来避免不必要的加锁操作,例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的,而 MVCC 利用了多版本
的思想,写操作更新最新的版本快照,而读操作去读旧版本快照,没有互斥关系,这一点和 CopyOnWrite 类似
。
在 MVCC 中事务的修改操作(DELETE、INSERT、UPDATE)
会为数据行
新增一个版本快照
。
脏读
和不可重复读
最根本的原因是事务读取到其它事务未提交的修改
。在事务进行读取操作时,为了解决脏读和不可重复读问题,MVCC 规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照,这不算是脏读。
版本号
系统版本号 SYS_ID
:是一个递增的数字,每开始一个新的事务,系统版本号就会自动递增。
事务版本号 TRX_ID
:事务开始时的系统版本号。
Undo 日志
MVCC 的多版本指的是多个版本的快照,快照存储在 Undo
日志中,该日志通过回滚指针 ROLL_PTR
把一个数据行的所有快照连接起来。
例如在 MySQL 创建一个表 t,包含主键 id
和一个字段 x
。我们先插入一个数据行,然后对该数据行执行两次更新操作。
INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;
因为没有使用 START TRANSACTION
将上面的操作当成一个事务来执行,根据 MySQL 的 AUTOCOMMIT
机制,每个操作都会被当成一个事务来执行,所以上面的操作总共涉及到三个事务
。快照中除了记录事务版本号 TRX_ID
和操作之外,还记录了一个 bit
的 DEL
字段,用于标记是否被删除。
INSERT、UPDATE、DELETE
操作会创建一个日志,并将事务版本号 TRX_ID
写入。DELETE
可以看成是一个特殊的 UPDATE
,还会额外将 DEL
字段设置为 1
。
ReadView
MVCC
维护了一个 ReadView
结构,主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, ...}
,还有该列表的最小值 TRX_ID_MIN
和 TRX_ID_MAX
。
在进行 SELECT
操作时,根据数据行快照的 TRX_ID
与 TRX_ID_MIN
和 TRX_ID_MAX
之间的关系,从而判断数据行快照是否可以使用:
(1)TRX_ID < TRX_ID_MIN
,表示该数据行快照时在当前所有未提交事务之前进行更改的,因此可以使用。
(2)TRX_ID > TRX_ID_MAX
,表示该数据行快照是在事务启动之后被更改的,因此不可使用。
(3)TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX
,需要根据隔离级别再进行判断:
-
读已提交:如果
TRX_ID
在TRX_IDs
列表中,表示该数据行快照对应的事务还未提交,则该快照不可使用。否则表示已经提交,可以使用。 -
可重复读:都不可以使用。因为如果可以使用的话,那么其它事务也可以读到这个数据行快照并进行修改,那么当前事务再去读这个数据行得到的值就会发生改变,也就是出现了不可重复读问题。
在数据行快照不可使用的情况下,需要沿着 Undo Log
的回滚指针 ROLL_PTR
找到下一个快照,再进行上面的判断。
快照读与当前读
快照读
MVCC 的 SELECT
操作是快照中的数据,不需要进行加锁操作。
SELECT * FROM table ...;
当前读
MVCC 其它会对数据库进行修改的操作(INSERT、UPDATE、DELETE)
需要进行加锁操作,从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁,而只是避免了 SELECT
的加锁操作。
INSERT;
UPDATE;
DELETE;
在进行 SELECT 操作时,可以强制指定进行加锁操作。以下第一个语句需要加 S
锁,第二个需要加 X
锁。
SELECT * FROM table WHERE ? lock in share mode;
SELECT * FROM table WHERE ? for update;
Next-Key Locks
Next-Key Locks
是 MySQL 的 InnoDB 存储引擎的一种锁实现。
MVCC 不能解决幻影读问题,Next-Key Locks
就是为了解决这个问题而存在的。在可重复读(REPEATABLE READ)隔离级别下,使用 MVCC + Next-Key Locks
可以解决幻读
问题。
Record Locks
锁定一个记录上的索引
,而不是记录本身。
如果表没有设置索引,InnoDB 会自动在主键上创建隐藏的聚簇索引,因此 Record Locks 依然可以使用。
Gap Locks
锁定索引之间的间隙,但是不包含索引本身。例如当一个事务执行以下语句,其它事务就不能在 t.c 中插入 15。
SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;
Next-Key Locks
它是 Record Locks
和 Gap Locks
的结合,不仅锁定一个记录上的索引,也锁定索引之间的间隙。 它锁定一个前开后闭区间,例如一个索引包含以下值:10, 11, 13, and 20,那么就需要锁定以下区间:
(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)
3、三范式区分
4、MyISAM与InnoDB的区别
事务: InnoDB 是事务型的,可以使用 Commit 和 Rollback 语句。
并发: MyISAM 只支持表级锁,而 InnoDB 还支持行级锁。
外键: InnoDB 支持外键。
备份: InnoDB 支持在线热备份。
崩溃恢复: MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多,而且恢复的速度也更慢。
其它特性: MyISAM 支持压缩表和空间数据索引。
5、B+Tree原理与MySQL索引
见:MySQL索引
6、窗口函数
假设我现在有这样一个数据表,它显示了某购物网站在每个城市每个区的销售额:
CREATE TABLE sales(
id INT PRIMARY KEY AUTO_INCREMENT,
city VARCHAR(15),
county VARCHAR(15),
sales_value DECIMAL
);INSERT INTO sales(city,county,sales_value)
VALUES
('北京','海淀',10.00),
('北京','朝阳',20.00),
('上海','黄埔',30.00),
('上海','长宁',10.00);
查询:
mysql> SELECT * FROM sales;
+----+------+--------+-------------+
| id | city | county | sales_value |
+----+------+--------+-------------+
| 1 | 北京 | 海淀 | 10 |
| 2 | 北京 | 朝阳 | 20 |
| 3 | 上海 | 黄埔 | 30 |
| 4 | 上海 | 长宁 | 10 |
+----+------+--------+-------------+
4 rows in set (0.00 sec)
需求:
现在计算这个网站在每个城市的销售总额、在全国的销售总额、每个区的销售额占所在城市销售额中的比率,以及占总销售额中的比率。
如果用分组和聚合函数,就需要分好几步来计算。
第一步,计算总销售金额,并存入临时表 a:
CREATE TEMPORARY TABLE a -- 创建临时表
SELECT SUM(sales_value) AS sales_value -- 计算总计金额
FROM sales;
查看一下临时表 a :
mysql> SELECT * FROM a;
+-------------+
| sales_value |
+-------------+
| 70 |
+-------------+
1 row in set (0.00 sec)
第二步,计算每个城市的销售总额并存入临时表 b:
CREATE TEMPORARY TABLE b -- 创建临时表
SELECT city, SUM(sales_value) AS sales_value -- 计算城市销售合计
FROM sales
mysql> SELECT * FROM b;
+------+-------------+
| city | sales_value |
+------+-------------+
| 北京 | 30 |
| 上海 | 40 |
+------+-------------+
2 rows in set (0.00 sec)
第三步,计算各区的销售占所在城市的总计金额的比例,和占全部销售总计金额的比例。我们可以通过下面的连接查询获得需要的结果:
mysql> SELECT s.city AS 城市,s.county AS 区,s.sales_value AS 区销售额,
-> b.sales_value AS 市销售额,s.sales_value/b.sales_value AS 市比率,
-> a.sales_value AS 总销售额,s.sales_value/a.sales_value AS 总比率
-> FROM sales s
-> JOIN b ON (s.city=b.city) -- 连接市统计结果临时表
-> JOIN a -- 连接总计金额临时表
-> ORDER BY s.city,s.county;
+------+------+----------+----------+--------+----------+--------+
| 城市 | 区 | 区销售额 | 市销售额 | 市比率 | 总销售额 | 总比率 |
+------+------+----------+----------+--------+----------+--------+
| 上海 | 长宁 | 10 | 40 | 0.2500 | 70 | 0.1429 |
| 上海 | 黄埔 | 30 | 40 | 0.7500 | 70 | 0.4286 |
| 北京 | 朝阳 | 20 | 30 | 0.6667 | 70 | 0.2857 |
| 北京 | 海淀 | 10 | 30 | 0.3333 | 70 | 0.1429 |
+------+------+----------+----------+--------+----------+--------+
4 rows in set (0.00 sec)
结果显示:市销售金额、市销售占比、总销售金额、总销售占比都计算出来了。
同样的查询,如果用窗口函数,就简单多了。我们可以用下面的代码来实现:
mysql> SELECT city AS 城市,county AS 区,sales_value AS 区销售额,
-> SUM(sales_value) OVER(PARTITION BY city) AS 市销售额, -- 计算市销售额
-> sales_value/SUM(sales_value) OVER(PARTITION BY city) AS 市比率,
-> SUM(sales_value) OVER() AS 总销售额, -- 计算总销售额
-> sales_value/SUM(sales_value) OVER() AS 总比率
-> FROM sales
-> ORDER BY city,county;
+------+------+----------+----------+--------+----------+--------+
| 城市 | 区 | 区销售额 | 市销售额 | 市比率 | 总销售额 | 总比率 |
+------+------+----------+----------+--------+----------+--------+
| 上海 | 长宁 | 10 | 40 | 0.2500 | 70 | 0.1429 |
| 上海 | 黄埔 | 30 | 40 | 0.7500 | 70 | 0.4286 |
| 北京 | 朝阳 | 20 | 30 | 0.6667 | 70 | 0.2857 |
| 北京 | 海淀 | 10 | 30 | 0.3333 | 70 | 0.1429 |
+------+------+----------+-----------+--------+----------+--------+
4 rows in set (0.00 sec)
结果显示,我们得到了与上面那种查询同样的结果。
使用窗口函数,只用了一步就完成了查询。而且,由于没有用到临时表,执行的效率也更高了。很显然,在这种需要用到分组统计的结果对每一条记录进行计算的场景下,使用窗口函数更好。
MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组,不同的是,分组操作会把分组的结果聚合成一条记录,而窗口函数是将结果置于每一条数据记录中。
窗口函数可以分为 静态窗口函数
和 动态窗口函数
。
(1)静态窗口函数的窗口大小是固定的,不会因为记录的不同而不同;
(2)动态窗口函数的窗口大小会随着记录的不同而变化。
窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数,如下表:
语法结构
窗口函数的语法结构是:
函数 OVER([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
或者是:
函数 OVER 窗口名 … WINDOW 窗口名 AS ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
window_function ( expr ) OVER ( PARTITION BY ... ORDER BY ... frame_clause
)
(1)OVER
关键字指定函数窗口的范围:
-
如果省略后面括号中的内容,则窗口会包含满足WHERE条件的所有记录,窗口函数会基于所有满足WHERE条件的记录进行计算。
-
如果OVER关键字后面的括号不为空,则可以使用如下语法设置窗口。
(2)窗口名: 为窗口设置一个别名,用来标识窗口。
(3)PARTITION BY子句: 指定窗口函数按照哪些字段进行分组。分组后,窗口函数可以在每个分组中分别执行。
(4)ORDER BY子句: 指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
(5)FRAME子句: 为分区中的某个子集定义规则,可以用来作为滑动窗口使用。
创建表:
CREATE TABLE goods(id INT PRIMARY KEY AUTO_INCREMENT,category_id INT,category VARCHAR(15),NAME VARCHAR(30),price DECIMAL(10,2),stock INT,upper_time DATETIME
);
添加数据:
INSERT INTO goods(category_id, category, NAME, price, stock, upper_time)
VALUES
(1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
(2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
(2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
(2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
(2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
(2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');
下面针对goods表中的数据来验证每个窗口函数的功能。
序号函数
ROW_NUMBER()函数
ROW_NUMBER()函数能够对数据中的序号进行顺序显示。
举例:查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息。
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id, category_id, category, NAME, price, stock
FROM goods;+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 3 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
| 5 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 |
| 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 |
| 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 |
| 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 |
+---------+----+-------------+---------------+------------+---------+-------+
12 rows in set (0.00 sec)
举例:查询 goods 数据表中每个商品分类下价格最高的3种商品信息。
SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category_id, category, NAME, price, stock FROM goods) t -- 子查询派生出的表需要给出别名,否则会报错
WHERE row_num <= 3;+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 3 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 |
| 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 |
| 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 |
+---------+----+-------------+---------------+------------+----------+-------+
6 rows in set (0.00 sec)
在名称为“女装/女士精品”的商品类别中,有两款商品的价格为89.90元,分别是卫衣和牛仔裤。两款商品的序号都应该为2,而不是一个为2,另一个为3。此时,可以使用RANK()函数
和DENSE_RANK()函数
解决。
RANK()函数
使用RANK()函数能够对序号进行并列排序,并且会跳过重复的序号,比如序号为1、1、3。
举例:使用RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id, category_id, category, NAME, price, stock
FROM goods;+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
| 5 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 |
| 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 |
| 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 |
| 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 |
| 4 | 7 | 2 | 户外运动 | 自行车 | 399.90 | 1000 |
| 4 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 |
| 6 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 |
+---------+----+-------------+---------------+------------+---------+-------+
12 rows in set (0.00 sec)
举例:使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息
SELECT * FROM (SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, category_id, category, NAME, price, stockFROM goods) t
WHERE category_id = 1 AND row_num <= 4;+---------+----+-------------+---------------+----------+--------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+----------+--------+-------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
+---------+----+-------------+---------------+----------+--------+-------+
4 rows in set (0.00 sec)
可以看到,使用RANK()函数得出的序号为1、2、2、4,相同价格的商品序号相同,后面的商品序号是不连续的,跳过了重复的序号。
DENSE_RANK()函数
DENSE_RANK()函数对序号进行并列排序,并且不会跳过重复的序号,比如序号为1、1、2。
举例:使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id, category_id, category, NAME, price, stock
FROM goods;+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 3 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
| 4 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 |
| 5 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 |
| 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 |
| 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 |
| 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 |
| 4 | 7 | 2 | 户外运动 | 自行车 | 399.90 | 1000 |
| 4 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 |
| 5 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 |
+---------+----+-------------+---------------+------------+---------+-------+
12 rows in set (0.00 sec)
举例:使用DENSE_RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息。
SELECT * FROM(SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, category_id, category, NAME, price, stockFROM goods) t
WHERE category_id = 1 AND row_num <= 3;
可以看到,使用DENSE_RANK()函数得出的行号为1、2、2、3,相同价格的商品序号相同,后面的商品序号是连续的,并且没有跳过重复的序号。
分布函数
PERCENT_RANK()函数
PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算。
(rank - 1) / (rows - 1)
其中,rank的值为使用RANK()函数产生的序号,rows的值为当前窗口的总记录数。
举例:计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值。
#写法一:
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;#写法二:
SELECT RANK() OVER w AS r,
PERCENT_RANK() OVER w AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price
DESC);+---+-----+----+-------------+---------------+----------+--------+-------+
| r | pr | id | category_id | category | NAME | price | stock |
+---+-----+----+-------------+---------------+----------+--------+-------+
| 1 | 0 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 0.2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 2 | 0.2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 4 | 0.6 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
| 5 | 0.8 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 1 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 |
+---+-----+----+-------------+---------------+----------+--------+-------+
6 rows in set (0.00 sec)
CUME_DIST()函数
CUME_DIST()函数主要用于查询小于或等于某个值的比例。
举例:查询goods数据表中小于或等于当前价格的比例。
SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,
id, category, NAME, price
FROM goods;
前后函数
LAG(expr,n)函数
LAG(expr,n)函数
返回当前行的前n行的expr的值。
举例:查询goods数据表中前一个商品价格与当前商品价格的差值。
SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
FROM (SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_priceFROM goodsWINDOW w AS (PARTITION BY category_id ORDER BY price)) t;+----+---------------+------------+---------+-----------+------------+
| id | category | NAME | price | pre_price | diff_price |
+----+---------------+------------+---------+-----------+------------+
| 5 | 女装/女士精品 | 百褶裙 | 29.90 | NULL | NULL |
| 1 | 女装/女士精品 | T恤 | 39.90 | 29.90 | 10.00 |
| 2 | 女装/女士精品 | 连衣裙 | 79.90 | 39.90 | 40.00 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 79.90 | 10.00 |
| 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 89.90 | 0.00 |
| 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 89.90 | 310.00 |
| 9 | 户外运动 | 登山杖 | 59.90 | NULL | NULL |
| 7 | 户外运动 | 自行车 | 399.90 | 59.90 | 340.00 |
| 10 | 户外运动 | 骑行装备 | 399.90 | 399.90 | 0.00 |
| 12 | 户外运动 | 滑板 | 499.90 | 399.90 | 100.00 |
| 11 | 户外运动 | 运动外套 | 799.90 | 499.90 | 300.00 |
| 8 | 户外运动 | 山地自行车 | 1399.90 | 799.90 | 600.00 |
+----+---------------+------------+---------+-----------+------------+
12 rows in set (0.00 sec)
LEAD(expr,n)函数
LEAD(expr,n)函数返回当前行的后n行的expr的值。
举例:查询goods数据表中后一个商品价格与当前商品价格的差值。
SELECT id, category, NAME, behind_price, price, behind_price - price AS diff_price
FROM(SELECT id, category, NAME, price, LEAD(price, 1) OVER w AS behind_priceFROM goodsWINDOW w AS (PARTITION BY category_id ORDER BY price)) t;
首尾函数
FIRST_VALUE(expr)函数
FIRST_VALUE(expr)函数返回第一个expr的值。
举例:按照价格排序,查询第1个商品的价格信息
SELECT id, category, NAME, price, stock, FIRST_VALUE(price) OVER w AS first_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
LAST_VALUE(expr)函数
LAST_VALUE(expr)函数返回最后一个expr的值。
举例:按照价格排序,查询最后一个商品的价格信息
SELECT id, category, NAME, price, stock, LAST_VALUE(price) OVER w AS last_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
其他函数
NTH_VALUE(expr,n)函数
NTH_VALUE(expr,n)函数返回第n个expr的值。
举例:查询goods数据表中排名第2和第3的价格信息。
SELECT id, category, NAME, price,NTH_VALUE(price, 2) OVER w AS second_price,
NTH_VALUE(price, 3) OVER w AS third_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
NTILE(n)函数
NTILE(n)函数将分区中的有序数据分为n个桶,记录桶编号。
举例:将goods表中的商品按照价格分为3组。
SELECT NTILE(3) OVER w AS nt,id, category, NAME, price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
总结
窗口函数的特点是可以分组,而且可以在分组内排序。另外,窗口函数不会因为分组而减少原表中的行数,这对我们在原表数据的基础上进行统计和排序非常有用。
练习题链接
参考链接:
mysql之窗口函数练习
7、数据删除truncate、delete与drop的区别
TRUNCATE TABLE语句:
(1)删除表中所有的数据
(2)释放表的存储空间
举例:
TRUNCATE TABLE detail_dept;
TRUNCATE语句不能回滚,而使用 DELETE 语句删除数据,可以回滚
对比:
DELETE FROM emp2;
#TRUNCATE TABLE emp2;SELECT * FROM emp2;ROLLBACK;SELECT * FROM emp2;
(1)truncate 和 delete 都可以清除数据表的所用数据,但 truncate 比 delete 的速度快,且使用的系统和事务日志资源少。
(2)truncate没有 where 的条件筛选,只能单独使用,delete 不仅可以单独而且还可以和 where 搭配,从而实现删除单条或多条数据。
(3)删除的实现原理不同。truncate 是通过释放存储表数据所使用的数据页来进行数据的删除,并且只在事务日志中记录页的释放。delete 语句每删除一行就在事务日志中为所删除的每行记录一项。
下面简单对比下三者的异同:
(1)truncate与drop是DDL语句,执行后无法回滚;delete是DML语句,可回滚。
(2)truncate只能作用于表;delete,drop可作用于表、视图等。
(3)truncate会清空表中的所有行,但表结构及其约束、索引等保持不变;drop会删除表的结构及其所依赖的约束、索引等。
(4)truncate会重置表的自增值;delete不会。
(5)truncate不会激活与表有关的删除触发器;delete可以。
(6)truncate后会使表和索引所占用的空间会恢复到初始大小;delete操作不会减少表或索引所占用的空间,drop语句将表所占用的空间全释放掉。
8、B树和B+树、MySQL聚簇索引和非聚簇索引
参考链接:
B树和B+树
https://blog.csdn.net/qq_33905217/article/details/121827393
聚簇索引和非聚簇索引
https://blog.csdn.net/u010786653/article/details/123579393
https://blog.csdn.net/qinxiaoqiang2011/article/details/126290979
9、SQL 函数:concat函数、concat_ws()函数、group_concat()函数
SQL 函数:concat函数、concat_ws()函数、group_concat()函数
SQL concat()函数
10、MySQL大表查询优化
MySQL大表查询优化