Month: July 2011

MySQL目前还不支持动态游标(可能这个名词用在这里不准确)，但很多时候我们需要这样的一个功能(http://bugs.mysql.com/bug.php?id=17153)
比如在存储过程中根据传入的参数动态查询出数据，然后遍历数据，这个时候我们当然想动态的构建出这个游标出来

人民大众就开始想尽办法来实现类似的功能，比如下面的创建一个临时表来完成

mysql> CREATE TEMPORARY TABLE tmp_data (uid int(11) NOT NULL) ENGINE=MEMORY DEFAULT CHARSET=utf8;

注意这里为了提高速度用的是MEMORY引擎，当然它有一些限制行的东西，比如表的大小限制，某些类型的字段不能保存
可以根据自己的情况来抉择
更多信息可以参考http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
还有一些参考意义的测评也可以借鉴，比如http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

这里还给它增加了个索引
mysql> ALTER TABLE tmp_data ADD INDEX idx_uid (uid);
mysql> SHOW INDEX FROM tmp_data;

当然用完之后记得清理表，简单的方法就是
mysql> DROP TEMPORARY TABLE tmp_data;

临时表在不同连接之间是不可见的，但是在多线程的环境中，这个还是应该值得注意

还有更多的问题值得研究，比如create和drop临时表不会产生事务提交
但直接DROP TABLE tmp_data;也会删除临时表，但是会提交前面打开事务

临时表和正式表重名机制等等

另外，其实在MySQL内部它也大量使用了临时表，当然对程序员来说都是透明的，你不用关心它什么时候用，数据是放在内存还是放在硬盘上的
想看的话可以通过explain查看

有关MySQL奇技淫巧(一般都是Linux下试过的，其它平台自行尝试)

0、学会查看MySQL错误日志
日志名一般为HOST_NAME.err，在里面说不定很容易找到你想要的东西

1、开启MySQL General Query Log
想查看MySQL执行了哪些SQL语句？
那么就开启这个日志吧
http://dev.mysql.com/doc/refman/5.1/en/query-log.html
http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html
可以通过启动mysqld加入相关参数或者动态开启

当然最方便的还是动态开启（不用重启数据库，貌似对MySQL版本有要求）
中文参考：http://www.taobaodba.com/html/477_动态开启mysql-general-log.html

mysql> set global log_output = file;
mysql> set global general_log_file=’/home/guohai/dev/x/topics/mysql/sql.log’;
mysql> set global general_log=on;

还有个要求就是MySQL对该日志文件有读写权限，我把它的own和group全改成跟MySQL对应的了，要不然报错

关闭只需要
mysql> set global general_log=off;

2、innotop使用
去http://code.google.com/p/innotop/下载最新版的安装包，这里用的是innotop-1.8.0
按照安装说明安装好，更详细的安装说明可以参见http://crazytoon.com/2008/05/21/mysql-how-do-you-install-innotop-to-monitor-innodb-in-real-time/
可能innotop版本不一样，自己稍微变通下
第一次运行innotop的时候会叫你输入一些配置信息，比如数据库链接信息
这个你可以在~/.innotop/innotop.conf中修改
guohai@KNIGHT:~/.innotop$ tree
.
|– innotop.conf
`– plugins

1 directory, 1 file

man innotop是永远不能少的
另外可以参见http://5iwww.blog.51cto.com/856039/570669
http://blog.csdn.net/qiuyepiaoling/archive/2010/11/29/6042379.aspx

3、mysql命令的一些有用参数选项
生产环境中手工操作数据库是个技术活
有些参数可以方便我们获取需要的数据，或者节省我们的时间
一些明显的信息提示或许可以帮助我们减少出错的概率
比如
mysql>prompt \h:\u@\d>
PROMPT set to ‘\h:\u@\d>’
localhost:root@hpm>
这样你就明显的看到，你是在哪台机器，用户是什么，操作的数据库是什么

MySQL Command Line Pager
mysql>\P more
mysql>\P less
mysql>\P

下面是别人总结出来有用的
http://www.developer.com/db/10-command-line-timesavers-for-mysql-tasks.html
http://www.ningoo.net/html/2008/mysql_cmdline_html_xml_prompt.html
自己也可以查看手册找到一些有用的信息

4、create table log_20110630 like log_template;
like子句会复制表结构，包括索引等，不包括权限

create table log_20110701 as select * from log_20110630;
复制数据，不包括索引