作者:
IT168 周卫丰 2007-08-08
【IT168 开源数据库】我们在前面已经简单介绍了一些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
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
…………
|
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) ![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
mysql
>
SET
GLOBAL query_cache_size
=
31457280
;
Query OK,
0
rows affected (
0.00
sec) ![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
mysql
>
SHOW VARIABLES
LIKE
'
query_cache_size
'
;
+
--
----------------+----------+
|
Variable_name
|
Value
|
+
--
----------------+----------+
|
query_cache_size
|
31457280
|
+
--
----------------+----------+
1
row
in
set
(
0.00
sec) ![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
方法二:
mysql
>
show variables
like
'
query_cache_size%
'
;
+
--
----------------+----------+
|
Variable_name
|
Value
|
+
--
----------------+----------+
|
query_cache_size
|
31457280
|
+
--
----------------+----------+
1
row
in
set
(
0.00
sec) ![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
mysql
>
SET
@@global
.query_cache_size
=
20971520
;
Query OK,
0
rows affected (
0.09
sec) ![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
mysql
>
show variables
like
'
query_cache_size%
'
;
+
--
----------------+----------+
|
Variable_name
|
Value
|
+
--
----------------+----------+
|
query_cache_size
|
20971520
|
+
--
----------------+----------+
1
row
in
set
(
0.00
sec) ![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
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) ![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
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 优化MySQL服务器
1.1 MYSQL服务器系统变量
我们在前面的章节中曾经讲到过MYSQL服务器的一些基本管理,这里我们再对MYSQL服务器的服务器变量和状态变量做个简单介绍。
查询MYSQL服务器系统变量:
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
获得MYSQL实际使用的服务器系统变量:
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
mysqld服务器维护两种变量。全局变量影响服务器的全局操作。会话变量影响具体客户端连接相关操作。服务器启动时,将所有全局变量初始化为默认值。可以在选项文件或命令行中指定的选项来更改这些默认值。服务器启动后,通过连接服务器并执行SET GLOBAL var_name语句可以更改动态全局变量。要想更改全局变量,必须具有SUPER权限。
方法一:
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
运行时,使用SET语句来设置系统变量。此时,不能使用后缀,但值可以采取下列表达式:
![](http://cms.it168.com/Images/OutliningIndicators/None.gif)
【内容导航】
第1页: MYSQL服务器系统变量 | 第2页: MYSQL服务器状态变量 |
第3页: MYSQL服务器关键参数优化 | 第4页: 一个函数调试工具 |
第5页: MYSQL数据库的SQL执行计划解释器 | 第6页: |