mysql⼏⼗万⾏数据查询怎么才快_对拥有⼀个⼏⼗万⾏表的MySQL性能优化的简单办法（转）...
对拥有⼀个⼏⼗万⾏表的MySQL性能优化的简单办法(转)[@more@]
数据库的优化⼤概是在系统管理中最具有挑战性的了，因为其对⼈员的素质要求⼏乎是全⽅⾯的，好的 DBA 需要各种综合素质。在排除了操作系统，应⽤等引起的性能问题以外，优化数据库最核⼼的实际上就是配置参数的调整。本⽂通过⼀个简单的参数调整，实现了对拥有⼀个⼏⼗万⾏表的 group by 优化的例⼦。通过这个简单的调整，数据库性能有了突飞猛进的提升。
java语言的运行机制本例⼦是针对 MySQL 调整的，不像其他商业数据库，MySQL 没有视图，特别是 Oracle 可以利⽤固化视图来提升查询性能，没有存储过程，因此性能的调整⼏乎只能通过配置合适的参数来实现。
调整的具体步骤(例⼦针对 pLog 0.3x 的博客系统)：
mysql语句转oracle发现最多的 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 |
高中数学余弦定理公式+---------------+------+------------------+------------------+---------+-------------+------+----------------------------------------------+
spacious| 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
warehouse怎么读音发音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:
jquery教程与例子调整 tmp_table_size为 80M 左右后，以上语句 14s 即可解决。
这个参数是 DBA 很容易忽视的。
其实，不单单是数据库，就是操作系统，也是受 tmp 的影响巨⼤，例如安装软件到 d: 盘，如果 TMP 环境变量指向 c: 盘，⽽ c: 空间不够，照样可能导致安装失败。
因此让 TMP 有⾜够的空间可以说是计算机系统⾥⼀个普遍适⽤的原则(写程序也是⼀样)
来⾃ “ ITPUB博客 ” ，链接：blog.itpub/8225414/viewspace-940569/，如需转载，请注明出处，否则将追究法律责任。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论