SQL，还有什么可以更简单的呢？ 我们每个人都可以写一个简单的请求 - 我们输入 选择，列出所需的列，然后 止，表名，一些条件 哪里 仅此而已 - 有用的数据就在我们的口袋里，并且（几乎）无论当时哪个 DBMS 在幕后（或者可能是 ）。 因此，可以从普通代码的角度考虑使用几乎任何数据源（关系型数据源或非关系型数据源）（以及它所暗示的所有内容 - 版本控制、代码审查、静态分析、自动测试等等）。 这不仅适用于数据本身、模式和迁移，而且通常适用于存储的整个生命周期。 在本文中，我们将在“数据库即代码”的视角下讨论使用各种数据库的日常任务和问题。
让我们从 。 “SQL vs ORM”类型的第一场战斗早在 .
对象关系映射
ORM 支持者传统上看重开发速度和易用性、独立于 DBMS 和干净的代码。 对于我们许多人来说，使用数据库的代码（通常是数据库本身）
它通常看起来像这样......
@Entity
@Table(name = "stock", catalog = "maindb", uniqueConstraints = {
@UniqueConstraint(columnNames = "STOCK_NAME"),
@UniqueConstraint(columnNames = "STOCK_CODE") })
public class Stock implements java.io.Serializable {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "STOCK_ID", unique = true, nullable = false)
public Integer getStockId() {
return this.stockId;
}
...该模型挂有巧妙的注释，并且在幕后某个地方，勇敢的 ORM 生成并执行大量 SQL 代码。 顺便说一句，开发人员正在尽最大努力通过数公里的抽象将自己与数据库隔离，这表明一些 .
在障碍的另一边，纯“手工”SQL 的拥护者注意到无需额外的层和抽象就可以从 DBMS 中榨取所有汁液的能力。 结果，出现了“以数据为中心”的项目，其中经过专门培训的人员参与数据库（他们也是“基础主义者”，他们也是“基础主义者”，他们也是“basdeners”等），开发人员只需“拉取”现成的视图和存储过程，无需深入细节。
如果我们两全其美怎么办？ 这是如何在一个具有肯定生命名字的奇妙工具中实现的 。 我会在我的意译中给出几行一般概念，你可以更详细地熟悉它 .
Clojure 是一种很酷的用于创建 DSL 的语言，但 SQL 本身也是一种很酷的 DSL，我们不需要另一种。 S 表达式很棒，但它们没有添加任何新内容。 结果，我们为了括号而得到括号。 不同意？ 然后等待数据库的抽象开始泄漏的那一刻，你开始与该函数战斗 （原始 SQL）
所以我该怎么做？ 让我们将 SQL 保留为常规 SQL - 每个请求一个文件：
-- name: users-by-country
select *
from users
where country_code = :country_code...然后读取此文件，将其转换为常规 Clojure 函数：
(defqueries "some/where/users_by_country.sql"
{:connection db-spec})
;;; A function with the name `users-by-country` has been created.
;;; Let's use it:
(users-by-country {:country_code "GB"})
;=> ({:name "Kris" :country_code "GB" ...} ...)遵循“SQL 本身，Clojure 本身”原则，您将得到：
- 没有语法上的意外。 您的数据库（与任何其他数据库一样）并不 100% 符合 SQL 标准 - 但这对于 Yesql 来说并不重要。 您永远不会浪费时间寻找具有 SQL 等效语法的函数。 你永远不必返回一个函数 (raw-sql "some('funky'::SYNTAX)")).
- 最好的编辑器支持。 您的编辑器已经拥有出色的 SQL 支持。 通过将 SQL 保存为 SQL，您可以简单地使用它。
- 团队兼容性。 您的 DBA 可以读取和写入您在 Clojure 项目中使用的 SQL。
- 更轻松的性能调整。 需要为有问题的查询制定计划吗？ 当您的查询是常规 SQL 时，这不是问题。
- 重用查询。 将这些相同的 SQL 文件拖放到其他项目中，因为它只是普通的旧 SQL - 只需共享它即可。
在我看来，这个想法非常酷，同时又非常简单，因此该项目获得了很多好评 多种语言版本。 接下来我们将尝试应用类似的理念，将 SQL 代码与 ORM 之外的其他代码分开。
IDE 和数据库管理器
让我们从一个简单的日常任务开始。 通常我们必须在数据库中搜索一些对象，例如，在模式中找到一个表并研究它的结构（使用了哪些列、键、索引、约束等）。 首先，我们期望从任何图形 IDE 或小型数据库管理器中获得这些能力。 这样速度很快，您不必等待半个小时才能绘制包含必要信息的窗口（尤其是与远程数据库的连接速度较慢），同时收到的信息是新鲜且相关的，并且没有缓存垃圾。 而且，数据库越复杂、越大、数量越多，做到这一点就越困难。
但通常我会扔掉鼠标，只写代码。 假设您需要找出“HR”架构中包含哪些表（以及哪些属性）。 在大多数 DBMS 中，可以通过 information_schema 中的这个简单查询来实现所需的结果：
select table_name
, ...
from information_schema.tables
where schema = 'HR'从数据库到数据库，此类参考表的内容根据每个 DBMS 的功能而有所不同。 例如，对于 MySQL，您可以从同一本参考书中获取特定于该 DBMS 的表参数：
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle 不知道 information_schema，但它确实有 ，并且不会出现大问题：
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse 也不例外：
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'类似的事情可以在 Cassandra 中完成（它用列族代替表，用键空间代替模式）：
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'对于大多数其他数据库，您也可以提出类似的查询（甚至 Mongo 也有 ，其中包含有关系统中所有集合的信息）。
当然，通过这种方式，您不仅可以获取有关表的信息，还可以获取有关任何对象的信息。 有时，好心人会为不同的数据库分享这样的代码，例如，在 habra 文章“记录 PostgreSQL 数据库的函数”系列中（, , ）。 当然，将这整堆查询保留在我的脑海中并不断地输入它们是一种乐趣，因此在我最喜欢的 IDE/编辑器中，我有一组预先准备好的常用查询片段，剩下的就是输入对象名称添加到模板中。
因此，这种导航和搜索对象的方法更加灵活，节省大量时间，并且允许您以现在需要的形式准确获取信息（例如，帖子中描述的） ).
对对象的操作
在我们找到并研究了必要的对象之后，是时候用它们做一些有用的事情了。 当然，手指也无需离开键盘。
众所周知，简单地删除表在几乎所有数据库中看起来都是一样的：
drop table hr.persons但随着表的创建，它变得更加有趣。 几乎任何 DBMS（包括许多 NoSQL）都可以以一种或另一种形式“创建表”，其主要部分甚至会略有不同（名称、列列表、数据类型），但其他细节可能会有很大差异，具体取决于特定 DBMS 的内部设备和功能。 我最喜欢的例子是，在 Oracle 文档中，“创建表”语法只有“裸露”的 BNF 。 其他 DBMS 的功能较为有限，但它们也都具有许多有趣且独特的用于创建表的功能（, , , ）。 其他 IDE（尤其是通用 IDE）的任何图形“向导”不太可能完全涵盖所有这些功能，即使可以，对于胆小的人来说也不会是一个奇观。 同时，正确、及时的书面陈述 创建表 将使您能够轻松地使用所有这些功能，使数据的存储和访问变得可靠、优化且尽可能舒适。
此外，许多 DBMS 都有自己特定类型的对象，这些对象在其他 DBMS 中不可用。 此外，我们不仅可以对数据库对象执行操作，还可以对DBMS本身执行操作，例如“杀死”进程、释放一些内存区域、启用跟踪、切换到“只读”模式等等。
现在让我们画一点
最常见的任务之一是使用数据库对象构建图表，并在漂亮的图片中查看对象及其之间的连接。 几乎任何图形 IDE、单独的“命令行”实用程序、专用图形工具和建模器都可以做到这一点。 他们会“尽其所能”为您绘制一些东西，您只需借助配置文件中的一些参数或界面中的复选框即可稍微影响此过程。
但这个问题可以更简单、更灵活、更优雅地解决，当然还要借助代码。 为了创建任意复杂的图表，我们有几种专门的标记语言（DOT、GraphML 等），以及一系列分散的应用程序（GraphViz、PlantUML、Mermaid），它们可以读取此类指令并以各种格式将其可视化。 好吧，我们已经知道如何获取有关对象及其之间的联系的信息。
下面是一个小例子，使用 PlantUML 和 （左边是一个 SQL 查询，它将生成 PlantUML 所需的指令，右边是结果）：
select '@startuml'||chr(10)||'hide methods'||chr(10)||'hide stereotypes' union all
select distinct ccu.table_name || ' --|> ' ||
tc.table_name as val
from table_constraints as tc
join key_column_usage as kcu
on tc.constraint_name = kcu.constraint_name
join constraint_column_usage as ccu
on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = 'FOREIGN KEY'
and tc.table_name ~ '.*' union all
select '@enduml'如果你尝试一点，然后基于 你可以得到与真实 ER 图非常相似的东西：
SQL查询稍微复杂一些
-- Шапка
select '@startuml
!define Table(name,desc) class name as "desc" << (T,#FFAAAA) >>
!define primary_key(x) <b>x</b>
!define unique(x) <color:green>x</color>
!define not_null(x) <u>x</u>
hide methods
hide stereotypes'
union all
-- Таблицы
select format('Table(%s, "%s n information about %s") {'||chr(10), table_name, table_name, table_name) ||
(select string_agg(column_name || ' ' || upper(udt_name), chr(10))
from information_schema.columns
where table_schema = 'public'
and table_name = t.table_name) || chr(10) || '}'
from information_schema.tables t
where table_schema = 'public'
union all
-- Связи между таблицами
select distinct ccu.table_name || ' "1" --> "0..N" ' || tc.table_name || format(' : "A %s may haven many %s"', ccu.table_name, tc.table_name)
from information_schema.table_constraints as tc
join information_schema.key_column_usage as kcu on tc.constraint_name = kcu.constraint_name
join information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = 'FOREIGN KEY'
and ccu.constraint_schema = 'public'
and tc.table_name ~ '.*'
union all
-- Подвал
select '@enduml'
如果您仔细观察，就会发现许多可视化工具实际上也使用类似的查询。 确实，这些要求通常是深刻的 ，更不用说对它们进行任何修改。
指标和监控
让我们继续讨论传统上复杂的主题——数据库性能监控。 我记得“我的一个朋友”给我讲过一个真实的小故事。 在另一个项目中，住着一位强大的 DBA，很少有开发人员认识他，或者见过他本人（尽管据传言，他在隔壁大楼的某个地方工作）。 X点，当一家大型零售商的生产系统再次开始“感觉不好”时，他默默地发来了Oracle企业管理器的图表截图，并在图表上仔细地用红色标记突出显示了关键的地方，以确保“可理解性”（温和地说，这并没有多大帮助）。 基于这张“照片卡”我不得不对待。 与此同时，没有人能够访问宝贵的（在这个词的两种意义上）企业管理器，因为该系统既复杂又昂贵，突然间“开发人员偶然发现了一些东西并破坏了一切。” 因此，开发者“凭经验”找到了刹车的位置和原因，并发布了补丁。 如果近期DBA的威胁信没有再次到达的话，那么大家就可以松口气，回到当前的任务中了（直到新的信）。
但监控过程可以看起来更有趣和友好，最重要的是，每个人都可以访问和透明。 至少是它的基本部分，作为主要监控系统的补充（这当然很有用，并且在许多情况下是不可替代的）。 任何 DBMS 都可以自由且绝对免费地共享有关其当前状态和性能的信息。 在同一个“血腥”Oracle DB 中，几乎所有有关性能的信息都可以从系统视图中获取，范围从进程和会话到缓冲区高速缓存的状态（例如， ，“监控”部分）。 Postgresql 还有一大堆系统视图 ，特别是任何DBA日常生活中都不可或缺的那些，比如 , , 。 MySQL 甚至为此有一个单独的模式。 。 A In Mongo 内置 将性能数据聚合到系统集合中 .
因此，配备某种可以执行自定义 SQL 查询的指标收集器（Telegraf、Metricbeat、Collectd）、这些指标的存储（InfluxDB、Elasticsearch、Timescaledb）和可视化工具（Grafana、Kibana），您可以获得相当简单的结果灵活的监控系统将与其他系统范围的指标（例如从应用程序服务器、操作系统等获取）紧密集成。 例如，这是在 pgwatch2 中完成的，它使用 InfluxDB + Grafana 组合和一组对系统视图的查询，也可以访问这些视图 .
在总
这只是使用常规 SQL 代码对我们的数据库可以完成的操作的大致列表。 我相信您可以找到更多用途，请写在评论中。 我们将讨论如何（最重要的是为什么）自动化所有这些并将其包含在您的 CI/CD 管道中。
来源： habr.com