手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆
浏览模式: 标准 | 列表Tag:mysql

SQL,大量的SQL

这两天在折腾数据,写了很多很多SQL(当然是对我来说,比如象锅巴那种牛人,他们一写就是几百上千行的,我不能比),我只写了100多行。基本上所有的操作都通过SQL完成了,除非实在没办法完成的。才用PHP处理。

对我来说,这两天 insert into xxx (xxx) select xxx from xxx where xxx,写的很多了

还有update a,b set a.xx=b.xx where a.id=b.id之类的也很多

在锅巴哥哥的介绍下,还知道把一句SQL分成多句,或者用下面这张图解决。。。

大小: 65.24 K
尺寸: 360 x 376
浏览: 1313 次
点击打开新窗口浏览全图

嗯,本来想用存储过程的,但赶时间,来不及学了。几年前看的东西全忘光了。靠,晚上回去翻开我的mysql书看一下下先。

 

Tags: mysql, sql

张宴:当MySQL 和 Memcached 遇到尾部空格时

张宴遇到的问题,我没有遇到过,只是以前知道,mysql在varchar类型插入的时候,会把字符串末尾的空格取消的(难道我记错了?),char的没记。不过按照char的类型,他是会用空格补全长度。所以,即使没空格也会多出空格。但,用PHP取出来后,我就没有注意过了。。。

下面是张宴遇到的问题,不过我觉得还是有必要用PHP实现一下看看究竟怎么回事。。。

同事下午遇到一问题,MySQL 和 Memcached 对于同一个key,不能对应起来。最终原因是:PHP将key写入MySQL数据库之前,没有经过trim()过滤首尾空格(关键是尾部空格),结果:

1、MySQL的varchar、char类型字段,SELECT .... WHERE 查询时忽略字段的尾部空格。例如varchar类型字段uidcode中,存储的含有尾部空格的内容“rewinx ”,通过以下三种方式均可查询到:

大小: 4.38 K
尺寸: 420 x 351
浏览: 1217 次
点击打开新窗口浏览全图

在MySQL手册第“11.4.1. CHAR和VARCHAR类型”节中写道(http://dev.mysql.com/doc/refman/5.1/zh/column-types.html#char): 请注意所有MySQL校对规则属于PADSPACE类。这说明在MySQL中的所有CHAR和VARCHAR值比较时不需要考虑任何尾部空格。请注意所有 MySQL版本均如此,并且它不受SQL服务器模式的影响。

   2、PHP 的 Memcache 扩展,则在set、get时,自动将key中的空格,转换成了半角下划线“_”。当然,直接通过 Memcached 协议来读取,key值“rewinx ”就要写成“rewinx_”了。

示 例:testmc.php

PHP代码
  1. <?php  
  2. $memcache_obj = new Memcache;  
  3. $memcache_obj->connect('192.168.8.34', 11211);  
  4. $memcache_obj->set('rewinx ''hello world');  
  5. echo $memcache_obj->get('rewinx ')."\r\n";  
  6. ?>  

大小: 3.28 K
尺寸: 434 x 151
浏览: 1192 次
点击打开新窗口浏览全图

Tags: mysql, memcached

好文推荐:五款常用mysql slow log分析工具的比较

一直在用MYSQL,因此对于慢查询就非常在意,虽然自己写代码的时候不是特别注意,但真正上线后还是要关注关注的。

最简单的看LOG就是用phpmyadmin查看一下,但这终究不是办法。所幸,工具还是很多的。javaeye上就有人贴出了工具和使用方法 :

启用 slow log

有两种启用方式:
1, 在my.cnf 里 通过 log-slow-queries[=file_name]
2, 在mysqld进程启动时,指定--log-slow-queries[=file_name]选项

比较的五款常用工具

mysqldumpslow, mysqlsla, myprofi, mysql-explain-slow-log, mysqllogfilter

mysqldumpslow, mysql官方提供的慢查询日志分析工具. 输出图表如下:
大小: 256.22 K
尺寸: 500 x 210
浏览: 1642 次
点击打开新窗口浏览全图
主要功能是, 统计不同慢sql的
出现次数(Count), 
执行最长时间(Time), 
累计总耗费时间(Time), 
等待锁的时间(Lock), 
发送给客户端的行总数(Rows), 
扫描的行总数(Rows), 
用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示).

mysqlsla, hackmysql.com推出的一款日志分析工具(该网站还维护了 mysqlreport, mysqlidxchk 等比较实用的mysql工具)
大小: 211.77 K
尺寸: 500 x 268
浏览: 1574 次
点击打开新窗口浏览全图
整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等.
 
格式说明如下:
总查询次数 (queries total), 去重后的sql数量 (unique)
输出报表的内容排序(sorted by)
最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数.
 
Count, sql的执行次数及占总的slow log数量的百分比.
Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.
95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.
Lock Time, 等待锁的时间.
95% of Lock , 95%的慢sql等待锁时间.
Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.
Rows examined, 扫描的行数量.
Database, 属于哪个数据库
Users, 哪个用户,IP, 占到所有用户执行的sql百分比
 
Query abstract, 抽象后的sql语句
Query sample, sql语句
 
除了以上的输出, 官方还提供了很多定制化参数, 是一款不可多得的好工具.
 
mysql-explain-slow-log, 德国人写的一个perl脚本.
http://www.willamowius.de/mysql-tools.html
大小: 107.58 K
尺寸: 500 x 147
浏览: 1577 次
点击打开新窗口浏览全图
大小: 98.35 K
尺寸: 500 x 145
浏览: 1548 次
点击打开新窗口浏览全图
功能上有点瑕疵, 不仅把所有的 slow log 打印到屏幕上, 而且统计也只有数量而已. 不推荐使用.
mysql-log-filter, google code上找到的一个分析工具.提供了 python 和 php 两种可执行的脚本.
http://code.google.com/p/mysql-log-filter/
大小: 179.2 K
尺寸: 500 x 156
浏览: 1554 次
点击打开新窗口浏览全图
功能上比官方的mysqldumpslow, 多了查询时间的统计信息(平均,最大, 累计), 其他功能都与 mysqldumpslow类似.
特色功能除了统计信息外, 还针对输出内容做了排版和格式化, 保证整体输出的简洁. 喜欢简洁报表的朋友, 推荐使用一下.
myprofi, 纯php写的一个开源分析工具.项目在 sourceforge 上.
http://myprofi.sourceforge.net/
大小: 176.74 K
尺寸: 500 x 137
浏览: 1558 次
点击打开新窗口浏览全图
功能上, 列出了总的慢查询次数和类型, 去重后的sql语句, 执行次数及其占总的slow log数量的百分比.
从整体输出样式来看, 比mysql-log-filter还要简洁. 省去了很多不必要的内容. 对于只想看sql语句及执行次数的用户来说, 比较推荐.

总结

工具/功能 一般统计信息 高级统计信息 脚本 优势
mysqldumpslow 支持 不支持 perl mysql官方自带
mysqlsla 支持 支持 perl 功能强大,数据报表齐全,定制化能力强.
mysql-explain-slow-log 支持 不支持 perl
mysql-log-filter 支持 部分支持 python or php 不失功能的前提下,保持输出简洁
myprofi 支持 不支持 php 非常精简
---EOF---
作者:galaxystar,来自:http://www.javaeye.com/topic/242516,他现在的博客是:http://kenwublog.com/。

Tags: slow query, mysql, tools

mysql server 小技巧

很多人都使用过mysql命令行,不可否认的是在命令行下如果频繁切换use的database,你会感觉到很迷惘,不太清楚当前的库或表是哪个,于是发现这个技巧后就欣欣然的COPY下来与各位一起分享一下。

在my.cnf 的 mysql 端 添加如下设置

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
prompt="(\u:[email protected] \R:\m)[\d]: "

会产生如下效果:

root@ubuntu:/home/hew# mysql -u hew -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 320
Server version: 5.1.41-3ubuntu12.1 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
(hew:mysql1@linuxbyte.org 21:12)[(none)]: use linuxbyte #注意这里
 
Database changed
 
(hew:mysql1@linuxbyte.org 21:13)[linuxbyte]: use linuxsky; #这里
 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
 
(hew:mysql1@linuxbyte.org 21:13)[linuxsky]: #这里

如上所示我们可以在mysql命令行下显示当前的mysql 用户,所在主机,时间和所用的数据库。
这个在关键时刻可以让我们避免很多误操作。

原文来自:http://www.blogread.cn/it/article.php?id=1605

Tags: mysql, 技巧

MySQL时区设置

MySQL 时区默认是服务器的时区。
可以通过以下命令查看

SQL代码
  1. mysql> SHOW VARIABLES LIKE '%time_zone%';  
  2. +------------------+--------+  
  3. | Variable_name    | Value  |  
  4. +------------------+--------+  
  5. | system_time_zone | CST    |  
  6. | time_zone        | SYSTEM |  
  7. +------------------+--------+  
  8. rows IN SET (0.00 sec)  

可以通过修改my.cnf, 在 [mysqld] 之下加来修改时区。

SQL代码
  1. default-time-zone=timezone 
  2. 例如:
  3. default-time-zone='+8:00' 

 

改了记得重启msyql.
注意一定要在 [mysqld] 之下加 ,否则会出现错误: unknown variable ‘default-time-zone=+8:00′

另外也可以通过命令

SQL代码
  1. SET time_zone=timezone  
  2. 例如:比如北京时间(GMT+0800)  
  3. SET time_zone='+8:00'  

这个和php的时区设置又有点差别,比如北京时间在php中是

PHP代码
  1. date_default_timezone_set('Etc/GMT-8');  

 

美国pst时间(GMT-08:00)

SQL代码
  1. SET time_zone = '-8:00';  
SQL代码
  1. mysql> SET time_zone = '+8:00';  
  2. Query OK, 0 rows affected (0.00 sec)  
  3. mysql> SELECT now();  
  4. +---------------------+  
  5. | now()               |  
  6. +---------------------+  
  7. | 2008-12-29 11:26:36 |  
  8. +---------------------+  
  9. 1 row IN SET (0.00 sec)  
  10. mysql> SET time_zone = '-8:00';  
  11. Query OK, 0 rows affected (0.00 sec)  
  12. mysql> SELECT now();  
  13. +---------------------+  
  14. | now()               |  
  15. +---------------------+  
  16. | 2008-12-28 19:27:09 |  
  17. +---------------------+  
  18. 1 row IN SET (0.00 sec)  

原文来自,冰山上的播客,http://xinsync.xju.edu.cn/index.php/archives/6842,略作修改。

Tags: mysql, date_set