mysql性能优化点记录

《mysql性能优化点记录》由会员分享，可在线阅读，更多相关《mysql性能优化点记录（7页珍藏版）》请在装配图网上搜索。

1、第一章myisam，可以基于blob和text的前500字节，创建索引myisam 支持fulltext延迟更新索引(delay_key_write)CREATE TABLE table3 ( id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(30) DEFAULT NULL, id2 INT(11) DEFAULT NULL, PRIMARY KEY (id) ENGINE=MYISAM DEFAULT CHARSET=utf8 DELAY_KEY_WRITE = 1ALTER TABLE table2 DELAY_KEY_WRITE = 1只

2、有myisam支持全文检索第三章 索引方面 字段尽可能的小 尽量避免null，用0代替。但是对性能的提升很小，最后考虑，索引的列最好不适用null mysql性能优化点记录一、优化数据访问查询性能低下的最基本原因就是访问了太多数据。一些查询不可避免的要筛选大量的数据，单这并不常见。大部分性能欠佳的查询都可以用减少数据访问的方式进行修改。在分析性能欠佳的查询的时候，下面两个步骤比较有用：1.应用程序是否在获取超过需要的数据。这通常是访问了过多的行或列。2.mysql服务器是否分析了超过需要的行。对于访问的数据行很大，而生成的结果中数据行很少，可以尝试修改。 1.使用覆盖索引，它存储了数据，所以存

3、储引擎不会去完整的行。2.更改架构，一个例子就是使用汇总表。3.重写复杂的查询，让mysql的优化器可以优化的执行。 二、复杂查询和多个查询1.把一个复杂的查询分解为多个简单的查询。（mysql一般的服务器，每秒钟可以处理50 000个查询）2.三、缩短查询将一次处理大量数据的操作，分解为多个小操作。循环的方式每次处理一部分数据。一次删除不要超过10 000行（delete）四、分解链接把一个多表连接分解成多个单个查询，然后在应用程序里实现联接。这样的优势1.缓存效率高。2.mysql，可以更有效的利用表锁，查询会锁住单个表较短时间。3.应用程序进行联接可以更方便的拓展数据库，把不同表放在不同

4、服务器上。4.查询更高效。5.可以减少多余的行访问，可以减少网络流量和内存消耗。 小结：在程序端进行联接的效率更高 1.可以缓存早期查询的大量数据。 2.使用了多个myisam表 3.数据分布在不同的服务器上。 4.对于大表使用in替换联接 5.一个连接引用了同一个表多次。 当你重建汇总和缓存表的时候，在操作的时候你常常需要它们的数据保持可见。你可以使用“shadow table”（影像表）来实现。当你已经创建它之后，你可以使用原子性的重命名来交换这些表。举个例子，如果你需要重建my_summary，你能创建my_summary_new，填充数据，把它和真正的表作交换。mysql DROP T

5、ABLE IF EXISTS my_summary_new, my_summary_old;mysql CREATE TABLE my_summary_new LIKE my_summary;- populate my_summary_new as desiredmysql RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary; mysql执行查询的一般性过程 1.客户端发送查询到服务器 2.服务器检查查询缓存， 3.服务器解析，预处理和优化查询，生成执行计划。 4.执行引擎调用存储引擎api执行查询。

6、5.服务器将结果发送到客户端。 mysql客户端、服务器协议 1.协议是半双工的。mysql服务器在某个时间可以发送或者接受数据，单不能同时发送和接收。所有没有办法阶段消息。 2.客户端用一个数据包将查询发送到服务器，所以max_packet_size这个配置参数对于大查询很重要的原因。 3.客户端从服务器提取数据的时候是服务器产生数据的同时把它们“推”到客户端的，客户端只需要接收推出来的数据，无法告诉服务器停止发送数据。 查询缓存 SELECT SQL_NO_CACHE * FROM ol_answerlog LIMIT 1000 SHOW STATUS LIKE last_query_co

7、st 关键字straight_join 强制执行引擎按照查询中表现的顺序来进行链接操作。严格的说，mysql不回尝试减少读取的行数，它只会试着优化对页面的读取，但是行数可以大致显示查询的开销。 连接优化器试着产生最低开销的查询计划。在可能的时候，他会从单表计划开始，检查所有的可能的子树的组合。但是对n个表连接，需要检查组合的数量就是n的阶乘，这个数量称为ie搜索空间， 它增长非常快，如果一个查询需要连接10个表，那么要检查的数量将是10！=36288000 当搜索空间非常巨大的时候优化耗费的时间就会非常长，这时候服务器就不回执行完整的分析，但表的数量超过optimizer_search_dep

8、th的值时，它就会走捷径，比如执行所谓的 贪婪搜索。 SHOW TABLE STATUS FROM servant_591upWHERE ENGINE IS NOT NULLAND NAME LIKE %ol_ans%; max min的优化select min(id) from ol_user where username = dddd(一)max 和 min 会扫描整张表。mysql的主键都是按照升序排列的。可以使用limit 改写查询，select userid from ol_user where username=test limit 1（二）对同一表进行select 和 updat

9、emysql不允许对一个表进行update的时候进行selectupdate tb1 as out_tableset cnt = (select count(*) from tb1 as inner_table where inner_table.type = outer_table.type);一个实现方式：衍生表，当成临时表来处理。update tb1 inner join(select type,count(*) as cntfrom tb1group by type)as der using(type)set t = t;(三、)优化特定类型的查询1.countcount的作用 统计值

10、的数量和统计行的数量值是非空表达式（NOT NULL）一个常见的错误就是在想统计行数的时候，在count的括号中放入列名，如果想知道结果的行数，应该总是使用COUNT(*)，这可以清晰的说明意图，并且得到好的性能。2.MYISAM只有在没有WHERE条件的时候COUNT(*)才是最快的，在有条件过滤的时候并不非常快。3.简单优化可以利用MYISAM对COUNT（*）的优化对已经有索引的一小部分做统计。SELECT COUNT(*) FROM WORD.CITY WHERE ID5;优化为下面的语句SELECT (SELECT COUNT(*) FROM CITY) - COUNT(*) FRO

11、M CITY WHEREID=5;这样的explain只扫描6行数据使用一个查询统计同一列中不同值的数量。select sum(if(color=blue,1,0) as blue,sum(if(color=red,1,0) as red from items;下面是一个等价查询select count(color=blue or null) as blue,count(color=red or null) as red from items;(四)优化联接1.确保on 或using使用的列上有索引。通常只需要在联接中的第2个表上添加索引就可以。2.确保group by或order by只引用

12、一个表中的列。这样可以使用索引。3.谨慎升级mysql（五）优化子查询对于子查询，尽可能的使用联接。（五）优化group by和distinct1.主要方式：索引2.优化group by的策略：临时表或文件排序分组。SQL_SMALL_RESULT ： 强制使用临时表SQL_BIG_RESULT :强制使用文件排序通常对表的id进行分组会更加高效可以使用SQL_MODE参数禁止SELECT中使用在group by中出现的列子查询创建的临时表不支持索引。所以要让子查询创建的临时表尽可能的小。3.使用ROLL UP 优化GROUP BYWITH ROLLUP最好的方式是将WITH ROLLUP 放

13、在应用程序里。注意: Rollup 与 order by 相互排拆 （六）优化limit和offsetLIMIT 和ORDER BY 一块使用。如果没有索引，就使用文件排序。（七）优化SQL_CALC_FOUND_ROWS这个地方很重要一个技巧：在含有limit的查询中添加SQL_CALC_FOUND_ROWS，这样就可以知道没有limit的时候会返回多少行数据。服务器会预测将会发现多少行数据。但是服务器并不能真正的做到，只是告诉服务器生成结果并丢掉结果中不需要的部分。而不是在得到需要的数据后就立即停止。这个选项代价很高。一个非常好的设计：如果每页有20条结果，那么应该查询limit 21行数

14、据，只显示20条，如果结果中有21行，那么就会有下一页。另一种方式：就是提取并缓存大量数据，比如1000行，然后从缓存中获取后续页面的数据。可以让程序知道一共有多少数据，少于1000，程序知道有多少页，如果大于1000，可以显示找到的结果超过1000个。这两种都比重复产生完整的结果效率高。如果以上两种都不可以使用，可以使用覆盖索引，使用单独的count（*）会更好(八)优化联合 unionmysql总是使用临时表来执行union，无法做更多的优化重要的是，一定要使用union all，除非真的是需要服务器消除重复的行，否则mysql会使用distinct选项，来确保所有行数据的唯一性。（九）查

15、询优化提示可以用一些提示控制优化器的行为，每个提示只影响当前查询。1.HIGH_PRIORITY 和 LOW_PRIORITYHIGH_PRIORITY 让mysql 将一个select语句放在其他的语句的前面，mysql将它放在队列的前面，而不是在队列中等待。可以用在insert语句中。lowpriority正好相反，可以用在SELECT INSERT UPDATE REPLACE DELETE 这两个选项在表锁的存储过程中有效，在innerdb无效，在myisam要小心使用，严重影响性能，禁止并发插入。2.delayed用户insert delete立即返回，放入缓冲当中，无法使用LAST

16、_ISNERT_ID()3.STRAIGHT_JOIN强制mysql按照查询中表出现的顺序来连接表。出现在两个连接的表中间时，强制这两个表按照顺序连接。用途：mysql没有选择更好的链接，或者优化器需要花费很长时间来确定连接顺序。4.SQL_SMALL_RESULT SQL_BIGA_RESULT用在group by 和distinct语句中的，如何使用临时表SQL_SMALL_RESULT ：结果很小，可以放在索引过的临时表中，SQL_BIGA_RESULT：结果很大，最好使用磁盘上的临时表进行排序。5.SQL_BUFFER_RESULT将结果放在临时表中，并且要尽快释放掉表锁。6.SQL_

17、CACHE SQL_NO_CACHE7.SQL_CALC_FOUND_ROWS在limit自居计算完整的结果集，可以通过found_ROWS()来取得它的行数，最好不使用这个提示、8.FOR_ UPDATE 和 LOCK IN SHARE MODE只有innodb支持，提示控制锁定，仅对行锁起作用。select语句中9.USE INDEX 和 IGNORE INDEX 和 FORCE INDEX控制索引的使用，在mysql5.1中，还有FOR ORDER BY FOR GROUP BY 用途：告诉优化器表扫描比索引代价高很多，重要的系统变量Optimizer_search_depth优化器检查

18、执行计划的深度。Optimizer_prune_level根据检查的行数来决定跳过一些查询计划。（九） 用户自定义变量一些需要注意的问题：会禁止缓存不能用于文字常量和标识的地方（表名，列名，limit）和连接有关，不能跨通信使用如果使用连接池，会引起代码隔离mysql 5.0大小写敏感不能显示的声明类型，最好的方式给变量显示的一个初始值 0 0.0 ，用户自定义变量的类型是动态的，赋值的时候才会变化。优化器有时候会把变量优化掉。set ont:=1; ：=运算符的优先级，低于其他的，最好使用括号（）。 也可以使用=赋值，最好统一使用：=未定义的变量不会引起语法错误，很容易犯错。五、mysql高

19、级特性查询缓存命中率Qcache_hits/(Qcache_his+Com_select) show status1.缓存未命中原因查询不可缓存，不确定函数。CURRENT_DATE,结果太大， Qcache_not_cached 会记录两种无法缓存的查询数量。服务器以前从来没见过这个缓存。查询结果被缓存过，但是服务器把它移除。很多缓存失效。2.很多缓存未命中，但是不能缓存的查询很少。查询缓存未被激活。服务器看到了以前未见过的查询缓存失效。A:如何对查询缓存进行维护调优Query_cache_type表示缓存是否被激活，on off demand,demand：只有sql_cache的查询才可

20、以被缓存。Query_cache_size缓存的总内存，字节单位。1024的倍数，Query_cache_min_res_unit分配缓存块的最小值Query_cache_limit限制了mysql存储的最大结果。如果超过这个值，会丢掉已经缓存过的值，并增加Query_not_chched的值。如果是这样需要在查询上增加QUERY_NO_CACHEQuery_cache_wlock_invalidate是否缓存其他链接已经锁定了的表，默认off，B:优化的一些法则减少碎片需要仔细选择Query_cache_min_res_unit,可以避免在查询缓存中造成大量的内存浪费。最佳设置根据典型查询结

21、果确定。可以使用内存（Queryc_cache_size - Query_free_memory）除以Query_queries_in_cache得到查询的平均大小。可以通过query_cache_limit的值来阻止缓存大结果。可以检查Qcache_free_blocks来探测缓存中碎片的情况，显示缓存中有多少内存块出于free状态。如果Qcache_free_blocks大致等于Qcache_total_blocks/2，则说明碎片非常严重，如果Qcache_lowmen_prunes的值在增加并且有大量的自由块，说明碎片导致查询整被从缓存中永久删除。可以使用FLUSH QUERY CAC

22、HE命令移除碎片。会把所有的存储块向上移动，把自由块移动到底部。会阻止访问查询缓存，锁定整个服务器，通常这个速度很快，不会移除缓存中的数据，RESET QUERY CACHE会清除缓存数据。提高缓存可用性。如果没有碎片，但是命中率不高，应该给缓存分配较少的内存。服务器清理查询的时候会Qcache_lowmen_prunes的值会增加，如果值增加的过快则说明：1.如果自由块很多，碎片2.如果自由块比较少，说明工作负载使用的内存超过所分配的内存。可以检查Qcache_free_memory知道未使用的内存。可以禁用缓存查看缓存是否真的有效果query_cacha_size = 0，可以关闭缓存（q

23、uery_cache_type 无法影响已经打开了的链接，也不会把内存归还给服务器。）5.1.5 innodb和查询缓存6.2.2 myisam键缓存SHOW VARIABLES LIKE key_buffer_size键缓冲区，myisam本身只缓存索引，没有数据。应该让key_buffer_size占到保留内存的25-50%。但是对于mysql5.0，最大上限都是4G但是可以创建多个命名键缓冲区。可以一次在内存中保存4G以上的数据。key_buffer_1 key_buffer_2 都是1G在配置文件增加两行 key_buffer_1.key_buffer_size = 1Gkey_buf

24、fer_2.key_buffer_size = 1G可以使用cache index 命令把表映射到缓存也可以用下面的命令把表的索引保存到key_buffer_1cache index t1,t2 in key_buffer_1也可以使用load index把表的索引预加载到缓存中load index into cache t1,t2;缓存命中率100-(Key_reads * 100) / Key_read_requests)缓存使用百分比100-(Key_blocks_unused * key_cache_block_size) * 100 / key_buffers_size)innodb 可以使用裸设备raw