浅谈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
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)