为什么SELECT col1,col2,col3,col4 会比SELECT * 慢?

10月 7, 2007 on 1:31 pm | In 未分类 | 5 Comments

之前做过一个测试:
select id,title from ** 与select * from **的速度测试。
这是后续版.

  1. <?php
  2. define("TIMES",10000);
  3. $SQL1="SELECT * FROM stat_visits";
  4. $SQL2="SELECT v_id,v_url,v_site_id,v_screen_size,v_h,v_m,v_s,v_flash,v_director,
  5. v_quicktime,v_realplayer,v_pdf,v_windowsmedia,v_java,v_cookie,v_ref,v_remote_ip,
  6. v_timezone,v_color_depth,v_type,v_year,v_month,v_day,v_time FROM stat_visits";
  7. $conn=mysql_connect("localhost","root","");
  8. mysql_select_db("test");
  9. $time1=array_sum(explode(' ', microtime()));
  10. for($i=0;$i<times;$i++)
  11. </times;$i++)
  12.   mysql_query($SQL1);
  13. $time2=array_sum(explode(' ', microtime()));
  14. for($i=0;$i<times;$i++)
  15. </times;$i++)
  16.   mysql_query($SQL2);
  17. $time3=array_sum(explode(' ', microtime()));
  18.  
  19. $cost1=$time2-$time1;
  20. $cost2=$time3-$time2;
  21. print "cost1:$cost1\n";
  22. print "cost2:$cost2\n";
  23.  
  24. ?>

测试的结果是:

  1. [st@localhost test]$ php select.php
  2. cost1:2.1355810165405
  3. cost2:2.5820469856262
  4. [st@localhost test]$ php select.php
  5. cost1:1.9796178340912
  6. cost2:2.5190000534058
  7. [st@localhost test]$ php select.php
  8. cost1:1.9672379493713
  9. cost2:2.9030020236969
  10. [st@localhost test]$ php select.php
  11. cost1:1.9810240268707
  12. cost2:2.5505940914154

好了，现在将SQL1和SQL2次序交换:

  1. calhost test]$ php select.php
  2. cost1:2.5933158397675
  3. cost2:2.0362050533295
  4. [st@localhost test]$ php select.php
  5. cost1:2.6104090213776
  6. cost2:2.056037902832
  7. [st@localhost test]$ php select.php
  8. cost1:4.2802629470825
  9. cost2:3.3958988189697
  10. [st@localhost test]$ php select.php
  11. cost1:2.6283531188965
  12. cost2:2.0102050304413

大体上，SELECT COL1,COL2,COL3….. 与SELECT * 相比，两者花费的时间之比是4:5.不知道非空表的结果会不会是这样?

Mysql 随机读取记录的一种实现

10月 7, 2007 on 12:28 pm | In 未分类 | No Comments

这两天觉得自己的基本功不够扎实,于是躲在家里看PHP,mysql手册。
发现这个句子在大部分情况下能实现随机读取mysql记录:
表的结构如下:

  1. | id             | bigint(11) unsigned | NO   | PRI | NULL    | auto_increment |
  2. | tag            | varchar(32)         | NO   |     |         |                |
  3. | hash           | varchar(32)         | NO   |     |         |                |
  4. | created        | bigint(11)          | NO   |     |         |                |
  5. | article_id     | bigint(11)          | NO   |     |         |                |
  1. select * from tag order by RAND() desc limit 10\G

秘决在于:
1.Rand函数.
2.不仅用order by id可以依id排序,用order by 1也可以依id排序.因此用rand函数来生成order by 子句。

犯错误了,order by 1 确实可以生效，但是rand函数不能用在order by 子句中。 而且,当表中有column(1),column(2)…..共10列时,用order by 1,order by 2,….分别是按column(1),column(2)…排序的，但是我用order by 11时，就报错了。再用SELECT　*　FROM table order by RAND()*1000,却不报错。

看下面的例子:

  1. select *,(rand()*1000) as a from x_feeds order by a;

每次输出，都不同。
再稍变一次:

  1. mysql&gt;select *,(rand()*1000) as a from x_feeds order by 514;

每次都出错。
再变之:

  1. mysql&gt;select *,(rand()*1000) as a from x_feeds order by 514.37134

将order by 后跟上小数(学计算机的不能这么说啊),就能正常输出，但是是按表的原来顺序输出的，每次结果都一样。
暂时未找到真正原因，mysql文档中文版如此说:

你不能在一个ORDER BY子句用RAND()值使用列，因为ORDER BY将重复计算列多次。然而在MySQL3.23中，你可以做： SELECT * FROM table_name ORDER BY RAND()，这是有利于得到一个来自SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000的集合的随机样本。注意在一个WHERE子句里的一个RAND()将在每次WHERE被执行时重新评估。

Mysql server tuning(译文)

06月 29, 2007 on 4:08 am | In 未分类 | 1 Comment

从某个英文pdf文档译过来的,很久了，还是Mysql Conference 2007时的PDF.
(如果能找到，我把原文地址和pdf给出来)
向原作者致谢….

调整Server Config时需要考虑的事情
设置是针对负载的，不存在说有什么针对一个16GB内存的优化方案这种说法.
存储引擎的选择或是混用是很重要的
有些设置取决于您的硬件和操作系统。

Approaching?tuning
您只需要设置一部分，其余的一些要具体分析（这些也很难决定如何去调整）
有些设置会影响Mysql的行为和安全，要小心
有些设置是可以基于连接的，利用这一点!
首先优化您的查询，否则您需要重新检阅您的设置。
常见错误:
运行默认设置。（当然如果您用来在您的本儿上存您的DVD收集资料， 那当然可以）
直接使用其他人的My.conf文件，都没检查一下是否符合您的需求。
虽然只有一两个查询需要，您仍为它设置了一个巨大的全局设置。您可以这样：
SET sort_buffer_size=128000000;

set sort_buffer_size=DEFAULT

我们能得到的信息：
SHOW STATUS
SHOW INNODB STATUS
操作系统的命令比如：vmstat,iostat,mpstat

使用内存:
为Mysql server分配更多内存常常带来显著的性能提升.
但是分配过多的内存会损失性能甚至是稳定性
检查一下：
Swapping
查看swap IO ，这比简单地使用swap space要好
使用了32位以上的地址(确保OS和mysql server都是64位的)

战胜Swapping
有些操作系统会使用Swap,虽然内存充足。
处理好IO cache和swap间的平衡。
设置VM子系统为只有在最后一次使用resort的时候才swap
tune VM subsystem to use swap only as last resort
echo 1 >/proc/sys/vm/swappiness
如果在linux上使用innodb可以用直接磁盘IO:
innodb_flush_method=O_DIRECt

了解一下变量的单位：
table_cache=128M
错了，它的单位是用个(entries)来衡量的。
key_buffer_size=1024
错了，key buffer size要用bytes来设定。
innodb_max_dirty_pages_pct=8GB
这个设置准确无误。

了解作用域和内存分配规则
key_buffer_size
全局的，由所有线程共用。
sort_buffer_size
需要排序时将内存分配给线程。
read_buffer_size:
一次性就分配指定大小的内存，虽然可能更小的内存就够了
过大的值反而会让事情慢得糟糕
更多的分配常常由OS来完成。
tmp_table_size:
指定一个最大值，在需要的时候会增长到这个值 。
调得过大也没什么事

细节:
关于SHOW STATUS
show global status:了解服务器的全局状态。了解负载的好方法。在Mysql4.1和以下版本中唯一一个。
show local status:
查询/会话配置的好东西
从mysql 5.0开始能用
show status 默认就是这个
有些变量是全局的
也出现会在show status 的输出中
mysqladmin extended -i100 -r
抽样一下，看看Mysql server现在干了些什么，这是个好办法
Show status:
aborted clients:
不用担心，许多程序都没有恰当地关闭连接。
aborted_connects:
可能意味着认证失败，网络超时或其他错误。
值得看一看，因为可能引起主机blocked out
max_connect_errors=100000
可能意味着有人尝试暴力密码破解
binlog_cache_disk_use/binlog_cache_use
bin log 溢出到磁盘的频率。
如果太频繁，需要增大–binlog_cache_size
com_xxx:
按查询来了解服务器负载
查询可以是相当复杂多样的

Powered by WordPress with Pool theme design by Borja Fernandez.
Entries and comments feeds. Valid XHTML and CSS. ^Top^