九，MySQL优化
对 MySQL 自身的优化主要是对其配置文件 my.cnf 中的各项参数进行优化调整。而这些参数数值与服务器硬件以及 MYSQL 运行状态信息息息相关。因此,不同的服务器需要根据实际情况进行相应的调整。在/usr/share/doc/mysql-server-VERSION 目录中保存着 my-huge.cnfmy-innodb-heavy-4G.cnf my-large.cnf my-medium.cnf my-small.cnf 这样一些文件。这些文件是根据不同的服务器硬件情况的一些配置模板。我们采用 my-huge.cnf 来作范例。
首先,在 mysql 中输入 show status;查看输出的信息。对照这些信息以及服务器硬件配置,我们来一步步更改 my.cnf 里的配置。
skip-locking
这个选项是取消文件系统的外部锁,避免 MySQL 的外部锁定,减少出错几率增强稳定性。
skip-name-resolve
不进行域名反解析,注意由此带来的权限/授权问题使用这一选项可以消除 MySQL 进行DNS 解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用 IP 地址方式,否则 MySQL 将无法正常处理连接请求!
key_buffer_size = 512M
key_buffer 被称为索引缓冲区。这个值控制缓冲区的大小。该值设定需要参照 SHOWSTATUS 的输出,如下所示:
mysql> show status like '%key_read%';
+-------------------+-----------+
| Variable_name | Value |
+-------------------+-----------+
| Key_read_requests | 163554268 |
| Key_reads | 98247 |
+-------------------+-----------+
2 rows in set (0.00 sec)
Key_reads 代表对数据库的查询操作命中磁盘 buffer 的个数,
Key_read_requests 是查询操作的总数。
命中磁盘的数除以总数就是不中比率 —— 在本例中每 1,000 个请求,大约有 0.6 个没有命中内存。如果每 1,000 个请求中命中磁盘的数目超过 1 个,就应该考虑增大索引缓冲区了。 调整这个值需要根据内存大小而定,如果是独立的 db 服务器,可以设置高达 80%的内存总量指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。一般情况下对于内存在 4GB 左右的服务器该参数可设置为 256M 或 384M。注意:该参数值设置的过大反而会使服务器整体效率降低!
back_log = 200
连接排队列表总数。指定 MySQL 连接队列数量。我们都知道在客户端连接 MYSQL 服务端的时候,服务端提供相应进程处理请求。当进程都在忙碌的时候,建立了 TCP 连接的请求暂时放入队列中等待处理。不同的操作系统在这个队列大小上有它自己的限制。 试图设定 back_log 高于你的操作系统的限制将是无效的。默认值为 50。对于 Linux 系统推荐设置为小于 512 的整数。
table_cache = 512K
打开表缓存总数。如果你经常对一些表进行操作的话,那么每次操作都要重新读入表就显得不那么划算了。因此,我们将读过的表放入到 table_cache 当中减少对磁盘的读取次数。合理设定该值可以避免频繁的打开数据表产生的开销。
sort_buffer_size = 6M
当 MySQL 必须要进行排序时,就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。如果要排序的数据太大,那么数据就必须保存到磁盘上的临时文件中,并再次进行排序。如下所示: sort_merge_passes 状态的变化指示了磁盘的活动情况。
mysql> SHOW STATUS LIKE "sort%";
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| Sort_merge_passes | 1 |
| Sort_range | 79192 |
| Sort_rows | 2066532 |
| Sort_scan | 44006 |
+-------------------+---------+
4 rows in set (0.00 sec)
如果 sort_merge_passes 很大,就表示需要注意 sort_buffer_size。例如,sort_buffer_size = 6M 将排序缓冲区设置为 6MB。在设置这些数字时要十分谨慎,因为这些设置针对于每个会话。该值乘以连接数才是最终的消耗!
read_buffer_size = 4M
MySQL 也会分配一些内存来读取表。理想情况下,查询应该先去找相应的索引,索引提供了足够多的信息,可以只读入所需要的行。但是有时候由于设计不佳或者数据本性使然,导致索引无法提供什么帮助,结果查询操作需要从表中读取大量的数据。观察这个问题需要知道运行了多少个 SELECT 语句,以及需要读取表中的下一行数据的次数(而不是通过索引直接访问)。通过下图,我们可以确定表扫描比率:
mysql> SHOW STATUS LIKE "com_select";
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_select | 318243 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE "handler_read_rnd_next";
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| Handler_read_rnd_next | 165959471 |
+-----------------------+-----------+
1 row in set (0.00 sec)
Handler_read_rnd_next / Com_select 得出了表扫描比率 —— 在本例中是521:1。如果该值超过 4000,就应该查看 read_buffer_size,把这个值设的大一些。例如 read_buffer_size = 4M。如果这个数字超过了 8M 仍然不见效果,就应该与开发人员讨论一下对这些查询进行调优了。
join_buffer_size = 8M 跨表查询操作所能使用的缓冲区大小。以上两个值和 sort_buffer_size 一样,该参数对应的分配内存也是对应每个连接的。
myisam_sort_buffer_size = 64M MyISAM 表发生变化时重新排序所需的缓冲。
tmp_table_size=
max_heap_table_size=
mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 30660 |
| Created_tmp_files |2 |
| Created_tmp_tables | 32912 |
+-------------------------+-------+
3 rows in set (0.00 sec)
每次使用临时表都会增大 Created_tmp_tables;基于磁盘的表也会增大Created_tmp_disk_tables。对于这个比率,并没有什么严格的规则,因为这依赖于所
涉及的查询。长时间观察 Created_tmp_disk_tables 会显示所创建的磁盘表的比率,您可以确定设置的效率。 tmp_table_size 和 max_heap_table_size 都可以控制临时表的最大大小,因此请确保在 my.cnf 中对这两个值都进行了设置。
thread_cache = 128
每次 mysqld 需要创建一个新线程时,这个值都会增加。观察下图,此处重要的值是Threads_created。如果这个数字在连续执行 SHOW STATUS 命令时快速增加,就应该尝试增大线程缓存。
mysql> SHOW STATUS LIKE 'threads%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Threads_cached | 27 |
| Threads_connected | 15 |
| Threads_created | 838610 |
| Threads_running | 3 |
+-------------------+--------+
4 rows in set (0.00 sec)
query_cache_size = 128M
我们肯定不希望每次想要查询什么的时候都需要重新对表进行一次读取。正确的处理方
法应该是将查询缓存到 cache 里。但是,有了 cache 也不是说就是万事大吉了。我们还需
要知道这个值设定的是否合适。通过观察缓存到 cache 中的这些查询结果到底是否被再次
使用了,也就是命中率如何来得到答案。用以下命令观察,如果 Qcache_lowmem_prunes
的值非常大,则表明经常出现缓冲不够的情况:
mysql> SHOW VARIABLES LIKE '%query_cache%';
mysql> SHOW STATUS LIKE 'Qcache%';
Qcache_free_blocks 缓存中相邻内存块的个数。数目大说明可能有碎片。
FLUSH QUERY CACHE 会对缓存中的碎片进行整理,从而得到一个空闲块。
Qcache_free_memory 缓存中的空闲内存。
Qcache_hits 每次查询在缓存中命中时就增大。
Qcache_inserts 每次插入一个查询时就增大。命中次数除以插入次数就是不中比率;用 1 减去这个值就是命中率。
Qcache_lowmem_prunes 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个数字在不断增长,就表示可能碎片非 常严重,或者内存很少。(上面的 free_blocks 和free_memory 可以告诉您属于哪种情况)。
Qcache_not_cached 不适合进行缓存的查询的数量,通常是由于这些查询不是 SELECT 语句。
Qcache_queries_in_cache 当前缓存的查询(和响应)的数量。
Qcache_total_blocks 缓存中块的数量。
set-variable = wait_timeout=60 设置超时时间,能避免长连接。
max_connect_errors = 100
如果一个主机在连接到服务器时有问题,并重试很多次后放弃,那么这个主机就会被锁定,直到 FLUSH HOSTS 之后才能运行。默认情况下,10 次失败就足以导致锁定了。将这个值修改为 100 会给服务器足够的时间来从问题中恢复。如果重试 100 次都无法建立连接,那么使用再高的值也不会有太多帮助,可能它根本就无法连接。
wait_timeout = 10 终止空闲时间超过 10 秒的连接
thread_concurrency = 4
该值设定最大并发线程数,根据你的 CPU 数量*2 来设定。
log-slow-queries = slow.log 慢查询的记录日志,查看日志然后对慢查询进行优化。
long_query_time = 5 多长时间的查询算做是慢查询，一般为2。
Log-queries-not-using-indexes
指定为没有使用���引的查询。你可以使用 mysqldumpslow 命令查看。
skip-innodbM
skip-bdb
关闭不需要的表类型,如果你需要,就不要加上这个。
附:MYSQL 支持的表类型
DBD
Berkeley DB(DBD)表是支持事务处理的表,由 Sleepycat 软件公司()开发。它提供 MySQL 用户期待已久的功能-事务控制。事
务控制在任何数据库系统中都是一个极有价值的功能,因为它们确保一组命令能成功地执行。
下面的表都属于非事务安全
HEAP
HEAP 表是 MySQL 中存取数据最快的表。这是因为他们使用存储在动态内存中的一个哈希索引。另一个要点是如果 MySQL 或服务器崩溃,数据将丢失。
ISAM
ISAM 表是早期 MySQL 版本的缺省表类型,直到 MyIASM 开发出来。建议不要再使用它。
MERGE
MERGE 是一个有趣的新类型,在 3.23.25 之后出现。一个 MERGE 表实际上是一个相同MyISAM 表的集合,合并成一个表,主要是为了效率原因。这样可以提高速度、搜索效率、修复效率并节省磁盘空间。
MyIASM
这是 MySQL 的缺省表类型。它基于 IASM 代码,但有很多有用的扩展。MyIASM 比较好的原因:
1、MyIASM 表小于 IASM 表,所以使用较少资源。
2、MyIASM 表在不同的平台上二进制层可移植。
3、更大的键码尺寸,更大的键码上限。
你可在创建表时指定表的类型。下例创建一个 HEAP 表:
mysql>CREATE TABLE email_addresses TYPE=HEAP (
->email char(55) NOT NULL,
->name char(30) NOT NULL,
->PRIMARY KEY(email) );
