Оптимизация SQL запросов - Продвинутые техники

EXPLAIN SELECT * FROM employees WHERE salary > 50000;

SELECT 
    o.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ius.user_seeks + ius.user_scans + ius.user_lookups AS TotalUsage,
    ius.last_user_seek AS LastSeek,
    ius.last_user_scan AS LastScan
FROM 
    sys.dm_db_index_usage_stats ius
    JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
    JOIN sys.objects o ON i.object_id = o.object_id
WHERE 
    ius.database_id = DB_ID()
    AND o.type = 'U'    -- User tables only
    AND i.type_desc <> 'HEAP'
ORDER BY 
    TotalUsage ASC;

-- Запрос с принудительной перекомпиляцией плана
SELECT e.employee_id, e.full_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.status = @EmployeeStatus
OPTION (RECOMPILE);

SELECT product_id, name FROM products 
WHERE description LIKE '%' + @SearchTerm + '%'

SELECT product_id, name FROM products 
WHERE CONTAINS(description, @SearchTerm)

SELECT c.customer_id, c.name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date > '2023-01-01'

SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE c.customer_id = o.customer_id 
    AND o.order_date > '2023-01-01'
)

-- Неоптимальный вариант с коррелированным подзапросом
SELECT 
    d.department_name,
    (SELECT AVG(salary) FROM employees e 
     WHERE e.department_id = d.department_id) AS avg_salary
FROM departments d;
 
-- Оптимизированный вариант с JOIN и GROUP BY
SELECT 
    d.department_name,
    AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- Создаём временную таблицу с нужными индексами
CREATE TEMPORARY TABLE active_orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    total_amount DECIMAL(10,2)
);
CREATE INDEX ix_active_orders_customer ON active_orders(customer_id);
 
-- Наполняем временную таблицу отфильтрованными данными
INSERT INTO active_orders
SELECT order_id, customer_id, total_amount 
FROM orders 
WHERE STATUS = 'active' AND order_date > DATEADD(MONTH, -3, GETDATE());
 
-- Теперь используем эту временную таблицу в нескольких запросах
SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent
FROM active_orders
GROUP BY customer_id;
 
SELECT ao.order_id, ao.total_amount, c.name
FROM active_orders ao
JOIN customers c ON ao.customer_id = c.customer_id
WHERE ao.total_amount > 1000;

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    date_trunc('month', order_date) AS MONTH,
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(quantity * price) AS total_revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
GROUP BY date_trunc('month', order_date), product_id;
 
CREATE UNIQUE INDEX idx_monthly_sales ON monthly_sales(MONTH, product_id);

CREATE TABLE product_summary (
    product_id INT PRIMARY KEY,
    name VARCHAR(200),
    category_name VARCHAR(100),
    price DECIMAL(10,2),
    stock_level INT,
    main_color VARCHAR(50),
    material VARCHAR(100),
    -- и другие часто запрашиваемые поля
);

SELECT 
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_rank
FROM 
    orders;

SELECT 
    DATE,
    product_id,
    price,
    LAG(price) OVER (PARTITION BY product_id ORDER BY DATE) AS prev_price,
    (price - LAG(price) OVER (PARTITION BY product_id ORDER BY DATE)) / 
    LAG(price) OVER (PARTITION BY product_id ORDER BY DATE) * 100 AS price_change_pct
FROM 
    daily_prices;

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT p.product_id, p.name, p.price FROM products p WHERE 1=1';
 
IF @category_id IS NOT NULL
    SET @SQL = @SQL + ' AND p.category_id = @category_id';
    
IF @min_price IS NOT NULL
    SET @SQL = @SQL + ' AND p.price >= @min_price';
    
IF @color IS NOT NULL
    SET @SQL = @SQL + ' AND EXISTS (SELECT 1 FROM product_attributes pa 
                                WHERE pa.product_id = p.product_id 
                                AND pa.attribute_name = ''Color'' 
                                AND pa.attribute_value = @color)';
-- И так далее для других фильтров
 
EXEC sp_executesql @SQL, 
    N'@category_id INT, @min_price DECIMAL(10,2), @color NVARCHAR(50)', 
    @category_id, @min_price, @color;

-- Сначала получаем список заказов
SELECT order_id, customer_id FROM orders WHERE STATUS = 'completed';
 
-- А потом для КАЖДОГО заказа делаем отдельный запрос для получения товаров
SELECT product_id, quantity FROM order_items WHERE order_id = 1;
SELECT product_id, quantity FROM order_items WHERE order_id = 2;
SELECT product_id, quantity FROM order_items WHERE order_id = 3;
-- И так N раз

SELECT o.order_id, o.customer_id, oi.product_id, oi.quantity
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed';

-- PostgreSQL, MySQL
SELECT * FROM huge_table 
ORDER BY id
LIMIT 1000 OFFSET 5000;
 
-- SQL Server
SELECT * FROM 
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM huge_table
) t
WHERE row_num BETWEEN 5001 AND 6000;

-- Вместо одного большого UPDATE
DECLARE @batch_size INT = 10000;
DECLARE @total INT = (SELECT COUNT(*) FROM huge_table WHERE STATUS = 'pending');
DECLARE @processed INT = 0;
 
WHILE @processed < @total
BEGIN
    UPDATE TOP (@batch_size) huge_table
    SET STATUS = 'processed'
    WHERE STATUS = 'pending';
    
    SET @processed = @processed + @@ROWCOUNT;
END

CREATE TABLE transactions (
    id INT NOT NULL,
    transaction_date DATE NOT NULL,
    amount DECIMAL(10,2),
    customer_id INT,
    PRIMARY KEY (id, transaction_date)
)
PARTITION BY RANGE (YEAR(transaction_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (2023),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

ALTER TABLE transactions DROP PARTITION p0;

SELECT id, dbo.CalculateComplexValue(id) AS complex_value
FROM huge_table;

SELECT id, 
       CASE WHEN some_condition THEN value1 * coefficient1 ELSE value2 * coefficient2 END AS complex_value
FROM huge_table;

DECLARE @customer_id INT;
DECLARE customer_cursor CURSOR FOR 
SELECT customer_id FROM customers WHERE STATUS = 'active';
 
OPEN customer_cursor;
FETCH NEXT FROM customer_cursor INTO @customer_id;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE orders SET discount = 0.1 WHERE customer_id = @customer_id;
    FETCH NEXT FROM customer_cursor INTO @customer_id;
END
 
CLOSE customer_cursor;
DEALLOCATE customer_cursor;

UPDATE orders 
SET discount = 0.1 
WHERE customer_id IN (SELECT customer_id FROM customers WHERE STATUS = 'active');

CREATE EVENT SESSION [SlowQueries] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    WHERE ([duration] > 1000000) -- больше 1 секунды
),
ADD EVENT sqlserver.rpc_completed(
    WHERE ([duration] > 1000000)
)
ADD TARGET package0.event_file(SET filename=N'C:\Temp\SlowQueries.xel')
WITH (MAX_MEMORY=4096 KB, MAX_DISPATCH_LATENCY=30 SECONDS)
GO
 
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;

SET STATISTICS IO, TIME ON;
SELECT * FROM customers WHERE last_order_date > '2023-01-01';

EXPLAIN ANALYZE SELECT * FROM customers WHERE last_order_date > '2023-01-01';

SELECT 
    DB_NAME(l.resource_database_id) AS DatabaseName,
    OBJECT_NAME(p.OBJECT_ID) AS LockedObjectName,
    p.index_id,
    l.resource_type,
    l.resource_description,
    l.request_mode,
    l.request_status,
    r.command,
    r.status,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    st.text AS SQLStatement
FROM 
    sys.dm_tran_locks l
JOIN 
    sys.dm_os_waiting_tasks wt ON l.lock_owner_address = wt.resource_address
JOIN 
    sys.dm_exec_requests r ON wt.waiting_task_address = r.task_address
JOIN 
    sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) AS st
LEFT JOIN 
    sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
WHERE 
    l.resource_database_id = DB_ID() -- текущая БД
ORDER BY 
    l.request_session_id, l.resource_type;

SELECT 
    c.customer_name,
    c.segment,
    SUM(t.amount) AS total_amount,
    COUNT(t.transaction_id) AS transaction_count,
    AVG(t.amount) AS avg_transaction
FROM 
    customers c
JOIN 
    transactions t ON c.customer_id = t.customer_id
WHERE 
    t.transaction_date BETWEEN @start_date AND @end_date
    AND t.status = 'completed'
GROUP BY 
    c.customer_name, c.segment
ORDER BY 
    total_amount DESC;

ALTER TABLE transactions
PARTITION BY RANGE (EXTRACT(YEAR_MONTH FROM transaction_date)) (
    PARTITION p202201 VALUES LESS THAN (202202),
    PARTITION p202202 VALUES LESS THAN (202203),
    -- и так далее
    PARTITION pMAX VALUES LESS THAN MAXVALUE
);

CREATE MATERIALIZED VIEW daily_transaction_stats AS
SELECT 
    customer_id,
    DATE(transaction_date) AS txn_date,
    COUNT(*) AS daily_count,
    SUM(amount) AS daily_sum,
    MIN(amount) AS min_amount,
    MAX(amount) AS max_amount
FROM 
    transactions
WHERE 
    STATUS = 'completed'
GROUP BY 
    customer_id, DATE(transaction_date);

SELECT p.* FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE p.is_active = 1
AND c.is_featured = 1
ORDER BY p.rating DESC
LIMIT 20;

CREATE INDEX idx_products_category_active_rating 
ON products(category_id, is_active, rating DESC);
 
-- А затем запрос был переписан так:
SELECT p.* FROM products p
WHERE p.is_active = 1
AND p.category_id IN (SELECT category_id FROM categories WHERE is_featured = 1)
ORDER BY p.rating DESC
LIMIT 20;