MySQL 介绍

MySQL 是世界上最流行的开源关系型数据库管理系统之一，被广泛应用于各种规模的应用程序中。本文将从基础概念到高级特性，全面介绍 MySQL 的核心知识。

MySQL 简介

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 以其可靠性、性能和易用性而闻名，被广泛应用于 Web 应用、企业应用和嵌入式应用等领域。

MySQL 的主要特点

• 开源免费：MySQL 社区版是完全免费的，可以自由使用和修改

• 性能卓越：针对不同的应用场景进行了优化，提供高性能的数据处理能力

• 可靠性高：经过多年的发展和广泛的应用，MySQL 已经成为一个非常稳定和可靠的数据库系统

• 跨平台：支持多种操作系统，包括 Windows、Linux、macOS 等

• 支持多种编程语言：提供了丰富的 API，支持 Java、PHP、Python、C++ 等多种编程语言

MySQL 安装与配置

Windows 安装

1. 从 MySQL 官网下载安装包

2. 运行安装程序，按照向导进行安装

3. 配置 MySQL 服务和初始密码

4. 验证安装是否成功

mysql -u root -p

Linux 安装

# CentOS/RHEL

sudo yum install mysql-server

sudo systemctl start mysqld

sudo systemctl enable mysqld

# Ubuntu/Debian

sudo apt-get install mysql-server

sudo systemctl start mysql

sudo systemctl enable mysql

基本配置

MySQL 的主要配置文件是 my.cnf（Linux）或 my.ini（Windows），常见的配置项包括：

[mysqld]

# 基本设置

port = 3306

socket = /tmp/mysql.sock

datadir = /var/lib/mysql

# 字符集设置

character-set-server = utf8mb4

collation-server = utf8mb4_general_ci

# 连接设置

max_connections = 1000

max_connect_errors = 10000

# 缓冲区设置

key_buffer_size = 256M

sort_buffer_size = 2M

read_buffer_size = 2M

# InnoDB 设置

innodb_buffer_pool_size = 1G

innodb_log_file_size = 256M

innodb_flush_log_at_trx_commit = 1

MySQL 基础架构

MySQL 的基础架构可以分为三层：

1. 连接层：负责客户端连接、授权认证

2. 服务层：包括查询缓存、解析器、优化器、执行器等

3. 存储引擎层：负责数据的存储和提取

查询执行流程

1. 客户端发送 SQL 语句到服务器

2. 服务器检查查询缓存，如果命中则直接返回结果

3. 服务器进行 SQL 解析，生成解析树

4. 预处理器进行语法检查和字段检查

5. 查询优化器生成执行计划

6. 执行器根据执行计划调用存储引擎接口

7. 存储引擎执行操作并返回结果

SQL 语句基础

数据定义语言（DDL）

-- 创建数据库

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 使用数据库

USE mydb;

-- 创建表

CREATE TABLE users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    username VARCHAR(50) NOT NULL UNIQUE,

    password VARCHAR(100) NOT NULL,

    email VARCHAR(100) UNIQUE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

-- 修改表

ALTER TABLE users ADD COLUMN status TINYINT DEFAULT 1;

ALTER TABLE users MODIFY COLUMN username VARCHAR(100);

ALTER TABLE users DROP COLUMN status;

-- 删除表

DROP TABLE users;

数据操作语言（DML）

-- 插入数据

INSERT INTO users (username, password, email) VALUES ('admin', 'password123', 'admin@example.com');

-- 更新数据

UPDATE users SET password = 'newpassword' WHERE username = 'admin';

-- 删除数据

DELETE FROM users WHERE id = 1;

-- 查询数据

SELECT * FROM users;

SELECT id, username, email FROM users WHERE id > 10 ORDER BY created_at DESC LIMIT 10;

数据查询语言（DQL）

-- 基本查询

SELECT * FROM users WHERE status = 1;

-- 连接查询

SELECT u.username, o.order_id, o.amount

FROM users u

JOIN orders o ON u.id = o.user_id

WHERE o.amount > 100;

-- 子查询

SELECT username FROM users

WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 分组查询

SELECT user_id, COUNT(*) as order_count, SUM(amount) as total_amount

FROM orders

GROUP BY user_id

HAVING COUNT(*) > 5;

数据控制语言（DCL）

-- 创建用户

CREATE USER 'webuser'@'localhost' IDENTIFIED BY 'password';

-- 授权

GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'webuser'@'localhost';

-- 撤销权限

REVOKE UPDATE ON mydb.* FROM 'webuser'@'localhost';

-- 删除用户

DROP USER 'webuser'@'localhost';

MySQL 数据类型

整数类型

| 类型 | 存储空间 | 最小值（有符号） | 最大值（有符号） |

|------|----------|------------------|------------------|

| TINYINT | 1 字节 | -128 | 127 |

| SMALLINT | 2 字节 | -32768 | 32767 |

| MEDIUMINT | 3 字节 | -8388608 | 8388607 |

| INT | 4 字节 | -2147483648 | 2147483647 |

| BIGINT | 8 字节 | -9223372036854775808 | 9223372036854775807 |

浮点数类型

• FLOAT：单精度浮点数，4 字节

• DOUBLE：双精度浮点数，8 字节

• DECIMAL(M,D)：定点数，M 表示总位数，D 表示小数位数

字符串类型

• CHAR(N)：固定长度字符串，最多 255 个字符

• VARCHAR(N)：可变长度字符串，最多 65535 个字符

• TEXT：文本类型，最大长度为 65535 个字符

• MEDIUMTEXT：中等长度文本，最大长度为 16777215 个字符

• LONGTEXT：长文本，最大长度为 4294967295 个字符

日期和时间类型

• DATE：日期，格式为 'YYYY-MM-DD'

• TIME：时间，格式为 'HH:MM:SS'

• DATETIME：日期和时间，格式为 'YYYY-MM-DD HH:MM:SS'

• TIMESTAMP：时间戳，范围从 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC

• YEAR：年份，范围从 1901 到 2155

二进制类型

• BINARY(N)：固定长度二进制字符串

• VARBINARY(N)：可变长度二进制字符串

• BLOB：二进制大对象，最大长度为 65535 字节

• MEDIUMBLOB：中等长度二进制大对象，最大长度为 16777215 字节

• LONGBLOB：长二进制大对象，最大长度为 4294967295 字节

MySQL 索引

索引是提高数据库查询性能的重要手段，MySQL 支持多种类型的索引。

索引类型

• 普通索引：最基本的索引类型，没有任何限制

• 唯一索引：索引列的值必须唯一，但允许有空值

• 主键索引：特殊的唯一索引，不允许有空值

• 组合索引：多个列组合在一起创建的索引

• 全文索引：用于全文搜索

• 空间索引：用于地理空间数据类型

创建索引

-- 创建表时指定索引

CREATE TABLE users (

    id INT AUTO_INCREMENT,

    username VARCHAR(50) NOT NULL,

    email VARCHAR(100),

    PRIMARY KEY (id),

    UNIQUE INDEX idx_username (username),

    INDEX idx_email (email)

);

-- 在已有表上创建索引

CREATE INDEX idx_created_at ON users (created_at);

CREATE UNIQUE INDEX idx_email ON users (email);

-- 创建组合索引

CREATE INDEX idx_name_email ON users (username, email);

-- 创建全文索引

CREATE FULLTEXT INDEX idx_content ON articles (content);

索引的使用原则

1. 选择合适的列建立索引：经常用于查询、排序、分组的列

2. 索引列的基数：基数越大（列中不同值的数量越多），索引的效果越好

3. 前缀索引：对于长字符串，可以只索引前几个字符

4. 覆盖索引：尽量使用能够覆盖查询的索引

5. 避免过多索引：索引会占用空间并影响写入性能

6. 组合索引的顺序：遵循最左前缀原则

MySQL 事务

事务是数据库操作的基本单位，具有 ACID 特性。

ACID 特性

• 原子性（Atomicity）：事务是不可分割的工作单位，要么全部执行，要么全部不执行

• 一致性（Consistency）：事务执行前后，数据库从一个一致性状态变到另一个一致性状态

• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行

• 持久性（Durability）：事务一旦提交，其结果就是永久性的

事务控制语句

-- 开始事务

START TRANSACTION;

-- 执行操作

INSERT INTO accounts (user_id, balance) VALUES (1, 1000);

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

INSERT INTO transactions (user_id, amount, type) VALUES (1, 100, 'withdraw');

-- 提交事务

COMMIT;

-- 回滚事务

ROLLBACK;

-- 设置保存点

SAVEPOINT my_savepoint;

-- 回滚到保存点

ROLLBACK TO SAVEPOINT my_savepoint;

事务隔离级别

MySQL 支持四种事务隔离级别，用于解决并发事务可能出现的问题（脏读、不可重复读、幻读）。

| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能影响 |

|----------|------|------------|------|----------|

| READ UNCOMMITTED | 可能 | 可能 | 可能 | 最小 |

| READ COMMITTED | 不可能 | 可能 | 可能 | 较小 |

| REPEATABLE READ（默认） | 不可能 | 不可能 | 可能 | 较大 |

| SERIALIZABLE | 不可能 | 不可能 | 不可能 | 最大 |

-- 查看当前隔离级别

SELECT @@transaction_isolation;

-- 设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL 锁机制

锁是数据库并发控制的基本手段，MySQL 中有多种类型的锁。

锁的类型

• 共享锁（S 锁）：允许多个事务同时读取同一资源，但不允许写入

• 排他锁（X 锁）：只允许一个事务访问资源，阻止其他事务读取和写入

• 意向锁：表示事务想要在表中的某些行上加共享锁或排他锁

• 记录锁：锁定索引记录

• 间隙锁：锁定索引记录之间的间隙

• 临键锁：记录锁和间隙锁的组合，锁定索引记录及其前面的间隙

加锁语句

-- 共享锁

SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁

SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 表级锁

LOCK TABLES users READ;

LOCK TABLES users WRITE;

UNLOCK TABLES;

MySQL 存储引擎

MySQL 的一个重要特性是支持多种存储引擎，不同的存储引擎具有不同的特性和适用场景。

常见存储引擎

InnoDB

InnoDB 是 MySQL 5.5 之后的默认存储引擎，具有以下特点：

• 支持事务，符合 ACID 特性

• 支持行级锁，提高并发性能

• 支持外键约束

• 支持崩溃恢复

• 使用聚集索引存储数据

适用场景：需要事务支持、高并发、外键约束的应用

MyISAM

MyISAM 是 MySQL 5.5 之前的默认存储引擎，具有以下特点：

• 不支持事务

• 支持表级锁

• 不支持外键

• 拥有较高的插入和查询速度

• 支持全文索引

适用场景：读密集型应用、不需要事务支持的应用

Memory

Memory 存储引擎将数据存储在内存中，具有以下特点：

• 数据存储在内存中，速度极快

• 重启后数据会丢失

• 支持哈希索引

适用场景：临时表、缓存、需要快速访问的小型数据集

Archive

Archive 存储引擎适用于存储和检索大量很少被访问的历史数据，具有以下特点：

• 只支持 INSERT 和 SELECT 操作

• 使用行级锁

• 高度压缩数据

适用场景：日志和数据归档

查看和设置存储引擎

-- 查看支持的存储引擎

SHOW ENGINES;

-- 查看表的存储引擎

SHOW TABLE STATUS WHERE Name = 'users';

-- 创建表时指定存储引擎

CREATE TABLE users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    username VARCHAR(50)

) ENGINE = InnoDB;

-- 修改表的存储引擎

ALTER TABLE users ENGINE = MyISAM;

MySQL 优化策略

查询优化

1. 使用 EXPLAIN 分析查询：了解 MySQL 如何执行查询

   EXPLAIN SELECT * FROM users WHERE username = 'admin';

2. 索引优化：

• 为经常用于查询条件的列创建索引

• 利用覆盖索引减少回表操作

• 避免在索引列上使用函数或表达式

3. SQL 语句优化：

• 只查询需要的列，避免 SELECT *

• 使用 LIMIT 限制结果集大小

• 使用连接（JOIN）代替子查询

• 使用 EXISTS 代替 IN

• 避免使用 OR 条件，可以使用 UNION 代替

4. 分页查询优化：

  -- 优化前

   SELECT * FROM users ORDER BY id LIMIT 10000, 10;

   

   -- 优化后

   SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;

数据库设计优化

1. 合理的表设计：

• 遵循数据库范式，减少数据冗余

• 适当反范式化，提高查询性能

• 使用合适的数据类型

• 避免过多的列

2. 分区表：对大表进行分区，提高查询和管理效率

CREATE TABLE sales (

       id INT,

       sale_date DATE,

       amount DECIMAL(10,2)

   )

   PARTITION BY RANGE (YEAR(sale_date)) (

       PARTITION p0 VALUES LESS THAN (2020),

       PARTITION p1 VALUES LESS THAN (2021),

       PARTITION p2 VALUES LESS THAN (2022),

       PARTITION p3 VALUES LESS THAN MAXVALUE

   );

3. 垂直拆分：将大表按列拆分成多个表

4. 水平拆分：将大表按行拆分成多个表

服务器优化

1. 配置优化：

• 调整缓冲池大小（innodb_buffer_pool_size）

• 优化日志写入策略（innodb_flush_log_at_trx_commit）

• 调整连接数（max_connections）

• 优化查询缓存（query_cache_size）

2. 硬件优化：

• 使用 SSD 存储数据

• 增加内存

• 使用多核 CPU

• 优化网络配置

MySQL 备份与恢复

备份方法

1. 逻辑备份：

• mysqldump：生成 SQL 语句的备份

     mysqldump -u root -p --all-databases > backup.sql

     mysqldump -u root -p --databases db1 db2 > backup.sql

     mysqldump -u root -p db1 table1 table2 > backup.sql

• mysqlpump：增强版的 mysqldump，支持并行备份

     mysqlpump -u root -p --all-databases > backup.sql

2. 物理备份：

• 直接复制数据文件

• 使用 MySQL Enterprise Backup

• 使用 Percona XtraBackup

     xtrabackup --backup --target-dir=/path/to/backup

3. 增量备份：

• 使用二进制日志（binlog）

• 使用 Percona XtraBackup 的增量备份功能

     xtrabackup --backup --target-dir=/path/to/full-backup

     xtrabackup --backup --target-dir=/path/to/inc1 --incremental-basedir=/path/to/full-backup

恢复方法

1. 逻辑备份恢复：

   mysql -u root -p < backup.sql

2. 物理备份恢复：

• 停止 MySQL 服务

• 替换数据文件

• 启动 MySQL 服务

3. 使用二进制日志恢复：

  mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

4. 使用 Percona XtraBackup 恢复：

   xtrabackup --prepare --target-dir=/path/to/backup

   xtrabackup --copy-back --target-dir=/path/to/backup

MySQL 高可用方案

主从复制

主从复制是 MySQL 最基本的高可用方案，一个主服务器（Master）可以有多个从服务器（Slave）。

配置主服务器

# my.cnf

[mysqld]

server-id = 1

log-bin = mysql-bin

binlog-format = ROW

配置从服务器

# my.cnf

[mysqld]

server-id = 2

relay-log = relay-bin

设置复制

-- 在主服务器上创建复制用户

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';

GRANT REPLICATION SLAVE ON . TO 'repl'@'%';

-- 获取主服务器状态

SHOW MASTER STATUS;

-- 在从服务器上配置复制

CHANGE MASTER TO

    MASTER_HOST='master_host',

    MASTER_USER='repl',

    MASTER_PASSWORD='password',

    MASTER_LOG_FILE='mysql-bin.000001',

    MASTER_LOG_POS=123;

-- 启动复制

START SLAVE;

-- 查看复制状态

SHOW SLAVE STATUS\G

组复制（Group Replication）

组复制是 MySQL 5.7 引入的一种新的复制方案，提供了更高的一致性和可用性。

# my.cnf

[mysqld]

server-id = 1

gtid_mode = ON

enforce_gtid_consistency = ON

binlog_checksum = NONE

plugin_load = 'group_replication.so'

group_replication_group_name = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'

group_replication_start_on_boot = OFF

group_replication_local_address = 'server1:33061'

group_replication_group_seeds = 'server1:33061,server2:33061,server3:33061'

MySQL InnoDB Cluster

MySQL InnoDB Cluster 是 MySQL 8.0 提供的一个完整的高可用解决方案，包括：

• MySQL Group Replication：提供数据复制

• MySQL Router：提供自动路由

• MySQL Shell：提供管理工具

# 使用 MySQL Shell 创建 InnoDB Cluster

mysqlsh

\connect root@server1

dba.createCluster('myCluster')

cluster = dba.getCluster()

cluster.addInstance('root@server2')

cluster.addInstance('root@server3')

MySQL 8.0 新特性

MySQL 8.0 引入了许多新特性，包括：

1. 窗口函数：

  SELECT 

       name, 

       department, 

       salary,

       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank

   FROM employees;

2. 公共表表达式（CTE）：

WITH cte AS (

       SELECT user_id, COUNT(*) as order_count

       FROM orders

       GROUP BY user_id

   )

   SELECT u.username, c.order_count

   FROM users u

   JOIN cte c ON u.id = c.user_id

   WHERE c.order_count > 10;

3. JSON 增强：

   SELECT JSON_TABLE(

       '{"employees": [{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]}',

       '$.employees[*]' COLUMNS(

           name VARCHAR(50) PATH '$.name',

           age INT PATH '$.age'

       )

   ) as employee_data;

4. 原子 DDL：DDL 操作现在是原子的，要么完全成功，要么完全失败

5. 隐藏索引：可以隐藏索引而不删除它，用于测试索引的影响

   ALTER TABLE users ALTER INDEX idx_email INVISIBLE;

   ALTER TABLE users ALTER INDEX idx_email VISIBLE;

6. 降序索引：支持降序索引

CREATE INDEX idx_created_at_desc ON users (created_at DESC);

7. 资源组：可以为不同的查询分配不同的资源

   CREATE RESOURCE GROUP rg_admin

       TYPE = USER

       VCPU = 0-3

       THREAD_PRIORITY = 10;

8. 角色管理：简化权限管理

   CREATE ROLE 'app_read', 'app_write';

   GRANT SELECT ON app.* TO 'app_read';

   GRANT INSERT, UPDATE, DELETE ON app.* TO 'app_write';

   GRANT 'app_read', 'app_write' TO 'app_user'@'localhost';

   SET DEFAULT ROLE 'app_read', 'app_write' TO 'app_user'@'localhost';