搜索
安装MySQL后，配置文件my.cnf在/MySQL安装目录/share/mysql目录中，该目录中还包含多个配置文件可供参考，有my-large.cnf，my-huge.cnf，my-medium.cnf，my-small.cnf，分别对应大中小型数据库应用的配置。win环境下即存在于MySQL安装目录中的.ini文件。下面列出了对性能优化影响较大的主要变量，主要分为连接请求的变量和缓冲区变量。1.连接请求的变量：1)max_connectionsMySQL的最大连接数，增加该值增加mysqld要求的文件描述符的数量。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。数值过小会经常出现ERROR1040:Toomanyconnections错误，可以过’conn%’通配符查看当前状态的连接数量，以定夺该值的大小。showvariableslike‘max_connections’最大连接数showstatuslike‘max_used_connections’响应的连接数如下：mysqlshowvariableslike‘max_connections‘;+———————–+——-+|Variable_name|Value|+———————–+——-+|max_connections|256|+———————–+——-+mysqlshowstatuslike‘max%connections‘;+———————–+——-+|Variable_name|Value|+—————————-+——-+|max_used_connections|256|+—————————-+——-+max_used_connections/max_connections*100%（理想值≈85%）如果max_used_connections跟max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。2)back_logMySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。当观察你主机进程列表（mysqlshowfullprocesslist），发现大量264084|unauthenticateduser|xxx.xxx.xxx.xxx|NULL|Connect|NULL|login|NULL的待连接进程时，就要加大back_log的值了。默认数值是50，可调优为128，对于Linux系统设置范围为小于512的整数。3)interactive_timeout一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE选项的客户。默认数值是28800，可调优为7200。2.缓冲区变量全局缓冲：4)key_buffer_sizekey_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOWSTATUSLIKE‘key_read%’获得）。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。举例如下：mysqlshowvariableslike‘key_buffer_size‘;+——————-+————+|Variable_name|Value|+———————+————+|key_buffer_size|536870912|+———————-+————+key_buffer_size为512MB，我们再看一下key_buffer_size的使用情况：mysqlshowglobalstatuslike‘key_read%‘;+————————+————-+|Variable_name|Value|+————————+————-+|Key_read_requests|27813678764||Key_reads|6798830|+————————+————-+一共有27813678764个索引读取请求，有6798830个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：key_cache_miss_rate＝Key_reads/Key_read_requests*100%，设置在1/1000左右较好默认配置数值是8388600(8M)，主机有4GB内存，可以调优值为268435456(256MB)。5)query_cache_size使用查询缓冲，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。通过检查状态值Qcache_*，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOWSTATUSLIKE‘Qcache%’获得）。如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query_cache_min_res_unit。举例如下：mysqlshowglobalstatuslike‘qcache%‘;+——————————-+—————–+|Variable_name|Value|+——————————-+—————–+|Qcache_free_blocks|22756||Qcache_free_memory|76764704||Qcache_hits|213028692||Qcache_inserts|208894227||Qcache_lowmem_prunes|4010916||Qcache_not_cached|13385031||Qcache_queries_in_cache|43560||Qcache_total_blocks|111212|+——————————-+—————–+mysqlshowvariableslike‘query_cache%‘;+————————————–+————–+|Variable_name|Value|+————————————–+———–+|query_cache_limit|2097152||query_cache_min_res_unit|4096||query_cache_size|203423744||query_cache_type|ON||query_cache_wlock_invalidate|OFF|+————————————–+—————+查询缓存碎片率=Qcache_free_blocks/Qcache_total_blocks*100%如果查询缓存碎片率超过20%，可以用FLUSHQUERYCACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。查询缓存利用率=(query_cache_size–Qcache_free_memory)/query_cache_size*100%查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes50的话说明query_cache_size可能有点小，要不就是碎片太多。查询缓存命中率=(Qcache_hits–Qcache_inserts)/Qcache_hits*100%示例服务器查询缓存碎片率＝20.46％，查询缓存利用率＝62.26％，查询缓存命中率＝1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。每个连接的缓冲6)record_buffer_size每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128K)，可改为16773120(16M)7)read_rnd_buffer_size随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。一般可设置为16M8)sort_buffer_size每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDERBY或GROUPBY操作。默认数值是2097144(2M)，可改为16777208(16M)。9)join_buffer_size联合查询操作所能使用的缓冲区大小record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size为每个线程独占，也就是说，如果有100个线程连接，则占用为16M*10010)table_cache表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOWSTATUSLIKE‘Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。1G内存机器，推荐值是128－256。内存在4GB左右的服务器该参数可设置为256M或384M。11)max_heap_table_size用户可以创建的内存表(memory