MySQL實戰詳解

一、存儲引擎

MySQL的存儲引擎決定了數據如何被存儲，不同的存儲引擎適用於不同類型的應用場景。MySQL支持多種存儲引擎，包括InnoDB、MyISAM、MEMORY等。

1、InnoDB

InnoDB是MySQL的默認存儲引擎，支持ACID事務、行鎖定和外鍵等特性，適用於高並發寫入的應用。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2、MyISAM

MyISAM是MySQL的舊有存儲引擎，不支持ACID事務和行鎖定，並發讀寫性能較好，適用於讀頻繁的應用。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

3、MEMORY

MEMORY也稱為HEAP，將數據存儲在內存中而不是硬盤中，訪問速度非常快，但是內存有限制，數據容易丟失。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

二、索引

索引可以加速數據的查找、過濾和排序，提高查詢性能。MySQL支持多種類型的索引，包括B-tree、HASH和FULLTEXT等。

1、B-tree索引

B-tree是一種常見的索引類型，適用於範圍查找和排序。MySQL支持普通索引、唯一索引和主鍵索引。

CREATE INDEX idx_name ON user(name);
CREATE UNIQUE INDEX idx_id ON user(id);
ALTER TABLE user ADD PRIMARY KEY (id);

2、HASH索引

HASH索引適用於等值查詢，它使用哈希函數將數據分散到不同的桶中，哈希衝突的數據存儲在同一個桶內。

CREATE INDEX idx_name ON user(name) USING HASH;

3、FULLTEXT索引

FULLTEXT索引適用於全文檢索，它可以在文本中查找關鍵詞。

CREATE FULLTEXT INDEX idx_name ON user(name);

三、優化

MySQL的優化可以提高數據庫性能，包括優化查詢、優化表結構、優化服務器設置等。

1、優化查詢

優化查詢可以通過索引、優化SQL語句、使用緩存等方式提高查詢性能。

SELECT * FROM user WHERE age >= 18 ORDER BY id DESC LIMIT 10;

2、優化表結構

優化表結構可以通過限制字段長度、避免使用NULL值、使用ENUM代替VARCHAR等方式減小表的體積，提高查詢性能。

CREATE TABLE `user` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用戶ID',
  `name` VARCHAR(50) NOT NULL COMMENT '用戶名',
  `gender` ENUM('male', 'female') NOT NULL COMMENT '性別',
  `age` TINYINT(3) UNSIGNED NOT NULL COMMENT '年齡',
  `city` VARCHAR(50) NOT NULL COMMENT '城市',
  PRIMARY KEY (`id`),
  INDEX `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';

3、優化服務器設置

優化服務器設置可以通過修改緩存大小、優化innodb_buffer_pool_size、調整max_connections等方式提高服務器性能。

innodb_buffer_pool_size=4G
max_connections=1000

四、數據備份與恢復

數據備份與恢復是數據庫管理的重要方面，可以在數據意外丟失時快速恢複數據。

1、物理備份

物理備份可以將整個數據庫備份到一個文件中，包括數據文件、二進制日誌文件、配置文件等。

mysqldump -uroot -p dbname > backup.sql

2、邏輯備份

邏輯備份可以將數據庫中的數據導出為SQL語句，適用於跨數據庫備份和導入數據。

mysqldump -uroot -p dbname > backup.sql

3、數據恢復

數據恢復可以通過source命令、MySQL Workbench、命令行等方式恢複數據。

mysql -uroot -p dbname < backup.sql