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 = 1 ALTER TABLE table2 DELAY_KEY

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

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

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

5、创建 my_summary_new，填充数据，把它和真正的表作交换。 mysql DROP TABLE IF EXISTS my_summary_new, my_summary_old; mysql CREATE TABLE my_summary_new LIKE my_summary; - populate my_summary_new as desired mysql RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;mysql 执行查询的一般性过程1.客户端发送查询到服务器2.服务器检查查询缓存

6、，3.服务器解析，预处理和优化查询，生成执行计划。4.执行引擎调用存储引擎 api 执行查询。5.服务器将结果发送到客户端。mysql 客户端、服务器协议1.协议是半双工的。mysql 服务器在某个时间可以发送或者接受数据，单不能同时发送和接收。所有没有办法阶 段消息。2.客户端用一个数据包将查询发送到服务器，所以 max_packet_size 这个配置参数对于大查询很重要的原因。3.客户端从服务器提取数据的时候是服务器产生数据的同时把它们“推”到客户端的，客户端只需要接收推出来 的数据，无法告诉服务器停止 发送数据。查询缓存SELECT SQL_NO_CACHE * FROM ol_ans

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

8、的时候优化耗费的时间就会非常长，这时候服务器就不回执行完整的分析，但表的数量超过 optimizer_search_depth 的值 时，它就会走捷径，比如执行所谓的 贪婪搜索。SHOW TABLE STATUS FROM servant_591upWHERE ENGINE IS NOT NULL AND NAME LIKE %ol_ans%;max min 的优化 select min(id) from ol_user where username = (一)max 和 min 会扫描整张表。 mysql 的主键都是按照升序排列的。 可以使用 limit 改写查询， select useri

9、d from ol_user where username= limit 1（二）对同一表进行 select 和 update mysql 不允许对一个表进行 update 的时候进行 select update tb1 as out_table set cnt = (select count(*) from tb1 as inner_table where inner_table.type = outer_table.type);一个实现方式：衍生表，当成临时表来处理。 update tb1 inner join( select type,count(*) as cnt from tb1 g

10、roup by type )as der using(type) set t = t;(三、)优化特定类型的查询 1.countcount 的作用 统计值的数量和统计行的数量 值是非空表达式（NOT NULL） 一个常见的错误就是在想统计行数的时候，在 count 的括号中放入列名，如果想知道结果的行数，应该总是使用 COUNT(*)，这可以清晰的说明意 图，并且得到好的性能。 2.MYISAM只有在没有 WHERE 条件的时候 COUNT(*)才是最快的，在有条件过滤的时候并不非常快。 3.简单优化 可以利用 MYISAM 对 COUNT（*）的优化对已经有索引的一小部分做统计。 SELEC

11、T COUNT(*) FROM WORD.CITY WHERE ID5;优化为下面的语句 SELECT (SELECT COUNT(*) FROM CITY) - COUNT(*) FROM 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

12、 null) as red from items;(四)优化联接 1.确保 on 或 using 使用的列上有索引。 通常只需要在联接中的第 2 个表上添加索引就可以。 2.确保 group by 或 order by 只引用一个表中的列。这样可以使用索引。 3.谨慎升级 mysql （五）优化子查询 对于子查询，尽可能的使用联接。 （五）优化 group by 和 distinct 1.主要方式：索引 2.优化 group by 的策略：临时表或文件排序分组。 SQL_SMALL_RESULT ： 强制使用临时表 SQL_BIG_RESULT :强制使用文件排序通常对表的 id 进行分组会更

13、加高效 可以使用 SQL_MODE 参数禁止 SELECT 中使用在 group by 中出现的列 子查询创建的临时表不支持索引。 所以要让子查询创建的临时表尽可能的小。 3.使用 ROLL UP 优化 GROUP BY WITH ROLLUP最好的方式是将 WITH ROLLUP 放在应用程序里。 注意: Rollup 与 order by 相互排拆 （六）优化 limit 和 offset LIMIT 和 ORDER BY 一块使用。 如果没有索引，就使用文件排序。 （七）优化 SQL_CALC_FOUND_ROWS 这个地方很重要 一个技巧：在含有 limit 的查询中添加 SQL_CA

14、LC_FOUND_ROWS，这样就可以知道没有 limit 的时候会返回多少行数 据。服务器会预测将会发现多少 行数据。但是服务器并不能真正的做到，只是告诉服务器生成结果并丢掉结果中不需要的部分。而不是在得到需 要的数据后就立即停止。这个 选项代价很高。 一个非常好的设计： 如果每页有 20 条结果，那么应该查询 limit 21 行数据，只显示 20 条，如果结果中有 21 行，那么就会有下一页。 另一种方式：就是提取并缓存大量数据，比如 1000 行，然后从缓存中获取后续页面的数据。 可以让程序知道一共有多少数据，少于 1000，程序知道有多少页，如果大于 1000，可以显示找到的结果超过

15、 1000 个。 这两种都比重复产生完整的结果效率高。 如果以上两种都不可以使用，可以使用覆盖索引，使用单独的 count（*）会更好 (八)优化联合 union mysql 总是使用临时表来执行 union，无法做更多的优化 重要的是，一定要使用 union all，除非真的是需要服务器消除重复的行， 否则 mysql 会使用 distinct 选项，来确保所有行数据的唯一性。 （九）查询优化提示 可以用一些提示控制优化器的行为，每个提示只影响当前查询。 1.HIGH_PRIORITY 和 LOW_PRIORITY HIGH_PRIORITY 让 mysql 将一个 select 语句放在其

16、他的语句的前面，mysql 将它放在队列的前面，而不是在队列中 等待。可以用在 insert 语句中 。lowpriority 正好相反，可以用在 SELECT INSERT UPDATE REPLACE DELETE 这两个选项在表锁的存储过程中有效，在 innerdb 无效，在 myisam 要小心使用，严重影响性能，禁止并发插入。 2.delayed用户 insert delete 立即返回，放入缓冲当中， ，无法使用 LAST_ISNERT_ID() 3.STRAIGHT_JOIN强制 mysql 按照查询中表出现的顺序来连接表。 出现在两个连接的表中间时，强制这两个表按照顺序连接。 用途：mysql 没有选择更好的链接，或者优化器需要花费很长时间来确定连接顺序。 4.SQL_SMALL_RESULT SQL_BIGA_RESULT用在 group by 和 distinct 语句中的，如何使用临时表 SQL_SMALL_R