SQL必知必会学习笔记(一)

进击的学霸...大约 10 分钟数据库SQL学习

学极客时间的 SQL必知必会专栏,做记录

初步了解

我们可以把 SQL 语言按照功能划分成以下的 4 个部分:

  1. DDL,英文叫做 Data Definition Language,也就是数据定义语言,它用来定义我们的数据库对象,包括数据库、数据表和列。通过使用 DDL,我们可以创建,删除和修改数据库和表结构。
  2. DML,英文叫做 Data Manipulation Language,数据操作语言,我们用它操作和数据库相关的记录,比如增加、删除、修改数据表中的记录。
  3. DCL,英文叫做 Data Control Language,数据控制语言,我们用它来定义访问权限和安全级别。
  4. DQL,英文叫做 Data Query Language,数据查询语言,我们用它查询想要的记录,它是 SQL 语言的重中之重。在实际的业务中,我们绝大多数情况下都是在和查询打交道,因此学会编写正确且高效的查询语句,是学习的重点。

SQL 语言定义了我们的需求,而不同的 DBMS(数据库管理系统)则会按照指定的 SQL 帮我们提取想要的结果。采用ER图(Entity Relationship Diagram / 实体 - 关系图)来设计数据库,ER 图评审通过后,我们再用 SQL 语句或者可视化管理工具(如 Navicat)创建数据表。ER图是我们用来描述现实世界的概念模型,在这个模型中有 3 个要素:实体、属性、关系,实体就是我们要管理的对象,属性是标识每个实体的属性,关系则是对象之间的关系,实体之间可以是一对一、一对多或多对多的关系。

SQL 书写规范:

  1. 表名、表别名、字段名、字段别名等都小写;
  2. SQL 保留字、函数名、绑定变量等都大写。
-- 例子
SELECT name, hp_max FROM heros WHERE role_main = '战士'

底下留言中有在争执数据库系统大小写的规范,我们使用大小写的规范是为了使代码可读性提高,即使数据库系统本身不区分大小写,也不影响我们的书写规范,通常情况下,我们的「代码写出来都是给人读的,附带在机器上运行」。

MySQL在Windows下都不区分大小写。 Oracle中,SQL语句是不区分大小写,如果查询中有字符,是区分大小写的 比如 SELECT * FROM heros WHERE name = 'guanyu' 和 SELECT * FROM heros WHERE name = 'GUANYU' 在Oracle中会认为是不同的查询,而在MySQL中是相同的查询 同时,我们可以通过修改系统参数来进行配置,比如在MySQL可以通过参数lower_case_table_names来配置 数据库和数据表的大小写敏感性

了解 DBMS

比如 MySQL、SQL Server、PostgreSQL、DB2 和 MongoDB 等,这些都是我们比较熟知的 DBMS ,我们使用 SQL 和这些 DBMS 沟通进行数据的存取。

  • DBMS 的英文全称是 DataBase Management System,数据库管理系统,实际上它可以对多个数据库进行管理,所以你可以理解为 DBMS = 多个数据库(DB) + 管理程序。
  • DB 的英文是 DataBase,也就是数据库。数据库是存储数据的集合,你可以把它理解为多个数据表。
  • DBA 的英文是 DataBase Administrator,数据库管理员。一般企业里的 DBA 主要负责数据库的权限控制、维护及一些偏底层的事情,像表设计查询优化这种业务相关的通常是业务上的后端处理。
  • DBS 的英文是 DataBase System,数据库系统。它是更大的概念,包括了数据库、数据库管理系统以及数据库管理人员 DBA。

数据库类型

  • 关系型数据库(RDBMS)就是建立在关系模型基础上的数据库,SQL 就是关系型数据库的查询语言。
  • 键值型数据库通过 Key-Value 键值的方式来存储数据,其中 Key 和 Value 可以是简单的对象,也可以是复杂的对象。Key 作为唯一的标识符,优点是查找速度快,在这方面明显优于关系型数据库,同时缺点也很明显,它无法像关系型数据库一样自由使用条件过滤(比如 WHERE),如果你不知道去哪里找数据,就要遍历所有的键,这就会消耗大量的计算。键值型数据库典型的使用场景是作为内容缓存。Redis 是最流行的键值型数据库。
  • 文档型数据库用来管理文档,在数据库中文档作为处理信息的基本单位,一个文档就相当于一条记录,MongoDB 是最流行的文档型数据库。
  • 搜索引擎也是数据库检索中的重要应用,常见的全文搜索引擎有 Elasticsearch、Splunk 和 Solr。虽然关系型数据库采用了索引提升检索效率,但是针对全文索引效率却较低。搜索引擎的优势在于采用了全文搜索的技术,核心原理是“倒排索引”。
  • 列式数据库是相对于行式存储的数据库,Oracle、MySQL、SQL Server 等数据库都是采用的行式存储(Row-based),而列式数据库是将数据按照列存储到数据库中,这样做的好处是可以大量降低系统的 I/O(行式存储是把一行的数据都串起来进行存储,然后再存储下一行。同样,列式存储是把一列的数据都串起来进行存储,然后再存储下一列。这样做的好处,就是相邻的数据的数据类型是一样的,因此也更容易压缩。压缩之后就自然降低了IO),适合于分布式文件系统,不足在于功能相对有限。
  • 图形数据库,利用了图这种数据结构存储了实体(对象)之间的关系。最典型的例子就是社交网络中人与人的关系,数据模型主要是以节点和边(关系)来实现,特点在于能高效地解决复杂的关系问题。

相比于 SQL,NoSQL 泛指非关系型数据库,包括了上述的键值型数据库、文档型数据库、搜索引擎、列存储和图形数据库。

SQL 执行

Oracle

SQL 在 Oracle 中的执行过程:

SQL 在 Oracle 中的执行过程

从上面这张图中可以看出,SQL 语句在 Oracle 中经历了以下的几个步骤。

  1. 语法检查:检查 SQL 拼写是否正确,如果不正确,Oracle 会报语法错误。
  2. 语义检查:检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候,列名写错了,系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。
  3. 权限检查:看用户是否具备访问该数据的权限。
  4. 共享池检查:共享池(Shared Pool)是一块内存池,最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划,来判断进行软解析,还是硬解析。那软解析和硬解析又该怎么理解呢?在共享池中,Oracle 首先对 SQL 语句进行 Hash 运算,然后根据 Hash 值在库缓存(Library Cache)中查找,如果存在 SQL 语句的执行计划,就直接拿来执行,直接进入“执行器”的环节,这就是软解析。如果没有找到 SQL 语句和执行计划,Oracle 就需要创建解析树进行解析,生成执行计划,进入“优化器”这个步骤,这就是硬解析。
  5. 优化器:优化器中就是要进行硬解析,也就是决定怎么做,比如创建解析树,生成执行计划。
  6. 执行器:当有了解析树和执行计划之后,就知道了 SQL 该怎么被执行,这样就可以在执行器中执行语句了。

共享池是 Oracle 中的术语,包括了库缓存,数据字典缓冲区等。库缓存区,它主要缓存 SQL 语句和执行计划。而数据字典缓冲区存储的是 Oracle 中的对象定义,比如表、视图、索引等对象。当对 SQL 语句进行解析的时候,如果需要相关的数据,会从数据字典缓冲区中提取。

库缓存这一个步骤,决定了 SQL 语句是否需要进行硬解析。为了提升 SQL 的执行效率,我们应该尽量避免硬解析,因为在 SQL 的执行过程中,创建解析树,生成执行计划是很消耗资源的。通过绑定变量来减少硬解析,例如 SQL> select * from player where player_id = :player_id;,缺点是使用动态 SQL 的方式,因为参数不同,会导致 SQL 的执行效率不同,同时 SQL 优化也会比较困难。

MySQL

MySQL 流程

可以看到 MySQL 由三层构成:

  • 连接层:客户端和服务器端建立连接,客户端发送 SQL 至服务器端;
  • SQL 层:对 SQL 语句进行查询处理;
  • 存储引擎层:与数据库文件打交道,负责数据的存储和读取。

SQL 层的结构

能看到 SQL 语句在 MySQL 中的流程是:SQL 语句→缓存查询→解析器→优化器→执行器,但是 8.0 以后的版本不再支持查询缓存,而是直接执行解析器→优化器→执行器的流程

与 Oracle 不同的是,MySQL 的存储引擎采用了插件的形式,每个存储引擎都面向一种特定的数据库应用环境,下面是一些常见的存储引擎:

  • InnoDB 存储引擎:它是 MySQL 5.5 版本之后默认的存储引擎,最大的特点是支持事务、行级锁定、外键约束等。
  • MyISAM 存储引擎:在 MySQL 5.5 版本之前是默认的存储引擎,不支持事务,也不支持外键,最大的特点是速度快,占用资源少。
  • Memory 存储引擎:使用系统内存作为存储介质,以便得到更快的响应速度。不过如果 mysqld 进程崩溃,则会导致所有的数据丢失,因此我们只有当数据是临时的情况下才使用 Memory 存储引擎。
  • NDB 存储引擎:也叫做 NDB Cluster 存储引擎,主要用于 MySQL Cluster 分布式集群环境,类似于 Oracle 的 RAC 集群。
  • Archive 存储引擎:它有很好的压缩机制,用于文件归档,在请求写入时会进行压缩,所以也经常用来做仓库。

在 MySQL 中每个表的设计都可以采用不同的存储引擎,我们可以根据实际的数据处理需要来选择存储引擎,这也是 MySQL 的强大之处。

SQL 执行分析-MySQL

-- 查看 profiling 是否开启
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

-- 输出为 0 表示未开启
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 查询表数据
mysql> select * from student;

-- 查看当前会话产生的所有 profiles
mysql> show profiles;
+----------+------------+---------+
| Query_ID | Duration   | Query   |
+----------+------------+---------+
...

-- 获取上一次操作的执行时间
mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000594 |
| Executing hook on transaction  | 0.000046 |
| starting                       | 0.000020 |
| checking permissions           | 0.000018 |  -- 权限检查
| Opening tables                 | 0.019565 |  -- 打开表
| init                           | 0.000033 |  -- 初始化
| System lock                    | 0.000032 |  -- 锁系统
| optimizing                     | 0.000017 |  -- 优化查询
| statistics                     | 0.000027 |
| preparing                      | 0.000029 |  -- 准备
| executing                      | 0.000045 |  -- 执行
| end                            | 0.000016 |
| query end                      | 0.000016 |
| waiting for handler commit     | 0.000031 |
| closing tables                 | 0.000022 |
| freeing items                  | 0.000075 |
| cleaning up                    | 0.000048 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

-- 查询指定操作 id 的 profiles
mysql> show profile for query 2;
评论
  • 按正序
  • 按倒序
  • 按热度