萬盛學電腦網

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

MySQL 入門核心知識圖譜:分頁、密碼、遠程、複製表、遺忘密碼五大實戰

MySQL 作為 Web 應用最主流的關聯式資料庫,掌握核心操作技能是後端開發必修課。本文整理五大高頻實戰場景,助你快速建立 MySQL 操作肌肉記憶。

場景一:大數據量分頁優化

傳統 LIMIT offset, size 在深分頁會全表掃描。優化核心:延遲關聯——先在索引定位主鍵,再回表取欄位。

-- 優化前:掃描 10 萬行
SELECT * FROM t ORDER BY id LIMIT 100000, 20;

-- 優化後:僅掃描索引
SELECT * FROM t 
WHERE id > (SELECT id FROM t ORDER BY id LIMIT 100000, 1) 
ORDER BY id LIMIT 20;

關鍵前提:主鍵有序、無大量刪除間隙。非主鍵排序需建立覆蓋索引。

場景二:MySQL 5.7/8.0 修改 root 密碼

版本差異導致語法不同,請對號入座:

MySQL 5.7

UPDATE mysql.user 
SET authentication_string = PASSWORD('新密碼') 
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

MySQL 8.0+

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密碼';
-- 或支援密碼驗證插件
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密碼';

修改後務必執行 FLUSH PRIVILEGES; 生效。

場景三:解決遠程連線「Host not allowed」

錯誤碼 1130 表示帳號無遠程連線權限,三步解決:

  1. 登入本機:mysql -u root -p
  2. 授權遠程存取:
-- 允許所有 IP(生產環境請限制具體 IP)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密碼' WITH GRANT OPTION;

-- MySQL 8.0 需先建用戶
CREATE USER 'root'@'%' IDENTIFIED BY '密碼';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
  1. 刷新權限並檢查防火牆/雲端安全組開放 3306 埠

場景四:完整複製表結構與資料

CREATE TABLE ... SELECT 僅複製資料與部份結構,索引、預設值、自增屬性會遺失。標準做法:

-- 1. 完整複製結構(含索引、約束)
CREATE TABLE new_tbl LIKE old_tbl;

-- 2. 複製資料
INSERT INTO new_tbl SELECT * FROM old_tbl;

僅複製結構不含資料:CREATE TABLE new_tbl LIKE old_tbl;
僅複製部份欄位資料:INSERT INTO new_tbl (col1, col2) SELECT col1, col2 FROM old_tbl;

場景五:遺忘 root 密碼緊急重置

通用流程(Linux/Windows):

  1. 停止服務 → 啟用 skip-grant-tables → 重啟
  2. 免密登入:mysql -u root
  3. 更新密碼(參考場景二版本差異)
  4. 移除 skip-grant-tables → 正常重啟

忘記密碼重置流程圖

進階學習路線圖

階段

核心主題建議資源
入門SQL 基礎、資料型別、基本增刪改查《MySQL 必知必會》
進階索引原理、執行計畫、鎖機制、事務隔離級別《高效能 MySQL》第 3 版
架構主從複制、讀寫分離、分庫分表、MHA/Orchestrator官方文檔 + 部落格實戰
運維備份恢復、監控告警、參數調優、故障排查Percona Toolkit、Prometheus+Grafana

五大場景覆蓋開發 80% 的高頻操作,建議在測試環境逐一實操,形成肌肉記憶。

copyright © 萬盛學電腦網 all rights reserved