萬盛學電腦網

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

MySQL 系統學習路徑:從命令行到存儲引擎、分頁優化、密碼重置的完整進階指南

MySQL 學習五大專題模塊

專題一:MySQL 命令行核心操作

  • 連接: mysql -h host -u user -p (密碼提示輸入,避免明文)
  • 庫表管理: CREATE/DROP/USE DATABASE、CREATE/ALTER/DROP TABLE、SHOW CREATE TABLE
  • 數據操作: INSERT/SELECT/UPDATE/DELETE/TRUNCATE
  • 備份恢復: mysqldump 導出、SOURCE / mysql < file 導入
  • 系統信息: SHOW VARIABLES/STATUS/PROCESSLIST/ENGINE INNODB STATUS

專題二:忘記 root 密碼重置 (Windows / Linux 通用)

方法:跳過權限表啟動 → 修改密碼 → 正常重啟


# 1. 停止 MySQL
systemctl stop mysqld          # Linux
net stop mysql                 # Windows

# 2. 跳過授權表啟動
mysqld --skip-grant-tables --skip-networking &  # 後台運行,禁止遠程連接

# 3. 免密登錄修改密碼
mysql -u root
FLUSH PRIVILEGES;  -- 關鍵:skip-grant-tables 下必須先執行
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass123!';
-- 或 5.7-
UPDATE mysql.user SET authentication_string=PASSWORD('NewPass') WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

# 4. 正常重啟
killall mysqld  # 或關閉後台進程
systemctl start mysqld

專題三:分頁優化實戰 (百萬級表)

痛點: LIMIT 1000000, 20 掃描 100 萬行,超時。
方案演進:

  1. 延遲關聯: 子查詢只取 ID (覆蓋索引) → 主鍵回表
  2. 游標分頁: WHERE id > last_id ORDER BY id LIMIT 20 (無 OFFSET,恆定快)
  3. 覆蓋索引: 僅查索引列避免回表
  4. 緩存熱頁: 首頁/熱門頁緩存 HTML/JSON

專題四:存儲引擎選型指南

引擎事務鎖粒度MVCC全文索引適用場景
InnoDB (默認 5.5+)✅ ACID行鎖✅5.6+**核心業務、高並發、數據一致性要求高**
MyISAM❌表鎖❌✅ (5.6+)只讀/插入為主、全文搜索、非關鍵日志
Memory❌表鎖❌❌臨時表、查找表、數據可丟失
Archive❌行鎖❌❌歸檔日志、僅插入/全掃描、高壓縮
CSV❌表鎖❌❌外部工具交換數據

核心建議: 99% 核心業務表**只用 InnoDB**,利用行鎖+MVCC+崩潰恢復。

專題五:創建全文索引 (FULLTEXT)


-- InnoDB 5.6+ 支持
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB;

-- 自然語言模式 (默認,相關性排序)
SELECT *, MATCH(title, content) AGAINST ('MySQL 優化' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST ('MySQL 優化' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

-- 布爾模式 (精確控制)
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST ('+MySQL -Oracle' IN BOOLEAN MODE);
-- +必含 -必不含 *通配符 ~降低相關性

中文分詞: MySQL 內置簡單分詞 (n-gram/MeCab 插件),生產建議接入 Elasticsearch/OpenSearch 專業全文搜索引擎。

學習進階路線圖 (同 181168 擴展版)

  1. 基礎 (1-2 週): SQL、數據類型、索引 B+樹、事務 MVCC、EXPLAIN
  2. 調優 (3-4 週): Schema 設計、索引優化、SQL 改寫、參數調優、監控工具鏈
  3. 高可用 (2-3 週): 複制拓撲、GTID、半同步、故障切換 (MHA/Orchestrator)、讀寫分離 (ProxySQL)
  4. 內核 (持續): InnoDB 架構、Buffer Pool、Redo/Undo、鎖系統、文件體系
  5. 運維工程化 (持續): 變更管理、容量規劃、安全合規、雲原生、故障複盤
copyright © 萬盛學電腦網 all rights reserved