"코드형 데이터베이스" 경험

SQL, 이보다 더 간단한 것은 무엇일까요? 우리 각자는 간단한 요청을 작성할 수 있습니다. 고르다, 필수 열을 나열한 다음 에, 테이블 이름, 일부 조건 어디에 그게 전부입니다. 유용한 데이터는 우리 주머니에 있으며 (거의) 그 당시 어떤 DBMS가 내부에 있는지에 관계없이(또는 아마도) 전혀 DBMS가 아닙니다). 결과적으로, 거의 모든 데이터 소스(관계형이든 그렇지 않든)에 대한 작업은 일반 코드의 관점에서 고려될 수 있습니다(버전 제어, 코드 검토, 정적 분석, 자동 테스트 등이 암시하는 모든 것 포함). 이는 데이터 자체, 스키마 및 마이그레이션뿐만 아니라 일반적으로 스토리지의 전체 수명에 적용됩니다. 이 기사에서는 "코드형 데이터베이스"라는 관점에서 다양한 데이터베이스 작업의 일상적인 작업과 문제에 대해 설명합니다.

그리고 바로 시작해보자 ORM. "SQL 대 ORM" 유형의 첫 번째 전투는 XNUMX년에 나타났습니다. 페트린 이전의 루스'.

객체 관계형 매핑

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 증오".

바리케이드의 반대편에는 순수한 "수제" SQL 지지자들이 추가 계층이나 추상화 없이 DBMS에서 모든 기능을 짜낼 수 있는 능력이 있다는 점에 주목합니다. 결과적으로 특별히 교육을 받은 사람들이 데이터베이스에 참여하는 "데이터 중심" 프로젝트가 나타나고(그들은 "기본주의자"이기도 하고 "기본주의자"이기도 하며 "기본주의자"이기도 함) 개발자도 마찬가지입니다. 세부 사항을 다루지 않고 미리 만들어진 뷰와 저장 프로시저만 "풀"하면 됩니다.

우리가 두 세계의 장점을 모두 갖고 있다면 어떨까요? 생명을 확인하는 이름을 가진 훌륭한 도구에서 이것이 수행되는 방법 예SQL. 무료 번역에서 일반적인 개념 중 몇 줄을 소개하겠습니다. 더 자세히 알아볼 수 있습니다. 여기에.

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 자체" 원칙을 준수하면 다음과 같은 이점을 얻을 수 있습니다.

• 구문론적 놀라움은 없습니다. 귀하의 데이터베이스는 (다른 데이터베이스와 마찬가지로) SQL 표준을 100% 준수하지 않습니다. 그러나 이는 Yesql에서는 중요하지 않습니다. SQL과 동등한 구문을 사용하는 함수를 찾는 데 시간을 낭비하지 않을 것입니다. 함수로 돌아갈 필요가 없습니다. (원시 SQL "some('funky'::SYNTAX)")).
• 최고의 편집자 지원. 귀하의 편집자는 이미 뛰어난 SQL 지원 기능을 갖추고 있습니다. SQL을 SQL로 저장하면 간단하게 사용할 수 있습니다.
• 팀 호환성. DBA는 Clojure 프로젝트에서 사용하는 SQL을 읽고 쓸 수 있습니다.
• 성능 튜닝이 더 쉬워졌습니다. 문제가 있는 쿼리에 대한 계획을 세워야 합니까? 쿼리가 일반 SQL인 경우에는 문제가 되지 않습니다.
• 쿼리를 재사용합니다. 동일한 SQL 파일을 다른 프로젝트에 끌어서 놓으세요. 그냥 오래된 SQL이기 때문입니다. 그냥 공유하세요.

제 생각에는 이 아이디어는 매우 멋지고 동시에 매우 간단합니다. 덕분에 이 프로젝트는 많은 것을 얻었습니다. 추종자 다양한 언어로. 그리고 우리는 다음으로 ORM을 훨씬 넘어서는 다른 모든 것에서 SQL 코드를 분리하는 유사한 철학을 적용하려고 노력할 것입니다.

IDE 및 DB 관리자

간단한 일상 업무부터 시작해 보겠습니다. 예를 들어 스키마에서 테이블을 찾고 해당 구조(사용되는 열, 키, 인덱스, 제약 조건 등)를 연구하는 등 데이터베이스에서 일부 개체를 검색해야 하는 경우가 많습니다. 그리고 그래픽 IDE나 작은 DB 관리자에서 무엇보다도 우리는 바로 이러한 기능을 기대합니다. 필요한 정보가 포함된 창이 나타날 때까지 속도가 빠르고(특히 원격 데이터베이스에 대한 연결이 느린 경우) XNUMX분을 기다릴 필요가 없으며, 동시에 수신된 정보는 신선하고 관련성이 높습니다. 캐시된 정크가 아닙니다. 더욱이, 데이터베이스가 더 복잡하고 클수록, 그 수가 많을수록 이를 수행하는 것이 더 어려워집니다.

하지만 보통은 마우스를 버리고 코드만 작성합니다. "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/편집기에는 자주 사용하는 쿼리에 대해 미리 준비된 스니펫 세트가 있고 남은 것은 다음을 입력하는 것뿐입니다. 개체 이름을 템플릿에 추가합니다.

결과적으로 이 객체 탐색 및 검색 방법은 훨씬 더 유연하고 많은 시간을 절약하며 현재 필요한 형식으로 정보를 정확하게 얻을 수 있습니다(예를 들어 게시물에 설명된 대로). "어떤 형식으로든 데이터베이스에서 데이터 내보내기: IntelliJ 플랫폼에서 IDE가 수행할 수 있는 작업").

개체 작업

필요한 물건을 찾아 연구한 후에는 그 물건을 가지고 뭔가 유용한 일을 할 차례입니다. 당연히 키보드에서 손가락을 떼지 않고도 가능합니다.

단순히 테이블을 삭제하면 거의 모든 데이터베이스에서 동일하게 보인다는 것은 비밀이 아닙니다.

drop table hr.persons

그러나 테이블을 만들면 더욱 흥미로워집니다. 대부분의 NoSQL을 포함하여 거의 모든 DBMS는 한 가지 형식 또는 다른 형식으로 "테이블을 생성"할 수 있으며 주요 부분(이름, 열 목록, 데이터 유형)은 약간 다를 수 있지만 기타 세부 사항은 극적으로 다를 수 있으며 상황에 따라 달라집니다. 특정 DBMS의 내부 장치 및 기능. 제가 가장 좋아하는 예는 Oracle 문서에 "create table" 구문에 대한 "naked" BNF만 있다는 것입니다. 31페이지를 차지. 다른 DBMS는 좀 더 적당한 기능을 갖고 있지만 각각 테이블 생성을 위한 흥미롭고 독특한 기능도 많이 가지고 있습니다.포스트 그레스, MySQL은, 바퀴벌레, 카산드라). 다른 IDE(특히 범용 IDE)의 그래픽 "마법사"가 이러한 모든 기능을 완전히 포괄할 수 있을 가능성은 거의 없으며, 설령 가능하더라도 마음이 약한 사람들에게는 광경이 아닐 것입니다. 동시에 정확하고 시기적절하게 작성된 진술 테이블 생성 이를 통해 모든 기능을 쉽게 사용할 수 있고, 데이터에 대한 저장 및 액세스를 안정적이고 최적이며 최대한 편안하게 만들 수 있습니다.

또한 많은 DBMS에는 다른 DBMS에서는 사용할 수 없는 고유한 특정 유형의 개체가 있습니다. 또한 데이터베이스 개체뿐만 아니라 DBMS 자체에서도 작업을 수행할 수 있습니다. 예를 들어 프로세스를 "종료"하고, 일부 메모리 영역을 확보하고, 추적을 활성화하고, "읽기 전용" 모드로 전환하는 등의 작업을 수행할 수 있습니다.

이제 좀 그려보자

가장 일반적인 작업 중 하나는 데이터베이스 개체로 다이어그램을 작성하고 아름다운 그림에서 개체와 개체 간의 연결을 확인하는 것입니다. 거의 모든 그래픽 IDE, 별도의 "명령줄" 유틸리티, 특수 그래픽 도구 및 모델러가 이를 수행할 수 있습니다. 그들은 "가능한 한 최선을 다해" 무언가를 그려줄 것이며 구성 파일의 몇 가지 매개변수나 인터페이스의 확인란을 통해서만 이 프로세스에 약간의 영향을 미칠 수 있습니다.

하지만 이 문제는 코드를 사용하면 훨씬 더 간단하고 유연하며 우아하게 해결할 수 있습니다. 복잡한 다이어그램을 생성하기 위해 우리는 여러 가지 전문적인 마크업 언어(DOT, GraphML 등)와 이러한 지침을 읽고 다양한 형식으로 시각화할 수 있는 다양한 애플리케이션(GraphViz, PlantUML, Mermaid)을 보유하고 있습니다. . 글쎄, 우리는 객체와 객체 간의 연결에 대한 정보를 얻는 방법을 이미 알고 있습니다.

PlantUML을 사용하여 이것이 어떻게 보일 수 있는지에 대한 작은 예는 다음과 같습니다. PostgreSQL용 데모 데이터베이스 (왼쪽에는 PlantUML에 필요한 명령을 생성하는 SQL 쿼리가 있고 오른쪽에는 결과가 있습니다):

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'

그리고 조금만 시도해 보면 PlantUML용 ER 템플릿 실제 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 Enterprise Manager의 그래프 스크린샷을 보냈고, 거기에서 그는 "이해성"을 의미하는 빨간색 마커로 중요한 부분을 주의 깊게 강조했습니다( 가볍게 말하면 별로 도움이 되지 않았습니다.) 그리고 이 '포토카드'를 바탕으로 치료를 해야 했어요. 동시에, 누구도 귀중한(두 가지 의미에서) Enterprise Manager에 액세스할 수 없었습니다. 시스템이 복잡하고 비용이 많이 들기 때문에 갑자기 "개발자들이 뭔가에 걸려서 모든 것을 망가뜨립니다." 이에 개발자들은 브레이크의 위치와 원인을 '경험적으로' 찾아내고 패치를 공개했다. 가까운 시일 내에 DBA의 위협적인 편지가 다시 도착하지 않으면 모든 사람은 안도의 한숨을 쉬고 현재 작업으로 돌아갈 것입니다(새 편지가 올 때까지).

그러나 모니터링 프로세스는 더욱 재미있고 친근해 보일 수 있으며, 가장 중요하게는 모든 사람이 접근 가능하고 투명하게 보일 수 있습니다. 최소한 기본 모니터링 시스템에 추가되는 기본 부분입니다(확실히 유용하고 대부분의 경우 대체할 수 없음). 모든 DBMS는 현재 상태와 성능에 대한 정보를 무료로 공유할 수 있습니다. 동일한 "피묻은" Oracle DB에서는 프로세스 및 세션에서 버퍼 캐시 상태에 이르기까지 성능에 대한 거의 모든 정보를 시스템 뷰에서 얻을 수 있습니다(예: DBA 스크립트, "모니터링" 섹션). Postgresql에는 또한 다음을 위한 다양한 시스템 뷰가 있습니다. 데이터베이스 모니터링, 특히 다음과 같이 DBA의 일상 생활에 없어서는 안될 것들입니다. pg_stat_activity, pg_stat_database, pg_stat_bg작가. MySQL에는 이를 위한 별도의 스키마도 있습니다. 성능_스키마. A Mongo 내장 프로파일러 성능 데이터를 시스템 모음으로 집계합니다. 시스템.프로필.

따라서 사용자 정의 SQL 쿼리를 수행할 수 있는 일종의 메트릭 수집기(Telegraf, Metricbeat, Collectd), 이러한 메트릭의 저장(InfluxDB, Elasticsearch, Timescaledb) 및 시각화 도구(Grafana, Kibana)를 사용하면 상당히 쉽게 결과를 얻을 수 있습니다. 그리고 다른 시스템 전반의 지표(예를 들어 애플리케이션 서버, OS 등에서 획득)와 긴밀하게 통합되는 유연한 모니터링 시스템입니다. 예를 들어, 이는 InfluxDB + Grafana 조합과 액세스할 수 있는 시스템 뷰에 대한 쿼리 세트를 사용하는 pgwatch2에서 수행됩니다. 맞춤 쿼리 추가.

전체로

이는 일반 SQL 코드를 사용하여 데이터베이스로 수행할 수 있는 작업의 대략적인 목록일 뿐입니다. 더 많은 용도를 찾을 수 있을 것이라고 확신합니다. 댓글을 작성해 주세요. 그리고 다음번에는 이 모든 것을 자동화하고 CI/CD 파이프라인에 포함시키는 방법(그리고 가장 중요한 이유)에 대해 이야기하겠습니다.

출처 : habr.com