服务器物理硬件优化

(1)增加内存。
(2)磁盘I/O是制约MySQL性能的最大因素之一 。
给磁盘做raid1+0（先做镜像，再做条带比如8块盘，需要分成4个基组，每个基组2块盘；每个基组先做raid1，再做raid0，共1个条带；允许所有磁盘基组中的磁盘各损坏一个，但是不允许同一基组中的磁盘同时有坏的）
使用固态硬盘做raid1+0
(3)可以重新挂载磁盘，并加上noatime参数，这样可以大幅度减少磁盘的i/o;
/dev/sda1 /data ext3 defaults,noatime,errors=remount-ro 0 0
mount -o remount /data

MySQL本身调优

(1) 如果未配置主从同步，可以把bin-log功能关闭，减少磁盘I/O
(2) 在my.cnf中加上skip-name-resolve,这样可以避免由于解析主机名延迟造成mysql执行慢

应用层次

查看慢查询日志，（show variables like '%slow%';）根据慢查询日志优化程序中的SQL语句，比如增加索引,开启慢查询会影响性能，建议打开从服务器上的慢查询。

调整几个关键的buffer和cache

1) key_buffer_size 此参数对MyISAM表性能影响最大，首先可以根据系统的内存大小设定它，大概的一个参考值：1G以下内存设定128M；2G/256M; 4G/384M;8G/1024M；16G/2048M.这个值可以通过检查状态值Key_read_requests和 Key_reads(show global status like 'key_read%';),可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低(这个比例值就是索引未命中缓存的概率)，至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。注意：该参数值设置的过大反而会使服务器整体效率降低!

show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 536870912 |
+-----------------+----------+
1 row in set (0.00 sec)

从上面的输出可以看出，分配了512M的内存给key_buffer_size，我们再看一下key_buffer_size的使用情况

show global status like 'key_read%';
+-------------------+-------------+
| Variable_name | Value |
+-------------------+-------------+
| Key_read_requests | 27234324768 |
| Key_reads | 6785489 |
+-------------------+-------------+
2 rows in set (0.00 sec)

一共有27234324768个索引请求，有6785489个请求在内存中没找到，没命中
缓存命中率计算
key_cache_rate = (Key_read_requests - Key_reads )/Key_read_requests
计算出的结果为 0.9997508479076385
这是一个很高的命中率了。

 

2) table_open_cache 打开一个表的时候，会临时把表里面的数据放到这部分内存中，一般设置成1024就够了，它的大小我们可以通过这样的方法来衡量： 如果你发现 open_tables等于table_cache，（open_tables表示打开表的数量，opened_tables表示打开过的表数量）并且opened_tables在不断增长，那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
比较适合的值为：
Open_tables/Opened_tables *100 >=85%
Open_tables/table_cache*100<=95%   3) sort_buffer_size 查询排序时所能使用的缓冲区大小,该参数对应的分配内存是每连接独占!如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 4 = 400MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。(show global status like 'sort%';)   4) read_buffer_size = 4M 读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!   5) join_buffer_size = 8M 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享!   6) myisam_sort_buffer_size = 64M 这个缓冲区主要用于修复表过程中排序索引使用的内存或者是建立索引时排序索引用到的内存大小，一般4G内存给64M即可。   7) query_cache_size MySQL查询操作缓冲区的大小，通过以下做法调整：SHOW STATUS LIKE ‘Qcache%’; 如果Qcache_lowmem_prunes该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。如果该值非常大，则表明经常出现缓冲不够的情况，需要增加缓存大小;Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，我们可以根据实际情况做出调整。一般情况下4G内存设置64M足够了。   8) thread_cache_size 当客户端断开时，服务器处理此客户端请求的线程将会缓存起来以响应下一个客户而不是销毁，threads_created表示创建过的线程数。参考如下值：1G —> 8 2G —> 16 3G —> 32 >3G —> 64

show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 1 |
| Threads_created | 206 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)

若发现Threads_connected的值过大，表明MySQL服务一直在创建线程，这也是比较耗资源的，会导致服务器整体负载高，可以适当增大配置文件中Thread_cache_size的值，查询服务器thread_cache_size配置：
show variables like 'thread_cache_size';

 

9) thread_concurrency 这个值设置为cpu核数的2倍即可

 

10) wait_timeout 表示空闲的连接超时时间，默认是28800s，这个参数是和interactive_timeout一起使用的，也就是说要想让wait_timeout 生效，必须同时设置interactive_timeout，建议他们两个都设置为10

11) max_connect_errors 是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。与性能并无太大关系。为了避免一些错误我们一般都设置比较大，比如说10000

12) max_connections 最大的连接数，根据业务请求量适当调整，设置500足够
set GLOBAL max_connections=500

show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+
1 row in set (0.00 sec)

查询一下该服务器以前承受的最大连接数

show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 241 |
+----------------------+-------+

MySQL服务器过去的最大连接数是241，没有超过上限256，这样就不会出现1040错误，比较理想的设置是：
Max_used_connections/max_connections *100% ≈ 85%

 

13) max_user_connections 是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。通常我们设置为100足够

 

14) open_files 文件打开数大于open_files_limit值时，MySQL数据库就会卡住，导致Apache服务器也打不开相应页面：

show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+----------+
| Open_files | 1390 |
+---------------+----------+
1 row in set (0.00 sec)

show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+

比较合适的设置是：
Open_files/open_files_limit *100% <=75% 15)Innodb_buffer_pool_size的合理设置（show variables like 'Innodb_buffer_pool_%';） InnoDB存储引擎的缓存机制和MyISAM的最大区别就是，InnoDB不仅仅缓存索引，同时还缓存实际的数据，此参数用来设置InnoDB最主要的buffer（InnoDB buffer pool）的大小，对InnoDB整体性能影响也最大。

mysql> show status like 'Innodb_buffer_pool_%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status        | not started |
| Innodb_buffer_pool_load_status        | not started |
| Innodb_buffer_pool_pages_data         | 625         |
| Innodb_buffer_pool_bytes_data         | 10240000    |
| Innodb_buffer_pool_pages_dirty        | 0           |
| Innodb_buffer_pool_bytes_dirty        | 0           |
| Innodb_buffer_pool_pages_flushed      | 14261       |
| Innodb_buffer_pool_pages_free         | 7563        |
| Innodb_buffer_pool_pages_misc         | 3           |
| Innodb_buffer_pool_pages_total        | 8191        |
| Innodb_buffer_pool_read_ahead_rnd     | 0           |
| Innodb_buffer_pool_read_ahead         | 0           |
| Innodb_buffer_pool_read_ahead_evicted | 0           |
| Innodb_buffer_pool_read_requests      | 21526964    |
| Innodb_buffer_pool_reads              | 580         |
| Innodb_buffer_pool_wait_free          | 0           |
| Innodb_buffer_pool_write_requests     | 49451       |
+---------------------------------------+-------------+

读缓存命中为：
（21526964-580）/21526964=99.99%
写缓存命中为：
625/8191=7.63%
这个数据是我自己的个人博客上的，Innodb_buffer_pool_size的值只有128M，而且博客写入数据库的内容很少重复，所以命中率这么低。

脚本tuning-primer.sh

最后，MySQL有一个调优脚本tuning-primer.sh，诸位可以参考一下两个链接
mysql脚本优化工具tuning-primer.sh使用教程说明
运行 tuning-primer.sh显示说明