SQL и базы данных: основы (индексы, ключи, нормализация), примеры SQL запросов, работа с таблицами CREATE, DROP, EDIT; использование SELECT (join, distinct, enlosed, like, sum, etc); балансировка/масштабирование (шардирование, репликация)

  • http://sqlfiddle.com/ – очень удобный сайт по экспериментам с SQL (запросы, создание страниц и проч.)
  • https://dbfiddle.uk/Gx3c6fmQ – аналогично, с примером
Отдельные статьи

 

масштабирование/балансировка БД
  • Разделение по базам данных и использование database links (oracle)
  • Для мелких/средних инсталляций зачастую не надо разделять базу и лучше это и не делать 🙂
1. Распределенные БД сильно сложнее не распределенных. Несколько раз подумай, будет ли твой барбершоп настолько популярным, прежде чем туда лезть.
  • Универсального способа масштабироваться не существует. Всегда присутствует trade-off. Выбор между репликацией, горизонтальным и вертикальным шардированием нужно делать, опираясь на предметную область и статистику использования БД. Но можно в среднем придумать некие гайдлайны по выбору:
Шардирование vs Репликация
• отказоустойчивость -> репликация
• много чтения -> репликация или шардирование
• много записи -> шардирование
  • Зачастую в крупных инсталляциях используется одновременно и шардирование и репликация

  • Репликация обыно реализуется в виде мастер-слейв реплики: запись только в мастер, а чтение со всех реплик.
  • шардирование: в отличии от реплик использует не копии базы, а разделение – каждый шард имеет свою часть разделенной БД; у шардов одна схема таблицы/БД, но шарды хранят разные данные (пользователь n1 хранится в первом шарде, пользователь n2 во втором), сами шарды выбираются по принципу хеширования от данных – т.е. куда записать/откуда прочитать данные выбирается на основе хеша
Шардинг (sharding). Шардинг (иногда шардирование) — это другая техника масштабирования работы с данными. Суть его в разделении (партиционирование) базы данных на отдельные части так, чтобы каждую из них можно было вынести на отдельный сервер. 
Осуществить шардирование можно несколькими способами:
- Средствами БД. Некоторые базы — MongoDB, Elasticsearch, ClickHouse и другие — умеют самостоятельно распределять данные между своими экземплярами. ((Считается наилучшим способом - если есть возможность без боли перейти на шардируемую БД, то конечно лучше сделать так. ))
- Надстройками к БД.
- Клиентскими средствами.
Методы работы в этих способах схожи: мы выбираем ключ для распределения данных (это может быть идентификатор, временная метка или хеш записи) и в соответствии с ним записываем информацию в нужный шард. Как правило, ключи стараются выбирать так, чтобы данные были равномерно распределены по шардам.

 

 

SQL Clients
  • Toad – enterprise продукт, работает с популярными в крупном enterprise как SQL, так и noSQL базами
The Toad toolset runs against Oracle, SQL Server, IBM DB2(LUW & z/OS), SAP ((sybase)) and MySQL
  • DBeaver
    • Community версия – работает с большим количеством relational databases (по сути всеми известными популярными), используя JDBC
    • Enterprise – поддерживает работу с noSQL базами типа MongoDB, Cassandra, Redis
  • phpmyadmin, MySQL workbench – для работы только с MySQL, второй более продвинутый
  • консольные для каждой базы
    • mysql
    • sqlite
    • psql
BASIC SQL

DDL – Data Definition Language

CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT

DML – Data Manipulation Language

SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

DCL – Data Control Message

GRANT
REVOKE

TCL – Transaction Control Language

COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION

 

 

ТРАНЗАКЦИТ/TRANSACTIONS

Транзакция — это набор операций по работе с базой данных (БД), объединенных в одну атомарную пачку. Транзакции БД позволяют защититься от частичного исполнения группы запросов – транзакция или будет исполнена полностью или не будет вообще. Это крайне важно во многих сценариях, самое очевидное – перевод денег между счетами. С точки зрения практики, до коммита, но после изменения сделанные, но не примененные изменения можно посмотреть и что еще более важно – откатить эти изменения по rollback. Не откатывать потенциально проблемные изменения и просто отключаться  от базы некорректно – в случае отключения транзакция не откатывается, а следующий кто подключится при коммите применит и старые действия транзакции.

#mysql
start transaction
insert into clients (name, surname) values ('Иван', 'Иванов'); 
insert into clients (name, surname) values ('Вася', 'Пупкин');
commit;
INDEX (индексы)

Ускорение select/join может быть достигнуто за счет использования индексов. Нужно учитывать, что index’ы

  • могут негативно влиять на write, т.е. если в таблицу ожидается большое количество write при меньшем количестве read – в среднем лучше индексы не использовать и наоборот
  • так же по сути создается вспомогательная таблица – это доп. память
  • не гарантируют ускорение чтения напр. если неудачно выбрано поле для индексации, хотя и вероятность что индексы приведут к деградации read низка
Применение индексов представляет собой компромисс между ускорением получения результатов запросов и замедлением обновлений и вставок данных. Первая часть этого компромисса – ускорение запросов – довольно очевидна: если поиск выполняется по отсортированному индексу вместо полного сканирования всей таблиц, то запрос проходит намного быстрее. Но всякий раз, когда вы обновляете, вставляете или удаляете строку таблицы с индексами, индексы также должны быть обновлены соответствующим образом. То есть такие операции на таблицах с индексами обходятся дороже.

Сами индексы работают быстрее за счет оптимизации структуры хранения данных – в виде отсортированных/сбалансированного дерева (проиндексирорванных) данных по которым проще осуществлять поиск.

Жизненный пример – поиск в базе 4 млн записей:

    • часы до индексирования
    • после индексирования 3 секунды

Как проиндексировать таблицу по полю.

CREATE INDEX <table>_index ON <table>(<field>)

 

DROP

В современных базах (MS SQL, MySQL, да даже sqlite3 python) поддерживается полезная функция DROP, но только в случае exist объекта (базы).

DROP TABLE if exists <table>

 

SELECT

Сопоставление таблиц (join, multiple where, вложенные запросы)

В Where плохо добавлять вложенный select т.к. по каждому результату будет дополнительный select. В общем случае всегда лучше сопоставлять данные в таблицах.

Данные в Oracle из нескольких таблиц можно выбрать и сопоставить не только с помощью стандартного join (ANSI синтаксис), но и с помощью указания нескольких таблиц в поле from (в базе, сделанных как вложенный запрос или временных таблиц). Это традицонный синтаксис Oracle.

Данные в таком случае могут выбираться в таблицы по разному, но сопоставление в любом случае идет через WHERE. Если в условиях WHERE указать (+) – это позволяет добавить данные, в случае если подпадение нулевое – т.е. отсутствие данных в одной из таблиц считается подпадением.

#1 JOIN
SELECT w.n,w.employee,w.emp_phone,wbox.equip_type,wbox.entrance,wbox.place
FROM walli_theft w 
LEFT JOIN walli_theft_box wbox on w.n=wbox.n 
WHERE w.h_id='119783'
ORDER BY theft_date

#2 MULTIPLE FROM
SELECT w.n,w.employee,w.emp_phone,wbox.equip_type,wbox.entrance,wbox.place
FROM walli_theft w, walli_theft_box wbox 
WHERE 1=1
AND w.h_id='119783'
AND w.n=wbox.n
ORDER BY theft_date

#3 TEMP TABLE
with
wbox as (
     select * from walli_theft_box
)
SELECT w.n,w.employee,w.emp_phone,wbox.equip_type,wbox.entrance,wbox.place
FROM walli_theft w, wbox
WHERE 1=1
AND w.h_id='119783'
AND w.n=wbox.n
ORDER BY theft_date

#4 ENCLOSED
SELECT w.n,w.employee,w.emp_phone,wbox.equip_type,wbox.entrance,wbox.place
FROM walli_theft w, (select * from walli_theft_box) wbox
WHERE 1=1
AND w.h_id='119783'
AND w.n=wbox.n
ORDER BY theft_date

временная таблица, формируемая на время запроса

Очень полезно, когда есть справочники и ты их сначала объединяешь в нужный вид между собой, а потом select’ишь из каждой временной таблицы данные.

with
 reg as (
  select distinct l.region, d.name
  from dict_one l, dict_two d
  where 1=1
  and d.id=1 and d.code=l.region
 ),
 bra as (
  select distinct l.branch, d.name
  from dict_one l, dict_two d
  where 1=1
  and d.id=2 and d.code=l.branch
 ),
 distr as (
  select distinct l.id, d.name
  from dict_one l, dict_two d
  where 1=1
  and d.id=3 and d.code=l.district
 )
SELECT tb.data,reg.name reg,bra.name bra,distr.name distr
FROM tb t, reg, bra, distr
WHERE 1=1
AND t.region=reg.region
AND t.branch=bra.branch
AND t.area=distr.id

distinct, group by, join

Простой GROUP BY где мы имеем некоторый ID и счетчик повторений этого ID с использованием COUNT (или сумму значений для этого ID с использованием SUM) для одной таблицы.

SELECT <field>, COUNT(<field>) 
FROM <table> 
GROUP BY <field>
EXMPL: SELECT Customer, SUM(OrderPrice) FROM orders GROUP BY Customer
Так же можно присвоить имя полю со счетчиком Count/Sum.
SELECT <field>, COUNT(<field>) AS CountOf
FROM <table> 
GROUP BY <field>
EXMPL: SELECT Customer, SUM(OrderPrice) AS sums FROM orders GROUP BY Customer

На выборку GROUP BY получившую имя с использованием AS можно применить фильтр с использованием HAVING, но не WHERE. Альтернативой HAVING может быть использование вложенных запросов, но читаемость такого кода хуже.

SELECT Customer, SUM(OrderPrice) AS sums FROM orders GROUP BY Customer HAVING sums < 2000

Если все что нужно это исключение дублей (уникальные записи) – нужно использовать DISTINCT, он работает быстрее. Но если нужно исключение дублей (уникальные записи) в рамках нескольких полей – зачастую проще использовать GROUP BY. К примеру, в запросе ниже мы объединяем таблицу пользователей и покупок с использованием LEFT JOIN и отображаем только Имена и Фамилии пользователей, которые имеют заказы с использованием WHERE и GROUP BY.

SELECT p.FirstName, p.LastName FROM persons p LEFT JOIN orders o on p.P_id=o.P_id WHERE o.OrderNo IS NOT NULL GROUP BY p.FirstName, p.LastName

 

стилистика

операции нужно писать с большой буквы, а переменные с малой. В таком случае sublime сможет подсветить синтаксис (должен быть большими SELECT в начале запроса)

date

mysql за три (n) последних месяца (с текущего дня)

select * from table where date >= now()-interval 3 month

mysql за последние тридцать (n) дней

SELECT * FROM table WHERE exec_datetime BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();

like

В Oracle можно спокойно указывать в качестве объекта LIKE переменную, конкатенцию переменной и спец-символа % делаем через ||.

AND gg.gp_text LIKE '%' || na.net_address || '%'

сумма значений выборки (столбца)

SELECT sum(ticket_counts) FROM ticket_films WHERE film='GoT'

вложенные запросы

Пример вложенного SQL – на базе результатов делаем последующие запросы.

-- FIND SWITCHES BY SY_ID overall
select device_id, device_name, serial_number from devices@orange dv where dv.group_id in (
       select device_group_id from os_eqm.device_groups@orange where group_type_id='605' and par_group_id in (
                   select device_group_id from os_eqm.device_groups@orange where group_type_id='1190' and par_group_id=(select header_id from groups_604@orange g4 where g4.adres='554') -- TKD by SY_ID
	     ) -- YASHIK by TKD; 
); -- DEVICES_ID + NAME + SERIAL by yashik

 

Партиционирование таблиц

Отсюда и отсюда

Партиционирование (partitioning) — это разбиение больших таблиц на логические части по выбранным критериям.
Разбиение таблицы на разделы очень полезно, если таблица содержит большое количество данных. Разбиение ускорит выборку и запись в таблицу. Разбивать на разделы нужно только большие таблицы и лишь в том случае, если исключение или сокращение партиций может быть достигнуто на основе критериев SQL-запроса

Вот некоторые преимущества партиционирования:
- Можно сохранять большее количество данных в одной таблице, чем может быть записано на одиночном диске или файловой системе.
- Некоторые запросы могут быть значительно оптимизированы в том, что данные, удовлетворяющие предложению WHERE могут быть сохранены только на одном или большем количестве разделов, таким образом исключая любые остающиеся разделы из поиска.

Leave a Reply