高性能MySQL学习笔记教学.pdf

高性能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 优化器有两个基本方案：静态优化和动态优化。静态优化可以简单地