#3- Index và chiến lược indexing chuyên sâu

-- Tạo B-tree index trong MySQL/PostgreSQL
CREATE INDEX idx_users_email ON users(email);

-- MySQL Memory storage engine hỗ trợ hash index
CREATE TABLE cache (
    id INT NOT NULL,
    data VARCHAR(100) NOT NULL,
    PRIMARY KEY USING HASH (id)
) ENGINE=MEMORY;

-- PostgreSQL
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- MySQL/InnoDB: Primary key luôn là clustered index
CREATE TABLE orders (
    id INT PRIMARY KEY,  -- Đây là clustered index
    customer_id INT,
    order_date DATE
);

-- SQL Server: Chỉ định clustered index
CREATE CLUSTERED INDEX idx_orders_date ON orders(order_date);

-- Tạo non-clustered index
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Tạo covering index cho truy vấn phổ biến
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Truy vấn sử dụng covering index (không cần table lookup)
SELECT customer_id, order_date FROM orders WHERE customer_id = 123;

-- MySQL: Xem các truy vấn phổ biến từ slow query log
SELECT query, count_star, avg_latency
FROM performance_schema.events_statements_summary_by_digest
ORDER BY count_star DESC
LIMIT 10;

-- PostgreSQL: Sử dụng pg_stat_statements
SELECT query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- Tốt: Cột có tính chọn lọc cao
CREATE INDEX idx_users_email ON users(email);  -- email thường unique

-- Kém hiệu quả: Cột có tính chọn lọc thấp
CREATE INDEX idx_users_status ON users(status);  -- status có thể chỉ có vài giá trị

-- Tạo composite index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- MySQL: Kiểm tra index usage
SELECT table_name, index_name, stat_value
FROM mysql.innodb_index_stats
WHERE stat_name = 'n_leaf_pages'
ORDER BY stat_value DESC;

-- PostgreSQL: Kiểm tra index usage
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- MySQL: Tìm index dư thừa tiềm năng
SELECT table_schema, table_name, redundant_index_name, dominant_index_name
FROM sys.schema_redundant_indexes;

-- PostgreSQL: So sánh index thủ công
SELECT
    i1.indrelid::regclass AS table_name,
    i1.indexrelid::regclass AS index1,
    i2.indexrelid::regclass AS index2
FROM
    pg_index i1
JOIN
    pg_index i2 ON i1.indrelid = i2.indrelid AND i1.indexrelid < i2.indexrelid
WHERE
    i1.indkey @> i2.indkey OR i2.indkey @> i1.indkey;

-- MySQL: Rebuild index
ALTER TABLE orders REORGANIZE PARTITION;
-- hoặc
OPTIMIZE TABLE orders;

-- PostgreSQL: Reindex
REINDEX TABLE orders;
-- hoặc
REINDEX INDEX idx_orders_customer;

-- MySQL: Cập nhật statistics
ANALYZE TABLE orders;

-- PostgreSQL: Cập nhật statistics
ANALYZE orders;

-- MySQL: Tạo spatial index
CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    location POINT NOT NULL,
    SPATIAL INDEX idx_location (location)
);

-- Truy vấn sử dụng spatial index
SELECT id, name, ST_AsText(location)
FROM locations
WHERE ST_Contains(
    ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
    location
);

-- PostgreSQL: Sử dụng PostGIS extension
CREATE EXTENSION postgis;
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location GEOMETRY(Point, 4326)
);
CREATE INDEX idx_locations_gist ON locations USING GIST(location);

-- MySQL: Tạo full-text index
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX idx_content (title, content)
);

-- Tìm kiếm sử dụng full-text index
SELECT id, title
FROM articles
WHERE MATCH(title, content) AGAINST('database optimization' IN BOOLEAN MODE);

-- PostgreSQL: Sử dụng tsvector và GIN index
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    content_tsv TSVECTOR
);

CREATE INDEX idx_articles_content ON articles USING GIN(content_tsv);

-- Tạo trigger để cập nhật content_tsv
CREATE FUNCTION articles_trigger() RETURNS trigger AS $$
BEGIN
    NEW.content_tsv :=
        setweight(to_tsvector('english', NEW.title), 'A') ||
        setweight(to_tsvector('english', NEW.content), 'B');
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvector_update BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE FUNCTION articles_trigger();

-- PostgreSQL: Tạo functional index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Truy vấn case-insensitive sử dụng index
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- MySQL 8.0+: Functional index
CREATE INDEX idx_users_lower_email ON users((LOWER(email)));

-- PostgreSQL: Tạo partial index
CREATE INDEX idx_orders_completed ON orders(order_date)
WHERE status = 'completed';

-- Truy vấn sử dụng partial index
SELECT * FROM orders
WHERE status = 'completed' AND order_date > '2023-01-01';

-- MySQL 8.0+: Tương tự với invisible index và filtered index

-- Oracle: Tạo bitmap index
CREATE BITMAP INDEX idx_orders_status ON orders(status);

-- PostgreSQL: Không hỗ trợ trực tiếp bitmap index, nhưng có thể sử dụng bitmap scan

-- Chiến lược indexing cho OLTP
-- 1. Index trên primary key và foreign keys
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME,
    status VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- 2. Index cho các truy vấn lookup phổ biến
CREATE INDEX idx_orders_date_status ON orders(order_date, status);

-- 3. Covering index cho các truy vấn hot
CREATE INDEX idx_orders_status_date_customer ON orders(status, order_date, customer_id);

-- Chiến lược indexing cho OLAP
-- 1. Index trên các cột dimension thường xuyên filter
CREATE INDEX idx_sales_product ON sales(product_id);
CREATE INDEX idx_sales_date ON sales(sale_date);
CREATE INDEX idx_sales_region ON sales(region_id);

-- 2. Bitmap index cho low-cardinality columns
-- (Oracle)
CREATE BITMAP INDEX idx_sales_payment_method ON sales(payment_method);

-- 3. Materialized views với index
CREATE MATERIALIZED VIEW mv_sales_by_product AS
SELECT product_id, SUM(amount) as total_sales
FROM sales
GROUP BY product_id;
CREATE INDEX idx_mv_sales_product ON mv_sales_by_product(product_id);

-- Chiến lược indexing cho e-commerce
-- 1. Product search và filtering
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(200),
    description TEXT,
    price DECIMAL(10,2),
    category_id INT,
    brand_id INT,
    created_at TIMESTAMP,
    is_active BOOLEAN
);

-- Full-text search
CREATE FULLTEXT INDEX idx_products_search ON products(name, description);

-- Filtering và sorting
CREATE INDEX idx_products_category_price ON products(category_id, price);
CREATE INDEX idx_products_brand_price ON products(brand_id, price);
CREATE INDEX idx_products_active_created ON products(is_active, created_at);

-- 2. Order management
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

-- Chiến lược indexing cho time-series
-- 1. Partitioning theo thời gian
CREATE TABLE metrics (
    id BIGINT PRIMARY KEY,
    device_id INT,
    metric_type VARCHAR(50),
    value FLOAT,
    timestamp TIMESTAMP
) PARTITION BY RANGE (timestamp);

CREATE TABLE metrics_2023_q1 PARTITION OF metrics
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
CREATE TABLE metrics_2023_q2 PARTITION OF metrics
    FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

-- 2. Index trên timestamp và device_id
CREATE INDEX idx_metrics_device_time ON metrics(device_id, timestamp);

-- 3. Specialized time-series database (TimescaleDB for PostgreSQL)
CREATE EXTENSION IF NOT EXISTS timescaledb;
SELECT create_hypertable('metrics', 'timestamp');

-- Chiến lược indexing cho social networks
-- 1. User relationships
CREATE TABLE friendships (
    user_id INT,
    friend_id INT,
    created_at TIMESTAMP,
    status VARCHAR(20),
    PRIMARY KEY (user_id, friend_id)
);
CREATE INDEX idx_friendships_friend_user ON friendships(friend_id, user_id);
CREATE INDEX idx_friendships_user_status ON friendships(user_id, status);

-- 2. Posts và feed
CREATE TABLE posts (
    id BIGINT PRIMARY KEY,
    user_id INT,
    content TEXT,
    created_at TIMESTAMP
);
CREATE INDEX idx_posts_user_time ON posts(user_id, created_at DESC);

-- 3. Likes và interactions
CREATE TABLE likes (
    user_id INT,
    post_id BIGINT,
    created_at TIMESTAMP,
    PRIMARY KEY (user_id, post_id)
);
CREATE INDEX idx_likes_post_time ON likes(post_id, created_at);

-- Các truy vấn hot
-- 1. Tìm kiếm sản phẩm
SELECT p.*, c.name as category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.name LIKE '%smartphone%'
  AND p.price BETWEEN 100 AND 500
  AND p.is_active = 1
ORDER BY p.created_at DESC
LIMIT 20;

-- 2. Chi tiết đơn hàng
SELECT o.*, oi.*, p.name as product_name
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.id = 12345;

-- 3. Báo cáo bán hàng
SELECT p.category_id, c.name,
       SUM(oi.quantity) as total_quantity,
       SUM(oi.quantity * oi.price) as total_sales
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
  AND o.order_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY p.category_id, c.name
ORDER BY total_sales DESC;

-- Thay thế LIKE với full-text search
CREATE FULLTEXT INDEX idx_products_name_desc ON products(name, description);

-- Index cho filtering và sorting
CREATE INDEX idx_products_active_category_price ON products(is_active, category_id, price);
CREATE INDEX idx_products_active_created ON products(is_active, created_at);

-- Truy vấn tối ưu
SELECT p.*, c.name as category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE MATCH(p.name, p.description) AGAINST('smartphone' IN BOOLEAN MODE)
  AND p.price BETWEEN 100 AND 500
  AND p.is_active = 1
ORDER BY p.created_at DESC
LIMIT 20;

-- Index cho foreign keys
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

-- Covering index cho thông tin thường xuyên truy vấn
CREATE INDEX idx_orders_detail ON orders(id, customer_id, status, total_amount);

-- Index cho reporting queries
CREATE INDEX idx_orders_status_date ON orders(status, order_date);
CREATE INDEX idx_order_items_order_product ON order_items(order_id, product_id);

-- Materialized view cho báo cáo
CREATE MATERIALIZED VIEW mv_sales_by_category AS
SELECT p.category_id, c.name,
       SUM(oi.quantity) as total_quantity,
       SUM(oi.quantity * oi.price) as total_sales,
       DATE_TRUNC('month', o.order_date) as month
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
  AND o.order_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY p.category_id, c.name
ORDER BY total_sales DESC;