SQL错题记录

牛客 SQL104 返回产品名称和每一项产品的总订单数

SELECT prod_name,COUNT(order_num) AS orders FROM 
Products A LEFT JOIN OrderItems B ON A.prod_id = B.prod_id
GROUP BY A.prod_id
ORDER BY prod_name ASC;

报错因为GROUP BY 后面跟的必须是select范围里的

基础

SQL的分类

1、DDL(Data Definition Language) 数据定义语言，用来操作数据库、表、列等；常用语句：CREATE、 ALTER、DROP

2、DML(Data Manipulation Language) 数据操作语言，用来操作数据库中表里的数据；常用语句：INSERT、 UPDATE、 DELETE 、SELECT

3、DCL(Data Control Language) 数据控制语言，用来操作访问权限和安全级别；常用语句：GRANT、DENY

数据库的三范式是什么？

1.第一范式(1NF)：属性不可再分

　　表中的每个字段都应该是原子性的，即不可再分解的基本数据类型，不能有多值或复杂类型的字段。

2.第二范式(2NF)：消除部分函数依赖

　　在满足1NF的基础上，表中的每个非主键字段都应该完全依赖于主键，而不是依赖于主键的一部分。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。

3.第三范式(3NF)：消除传递函数依赖

　　在2NF的基础上，消除非主键字段之间的传递依赖，即不允许一个非主键字段依赖于另一个非主键字段。

CHAR和VARCHAR区别：

两者不同的是：VARCHAR存储可变长度的字符串。

当数据为CHAR(M)类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节；而VARCHAR(M)所对应的数据所占用的字节数为实际长度加1

DROP TRUNCATE和DETELE的区别

用法不同： DROP 删除整个表 DELETE 删除部分记录 TRUNCATE 删除表中所有数据

1、DELETE语句后可跟WHERE子句，可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录；但是，TRUNCATE语句只能用于删除表中的所有记录。

2、使用TRUNCATE语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用DELETE语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1

3、DELETE语句是DML语句，DROP TRUNCATE语句通常被认为是DDL语句

count(*)，count(1)，count(列名)

都是用来统计某个字段的记录数

执行效果上：

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略为NULL的值。
count(1)忽略所有列，用1代表代码行，1表示一个固定值,也可以用count(2)、count(3)代替，在统计结果的时候，不会忽略为NULL的值。
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是指空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。执行效率上：

列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
如果有主键，则count(主键)的执行效率是最优的
如果表只有一个字段，则count(*)最优。

各种连接的区别

INNER JOIN：只有当两个表都存在满足条件的记录时才会返回行。
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行

JOIN默认为INNER JOIN

MySQL执行查询的过程

连接。客户端通过 TCP 连接发送连接请求到 MySQL 连接器，连接器会对该请求进行权限验证及连接资源分配
查缓存。（此时不会对SQL语句进行解析，因此要求查询语句的字符完全相同）
分析。没有命中缓存，则交给分析器进行语法和词法的分析，通俗来说就是解析SQL语句的目的和SQL语句语法是否正确
优化。优化器会按索引等生成优化计划。
执行。执行语句，然后从存储引擎返回数据。

执行顺序

查询语句的书写顺序和执行顺序 select ===> from ===> where ===> group by ===> having ===> order by ===> limit 查询语句的执行顺序 from ===> where ===> group by ===> having ===> select ===> order by ===> limit

索引（Index）

为什么MySQL要用B+树作为索引？

hash

hash可以通过key快速定位value，适合等值查询（复杂度O(1)），但由于其无顺序性，不适合范围查询和顺序查询。

二分查找树（BST）

二分查找树（BST）的查询性能依赖于它的平衡程度，退化成链表时，查询的时间复杂度从O(logN)变为O(N)

自平衡二叉树（AVL）和红黑树

对于树形结构，磁盘OI的次数等于树的高度。

AVL和红黑树，都会随着插入的元素增多，而导致树的高度变高，这就意味着磁盘 I/O 操作次数多，会影响整体数据查询的效率。

B树/B-树

B树即多路平衡查找树，B是balance的意思。

B-树的非叶子节点和叶子都存储了索引和数据，B+树只有叶子节点存储索引和数据，非叶节点只存储索引。

1、磁盘I/O性能来看：因此B+树的非叶节点可以存储更多的索引，I/O次数更低。

2、区间查询来看：B+树的数据都存在叶子节点中，进行区间查询时只要扫一遍叶子节点即可。而B-树则需要中序遍历。

B+树

微信图片_20230605145036.jpg

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。

每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都指向下一个叶子节点，形成一个链表。

索引的类型

按数据结构：B+ tree索引/hash索引/RTree 索引/全文索引

按物理存储：聚簇索引/非聚簇索引

按字段特性：主键索引/唯一索引/普通索引/前缀索引

按字段个数：单列索引/联合索引

聚簇索引/非聚簇索引

聚簇索引

索引结构和数据一起存放的索引。InnoDB 中，索引B+ Tree的叶子节点存储了整行数据，聚簇索引也就是主键索引。

优点：

查询速度快：定位到索引即定位到了数据，比非聚簇索引少了一次读数据的IO操作。

缺点：

依赖于有序的数据：索引数据无序时，在插入时需要排序，对于字符串或UUID等索引数据，插入或查找的速度会慢。

更新代价大：索引列的数据修改时，对应的索引也要修改，而且聚簇索引的叶子节点还存放着数据，修改代价大。

非聚簇索引

索引结构和数据分开存放的索引。InnoDB 中，索引B+ Tree的叶子节点存储的是索引和主键值，非聚簇索引也就是二级索引(辅助索引)。

优点：

更新代价较聚簇索引小。

缺点：

同样依赖有序数据。

需要二次查询（回表）：定位到主键后还需要通过主键进行二次查询。

联合索引

即组合索引/复合索引。使用表中的多个字段创建索引。

最左前缀匹配原则

最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、<）才会停止匹配。

（对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。）

所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

索引优化

前缀索引

前缀索引就是使用某个字段中字符串的前几个字符建立索引，目的是减小索引字段的大小，优化查询速度。

前缀索引的局限性：order by时不能使用；无法覆盖索引。

覆盖索引

一个索引覆盖了所有要查询的字段，即为覆盖索引。覆盖索引时，不需要回表查询。

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

索引下推（没懂）

索引下推是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

如何创建索引

在执行CREATE TABLE时创建索引。

CREATE TABLE user_index2 (
    id INT auto_increment PRIMARY KEY,
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
);

使用ALTER TABLE命令去增加索引。

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

使用CREATE INDEX命令创建。

CREATE INDEX index_name ON table_name (column_list);

建立索引的建议/原则

选择合适的字段创建索引

不为 NULL 的字段/被频繁查询的字段/被作为WHERE条件查询的字段/频繁需要排序的字段（索引已经排序）/被经常频繁用于连接的字段

被频繁更新的字段应该慎重建立索引
主键索引最好是自增的

自增的索引，新增时直接追加插入一条新记录，都是追加操作，不需要重新移动数据。因此可以减少页分裂（把一个数据页的数据复制到另一个页）和移动的频率。

尽可能的考虑建立联合索引而不是单列索引

每个表的索引控制在5个以内。每一个索引对应一个B+树，索引也会占用空间，且修改时会耗费时间。

避免冗余索引

存在索引（a,b）时，索引（a）就是冗余索引。

字符串类型的字段使用前缀索引代替普通索引
删除长时间不使用的索引
避免索引失效

索引失效的情况

1、使用了左模糊或者左右模糊的LIKE查询（%开头）

因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。

2、对索引进行计算、函数、类型转换等操作

因为索引保存的是索引字段的原始值，而不是操作后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

3、联合索引非最左匹配

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

4、WHERE 子句中的 OR

OR含义就是两个只要满足一个即可，只要OR有条件列不是索引列，就会进行全表扫描。

可以改用union。

explain 关键字（待补。。。）

使用 EXPLAIN 命令来分析 SQL 的执行计划，这样就知道语句是否命中索引了。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

事务（Transaction）

事务的四个特性(ACID)

原子性（Atomicity） ：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据空间是独立的；
持久性（Durabilily）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

InnoDB如何保证四个特性

持久性是通过 redo log （重做日志）来保证的；
原子性是通过 undo log（回滚日志）来保证的；
隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

并发事务会造成的问题

脏读（Dirty read）：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。
不可重复读（Unrepeatable read）：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
幻读（Phantom read）：事务A多次按某一查询条件查询记录数量，事务B插入或删除了一些行，导致事务A查询到的记录数量前后不一致。不可重复读侧重于修改某一数据，幻读侧重于新增或删除（多了或少了行），脏读是一个事务回滚影响另外一个事务。

事务的隔离级别

READ-UNCOMMITTED（读不提交）：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED（读提交）：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ（可重复读）：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE（串行化）：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL 默认采用的 REPEATABLE_READ 隔离级别。

MySQL如何实现事务的隔离级别

MySQL事务隔离机制的实现基于锁机制和并发调度（MVCC）。

读不提交 RU：

写操作加排它锁，读操作不加任何锁，会导致脏读；
注意，并不是说写操作加了排他锁，读操作就没办法进行了，因为读操作并不需要加锁，所以也就不会被排它锁影响。

读提交 RC：

写操作（当前读）加排它锁，读操作（快照读）通过MVCC避免脏读，每执行一条语句就重新生成最新的read view，这会导致不可重复读问题。

可重复读 RR：

写操作（当前读）加排它锁，读操作（快照读）通过MVCC避免不可重复读，在事务启动的时候就生成read view，整个事务结束都一直使用这个read view；
快照读不加gap锁，所以其他事务是可以继续插入的，只不过在读的时候进行了屏蔽或者说是过滤。可以说使用MVCC解决了快照读下的幻读问题；
当前读下，通过加next-key lock来避免幻读。next-key lock是行锁和间隙（gap）锁的合并，间隙锁会锁定一个范围，但是不包括记录本身。

串行化 Serializable：

读写都加锁，完全串行化。

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同：

「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

MySQL解决幻读

但是InnoDB 实现的 REPEATABLE-READ 隔离级别很大程度上解决了幻读情况，主要有下面两种情况：

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

Read View/MVCC的执行原理

MVCC（多版本并发控制）通过ReadView的四个字段以及数据记录里的两个隐藏字段，来控制并发事务对某记录的访问。

Read View中的四个字段

自己理解：ReadView控制当前事务读取的字段的修改都是在当前事务启动之前提交的参数说明：

1.creator_trx_id：当前事务id

2.m_ids：所有活跃事务（已启动但未提交）的事务id

3.min_trx_id：m_ids里最小的事务id值

4.max_trx_id：最大事务id

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
- 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
- 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

数据行的两个隐藏列

trx_id：记录改动该记录的事务id
roll_pointer：指针指向undo日志，内容是修改前旧版本的记录。

锁

锁的分类

1、按锁的粒度（加锁的范围）分类

在关系型数据库中，可以按照锁的粒度把数据库锁分为表级锁、行级锁。

MyISAM采用表级锁，InnoDB支持行级锁和表级锁，默认为行级锁。

表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。

行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

表级锁

意向锁
- 意向共享锁（IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
- 意向排他锁（IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁在添加行锁之前由数据引擎自己添加，用户不能手动加。

再加表级S或X锁时，直接获取该表的意向锁，看是否兼容，而不用一行一行遍历是否存在行锁来判断冲突。

意向锁之间是互相兼容的。

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

行级锁

Record Lock，记录锁，也就是仅仅把一条记录锁上；
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

2、按锁的类别分类

从锁的类别上来讲，有共享锁和排他锁。不论是表级锁还是行级锁，都存在共享锁和排他锁。

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

乐观锁和悲观锁

乐观锁和悲观锁都是控制并发事务的手段，目的是实现事务的隔离性。

乐观锁：

假定不会发生并发冲突，拿到数据的时候不上锁，只在提交操作时判断数据是否被其他事务修改，若无修改则执行，否则不执行。

实现方式：自己手动实现，一般是记录数据版本，通过版本号或时间戳。

适用场景：乐观锁适用于少写多读场景，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

实现举例（可以不讲）：表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。

悲观锁：

假定会发生并发冲突，操作数据时上锁，完成时才释放锁。

实现方式：数据库的锁机制

适用场景：悲观锁适用于多写场景（经常发生冲突）

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法：

死锁发生前：

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会；
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
如果业务处理不好可以用分布式事务锁或者使用乐观锁。

死锁发生时：

等待，直到超时
发起死锁检测，主动回滚一条事务，让其他事务继续执行

快照读(snapshot read)和当前读(current read)

MVCC中读操作分为快照读和当前读。

快照读

读取的是记录的可见版本（可能存在多个历史版本），不用加锁，通过MVCC来实现；

在读提交事务级别下，每执行一个语句都会生成最新Read View。读取该read view对应的版本。

在可重复读级别，事务开始时生成一次Read View并使用至事务结束。读取的是事务最开始时的ReadView对应的版本。

简单的select操作属于快照读

select * from table where ?

当前读

读取的是记录的最新版本，会给行记录加锁，事务结束后释放。

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

日志

MySQL的事务日志

undo log（回滚日志）

是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。

回滚：在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。

MVCC:通过 ReadView + undo log 实现 MVCC，进行快照读时，如果该记录不满足当前ReadView的可见性，就顺着行记录的回滚指针找到undo log版本链中可见的记录。

redo log（重做日志）

是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；

数据库在事务执行过程中，就把修改的记录写入redo log，存在redo log buffer中，在后续的系统空闲时间点再存入磁盘中的redo log file。这种先写日志，再写磁盘的技术在MySQL中叫做WAL（Write-Ahead Logging）技术。

为了防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 MySQL 服务的时候，会根据 redo log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

binlog （归档日志）

是 Server 层生成的日志，主要用于数据备份和主从复制；

MySQL学习