MySQL 數據庫優化全攻略：索引、SQL、架構、參數調優

MySQL 數據庫優化全攻略：索引、SQL、架構、參數調優

一、基礎對象操作

表結構與數據複製

CREATE TABLE t3 LIKE t1;
INSERT INTO t3 SELECT * FROM t1;

索引管理

-- 創建
ALTER TABLE t ADD INDEX idx_name (col);
CREATE UNIQUE INDEX uq_name ON t(col);
-- 刪除
DROP INDEX idx_name ON t;
ALTER TABLE t DROP PRIMARY KEY;

視圖操作

CREATE VIEW v_t1 AS SELECT * FROM t1 WHERE id BETWEEN 5 AND 10;
DROP VIEW v_t1;

二、常用內建函數速查

三、預處理語句

PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE id > ?';
SET @i = 5;
EXECUTE stmt1 USING @i;
DEALLOCATE PREPARE stmt1;

四、事務與存儲過程

-- 事務保存點
SET autocommit=0;
DELETE FROM t1 WHERE id=11;
SAVEPOINT p1;
DELETE FROM t1 WHERE id=10;
ROLLBACK TO p1;

-- 存儲過程
CREATE PROCEDURE p1()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 10 DO
        SELECT i; SET i = i+1;
    END WHILE;
END;
CALL p1();

五、觸發器範例

CREATE TRIGGER tg1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN INSERT INTO t2(id) VALUES(NEW.id); END;

六、auto_increment 重置

TRUNCATE TABLE t;          -- 歸 1
ALTER TABLE t AUTO_INCREMENT = 1;

七、GROUP BY WITH ROLLUP 多維匯總

SELECT cname, pname, COUNT(*) FROM demo GROUP BY cname, pname WITH ROLLUP;

輸出含各層級小計及總計行（NULL 代表該維度匯總）。

八、外鍵注意事項

• 僅 InnoDB 支持外鍵，MyISAM 創建雖不報錯但不生效
• ON DELETE CASCADE / UPDATE CASCADE 實現級聯

九、SQL 優化標準流程

1. 掌握執行頻率：SHOW GLOBAL STATUS LIKE 'Com_%'
2. 定位慢 SQL：EXPLAIN SELECT ... 或開啟慢查詢日誌
3. 分析執行計劃：重點關注 type（system > const > eq_ref > ref > range > index > ALL）、key、rows、Extra

十、索引使用鐵律

• 最左前綴原則：聯合索引必須從最左列開始匹配
• LIKE 'abc%' 走索引，LIKE '%abc' 全掃
• 字符串列比較必須加引號，否則隱式轉換導致索引失效
• IS NULL 可走索引，OR 兩側必須都有索引才會使用
• MySQL 評估全表掃描更快時會放棄索引

十一、監控索引健康度

SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_key 高 = 索引命中好
-- Handler_read_rnd_next 高 = 需補索引

十二、兩招立竿見影優化

-- 1. 分析表
CHECK TABLE tbl_name;
-- 2. 優化表（碎片整理，僅 MyISAM/BDB/InnoDB 有效）
OPTIMIZE TABLE tbl_name;

十三、常見 SQL 改寫技巧

大批量導入

-- MyISAM
ALTER TABLE t DISABLE KEYS;
LOAD DATA INFILE 'file' INTO TABLE t;
ALTER TABLE t ENABLE KEYS;

-- InnoDB：按主鍵順序導入、關閉 unique_checks/autocommit

優化 GROUP BY

SELECT id, SUM(m) FROM t GROUP BY id ORDER BY NULL;  -- 禁止文件排序

子查詢轉 JOIN

-- 原：WHERE id NOT IN (SELECT id FROM t2)
-- 優化：
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL;

十四、架構層面優化

• 分庫分表 / 分區降低單表行數
• 讀寫分離：一主多從，主寫從讀
• 中間表/視圖預聚合
• 選擇合適存儲引擎（InnoDB 事務、MyISAM 讀多寫少、HEAP 極速查詢）

十五、服務器參數關鍵調優

• sync_binlog=1 保證數據不丟，性能折半；高併發可設 0 或 N
• max_allowed_packet 主從需一致，建議 16M+
• slave-skip-errors 僅用於非核心只讀從庫
• read-only 限制從庫寫入

十六、鎖機制與備份

-- 全局讀鎖（適合 mysqldump 熱備）
FLUSH TABLES WITH READ LOCK;
-- 釋放
UNLOCK TABLES;

十七、root 密碼重置

systemctl stop mysqld
mysqld_safe --skip-grant-tables &
mysql -uroot
UPDATE mysql.user SET authentication_string=PASSWORD('new') WHERE user='root';
FLUSH PRIVILEGES;