高性能MySQL学习笔记讲述.pdfVIP

高性能MySQL 学习笔记 发表于2013/11/04 由YY_Crazy 超长文，慎入！ 这是我大二暑假读《高性能MySQL》的笔记，当时分篇记录在了blogspot 的博客上，现在那 个博客已经废弃，今天爬篱笆的时候无意间看到，就把它整理了一下放到这里来。 MySQL 查询性能优化 查询性能低下的最基本原因就是访问了太多数据。在分析性能欠佳的查询的时候，下面两个 步骤比较有用： 1.查明应用程序是否正在获取超过需要的数据。这通常意味着访问了过多的行或列。 2.查明MySQL 服务器是否分析了超过需要的行。 当我们找到有问题的查询语句时，我们就应该重构它，下面是一些重构查询的技巧及使用它 们的时机。 1.缩短查询 一种处理查询的办法是分治法，让查询的本质上不变，但是每次只执行一小部分，以减少受 影响的行数。 比如，下面是一个巨大的查询： ? 1 DELETE FROM messages WHERE created DATE_SUB(NOW(), INTERVAL 3 MONTH); 应该用类似于下面的伪代码的查询替换它： ? rows_affected = 0; 1 do { 2 rows_affected = do_query(DELETE FROM messages WHERE created DATE_SUB(NOW(), 3 INTERVAL 3 MONTH) LIMIT 10000); 4 } while rows_affected 0; 足够短的任务对服务器的影响最小。在DELETE 语句中加入休眠语句也是一个好主意，它可 以分摊负载，并且减少锁住资源的时间。 2.分解联接 许多高性能的网站都用了“分解联接”技术，可以把一个多表联接分解成多个单个查询，然后 在应用程序端实现联接操作。 ? 1 SELECT * FROM tag 2 JOIN tag_post ON tag_post.tag_id = tag.id 3 JOIN post ON tag_post.post_id = post.id 4 WHERE tag.tag = mysql; 可以用下面的语句代替： ? 1 SELECT * FROM tag WHERE tag = mysql; 2 SELECT * FROM tag_post WHERE tag_id = 1234; 3 SELECT * FROM post WHERE post.id IN (123, 456, 567, 9098, 8904); 这种重构方式有下面这些重大的性能优势： a.缓存的效率更高。如果只有一个表经常改变，那么分解联接就可以减少缓存失效的次数。 b.对于MyISAM 表来说，每个表一个查询可以更有效地利用表锁，因为查询会锁住单个表较 短时间，而不是把所有表长时间锁住。 c.在应用程序端进行联接可以更方便地扩展数据库，把不同的表放在不同的服务器上面。 d.查询本身会更高效。 e.可以减少多余的行访问。在应用程序端进行联接意味着对每行数据只会访问一次，而联接 从本质上来说是非正则化的，它会反复地访问同一行数据。基于同样的原因，这种重构方式 可以减少网络流量和内存消耗。 想得到高性能，最佳的方式就是学习MySQL 如何优化和执行查询。 MySQL 使用的是基于开销（Cost ）的优化器，这意味着它会预测不同执行计划的开销，并且 选择开销最小的一个。开销的单位是一次对大小为4KB 的页面的随机读取。 优化器不会考虑任何缓存因素，它认为每次读取都会有相同的IO 开销。 由于种种原因，优化器并不总是能选择最好的方案： 1.统计数据可能是错误的。 2.开销指标和运行查询的实际开销并不精确相等。 3.MySQL 的优化并不总是和我们想的一样。它只考虑开销。 4.MySQL 不会考虑正在并发运行的其他查询，而并发查询会影响查询运行的速度。 5.MySQL 并不总是根据开销来进行优化，有时候它仅仅遵从一些原则。 6.优化器不会考虑不受它控制的操作的开销，比如执行存储函数和用户定义的函数。 7.优化器不会总是估算每一个可能的执行计划，所以它可能错过优化方案。 MySQL 优化器有两个基本方案：静态优化和动态优化。静态优化可以简单地通过探测解析树 （Parse T