为IT服务 [龙飞的博客]

今天在把博客的系统换成64位装mysql时提示：
error while loading shared libraries: libstdc++.so.5: cannot open shared object file: No such file or directory
之前装32位习惯了yum -y install compat-libstdc++-33 后发现还是这样，才想起来是64位
重新yum -y install compat-libstdc++-33.x86_64 在安装就ok了
所以安装64位系统这些lib库可留心下把64位的也装了。

以前也是一时起兴，顺手试了试结果php configure的时候出错，就放在那一直也没空弄。
最近打算把部分虚拟机扩到8G内存测试下压力所以又把64的系统装起来再次尝试。
吃好晚饭忽然觉得有点头晕恶心，什么事情都干不了，就在那里翻config的log，结果发现原来是krb5没装，yum install krb5 krb5-devel后重新configure搞定。忽然发现头也不疼了哈哈。

原文地址：http://www.chedong.com/blog/archives/001451.html
官网：https://launchpad.net/mysql-tuning-primer
加path那段我自己写的，不然报错跟输错密码一样，郁闷的让我想了半天密码

年初的时候收藏过一篇关于mysqlreport的报表解读，和内置的show status,和show variables相比mysqlreport输出一个可读性更好的报表；但Sundry MySQL提供的脚本相比mysqlreport更进一步：除了报表还进一步提供了修改建议。安装和使用非常简单：

wget http://www.day32.com/MySQL/tuning-primer.sh
chmod +x tuning-primer.sh

先要把mysql的bin目录加进path

export PATH=/usr/local/mysql/bin:$PATH

./tuning-primer.sh

然后根据提示看把，按照问题重要程度分别用黄色/红色字符标记问题

最近迁移的机器多了，碰到的问题也就多。这个pureftpd+mysql就把我折腾了1天
症状就是登陆ftp后输完用户名和密码，ftp链接立刻就断开了
[00:14:19] [R] 正在连接到 10.130.128.146 -> IP=10.130.128.146 PORT=21 (次尝试 # 5)
[00:14:19] [R] 已连接到 10.130.128.146
[00:14:19] [R] 220---------- Welcome to Pure-FTPd ----------
[00:14:19] [R] 220-You are user number 1 of 25 allowed.
[00:14:19] [R] 220-Local time is now 00:13. Server port: 21.
[00:14:19] [R] 220-This is a private system - No anonymous login
[00:14:19] [R] 220 You will be disconnected after 15 minutes of inactivity.
[00:14:19] [R] USER user89
[00:14:19] [R] 331 User user89 OK. Password required
[00:14:19] [R] PASS (hidden)
[00:14:19] [R] 连接失败 (连接丢失)

注意这提示并非用户名密码错误。用户验证已经通过了。结果噩梦开始了，其实解决很简单，只要mysql里取出来的用户uid&gid和用户主目录的uid&gid对应就行。我一开始想到过这个问题，结果editplus里开了2个conf，我改的一个，结果上传的另一个….我的天，我晚上在家从头开始配的时候竟然犯了同样的错误，绕了一大圈弯路，最后关配置文件打算用puredb才发现这个问题。

具体修改pureftpd-mysql.conf
# Query to execute in order to fetch the system user name or uid
# MYSQLGetUID SELECT Uid FROM users WHERE User="\L"
# Optional : default UID - if set this overrides MYSQLGetUID
MYSQLDefaultUID 501
# Query to execute in order to fetch the system user group or gid
# MYSQLGetGID SELECT Gid FROM users WHERE User="\L"
# Optional : default GID - if set this overrides MYSQLGetGID
MYSQLDefaultGID 501

我这所有用户目录所有者都是www:www，所以对照下etc/passwd里面www的uid和gid直接定义了MYSQLDefaultUID和MYSQLDefaultGID。不取mysql的

这里推荐个不错的pureftpd教程
Pure-FTPd + LDAP + MySQL + PGSQL + Virtual-Users + Quota How To

http://netkiller.8800.org/article/ftpserver/

汗。。。看来最近迁移的服务器太多搞得头晕了，竟然是这种低级错误。看来国庆长假要好好休息了。

今天在做服务器搬迁，新的主机使用的mysql5.0，一开始直接把data目录复制了过去，结果发现所有char相关字段长度全部变成原来的一半。无奈只好用dump了
我这的环境都是gbk所以下面是以gbk为例子
导出：

1

mysqldump -u xxx -pxxx --skip-extended-insert --default-character-set=gbk db1 > d:\db1.sql

导入：

1

mysql.exe -u xxx -pxxx --default-character-set=gbk db1 < d:\db1.sql

由于4.0是没有编码设置，所以在新的5.0上面要设置mysql的conf把default-character-set=gbk，character-set-server=gbk

这样就能解决一般经常碰到的乱码问题了

当然导入过程中可能会有一些错误提示，根据提示修改下导出的sql文件就行。一般比如group这类特殊字段名要改成`group`才能正确导入到5.0。建议使用notepad++来编辑sql文件

Q：列出在 PHP 中执行排序要优于在 MYSQL 中排序的原因？给一些必须在MYSQL中排序的实例？

A：通常来说，执行效率需要考虑 CPU、内存和硬盘等的负载情况，假定 MYSQL 服务器和 PHP 的服务器都已经按照最适合的方式来配置，那么系统的可伸缩性（Scalability）和用户感知性能（User-perceived Performance）是我们追求的主要目标。在实际运行中，MYSQL 中数据往往以 HASH tables、BTREE 等方式存贮于内存，操作速度很快；同时 INDEX 已经进行了一些预排序；很多应用中，MYSQL 排序是首选。而在应用层（PHP）中排序，也必然在内存中进行，与 MYSQL 相比具有如下优势：

• 1、 考虑整个网站的可伸缩性和整体性能，在应用层（PHP）中排序明显会降低数据库的负载，从而提升整个网站的扩展能力。而数据库的排序，实际上成本是非常高的，消耗内存、CPU，如果并发的排序很多，DB 很容易到瓶颈。
• 2、 如果在应用层(PHP)和MYSQL之间还存在数据中间层，合理利用，PHP会有更好的收益。
• 3、 PHP在内存中的数据结构专门针对具体应用来设计，比数据库更为简洁、高效；
• 4、 PHP不用考虑数据灾难恢复问题，可以减少这部分的操作损耗；
• 5、 PHP不存在表的锁定问题；
• 6、 MYSQL中排序，请求和结果返回还需要通过网络连接来进行，而PHP中排序之后就可以直接返回了，减少了网络IO。

至于执行速度，差异应该不会很大，除非应用设计有问题，造成大量不必要的网络IO。另外，应用层要注意PHP 的 Cache 设置，如果超出会报告内部错误；此时要根据应用做好评估，或者调整Cache。具体选择，将取决于具体的应用。

列出一些 PHP 中执行排序更优的情况：

• 1、 数据源不在 MYSQL 中，存在硬盘、内存或者来自网络的请求等；
• 2、 数据存在 MYSQL 中，量不大，而且没有相应的索引，此时把数据取出来用PHP排序更快；
• 3、 数据源来自于多个 MYSQL 服务器，此时从多个 MYSQL 中取出数据，然后在PHP中排序更快；
• 4、 除了 MYSQL 之外，存在其他数据源，比如硬盘、内存或者来自网络的请求等，此时不适合把这些数据存入 MYSQL 后再排序；

列出一些必须在 MYSQL 中排序的实例：

• 1、 MYSQL 中已经存在这个排序的索引；
• 2、 MYSQL 中数据量较大，而结果集需要其中很小的一个子集；比如 1000000 行数据，取TOP 10；
• 3、 对于一次排序、多次调用的情况，比如统计聚合的情形，可以提供给不同的服务使用，那么在 MYSQL 中排序是首选的。另外，对于数据深度挖掘，通常做法是在应用层做完排序等复杂操作，把结果存入MYSQL即可，便于多次使用。
• 4、 不论数据源来自哪里，当数据量大到一定的规模后，由于占用内存/Cache 的关系，不再适合 PHP 中排序了；此时把数据复制、导入或者存在 MYSQL ，并用 INDEX 优化，是优于 PHP 的。不过，用 Java，甚至 C++ 来处理这类操作会更好。 [有些类似大数据集聚合或者汇总的数据，在客户端排序得不偿失。当然，也有用类似搜索引擎的思路来解决类似应用的情况。]

从网站整体考虑，就必须加入人力和成本的考虑。假如网站规模和负载较小，而人力有限（人数和能力都可能有限），此时在应用层（PHP）做排序要做不少开发和调试工作，耗费时间，得不偿失；不如在 DB 中处理，简单快速。对于大规模的网站，电力、服务器的费用很高，在系统架构上精打细算，可以节约大量的费用，是公司持续发展之必要；此时如果能在应用层(PHP) 进行排序并满足业务需求，尽量在应用层进行。

–EOF–

之前做过一次论坛宠物的ORDER BY RAND()的优化，这篇文章只是深入的去读解优化的原理和具体步骤。觉得讲的不错分享一下。

原文地址：http://jan.kneschke.de/projects/mysql/order-by-rand

翻译：ShiningRay

译者序
之前有位朋友提到从MySQL随机取1条记录其实只要SELECT * FROM table ORDER BY RAND() LIMIT 1即可。其实这个语句有很大的性能问题，对于大表的效率是非常低下的。我们可以看一下MySQL对其的解释：

EXPLAIN SELECT *
FROM `money_logs`
ORDER BY RAND( )
LIMIT 1
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table ALL NULL NULL NULL NULL 173784 Using temporary; Using filesort

这个SQL语句无法使用任何索引，还必须使用临时表和文件排序，在一个15万条记录的MyISAM表需要花大约0.3秒。已经是相当慢的了。如何优化，请往下看：

第一个例子我们先假设ID是从1开始并且1和ID的最大值之间没有任何空档。

将工作移入应用程序
第一个想法：如果我们可以事先在应用程序中计算出ID，那么就可以简化整个工作。

SELECT MAX(id) FROM random;
## 在应用程序中生成随机id
SELECT name FROM random WHERE id = 由于MAX(id) == COUNT(id) 我们只要生成从1到MAX(id)之间一个随机数，并将其传给数据库并取回随机行。

上面第一个SELECT基本上是一个可以被优化掉的空操作。第二个是一个针对常量的eq_ref查询，同样也很快。

将任务放入数据库
不过有必要将其放入应用程序吗？难道我们不能在数据库里完成？

# 生成一个随机 ID
> SELECT RAND() * MAX(id) FROM random;
+——————+
| RAND() * MAX(id) |
+——————+
| 689.37582507297 |
+——————+
# 喔，这是一个浮点数，不过我们需要整数

> SELECT CEIL(RAND() * MAX(id)) FROM random;
+————————-+
| CEIL(RAND() * MAX(id)) |
+————————-+
| 1000000 |
+————————-+
# 好多了。不过性能如何？

> EXPLAIN
SELECT CEIL(RAND() * MAX(id)) FROM random;
+—-+————-+——-+——-+——+————-+
| id | select_type | table | type | rows | Extra |
+—-+————-+——-+——-+——+————-+
| 1 | SIMPLE | random | index | 1000000 | Using index |
+—-+————-+——-+——-+——+————-+
## 一个索引扫描？我们没有对MAX()进行优化

> EXPLAIN
SELECT CEIL(RAND() * (SELECT MAX(id) FROM random));
+—-+————-+——-+——+——+——————————+
| id | select_type | table | type | rows | Extra |
+—-+————-+——-+——+——+——————————+
| 1 | PRIMARY | NULL | NULL | NULL | No tables used |
| 2 | SUBQUERY | NULL | NULL | NULL | Select tables optimized away |
+—-+————-+——-+——+——+——————————+
## 一个简单的子查询给我们将性能找了回来。OK，现在我们知道如何生成随机ID了，不过如何获取记录行？

> EXPLAIN
SELECT name
FROM random
WHERE id = (SELECT CEIL(RAND() *
(SELECT MAX(id)
FROM random));
+—-+————-+——–+——+—————+——+———+——+———+——————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——–+——+—————+——+———+——+———+——————————+
| 1 | PRIMARY | random | ALL | NULL | NULL | NULL | NULL | 1000000 | Using where |
| 3 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+—-+————-+——–+——+—————+——+———+——+———+——————————+
> show warnings;
+——-+——+——————————————+
| Level | Code | Message |
+——-+——+——————————————+
| Note | 1249 | Select 2 was reduced during optimization |
+——-+——+——————————————+哦，不！不要走这条路。虽然它很直观，但是也是最容易犯的错。理由是：在WHERE子句中的SELECT会针对外部SELECT取出的每一行执行一次。这可能会是0到4091行，看你的运气了。

我们必须用一种方法确保随机ID只被生成一次：

SELECT name
FROM random JOIN
(SELECT CEIL(RAND() *
(SELECT MAX(id)
FROM random)) AS id
) AS r2
USING (id);
+—-+————-+————+——–+——+——————————+
| id | select_type | table | type | rows | Extra |
+—-+————-+————+——–+——+——————————+
| 1 | PRIMARY | | system | 1 | |
| 1 | PRIMARY | random | const | 1 | |
| 2 | DERIVED | NULL | NULL | NULL | No tables used |
| 3 | SUBQUERY | NULL | NULL | NULL | Select tables optimized away |
+—-+————-+————+——–+——+——————————+内部的 SELECT 生成了一个常数临时（TEMPORARY）表并且联接（JOIN）只选择了一行。完美。

没有排序、没有应用程序介入，查询的大部分都被优化了。

在数字中加入空档
为了使最终的解决方案通用化，我们必须考虑空档的可能性，如当你删除(DELETE)了记录行。

SELECT name
FROM random AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;
+—-+————-+————+——–+——+——————————+
| id | select_type | table | type | rows | Extra |
+—-+————-+————+——–+——+——————————+
| 1 | PRIMARY | | system | 1 | |
| 1 | PRIMARY | r1 | range | 689 | Using where |
| 2 | DERIVED | NULL | NULL | NULL | No tables used |
| 3 | SUBQUERY | NULL | NULL | NULL | Select tables optimized away |
+—-+————-+————+——–+——+——————————+JOIN现在加入了所有大于等于我们随机数的ID，并且当直接匹配不存在的时候，只选择最临近的记录。不过一旦找到了某一行，我们就立刻停止（LIMIT 1）。同时我们根据索引（ORDER BY id ASC）读取记录。由于我们使用了>=而非=，所以我们可以削去CEIL同时还能获取同样的结果，节省了一点点开销。

平均分布
一旦ID的分布不再是平均的了，那么我们对行的选择也不是完全随机的了。

> select * from holes;
+—-+———————————-+———-+
| id | name | accesses |
+—-+———————————-+———-+
| 1 | d12b2551c6cb7d7a64e40221569a8571 | 107 |
| 2 | f82ad6f29c9a680d7873d1bef822e3e9 | 50 |
| 4 | 9da1ed7dbbdcc6ec90d6cb139521f14a | 132 |
| 8 | 677a196206d93cdf18c3744905b94f73 | 230 |
| 16 | b7556d8ed40587a33dc5c449ae0345aa | 481 |
+—-+———————————-+———-+RAND函数生成诸如9到15之间的ID都会让id 16被选择。

目前还没有针对这个问题的真正的解决方案，不过你的数据是大部分不变的话可以添加一个将行号映射到ID的映射表：

> create table holes_map ( row_id int not NULL primary key, random_id int not null);
> SET @id = 0;
> INSERT INTO holes_map SELECT @id := @id + 1, id FROM holes;
> select * from holes_map;
+——–+———–+
| row_id | random_id |
+——–+———–+
| 1 | 1 |
| 2 | 2 |
| 3 | 4 |
| 4 | 8 |
| 5 | 16 |
+——–+———–+row_id现在则是没有空档的，我们就可以再次运行我们的随机查询了：

SELECT name FROM holes
JOIN (SELECT r1.random_id
FROM holes_map AS r1
JOIN (SELECT (RAND() *
(SELECT MAX(row_id)
FROM holes_map)) AS row_id)
AS r2
WHERE r1.row_id >= r2.row_id
ORDER BY r1.row_id ASC
LIMIT 1) as rows ON (id = random_id);1000次查找之后，我们可以看到一个平均分布：

> select * from holes;
+—-+———————————-+———-+
| id | name | accesses |
+—-+———————————-+———-+
| 1 | d12b2551c6cb7d7a64e40221569a8571 | 222 |
| 2 | f82ad6f29c9a680d7873d1bef822e3e9 | 187 |
| 4 | 9da1ed7dbbdcc6ec90d6cb139521f14a | 195 |
| 8 | 677a196206d93cdf18c3744905b94f73 | 207 |
| 16 | b7556d8ed40587a33dc5c449ae0345aa | 189 |
+—-+———————————-+———-+使用触发器维护有空档的表
让我们就用前面的几个表:

DROP TABLE IF EXISTS r2;
CREATE TABLE r2 (
id SERIAL,
name VARCHAR(32) NOT NULL UNIQUE
);

DROP TABLE IF EXISTS r2_equi_dist;
CREATE TABLE r2_equi_dist (
id SERIAL,
r2_id bigint unsigned NOT NULL UNIQUE
);
一旦我们在r2中改动了某些东西，我们希望r2_equi_dist也被更新。

DELIMITER $$
DROP TRIGGER IF EXISTS tai_r2$$
CREATE TRIGGER tai_r2
AFTER INSERT ON r2 FOR EACH ROW
BEGIN
DECLARE m BIGINT UNSIGNED DEFAULT 1;

SELECT MAX(id) + 1 FROM r2_equi_dist INTO m;
SELECT IFNULL(m, 1) INTO m;
INSERT INTO r2_equi_dist (id, r2_id) VALUES (m, NEW.id);
END$$
DELIMITER ;

DELETE FROM r2;

INSERT INTO r2 VALUES ( NULL, MD5(RAND()) );
INSERT INTO r2 VALUES ( NULL, MD5(RAND()) );
INSERT INTO r2 VALUES ( NULL, MD5(RAND()) );
INSERT INTO r2 VALUES ( NULL, MD5(RAND()) );

SELECT * FROM r2;
+—-+———————————-+
| id | name |
+—-+———————————-+
| 1 | 8b4cf277a3343cdefbe19aa4dabc40e1 |
| 2 | a09a3959d68187ce48f4fe7e388926a9 |
| 3 | 4e1897cd6d326f8079108292376fa7d5 |
| 4 | 29a5e3ed838db497aa330878920ec01b |
+—-+———————————-+
SELECT * FROM r2_equi_dist;
+—-+——-+
| id | r2_id |
+—-+——-+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+—-+——-+
INSERT很简单，但在DELETE时我们需要更新equi-dist-id来维持id的连续。

DELIMITER $$
DROP TRIGGER IF EXISTS tad_r2$$
CREATE TRIGGER tad_r2
AFTER DELETE ON r2 FOR EACH ROW
BEGIN
DELETE FROM r2_equi_dist WHERE r2_id = OLD.id;
UPDATE r2_equi_dist SET id = id – 1 WHERE r2_id > OLD.id;
END$$
DELIMITER ;

DELETE FROM r2 WHERE id = 2;

SELECT * FROM r2;
+—-+———————————-+
| id | name |
+—-+———————————-+
| 1 | 8b4cf277a3343cdefbe19aa4dabc40e1 |
| 3 | 4e1897cd6d326f8079108292376fa7d5 |
| 4 | 29a5e3ed838db497aa330878920ec01b |
+—-+———————————-+
SELECT * FROM r2_equi_dist;
+—-+——-+
| id | r2_id |
+—-+——-+
| 1 | 1 |
| 2 | 3 |
| 3 | 4 |
+—-+——-+
UPDATE就非常直观了。我们只要维护一下外键约束：

DELIMITER $$
DROP TRIGGER IF EXISTS tau_r2$$
CREATE TRIGGER tau_r2
AFTER UPDATE ON r2 FOR EACH ROW
BEGIN
UPDATE r2_equi_dist SET r2_id = NEW.id WHERE r2_id = OLD.id;
END$$
DELIMITER ;

UPDATE r2 SET id = 25 WHERE id = 4;

SELECT * FROM r2;
+—-+———————————-+
| id | name |
+—-+———————————-+
| 1 | 8b4cf277a3343cdefbe19aa4dabc40e1 |
| 3 | 4e1897cd6d326f8079108292376fa7d5 |
| 25 | 29a5e3ed838db497aa330878920ec01b |
+—-+———————————-+
SELECT * FROM r2_equi_dist;
+—-+——-+
| id | r2_id |
+—-+——-+
| 1 | 1 |
| 2 | 3 |
| 3 | 25 |
+—-+——-+
一次多行
如果你想一次取回多行，你可以：

执行多次查询
写一个存储过程执行查询并将结果存入一个临时表
进行一个UNION
存储过程
存储过程为你提供了通用语言中很有用的一些结构：

循环
控制结构
过程
…
这个任务中我们只需要一个LOOP:

DELIMITER $$
DROP PROCEDURE IF EXISTS get_rands$$
CREATE PROCEDURE get_rands(IN cnt INT)
BEGIN
DROP TEMPORARY TABLE IF EXISTS rands;
CREATE TEMPORARY TABLE rands ( rand_id INT );

loop_me: LOOP
IF cnt < 1 THEN
LEAVE loop_me;
END IF;

INSERT INTO rands
SELECT r1.id
FROM random AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;

SET cnt = cnt – 1;
END LOOP loop_me;
END$$
DELIMITER ;

CALL get_rands(4);
SELECT * FROM rands;
+———+
| rand_id |
+———+
| 133716 |
| 702643 |
| 112066 |
| 452400 |
+———+
下面的问题留给读者解决：

使用动态SQL并传入临时表的名字
在表上使用一个UNIQUE索引并捕获UNIQUE键冲突来消除结果集中可能的重复记录。
性能
现在让我们看看性能方面发生了什么变化。我们有三个不同的查询来解决这个问题。

Q1. ORDER BY RAND()
Q2. RAND() * MAX(ID)
Q3. RAND() * MAX(ID) + ORDER BY ID
Q1预期消耗N * log2(N)，Q2和Q3接近常数。

下标是评测的结果，针对N行的表（从一千行到一百万行），每个查询执行1000次。

100 1.000 10.000 100.000 1.000.000
Q1 0:00.718s 0:02.092s 0:18.684s 2:59.081s 58:20.000s
Q2 0:00.519s 0:00.607s 0:00.614s 0:00.628s 0:00.637s
Q3 0:00.570s 0:00.607s 0:00.614s 0:00.628s 0:00.637s如你所见，普通的ORDER BY RAND()从仅100行的表开始便落后于优化过的查询了。

关于这些查询更详细的分析可以在analyzing-complex-queries查阅.

本工具产生的配置文件仅作参考用途，对此带来的后果不符任何责任，请注意使用。

看这里

http://imysql.cn/my_cnf_generator