对拥有一个几十万行表的MySQL性能优化的简单办法

• 
  　　数据库的优化大概是在系统管理中最具有挑战性的了，因为其对人员的素质要求几乎是全方面的，好的 DBA 需要各种综合素质。在排除了操作系统，应用等引起的性能问题以外，优化数据库最核心的实际上就是配置参数的调整。
  本文通过一个简单的参数调整，实现了对拥有一个几十万行表的 group by 优化的例子。通过这个简单的调整，数据库性能有了突飞猛进的提升。
  本例子是针对 mysql 调整的，不像其他商业数据库，MySQL 没有视图，特别是 Oracle 可以利用固化视图来提升查询性能，没有存储过程，因此性能的调整几乎只能通过配置合适的参数来实现。
  
  调整的具体步骤(例子针对 pLog 0.3x 的博客系统)：
  
  发现最多的 slow log 是：
  SELECT category_id, COUNT(*) AS 'count' FROM plog_articles WHERE blog_id = 2 AND status = 'PUBLISHED' group by category_id;
  一般在 20s 以上，甚至 30s 。
  而当 blog_id=1 或者其他时，都能很快的选出结果。
  于是怀疑索引有问题，重新建立索引，但无济于事。 EXPLAIN 结果如下：
  mysql> EXPLAIN SELECT category_id, COUNT(*) AS 'count' FROM plog_articles WHERE blog_id = 2 AND status = 'PUBLISHED' group by category_id;
  +---------------+------+------------------+------------------+---------+-------------+------+----------------------------------------------+
  table type possible_keys key key_len ref rows Extra
  +---------------+------+------------------+------------------+---------+-------------+------+----------------------------------------------+
  plog_articles ref idx_article_blog idx_article_blog 5 const,const 4064 Using where; Using temporary; Using filesort
  +---------------+------+------------------+------------------+---------+-------------+------+----------------------------------------------+
  1 row in set (0.00 sec)
  
  于是想到每次查看 blog_id = 2 的博客时，系统负载就提高，有较高的 swap 。于是查看 temporary table 有关的资料，果然有这样的说法：
  
  If you create a lot of disk-based temporary tables, increase the size of tmp_table_size if you can do so safely. Keep in mind that setting the value too high may result in excessive swapping or MySQL running out of memory if too many threads attempt to allocate in-memory temporary tables at the same time. Otherwise, make sure that tmpdir points to a very fast disk that's not already doing lots of I/O.
  Another problem that doesn't show up in the slow query log is an excessive use of disk-based temporary tables. In the output of EXPLAIN, you'll often see Using temporary. It indicates that MySQL must create a temporary table to complete the query. However, it doesn't tell you whether that temporary table will be in memory or on disk. That's controlled by the size of the table and MySQL's tmp_table_size variable.
  If the space required to build the temporary table is less than or equal to tmp_table_size, MySQL keeps it in memory rather than incur the overhead and time required to write the data to disk and read it again. However, if the space required exceeds tmp_table_size, MySQL creates a disk-based table in its tmpdir Directory (often /tmp on Unix systems.) The default tmp_table_size size is 32 MB.
  To find out how often that happens, compare the relative sizes of the Created_tmp_tables and Created_tmp_disk_tables counters:
  
  调整 tmp_table_size为 80M 左右后，以上语句 14s 即可解决。
  
• [1] [2] 下一页 
• 复制本页网址和标题，发送给你QQ/Msn的好友一起分享
  
  
• 对拥有一个几十万行表的MySQL性能优化的简单办法 相关文章：
• ·对拥有一个几十万行表的MySQL性能优化的简单办法
• 对拥有一个几十万行表的MySQL性能优化的简单办法 相关软件

上一篇:MySQL入门学习(5)

下一篇:解决MySQL启动时万恶的的“1067”错误

• 特别声明：本站除部分特别声明禁止转载的专稿外的其他文章可以自由转载，但请务必注明出处和原始作
• 者.文章版权归文章原始作者所有.对于被本站转载文章的个人和网站，我们表示深深的谢意。如果本站转
• 载的文章有版权问题请联系编辑人员,我们尽快予以更正. 转载请注明来源：http://www.hackhome.com

精品推荐

热点TOP10

特别推荐