C1G军火库

项目中的静态页根据信息表的id来自动分级目录，结构为每3位为一级。
如id=347234 就会先补全至9位->000347234再分级成/000/347/show_234.html。

程序中是用php来做的，目前需要导出一批地址数据，懒的写程序就直接用sql了。
SELECT id,CompanyName,CONCAT(’http://www.c1gstudio.com/’,left(lpad(id,9,0),3),’/',SUBSTR(lpad(id,9,0),4,3),’/show_’,right(lpad(id,9,0),3),’.html’) as url
FROM `t_reprint`
WHERE FromSite=’C1G军火库’ and `OperatorID`=’1′

在phpmyadmin 3.0.0中导出界面可能对中文支持不好，导致语句中断，不能导出
在远程创建用户，在本地phpmyadmin 2.8.2.4用远程用户导出，报substr函数未找到…(但在本地测试是可以的)

使用命令行导出
./mysql -hlocalhost -uroot -p
输入密码
use db;
SELECT id,CompanyName,CONCAT(’http://www.c1gstudio.com/’,left(lpad(id,9,0),3),’/',SUBSTR(lpad(id,9,0),4,3),’/show_’,right(lpad(id,9,0),3),’.html’) as url
INTO OUTFILE ‘./output.txt’
FROM `t_reprint`
WHERE FromSite=’C1G军火库’ and `OperatorID`=’1′ ;
 然后在mysql/var/下就可以找到output.txt

http://dev.mysql.com/doc/refman/5.1/zh/installing.html#upgrading-from-5-0

首先是perl支持

shell>perl -MCPAN -e shell

cpan>install DBI

cpan>install DBD::mysql ( 安装这个时test通不过)

cpan>q

退出

unset LANG

cd ~/.cpan/build/DBI-1.607
perl Makefile.PL
make test
make install

cd ../DBD-mysql-4.007
perl Makefile.PL –mysql_config=/opt/mysql/bin/mysql_config
make
make test
<coolcode>
 failed: Can’t load ‘/root/.cpan/build/DBD-mysql-4.007/blib/arch/auto/DBD/mysql/mysql.so’
</coolcode>

ln -s /opt/mysql/lib/mysql/* /usr/lib/
make test
<coolcode>
/root/.cpan/build/DBD-mysql-4.007/blib/arch/auto/DBD/mysql/mysql.so: undefined symbol:
</coolcode>
make realclean
perl Makefile.PL –mysql_config=/opt/mysql/bin/mysql_config –libs=”-L/opt/mysql/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm”
<coolcode>
all skipped: ERROR: Access denied for user ‘root’@'localhost’ (using password: NO). Can’t continue test
</coolcode>
perl Makefile.PL –mysql_config=/opt/mysql/bin/mysql_config –libs=”-L/opt/mysql/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm” –testpassword=123456
make
make test
<coolcode>
t/00base………………..String found where operator expected at t/00base.t line 20, near “BAIL_OUT “Unable to load DBI”"
        (Do you need to predeclare BAIL_OUT?)
</coolcode>

make realclean
perl Makefile.PL –mysql_config=/opt/mysql/bin/mysql_config –libs=”-L/opt/mysql/lib/mysql -lmysqlclient -lz” –cflags=-I/opt/mysql/include/mysql –testhost=localhost –testsocket=/opt/mysql/mysql.sock –testdb=bugdb –testuser=root –testpassword=123456
make
make test
<coolcode>
t/00base………………..String found where operator expected at t/00base.t line 20, near “BAIL_OUT “Unable to load DBI”"
        (Do you need to predeclare BAIL_OUT?)
</coolcode>
(在另一机器上没出这个错)-_-*!!! 直接make install
检察是否安装成功
shell>perldoc DBI
shell>perldoc DBI::mysql

从源码装

http://search.cpan.org/

http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql.pm#INSTALLATION

gzip -cd DBI-(version).tar.gz | tar xf - cd DBI-(version) perl Makefile.PL make make test make install cd .. gzip -cd Data-ShowTable-(version).tar.gz | tar xf - cd Data-ShowTable-3.3 perl Makefile.PL make make install

Data-ShowTable-3.3.tar.gz 安装时有个错误 *** ERROR: Unterminated I<…> at line 724 in file ShowTable.pm 用以下方法解决 http://lists.mysql.com/perl/1015

cd .. gzip -cd DBD-mysql-(version)-tar.gz | tar xf - cd DBD-mysql-(version) perl Makefile.PL –mysql_config=/opt/mysql/bin/mysql_config –libs=”-L/opt/mysql/lib/mysql -lmysqlclient -lcrypt -lnsl -lm -lz” –cflags=-I/opt/mysql/include/mysql –testsocket=/opt/mysql/mysql.sock –testhost=localhost –testuser=root –testpassword=****** make make test make install

在数据库段分配一个专门用于备份的用户
mysql> grant select,reload,lock tables on *.* to ‘hotcopyer’@‘localhost’ identified by ‘123456′;
mysql> grant insert on hotcopy.checkpoint to hotcopyer@‘localhost’;
mysql> flush privileges;

建表记录下历史
create database hotcopy;
create table checkpoint(time_stamp timestamp not null,src varchar(32),dest varchar(60), msg varchar(255));

cd /opt/mysql/bin/
./mysqlhotcopy test_ucenter –noindices –allowold –checkpoint hotcopy.checkpoint –addtodest /home/admin –user=hotcopyer –password=123456

你也可以将备份用的用户和密码放在~/.my.cnf下
# vi /root/.my.cnf
<coolcode>
[client]
host=localhost
user=hotcopyer
password=123456
</coolcode>

mysql有一个功能就是可以log下来运行的比较慢的sql语句，默认是没有这个log的，为了开启这个功能，要修改my.cnf或者在mysql启动的时候加入一些参数。如果在my.cnf里面修改，需增加如下几行

long_query_time = 1
log-slow-queries = /var/youpath/slow.log
log-queries-not-using-indexes

long_query_time 是指执行超过多久的sql会被log下来，这里是1秒。
log-slow-queries 设置把日志写在那里，可以为空，系统会给一个缺省的文件host_name-slow.log，我生成的log就在mysql的data目录
log-queries-not-using-indexes 就是字面意思，log下来没有使用索引的query。

把上述参数打开，运行一段时间，就可以关掉了，省得影响生产环境。

接下来就是分析了，我这里的文件名字叫host-slow.log。
先mysqldumpslow –help以下，俺主要用的是
-s ORDER what to sort by (t, at, l, al, r, ar etc), ‘at’ is default
-t NUM just show the top n queries
-g PATTERN grep: only consider stmts that include this string

-s，是order的顺序，说明写的不够详细，俺用下来，包括看了代码，主要有
c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒叙
-t，是top n的意思，即为返回前面多少条的数据
-g，后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log

上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。
mysqldumpslow -t 10 -s t -g “left join” host-slow.log
这个是按照时间返回前10条里面含有左连接的sql语句。

用了这个工具就可以查询出来那些sql语句是性能的瓶颈，进行优化，比如加索引，该应用的实现方式等。

可以用另一表的数据更新当前表。
UPDATE items as a,month as b SET a.price=b.price
WHERE a.id=b.id;

基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是4。索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数据列用于记录性别（只有”M”和”F”两种值），那么索引的用处就不大。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是”30%”。

需要修改下php.ini里odbc的设置，否则遇到长字段可能只取到部分数据。

Returns a random floating-point value v between 0 and 1 inclusive (that is, in the range 0 <= v <= 1.0). If an integer argument N is specified, it is used as the seed value, which produces a repeatable sequence.

mysql> SELECT RAND();
-> 0.9233482386203
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND();
-> 0.63553050033332
mysql> SELECT RAND();
-> 0.70100469486881
mysql> SELECT RAND(20);
-> 0.15888261251047

To obtain a random integer R in the range i <= R <= j, use the expression FLOOR(i + RAND() * (j – i). For example, to obtain a random integer in the range of 7 to 12 inclusive, you could use the following statement:

SELECT FLOOR(7 + (RAND() * 5));

将discuz论坛中所有贴子的浏览数改为13~93之间
update cdb_threads set views=FLOOR( 13+(RAND() * 80));

Error : Unknown column ‘j.cid’ in ‘on clause’
Errno : 1054
select j.*,jf.jid,jf.pid,jf.man,m.cid as comid,m.company from wane_comjobs j,wane_comjobs_fields jf left join wane_member_comfields m on (j.cid=m.cid) where j.jid=jf.jid

语句在4.0上可以跑,看文档在5.1上也能跑,在5.0上跑时需将要连接的表放在右边。

如
SELECT j. * , jf.jid, jf.pid, jf.man, m.cid AS comid, m.company
FROM wane_comjobs j,wane_comjobs_fields jf
LEFT JOIN wane_member_comfields m ON ( j.cid = m.cid )
WHERE j.jid = jf.jid

大家在写sql 语句的时候， 如果是 select .. where 类型的语句， 有注意到条件的前后顺序吗？我今天做个小实验。
比如查询地址里 包含“海口市”及“振兴路” 两个关键字的数据， 一般时候可能会用
select * from dm_addr where addr like ‘%海口市%’ and addr like ‘%振兴路%’ 的形式，但其实这种查询效率不高，原因在于条件的前后顺序。以下是测试结果

mysql> select count(1) from dm_addr where addr like ‘%振兴路%’ and addr like ‘%海口市%’;
+———-+
| count(1) |
+———-+
| 96 |
+———-+
1 row in set (0.82 sec)

mysql> select count(1) from dm_addr where addr like ‘%海口市%’ and addr like ‘%振兴路%’;
+———-+
| count(1) |
+———-+
| 96 |
+———-+
1 row in set (0.91 sec)

我做了很多次查询
where addr like ‘%海口市%’ and addr like ‘%振兴路%’ 总是比 where addr like ‘%振兴路%’ and addr like ‘%海口市%’
慢，原因是查询是先处理第一条件，然后在处理第二个条件，先查询出所有 包含“海口市”的记录，再在这些记录中查询包含”振兴路” 的记录。 对于地址情况， 包含“海口市”的记录可能大于”振兴路” 的记录，我做测试的表有2万条数据， 已经有大概0.1s的差别。如果碰到大数据，这个差别会更大。
按以上的分析结果， 我们写SQL语句的时候， 对于select …where … 类型的语句，应该把查询结果范围小的条件放在前面，查询范围大的条件放在后面，这样会提高效率。

对于OR的情况：
测试如下：

mysql> select count(1) from dm_addr where addr like ‘%海口市%’ or addr like ‘%振兴路%’;
+———-+
| count(1) |
+———-+
| 39168 |
+———-+
1 row in set (0.84 sec)
mysql> select count(1) from dm_addr where addr like ‘%振兴路%’ or addr like ‘%海口市%’;
+———-+
| count(1) |
+———-+
| 39168 |
+———-+
1 row in set (0.95 sec)

OR语句的处理过程是这样， 对于每一条地址， 逐个分析条件，如果符合第一个条件，就不做第二条件的判断，那么我们应该把容易做判断的条件放在前面， 比如查询地址里 包含“海口市”或“振兴路” 个关键字的数据，按地址一般排法，XX市应该在XX路前。所以如果用第一种方式，那就效率高，判断有海口市就可以确认这行地址有效了。 所以在写OR类型的条件时， 应该把容易判断的条件写在前面。

再做一个NOT … OR .. NOT … 测试，查询不包含 海口市 或者不包含 振兴路 的数据
mysql> select count(1) from dm_addr where addr not like ‘%海口市%’ or addr not like ‘%振兴路%’;
+———-+
| count(1) |
+———-+
| 38752 |
+———-+
1 row in set (0.92 sec)

mysql> select count(1) from dm_addr where addr not like ‘%振兴路%’ or addr not like ‘%海口市%’;
+———-+
| count(1) |
+———-+
| 38752 |
+———-+
1 row in set (0.86 sec)

因为每条数据都有海口市， 所以对于第一个查询，判断第一个条件都失败了， 都需要判断第二个。
对于第二个查询， 只要判断第一个条件为真就可以确定这个记录， 不需要判断第一个条件，
所以第二个查询效率高过第一个。

结论：对于select … where … 类型的语句。
对于OR条件， 需要把命中率高的条件放在前面。
对于AND条件，需要把条件限制范围小的条件放在前面。
希望这些实验能提高phper 的程序效率。以上测试是对于mysql 5.0 win 做的。好象记得ORACLE刚好相反， 是先判断最后的条件。