浅谈MySQL数据库优化


我们在前面已经简单介绍了一些MYSQL数据库的基本操作,这一章我们将针对MYSQL数据库管理员详细介绍下MYSQL数据库的优化问题。

1 优化MySQL服务器

1.1 MYSQL服务器系统变量

我们在前面的章节中曾经讲到过MYSQL服务器的一些基本管理,这里我们再对MYSQL服务器的服务器变量和状态变量做个简单介绍。

查询MYSQL服务器系统变量:
C:\Program Files\MySQL\MySQL Server 5.0\bin> mysqld --verbose –help
通过mysqladmin命令来查询MYSQL服务器系统变量:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqladmin -uroot -p variables > d:\init.txt
Enter password: ******

Init.txt部分内容:
+---------------------------------+----------------------------------------------------------------+
| Variable_name                   | Value                                                          |
+---------------------------------+----------------------------------------------------------------+
| auto_increment_increment        | 1                                                              |
| auto_increment_offset           | 1                                                              |
| automatic_sp_privileges         | ON                                                             |
| back_log                        | 50

…………
| version_compile_machine         | ia32                                                           |
| version_compile_os              | Win32                                                          |
| wait_timeout                    | 28800                                                          |
+---------------------------------+----------------------------------------------------------------+


获得MYSQL实际使用的服务器系统变量:
mysql> show variables;
利用like参数来显示具体的服务器系统变量:
mysql> show variables like 'init_connect%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| init_connect  |       |
+---------------+-------+
1 row in set (0.00 sec)

调整MYSQL服务器的系统变量

mysqld服务器维护两种变量。全局变量影响服务器的全局操作。会话变量影响具体客户端连接相关操作。服务器启动时,将所有全局变量初始化为默认值。可以在选项文件或命令行中指定的选项来更改这些默认值。服务器启动后,通过连接服务器并执行SET GLOBAL var_name语句可以更改动态全局变量。要想更改全局变量,必须具有SUPER权限。

方法一:
mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 23068672 |
+------------------+----------+
1 row in set (0.01 sec)

mysql> SET GLOBAL query_cache_size = 31457280;
Query OK, 0 rows affected (0.00 sec)

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


方法二:

mysql> show variables like 'query_cache_size%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 31457280 |
+------------------+----------+
1 row in set (0.00 sec)

mysql> SET @@global.query_cache_size = 20971520;
Query OK, 0 rows affected (0.09 sec)

mysql> show variables like 'query_cache_size%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 20971520 |
+------------------+----------+
1 row in set (0.00 sec)

mysql> select @@query_cache_size;
+--------------------+
| @@query_cache_size |
+--------------------+
|           20971520 |
+--------------------+
1 row in set (0.06 sec)

mysqld服务器还为每个客户端连接维护会话变量。连接时使用相应全局变量的当前值对客户端会话变量进行初始化。客户可以通过SET [SESSION] var_name语句来更改动态会话变量。设置会话变量不需要特殊权限,但客户可以只更改自己的会话变量,而不更改其它客户的会话变量。

mysql> SET sort_buffer_size = 10 * 1024 * 1024;
Query OK, 0 rows affected (0.08 sec)

mysql> show variables like 'sort_buffer%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| sort_buffer_size | 10485760 |
+------------------+----------+
1 row in set (0.00 sec)

注意,当使用启动选项设置变量时,变量值可以使用后缀K、M或G分别表示千字节、兆字节或gigabytes。例如,下面的命令启动服务器时的键值缓冲区大小为16 megabytes:
C:\ProgramFiles\MySQL\MySQL Server 5.0\bin>mysqld--key_buffer_size=16M
后缀的大小写美关系;16M和16m是同样的。

运行时,使用SET语句来设置系统变量。此时,不能使用后缀,但值可以采取下列表达式:
mysql> SET sort_buffer_size = 10 * 1024 * 1024;

1.2 MYSQL服务器状态变量

mysqladmin查看服务器状态变量(动态变化):

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqladmin -uroot -p extended-status

Enter password: ******
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 0        |
| Aborted_connects                  | 3        |
| Binlog_cache_disk_use             | 0        |
| Binlog_cache_use                  | 0        |
| Bytes_received                    | 2664     |
| Bytes_sent                        | 96723    |
| Com_admin_commands                | 0        |
| Com_alter_db                      | 0        |
| Com_alter_table                   | 0        |
| Com_analyze                       | 0        |


该命令和下面命令等效:
//获得MYSQL服务器的统计和状态指标
mysql> show status;
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 0        |
| Aborted_connects                  | 3        |
| Binlog_cache_disk_use             | 0        |
| Binlog_cache_use                  | 0        |
| Bytes_received                    | 765      |
| Bytes_sent                        | 80349    |
| Com_admin_commands                | 0        |
| Com_alter_db                      | 0        |
| Com_alter_table                   | 0        |
| Com_analyze                       | 0        |

//刷新MYSQL服务器状态变量
mysql> show status like 'Bytes_sent%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Bytes_sent    | 53052 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Bytes_sent%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Bytes_sent    | 11    |
+---------------+-------+
1 row in set (0.00 sec)

1.3 MYSQL服务器关键参数优化

key_buffer_size

这个参数对MyISAM表来说非常重要。如果我们的系统只是使用MyISAM表,可以把它设置为操作系统物理内存的 30-40%。取决于索引大小、数据量以及负载,MyISAM表会使用操作系统的缓存来缓存数据,因此需要留出部分内存给它们,很多情况下数据比索引大多了。尽管如此,需要总是检查是否所有的 key_buffer都被利用了 。如果*.MYI 文件只有 1GB,而 key_buffer 却设置为 4GB 的话就太浪费了。如果没有MyISAM表,那么也保留16-32MB 的 key_buffer_size 以供临时表索引使用,也就是不要禁止这个参数(设置为0)。

我们下边给出几个比较重要的MYSQL服务器参数,这里我们着重讲述InooDB引擎相关的参数。

innodb_buffer_pool_size

该参数对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以,然而Innodb在默认的innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb并且系统地数据量非常大的话则可以设置它高达 70-80% 的可用内存。

有些总结中说到,在 Linux x86 上不要把内存设置太高,内存使用下面的加起来不要超过2G,这个我们提醒一下。

innodb_buffer_pool_size+ key_buffer_size+
max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB < 2G

innodb_additional_mem_pool_size

InnoDB 用来存储数据字典(data dictionary)信息和其它内部数据结构(internal data structures)的存储器组合(memory pool)大小。理想的值为 2M,如果有更多的表你就需要在这里重新分配。如果 InnoDB 用尽这个池中的所有内存,它将从操作系统中分配内存,并将错误信息写入 MySQL 的错误日志中。在 my.ini 中以数字格式设置。

innodb_log_file_size

在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高,但是要注意到可能会增加恢复时间。我们经常设置为 64-512MB,根据服务器文件系统大小而异。

innodb_log_buffer_size

默认的设置在中等强度写入负载以及较短事务的情况下,服务器性能还可以。如果存在更新操作峰值或者负载较大,就应该考虑加大它的值了。如果它的值设置太高了,可能会浪费内存 -- 它每秒都会刷新一次,因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。

innodb_flush_log_at_trx_commit

是否为Innodb比MyISAM慢1000倍而头大?看来也许你忘了修改这个参数了。默认值是 1,这意味着每次提交的更新事务(或者每个事务之外的语句)都会刷新到磁盘中,而这相当耗费资源。很多应用程序,尤其是从 MyISAM转变过来的那些,把它的值设置为 2 就可以了,也就是不把日志刷新到磁盘上,而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去,因此通常不会丢失每秒1-2次更新的消耗。如果设置为 0 就快很多了,不过也相对不安全了 -- MySQL服务器崩溃时就会丢失一些事务。设置为 2 只会丢失刷新到操作系统缓存的那部分事务。

innodb_log_files_in_group

日志组中的日志文件数目。InnoDB 以环型方式(circular fashion)写入文件。数值 3 被推荐使用。在 my.ini 中以数字格式设置。

innodb_lock_wait_timeout

在回滚(rooled back)之前,InnoDB 事务将等待超时的时间(单位 秒)。InnoDB 会自动检查自身在锁定表与事务回滚时的事务死锁。如果使用 LOCK TABLES 命令,或在同一个事务中使用其它事务安全型表处理器(transaction safe table handlers than InnoDB),那么可能会发生一个 InnoDB 无法注意到的死锁。在这种情况下超时将用来解决这个问题。这个参数的默认值为 50 秒。在 my.ini 中以数字格式设置。

table_cache

打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁,所以通常要加大缓存数量,使得足以最大限度地缓存打开的表。它需要用到操作系统的资源以及内存,对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话,那么设置为 1024 也许比较合适(每个线程都需要打开表),如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。

tmp_table_size

如果内存内的临时表超过该值,MySQL自动将它转换为硬盘上的MyISAM表,所以我们在前边讲述key_buffer_size的时候曾经说过即使我们的系统中没有MyISAM表也要保留key_buffer_size的值为16-32M。如果系统有很多GROUP BY查询并且有大量内存,则可以增加tmp_table_size的值。

thread_cache

线程的创建和销毁的开销可能很大,因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created的值也比较大,那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。

query_cache

如果你的应用程序有大量读,而且没有应用程序级别的缓存,那么这很有用。不要把它设置太大了,因为想要维护它也需要不少开销,这会导致MySQL变慢。通常设置为 32-512Mb。设置完之后最好是跟踪一段时间,查看是否运行良好。在一定的负载压力下,如果缓存命中率太低了,就启用它。

max_connections

允许的并行客户端连接数目。根据系统的连接数来决定该参数的大小。

sort_buffer

就像你看到的上面这些全局表量,它们都是依据硬件配置以及不同的存储引擎而不同,但是会话变量通常是根据不同的负载来设定的。如果你只有一些简单的查询,那么就无需增加 sort_buffer_size 的值了,尽管你有 64GB 的内存。搞不好也许会降低性能。我通常在分析系统负载后才来设置会话变量。

innodb_file_io_threads

InnoDB 中的文件 I/O 线程。 通常设置为 4,但是在 Windows 下可以设定一个更大的值以提高磁盘 I/O。在 my.ini 中以数字格式设置。

innodb_fast_shutdown

如果把这个参数设置为0,InnoDB在关闭之前做一个完全净化和一个插入缓冲合并。这些操作要花几分钟时间,设置在极端情况下要几个小时。如果你设置这个参数为1,InnoDB在关闭之时跳过这些操作。默认值为1。如果你设置这个值为2 (在Netware无此值), InnoDB将刷新它的日志然后冷关机,仿佛MySQL崩溃一样。已提交的事务不会被丢失,但在下一次启动之时会做一个崩溃恢复。

innodb_max_dirty_pages_pct

这是一个范围从0到100的整数。默认是90。InnoDB中的主线程试着从缓冲池写页面,使得脏页(没有被写的页面)的百分比不超过这个值。如果你有SUPER权限,这个百分比可以在服务器运行时按下面来改变:
SET GLOBAL innodb_max_dirty_pages_pct = value;

innodb_thread_concurrency

InnoDB试着在InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制。如果有性能问题,并且SHOW INNODB STATUS显示许多线程在等待信号,可以让线程“thrashing” ,并且设置这个参数更小或更大。如果你的计算机有多个处理器和磁盘,你可以试着这个值更大以更好地利用计算机的资源。一个推荐的值是系统上处理器和磁盘的个数之和。值为500或比500大会禁止调用并发检查。默认值是20,并且如果设置大于或等于20,并发检查将被禁止。

THREAD_STAC

每个线程的堆栈大小。用crash-me测试检测出的许多限制取决于该值。 默认值足够大,可以满足普通操作

2 一个函数调试工具

如果我们的问题只是与具体MySQL表达式或函数有关,我们可以使用mysql客户程序所带的BENCHMARK()函数执行定时测试。其语法为:
BENCHMARK(loop_count,expression)

mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.13 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.03 sec)

本文作者:



相关阅读:
SQL语句实现SQL Server 2000及Sql Server 2005日志收缩(批量)
用PHP的zlib压缩和解压缩swf文件
SQL 实用语句
asp.net中Request.QueryString与Request.Param的区别分析
用VBS实现一个小键盘动作CTRL+V粘贴操作与思路分析
Linux上最常用的用户名和密码 有的快改
调查结果:Linux系统最需要的是电脑游戏
SQL Sever性能怎样全方位诊断?
Linux 环境搭建推荐教程(php运行环境)
用JQuery和PHP实现无刷新删除数据库数据
让你的ASP.NET虚拟主机也支持子网站
使用ASP控制指定站点解析脚本语言函数
WIN2000+PHP+MYSQL+TOMCAT+JSP完全整合安装手册
AJAX 二级级联菜单实现代码
快速导航

Copyright © 2016 phpStudy |