Лучшие практики

-- Плохо
SELECT a, b, c FROM t WHERE d = 1;

-- Хорошо
SELECT 
    customer_id,
    customer_name,
    order_amount
FROM customers
WHERE is_active = 1;

-- Плохо
SELECT c.name, o.amount, i.quantity
FROM customers c, orders o, items i
WHERE c.id = o.cust_id AND o.id = i.order_id;

-- Хорошо
SELECT 
    c.name AS customer_name,
    o.amount AS order_amount,
    i.quantity AS item_quantity
FROM customers AS c
JOIN orders AS o ON c.id = o.customer_id
JOIN order_items AS i ON o.id = i.order_id;

-- Плохо (все в одну строку)
SELECT c.name,o.amount FROM customers c JOIN orders o ON c.id=o.customer_id WHERE o.amount>1000 ORDER BY o.amount DESC;

-- Хорошо (структурированно)
SELECT 
    c.name AS customer_name,
    o.amount AS order_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 1000
ORDER BY o.amount DESC;

-- Плохо (глубоко вложенный подзапрос)
SELECT * FROM (
    SELECT * FROM (
        SELECT * FROM users WHERE is_active = 1
    ) active_users
    WHERE created_at > '2024-01-01'
) recent_users
WHERE city = 'Москва';

-- Хорошо (CTE — понятно и читаемо)
WITH active_users AS (
    SELECT * FROM users WHERE is_active = 1
),
recent_active_users AS (
    SELECT * FROM active_users WHERE created_at > '2024-01-01'
)
SELECT * FROM recent_active_users WHERE city = 'Москва';

-- Расчет доли рынка по категориям товаров
-- Формула: (продажи категории / общие продажи) * 100
WITH category_sales AS (
    SELECT 
        category_id,
        SUM(amount) AS total
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY category_id
),
total_sales AS (
    SELECT SUM(amount) AS total FROM orders WHERE order_date >= '2024-01-01'
)
SELECT 
    cs.category_id,
    cs.total AS category_sales,
    ts.total AS overall_sales,
    ROUND(cs.total / ts.total * 100, 2) AS market_share_percent
FROM category_sales cs
CROSS JOIN total_sales ts;

-- Плохо (читает все колонки, даже ненужные)
SELECT * FROM users WHERE id = 123;

-- Хорошо (только нужные колонки)
SELECT id, name, email FROM users WHERE id = 123;

-- Плохо (фильтрация после группировки)
SELECT department, AVG(salary) 
FROM employees
GROUP BY department
HAVING department IN ('IT', 'Sales');

-- Хорошо (фильтрация до группировки)
SELECT department, AVG(salary) 
FROM employees
WHERE department IN ('IT', 'Sales')
GROUP BY department;

-- Плохо (IN может быть медленным, особенно с большим подзапросом)
SELECT * FROM customers 
WHERE id IN (SELECT customer_id FROM orders);

-- Хорошо (EXISTS останавливается при первом совпадении)
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Плохо (индекс на created_at не используется)
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';

-- Хорошо (индекс используется)
SELECT * FROM orders 
WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16';

-- Посмотреть структуру и примеры данных
SELECT * FROM large_table LIMIT 10;

-- Но будьте осторожны: ORDER BY без LIMIT может быть тяжелым
SELECT * FROM large_table ORDER BY id LIMIT 10;

-- Какие колонки индексировать:
-- 1. Колонки в WHERE
-- 2. Колонки в JOIN
-- 3. Колонки в ORDER BY (если часто)

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- Плохо (OR часто не использует индексы эффективно)
SELECT * FROM users 
WHERE city = 'Москва' OR city = 'СПб';

-- Хорошо (IN лучше)
SELECT * FROM users 
WHERE city IN ('Москва', 'СПб');

-- Для сложных условий — UNION
SELECT * FROM users WHERE city = 'Москва' AND age > 30
UNION
SELECT * FROM users WHERE city = 'СПб' AND status = 'vip';

-- Плохо (UNION убирает дубликаты, требует сортировки)
SELECT name FROM active_users
UNION
SELECT name FROM archived_users;

-- Хорошо (UNION ALL просто объединяет, быстрее)
SELECT name FROM active_users
UNION ALL
SELECT name FROM archived_users;

-- Плохо: JOIN двух больших таблиц без фильтрации
SELECT * FROM orders o
JOIN order_items oi ON o.id = oi.order_id;

-- Хорошо: сначала фильтруем, потом JOIN
SELECT * FROM (
    SELECT * FROM orders WHERE order_date >= '2024-01-01'
) o
JOIN order_items oi ON o.id = oi.order_id;

-- ОПАСНО! SQL-инъекция
"SELECT * FROM users WHERE name = '" + userName + "'";

-- Безопасно (параметризованный запрос)
PREPARE stmt FROM 'SELECT * FROM users WHERE name = ?';
EXECUTE stmt USING @userName;

-- Аналитику — только SELECT
GRANT SELECT ON database.* TO 'analyst'@'localhost';

-- Приложению — только необходимые операции
GRANT SELECT, INSERT, UPDATE ON app_db.* TO 'app_user'@'%';
GRANT DELETE ON app_db.logs TO 'app_user'@'%';  -- только если нужно

-- Никогда не давайте ALL PRIVILEGES обычным пользователям

-- Представление, показывающее только нечувствительные данные
CREATE VIEW public_employee_data AS
SELECT id, name, department, position
FROM employees;
-- скрыты salary, passport_number, address

GRANT SELECT ON public_employee_data TO analyst_role;

-- Если пользователь вводит '%' или '_', это может изменить логику поиска
-- Плохо
SELECT * FROM products WHERE name LIKE '%' + user_input + '%';

-- Хорошо (экранирование специальных символов)
SELECT * FROM products 
WHERE name LIKE CONCAT('%', REPLACE(REPLACE(user_input, '%', '\%'), '_', '\_'), '%') 
ESCAPE '\';

-- Плохо
CREATE TABLE users (
    age VARCHAR(10),      -- число в строке
    is_active VARCHAR(3), -- 'yes'/'no'
    price VARCHAR(20)     -- деньги в строке
);

-- Хорошо
CREATE TABLE users (
    age INT,
    is_active BOOLEAN,
    price DECIMAL(10,2)
);

-- Плохо (NULL везде, где можно)
CREATE TABLE users (
    id INT,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- Хорошо (явные NOT NULL для обязательных полей)
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NULL  -- явно указано, что может быть NULL
);

-- Плохо (FLOAT может дать неточные результаты)
price FLOAT

-- Хорошо (DECIMAL точен)
price DECIMAL(10,2)

-- Плохо
name VARCHAR(255)   -- для имени? 255 символов?
description TEXT    -- а может, VARCHAR(500) достаточно?

-- Хорошо
name VARCHAR(100)      -- достаточно для 99.9% имен
description VARCHAR(500)  -- если знаем, что описания короткие
detailed_description TEXT   -- если действительно нужен длинный текст

-- Плохо (долгая транзакция с внешним вызовом)
BEGIN;
SELECT * FROM users WHERE id = 123;
-- вызов внешнего API (5 секунд)
UPDATE users SET status = 'active' WHERE id = 123;
COMMIT;

-- Хорошо (внешние вызовы за пределами транзакции)
user = SELECT * FROM users WHERE id = 123;
-- вызов внешнего API (5 секунд)
BEGIN;
UPDATE users SET status = 'active' WHERE id = 123;
COMMIT;

-- Псевдокод (язык приложения)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Хорошо (с обработкой ошибок)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
IF (error) THEN
    ROLLBACK;
    RAISE ERROR 'Transaction failed';
ELSE
    COMMIT;
END IF;

-- Для отчетов, где важна согласованность
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT SUM(amount) FROM orders WHERE status = 'completed';
SELECT SUM(amount) FROM orders WHERE status = 'cancelled';
COMMIT;

-- Для банковских транзакций
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- перевод денег
COMMIT;

-- Плохо (неявный JOIN, легко забыть условие)
SELECT * FROM customers, orders;

-- Хорошо (явный JOIN)
SELECT * FROM customers
JOIN orders ON customers.id = orders.customer_id;

-- Плохо (INNER JOIN теряет клиентов без заказов)
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- Хорошо (LEFT JOIN сохраняет всех клиентов)
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- Плохо (медленно, если колонки не индексированы)
SELECT * FROM orders o
JOIN customers c ON o.customer_phone = c.phone;  -- phone без индекса

-- Хорошо (индекс на phone)
CREATE INDEX idx_customers_phone ON customers(phone);
CREATE INDEX idx_orders_customer_phone ON orders(customer_phone);

-- Если нужно больше 5-6 JOIN, возможно, схема требует пересмотра
-- Или стоит использовать материализованные представления

-- Страница 1 (первые 50)
SELECT * FROM products ORDER BY id LIMIT 50 OFFSET 0;

-- Страница 2 (следующие 50)
SELECT * FROM products ORDER BY id LIMIT 50 OFFSET 50;

-- Проблема: OFFSET 1000000 заставляет базу данных пропустить миллион строк
-- Решение: keyset pagination (поиск по последнему ID)
SELECT * FROM products 
WHERE id > 12345 
ORDER BY id 
LIMIT 50;

-- PostgreSQL
INSERT INTO users (id, name, email) 
VALUES (1, 'Иван', 'ivan@example.com')
ON CONFLICT (id) 
DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;

-- MySQL
INSERT INTO users (id, name, email) 
VALUES (1, 'Иван', 'ivan@example.com')
ON DUPLICATE KEY UPDATE 
    name = VALUES(name), 
    email = VALUES(email);

-- Плохо: 1000 отдельных INSERT
INSERT INTO logs (message) VALUES ('log1');
INSERT INTO logs (message) VALUES ('log2');
-- ...

-- Хорошо: один INSERT на 1000 строк
INSERT INTO logs (message) VALUES 
    ('log1'), ('log2'), ..., ('log1000');

-- Получить клиентов с хотя бы одним заказом
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Получить клиентов без заказов
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Плохо (DISTINCT скрывает проблему с дубликатами)
SELECT DISTINCT c.name, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- Скорее всего, проблема: JOIN создает дубликаты из-за нескольких заказов
-- Правильно: использовать агрегацию
SELECT c.name, SUM(o.amount)
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- Плохо (подзапрос выполняется для каждой строки)
SELECT 
    name,
    (SELECT MAX(salary) FROM employees) AS max_salary
FROM employees;

-- Хорошо (вычислить один раз)
WITH max_salary AS (SELECT MAX(salary) AS max_salary FROM employees)
SELECT e.name, m.max_salary
FROM employees e
CROSS JOIN max_salary m;

-- Плохо (индекс не используется)
SELECT * FROM users WHERE LOWER(email) = 'ivan@example.com';

-- Хорошо (хранить email в нижнем регистре или использовать индекс по выражению)
SELECT * FROM users WHERE email = 'ivan@example.com';
-- или
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'ivan@example.com';

-- Плохо (индекс не используется, полное сканирование)
SELECT * FROM products WHERE name LIKE '%phone%';

-- Хорошо (индекс может использоваться)
SELECT * FROM products WHERE name LIKE 'iphone%';

-- Для полнотекстового поиска используйте специализированные инструменты
SELECT * FROM products WHERE MATCH(name) AGAINST('phone');

-- Плохо (очень медленно на больших таблицах)
SELECT * FROM users ORDER BY RAND() LIMIT 10;

-- Хорошо (случайная выборка с использованием TABLESAMPLE или подзапроса)
-- PostgreSQL
SELECT * FROM users TABLESAMPLE SYSTEM(1) LIMIT 10;

-- Альтернатива: случайный ID из диапазона
SELECT * FROM users WHERE id >= (
    SELECT FLOOR(RANDOM() * (MAX(id) - MIN(id) + 1)) + MIN(id) FROM users
) LIMIT 1;