MySQL 數據庫優化核心知識點總結（精簡版）
1. 表複製
CREATE TABLE t3 LIKE t1; INSERT INTO t3 SELECT * FROM t1;
2. 索引 DDL
ALTER TABLE t ADD INDEX idx(c); CREATE UNIQUE INDEX uq ON t(c);
DROP INDEX idx ON t; ALTER TABLE t DROP PRIMARY KEY;
3. 視圖
CREATE VIEW v AS SELECT * FROM t WHERE id>4; DROP VIEW v;
4. 常用函數
- 字符串：CONCAT, LCASE/UCASE, LENGTH, TRIM, REPLACE, SUBSTRING
- 數學：CEILING, FLOOR, MAX/MIN, SQRT, RAND
- 日期：NOW, CURDATE, UNIX_TIMESTAMP, FROM_UNIXTIME, DATEDIFF
5. 預處理/事務/存儲/觸發器
PREPARE s FROM 'SELECT * FROM t WHERE id>?'; SET @i=5; EXECUTE s USING @i;
SET autocommit=0; SAVEPOINT p1; ROLLBACK TO p1;
CREATE PROCEDURE p() BEGIN WHILE @i<10 DO SELECT @i; SET @i=@i+1; END WHILE; END;
CREATE TRIGGER tg BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(NEW.id);
6. auto_increment 重置
TRUNCATE TABLE t; ALTER TABLE t AUTO_INCREMENT=1;
7. GROUP BY WITH ROLLUP
SELECT cname, pname, COUNT(*) FROM demo GROUP BY cname, pname WITH ROLLUP;
8. 外鍵：僅 InnoDB 生效
9. SQL 優化三步走
SHOW GLOBAL STATUS LIKE 'Com_%' 掌握頻率- 慢查詢日誌 +
EXPLAIN 定位
- 看
type、key、rows、Extra
10. 索引鐵律
- 最左前綴、LIKE 右模糊走索引、字符串加引號、IS NULL 可用、OR 兩邊需有索引、評估全掃更快則放棄索引
11. 索引監控
SHOW STATUS LIKE 'Handler_read%'; -- key 高好，rnd_next 高需加索引
12. 立竿見影兩招
CHECK TABLE t; OPTIMIZE TABLE t;
13. 常見 SQL 改寫
- 大批量導入：MyISAM DISABLE/ENABLE KEYS；InnoDB 按主鍵序+關閉 unique_checks/autocommit
- GROUP BY 加
ORDER BY NULL 禁排序
- NOT IN 子查詢 → LEFT JOIN ... IS NULL
14. 架構優化
- 分庫分表/分區降低單表量
- 讀寫分離、中間表預聚合
- 選擇引擎：InnoDB 事務、MyISAM 讀多、HEAP 極速
15. 服務器關鍵參數
sync_binlog=1 安全/性能權衡max_allowed_packet 主從一致 16M+slave-skip-errors 僅非核心從庫read-only 保護從庫
16. 鎖與備份
FLUSH TABLES WITH READ LOCK; -- mysqldump 熱備
UNLOCK TABLES;
17. Root 密碼重置
systemctl stop mysqld; mysqld_safe --skip-grant-tables &;
mysql -uroot -e "UPDATE mysql.user SET authentication_string=PASSWORD('new') WHERE user='root'; FLUSH PRIVILEGES;"
