mysql优化在整体的系统运行过程中，数据库服务器 MySQL 的压力是最大的，不仅占用很多的内存和 cpu 资源，而且占用着大部分的磁盘 io资源，连 PHP 的官方都在声称，说 PHP 脚本 80% 的时间都在等待 MySQL查询返回的结果。由此可见，提高
mysql优化
在整体的系统运行过程中，数据库服务器 MySQL 的压力是最大的，不仅占用很多的内存和 cpu 资源，而且占用着大部分的磁盘 io
资源，连 PHP 的官方都在声称，说 PHP 脚本 80% 的时间都在等待 MySQL
查询返回的结果。由此可见，提高系统的负载能力，降低 MySQL 的资源消耗迫在眉睫。
2.1、修改全站搜索
修改my.ini(my.cnf) ，在 [mysqld]
后面加入一行“ft_min_word_len=1”，然后重启Mysql，再登录网站后台（模块管理->全站搜索）重建全文索引。
2.2、记录慢查询sql语句，修改my.ini(my.cnf)，添加如下代码：
#log-slow-queries
long_query_time = 1 #是指执行超过多久的 sql 会被 log 下来
log-slow-queries = E:/wamp/logs/slow.log
#设置把日志写在那里，可以为空，系统会给一个缺省的文件
#log-slow-queries = /var/youpath/slow.log
linux下 host_name-slow.log
log-queries-not-using-indexes
2.3、mysql缓存的设置
mysql>show variables like
'%query_cache%';
mysql本身是有对sql语句缓存的机制的，合理设置我们的mysql缓存可以降低数据库的io资源。
#query_cache_type= 查询缓存的方式(默认是 ON)
query_cache_size 如果你希望禁用查询缓存，设置
query_cache_size=0。禁用了查询缓存，将没有明显的开销
query_cache_limit 不缓存大于这个值的结果。(缺省为 1M)
2.4、查询缓存的统计信息
mysql> SHOW STATUS LIKE ‘qcache%’;
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
可以重置一些计数器，如果服务器已经运行了一段时间，这会非常有帮助。
2.5、my.ini(my.conf)配置
2.5.1、key_buffer_size = 256M
#
key_buffer_size指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。
对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低！
2.5.2、
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占！如果有100个连接，那么实际分配的总共排序缓冲区大小为100
× 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。
2.5.3、
read_buffer_size = 4M
读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享！
2.5.4、join_buffer_size = 8M
联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享！
2.5.5、
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察：
# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
#
如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况；
#如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；
Qcache_free_blocks，如 果该值非常大，则表明缓冲区中碎片很多
2.5.6、
tmp_table_size = 256M
max_connections = 768
指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many
Connections的错误提示，则需要增大该参数值。
2.5.7、
max_connect_errors = 10000000
wait_timeout = 10
指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
2.5.8、
thread_concurrency = 8
该参数取值为服务器逻辑CPU数量×2，如果服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4 × 2 ＝
8
2.5.9、
skip-networking
开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！
工作方式要点
1. 以查询语句作为key, 以结果集作为值的放key => value方式缓存
2. 直接以查询语句作为key , 不会对查询语句做任何变换， 比如中间的注释， 不同的大小写都会导致有不同的key， 比如
SELECT F_a FROM t_b; 和
SELECT F_a from t_b;是不同的缓存key
3. 不缓存语句中包含有非确定性函数的查询的结果集合， 比如CURRENT_USER(), NOW()等， 对于这类查询，
尽管结果不会被缓存 ，但是当查询缓冲功能打开时， 每次查询时， 还是会去查询缓存区先查看的； 对于这种情况，
用具体的值代替， 比如
DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
DATE_SUB('2007-07-14', INTERVAL 1 DAY)
4. 当某个表被修改了， 会导致与该表有关的所有查询缓存都无效
5. 超过 字节的结果集部会被缓存
用缓存会有哪些效率损失
1. 所有的查询请求执行前会去缓冲区里面看一下是否有匹配项
2. 当一个查询时可以缓存的而没有被缓存，那么查询执行完成后， 会把结果放到缓存中
3. 当某个表被修改后， 与它其有关的所有缓存项都将失效
4. 对于innodb来说， 如果某个表在某个事务中被修改了， 那么不但与该表相关的所有缓存项需要变成无效， 而且在事务提交前，
与该表有关的查询都不能缓存
内存管理
1. 40kb左右的内存用来维护一些数据结构， 其次就是结果集缓池了； 缓存池是一次性从系统分配好的，
因此每次分配块时不会都去调用系统内存分配调用函数
2. 维护的数据有： 表和缓存结果集的对应关系， 缓存结果集， 查询语句集合等
3. 缓存池以(可变大小的)块为单位使用，
每次申请时，从缓存池里分配能够满足要求的尽可能小（不能小于query_cache_min_res_unit）的内存块；
由于分配的内存大小不大可能与数据块大小一样， 因此有一些小的内存块被空置；
时间一长， 就会出现内存碎片 （这个和操作系统上的最佳内存分配方式类似）
什么样缓存命中率好
用缓存的效率损失前面说过了， 不用缓存的话， 意味着每次都需要重新产生结果集； 但到底是那个效率更好，
这个从理论上讲是不容易做出判断的， 而且还受到一些外部因素的影响， 比如缓存能减少查询的时间，
但是不能减少发送数据集的时间， 如果瓶颈在于发送数据的速度的话， 缓存的效果就看不到了。 另外， 有时候高的命中率也不能代笔高的效率，
比如有两个表， t_a有10次查询， 都命中了， 那么命中率是100%；
另一个表有1000000次更新而没有查询， 那么这写更新的开销可能比这个100%的命中率带来的好处要多。
理论上见， 缓存最理想用在哪些产生数据集资源要求多， 而存储资源要求少的查询；比如COUNT(), SUM等， 当然，前提是表数据不是频繁变化。
一个判断缓存效果的方式是看缓存命中率 Qcache_hits / (Qcache_hits+Com_select)，
但这个值多少合适就需要根据实际情况而定了， 这个和所缓存的查询有关， 也和所缓存的查询的结果集有关。也和服务器所处的阶段有关，
比如预热阶段的命中率比较低是正常的。 当然， 缓存命中率低的一些原因有:
1. 查询是不能被缓存的， 比如有CRRRENT_DATE的函数
2. 总是新的查询语句， 比如预热阶段
3. 所缓存语句涉及到的表被频繁更新， 通过COM_update, COM_delete等来查看
4. 业务本省就没有多少重复查询
5. 缓存池的大小可能不够， 导致频繁的有查询结果集被清理掉或者空间不够而无法缓存，
通过QCACHE_lowmem_prunes可以看由于内存较少从缓存删除的查询数量
6. 缓存池碎片化过于严重
另外， 要注意一种情况， 就是一条记录被查询时， 如果没有命中 ，那么系统会缓存这条记录 ，但是马上被修改了，
从而导致该记录立刻被失效掉， 然后， 又被查询，这样导致很多无效的缓存； 从而导致效率降低， 这种情况下，
COM_select 和COM_insert相差不大。
碎片问题
碎片随缓存的命中率影响比较大 ，通过一些参数可以大体估计一下，
从Qcache_free_blocks可以知道大概有多少的空闲块， 如果这个值与Qcache_total_blocks/2比较接近的话
，就意味着碎片化比较严重了， 这个可以这么理解， 当
产生碎片时， 通常是一个分配的内存块没有用完而产生而而导致的， 这是就是一个有效块和一个空闲块了， 从而达成1：1的大体比例；
这个时候， 如果Qcache_lowmem_prunes还很高的话， 那说明碎片在引起一些问题了。
内存块分配的大小对碎片的产生有着比重要的影响， 如果过大， 导致分配的内存块无法有效的使用， 从而使得碎片增加 ，过小的话，
不但可能导致碎片也会增加， 而且效率会降低， 因为可能在缓存一个结果集的过程中多次分配内存块。 这个时候，
通过变量query_cache_min_res_unit来控制每次分配的内存块的大小， 那么如何来计算大体值呢，
通过（query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache
可以计算出缓存的结果集的平均大小。 另外， 缓存太大的记过集可能得大于失，
因为这可能导致多次的内存块分配， 如果加上缓存失效时间短的话， 就更不合算了， 在这种情况下，
考虑通过query_cache_limit来控制那么太大的结果集的缓存。
改善缓存的使用
缓存的多少对缓存效果影响比较大的。
Qcache_lowmem_prunes这个状态参数给出了有多少条目因为内存不够而无法被缓存的。如前面提到的，
碎片化可能导致这个问题(这时Qcache_free_blocks一般比较多)，另外一个情况就是缓存池大小不够，
这种情况下 ，Qcache_free_blocks比较少， 同时Qcache_free_memory也不多。
在非启动预热阶段， 如果碎片化不严重 ，Qcache_free_blocks和Qcache_free_memory也不少 ，Qcache_lowmem_prunes也不大 ，而Qcache_hits现实命中率还是不高的话， 那要看看是不是业务的库操作不适合缓存， 或者语句不适合缓存。
Innodb与查询缓存
在Mysql4.0以前， 事务里面是不允许访问缓存的， Mysql4.1以后， 可以访问了， 但是以表为基础，
根据表的事务ID和锁情况来控制查询缓存的访问：
1. 当事务的ID小于与它相关的表的事务ID（innodb的每个表， 在内存中有一个与之相关的事务ID）时， 不允许访问内存缓存，
比如但事务并发时 ，一个事物ID大的事务提交了数据修改后， 比其事务ID小的， 但没有提交的所有事务都无法访问缓存的结果集
2. 任何有加锁行为的语句都会导致无法缓存， 比如 SELECT.... FOR UPDATE；
设计和编码时的优化
1. 把大表分成多个小表， 这是因为查询缓存的实效是以表位单位来处理的
2. 批处理多个修改操作比单个修改操作有效， 因为可以减少失效查询缓存的次数， 从而减少开销
3. 对于写负荷很大的应用， 可以考虑关闭查询缓存功能.
声明： 此文观点不代表本站立场；转载须要保留原文链接；版权疑问请联系我们。
