跳转到内容

MySQL/优化

Edit links
来自维基教科书，开放的书本，开放的世界

在开始优化之前

[编辑 | 编辑源代码]

当数据库似乎“很慢”时，首先考虑以下所有要点，例如，通过在应用程序中使用更复杂的算法来完全消除某个查询，始终是优化它的最优雅方法:)

  1. 找到瓶颈（CPU、内存、I/O、哪些查询）
  2. 优化应用程序（删除不必要的查询或缓存 PHP 生成的网页）
  3. 优化查询（使用索引、临时表或不同的连接方式）
  4. 优化数据库服务器（缓存大小等）
  5. 优化系统（不同的文件系统类型、交换空间和内核版本）
  6. 优化硬件（有时确实是最便宜、最快的途径）

为了找到这些瓶颈，以下工具被证明很有帮助

vmstat
快速监控 cpu、内存和 I/O 使用情况，并确定哪个是瓶颈
top
检查 mysqld 和应用程序的当前内存和 cpu 使用情况
mytop
找出哪些查询导致问题
mysql-admin（GUI 应用程序，不要与 mysqladmin 混淆）
以非常方便的方式监控和调整 mysql
mysqlreport
其输出应作为一种逐步检查清单使用

使用这些工具，大多数应用程序也可以使用以下几组进行非常广泛的分类

  • 基于 I/O 和读取（博客、新闻）
  • 基于 I/O 和写入（网页访问跟踪器、会计数据收集）
  • 基于 CPU（复杂的内容管理系统、业务应用程序）

优化表

[编辑 | 编辑源代码]

定期使用以下命令重新组织磁盘空间，这将减小表大小，而不会删除任何记录[1]

OPTIMIZE TABLE MyTable1

此外，在创建表时，最好使用其最小类型。例如

  • 如果数字始终为正数，则选择 unsigned 类型，以便能够在相同数量的字节中存储两倍多的数据。
  • 要存储当代日期（从 1970 年到 2038 年），最好使用 4 个字节的 timestamp，而不是 8 个字节的 datetime。[2]

优化查询

[编辑 | 编辑源代码]

使用 BENCHMARK 比较函数

[编辑 | 编辑源代码]

BENCHMARK() 函数可用于比较 MySQL 函数或运算符的速度。例如

mysql> SELECT BENCHMARK(100000000, CONCAT('a','b'));
+---------------------------------------+
| BENCHMARK(100000000, CONCAT('a','b')) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (21.30 sec)

但是，这不能用于比较查询

mysql> SELECT BENCHMARK(100, SELECT `id` FROM `lines`);
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for
the right syntax to use near 'SELECT `id` FROM `lines`)' at line 1

由于 MySQL 只需要一小部分时间来解析查询，并且系统可能也忙于执行其他操作，因此运行时间少于 5-10 秒的基准测试可以被认为是完全没有意义的，并且在这种数量级的运行时间差异可以被认为是纯粹的巧合。

使用 EXPLAIN 分析函数

[编辑 | 编辑源代码]

当您在 SELECT 语句之前加上 EXPLAIN 关键字时，MySQL 会解释它将如何处理 SELECT，提供有关如何连接表以及连接顺序的信息。这允许在函数中放置一些最终的 提示。

在追求良好性能时，使用和理解 EXPLAIN 至关重要，因此官方文档的相关章节是必读的！

一个简单的例子

[编辑 | 编辑源代码]

两个都没有索引的表的连接

 mysql> explain SELECT * FROM a left join b using (i) WHERE a.i < 2;
 +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
 |  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
 |  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |    3 |             |
 +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
 2 rows in set (0.01 sec)

现在第二个表获得了索引，explain 显示 MySQL 现在知道只需要使用 3 行中的 2 行。

 mysql> ALTER TABLE b ADD KEY(i);
 Query OK, 3 rows affected (0.01 sec)
 Records: 3  Duplicates: 0  Warnings: 0

 mysql> explain SELECT * FROM a left join b using (i) WHERE a.i < 2;
 +----+-------------+-------+------+---------------+------+---------+----------+------+-------------+
 | id | select_type | table | type | possible_keys | key  | key_len | ref      | rows | Extra       |
 +----+-------------+-------+------+---------------+------+---------+----------+------+-------------+
 |  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL     |    4 | Using where |
 |  1 | SIMPLE      | b     | ref  | i             | i    | 5       | test.a.i |    2 |             |
 +----+-------------+-------+------+---------------+------+---------+----------+------+-------------+
 2 rows in set (0.00 sec)

现在第一个表也获得了索引，以便 WHERE 条件可以得到改善，MySQL 在尝试在数据文件中搜索之前就知道第一个表中只有一行是相关的。

 mysql> ALTER TABLE a ADD KEY(i);
 Query OK, 4 rows affected (0.00 sec)
 Records: 4  Duplicates: 0  Warnings: 0

 mysql> explain SELECT * FROM a left join b using (i) WHERE a.i < 2;
 +----+-------------+-------+-------+---------------+------+---------+----------+------+-------------+
 | id | select_type | table | type  | possible_keys | key  | key_len | ref      | rows | Extra       |
 +----+-------------+-------+-------+---------------+------+---------+----------+------+-------------+
 |  1 | SIMPLE      | a     | range | i             | i    | 5       | NULL     |    1 | Using where |
 |  1 | SIMPLE      | b     | ref   | i             | i    | 5       | test.a.i |    2 |             |
 +----+-------------+-------+-------+---------------+------+---------+----------+------+-------------+
 2 rows in set (0.02 sec)

优化 MySQL 服务器

[编辑 | 编辑源代码]

状态和服务器变量

[编辑 | 编辑源代码]

MySQL 可以通过观察 状态变量 和设置 服务器变量 来进行监控和调整，这些变量可以是全局的，也可以是每个会话的。状态变量可以通过 SHOW [GLOBAL|SESSION] STATUS [LIKE '%foo%'] 或 mysqladmin [extended-]status 来监控。服务器变量可以在 /etc/mysql/my.cnf 文件中设置，也可以通过 SET [GLOBAL|SESSION] VARIABLE foo := bar 设置，并可以通过 mysqladmin variables 或 SHOW [GLOBAL|SESSION] VARIABLES [LIKE '%foo%'] 显示。

通常，状态变量以大写字母开头，服务器变量以小写字母开头。

在处理上述每个会话系统变量时，始终应考虑必须将它们乘以 max_connections 以估计最大的内存消耗。如果不能这样做，当比平时更多的客户端连接到服务器时，很容易在负载高峰时导致服务器崩溃！可以使用以下公式进行快速粗略的估计

   min_memory_needed = global_buffers + (thread_buffers * max_connections)

   global_buffers:
       key_buffer
       innodb_buffer_pool
       innodb_log_buffer
       innodb_additional_mem_pool
       net_buffer

   thread_buffers:
       sort_buffer
       myisam_sort_buffer
       read_buffer
       join_buffer
       read_rnd_buffer

注意：尤其是在处理服务器设置时，所有信息都应在官方文档的相关章节中进行验证，因为这些信息可能会发生变化，本文作者缺乏有关服务器内部工作原理的确认知识。


索引

[编辑 | 编辑源代码]

索引是一种更快地定位元素的方法。这适用于单个元素，也适用于元素范围。

实验

[编辑 | 编辑源代码]

注意：当您进行时间测试时，请确保禁用查询缓存（在 my.cnf 中设置 query_cache_type=0），以强制每次输入查询时重新计算，而不是从缓存中获取预先计算的结果。


让我们运行以下 Perl 程序

#!/usr/bin/perl

use strict;

print "DROP TABLE IF EXISTS weightin;\n";
print "CREATE TABLE weightin (
	id INT PRIMARY KEY auto_increment,
	line TINYINT,
	date DATETIME,
	weight FLOAT(8,3)
);\n";


# 2 millions records, interval = 100s
for (my $timestamp = 1000000000; $timestamp < 1200000000; $timestamp += 100) {
    my $date = int($timestamp + rand(1000) - 500);
    my $weight = rand(1000);
    my $line = int(rand(3)) + 1;
    print "INSERT INTO weightin (date, line, weight) VALUES (FROM_UNIXTIME($date), $line, $weight);\n";
}

它做了什么？它模拟了工业生产线定期称重物体的數據流，以便我们可以计算平均材料使用量。随着时间的推移，大量记录会堆积起来。

如何使用它？

 mysql> CREATE DATABASE industrial
 $ perl generate_huge_db.pl | mysql industrial
 real	6m21.042s
 user	0m37.282s
 sys	0m51.467s

我们可以用以下命令检查元素的数量

 mysql> SELECT COUNT(*) FROM weightin;
 +----------+
 | count(*) |
 +----------+
 |  2000000 | 
 +----------+
 1 row in set (0.00 sec)

大小必须很重要

 $ perl generate_huge_db.pl > import.sql
 $ ls -lh import.sql
 -rw-r--r-- 1 root root 189M jun 15 22:08 import.sql
 
 $ ls -lh /var/lib/mysql/industrial/weightin.MYD
 -rw-rw---- 1 mysql mysql 35M jun 15 22:17 /var/lib/mysql/industrial/weightin.MYD
 
 $ time mysqldump industrial > dump.sql 
 real	0m9.599s
 user	0m3.792s
 sys	0m0.616s
 $ ls -lh dump.sql
 -rw-r--r-- 1 root root 79M jun 15 22:18 dump.sql
 
 $ time mysqldump industrial | gzip > dump.sql.gz 
 real	0m17.339s
 user	0m11.897s
 sys	0m0.488s
 $ ls -lh dump.sql.gz  
 -rw-r--r-- 1 root root 22M jun 15 22:19 dump.sql.gz

顺便说一下，从转储中恢复速度要快得多，因为它使用的是扩展插入！

 # time zcat dump.sql.gz | mysql industrial
 real	0m31.772s
 user	0m3.436s
 sys	0m0.580s

此 SQL 命令将扫描所有记录以获取总和

 mysql> SELECT SUM(*) FROM weightin;

假设我们需要计算 2008 年 1 月 1 日使用的总材料

 mysql> SELECT COUNT(*), SUM(poids) FROM pesee WHERE date >= '2008-01-01' AND date < '2008-01-02';

即使对于这少量的记录，MySQL 也需要浏览整个数据库。这是因为记录可能在任何地方：底部、结尾、中间，没有任何保证记录是有序的。

为了改进这一点，我们可以为 “date” 字段添加索引。这意味着 MySQL 将创建一个新的隐藏表格，其中所有日期按时间顺序排序，并存储其偏移量（位置）在 “weightin” 表中，以便检索完整的记录。

由于索引已排序，因此 MySQL 查找单个记录（使用二进制搜索算法）甚至数据范围（查找第一个和最后一个元素，范围在两者之间）要快得多。

添加索引

 ALTER TABLE weightin ADD INDEX (date);

如果查询需要在字段上进行计算（例如 TIME(date)），则索引不起作用，但对于范围（例如 WHERE date < '2008-01-02'）有效。

您会注意到 .MYD 文件的大小增加了

 $ ls -lh /var/lib/mysql/industrial/
 -rw-rw---- 1 mysql mysql  49M jun 15 22:36 weightin.MYI

这是 MySQL 存储索引的地方。最初有一个 “id” 字段的索引，这是所有主键的情况。


另一个例子

[编辑 | 编辑源代码]

另一个例子：假设我们想优化这个查询

 mysql> SELECT DISTINCT line FROM weightin;

我们可以通过在 “line” 字段上添加索引来实现，以便将重复项分组在一起，从而避免查询重新扫描整个表格来定位它们。

 ALTER TABLE weightin ADD INDEX (line);

索引文件的大小增加了

 -rw-rw---- 1 mysql mysql  65M jun 15 22:38 weightin.MYI

一般注意事项

[编辑 | 编辑源代码]

对于 SELECT 查询，始终被问到的第一个也是最重要的一个问题是，索引（也称为“键”）是否已配置，以及如果已配置，数据库服务器是否实际使用它们。


1. 检查索引是否实际被使用

可以使用 “EXPLAIN” 命令检查单个查询。对于整个服务器，应监控 “Sort_%” 变量，因为它们指示 MySQL 多久需要浏览整个数据文件，因为没有可用的索引。

2. 索引是否已缓冲

将索引保存在内存中可以大大提高读取性能。 “Key_reads / Key_read_requests” 的商表示 MySQL 在需要键时实际访问磁盘上的索引文件的频率。Key_writes 也是如此，使用 mysqlreport 为您执行此处的计算。如果百分比过高，MyISAM 的 key_buffer_size 和 InnoDB 的 innodb_buffer_pool_size 是要调整的相应变量。

Key_blocks_% 变量可用于查看实际使用了配置的键缓冲区中的多少。单位为 1KB，除非在 key_cache_block_size 中另有设置。由于 MySQL 在内部使用了一些块，因此必须检查 key_blocks_unused。要估计缓冲区的大小，可以将相关的 .MYI 文件的大小加起来。对于 InnoDB，有 innodb_buffer_pool_size，尽管在这种情况下，不仅索引会被缓冲，数据也会被缓冲。

3. 其他设置

sort_buffer_size（每个线程）是用于 ORDER BY 和 GROUP BY 的内存。myisam_sort_buffer_size 是完全不同的东西，不应修改。

read_buffer_size（每个线程）是在进行全表扫描时一次从磁盘读取到内存的内存块的大小，因为大型表格无法完全放入内存。这很少需要调整。

查询缓存

[编辑 | 编辑源代码]

如果您有基于读取的应用程序，那么不使用 4.0.1 以下的任何 MySQL 版本的主要原因是，从该版本开始，MySQL 能够存储 SELECT 查询的结果，直到其表格被修改。

查询缓存可以使用 query_cache_% 变量进行配置。这里最重要的全局变量是 query_cache_size 和 query_cache_limit，它们可以防止结果异常大的单个查询（大于此大小）占用整个缓存。

请注意，查询缓存块的大小可变，最小大小为 query_cache_min_res_unit，因此在完全刷新缓存后，空闲块的数量理想情况下只有一个。Qcache_free_blocks 的值较大仅表示碎片率高。

值得监控以下变量

  • Qcache_free_blocks
如果此值很高，则表示碎片率很高，尽管这并不一定是一件坏事。
  • Qcache_not_cached
如果此值很高，则表示有太多不可缓存的查询（例如，因为它们使用像 now() 这样的函数），或者 query_cache_limit 的值太低。
  • Qcache_lowmem_prunes
这是由于缓存已满而不是因为它们的底层表格已被修改而被清除的旧结果的数量。必须增加 query_cache_size 以降低此变量的值。

示例

空缓存

mysql> SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

已使用的缓存（savannah.gnu.org）

mysql> SHOW VARIABLES LIKE "query_cache_size";
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 33554432 |
+------------------+----------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE "Qcache%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1409     |
| Qcache_free_memory      | 27629552 |
| Qcache_hits             | 7925191  |
| Qcache_inserts          | 3400435  |
| Qcache_lowmem_prunes    | 2946778  |
| Qcache_not_cached       | 71255    |
| Qcache_queries_in_cache | 4546     |
| Qcache_total_blocks     | 10575    |
+-------------------------+----------+
8 rows in set (0.00 sec)

匹配的 my.cnf 配置参数为

query_cache_size = 32M

清除缓存（在测试新查询的效率时很有用）

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

等待锁

[编辑 | 编辑源代码]

Table_locks_% 变量显示必须等待的查询数量，因为它们尝试访问的表格当前被其他查询锁定。这些情况可能是由于 “LOCK TABLE” 语句以及例如对同一表格的同步写入访问造成的。


表格缓存

[编辑 | 编辑源代码]

MySQL 需要一定的时间才能“打开”表格并读取其元数据，例如列名等。

如果许多线程试图访问同一个表格，它会被多次打开。

为了加快速度，元数据可以在 table_cache（自 MySQL 5.1.3 以来也称为 table_open_cache）中缓存。

此设置的理想值是 max_connections 的数量乘以每个 SELECT 通常使用的表格数量。

使用 mysqlreport 或查看当前 Open_tables 和自 Opened_tables 以及 Uptime，可以计算出每秒所需的表格打开次数（但要考虑夜间等非高峰时段）。

连接和线程

[编辑 | 编辑源代码]

对于每个客户端连接（也称为会话），MySQL 在主 mysqld 进程下创建一个独立的线程。对于每秒有数百个新连接的大型网站，创建线程本身可能会消耗大量时间。为了加快速度，空闲线程可以在其客户端断开连接后被缓存。经验法则是，每秒不应新建超过一个线程。向服务器发送多个查询的客户端应使用 持久连接，例如使用 PHPs mysql_pconnect() 函数。

此缓存可以使用 thread_cache_size 进行配置，并使用 threads_% 变量进行监控。

为了避免超载，如果当前使用的连接数超过 max_connections，则 MySQL 会阻止新的连接。从 max_used_connections 开始，并监控 Aborted_clients 中被拒绝的连接数量，以及 Aborted_connections 中超时连接的数量。使用持久连接的客户端忘记断开连接很容易导致拒绝服务，因此请注意！通常，连接在空闲 wait_timeout 秒后关闭。


临时表格

[编辑 | 编辑源代码]

MySQL 在排序或分组结果时创建临时表格是完全正常的。这些表格要么保存在内存中，要么太大而写入磁盘，这自然要慢得多。Created_tmp_% 变量中的磁盘表格数量应可忽略不计，否则应重新考虑 max_heap_table_size 和 tmp_table_size 中的设置。

延迟写入

[编辑 | 编辑源代码]

在将 Web 服务器访问日志文件写入数据库等情况下，对于同一表格中的许多后续 INSERT 查询（用于不太重要的数据），可以通过建议服务器暂时缓存写入请求，然后将一批数据发送到磁盘来提高性能。

但请注意，所有提到的方法都与 ACID 兼容性相冲突，因为 INSERT 查询在数据实际写入磁盘之前向客户端确认 OK，因此在断电或服务器崩溃的情况下仍可能丢失数据。此外，文档中提到的副作用通常读起来就像现代药物的病人信息手册……

使用 `CREATE` 或 `ALTER TABLE` 语句，可以为 MyISAM 表设置 **DELAY_KEY_WRITE** 选项。缺点是，在崩溃后，表会自动标记为损坏，需要检查/修复，这可能需要一些时间。

InnoDB 可以使用 **innodb_flush_log_at_trx_commit** 选项来延迟数据写入。如果服务器崩溃，数据本身应该是保持一致的，只需要重建索引。

**INSERT DELAYED** 在每个查询的基础上对主要的存储引擎生效。

进一步阅读

[编辑 | 编辑源代码]

关于 MySQL 服务器优化的有用链接

参考资料

[编辑 | 编辑源代码]
  1. ↑ https://dev.mysqlserver.cn/doc/refman/5.1/en/optimize-table.html
  2. ↑ https://dev.mysqlserver.cn/doc/refman/5.1/en/datetime.html
检索自 "https://wikibooks.cn/w/index.php?title=MySQL/Optimization&oldid=4419106"
华夏公益教科书