MySQL数据库
优化 MySQL 查询：索引
索引的作用
索引是提高查找速度的一种方式。它适用于单个元素和元素范围的查找。
实验
注意：在进行性能测试时，确保查询缓存被禁用（在 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";
# 生成 200 万条记录，每条记录间隔 100 秒
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
使用 mysqldump 备份：
$ 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
查询优化
例如，计算所有记录的总和：
mysql> SELECT SUM(*) FROM weightin;
如果我们需要计算 2008 年 1 月 1 日的物料总使用量：
mysql> SELECT COUNT(*), SUM(weight) FROM weightin WHERE date >= '2008-01-01' AND date < '2008-01-02';
MySQL 需要扫描整个数据库，即使记录数量很少。这是因为记录的位置是随机的，无法保证记录按顺序排列。
优化方法：添加索引
为 date 字段添加索引，这样 MySQL 会创建一个排序好的隐藏表，存储 date 字段的偏移量（位置），从而加速查询。
ALTER TABLE weightin ADD INDEX (date);
注意：
- 索引对范围查询（如
WHERE date < '2008-01-02'）有效，但对于计算字段（如TIME(date)）则无效。
添加索引后，.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：与sort_buffer_size不同，不应更改。 -
read_buffer_size（每线程）：在进行全表扫描时，从磁盘一次读取到内存的内存块大小。对于大表（无法完全放入内存中）可能需要调整，但通常不需要频繁调优。