32、MySQL 管理与优化全解析

java5

于 2025-11-24 10:33:57 发布

阅读量9

点赞数

CC 4.0 BY-SA版权

分类专栏： PHP与MySQL实战精讲文章标签： MySQL 二进制日志访问控制

本文链接：https://blog.youkuaiyun.com/java5/article/details/155471997

PHP与MySQL实战精讲专栏收录该内容

40 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

MySQL 管理与优化全解析

1. 启用二进制日志

启用二进制日志是 MySQL 管理中的重要操作，以下是具体步骤：
1. 修改配置文件 ：移除配置文件中的 # ，使 server-id = 1 。若看不到该行，可使用 Ctrl+W 并输入 server-id 。
2. 保存文件 ：按下 Ctrl+O 保存文件（这里是字母 “o”，不是数字 0，在 nano 术语中代表 “output”）。
3. 退出编辑器 ：按下 Ctrl+X 退出 nano 并返回命令提示符。
4. 重启 MySQL ：运行以下命令重启 MySQL 以读取更新后的配置：

sudo systemctl restart mysql.service

这里的 sudo 前缀很重要，因为只有具有管理员权限的人才能启动和停止服务。

启用二进制日志后，每次服务器刷新日志文件时都会创建一个新文件，实际上，这通常在服务器重启时发生。可以通过以下命令检查指定位置是否创建了新的日志文件：

ls /var/log/mysql

2. 二进制日志的存储与恢复

存储建议 ：如果可能，应将二进制日志存储在与 MySQL 数据库文件不同的硬盘上，这样即使硬盘出现故障，也不会同时丢失数据库和备份。
恢复数据库 ：在发生灾难时，只要有完整备份和备份后生成的二进制日志文件，恢复数据库就相对简单。先设置一个新的空 MySQL 服务器，然后应用完整备份，最后使用 mysqlbinlog 实用程序应用二进制日志。

例如，假设有两个二进制日志文件 binlog.000041 和 binlog.000042 ，可以使用以下命令生成 SQL 文本文件并应用到 MySQL 服务器：

mysqlbinlog binlog.000041 binlog.000042 > binlog.sql
mysql -u root -psecret < binlog.sql

3. MySQL 访问控制

MySQL 中的 mysql 数据库用于跟踪用户、密码以及他们的权限。用户访问由 mysql 数据库中的五个表控制： user 、 db 、 host 、 tables_priv 和 columns_priv 。

主机名问题 ：当创建只能从运行 MySQL 服务器的计算机登录的用户时，在 MySQL Workbench 的 “添加新用户” 表单的 “主机” 字段中，指定 www.example.com 或 localhost 都不够可靠。建议为需要从服务器所在机器连接的用户在 MySQL 访问系统中创建两个用户条目，一个使用机器的实际主机名，另一个使用 localhost 。

以下是一个示例用户表：
| Host | User | Password |
|------------------|------|------------------|
| localhost | root | encrypted value |
| www.example.com | root | encrypted value |
| localhost | | |
| www.example.com | | |
| %.example.com | jess | encrypted value |

由于 Jess 的条目主机名最不具体，排在最后。当 Jess 从 www.example.com 尝试连接时，MySQL 服务器会将她的连接尝试与匿名用户条目匹配，从而拒绝连接。

解决方案有两种：
1. 删除匿名用户条目：

DELETE FROM mysql.user WHERE User="";

为需要从 localhost 连接的所有用户再添加两个条目：
| Host | User | Password |
|------------------|------|------------------|
| localhost | root | encrypted value |
| www.example.com | root | encrypted value |
| localhost | jess | encrypted value |
| www.example.com | jess | encrypted value |
| localhost | | |
| www.example.com | | |
| %.example.com | jess | encrypted value |

由于维护每个用户的三个用户条目（和三组权限）过于繁琐，建议删除匿名用户，除非有特殊需求。

4. 忘记密码的处理

如果忘记了 MySQL 服务器的密码，可以按照以下步骤恢复控制：
1. 关闭 MySQL 服务器 ：运行以下命令关闭服务器：

sudo systemctl stop mysql.service

重启服务器并跳过权限表 ：在 MySQL 服务器的 my.cnf 配置文件的 [mysqld] 行下添加 skip-grant-tables 并保存文件：

[mysqld]
skip-grant-tables

重启服务器 ：运行以下命令重启服务器：

sudo systemctl start mysql.service

更改密码 ：连接到 MySQL 服务器（使用 MySQL Workbench 或 mysql 命令提示符实用程序），然后更改用户密码：

UPDATE mysql.user SET Password=PASSWORD("newpassword") WHERE User="homestead";

恢复正常设置 ：断开连接，关闭 MySQL 服务器，移除 skip-grant-tables 选项，再次启动服务器，即可使用新密码连接。

5. 数据库索引

数据库索引可以加快 MySQL 在 SELECT 查询中查找记录的速度。例如，在 Internet Joke Database 中， joke 表可能包含数千甚至数十万行数据。如果没有索引，执行以下查询时：

SELECT joketext FROM joke WHERE id = 1234

MySQL 必须逐行检查 joke 表的 id 列，直到找到值为 1234 的行。而且，由于没有索引，MySQL 无法确定只有一行具有该值，还需要扫描整个表以确保找到所有匹配的行。

幸运的是， joke 表的 id 列有一个索引，名为 PRIMARY 。这是因为在创建表时， id 列被定义为 PRIMARY KEY ：

CREATE TABLE joke (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    joketext TEXT,
    jokedate DATE NOT NULL,
    authorId INT
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

在数据库术语中，“key” 就是 “index” 的意思， PRIMARY KEY 是一个名为 PRIMARY 的索引，要求该列的每个值在表中唯一。

可以使用 EXPLAIN 命令来确认查询是否使用了索引：

EXPLAIN SELECT joketext FROM joke WHERE id = 1

然而，对于以下查询：

SELECT * FROM joke WHERE authorId = 2

MySQL 无法找到索引来辅助查询，因此需要对整个表进行扫描。可以通过在 joke 表的 authorId 列上添加索引来加快查询速度：
1. 在 MySQL Workbench 中，选择 joke 表，右键单击并选择 “Alter Table”。
2. 在 “Indexes” 选项卡上，通过双击空单元格在 PRIMARY 下方添加一行。
3. 为索引命名（通常使用列名），并选择类型为 INDEX 。
4. 从中间面板中选择 authorId 列，然后点击 “Apply”。

再次使用 EXPLAIN 命令确认查询是否使用了新的 authorId 索引。

需要注意的是，虽然索引可以加快查询速度，但也需要额外的磁盘空间，并且每次对数据库内容进行更改（如 INSERT 或 UPDATE 查询）时，MySQL 都需要花费时间重建所有受影响的索引。因此，通常只应添加必要的索引，以保持网站的 SELECT 查询快速。一般来说，应该在 WHERE 、 GROUP BY 、 ORDER BY 或 JOIN ... ON 子句中使用的列上创建索引。

6. 多列索引

并非每个表都有 id 列。例如，在关联笑话和类别的表中，需要一个连接表 joke_category ，它有两个列： jokeId 和 categoryId 。

CREATE TABLE joke_category (
    jokeId INT NOT NULL,
    categoryId INT NOT NULL,
    PRIMARY KEY (jokeId, categoryId)
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

这个表的主键由 jokeId 和 categoryId 两列组成，这种多列索引称为复合索引。复合索引可以加快涉及两个索引列的查询速度，例如：

SELECT * FROM joke_category WHERE jokeid = 3 AND categoryid = 4

通过使用复合索引，可以避免重复记录，提高查询效率。

graph LR
    A[开始] --> B[启用二进制日志]
    B --> C[设置存储位置]
    C --> D[处理访问控制]
    D --> E[应对忘记密码]
    E --> F[使用索引优化]
    F --> G[应用多列索引]
    G --> H[结束]

综上所述，通过正确启用二进制日志、合理存储日志文件、有效管理用户访问控制、处理密码遗忘问题以及使用索引优化查询，可以提高 MySQL 数据库的性能和可靠性。在实际应用中，应根据具体情况选择合适的方法和策略，以满足业务需求。

MySQL 管理与优化全解析

7. 查看索引效果

在实际操作中，我们可以通过 EXPLAIN 命令来查看查询是否使用了索引，以及使用了哪些索引。以下是一个具体的示例：

EXPLAIN SELECT joketext FROM joke WHERE id = 1;

运行这个查询后，我们可以看到类似如下的结果：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|----|-------------|-------|------------|------|---------------|-----|---------|-----|------|----------|-------|
| 1 | SIMPLE | joke | NULL | const| PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |

从这个结果中，我们可以确认查询使用了 PRIMARY 索引。

而对于没有合适索引的查询，如：

EXPLAIN SELECT * FROM joke WHERE authorId = 2;

结果可能会显示 NULL 表示没有使用索引，这意味着 MySQL 需要进行全表扫描，查询效率会较低。

8. 索引的创建与删除

在 MySQL 中，除了通过 MySQL Workbench 创建索引，我们也可以使用 SQL 语句来创建和删除索引。

创建索引 ：

-- 在 joke 表的 authorId 列上创建普通索引
CREATE INDEX idx_authorId ON joke (authorId);

-- 创建复合索引
CREATE INDEX idx_joke_category ON joke_category (jokeId, categoryId);

删除索引 ：

-- 删除 joke 表上的 idx_authorId 索引
DROP INDEX idx_authorId ON joke;

9. 索引的选择策略

在决定是否创建索引以及创建什么样的索引时，我们需要考虑以下几个因素：
- 查询频率 ：对于经常执行的查询，尤其是包含复杂 WHERE 子句的查询，创建索引可以显著提高查询速度。
- 数据分布 ：如果某列的数据分布比较均匀，例如性别列只有 “男” 和 “女” 两个值，那么创建索引可能不会带来明显的性能提升。
- 更新频率 ：如果表的更新操作（如 INSERT 、 UPDATE 、 DELETE ）非常频繁，过多的索引会增加数据库的负担，因为每次更新都需要更新相关的索引。

以下是一个简单的决策流程图：

graph LR
    A[有频繁查询?] -->|是| B[查询包含复杂 WHERE 子句?]
    A -->|否| C[不创建索引]
    B -->|是| D[列数据分布均匀?]
    B -->|否| E[创建索引]
    D -->|是| C
    D -->|否| E

10. 总结与最佳实践

为了更好地管理和优化 MySQL 数据库，我们可以总结以下最佳实践：
1. 二进制日志 ：
- 启用二进制日志以支持数据库恢复。
- 将二进制日志存储在与数据库文件不同的硬盘上，提高数据安全性。
2. 访问控制 ：
- 合理设置用户的主机名，避免因主机名问题导致连接失败。
- 及时删除不必要的匿名用户，简化用户管理。
3. 密码管理 ：
- 牢记密码，若忘记可按步骤跳过权限表修改密码。
4. 索引使用 ：
- 为经常用于查询条件的列创建索引，但避免过度索引。
- 对于涉及多列查询的情况，考虑使用复合索引。

通过遵循这些最佳实践，我们可以提高 MySQL 数据库的性能、安全性和可靠性，确保数据库能够稳定地支持业务需求。在实际应用中，还需要根据具体的业务场景和数据特点进行灵活调整。

11. 常见问题解答

以下是一些关于 MySQL 管理和优化的常见问题及解答：
| 问题 | 解答 |
|------|------|
| 为什么我的查询还是很慢，即使我已经创建了索引？ | 可能是索引没有被正确使用，检查 EXPLAIN 结果，确保查询条件与索引列匹配；也可能是数据分布不均匀，导致索引效果不佳。 |
| 我应该在哪些列上创建索引？ | 通常在 WHERE 、 GROUP BY 、 ORDER BY 或 JOIN ... ON 子句中使用的列上创建索引，但要考虑数据分布和更新频率。 |
| 如何查看数据库中所有的索引？ | 可以使用 SHOW INDEX FROM table_name; 命令查看指定表的所有索引。 |

通过了解这些常见问题及解答，可以更好地应对 MySQL 管理和优化过程中遇到的问题。

12. 后续学习建议

如果你想进一步深入学习 MySQL 管理和优化，可以参考以下学习资源和方向：
- 官方文档 ：MySQL 官方文档是最权威的学习资料，包含了详细的语法说明和操作指南。
- 在线课程 ：许多在线学习平台提供了 MySQL 相关的课程，可以系统地学习数据库知识。
- 实践项目 ：通过实际参与数据库项目，积累经验，提高解决实际问题的能力。

不断学习和实践，将有助于你成为一名优秀的 MySQL 数据库管理员。