Saturn's Weblog

在数据库驱动的WEB应用中出现中文乱码，绝大多少都是由于数据库和程序字符编码不一致造成的。

在很多PHP应用中，我们使用开源ORM框架Doctrine来给应用程序添加一个数据抽象层（Data Abstract Layer），以此完善和丰富分层的程序架构。在Codeigniter中，我们可以以插件（Plugins）的形式将Doctrine完美的嵌入到CI应用中，PHPandStuff上有一个系列教程专门教大家解决这个问题，这里我就不再赘述。

需要注意的是，如果直接使用PHPandStuff教程提供的Plugin配置源代码开发中文网站，可能会出现中文乱码问题。这是由于它没有指定数据库字符集。为了解决这个问题，我们需要做个小的修改。

打开application/plugins/doctrine_pi.php，找到链接数据库的地方，在其之后添加如下两行：

// 因为使用的是Doctorine ORM，所以config/database.php中设置的DbCollate和Charset无效
// 需要在连接数据库成功之后，手动设置
// by Saturn
Doctrine_Manager::connection()->setCharset('utf8');
Doctrine_Manager::connection()->setCollate('utf8_general_ci');

完整的doctrine_pi.php文件代码如下（部分代码由PHPandStuff原作者贡献）：

// ------------------------------------------------
// system/application/plugins/doctrine_pi.php
// load Doctrine library
require_once APPPATH.'/plugins/doctrine/lib/Doctrine.php';

// load database configuration from CodeIgniter
require_once APPPATH.'/config/database.php';

// this will allow Doctrine to load Model classes automatically
spl_autoload_register(array('Doctrine', 'autoload'));

// we load our database connections into Doctrine_Manager
// this loop allows us to use multiple connections later on
foreach ($db as $connection_name => $db_values) {

 // first we must convert to dsn format
 $dsn = $db[$connection_name]['dbdriver'] .
  '://' . $db[$connection_name]['username'] .
  ':' . $db[$connection_name]['password'].
  '@' . $db[$connection_name]['hostname'] .
  '/' . $db[$connection_name]['database'];

 Doctrine_Manager::connection($dsn,$connection_name);
}


// 因为使用的是Doctorine ORM，所以config/database.php中设置的DbCollate和Charset无效
// 需要在连接数据库成功之后，手动设置
// by Saturn
Doctrine_Manager::connection()->setCharset('utf8');
Doctrine_Manager::connection()->setCollate('utf8_general_ci');

// CodeIgniter's Model class needs to be loaded
require_once BASEPATH.'/libraries/Model.php';

// telling Doctrine where our models are located
Doctrine::loadModels(APPPATH.'/models');
// (OPTIONAL) CONFIGURATION BELOW

// this will allow us to use "mutators"
Doctrine_Manager::getInstance()->setAttribute(
 Doctrine::ATTR_AUTO_ACCESSOR_OVERRIDE, true);

// this sets all table columns to notnull and unsigned (for ints) by default
Doctrine_Manager::getInstance()->setAttribute(
 Doctrine::ATTR_DEFAULT_COLUMN_OPTIONS,
 array('notnull' => true, 'unsigned' => true));

// set the default primary key to be named 'id', integer, 4 bytes
Doctrine_Manager::getInstance()->setAttribute(
 Doctrine::ATTR_DEFAULT_IDENTIFIER_OPTIONS,
 array('name' => 'id', 'type' => 'integer', 'length' => 4));
 
/* End of file doctrine_pi.php */
/* Location: ./system/application/plugins/doctrine_pi.php */

在MySQL中，与字符编码相关的参数有：字符集（Character Set）和整理（collation ）两个选项，对于每个数据库，这两个选项都可以根据需要分别制定。需要注意的是，Character Set和collation 是存在联系的。具体的定义，联系和区别，我们可以参考stackoverflow上的解释，我觉得非常经典，简单来说：

字符集（Character Set）就是一系列符号和编码的组合；而整理（collation）则用来比较具体字符集的标准。

在Mysql中，如果我们不指定字符集和整理两个选项，程序会使用系统默认的字符集。而Mysql的默认字符集是lantin1，默认整理参数是latin1_swedish_ci。对于英文语系来说，这两个参数不会对数据的存储造成任何影响。但是对于其他字符集，特别是我们需要存储东亚字符（比如中文）时，我们最好手动在数据库和程序中分别指定相一致的字符编码，否则就会出现乱码的情况。

上例中所添加的两行代码就是在程序中指定字符集和整理为utf-8；为了彻底避免和解决乱码问题，你也应该确认数据库的字符集和编码与上面的设定相一致，也就是utf-8。

Update: mysql中允许你在3个不同级别对字符集进行指定，分别是：数据库（database）级，表（table）级和栏位（field）级。请确认你数据库中这三个级别的字符集相一致。

谨以此文献给那些希望优化MySQL数据库查询性能，却又不知道如何着手的同仁们。本文将持续更新。

本文的第一个版本发布在CI中国社区，主要是发现很多同学虽然知道索引能够提升MySQL数据库的读取性能，但却存在很多操作上的误区。所以，我特此写了这篇文章，希望能够对大家有些启示。

下面的内容很大程度上是我在平时项目中积累所产生的认识，既然是一家之言，难免会有错误。如果对其中某一条规则有异议，欢迎和我交流。

在开始之前，我想表明一个观点，很多同学在编写WEB程序时，往往非常重视程序的结构和效率，这固然是一个很好的习惯。但他们却忽视了对数据库的结构和优化。在我看来，对于目前很多网站来说，特别是那些对数据库依赖非常严重的泛WEB 2.0网站，数据库调优比程序本身的效率更重要。

本文的主题是：在MySQL中，如何通过合理的建立索引，大幅提升包含ORDER BY语句的效率。在这里，我主要以讲述操作为主，至于为什么这么做，各位同学就要去查阅包括MySQL手册在内的其他资料了。

»阅读全文

本文讨论的是如何从MySQL一个数据表中提取一条随机的效率，同时要保证效率最高。

方法一

这是最原始最直观的语法，如下：

SELECT * FROM foo ORDER BY RAND() LIMIT 1

当数据表中数据量较小时，此方法可行。但当数据量到达一定程度，比如100万数据或以上，就有很大的性能问题。如果你通过EXPLAIN来分析这个语句，会发现虽然MySQL通过建立一张临时表来排序，但由于ORDER BY和LIMIT本身的特性，在排序未完成之前，我们还是无法通过LIMIT来获取需要的记录。亦即，你的记录有多少条，就必须首先对这些数据进行排序。

方法二

看来对于大数据量的随机数据抽取，性能的症结出在ORDER BY上，那么如何避免？方法二提供了一个方案。

首先，获取数据表的所有记录数：

SELECT count(*) AS num_rows FROM foo

然后，通过对应的后台程序记录下此记录总数（假定为num_rows）。

然后执行：

SELECT * FROM foo LIMIT [0到num_rows之间的一个随机数],1

上面这个随机数的获得可以通过后台程序来完成。此方法的前提是表的ID是连续的或者自增长的。

这个方法已经成功避免了ORDER BY的产生。

方法三

有没有可能不用ORDER BY，用一个SQL语句实现方法二？可以，那就是用JOIN。

SELECT * FROM Bar B JOIN (SELECT CEIL(MAX(ID)*RAND()) AS ID FROM Bar) AS m ON B.ID >= m.ID LIMIT 1;

此方法实现了我们的目的，同时，在数据量大的情况下，也避免了ORDER BY所造成的所有记录的排序过程，因为通过JOIN里面的SELECT语句实际上只执行了一次，而不是N次（N等于方法二中的num_rows）。而且，我们可以在筛选语句上加上“大于”符号，还可以避免因为ID好不连续所产生的记录为空的现象。

还有更好的方案吗？有待总结。