MySQL

mysql 持久连接 mysql_connect mysql_pconnect

0
1 year ago
by leakon in

在某些场合，mysql_pconnect( ) 是不适用的。

——————————————————————————–

状况一：

使用 1 部 web server 与 1 部 MySQL server（两者可能同在一部主机上），而 web server 固定只对 MySQL server 上的某一个数据库进行存取动作。

因为每次存取数据库时，都是由 web 那边使用同一账号对 MySQL 上的同一数据库作业，若我们将 MySQL 与 web server 的「同时联机数」都调整为 200，就好像 MySQL 这边一直有 200 位「服务生」，随时等着接待来自 web 的 200 位「顾客」似的。而且「顾客」离开之后，「服务生」也不下场休息，时时都站在门口等着接待下一个「顾客」。

在这种情况下，您只要注意将 MySQL 的「同时联机数」调得比 web server 的高或相等，就会发现使用 mysql_pconnect( ) 是个不错的选择。
——————————————————————————–

状况二：

使用 1 部 web server 与 1 部 MySQL server（两者可能同在一部主机上），而 web server 会对 MySQL server 上的两个数据库进行存取动作。

从 web server 那边提出数据存取需求时，有时是针对第 1 个数据库（DB1），有时则是针对第 2 个数据库（DB2）。若我们也将 MySQL 与 web server 的「同时联机数」都调整为 200，这样一来，就好像 MySQL 这边有 200 位「服务生」，但同时经营两个「吧台」（DB1 与 DB2），而「顾客」可能多达 200 位。

一开始，DB1 这个「吧台」比较热门，MySQL 派了 150 位「服务生」上场接待；同样地，当「顾客」离开之后，这 150 位「服务生」仍守着 DB1 而不下场休息。后来，DB2 那边也热闹起来了，「顾客」越来越多，MySQL 得加派「服务生」上场，有几个能派？答案是 50 个！

为什么「服务生」的人力调配会捉襟见肘？那是因为 web 那边使用了 mysql_pconnect( ) 来建立联机。「服务生」一开始被指定到哪个「吧台」工作，就会持续在那边停留，绝不「转台」。
——————————————————————————–

请注意，当使用持续性的联机时，每个已建立的联机只为来自同一部 web server、使用同一组账号，且存取同一数据库的使用者服务。

如此一来，假设每部 web server 的「同时联机数」都是 200，而且同时使用 2 部 web server 会怎么样呢？从 web1 来了 50 个「顾客」，先是到 DB1 走一趟，接着再到 DB2 晃一圈，这样需要多少「服务生」接待他们？100 个（web1->DB1: 50　web1->DB2: 50）！又从 web2 来了 50 个「顾客」，也做了同样的动作（web2->DB1: 50　web2->DB2: 50）。在此之后，还有「服务生」是闲着的吗？后续若从 web1 或 web2 同时涌入多于 50 位「顾客」时，谁来应付他们？

倘若您使用的是像 Apache 这类的 multi-process web server（一个 parent process 协调一组 children processes 运作），某个 children process 建立的「持续联机」，是不能分享给其它 children process 来使用的（「服务生」只对先前接待过的「顾客」服务）。在这样的情况下，将会使得 MySQL 上闲置的 process 越积越多（很多「服务生」站在门口等着「老顾客」上门，而不理会「新顾客」）。
mysql_pconnect( ) 一定是最佳选择吗？我想未必尽然。
——– 两者之间的区别 ————–
mysql_pconnect() 和 mysql_connect() 非常相似，但有两个主要区别。
首先，当连接的时候本函数将先尝试寻找一个在同一个主机上用同样的用户名和密码已经打开的（持久）连接，如果找到，则返回此连接

标识而不打开新连接。
其次，当脚本执行完毕后到 SQL 服务器的连接不会被关闭，此连接将保持打开以备以后使用（mysql_close() 不会关闭由

mysql_pconnect() 建立的连接）。
可选参数 client_flags 自 PHP 4.3.0 版起可用。
此种连接称为”持久的”。
看到这里，写一条代码来测试一下

/*
* pconnect_test.php
*/
$link = mysql_pconnect(“localhost“, “mysql_user“, “mysql_password“)
or die(“Could not connect: “ . mysql_error());
print (“Connected successfully“);

通过刷新网页的方式执行这条代码，发现每执行一次，mysql的进程数就增加一个。在这里我不禁有了疑问。上面说mysql_pconnect这个函

数的使用的时候，不是说”当连接的时候本函数将先尝试寻找一个在同一个主机上用同样的用户名和密码已经打开的（持久）连接，如果找到

，则返回此标识而不打开新连接”么？为什么我每刷新一次页面他就给我打开一个新的连接呢？

考虑到这有可能是PHP的bug，我到PHP的bug列表中找关于和too many connections 有关的条目。
相关的话题主要有三个，分别是

#11966        mysql_pconnect opens new connections with the same parameters
#26117        Persistent connection not reused
#13589        Persistent connections stay open and accumulate

描述比较长,我就不在这里贴,具体的内容你自己去看。重点主要是”当一个进程打开一个mysql的持续连接，只要该进程还存在，这个持续

的连接就不会断开，而且每一个进程会打开一个mysql的持续连接，而不能使用其他进程打开的持续连接”。

MySQL IN 条件语句 排序

0
1 year ago
by leakon in

有个场景，一个几万条记录的表，主键是 id，我想从表中取 id 为 30，20，80，40 的几条记录。

注意，30，20，80，40，是我预期的顺序，我希望 MySQL 按这样的顺序返回记录。

于是我这样写 SQL：

SELECT * FROM my_table WHERE id IN (30, 20, 80, 40);

结果是，他没有按我给的顺序返回。

怎么办？

查到了 FIELD() 函数。

FIELD(str,str1,str2,str3,…)
Returns the index (position) of str in the str1, str2, str3, … list. Returns 0 if str is not found.

把 SQL 语句改写为：

SELECT * FROM my_table WHERE id IN (30, 20, 80, 40) ORDER BY FIELD(id, 30, 20, 80, 40);

排序过程是：

把选出的记录的 id 在 FIELD 列表中进行查找，并返回位置，以位置作为排序依据。

这样的用法，会导致 Using filesort，是效率很低的排序方式。除非数据变化频率很低，或者有长时间的缓存，否则不建议用这样的方式排序。

把 MySQL 返回的结果，用 PHP 在内存中按 id 顺序重新排列，是个不错的优化方案。

MySQL 索引优化

2
1 year ago
by leakon in

表中包含 10 万条记录，有一个 datetime 类型的字段。

取数据的语句：

SELECT * FROM my_table WHERE created_at < '2010-01-20';

用 EXPLAIN 检查，发现 type 是 ALL， key 是 NULL，根本没用上索引。

可以确定的是，created_at 字段设定索引了。

什么原因呢？

用 SELECT COUNT(*) 看了一下符合 WHERE 条件的记录总数，居然是 6W 多条！！

难怪不用索引，这时用索引毫无意义，就好像 10 万条记录的用户表，有个性别字段，不是男就是女，在这种字段设置索引是错误的决定。

稍微改造一下上述语句：

SELECT * FROM my_table WHERE created_at BETWEEN '2009-12-06' AND '2010-01-20';

这回问题解决！

符合条件的记录只有几百条，EXPLAIN 的 type 是 range，key 是 created_at，Extra 是 Using where 。

自己总结个准则，索引的目的就是尽量缩小结果集，这样才能做到快速查询。

MySQL 多实例运行

0
1 year ago
by leakon in

今天要在服务器上运行多个 MySQL，用不同的端口，彼此隔离数据。

我最先想到的办法是下源码，编译多份，编译的时候设置不同的默认路径、端口和 my.cnf 配置文件位置。

被同事劝止……

人家告诉我不用装多份，只装一份二进制 rpm 版本，然后 copy 多份配置文件，再用不同的命令行参数启动即可。

/usr/sbin/mysqld --user=mysql --skip-external-locking \
	--port=3316 --defaults-file=/etc/my.s1.cnf \
	--basedir=/ --datadir=/var/lib/mysql_s1/ \
	--pid-file=/var/lib/mysql/s1.pid \
	--socket=/var/lib/s1.sock &

/usr/sbin/mysqld --user=mysql --skip-external-locking \
	--port=3326 --defaults-file=/etc/my.s2.cnf \
	--basedir=/ --datadir=/var/lib/mysql_s2/ \
	--pid-file=/var/lib/mysql/s2.pid \
	--socket=/var/lib/s2.sock &

就这么简单！

我曾经用原有的想法，编译过多份 MySQL！

笨死算了~~

顺便说下 –skip-external-locking 这个参数，由此也可引出另一种方式。

MySQL 支持 multi 方式运行多个实例，在 my.cnf 中，每个 [mysql#] 段代表一个实例，# 替换为正整数即可。

看手册的意思，好像是说多个实例可以共用相同的数据目录，还未证实。

但 –skip-external-locking 的说明是，当用 multi 方式启动多个 mysqld server ，多个 server 对同一个 table 进行读写时会锁表。如果你不会同时读写同一张表，那么可以加上这个参数启动 MySQL。

也许可以提高性能吧，回头有空了测试下~

Duplicate entry ‘localhost-’ for key 1

1
1 year ago
by leakon in ,

导致这个错误的原因：
因为安装系统时设置了hostname为localhost导致mysql在创建表的时候没有创建成功。因此mysql库中user表里没有root这个用户或者说root没有显示出来，无法查看root的状态。这时需要手动创建。然而在安全模式无法直接创建用户，错误见下。
变个方向就能创建了，先给root给予权限，然后刷新表。之后就可以创建了。
一、killall -TERM mysqld
以安全模式启动mysql
/usr/bin/mysqld_safe –skip-grant-tables &
进入mysql
/usr/bin/mysql
mysql> use mysql
Database changed
mysql> select * from user where user=’root’;
Empty set (0.00 sec)

mysql> grant all privileges on *.* to root@localhost identified by ‘linuxtone’ with GRANT OPTION;
ERROR 1290 (HY000): The MySQL server is running with the –skip-grant-tables option so it cannot execute

this statement
mysql> update user set password=password(‘linuxtone’) where user=’root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

注意单双引号。

mysql> flush privileges;   //注意先刷新下表
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to root@localhost identified by ‘linuxtone’ with GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where user=’root’;

后期处理：
/usr/local/mysql/bin/mysqladmin shutdown -uroot -p
刚才的密码
然后正常启动：/usr/bin/mysqld_safe &
/usr/local/mysql/bin/mysql -uroot -p
能正常登录！

二Duplicate entry ‘localhost-’ for key 1 问题，很可能是你的主机名有问题，比如是localhost？
导致mysql 的root 帐号的三个主机值（分别是%、localhost、主机名）的后二者的名字弄成一样了，导使唯一键值出现重复而禁用该记录了？
朋友的主机名设置成了：localhost 遭成了Duplicate entry ‘localhost-’ for key 1

设置好正确的主机名仍后重新编译就可以了，朋友安装系统的时候没有注意主机名

用工具提高效率 解决 PhpMyAdmin 乱码

0
1 year ago
by leakon in , ,

最近做的项目有点特殊，多人共用开发服务器，无法搭建个人独享的开发环境。

连数据库，不能使用客户端的 GUI 工具，本来我已经离不开 SQLyog 了，现在逼我用命令行，我怎么受得了？

欣慰的是，尽管 MySQL server 虽然不在开发服务器上，也没有 root 权限给我的 IP 做授权，但开发服务器的 IP 是在授权列表内的。

我又想起了 PhpMyAdmin 这个好久不用的工具。

在 Dev server 上装好，配好用户名和密码，然后即可访问 MySQL，不过又遇到新问题！

MySQL server、Database、Table 和 Column 的字符集都是 latin1，而写入数据库的字符是 GBK。虽然设计不够合理，但也算勉强能用，用 SecureCRT 的命令行连接 MySQL，是可以看到正确的中文字符的，因为 SecureCRT 设置的是默认的字符集。

但 PhpMyAdmin 却总是乱码！

查了下 Variables 标签，有 2 个关于字符集的高亮提示，意思是全局设置是 latin1，但 character set client 是 utf8。

任凭我怎么改 MySQL connection collation 的设置，都无效。

tail 了一下 MySQL query log，看到 PhpMyAdmin 总是发送

SET CHARACTER SET ‘utf8′
SET collation_connection = ‘utf8_general_ci’

这 2 条语句！

我又 grep 一下，看到有个文件中的代码，让这 2 条语句被强制执行！

用 解决 PhpMyAdmin 字符乱码问题 所述的方法修改，即刻解决问题！

这下又能高效的访问 MySQL 啦，我相信打字再快的人，输入一条 Select 语句也没有我点击一下鼠标快！

MySQL 多级同步 Master Slave

0
2 years ago
by leakon in

多级同步，备份，服务器排列情况：

A -> B -> C

在 A、B 之间，A 是 Master，B 是 Slave。

在 B、C 之间，B 是 Master，C 是Slave。

这里最重要的是 B，他既是 Slave，又是 Master。

应该在 my.cnf 中加入下述 2 行：

# binary logging – not required for slaves, but recommended

log-bin=mysql-bin

log-slave-updates

开启 mysql-bin，并在 Slave 更新的同时，也同步更新 mysql-bin。

只有这样，C 才能在 A 更新时，也同步得到更新。

SofavDB_Debug_PDO MySQL 执行时间 日志

0
3 years ago
by leakon in , , ,

这个类可以完全替代 PDO，如果你需要知道自己的程序跟 MySQL 交互花费多少时间，而你又有洁癖，不想把自己的代码搞得一团糟，那我强烈地建议你试试这个 SofavDB_Debug_PDO。

通常，创建一个 PDO 连接，都是 new PDO()。

在开发环境中，你只需要把上面那个语句替换成 new SofavDB_Debug_PDO() 即可。

然后，凡是通过 prepare 的 statement 执行的任何查询和修改的语句，执行时间都能记录下来。

最后，通过 SofavDB_Debug_PDO::getTimer() 就可以得到一个数组，极其方便……

当然，这个类的执行效率会有一些小小的下降，不适合用于生产环境。

源码请见 GoogleCode：

http://leakon.googlecode.com/svn/trunk/leakon/php/sofav_db/SofavDB_Debug_PDO.php

MySQL 索引 优化 Using filesort

2
3 years ago
by leakon in , ,

MySQL 数据库，MyISAM 类型的表 table_item，有 5、6 个字段，主键是 id。

user_id 和 item_id 两个字段都是单独的 INDEX 类型的索引。

问题是如何发现的？

今天搞程序，在自己的开发环境下，打开一个简单的页面都要好长时间，不知道问题出在哪里，只发现硬盘灯闪个不停。

观察 Windows 的任务管理器，看到 mysqld-nt.exe 这个进程的 “I/O 读取字节” 高达十几 G！！！

再次测试，发现每次刷新页面，这个进程要读取几十 M 的数据。

奇了怪了，查看 SQL 语句，还有表结构，字段都建了索引了呀。

后来 EXPLAIN 了一下，看到结果是 Using where; Using filesort。

explain SELECT * FROM table_item WHERE user_id = 2 ORDER BY item_id LIMIT 0, 5

翻了 MySQL 手册，仔细看下 filesort 的说明，知道了 Using filesort 是一种速度很慢的外部排序。

不过我不理解为什么会使用 filesort 排序，WHERE 和 ORDER BY 用到的字段都是有索引的呀。

赶紧 Google，找到几篇解释 Using filesort 的文章，得到的启示就是索引定义不当，MySQL 没有用到索引。

记得以前 Chenbin 给俺们培训过 MySQL 的优化，我还参加了两次，总感觉自己了解了如何优化 MySQL，没想到这么简单的语句我都没能优化。

现在想起来一些 MySQL 的特性了。

  1. 一条 SQL 语句只能使用 1 个索引 (5.0-)，MySQL 根据表的状态，选择一个它认为最好的索引用于优化查询
  2. 联合索引，只能按从左到右的顺序依次使用

这 2 点刚好可以解决我的问题。

user_id 和 item_id 是 2 个索引，我的语句中，MySQL 选择了 user_id，那么 item_id 的索引没有起到任何用处，所以，当我要排序的时候，由于记录数较多，内存中的排序 buffer 满了，只能 Using filesort 进行外部排序，因此每次查询要从磁盘读取几十 M 的数据，太慢了。

修改表结构，删除 user_id 和 item_id 的 INDEX 索引，建立一个名为 user_item 的联合 UNIQUE 索引，顺序是先 user_id 后 item_id，再 EXPLAIN，这回只有 Using where 了。

再刷新页面，观察任务管理器，mysqld-nt.exe 只读取了 2K 的数据，页面咔的一下就出来了……

PHP MySQL localhost 127.0.0.1

0
3 years ago
by leakon in , ,

今天刚发现一个问题，PHP连接MySQL的时候，不同环境的localhost会有不同的结果。

我的服务器装了2套PHP，其中有一个是用源码编译的，另一个是xampp集成包。

编译的php用于生产环境，xampp用来建立测试环境。

把线上的代码放到测试环境下，居然报告无法连接MySQL！

刚开始以为是端口的问题，可是我在命令行下怎么连接都没问题。最后抱着试试看的想法，把localhost改成了127.0.0.1，这回居然成功了。

我不能理解，线上代码运行的好好的，配置文件就是localhost呀，怎么到测试环境就必须改成127.0.0.1才行？

两套环境，主要的区别就是PHP，虽然版本都一样，不过xampp是编译好的，我估计问题出在这里。

然后立刻写了一个测试程序：

$connA = mysql_connect(’127.0.0.1:3306′, ‘leakon’, ‘pass’);
$connB = mysql_connect(‘localhost:3306′, ‘leakon’, ‘pass’);
var_dump($connA);
var_dump($connB); 

在命令行下分别用编译和xampp的php执行上述代码，果然发现两项结果不一样。

后来分析了一下，按照这种方式理解：编译PHP的时候需要指定MySQL的安装路径，这个时候localhost就指向对应的MySQL。与编译版的PHP不一样，xampp指向的是随包附带的二进制版MySQL，因此他发现这个MySQL的root密码不对，拒绝连接。

但用127.0.0.1作为主机地址时，PHP就不会按照编译的localhost找MySQL服务器，而是根据端口来找，这回就没问题了。

同时也发现了一个问题，当用localhost:port作为主机地址时，PHP会忽略端口号！

不信你试试上面的代码，那个port写成什么都无所谓，只要是localhost，就会链接特定的MySQL。

不知道为什么，就当经验，记住这个事实吧！

Go to Top