Đặc thù MySQL và Performance

SHOW ENGINE INNODB STATUS\G
-- Xem "INSERT BUFFER AND ADAPTIVE HASH INDEX"

Clustered index (PK) leaf:          (id=1, name='A', email='a@x.com')
Secondary index leaf (idx_email):   (email='a@x.com', PK=1)
                                              ↓ seek PK=1 về clustered
                                              → (id=1, name='A', email='a@x.com')

CREATE INDEX idx_abc ON orders(user_id, status, created_at);

-- ✅ Dùng được idx_abc: WHERE user_id = 1
-- ✅ Dùng được idx_abc: WHERE user_id = 1 AND status = 'done'
-- ✅ Dùng được idx_abc: WHERE user_id = 1 AND created_at > '2025-01-01'
-- ❌ KHÔNG dùng idx_abc: WHERE status = 'done'                     (thiếu leftmost)
-- ❌ KHÔNG dùng idx_abc: WHERE user_id = 1 OR status = 'done'      (OR phá leftmost)
-- ⚠️ Dùng 1 phần:       WHERE user_id = 1 AND created_at > '...'  (skip cột giữa → chỉ dùng user_id prefix)

-- Query: SELECT total FROM orders WHERE user_id = ? AND created_at > ?
CREATE INDEX idx_cover ON orders(user_id, created_at, total);

-- EXPLAIN hiển thị "Using index" → covering, không đụng vào clustered index
EXPLAIN SELECT total FROM orders WHERE user_id = 1 AND created_at > '2025-01-01';

-- Index trên 10 ký tự đầu của email
CREATE INDEX idx_email_prefix ON users(email(10));

-- Chọn độ dài prefix với cardinality gần bằng full column:
SELECT COUNT(DISTINCT LEFT(email, 4)) / COUNT(*) AS sel_4,
       COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel_8,
       COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
       COUNT(DISTINCT email) / COUNT(*) AS sel_full
FROM users;
-- Chọn độ dài prefix có selectivity gần với sel_full nhất

CREATE FULLTEXT INDEX ft_content ON articles(title, body);

-- Với ngram parser (cần cho tiếng Việt, CJK):
CREATE FULLTEXT INDEX ft_content_ngram ON articles(title, body) WITH PARSER ngram;

[mysqld]
ngram_token_size = 2   # bigram, mặc định
ft_min_word_len = 2    # độ dài từ tối thiểu
innodb_ft_min_token_size = 2

-- Tạo cột geometry
CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    geo POINT NOT NULL SRID 4326,
    SPATIAL INDEX idx_geo (geo)
);

-- Query tìm điểm trong bán kính
SELECT name, ST_Distance_Sphere(geo, ST_GeomFromText('POINT(106.7 10.8)', 4326)) AS dist
FROM locations
WHERE ST_Within(geo, ST_Buffer(ST_GeomFromText('POINT(106.7 10.8)', 4326), 0.1))
ORDER BY dist;

-- Index trên expression — giải bài "non-sargable"
CREATE INDEX idx_lower_email ON users ((LOWER(email)));

-- Query dùng được:
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

-- Index trên JSON path (kết hợp với generated column — xem mục JSON)
CREATE INDEX idx_json_city ON orders ((CAST(data->>'$.city' AS CHAR(50))));

-- Tạo index nhưng optimizer không dùng — test trước khi enable/disable
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;

CREATE INDEX idx_orders_desc ON orders(created_at DESC);
-- Query "ORDER BY created_at DESC" dùng index thẳng, không cần filesort

-- Trên bảng articles
CREATE FULLTEXT INDEX ft_content ON articles(title, body);

-- Hoặc khi CREATE TABLE
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT INDEX ft_articles (title, body)
) ENGINE=InnoDB;

-- Tìm kiếm theo độ liên quan (relevance score)
SELECT id, title,
       MATCH(title, body) AGAINST('MySQL performance tuning') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('MySQL performance tuning' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

-- Phải có 'MySQL', có thể có 'performance', không có 'postgres'
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL performance -postgres' IN BOOLEAN MODE);

-- Toán tử boolean:
-- +word    : phải có
-- -word    : không được có
-- word     : optional (tăng relevance nếu có)
-- "phrase" : exact phrase
-- word*    : prefix wildcard
-- (a b)    : nhóm
-- >word <word : tăng/giảm trọng số
-- ~word    : negative contribution (giảm relevance)

-- MySQL chạy query 2 lần:
-- Lần 1: tìm row match 'MySQL' → lấy ra các từ phổ biến trong những row đó
-- Lần 2: tìm lại với cả 'MySQL' + các từ mở rộng đó
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('MySQL' WITH QUERY EXPANSION);

-- Tạo FULLTEXT index với ngram parser
CREATE FULLTEXT INDEX ft_vi ON articles_vn(title, body) WITH PARSER ngram;

-- ngram_token_size = 2 (bigram) — mặc định
-- Từ "MySQL" → token: "My", "yS", "SQ", "QL"
-- Từ "hiệu năng" → token: "hi", "iệ", "ệu", " n", "nă", "ăn", "ng"

-- Search bình thường:
SELECT * FROM articles_vn
WHERE MATCH(title, body) AGAINST('hiệu năng' IN BOOLEAN MODE);

SET GLOBAL innodb_ft_cache_size = 80000000;   -- cache size cho FTS
OPTIMIZE TABLE articles;                       -- force merge FTS index

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    profile JSON
);

-- Insert
INSERT INTO users (name, profile) VALUES
('Vinh', JSON_OBJECT('age', 25, 'city', 'HCM', 'skills', JSON_ARRAY('SQL', 'Python'))),
('An',   JSON_OBJECT('age', 30, 'city', 'HN',  'skills', JSON_ARRAY('Java', 'Go')));

-- JSON_EXTRACT với cú pháp path $ (hoặc dùng -> shorthand)
SELECT name,
       profile->>'$.city' AS city,
       profile->>'$.age'  AS age,
       profile->'$.skills[0]' AS first_skill
FROM users;

-- JSON_CONTAINS: kiểm tra sự tồn tại
SELECT * FROM users
WHERE JSON_CONTAINS(profile->'$.skills', '"Python"');

-- Chuyển JSON array thành relational rows
SELECT jt.* FROM orders,
JSON_TABLE(
    items,
    '$[*]' COLUMNS (
        product VARCHAR(100) PATH '$.product',
        qty     INT          PATH '$.qty',
        price   DECIMAL(10,2) PATH '$.price'
    )
) AS jt;

-- Tạo generated column từ JSON path
ALTER TABLE users ADD COLUMN city VARCHAR(100)
    GENERATED ALWAYS AS (profile->>'$.city') STORED;

-- Index trên generated column → JSON query dùng index!
CREATE INDEX idx_city ON users(city);

-- Query này giờ dùng được index
SELECT * FROM users WHERE profile->>'$.city' = 'HCM';
-- Hoặc: SELECT * FROM users WHERE city = 'HCM';

-- Định nghĩa Duality View
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW user_dv AS
users {
    _id: id,
    name: name,
    email: email,
    orders @unnest @outer [{
        _id: id,
        amount: amount,
        status: status
    }]
};

-- Đọc như JSON document
SELECT * FROM user_dv WHERE JSON_CONTAINS(data, '{"name":"Vinh"}');

-- Ghi như JSON document (từ 9.7 — Community Edition)
UPDATE user_dv SET data = JSON_SET(data, '$.name', 'Vinh Updated') WHERE data->>'$._id' = '1';

DELIMITER $$

CREATE PROCEDURE GetOrdersByUser(
    IN  p_user_id INT,
    IN  p_status  VARCHAR(20)
)
BEGIN
    SELECT id, total, created_at, status
    FROM orders
    WHERE user_id = p_user_id
      AND (p_status IS NULL OR status = p_status)
    ORDER BY created_at DESC;
END$$

DELIMITER ;

-- Gọi procedure
CALL GetOrdersByUser(1, 'done');
CALL GetOrdersByUser(1, NULL);   -- trả về tất cả status

DELIMITER $$

CREATE PROCEDURE GetOrderStats(
    IN   p_user_id INT,
    OUT  p_total   DECIMAL(18,2),
    OUT  p_count   INT
)
BEGIN
    SELECT SUM(amount), COUNT(*)
    INTO p_total, p_count
    FROM orders
    WHERE user_id = p_user_id;
END$$

DELIMITER ;

-- Gọi và lấy OUT params
CALL GetOrderStats(1, @total, @count);
SELECT @total, @count;

DELIMITER $$

CREATE PROCEDURE ProcessOrders(IN p_user_id INT)
BEGIN
    DECLARE v_order_id INT;
    DECLARE v_amount DECIMAL(18,2);
    DECLARE v_done INT DEFAULT 0;

    -- CURSOR
    DECLARE cur CURSOR FOR
        SELECT id, amount FROM orders WHERE user_id = p_user_id;

    -- HANDLER: khi cursor hết row → set v_done = 1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_order_id, v_amount;

        IF v_done = 1 THEN
            LEAVE read_loop;
        END IF;

        -- Xử lý từng row
        IF v_amount > 1000 THEN
            UPDATE orders SET priority = 'HIGH' WHERE id = v_order_id;
        ELSE
            UPDATE orders SET priority = 'NORMAL' WHERE id = v_order_id;
        END IF;

    END LOOP read_loop;

    CLOSE cur;
END$$

DELIMITER ;

-- IF / ELSEIF / ELSE
IF v_amount > 10000 THEN
    SET v_discount = 20;
ELSEIF v_amount > 5000 THEN
    SET v_discount = 10;
ELSE
    SET v_discount = 0;
END IF;

-- CASE expression
SET v_status = CASE
    WHEN v_days_overdue > 30 THEN 'Bad Debt'
    WHEN v_days_overdue > 0  THEN 'Overdue'
    ELSE 'Current'
END;

-- LOOP / WHILE / REPEAT
WHILE v_counter < 10 DO
    SET v_counter = v_counter + 1;
END WHILE;

REPEAT
    SET v_counter = v_counter - 1;
UNTIL v_counter = 0 END REPEAT;

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
trigger_body;

DELIMITER $$

CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    -- Tự động set created_at nếu NULL
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;

    -- Validate amount > 0
    IF NEW.amount <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'amount must be positive';
    END IF;
END$$

DELIMITER ;

CREATE TABLE orders_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    old_status VARCHAR(20),
    new_status VARCHAR(20),
    changed_at DATETIME DEFAULT NOW(),
    changed_by VARCHAR(100)
);

DELIMITER $$

CREATE TRIGGER audit_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO orders_audit (order_id, old_status, new_status, changed_by)
        VALUES (OLD.id, OLD.status, NEW.status, CURRENT_USER());
    END IF;
END$$

DELIMITER ;

-- Tự động tính total = quantity * unit_price
CREATE TRIGGER calc_total
BEFORE INSERT ON order_items
FOR EACH ROW
SET NEW.total = NEW.quantity * NEW.unit_price;

CREATE TRIGGER validate_stock
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
    DECLARE v_stock INT;
    SELECT stock INTO v_stock FROM products WHERE id = NEW.product_id;
    IF v_stock < NEW.quantity THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient stock';
    END IF;
END;

-- View cơ bản
CREATE VIEW active_users AS
SELECT id, name, email, created_at
FROM users
WHERE status = 'active';

-- Query view như bảng thường
SELECT * FROM active_users WHERE email LIKE '%@gmail.com';

-- CREATE OR REPLACE (sửa view không cần DROP)
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, phone, created_at
FROM users
WHERE status = 'active' AND deleted_at IS NULL;

-- Updatable view (chỉ SELECT đơn giản từ 1 bảng)
CREATE VIEW vip_users AS
SELECT id, name, email, vip_level FROM users WHERE vip_level > 0;

-- Có thể UPDATE qua view
UPDATE vip_users SET vip_level = 3 WHERE id = 1;
-- Dù cột vip_level > 0 sau UPDATE, row vẫn trong view (vip_level = 3 > 0)

-- Có thể DELETE qua view
DELETE FROM vip_users WHERE id = 2;

CREATE VIEW vip_users AS
SELECT id, name, email, vip_level FROM users WHERE vip_level > 0
WITH CHECK OPTION;

-- ✅ OK: vip_level = 3 > 0, vẫn thỏa view condition
UPDATE vip_users SET vip_level = 3 WHERE id = 1;

-- ❌ Lỗi: vip_level = 0, không thỏa view condition → bị từ chối
UPDATE vip_users SET vip_level = 0 WHERE id = 1;
-- Error: CHECK OPTION failed

-- Tạo bảng snapshot
CREATE TABLE mv_daily_stats AS
SELECT DATE(created_at) AS day, COUNT(*) AS order_count, SUM(amount) AS revenue
FROM orders
GROUP BY DATE(created_at);

-- Refresh bằng Event (xem mục Events bên dưới)
CREATE EVENT refresh_mv
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    TRUNCATE mv_daily_stats;
    INSERT INTO mv_daily_stats
    SELECT DATE(created_at), COUNT(*), SUM(amount) FROM orders GROUP BY DATE(created_at);
END;

-- Kiểm tra trạng thái
SHOW VARIABLES LIKE 'event_scheduler';

-- Bật
SET GLOBAL event_scheduler = ON;

-- Config permanent
-- [mysqld]
-- event_scheduler = ON

-- Chạy 1 lần vào thời điểm cụ thể (AT)
CREATE EVENT clean_old_logs
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
    DELETE FROM access_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Chạy định kỳ (EVERY)
CREATE EVENT daily_report
ON SCHEDULE EVERY 1 DAY
    STARTS '2025-01-01 00:00:00'
    ENDS '2025-12-31 23:59:59'
DO
BEGIN
    INSERT INTO daily_stats (day, total_orders, revenue)
    SELECT CURDATE(), COUNT(*), SUM(amount)
    FROM orders
    WHERE DATE(created_at) = CURDATE()
    ON DUPLICATE KEY UPDATE
        total_orders = VALUES(total_orders),
        revenue = VALUES(revenue);
END;

-- Bảo trì: OPTIMIZE mỗi tuần
CREATE EVENT weekly_optimize
ON SCHEDULE EVERY 1 WEEK
DO
    OPTIMIZE TABLE orders, order_items, users;

-- Liệt kê events
SHOW EVENTS;

-- Xem chi tiết
SHOW CREATE EVENT daily_report;

-- Tạm dừng / kích hoạt
ALTER EVENT daily_report DISABLE;
ALTER EVENT daily_report ENABLE;

-- Xóa
DROP EVENT IF EXISTS daily_report;

-- RANGE partitioning theo year
CREATE TABLE sales (
    id BIGINT,
    sold_at DATE,
    amount DECIMAL(18, 2)
)
PARTITION BY RANGE (YEAR(sold_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

-- HASH partitioning
PARTITION BY HASH(user_id) PARTITIONS 8;

-- LIST
PARTITION BY LIST (region) (
    PARTITION p_north VALUES IN ('HN', 'HP'),
    PARTITION p_south VALUES IN ('HCM', 'CT')
);

Source                      Replica
  ↓ write binlog              ↓ read binlog
  COMMIT (không chờ)          apply

# Source
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW              # ROW > STATEMENT > MIXED

-- Source: tạo user replication
CREATE USER 'repl'@'%' IDENTIFIED BY 'replpwd';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- Replica: setup
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='source.example.com',
    SOURCE_USER='repl',
    SOURCE_PASSWORD='replpwd',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=0;
START REPLICA;
SHOW REPLICA STATUS\G

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1            # > 1 giây
log_queries_not_using_indexes = 1

# Phân tích
mysqldumpslow -s t /var/log/mysql/slow.log | head -20

# Hoặc dùng pt-query-digest (Percona Toolkit)
pt-query-digest slow.log

-- Top query theo time
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

-- Sys schema — view trên performance_schema
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

-- Index không dùng (sau khi DB chạy 1 thời gian)
SELECT * FROM sys.schema_unused_indexes;

# Single DB
mysqldump -u root -p --single-transaction --routines --triggers learnmysql > dump.sql

# All DBs
mysqldump -u root -p --all-databases --single-transaction > all.sql

# Restore
mysql -u root -p learnmysql < dump.sql

# Từ 9.7, có thể bỏ qua các setting manual này nếu chạy trong container:
# innodb_buffer_pool_instances sẽ auto-detected từ cgroup CPU limit

[mysqld]
innodb_dedicated_server = ON
# Từ 9.7, tự động detect cả redo log capacity phù hợp