安裝PostgreSQL on Mac OSX

之前Sam使用過資料庫的經驗有Oracle、MSSQL，當然最常依然是MySQL。基於php全球使用人數眾多，而通常與MySQL直接綁在一起變成書籍、課程等，也讓MySQL的聲名大噪。不過最近由於論文要研究一些國外的典藏系統(DSpace)，發現他們除了商用資料庫外，也有使用PostgreSQL(念法為post-gres-ql)。PostgreSQL首次發行日為1989，MySQL為1996，所以請不要因為沒聽過它的名號就認為它很新或不夠成熟...

HP Scrawlr 幫你偵測網站安全性

今天朋友傳了一個軟體給我玩, 主要是這隻程式會用盡辦法去入侵你輸入的網站, 也就是SQL Injection, 以香港AAStock來作測試, 發現一堆頁面都可以被攻擊, 也能掃出資料表內容...實在很可怕。

但是我是那種唯恐天下不亂的人, 所以搞不好能拿來幹些見不得人的事 :P

下載HP Scrawlr

php與MySQL連接類別

日前整理的一個類別, 特殊的地方在於使用了Array儲存查詢結果, 所以能夠依照查詢時給予的ID重新調用結果。
此外設定檔存放在db_mysql.inc中, 如果臨時需要調換資料庫則在建構子中填入即可。

db_mysql.inc :

<?php

// or set date.timezone = Asia/Taipei in php.ini
if(function_exists("date_default_timezone_set")) date_default_timezone_set('Asia/Taipei');

// database configure.
define(DB_HOST, 'localhost');
define(DB_USER, 'root');
define(DB_PASSWD, 'root1234');
define(DB_DATABASE, 'PHP_MODULE_TEST');
define(DB_PERSISTENT, false);
define(DB_UTF, true);

?>

database_mysql.php :

<?php

require('db_mysql.inc');

class DataBase 
{
    // Connection parameters
    var $host = '';
    var $user = '';
    var $password = '';
    var $database = '';
    var $persistent = false;
    var $utf = true;

    // Database connection handle
    var $conn = null;
    var $connected = false;
 
    // Query result
    var $result = array();
    var $insert_id = array();
    
    // constructor.
    function DataBase($otherDatabase = null)
    {
        $this->host = DB_HOST;
        $this->user = DB_USER;
        $this->password = DB_PASSWD;
        if($otherDatabase == null)
            $this->database = DB_DATABASE;
        else
            $this->database = $otherDatabase;
        $this->persistent = DB_PERSISTENT;
        $this->utf = DB_UTF;
    }

    // open database connection.
    function connect() 
    {
        // Choose the appropriate connect function
        if ($this->persistent){
            $func = 'mysql_pconnect';
        }else{
            $func = 'mysql_connect';
        }
     
        // Connect to the MySQL server
        $this->conn = $func($this->host, $this->user, $this->password) or die($this->error());
        if($this->utf == true){
            mysql_query("SET NAMES 'utf8'");
        }

        // Select the requested database
        mysql_select_db($this->database, $this->conn) or die($this->error());

        $this->connected = true;
    }

    // database query.
    function query($rs_id='RESULT_ID', $sql='', $debug=0)
    {
        if($this->connected == false) $this->connect();
        if($debug!=0) $this->debug($sql, $debug);
        $this->result[$rs_id] = mysql_query($sql, $this->conn) or die($this->error());
        $this->insert_id[$rs_id] = mysql_insert_id();
        return ($this->result[$rs_id] != false);
    }
    
    // 取得先前操作MySQL所受到影響的列的數目
    function affectedRows()
    {
        return (mysql_affected_rows($this->conn));
    }

    // 取得結果中列的數目
    function numRows($rs_id)
    {
        return (mysql_num_rows($this->result[$rs_id]));
    }
    
    // 取得查詢後的物件結果(使用$row->user_id查詢)
    function fetchObject($rs_id)
    {
        return (mysql_fetch_object($this->result[$rs_id]));
    }
    
    // 取得查詢後的陣列結果(使用$row['user_id']查詢)
    function fetchArray($rs_id)
    {
        return (mysql_fetch_array($this->result[$rs_id]));
    }
    
    // 回傳一個欄位的值(ex: select name from school where id=1, 傳回$rs['name'])
    function fetchAssoc($rs_id)
    {
        return (mysql_fetch_assoc($this->result[$rs_id]));
    }

    // 釋放�憶體
    function freeResult($rs_id)
    {
        return (mysql_free_result($this->result[$rs_id]));
    }

    // 移動內部指標
    function resetResult($rs_id)
    {
        return (mysql_data_seek($this->result[$rs_id], 0));
    }
    
    // 取得先前insert操作的id主鍵(AUTO_INCREMENTED)
    function getInsertID($rs_id)
    {
        return $this->insert_id[$rs_id];
    }

    // show database configure.
    function showDetail()
    {
        $str = '';
        $str .= 'DB_HOST: '.DB_HOST.'<br>';
        $str .= 'DB_USER: '.DB_USER.'<br>';
        $str .= 'DB_PASSWD: '.DB_PASSWD.'<br>';
        $str .= 'DB_DATABASE: '.DB_DATABASE.'<br>';
        if(DB_PERSISTENT == true)
            $str .= 'DB_PERSISTENT: true<br>';
        else
            $str .= 'DB_PERSISTENT: false<br>';
        if(DB_UTF == true)
            $str .= 'DB_UTF: true<p>';
        else
            $str .= 'DB_UTF: false<p>';

        return $str;
    }

    // debug sql condition.
    function debug($sql,$debug)
    {
        if($debug==1)echo "<script>alert(\"".$sql."\");</script>";
        else if($debug==2)echo '<p><b>'.$sql.'</b></p>';
        else if($debug==3)exit;
    }                                 

    // close connection. (無法關閉pconnect所開啟的連線)
    function close()
    {
        $this->connected = false;
        return mysql_close($this->conn);
    }

    function error()
    {
        return mysql_error();
    }
}
?>

MySQL的優化調效與應用層注意事項

一. 系統參數

1. key_buffer_size ->

係與索引塊的緩衝區大小息息相關，故將之設定為較大後，對於來自於所有用戶執行緒的讀寫處理能力，自然有所助益，但一旦設定的太大，反而會拖累系統的執行效能。

系統的默認值為8MB，可抓系統主機記憶體容量的1/5（註：假設伺服器具2GB記憶體，則以400MB為基準)。

2. back_log ->

該數值的主要意義，指MySQL暫時停止回應新請求之前的短暫時間內可允許多少個請求，被存放於堆疊之中。
期望在短暫時間內能夠連結到較多的請求數量，或在主機進程列表當中發現到大量的等待連接進程時，則可把back_log值予以增加。
但不能無限上綱地擴大，因為作業系統對於接收TCP/IP連接的listen queue大小有其上限。
系統所默認的back_log值為50，只要不把它調到10倍（亦即500）以上，通常不會有太大問題。

3. max_connections ->

若擔心系統所承受的同時上線使用人數過多，導致經常出現「Too many connections」錯誤的話，則可考量將max_connections數值從Default的100加以提高。

4. interactive_timeout ->

系統在面對1個交互連接行為上，所產生的等待時間，以秒為單位，而系統默認值為28800，此時不妨將之調低，改為14400或7200都可，旨在降低其於交互連接過程中的等待秒數。

5. sort_buffer ->

系統在面對所有需要被排序的執行緒，即可個別分配較大的緩衝區，從而提升ORDER BY或GROUP BY的行進速度。 系統默認值僅2MB，可考慮提高到8MB或16MB。

6. table_cache ->

代表所有執行緒在開啟表單時，所能支撐的檔描述符總量，而MySQL每開啟1個表，便需要搭配2個檔描述符。 系統默認值為64，可考慮調整為256或512。

7. record_buffer ->

倘若數值愈大，則每個刻正接受掃描的表單，便可分配到較大的緩衝區，從而加速順序掃描作業之運行。 而系統默認值是128KB，可將之大幅調高到8MB或16MB無妨。

二. 以JOIN取代Sub-Queries

不可諱言，透過子查詢方式，將可1次性地搞定許多邏輯上原需多步驟方能完成的SQL操作，且能一併脫離事務或表單的閉鎖效應，更難能可貴的是，撰寫的難度也不算高。
只不過，在多數情況下，用戶也不妨考慮採用效率更高的連接方式，藉以取代子查詢。系統管理員一旦運用連接後，將不難發現到，查詢速度將比以前快上許多。

三. 慎選欄位屬性

當使用者在建立表單時，基於追求高效能考量，一定得注意表單中欄位的寬度，必須儘可能把它設定得小一些。 有關整數型(INT)欄的定義，採用MEDIUMINT會比採用BIGINT來得好，而能夠以CHAR(6)定義的欄位，便犯不著將之設定為CHAR(255)甚或VARCHAR，因為後兩者都會佔據一些無謂的資料庫空間。

此外，當面對一些諸如「性別」、「縣市」等文本欄位元時，可以考慮將之定義為ENUM型態，此乃由於，在MySQL資料量的運作過程中，ENUM類型會被當做數值型資料來加以處理，而系統對於數值型資料的處理效率，絕對比處理文本類型資料的速度要來得快。

另一方面，使用者亦須培養1個觀念，亦即儘可能將欄位設定為NOT NULL，如此一來，資料庫系統在查詢作業的過程裡頭，便無須再就NULL值進行比較，執行速度也將之加快。

四. 考慮以UNION替代臨時表

自MySQL 4.0版系統開始，便支援聯合（UNION）查詢功能，亦即可將所有原本需要採用臨時表的2條或更多條的SELECT查詢，合併至單一查詢程式之中，而待用戶查詢會話結束後，臨時表將隨即被自動刪除，據此保持資料庫的整齊一致性，並有助效率的提升。

五. 採用事務語法

舉個簡單的例子，當用戶欲將1筆資料，同時插入2個相互關聯的表單時，萬一第1個表單中成功更新之後，資料庫系統出現突發性意外，導致第2個表單中的操作無法順利完成，則資料庫系統裡頭的資料，便將趨於不完整，甚至遭致破壞。
要想避免此類狀況產生，別無他法，只能採用事務語法，確保整個系列語句之中的每條語句，都能一齊「成功」或一齊「失敗」，如此一來，資料庫當中的資料，其一致性與完整性便無遭致破壞之虞。

事務語法係以BEGIN關鍵字來出發，並以COMMIT關鍵字作為結束，此行進的過程中，任1條SQL語句操作失敗的話，只消透過ROLLBACK命令，即可將資料庫回復至BEGIN開始之前的狀態。

BEGIN;
INSERT INTO ....
COMMIT;
六. 透過鎖定表　避免延遲衝擊

即便透過事務語法，堪稱維持資料庫完整性的理想途徑，然其畢竟具有「獨佔」之意涵，在有些情況下，特別是處於大型應用環境當中，便可能影響資料庫效能，
究其主因，在於事務語法執行的過程中，致使資料庫遭到鎖定，故對於其他用戶的請求，也僅能暫時等待到該事務結束才能進行。

倘若該資料庫系統僅少數使用者所採用，此一影響即不會成為大問題，但萬一有眾多使用者同時訪問資料庫的話，則延遲現象便將趨於嚴重。

為解決這個問題，系統管理員不妨採用鎖定表的方式，藉以獲致較佳效能，譬如下例，即是以此方式，來實現與前述（註：採用事務語法）的相同目的。

LOCK TABLE inventory WRITE
SELECT ....
UPDATE ....
UNLOCK TABLES

七. 藉由外鍵(FOREIGN KEY)做法　保持資料關聯性

一旦使用外鍵，便可確保customerinfo表中的 CustomerID，都能鏡射至salesinfo表中CustomerID，其間若有任1條缺乏合法CustomerID的記錄，絕對不會被插入或更新至salesinfo表單裡頭，如同下列步驟。

CREATE TABLE customerinfo
(
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID)
) TYPE=INNODB;

CREATE TABLE salesinfo
(
SalesID INT NOT NULL,
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID, SalesID),
FOREIGN KEY(CustomerID) REFERENCES customerinfo(CustomerID) ON DELETECASCADE
) TYPE=INNODB;

ON DELETE CASCADE參數的主要功能，係確保當customerinfo表單之中的任1條客戶記錄遭到刪除時，salesinfo表單裡頭所有與該客戶相關聯的記錄，也將一併被自動刪除。
欲於MySQL系統中採用外鍵，系統管理者務必得留意，在建立表單時，應將表單類型定義為InnoDB。

八. 針對適當欄位元建立索引

事實上，索引絕對可謂增進資料庫效能所慣用之方式，特別是在查詢語句當中，若含括了MAX(), MIN()或ORDERBY等命令時，效能提升的效果尤其顯著。
多數情況下，索引應被建立在一些將被套用於JOIN, WHERE判斷、以及ORDER BY排序的欄位上，而儘量不要出現在資料庫當中某個內含大量重複數值的欄位，
尤其對於ENUM型態的欄位而言，會出現大量重複值，在這般欄位元上建立索引，不僅不會產生任何助益，反倒可能降低資料庫效能。
對於大型資料庫而言，把資料裝載至缺乏FULLTEXT索引的表單中，接著透過ALTER TABLE或CREATE INDEX來建立索引，其實速度會非常快。

九. SQL語句優化

> 語句中沒有使用函數的速度會比有函數的快。

> like 語句使用就查詢方式而論，將會就表單當中的每1條記錄逐一比較，執行速度並不理想。

SELECT * FROM TEST_TABLE WHERE name LIKE "Database%"

但透過下列全不採用LIKE關鍵字或是通配符號的查詢方式，所得查詢結果亦無異於前例，但在執行速度方面，顯然就高明許多。

SELECT * FROM TEST_TABLE WHERE name>= "Database" and name < "Databasf"

> 只查詢你需要的欄位，而不是 SELECT *.

> 處理複雜的數據時，善用臨時資料表（temporary tables）.

> 越少聯合資料表查詢越好.

> 使用正確的數據類型, 不要把所有數據都加上引號, 數據庫會因為要把數據轉化為正確的類型而變慢.

> 循環查詢, 最常見到的問題。所謂循環查詢是指在迴圈中執行的查詢，這是非常消耗資源的做法，而且很多時候是絕對不需要的。

循環查詢的錯誤例子

if (isset($_GET['ids'])) {
foreach($_GET['ids'] as $id) {
$rs = mysql_query('SELECT * FROM my_table WHERE my_id = ' . (int) $id);
$row = mysql_fetch_assoc($rs);
print_r($row);
}
}

改正這個錯誤例子

if (isset($_GET['ids'])) {
$ids = array_map('intval', $_GET['ids']);
$ids = implode(',', $ids);
$rs = mysql_query('SELECT * FROM my_table WHERE my_id IN (' . $ids . ')');
while($row = mysql_fetch_assoc($rs)) {
print_r($row);
}
}

十. 資料庫設計

很多時候你會發現資料庫設計是問題的根源，一個差勁的資料模型可以使應用程式的效能和可維護性都深受影響，值得注意的是，一個越是為效能而優化的資料庫，可維護性也越低（這一點是有爭議的，它其實要視乎應用程式的大小和規模）。

建議大家閱讀「MySQL 的資料庫設計手冊」。http://dev.mysql.com/doc/refman/5.0/en/optimizing-database-structure.html

十一. 應用程式層面的 SQL 效能

應用程式層面的 SQL 效能與 SQL 查詢的效能屬於不同的課題，這是有關如何在設計上使 SQL 與應用程式互相配合讓工作做得更好

這方面的研究主要集中於如何降低查詢的次數，從而提高可擴展性和改善效能，不過，高的效能並不等於好的可擴展性，正如具有可擴展性並不等於有高的效能。