萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 10 大隱藏技巧:XML 輸出、快速重建索引、壓縮表、CSV 匯出與條件函數實戰

技巧一:XML 格式輸出查詢結果

mysql --xml -e "SELECT * FROM test.stories;"
# 輸出結構化 XML,便於程式解析或系統整合

技巧二:快速重建全文索引

修改全文搜尋參數後,無需 DROP + CREATE INDEX,直接:

REPAIR TABLE content QUICK;

秒級完成,適用大表維護窗口極短場景。

技巧三:壓縮唯讀 MyISAM 表

myisampack movies.MYI
# 壓縮率可達 40%+,節省磁碟空間,查詢速度不降反升

限制:壓縮後表變為唯讀,需解壓才能寫入。

技巧四:SQL 內建條件邏輯 (IF / CASE)

SELECT IF(priv=1, 'admin', 'guest') AS usertype FROM privs WHERE username='joe';
-- 等同 CASE WHEN priv=1 THEN 'admin' ELSE 'guest' END

避免應用層二次判斷,減少網路往返。

技巧五:CSV 格式匯出供 Excel/BI 直接開啟

mysqldump -T /tmp/export --fields-terminated-by=',' db_name table_name
# 產生 .txt (資料) + .sql (結構),直接拖入 Excel

技巧六:多表關聯查詢善用 JOIN 而非子查詢

-- 低效子查詢
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount>100);
-- 高效 JOIN
SELECT u.* FROM users u JOIN orders o ON u.id=o.user_id WHERE o.amount>100;

技巧七:LIMIT 分頁優化 (大偏移量問題)

-- 避免 LIMIT 100000, 20 掃描 10 萬行
-- 改用延遲關聯 / 書籤分頁
SELECT * FROM t WHERE id > 100000 ORDER BY id LIMIT 20;

技巧八:EXPLAIN 解讀執行計畫關鍵欄位

欄位重點觀察
typesystem > const > eq_ref > ref > range > index > ALL (越前越好)
key實際使用的索引,NULL 代表全表掃描
rows預估掃描行數,越小越好
ExtraUsing index (覆蓋索引)、Using where、Using temporary、Using filesort

技巧九:覆蓋索引避免回表

-- 查詢只需 id, name,建立複合索引
ALTER TABLE users ADD INDEX idx_name_email (name, email);
SELECT id, name FROM users WHERE name='John'; -- 僅走索引,不回表

技巧十:資料類型選擇原則

  • 整數優先 TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT,足夠就好
  • 定長字串用 CHAR,變長用 VARCHAR,避免 TEXT/BLOB 除非必要
  • 金額用 DECIMAL(10,2),絕不使用 FLOAT/DOUBLE
  • 時間戳用 TIMESTAMP (4 bytes, 1970-2038) 或 DATETIME (8 bytes, 範圍大)
copyright © 萬盛學電腦網 all rights reserved