MySQL 优化

服务器优化原则：

1.内存里的数据要比磁盘上的数据访问起来快；
2.让数据尽可能长时间地留在内存里能减少磁盘读写活动的工作量；
3.让索引信息留在内存里要比让数据记录的内容留在内存里更重要。

针对以上几个原则，我们应该调整服务器：
增加服务器的缓存区容量，以便数据在内存在停留的时间长一点，以减少磁盘I/0。下面介绍几个重要的缓冲区：

===========================================================================================

1.数据表缓冲区(table_cache)

数据表缓冲区存放着与打开的数据表相的信息，它的大小可由服务器参数“table_cache”设置。Opened_tables参数 记录服务器进行过多少次
数据表打开操作，如果该值变化很大，就可能是数据表缓冲区已满，需把一些不常用的表移出缓冲区，以腾出空打开新的数据表。可用以下命令
查看Opened_tables的值： SHOW STATUS LIKE 'Opened_tables';

说明(针对某个表只记录一次.重复对一个表不记录.)

[root@localhost ~]# cat /etc/my.cnf
table_cache = 256

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




显示打开表的活动
mysql> SHOW STATUS LIKE 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 5000   |
| Opened_tables | 195 |
+---------------+-------+
2 rows in set (0.00 sec)


说明
目前有 5,000 个表是打开的，有 195 个表需要打开，因为现在缓存中已经没有可用文件描述符了（由于统计信息在前面已经清除了，
因此可能会存在 5,000 个打开表中只有 195 个打开记录的情况）。如果 Opened_tables 随着重新运行 SHOW STATUS 命令快速增加，
就说明缓存命中率不够。如果 Open_tables 比 table_cache 设置小很多，就说明该值太大了（不过有空间可以增长总不是什么坏事）。
例如，使用 table_cache = 5000 可以调整表的缓存。

============================================================================================================

2.索引缓存(key_buffer)

在MyISAM和ISAM数据表中，索引被缓存在“key buffer”里，它的大小由服务器参数“key_buffer_size”来控制。系统默认的大小是8M，如果
内存充足的话可适当扩大该值，以使更多索引块缓存在该区里，以加快索引的速度。
InnoDB和BDB数据表也各有一个缓冲区，分别叫innodb_buffer_pool_size和bdb_cache_size。InnoDB还有 一个日志缓冲区叫innodb_log_buffer_size。
说明(myisam属于非事务安全型，innodb和bdb属于事务安全型)

[root@localhost ~]# cat /etc/my.cnf
#bdb_cache_size = 64M
key_buffer = 256M (内存的一半或1/4)
#innodb_buffer_pool_size = 256M



确定关键字效率
           
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 代表命中磁盘的请求个数， Key_read_requests 是总数。命中磁盘的读请求数除以读请求总数就是不中比率 ——
在本例中每 1,000 个请求，大约有 0.6 个没有命中内存。如果每 1,000 个请求中命中磁盘的数目超过 1 个，就应该考虑增大关键字缓冲区了。
例如，key_buffer = 384M 会将缓冲区设置为 384MB
Key_reads与Key_read_requests之比越小越好

========================================================================================================================



3.查询缓冲区(query_cache_size)

自4.0.1开始，MySQL多了一个缓冲区，叫查询缓冲区，主要用来存放重复执行的查询文本和结果，当再次遇到相同的查询，服务器会 直接从缓冲区中返回结果。
该功能是内建的功能，如不想支持该功能，可在编译服务器时用configure脚本的--without-query-cache选项去掉该功能。
查询缓冲区由三个服务器参数控制，分别是：

1、query_cache_size
控制缓冲区的大小，如果该值为0,则禁用查询缓冲功能。设置方法是在选项文件中设置:
[mysqld]
set-variable = query_cache_size = 16M    
这样就设置了一个16M的查询缓冲区

2、query_cache_limit
缓冲结果集的最大容量(以字节为单位)，如果查询的结果集大于该值，则不缓冲该值。

3、query_cache_type
缓冲区的操作模式。
0表示不进行缓冲；
1表示除SELECT SQL_NO_CACHE开头的查询外，其余的都缓冲；
2表示只对以SELECT SQL_ON_CACHE开头的查询进行缓冲。


[root@localhost ~]# cat /etc/my.cnf
#query_cache_type=0
query_cache_size= 16M
query_cache_limit=2M



显示查询缓冲区的内容
mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value |
+-------------------------+----------+
| Qcache_free_blocks    | 1        |
| Qcache_free_memory    | 16768448 |
| Qcache_hits          | 0        |
| Qcache_inserts       | 0        |
| Qcache_lowmem_prunes | 0        |
| Qcache_not_cached    | 13    |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.01 sec)

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


说明:
MySQL 查询缓存变量
变量名                         说明
Qcache_free_blocks             缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory             缓存中的空闲内存。
Qcache_hits                   每次查询在缓存中命中时就增大。
Qcache_inserts                每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；用 1 减去这个值就是命中率。在上面这个例子中，大约有 87% 的查询都在缓存中命中。
Qcache_lowmem_prunes          缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存 很少。
                               （上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）。
Qcache_not_cached             不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。
Qcache_queries_in_cache       当前缓存的查询（和响应）的数量。
Qcache_total_blocks              缓存中块的数量。

通常，间隔几秒显示这些变量就可以看出区别，这可以帮助确定缓存是否正在有效地使用。运行 FLUSH STATUS
可以重置一些计数器，如果服务器已经运行了一段时间，这会非常有帮助。使用非常大的查询缓存，期望可以缓存所有东西，这种想法非常诱人。
由于 mysqld 必须要对缓存进行维护，例如当内存变得很低时执行剪除，因此服务器可能会在试图管理缓存时而陷入困境。
作为一条规则，如果 FLUSH QUERY CACHE 占用了很长时间，那就说明缓存太大了。


============================================================================================

4.线程缓存(thread_cache)
root@localhost ~]# cat /etc/my.cnf
thread_cache = 40


显示线程使用统计信息
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)

说明:
与表的缓存类似，对于线程来说也有一个缓存。 mysqld 在接收连接时会根据需要生成线程。在一个连接变化很快的繁忙服务器上，
对线程进行缓存便于以后使用可以加快最初的连接。此处重要的值是 Threads_created，每次 mysqld 需要创建一个新线程时，
这个值都会增加。如果这个数字在连续执行 SHOW STATUS 命令时快速增加，就应该尝试增大线程缓存。
例如，可以在 my.cnf 中使用 thread_cache = 40 来实现此目的。

=================================================================================================


5.sort_buffer_size(排序操作使用的缓冲区大小)

每个会话的设置

下面这些设置针对于每个会话。在设置这些数字时要十分谨慎，因为它们在乘以可能存在的连接数时候，这些选项表示大量的内存！
您可以通过代码修改会话中的这些数字，或者在 my.cnf 中为所有会话修改这些设置。

当 MySQL 必须要进行排序时，就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。如果要排序的数据太大，
那么数据就必须保存到磁盘上的临时文件中，并再次进行排序。如果 sort_merge_passes 状态变量很大，这就指示了磁盘的活动情况。

给出了一些与排序相关的状态计数器信息。


清单 8. 显示排序统计信息
           
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 = 4M 将排序缓冲区设置为 4MB。


说明:
sort_buffer_size = 6M
查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占！如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。
所以，对于内存在4GB左右的服务器推荐设置为6-8M

==============================================================================================================


6.read_buffer_size

当一个查询不断地扫描某一个表，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如 果你认为连续扫描进行得太慢，
可以通过增加该变量值以及内存缓冲区大小提高其性能。
           
确定表扫描比率
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，就应该与开发人员讨论一下对这些查询进行调优了！

说明:
读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享

==============================================================================================================


7.join_buffer_size = 8M
联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享！
========================================================================================================


8.thread_concurrency = 8
该参数取值为服务器逻辑CPU数量×2，在本例中，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际 取值为4 × 2 ＝ 8
=============================================================================================================

9.确定临时表的使用(max_heap_table_size)

临时表可以在更高级的查询中使用，其中数据在进一步进行处理（例如 GROUP BY 字句）之前，都必须先保存到临时表中；
理想情况下，在内存中创建临时表。但是如果临时表变得太大，就需要写入磁盘中。清单 7 给出了与临时表创建有关的统计信息。

[root@localhost ~]# mysql
Welcome to the MySQL monitor.   Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.10a-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files    | 3     |
| Created_tmp_tables    | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql>


每次使用临时表都会增大 Created_tmp_tables；基于磁盘的表也会增大 Created_tmp_disk_tables。对于这个比率，并没有什么严格的规则，
因为这依赖于所涉及的查询。长时间观察 Created_tmp_disk_tables 会显示所创建的磁盘表的比率，您可以确定设置的效率。 tmp_table_size
和 max_heap_table_size 都可以控制临时表的最大大小，因此请确保在 my.cnf 中对这两个值都进行了设置。

=============================================================================================


10.back_log

指定MySQL可能的连接数量。当MySQL主线程在很短的时间内接收到非常多的连接请求，该参数生效，主线程花费很短的时间检查连接 并且启动一个新线程。
back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接，则需要增大 该参数的值，
该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的*作系统在这个队列大小上有它自 己的限制。
试图设定back_log高于你的*作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数
=============================================================================================



11.skip-networking
开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB 服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！
==============================================================================================



12.skip-name-resolve
禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除 MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，
否则MySQL将无法正常处理连接请求！
三.MySQL 资源设置
           
max_connections=500
wait_timeout=10
max_connect_errors = 100

连接最大个数是在第一行中进行管理的。与 Apache 中的 MaxClients 类似，其想法是确保只建立服务允许数目的连接。要确定服务器上目前建立过的最大连接数，请执行
SHOW STATUS LIKE 'max_used_connections'。

第 2 行告诉 mysqld 终止所有空闲时间超过 10 秒的连接。在 LAMP 应用程序中，连接数据库的时间通常就是 Web 服务器处理请求所花费的时间。有时候，如果负载过重，
连接会挂起，并且会占用连接表空间。如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设低一点并不可取！

最后一行是一个安全的方法。如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到 FLUSH HOSTS 之后才能运行。默认情况下，
10 次失败就足以导致锁定了。将这个值修改为 100 会给服务器足够的时间来从问题中恢复。如果重试 100 次都无法建立连接，那么使用再高的值也不会有太多帮助，
可能它根本就无法连接。


====================================================================================================


四.MySQL 服务器参数

服务器参数分为全局级和会话级两个级别。全局级参数将影响整个服务器，会话级参数则只影响某给定客户连接上的工作。
如果某个变量同时存在于两个级别，则服务器在客户建立连接时用全局变量的值去初始化相应的会话级参数，一旦客户连接建立起来后，
对全局参数所作的修改不会影响到相应的会话级参数当前值。设置全局参数和会话级参数的语句：

全局级：
mysql> SET GLOBAL variable = value;
mysql> SET @@GLOBAL.variable = value;

会话级：
mysql> SET SESSION variable = value;
mysql> SET @@SESSION.variable = value;

默认不带级别限定符的SET语句修改的参数属会话级，如：
mysql> SET variable = value;
mysql> SET @@variable = value;

可用一条SET语句设置多个参数，参数间用逗号分隔，如：
SET SESSION variable = value1,value2,value3;

SESSION和LOCAL是同义语，可用LOCAL代替SESSION，如：@@LOCAL


显示参数的语句如下
SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'TEST';
SHOW SESSION VARIABLES;
SHOW SESSION VARIABLES LIKE 'TEST';
如不带限定符，则返回会话级参数，如会话级参数不存在则返回全局级参数。

也可用命令行方式显示服务器参数变量(全局)，如：
% mysqladmin variables


例如:
[root@localhost mysql]# mysql
Welcome to the MySQL monitor.   Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.10a-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set session wait_timeout=2880;
Query OK, 0 rows affected (0.00 sec)

mysql> set global wait_timeout=2880;
Query OK, 0 rows affected (0.00 sec)

mysql> set @@session.wait_timeout=288;
Query OK, 0 rows affected (0.00 sec)

mysql> set @@local.wait_timeout=28800;
Query OK, 0 rows affected (0.00 sec)

说明:
具备SUPER权限才能设置全局参数，新设置值的效力将持续到该参数被再次修改或服务器退出。设置会话级参数不用特殊的权限，
新设置值的效力将持续到该值被再次修改或连接断开。
===========================================================================================

五.MySQL 常用命令
show index from tb1   
show [full] processlist
show status            
show table status
show variables   
================================================================
Linux下解决MySQL打开文件数错误
以前提示mysql的连接数不够用,修改max_connections以后,最近服务器常出现 Can't open file: './yejr/access.frm' (errno: 24)的错误.

　　原来是打开文件太多 了, 好办.用sysctl来调整一下就好了:
　　[root@yejr]# sysctl -w fs.file-max=43621
　 　[root@yejr]# sysctl -a | grep fs.file-max
　　fs.file-max = 43621

　 　FreeBSD 下也用sysctl来调整:
　　[root@yejr]# sysctl -w kern.maxfiles=123280
　 　[root@yejr]# sysctl -a | grep kern.maxfiles
　　kern.maxfiles = 123280

　 　最后, 还有最重要的一点是, 修改 mysqld 的配置文件 my.cnf, 增加如下一行:

　　 open_files_limit = 4096
　　#根据自己的情况适当调整,系统默认值是
　　# max_connections*5 或 max_connections + table_cache*2

　　然后, 以root身份重新启动 mysqld. 在这里, 尽管 my.cnf 中指定的运行用户不是root, 一样可以以root身份来启动mysqld, 否则 open_files_limit 选项无法生效, 因为内核限制了普通用户的最多打开文件数.