PHP 自 5.3.0 開始，引入 mysqlnd 這個 extension。
在 5.3.0 的 Full ChangeLog 中可以看到這行：
Added mysqlnd extension as replacement for libmysql for ext/mysql, mysqli and PDO_mysql. (Andrey, Johannes, Ulf)
依照個人的過往經驗，native driver 的效能都會比較好，而 mysqlnd 也不例外（有興趣的可以看看這篇文章）。
比較可惜的是，mysqlnd 在 Windows 平台的 PHP 是預設套件；在 *NIX 平台，使用前必須在編譯前就先做好設定（configure；參數可參考 PHP 官方的mysqlnd 安裝文件）。
剛才，很高興看到 FreeBSD 的 PHP5 ports 加入了 option，讓我們可以輕鬆搞定。 
發現 MongoDB 是好一陣子前的事，而我也在某台機器上把它裝起來。
之前玩的時候，發現 MongoDB 不須帳號與密碼就可以連接，而且也無法配置使用者權限，所以把它丟在旁邊。
前幾天，我在網路上看到網友詢問 MongoDB 有沒有辦法作 JOIN，得到的答案是否定的。
趁著今天在家閒閒沒事作，書也看完幾個段落，就透過 PHP 來試試這種 ODBMS 如何實作資料關聯。
MongoDB 與常見的資料庫（例如：MySQL）有些微的不同：
| 資料庫 | 資料表 | |
| MongoDB 稱之為 | Database（DB） | Collection |
| MySQL 稱之為 | Database（DB） | Table |
先把這個觀念講清楚，底下的範例程式碼才不會看得霧煞煞。
首先，先建立 DB 與 Collection：
// 連接 MongoDB $m = new Mongo(); // 連接資料庫，名稱就是 test $testDB = $m->selectDB( 'test' ); // 建立 Collection，名稱分別是 user 與 sex $testDB->createCollection( 'user' ); $testDB->createCollection( 'sex' );
再來，存入性別資料：
// 連接 MongoDB
$m = new Mongo();
// 連接資料庫，名稱就是 test
$testDB = $m->selectDB( 'test' );
// 在 sex 這個 Collection 裡面放入資料，好讓程式分辨男性與女性
$testDB->sex->insert( array('sex_name' => 'Female') );
$testDB->sex->insert( array('sex_name' => 'Male') );
// 把 sex 這個 Collection 的資料倒出來看
$cursor = $testDB->sex->find();
$array = iterator_to_array($cursor);
var_dump($array);
開始存入使用者名稱，並紀錄他（她）們的性別：
// 連接 MongoDB
$m = new Mongo();
// 連接資料庫，名稱就是 test
$testDB = $m->selectDB( 'test' );
// 找出男性資料，並取得關聯值
$male = $testDB->sex->findOne( array('sex_name' => 'Male') );
$refMale = $testDB->sex->createDBRef( $male );
// 找出女性資料，並取得關聯值
$female = $testDB->sex->findOne( array('sex_name' => 'Female') );
$refFemale = $testDB->sex->createDBRef( $female );
// 存入使用者資料
$testDB->user->insert( array('name' => 'BoyName', 'sex' => $refMale) );
$testDB->user->insert( array('name' => 'GirlName', 'sex' => $refFemale) );
// 取出並顯示使用者資料
$cursor = $testDB->user->find();
$array = iterator_to_array($cursor);
foreach ( $array as $user ) {
// 找出性別的關聯物件
$sexRef = $testDB->user->getDBRef($user['sex']);
echo "Name: {$user['name']}\tSex: {$sexRef['sex_name']}\n";
}
以上，簡單的試玩心得。 
最近在 MySQL 處理包含 weekday 型態的資料，發覺... 在 MySQL 處理 weekday 真的是「輕鬆寫意」...
根據 DAYOFWEEK() 與 WEEKDAY() 的定義，回傳的資料都是數值。
而大多數 DBMS 都有取得 weekday 的方法。
我會說「 MySQL 處理 weekday 輕鬆寫意」的原因是... ENUM ！
我把將欄位型態定義為 enum('Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat')。
今天（2010/04/01，愚人節）是星期四，若是要抓出該欄位為 Thu 的資料，只要用這種語法：
SELECT ... WHERE `col`= DAYOFWEEK( NOW() )
用這類方法要注意的是，DAYOFWEEK() 與 WEEKDAY() 對於每個 weekday，回傳的數值都不一樣。
(Visited 3409 times)我利用前兩天的空閒時間寫了個 PHP 的小程式，運用 PHP 的 PDO 元件，對 MySQL 進行小測試。
Server 作業系統與硬體：
MySQL 5.1.44 的設定（/etc/my.cnf）：
# defult setting (maybe changed) key_buffer_size = 128M max_allowed_packet = 4M table_open_cache = 4096 sort_buffer_size = 1M net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M # added for tunning by Joe Horn skip-name-resolve connect_timeout = 60 join_buffer_size = 1M max_connect_errors = 10000 max_connections = 100 max_heap_table_size = 1G query_cache_size = 16M slave_net_timeout = 30 sync_binlog=1 thread_cache_size = 512 thread_concurrency = 8 tmp_table_size = 1G table_definition_cache = 512 # choose one ( depend on query amount ) #concurrent_insert=2 low_priority_updates=1
這台機器跑 Super Smack 的結果：
# super-smack -d mysql update-select.smack 80 1000 Query Barrel Report for client smacker connect: max=14ms min=1ms avg= 4ms from 80 clients Query_type num_queries max_time min_time q_per_s select_index 80000 101 6 717.91 update_index 80000 108 1 717.91
我用測試程式產生了一百萬筆資料的 MyISAM table，各種測試各循環 1000 次，產生如下的測試結果（後面的數字單位是秒）：
SELECT BY PK_COL WITH_QUERY_CACHE: 0.058 SELECT BY PK_COL WITHOUT_QUERY_CACHE: 0.062 SELECT BY PK_COL LIMIT WITH_QUERY_CACHE: 0.058 SELECT BY PK_COL LIMIT WITHOUT_QUERY_CACHE: 0.062 SELECT BY UNIQUE_COL WITH_QUERY_CACHE: 0.058 SELECT BY UNIQUE_COL WITHOUT_QUERY_CACHE: 0.062 SELECT BY UNIQUE_COL LIMIT WITH_QUERY_CACHE: 0.058 SELECT BY UNIQUE_COL LIMIT WITHOUT_QUERY_CACHE: 0.062 SELECT BY INDEX_COL WITH_QUERY_CACHE: 0.058 SELECT BY INDEX_COL WITHOUT_QUERY_CACHE: 0.061 SELECT BY INDEX_COL LIMIT WITH_QUERY_CACHE: 0.058 SELECT BY INDEX_COL LIMIT WITHOUT_QUERY_CACHE: 0.063 SELECT BY COL WITH_QUERY_CACHE: 0.786 SELECT BY COL WITHOUT_QUERY_CACHE: 0.063 SELECT BY COL LIMIT WITH_QUERY_CACHE: 0.059 SELECT BY COL LIMIT WITHOUT_QUERY_CACHE: 0.062 UPDATE BY PK_COL WITH_QUERY_CACHE: 1.139 UPDATE BY PK_COL WITHOUT_QUERY_CACHE: 1.199 UPDATE BY PK_COL LIMIT WITH_QUERY_CACHE: 0.125 UPDATE BY PK_COL LIMIT WITHOUT_QUERY_CACHE: 0.142 UPDATE BY UNIQUE_COL WITH_QUERY_CACHE: 2.734 UPDATE BY UNIQUE_COL WITHOUT_QUERY_CACHE: 1.203 UPDATE BY UNIQUE_COL LIMIT WITH_QUERY_CACHE: 0.147 UPDATE BY UNIQUE_COL LIMIT WITHOUT_QUERY_CACHE: 0.163 UPDATE BY INDEX_COL WITH_QUERY_CACHE: 1.183 UPDATE BY INDEX_COL WITHOUT_QUERY_CACHE: 1.063 UPDATE BY INDEX_COL LIMIT WITH_QUERY_CACHE: 0.138 UPDATE BY INDEX_COL LIMIT WITHOUT_QUERY_CACHE: 0.154 UPDATE BY COL WITH_QUERY_CACHE: 4704.859 UPDATE BY COL WITHOUT_QUERY_CACHE: 4641.191 UPDATE BY COL LIMIT WITH_QUERY_CACHE: 0.156 UPDATE BY COL LIMIT WITHOUT_QUERY_CACHE: 0.167
根據測試結果，大概可以看到出以下幾個要點：
幾天前，跟 Solaris 叔叔 談到 MySQL 的 ENUM，回家後上網找了些資料，剛好找出了一些使用 MySQL 時，與效能有關的 tips。
稍微整理過後，放在這裡：
| EQ_REF | 一對一比對 |
| REF | 一對多比對 |
| RANGE | 特定範圍的資料會被傳回 |
| INDEX | 使用 INDEX 裡的資料 |
| ALL | 對整個 TABLE 作掃瞄（最差） |
SELECT `col_a` FROM `table_a` LEFT JOIN `table_b` ON `table_a`.`col_b` = `table_b`.`col_c` WHERE `col_d` = ....; -- SELECT 的欄位不需 INDEX，ex： `col_a`。 -- JOIN 的欄位需要 INDEX，ex： `table_a`.`col_b` 與 `table_b`.`col_c`。 -- WHERE 的欄位需要 INDEX，ex： `col_d`。
SELECT * ...
-- 下列這種語法無法利用 INDEX SELECT ... WHERE `col` LIKE '%string%'; -- 下列這種語法會利用 INDEX SELECT ... WHERE `col` LIKE 'string%';
-- 下列這種語法會使用 INDEX，速度快。 SELECT ... WHERE `col` < 100 / 10; -- 下列這種語法會導致 FULL TABLE SCAN，速度慢。 SELECT ... WHERE `col` * 10 < 100;
幾個小時前，PTT 的 Database 板 又有人問到 MySQL 的 "SELECT ... LIMIT m,n" 相關問題。
其實，在大多數的網頁系統中，"SELECT ... LIMIT m,n" 應該是一定要用的語法，尤其是在使用者驗證時。
簡單舉例，MySQL 的 "SELECT ..." 語法運作可說是拿著籃子去裝東西，若以 LIMIT 語法限制籃子裝東西的數量，MySQL 會在籃子裝滿後就停止動作，並傳回結果。
在網頁系統中，因為使用者帳號都是獨立的，所以，進行使用者登入驗證時，只需要抓出一筆資料來進行比對。
因此，在網頁系統中，我時常用這種語法來進行使用者登入的驗證：
SELECT `使用者身份` FROM `users` WHERE `username`='使用者名稱' AND `password`='雜湊後的密碼' LIMIT 1;
如果找不到資料，代表使用者的帳號/密碼錯誤。
而直接把密碼加入 WHERE 子句，不挑出來用程式比對則是為了杜絕 SQL Injection 導致的密碼外洩。
另外，這樣作也不錯：
SELECT `使用者身份` FROM `users` WHERE `username`='使用者名稱' AND `password`=PASSWORD( '雜湊前或雜湊後的密碼' ) LIMIT 1;
除了已知的效能差異（以前用 Ubench 測過的，gaod 長輩也貢獻了一些資訊，都 紀錄在這）、預設支援的記憶體容量之外，這兩天又找出另外一項。
某台機器跑 FreeBSD i386 platform，搭配 PAE 與 SMP。
根據以往的經驗，這種記憶體容量超過 4G 的機器，我會選 amd64 platform。
（至少經手過的幾台機器到現在都很健康...）
近來這台機器的 MySQL daemon process 時常會掛掉...
追蹤 error log 之後，發現這種錯誤訊息：
Out of memory (Needed 16391 bytes)
我把錯誤訊息丟上 Google 後，才發現 FreeBSD i386 platform 版本，其預設之單 process 記憶體容量限制為 512 MB，而 MySQL daemon process 使用超過 512 MB 後就會噴出上述的錯誤訊息，接著就是嗝屁... 
我沒碰過這種瓶頸，似乎是 amd64 platform 沒有這種限制。
i386 platform 遭遇到這種瓶頸的解法是在 /boot/loader.conf 裡面丟進類似這三行：
kern.maxdsiz="2G"
kern.dfldsiz="2G"
kern.maxssiz="256M"
（拉高單 process 的記憶體容量限制，變為 2G）
然後 reboot，準備驗收成果...
幾個月前，我幫人家建置了一套網頁系統，用以觀察網站瀏覽趨勢。
想當然爾，這種系統勢必會對資料庫產生大量 INSERT 或 UPDATE 的 query，而且會保留大量歷史性資料。
為了使系統能快速存取後端資料庫，我將存放 raw data 資料表的 storage engine 設定為 InnoDB。
（因為 InnoDB 支援了 row lock。 ）
並搭配定時執行的 script 整理資料，進行統計，並存放在 MyISAM 格式的資料表中。
最近，這套系統的後端資料庫伺服器，mysqld 三不五時會自行 restart，系統狀態也看似正常（根據他們的說法）。
追蹤後，發現 InnoDB 的運作狀態被忽略了...
光是其中一塊放置 raw data 的資料表就存放了三百多萬筆資料，佔用了 3xx MB 的空間，可是 innodb_buffer_pool_size 只有 256 MB，連 mysqldump 都無法完全撈出這塊資料表的內容...
追查程式後，發現原本用以刪除過期 raw data 的那些程式碼片段都被註解掉.. 
這段故事給我們幾個啟示：
我在 之前的文章 提過 MySQL 裡面，協助我們處理時間資料的 functions，現在來講一下該注意的事。
可能很多人還沒發現到 Year 2038 Problem 。
2007 年 12 月，我在開發某個網頁系統時碰上了這個問題。
在 PHP5 裡面， mktime(11, 14, 7, 1, 19, 2038) 傳回 2147483647，mktime(11, 14, 8, 1, 19, 2038) 卻丟出空白值，而 2147483647 正好是 C 語言中，signed long int 的最大值。
而且，MySQL 的 UNIX timestamp 也有這種問題，以下這串語法傳回來的數值是 0 。
SELECT UNIX_TIMESTAMP( NOW( ) + INTERVAL 50 YEAR );
目前，就我測試過的部份，PHP4 與 JAVA 都沒有這種問題。
可是，我們不知道 PHP4 開發出來的系統，會不會被拿到 PHP5 上面跑。
所以，在 MySQL 裡面使用 datetime 或 timestamp 來紀錄時間資料，並搭配處理時間資料的 functions 來處理時間型資料，不僅方便我們辨識時間，也可以省去不必要的麻煩。
有些人可能會覺得，使用 datetime 或 timestamp 來紀錄時間資料，取出來的資料型態是字串，如果只需要年、月、日，就得作字串分割。
其實，MySQL 的 DATE_FORMAT 就足以應付這種狀況了。
最後，用 MySQL 處理 datetime 或 timestamp 型態的資料，請多注意一件事：
SELECT '2008-01-31 00:00:00' + INTERVAL 1 MONTH;
其結果為 2008-02-29 00:00:00。
SELECT '2008-01-31 00:00:00' + INTERVAL 30 DAY;
其結果則是 2008-03-01 00:00:00。
以上，我的報告完畢，謝謝收看。
一、兩年以前，我會直接在 MySQL 中，以 varchar 型態，直接把 IP address 存入。
直到我在某次進行系統開發時，翻過 MySQL 的 Operator and Function Reference ，才發現這兩個好用的 functions ：
透過這兩個 functions ，我開始用 unsigned int 型態來存 IP address。
跟以往的 varchar 比較，使用 unsigned int ，搭配這兩個 functions 有著以下這兩項優勢：
尤其是第二項，以往使用 varchar 型態儲存 IP address 時，我必須先取出資料，再使用字串處理函式，甚至是正規表示式對字串進行切割。
而今，運用 INET_ATON() ，把原本的 IP address 轉成數字存成 unsigned int 之後，我只要把區段的頭、尾 IP address 用 INET_ATON() 轉換成數字，就可以利用大於、小於，甚至是 MySQL 的 between 來作比對或判斷了。
如果遇到的是以 CIDR 表示的區段，使用 PHP 開發程式或系統的人也可以選用 Pear 的 Net_IPv4 套件，呼叫 parseAddress() ，取得該區段的頭、尾 IP 等資訊，再運用 INET_ATON() ，一樣可以利用大於、小於、MySQL 的 between 來作比對或判斷。
(Visited 7388 times)
Recent Comments