資料庫設計原則

以前跟同事一起訂下的資料庫設計原則，來減少一開始設計不好造成事後要進行大改的風險。

實體關聯的設計是最需要謹慎的，這裡一旦與規格差太多，程式可能就要重寫。

欄位名稱建議在系統中是唯一的，這樣可以減少 JOIN 時還需要換名稱，FK 使用跟 PK 一樣的名稱也可以增加維護性。

---

資料庫設計流程

(Instance 層級)

• 依照 Instance 建立 ER 圖，須清楚描述關聯與實體
• 審視並釐清實體對於規格的含蓋範圍

(Table 層級)

• 依照釐清後的含蓋範圍，修正 ER 圖
• 審視並確認完整 ER圖

(Column 層級)

• 定義各資料表欄位，建立 OrgTableSchema.sql
• 審視並確認完整 Schema
• 有問題重複上述步驟

(Develop 層級)

• 維護 OrgTableSchema.sql
• 使用 DbSchemaTool 工具產生 Schema.sql
• 建立資料庫
• 修正 Dbml
• 更新 DB 專案

資料表與欄位定義

• Id 是 identity 去尾的縮寫，所以 d 要小寫
• No 改成 Num 會比較好，因為會跟 Yes, No 混淆
• Id 是流水號，其他的建議用 Num 或 Code 表示
• 避免用單單字做欄位名，盡量用多單字 ( Ex. IssueType, CompanyCode, SettingId, CompanyName )
• 盡量用 流水號 或 Guid 這類無意義的 id 做 PK
• 除了多對多的中間表，PK 都必須是單一欄位
• 資料來源為 OptionSetting, 值為 OptionId 者，欄位名稱命名須加尾贅詞 Id，型態為 INT
• 資料來源為 Enum, 欄位型態則為 Nvarchar(N)
• 盡量避免資料來源為 bool 對應 bit，因為擴充性太低，資料來源改用 Enum 對應 Nvarchar(N)
• 歷史檔與主檔的差異
• 主檔的 ModifiedBy ModifierdDate 為歷史檔的 CreatedBy CreatedDate，歷史檔無 ModifiedBy ModifierdDate
• 新增歷史檔的 PK , 主檔的 PK 設定為 FK
• 其餘欄位應該與主檔相同
• 每一個 FK 需預設建立 IX，其餘調整於開發完成後，依照使用者回報進行調整
• 多對多的中間表如果超過 3 個附加欄位，必須用一般的方式設計

PK 必須使用 Guid 的情況

• 如果只保留短期資料，但會有大量新增或刪除(每天一萬筆新增)
• 如果有多系統都可以新增資料，最後要合流的狀況

設計 Table 的欄位順序

1. PK
2. FK [主檔]
3. FK [選項]
4. AK
5. 其餘不重要的資料欄位
6. CreatedBy
7. CreatedDate
8. ModifiedBy
9. ModifierdDate

資料型態

• 文字 : NVARCHAR 長度為預定輸入的兩倍
• 日期 : DATETIMEOFFSET 具有時區紀錄(MSDN 建議)
• 時間 : TIME
• 整數 : INT
• Enum : NVARCHAR (32)
• Guid : UNIQUEIDENTIFIER
• 金錢 : MONEY
• 精確浮點數 : DECIMAL (18, 4)

鍵值規則

• Table 規則 {ProjectName}_{TableName}
   Ex: WMS_Carrier
• Foreign Key 規則 FK_{ProjectName}_{TableName}_{Columns}
   Ex: FK_WMS_CarrierMaterial_CarrierId
• Unique 規則 AK_{ProjectName}_{TableName}_{Columns}
   Ex: FK_WMS_CarrierMaterial_CarrierId
• Index 規則 IX_{ProjectName}_{TableName}_{Columns}
   Ex: IX_WMS_CarrierMaterial_CarrierId

縮寫解釋

• PK: Primary Key
• FK: Foreign Key
• AK: Alternate Key
• IX: IndeX
• CK: ChecK
• DF: DeFault

MSSQL 定序設定

定序 Chinese_Taiwan_Stroke_CS_AI

_CS 區分大小寫
_CI 不區分大小寫

_AS 區分腔調 a != á
_AI 不區分腔調

_KS 區分日文假名字元
_WS 區分全形與半形字元

定序會影響查詢與唯一值的判定，例如不區分大小的定序在 WHERE 'abc' = 'ABC' 會是 true。

定序選擇並沒有標準答案，我個人是採用區分大小的定序，如果規格是不區分的時候，再用程式轉大寫或小寫，這部分可以在 DAO 統一完成，雖然也會有遺漏的情況，但至少是可以由程式掌控，如果出現只有某些不區分，而大部分還是區分的時候，還是由程式掌控會比較好。

[轉載] Get Tables And Columns Details In Schema Using JDBC

轉載自：JavaRoots: Get Tables And Columns Details In Schema Using JDBC

This is a sample program written using java and JDBC API , to print all the tables in a database schema . It also prints columns available in the particular table.

package com.datamigration.main;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.datamigration.db.DataBase;

/**
 * @author Abhishek Somani
 */
public class PrintTable {

    public static String SCHEMA_NAME = "${YOUR_SCHEMA_NAME}";

    public static void main(String[] args) {

        // create and setup your database and get db connection
        DataBase db = new DataBase();
        db.init();

        try {
            Connection con = db.getConnection();
            DatabaseMetaData metaData = con.getMetaData();

            String tableType[] = { "TABLE" };

            StringBuilder builder = new StringBuilder();

            ResultSet result = metaData.getTables(
                null, SCHEMA_NAME, null, tableType
            );

            while (result.next()) {
                String tableName = result.getString(3);

                builder.append(tableName + "( ");
                ResultSet columns = metaData.getColumns(
                    null, null, tableName, null
                );

                while (columns.next()) {
                    String columnName = columns.getString(4);
                    builder.append(columnName);
                    builder.append(",");
                }
                builder.deleteCharAt(builder.lastIndexOf(","));
                builder.append(" )");
                builder.append("\n");
                builder.append("----------------");
                builder.append("\n");

            }

            System.out.println(builder.toString());

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

This program will print tables along with colmuns like this :

TABLE1( ID,NAME,STATUS,CREATED_AT,UPDATED_AT)
-----------------------------------
TABLE2( ID,NAME,STATUS,CREATED_AT,UPDATED_AT)

[轉載] 調校 SQL 以徹底改善應用程式效能

轉載自：調校 SQL 以徹底改善應用程式效能

有些程式員在撰寫前端的應用程式時，會透過各種 OOP 語言將存取資料庫的 SQL 陳述式串接起來，卻忽略了 SQL 語法的效能問題。版工曾聽過某半導體大廠的新進程式員，所兜出來的一段 PL/SQL 跑了好幾分鐘還跑不完；想當然爾，即使他前端的 AJAX 用得再漂亮，程式效能頂多也只是差強人意而已。以下是版工整理出的一些簡單心得，讓長年鑽究 ASP.NET / JSP / AJAX 等前端應用程式，卻無暇研究 SQL 語法的程式員，避免踩到一些 SQL 的效能地雷。


1、資料庫設計與規劃

• Primary Key 欄位的長度儘量小，能用 small integer 就不要用 integer。例如員工資料表，若能用員工編號當主鍵，就不要用身分證字號。
• 一般欄位亦同。若該資料表要存放的資料不會超過 3 萬筆，用 small integer 即可，不必用 integer。
• 文字資料欄位若長度固定，如：身分證字號，就不要用 varchar 或 nvarchar，應該用 char 或 nchar。
• 文字資料欄位若長度不固定，如：地址，則應該用 varchar 或 nvarchar。除了可節省儲存空間外，存取磁碟時也會較有效率。
• 設計欄位時，若其值可有可無，最好也給一個預設值，並設成「不允許 NULL」(一般欄位預設為「允許 NULL」)。因為 SQL Server 在存放和查詢有 NULL 的資料表時，會花費額外的運算動作 [2]。
• 若一個資料表的欄位過多，應垂直切割成兩個以上的資料表，並用同名的 Primary Key 一對多連結起來，如：Northwind 的 Orders、Order Details 資料表。以避免在存取資料時，以叢集索引掃描時會載入過多的資料，或修改資料時造成互相鎖定或鎖定過久。

---

2、適當地建立索引

• 記得自行幫 Foreign Key 欄位建立索引，即使是很少被 JOIN 的資料表亦然。
• 替常被查詢或排序的欄位建立索引，如：常被當作 WHERE 子句條件的欄位。
• 用來建立索引的欄位，長度不宜過長，不要用超過 20 個位元組的欄位，如：地址。
• 不要替內容重複性高的欄位建立索引，如：性別；反之，若重複性低的欄位則適合建立索引，如：姓名。
• 不要替使用率低的欄位建立索引。
• 不宜替過多欄位建立索引，否則反而會影響到新增、修改、刪除的效能，尤其是以線上交易 (OLTP) 為主的網站資料庫。
• 若資料表存放的資料很少，就不必刻意建立索引。否則可能資料庫沿著索引樹狀結構去搜尋索引中的資料，反而比掃描整個資料表還慢。
• 若查詢時符合條件的資料很多，則透過「非叢集索引」搜尋的效能，可能反而不如整個資料表逐筆掃描。
• 建立「叢集索引」的欄位選擇至為重要，會影響到整個索引結構的效能。要用來建立「叢集索引」的欄位，務必選擇「整數」型別 (鍵值會較小)、唯一、不可為 NULL。

---

3、適當地使用索引

• 有些書籍會提到，使用 LIKE、% 做模糊查詢時，即使您已替某個欄位建立索引 (如下方例子的 CustomerID)，但以常數字元開頭才會使用到索引，若以萬用字元 (%) 開頭則不會使用索引，如下所示：
  

  USE Northwind;
  GO
  SELECT * FROM Orders WHERE CustomerID LIKE 'D%'; --使用索引
  SELECT * FROM Orders WHERE CustomerID LIKE '%D'; --不使用索引
  

  
  執行完成後按 Ctrl+L，可檢閱如下圖的「執行計畫」。
  
  
  圖 1　可看出「查詢最佳化程式」有使用到索引做搜尋
  
  
  圖 2　在此的叢集索引掃描，並未直接使用索引，效能上幾乎只等於掃描整個資料表
  
  但經版工反覆測試，這種語法是否會使用到索引，抑或會逐筆掃描，並非絕對的。仍要看所下的查詢關鍵字，以及欄位內儲存的資料內容而定。但對於儲存資料筆數龐大的資料表，最好還是少用 LIKE 做模糊查詢。
  
  
• 以下的運算子會造成「負向查詢」，常會讓「查詢最佳化程式」無法有效地使用索引，最好能用其他運算子和語法改寫 (經版工測試，並非有負向運算子，就絕對無法使用索引)：
  NOT 、 != 、 <> 、 !> 、 !< 、 NOT EXISTS 、 NOT IN 、 NOT LIKE
• 避免讓 WHERE 子句中的欄位，去做字串串接或數字運算，否則可能導致「查詢最佳化程式」無法直接使用索引，而改採叢集索引掃描 (經版工測試並非絕對)。
• 資料表中的資料，會依照「叢集索引」欄位的順序存放，因此當您下 BETWEEN、GROUP BY、ORDER BY 時若有包含「叢集索引」欄位，由於資料已在資料表中排序好，因此可提升查詢速度。
• 若使用「複合索引」，要注意索引順序上的第一個欄位，才適合當作過濾條件。

---

4、避免在 WHERE 子句中對欄位使用函數

對欄位使用函數，也等於對欄位做運算或串接的動作，一樣可能會讓「查詢最佳化程式」無法有效地使用索引。但真正對效能影響最重大的，是當您的資料表內若有 10 萬筆資料，則在查詢時就需要呼叫函數 10 萬次，這點才是真正的效能殺手。程式員應注意，在系統開發初期可能感覺不出差異，但當系統上線且資料持續累積後，這些語法細節所造成的效能問題就會逐步浮現。

SELECT * FROM Orders WHERE DATEPART(yyyy, OrderDate) = 1996 AND DATEPART(mm, OrderDate)=7
-- 可改成
SELECT * FROM Orders WHERE OrderDate BETWEEN '19960701' AND '19960731'

SELECT * FROM Orders WHERE SUBSTRING(CustomerID, 1, 1) = 'D'
-- 可改成
SELECT * FROM Orders WHERE CustomerID LIKE 'D%'

注意當您在下 UPDATE、DELETE 陳述式時，若有採用 WHERE 子句，也應符合上述原則。

---

5、AND 與 OR 的使用

在 AND 運算中，「只要有一個」條件有用到索引 (如下方的 CustomerID)，即可大幅提升查詢速度，如下圖 3 所示：

SELECT * FROM Orders WHERE CustomerID='VINET' AND Freight=32.3800 
--使用索引，會出現下圖 3 的畫面

SELECT * FROM Orders WHERE Freight=32.3800 
--不使用索引，會出現上圖 2 的畫面

圖 3

但在 OR 運算中，則要「所有的」條件都有可用的索引，才能使用索引來提升查詢速度。因此 OR 運算子的使用必須特別小心。

若您將上方 AND 的範例，邏輯運算子改成 OR 的話，如下所示：

SELECT * FROM Orders WHERE CustomerID='VINET' OR Freight=32.3800

由於無法有效地使用索引，也會出現圖 2 的畫面。

在使用 OR 運算子時，只要有一個條件 (欄位) 沒有可用的索引，則其他所有的條件 (欄位) 都有索引也沒用，只能如圖 2 般，把整個資料表或整個叢集索引都掃描過，以逐筆比對是否有符合條件的資料。


據網路上文件的說法 [1]，上述的 OR 運算陳述式，我們還可用 UNION 聯集適當地改善，如下：

SELECT * FROM Orders WHERE CustomerID='VINET'
UNION
SELECT * FROM Orders WHERE Freight=32.3800

此時您再按 Ctrl+L 檢閱「執行計畫」，會發現上半段的查詢會使用索引，但下半段仍用叢集索引掃描，對效能不無小補。

---

6、適當地使用子查詢

相較於「子查詢 (Subquery)」，若能用 JOIN 完成的查詢，一般會比較建議使用後者。原因除了 JOIN 的語法較容易理解外，在多數的情況下，JOIN 的效能也會比子查詢較佳；但這並非絕對，也有的情況可能剛好相反。

我們知道子查詢可分為「獨立子查詢」和「關聯子查詢」兩種，前者指子查詢的內容可單獨執行，後者則無法單獨執行，亦即外層查詢的「每一次」查詢動作都需要引用內層查詢的資料，或內層查詢的「每一次」查詢動作都需要參考外層查詢的資料。

以下我們看一個比較極端的例子 [2]。若我們希望所有查詢出來的資料，都能另外給一個自動編號，版工我在之前的文章「用 SQL Server 2005 新增的 ROW_NUMBER 函數撰寫 GridView 分頁」中有介紹過，可用 SQL Server 2005 中新增的 ROW_NUMBER 函數輕易地達成，且 ROW_NUMBER 函數還能再加上「分群 (PARTITION BY)」等功能，而且執行效能極佳。


圖 4　將 Orders 資料表的 830 筆資料都撈出來，並在右側給一組自動編號

現在我們要如上圖 4 般，將 Northwind 中 Orders 資料表的 830 筆資料都撈出來，並自動給一組編號，若用 ROW_NUMBER 函數的寫法如下所示，而且效能極佳，只要 2 ms (毫秒)，亦即千分之二秒。

SET STATISTICS TIME ON
SELECT OrderID, ROW_NUMBER() OVER(ORDER BY OrderID) AS 編號
FROM dbo.Orders

但如果是在舊版的 SQL Server 2000 中，我們可能得用以下的「子查詢」寫法：

SET STATISTICS TIME ON
SELECT OrderID,
(SELECT COUNT(*) FROM dbo.Orders AS 內圈
WHERE 內圈.OrderID <= 外圈.OrderID) AS 編號
FROM dbo.Orders AS 外圈
ORDER BY 編號

但這種舊寫法，會像先前所提到的，外層查詢的「每一次」查詢動作都需要引用內層查詢的資料。以上方例子而言，外層查詢的每一筆資料，都要等內層查詢「掃描整個資料表」並作比對和計數，因此 830 筆資料每一筆都要重複掃描整個資料表 830 次，所耗用的時間也因此爆增至 170 ms。

若您用相同的寫法，去查詢 AdventureWorks 資料庫中，有 31,465 筆資料的 Sales.SalesOrderHeader 資料表，用 ROW_NUMBER 函數要 677 ms，還不到 1 秒鐘；但用子查詢的話，居然要高達 225,735 ms，將近快 4 分鐘的時間。

雖然這是較極端的範例，但由此可知子查詢的撰寫，在使用上不可不慎，尤其是「關聯子查詢」。程式員在程式開發初期、資料量還很少時感受不到此種 SQL 語法的重大陷阱；但等到系統上線幾個月或一兩年後，可能就會有反應遲緩的現象。

---

7、其他查詢技巧

• DISTINCT、ORDER BY 語法，會讓資料庫做額外的計算。此外聯集的使用，若沒有要剔除重複資料的需求，使用 UNION ALL 會比 UNION 更佳，因為後者會加入類似 DISTINCT 的演算法。
• 在 SQL Server 2005 版本中，存取資料庫物件時，最好明確指定該物件的「結構描述 (Schema)」，也就是使用兩節式名稱。否則若呼叫者的預設 Schema 不是 dbo，則 SQL Server 在執行時，會先尋找該使用者預設 Schema 所搭配的物件，找不到的話才會轉而使用預設的 dbo，會多耗費尋找的時間。例如若要執行一個叫做 dbo.mySP1 的 Stored Procedure，應使用以下的兩節式名稱：
  

  EXEC dbo.mySP1
  

---

8、儘可能用 Stored Procedure 取代前端應用程式直接存取資料表

Stored Procedure 除了經過事先編譯、效能較好以外，亦可節省 SQL 陳述式傳遞的頻寬，也方便商業邏輯的重複使用。再搭配自訂函數和 View 的使用，將來若要修改資料表結構、重新切割或反正規化時亦較方便。

---

9、儘可能在資料來源層，就先過濾資料

使用 SELECT 語法時，儘量避免傳回所有的資料至前端而不設定 WHERE 等過濾條件。雖然 ASP.NET 中 SqlDataSource、ObjectDataSource 控制項的 FilterExpression 可再做篩選，GridView 控制項的 SortExpression 可再做排序，但會多消耗掉資料庫的系統資源、Web server 的記憶體和網路頻寬。最好還是在資料庫和資料來源層，就先用 SQL 條件式篩選出所要的資料。

---

結論：

本文的觀念，不管是寫 SQL statement、Stored Procedure、自訂函數或 View 皆然。本文只是挑出程式員較容易犯的 SQL 語法效能問題，以期能在短時間瀏覽過本文後，在寫 ADO.NET 程式時能修正以往隨興的 SQL 撰寫習慣。文中提到的幾點，只不過是 SQL 語法效能議題的入門篇。後續有時間的話，版工會再補充在本帖的回應留言，或另開新主題。

---

參考文件：

[1] SQL查詢最佳化 (網際烏托邦)：
http://www.ithome.com.tw/plog/index.php?op=ViewArticle&articleId=5421&blogId=620

參考書籍：

[2] SQL Server 2005 Performance Tuning 效能調校：
作者：胡百敬、姚巧枚、劉承修
出版社：悅知出版社
http://tlsj.tenlong.com.tw/WebModule/BookSearch/bookSearchViewAction.do?isbn=9789866761225&sid=41966

[3] SQL Server 2005 完全實戰：
作者：章立民
出版社：碁峰出版社

相關文件：

[4] 臺大醫院資料庫分割疏失，系統幾近停擺 (ITHome)：
http://www.ithome.com.tw/itadm/article.php?c=43597

[5] 當DataGrid遇見100萬筆資料：
http://blog.sina.com.tw/4907/article.php?pbgid=4907&entryid=3921

[6] ASP.NET 2.0 GridView 範例集 - 「4-8-4、GridView的效能」：
http://blog.csdn.net/Code6421/archive/2007/12/22/1958167.aspx

[7] 有關開啟頁面時，一次載入數千筆資料的效能問題：
http://www.blueshop.com.tw:80/board/show.asp?subcde=BRD200709141021458MV

[MySQL] 計算漢明距離

之前寫了用 PHP 計算圖片的 hash code，接著要怎麼去搜尋相似的圖片呢？

資料庫是一個很好的選擇，首先建立一個簡單的 Table：

CREATE TABLE image_hash (
    id         INT(11) NOT NULL AUTO_INCREMENT,
    filepath   VARCHAR(256) NOT NULL,
    hash       BIGINT(20) UNSIGNED ZEROFILL NOT NULL,

    PRIMARY KEY  (id)
) ENGINE=MYISAM;

再來要怎麼將 hash code 寫進資料表呢？我們可以利用 b'010101' 的 bit 表示語法將 hash code 64位元字串存進 BIGINT 裡面。

INSERT INTO image_hash_info (filepath, hash)
    VALUES('/home/jax/12.jpg', b'10100111...');

資料已經備齊了，最重要的就是怎麼計算漢明距離了，MySQL 提供了 BIT_COUNT() 的函數，可以計算數字中位元 1 的個數，所以剩下的就是 XOR 的運算了。

SELECT BIT_COUNT(29), BIT_COUNT(b'101010');
       -- -> 4, 3

找尋與某一個 hash code 相似的圖片

SELECT * FROM image_hash
    WHERE BIT_COUNT(hash ^ b'1010011110...') <= 10

參考來源：
MySQL 5.0 Bit Functions

[轉載][MySQL] 修復工具 mysqlcheck

轉載自：mysql修复工具mysqlcheck

mysqlcheck -a -c -o -r -m --all-databases　-uroot -p

即可最佳化所有db
參數含意：

• -a = Analyse given tables.
• -c = Check table for errors
• -o = Optimise table
• -r = Can fix almost anything except unique keys that aren't unique
• -m = --medium-check

mysqlcheck 客戶端可以檢查和修復 MyISAM 資料表。它還可以優化和分析資料表。

mysqlcheck 的功能類似 myisamchk，但其工作不同。主要差別是當 mysqld 服務器在運行時必須使用mysqlcheck，而 myisamchk 應用於服務器沒有運行時。使用 mysqlcheck 的好處是不需要停止服務器來檢查或修復資料表。使用 myisamchk 修復失敗是不可逆的。

Mysqlcheck 為用戶提供了一種方便的使用 SQL 語句 CHECK TABLE、REPAIR TABLE、ANALYZE TABLE和OPTIMIZE TABLE 的方式。它確定在要執行的操作中使用使用哪個語句，然後將語句發送到要執行的服務器上。


有3種方式來調用mysqlcheck：
shell> mysqlcheck [options] db_name [tbl_name ...]
shell> mysqlcheck [options] --databases db_name ...
shell> mysqlcheck [options] --all-databases

如果沒有指定任何資料表或使用 --databases 或 --all-databases 選項，則檢查整個數據庫。

同其它客戶端比較，mysqlcheck 有一個特殊特性。重新命名二進制可以更改檢查資料表的默認行為(--check)。如果你想要一個工具默認可以修復資料表的工具，只需要將 mysqlcheck 重新復制為 mysqlrepair，或者使用一個符號鏈接 mysqlrepair 鏈接 mysqlcheck。如果調用 mysqlrepair，可按照命令修復資料表。


下面的名可用來更改 mysqlcheck 的默認行為：

mysqlrepair	默認選項為 --repair
mysqlanalyze	默認選項為 --analyze
mysqloptimize	默認選項為 --optimize

mysqlcheck支持下面的選項：

-A, --all-databases

檢查所有數據庫中的所有資料表。與使用---database選項相同，在命令行中命名所有數據庫。

-a, --analyze

分析資料表。

-1, --all-in-1

不是為每個表發出一個語句，而是為命名數據庫中待處理的所有表的每個數據庫執行一個語句。

--auto-repair

如果某個被檢查的表破壞了，自動修復它。檢查完所有表後自動進行所有需要的修復。

--character-sets-dir=name

字符集的安裝目錄。參見5.10.1節，"數據和排序用字符集"。

-c, --check

檢查資料表的錯誤。

-C, --check-only-changed

只檢查上次檢查以來已經更改的或沒有正確關閉的資料表。

--compress

壓縮在客戶端和服務器之間發送的所有信息（如果二者均支持壓縮）。

-B, --databases

處理數據庫中命名的所有資料表。使用該選項，所有字名參量被看作數據庫名，而不是資料表名稱。

-#, --debug[=#]

寫調試日誌。 debug_options字符串通常為'd:t:o,file_name'。

--default-character-set=name

使用charsetas默認字符集。參見5.10.1節，"數據和排序用字符集"。

-F, --fast

只檢查沒有正確關閉的資料表。

-f, --force

即使出現SQL錯誤也繼續。

-e, --extended

如果你正使用該選項來檢查資料表，可以確保它們100%地一致，但需要很長的時間。 如果你正使用該選項來修復資料表，則運行擴展修復，不但執行的時間很長，而且還會產生大量的垃圾行!

-?, --help

顯示幫助消息並退出。

-h, --host=name

連接給定主機上的MySQL服務器。

-m, --medium-check

執行比--extended操作更快的檢查。只能發現99.99%的錯誤，在大多數情況下這已經足夠了。

-o, --optimize

優化資料表。

-p, --password[=name]

當連接服務器時使用的密碼。如果使用短選項形式(-p)，選項和密碼之間不能有空格。 如果在命令行中--password或-p選項後面沒有密碼值，則提示輸入一個密碼。

-P, --port=#

用於連接的TCP/IP端口號。

--protocol=name

使用的連接協議。

-q, --quick

如果你正使用該選項在檢查資料表，它防止掃描行以檢查錯誤鏈接的檢查。這是最快的檢查方法。 如果你正使用該選項在修復資料表，它嘗試只修復索引樹。這是最快的修復方法。

-r, --repair

執行可以修復大部分問題的修復，只是唯一值不唯一時不能修復。

-s, --silent

沉默模式。只打印錯誤消息。

-S, --socket=name

用於連接的套接字文件。

--tables

覆蓋---database或-B選項。選項後面的所有參量被視為資料表名稱。

-u, --user=name

當連接服務器時使用的MySQL用戶名。

-v, --verbose

冗長模式。打印關於各階段程序操作的信息。

-V, --version

顯示版本信息並退出。

[轉載] [MySQL] QueryCache原理

轉載自：PHP 程式 學習 筆記本 [Mysql]QueryCache原理

原理
QueryCache(下面簡稱QC)是根據SQL語句來cache的。一個SQL查詢如果以select開頭，那麼MySQL服務器將嘗試對其使用QC。每個Cache都是以SQL文本作為key來存的。在應用QC之前，SQL文本不會被作任何處理。也就是說，兩個SQL語句，只要相差哪怕是一個字符（例如大小寫不一樣；多一個空格等），那麼這兩個SQL將使用不同的一個CACHE。

不過SQL文本有可能會被客戶端做一些處理。例如在官方的命令行客戶端裡，在發送SQL給服務器之前，會做如下處理：

• 過濾所有註釋
• 去掉SQL文本前後的空格,TAB等字符。注意，是文本前面和後面的。中間的不會被去掉。

下面的三條SQL裡，因為SELECT大小寫的關係，最後一條和其他兩條在QC裡肯定是用的不一樣的存儲位置。而第一條和第二條，區別在於後者有個註釋，在不同客戶端，會有不一樣的結果。所以，保險起見，請儘量不要使用動態的註釋。在PHP的mysql擴展裡，SQL的註釋是不會被去掉的。也就是三條SQL會被存儲在三個不同的緩存裡，雖然它們的結果都是一樣的。

SELECT * FROM people where name='surfchen';

SELECT * FROM people where /*hey~*/name='surfchen';

SELECT * FROM people where name='surfchen';

目前只有select語句會被cache，其他類似show,use的語句則不會被cache。

因為QC是如此前端，如此簡單的一個緩存系統，所以如果一個表被更新，那麼和這個表相關的SQL的所有QC都會被失效。假設一個聯合查詢裡涉及到了表A和表B，如果表A或者表B的其中一個被更新（update或者delete），這個查詢的QC將會失效。

也就是說，如果一個表被頻繁更新，那麼就要考慮清楚究竟是否應該對相關的一些SQL進行QC了。一個被頻繁更新的表如果被應用了QC，可能會加重數據庫的負擔，而不是減輕負擔。我一般的做法是默認打開QC，而對一些涉及頻繁更新的表的SQL語句加上SQL_NO_CACHE關鍵詞來對其禁用CACHE。這樣可以儘可能避免不必要的內存操作，儘可能保持內存的連續性。

那些查詢很分散的SQL語句，也不應該使用QC。例如用來查詢用戶和密碼的語句——「select pass from user where name='surfchen'」。這樣的語句，在一個系統裡，很有可能只在一個用戶登陸的時候被使用。每個用戶的登陸所用到的查詢，都是不一樣的SQL文本，QC在這裡就幾乎不起作用了，因為緩存的數據幾乎是不會被用到的，它們只會在內存裡佔地方。


存儲塊
在本節裡「存儲塊」和「block」是同一個意思
QC緩存一個查詢結果的時候，一般情況下不是一次性地分配足夠多的內存來緩存結果的。而是在查詢結果獲得的過程中，逐塊存儲。當一個存儲塊被填滿之後，一個新的存儲塊將會被創建，並分配內存（allocate）。單個存儲塊的內存分配大小通過query_cache_min_res_unit參數控制，默認為4KB。最後一個存儲塊，如果不能被全部利用，那麼沒使用的內存將會被釋放。如果被緩存的結果很大，那麼會可能會導致分配內存操作太頻繁，系統系能也隨之下降；而如果被緩存的結果都很小，那麼可能會導致內存碎片過多，這些碎片如果太小，就很有可能不能再被分配使用。

除了查詢結果需要存儲塊之外，每個SQL文本也需要一個存儲塊，而涉及到的表也需要一個存儲塊（表的存儲塊是所有線程共享的，每個表只需要一個存儲塊）。存儲塊總數量=查詢結果數量*2+涉及的數據庫表數量。也就是說，第一個緩存生成的時候，至少需要三個存儲塊：表信息存儲塊，SQL文本存儲塊，查詢結果存儲塊。而第二個查詢如果用的是同一個表，那麼最少只需要兩個存儲塊：SQL文本存儲塊，查詢結果存儲塊。

通過觀察Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每個緩存結果占用的存儲塊。它們的比例如果接近1:2，則說明當前的query_cache_min_res_unit參數已經足夠大了。如果Qcache_total_blocks比Qcache_queries_in_cache多很多，則需要增加query_cache_min_res_unit的大小。

Qcache_queries_in_cache * query_cache_min_res_unit（sql文本和表信息所在的block佔用的內存很小，可以忽略）如果遠遠大於query_cache_size-Qcache_free_memory，那麼可以嘗試減小query_cache_min_res_unit的值。


調整大小
如果Qcache_lowmem_prunes增長迅速，意味著很多緩存因為內存不夠而被釋放，而不是因為相關表被更新。嘗試加大query_cache_size，儘量使Qcache_lowmem_prunes零增長。


啟動參數
show variables like 'query_cache%' 可以看到這些信息。
query_cache_limit:如果單個查詢結果大於這個值，則不Cache
query_cache_size:分配給QC的內存。如果設為0，則相當于禁用QC。要注意QC必須使用大約40KB來存儲它的結構，如果設定小於40KB,則相當于禁用QC。QC存儲的最小單位是1024 byte，所以如果你設定了一個不是1024的倍數的值，這個值會被四捨五入到最接近當前值的等於1024的倍數的值。
query_cache_type:
0 完全禁止QC，不受SQL語句控制（另外可能要注意的是，即使這裡禁用，上面一個參數所設定的內存大小還是會被分配）；
1啟用QC，可以在SQL語句使用SQL_NO_CACHE禁用；
2可以在SQL語句使用SQL_CACHE啟用。

query_cache_min_res_unit:每次給QC結果分配內存的大小


狀態
show status like 'Qcache%' 可以看到這些信息。
Qcache_free_blocks:當一個表被更新之後，和它相關的cache blocks將被free。但是這個block依然可能存在隊列中，除非是在隊列的尾部。這些blocks將會被統計到這個值來。可以用FLUSH QUERY CACHE語句來清空free blocks。

其他幾個狀態變量的意義：
Qcache_free_memory 表示查詢緩存區現在還有多少的可用內存,如果很小，考慮增加query_cache_size
Qcache_hits 表示查詢緩存區的命中個數，也就是直接從查詢緩存區作出響應處理的查詢個數
Qcache_inserts 表示查詢緩存區此前總過緩存過多少條查詢命令的結果
Qcache_lowmem_prunes 表示查詢緩存區已滿而從其中溢出和刪除的查詢結果的個數
Qcache_not_cached 自mysql進程啟動起，沒有被cache的只讀查詢數量（包括select,show,use,desc等）
Qcache_queries_in_cache 當前被cache的SQL數量
Qcache_total_blocks:在QC中的blocks數。一個query可能被多個blocks存儲，而這幾個blocks中的最後一個，未用滿的內存將會被釋放掉。例如一個QC結果要佔6KB內存，如果query_cache_min_res_unit是4KB，則最後將會生成3個blocks，第一個block用來存儲sql語句文本，這個不會被統計到query+cache_size裡，第二個block為4KB，第三個block為2KB（先allocate4KB，然後釋放多餘的2KB）。每個表，當第一個和它有關的SQL查詢被CACHE的時候，會使用一個block來存儲表信息。也就是說，block會被用在三處地方：表信息，SQL文本，查詢結果。

優化提示：如果Qcache_lowmem_prunes 值比較大，表示查詢緩存區大小設置太小，需要增大。如果Qcache_free_blocks 較多，表示內存碎片較多，需要清理，flush query cache

根據我看的 《High Performance MySQL》中所述，關於query_cache_min_res_unit大小的調優，書中給出了一個計算公式，可以供調優設置參考：
query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

還要注意一點的是，FLUSH QUERY CACHE 命令可以用來整理查詢緩存區的碎片，改善內存使用狀況，但不會清理查詢緩存區的內容，這個要和RESET QUERY CACHE相區別，不要混淆，後者才是清除查詢緩存區中
的所有的內容。

[MySQL] 整數型態的大小

類型	Byte	肯定位數	最大位數	最大值	無符號最大值
TINYINT	1	2	3	127	255
SMALLINT	2	4	5	32767	65535
MEDIUMINT	3	7	8	8388607	16777215
INT	4	9	10	2147483647	4294967296
BIGINT	8	19	20	9223372036854775807	18446744073709551615

[Ubuntu 11] 安裝 Redmine 與 SVN

安裝 LAMP & Redmine

# 安裝 LAMP
apt-get install apache2 php5 mysql-server php5-mysql libapache2-mod-php5 libapache2-mod-auth-mysql 

# 安裝 svn, dav_svn
apt-get install subversion libapache2-svn 

# 安裝 redmine
apt-get install redmine redmine-mysql libapache2-mod-fcgid libapache2-mod-passenger

# 可以使用下面的方式重新設定 redmine DB 連結
#dpkg-reconfigure redmine 

# 啟用 Apache 套件 php5, headers, expires
a2enmod php5 dav_svn auth_mysql cgid fcgid passenger rewrite ssl setenvif 

# 安裝 git, phpmyadmin
apt-get install mercurial git-core phpmyadmin

# 連結 phpmyadmin 設定檔
cd /etc/apache2/conf.d/
ln -s ../../phpmyadmin/apache.conf phpmyadmin.conf
service apache2 restart

安裝 Redmine 套件

gem install pandoc-ruby rdiscount rpeg-markdown bluefeather

cd /usr/share/redmine/vendor/plugins


# 安裝 Code Review 套件
hg clone https://bitbucket.org/haru_iida/redmine_code_review
rake db:migrate_plugins RAILS_ENV=production


# 安裝 Markdown Extra formatter 套件
git clone git://github.com/juno/redmine_markdown_extra_formatter.git


# 安裝 reStructuredText formatting 套件
git clone git://github.com/alphabetum/redmine_restructuredtext_formatter.git
cd redmine_restructuredtext_formatter
git checkout pandoc-ruby

建立SVN庫

mkdir -p /home/repoadmin/repos
svnadmin create /home/repoadmin/repos/team1
svnadmin create /home/repoadmin/repos/team2
chown -R www-data:www-data /home/repoadmin/repos

建立SVN認證時需要的DB使用者(redmine_svn_auth)

CREATE USER 'redmine_svn_auth'@'localhost' IDENTIFIED BY 'redmine_svn_auth';
GRANT SELECT(id, login, hashed_password, status ,type) ON redmine_default.users TO 'redmine_svn_auth'@'localhost';

設定 Apache2 Site

cd /etc/apache2/sites-available/
cp default redmine_svn
vim redmine_svn

<VirtualHost *:80>
    ServerAdmin webmaster@localhost
    DocumentRoot /usr/share/redmine/public

    <Directory />
        Options FollowSymLinks
        AllowOverride None
    </Directory>

    <Directory /usr/share/redmine/public>
        Options ExecCGI FollowSymLinks
        AllowOverride None
        Order allow,deny
        Allow from all

        RewriteEngine On
        RewriteRule ^$ index.html [QSA]
        RewriteRule ^([^.]+)$ $1.html [QSA]
        RewriteCond %{REQUEST_FILENAME} !-f [OR]
        RewriteCond %{REQUEST_FILENAME} dispatch.fcgi$
        RewriteRule ^(.*)$ dispatch.fcgi [QSA,L]
    </Directory>

    <Location /svn>
        DAV svn
        SVNParentPath /home/repoadmin/repos
        SVNPathAuthz off

        AuthBasicAuthoritative Off
        AuthUserFile /dev/null
        AuthType Basic
        AuthName "Subversion Repository"

        # auth_mysql help in http://localhost/doc/libapache2-mod-auth-mysql/DIRECTIVES.gz
        Auth_MYSQL On
        Auth_MySQL_Host localhost
        Auth_MYSQL_DB redmine_default
        Auth_MYSQL_Username redmine_svn_auth
        Auth_MYSQL_Password redmine_svn_auth
        Auth_MYSQL_Password_Table users
        Auth_MYSQL_Username_Field login
        Auth_MYSQL_Password_Field hashed_password
        Auth_MySQL_Password_Clause " AND status=1 AND type='User' "
        Auth_MYSQL_Empty_Passwords Off
        Auth_MYSQL_Encryption_Types SHA1Sum
        # Options: Crypt_DES, Crypt_MD5, Crypt, PHP_MD5, SHA1Sum, MySQL, Apache

        Require valid-user
    </Location>

    LogLevel warn
    CustomLog /var/log/apache2/redmine_access.log combined
    ErrorLog /var/log/apache2/redmine_error.log
</VirtualHost>

重新啟動 Apache

a2dissite default
a2ensite redmine_svn
service apache2 restart

測試網址

• Redmine: http://localhost/
• SVN: http://localhost/svn/team1/

參考來源：
將 Redmine 安裝於 Debian、Ubuntu Linux
Ubuntu 10.04 using Passenger
svn 使用和 redmine 相同帳號進行認證

[Ubuntu] 安裝 Oracle Client 與 PDO_OCI

於 Oracle Database Instant Client 下載 Client/SDK
(Version 10.2.0.4 Instant Client Package - Basic, Instant Client Package - SDK)
oracle-instantclient-basic-10.2.0.4-1.i386.rpm
oracle-instantclient-devel-10.2.0.4-1.i386.rpm

# 安裝套件轉換器
apt-get install alien 

# 轉換 rpm 套件到 deb，並安裝
alien -i oracle-instantclient-basic*.rpm
alien -i oracle-instantclient-devel*.rpm

# 安裝 Apache2,PHP,MySQL
apt-get install apache2 php5 mysql-server php5-mysql libapache2-mod-php5

# 安裝 PEAR 
apt-get install php-pear php5-dev dh-make-php make re2c

# 下載 PDO_OCI 原始檔 
pecl download pdo
pecl download pdo_oci
tar zxvf PDO-1.0.3.tgz
tar zxvf PDO_OCI-1.0.tgz

mkdir -p PDO_OCI-1.0/include/php/ext/
mv PDO-1.0.3 PDO_OCI-1.0/include/php/ext/pdo
cd PDO_OCI-1.0/
phpize
./configure --with-pdo-oci=instantclient,/usr,10.2.0.4

make -j$(grep processor /proc/cpuinfo |wc -l)
make install  # /usr/lib/php5/20xxxxxx+lfs/pdo_oci.so

vim /etc/php5/conf.d/pdo_oci.ini # 建立 pdo_oci.ini, 內容如下:
extension=pdo_oci.so

vim /etc/apache2/envvars # 在最後面加入環境變數, 內容如下: 
export NLS_LANG="TRADITIONAL CHINESE_TAIWAN.UTF8"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

# 重新啟動 Apache 
service apache2 restart

參考來源：
Debian 安裝設定 PHP 連 Oracle extension 使用 PDO(PDO_OCI)
Oracle Instant Client

Sphinx 增量索引的方法

之前有寫過一篇 MySQL 全文檢索引擎 - Sphinx 的文章，最近又把它拿出來用了，不過當時即時更新索引的問題，如今則找到解決的方法了，透過更新增量索引的方式達到即時更新。

簡單的說就是利用兩個索引表的合併查詢來做到，一個是完整的索引表，一個是針對當日資料變動的增量索引。

部分的設定檔如下：

# ...

source _source_base
{
    # 來源-共用的設定
}

source people_full : _source_base
{
    sql_query      = \
        SELECT \
            people_profile.id, \
            people_profile.main_name \
        FROM people_profile
        
    sql_query_killlist = \
        SELECT id FROM people_profile \
        WHERE update_date >= CURDATE()
}

# 增量索引來源，這邊只會抓出當日變動的資料
# 建議在 update_date 欄位加上 MySQL INDEX
source people_delta : people_full
{
    sql_query      = \
        SELECT \
            people_profile.id, \
            people_profile.main_name \
        FROM people_profile \
        WHERE people_profile.update_date >= CURDATE()
}


index _index_base
{
    # 索引-共用的設定
}

index people_full : _index_base
{
    source          = people_full
    path            = /var/lib/sphinxsearch/data/people_full
}

# 增量索引
index people_delta : _index_base
{
    source          = people_delta
    path            = /var/lib/sphinxsearch/data/people_delta
}

# 透過 distributed 類型來合併索引
index people
{
    type            = distributed
    local           = people_full
    local           = people_delta
}

# ...

建立 SphinxSE 表
特別注意在 CONNECTION 中所指定索引表為 people。

CREATE TABLE people_sphinx(
    id BIGINT UNSIGNED NOT NULL   COMMENT '搜尋結果的 Id',
    weight INT NOT NULL           COMMENT '搜尋結果的權重',
    query VARCHAR(3072) NOT NULL  COMMENT '搜尋的查詢條件',
  
    INDEX(query)
)ENGINE=SPHINX 
CONNECTION="sphinx://localhost:9312/people" 
COMMENT='People Sphinx搜尋連接介面';

SQL 的查詢測試
這裡使用 INNER JOIN 方式作查詢，這樣對於刪除資料的變動，就不會出現在查詢結果中。

SELECT A.id, A.main_name, B.weight
FROM people_sphinx B
INNER JOIN people_profile A
USING(id) 
WHERE B.query='馬丁尼茲;mode=any;limit=1000'

透過 PHP 更新增量索引
在資料 INSERT 或 UPDATE 時，去呼叫索引更新，這樣在第一時間就可以更新索引。

shell_exec('sudo indexer --quiet --rotate people_delta 2>&1');

如果 Server 是 Ubuntu，請記得在 vim /etc/sudoers 中賦予 apache 使用 indexer 的權限。
www-data ALL=(root) NOPASSWD: /usr/bin/indexer


在 crontab 中加上排程
利用離峰時間來更新完整的索引表，由於刪除資料的變動需要更新完整索引表才有辦法移除。
00 00 * * * indexer --quiet --rotate --all > /dev/null 2>&1

[轉載][MySQL] Replication option binlog_format

轉載自：MYSQL5.1复制参数binlog_format

MySQL 5.1 中，在復制方面的改進就是引進了新的複制技術：基於行的複制。 簡言之，這種新技術就是關注表中發生變化的記錄，而非以前的照抄binlog 模式。 從MySQL 5.1.12 開始，可以用以下三種模式來實現：基於SQL語句的複制(statement-based replication, SBR)，基於行的複制(row-based replication, RBR)，混合模式複制(mixed-based replication, MBR)。 相應地，binlog的格式也有三種：STATEMENT，ROW，MIXED。 MBR 模式中，SBR 模式是默認的。


在運行時可以動態低改變binlog的格式，除了以下幾種情況：

1. 存儲過程或者觸發器中間
2. 啟用了NDB
3. 當前會話試用RBR 模式，並且已打開了臨時表

如果binlog採用了MIXED 模式，那麼在以下幾種情況下會自動將binlog的模式由SBR 模式改成RBR 模式。

1. 當DML語句更新一個NDB表時
2. 當函數中包含UUID() 時
3. 2個及以上包含AUTO_INCREMENT 字段的表被更新時
4. 行任何INSERT DELAYED 語句時
5. 用UDF 時
6. 視圖中必須要求使用RBR 時，例如創建視圖是使用了UUID() 函數

設定主從復制模式的方法非常簡單，只要在以前設定複製配置的基礎上，再加一個參數：

binlog_format="STATEMENT" 
#binlog_format="ROW" 
#binlog_format="MIXED"

當然了，也可以在運行時動態修改binlog的格式。 例如

mysql> SET SESSION binlog_format = 'STATEMENT'; 
mysql> SET SESSION binlog_format = 'ROW'; 
mysql> SET SESSION binlog_format = 'MIXED'; 

mysql> SET GLOBAL binlog_format = 'STATEMENT'; 
mysql> SET GLOBAL binlog_format = 'ROW'; 
mysql> SET GLOBAL binlog_format = 'MIXED'; 

現在來比較以下SBR 和RBR 2中模式各自的優缺點

SBR 的優點：

1. 歷史悠久，技術成熟
2. binlog文件較小
3. binlog中包含了所有數據庫更改信息，可以據此來審核數據庫的安全等情況
4. binlog可以用於實時的還原，而不僅僅用於復制
5. 主從版本可以不一樣，從服務器版本可以比主服務器版本高

SBR 的缺點：

1. 不是所有的UPDATE語句都能被複製，尤其是包含不確定操作的時候。
2. 調用具有不確定因素的UDF 時復制也可能出問題
3. 使用以下函數的語句也無法被複製：
   • LOAD_FILE()
   • UUID()
   • USER()
   • FOUND_ROWS()
   • SYSDATE() (除非啟動時啟用了--sysdate-is-now 選項)
4. INSERT ... SELECT 會產生比RBR 更多的行級鎖
5. 複製需要進行全表掃描(WHERE 語句中沒有使用到索引)的UPDATE 時，需要比RBR 請求更多的行級鎖
6. 對於有AUTO_INCREMENT 字段的InnoDB表而言，INSERT 語句會阻塞其他INSERT 語句
7. 對於一些複雜的語句，在從服務器上的耗資源情況會更嚴重，而RBR 模式下，只會對那個發生變化的記錄產生影響
8. 存儲函數(不是存儲過程)在被調用的同時也會執行一次NOW() 函數，這個可以說是壞事也可能是好事
9. 確定了的UDF 也需要在從服務器上執行
10. 數據表必須幾乎和主服務器保持一致才行，否則可能會導致複製出錯
11. 執行複雜語句如果出錯的話，會消耗更多資源

RBR 的優點：

1. 任何情況都可以被複製，這對複制來說是最安全可靠的
2. 和其他大多數數據庫系統的複制技術一樣
3. 多數情況下，從服務器上的表如果有主鍵的話，複製就會快了很多
4. 複製以下幾種語句時的行鎖更少：
   • INSERT ... SELECT
   • 包含AUTO_INCREMENT 字段的INSERT
   • 沒有附帶條件或者並沒有修改很多記錄的UPDATE 或DELETE 語句
5. 執行INSERT，UPDATE，DELETE 語句時鎖更少
6. 從服務器上採用多線程來執行複製成為可能

RBR 的缺點：

1. binlog 大了很多
2. 複雜的回滾時binlog 中會包含大量的數據
3. 主服務器上執行UPDATE 語句時，所有發生變化的記錄都會寫到binlog 中，而SBR 只會寫一次，這會導致頻繁發生binlog 的並發寫問題
4. UDF 產生的大BLOB 值會導致複製變慢
5. 無法從binlog 中看到都複製了寫什麼語句
6. 當在非事務表上執行一段堆積的SQL語句時，最好採用SBR 模式，否則很容易導致主從服務器的數據不一致情況發生

另外，針對系統庫 mysql 裡面的表發生變化時的處理規則如下：

1. 如果是採用INSERT，UPDATE，DELETE 直接操作表的情況，則日誌格式根據binlog_format 的設定而記錄
2. 如果是採用GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那麼無論如何都採用SBR 模式記錄

注：採用RBR 模式後，能解決很多原先出現的主鍵重複問題

[MySQL] Replication Master to Slave

Master上的設定

my.cnf

# 主機ID，不可以重複，範圍 0~4294967295
server-id       = 1

# Log 的名稱或路徑，只有名稱會將檔案放在 datadir 下
log_bin         = mysql-bin

# Log 的格式{ROW,STATEMENT,MIXED}，最低支援版本 5.1.8  
binlog_format = MIXED

# 需要同步的資料庫，多個寫多行
binlog_do_db    = test
binlog_do_db    = mydb

# 針對 InnoDB 且有用 transaction 的設定
innodb_flush_log_at_trx_commit=1
sync_binlog=1 

進入Master mysql
mysql -u root -p

建立同步連接帳號 db_sync，密碼12345，給 slave_host，
mysql> GRANT REPLICATION SLAVE ON *.* TO 'db_sync''slave_host' IDENTIFIED BY '12345';

鎖定資料庫寫入
mysql> FLUSH TABLES WITH READ LOCK;

取得 MASTER 上的 Log File 名稱與 Position 編號，這個編號代表當前的 Log 戳記
mysql> SHOW MASTER STATUS\G;

File:	mysql-bin.000010
Position:	106
Binlog_Do_DB:	test,mydb
Binlog_Ignore_DB:

離開 mysql
mysql> quit

匯出 MASTER 上的資料
mysqldump -uroot -p --flush-logs --opt --master-data test > test.sql

解除鎖定
mysql -u root -p -e UNLOCK TABLES;



Slave上的設定

my.cnf

# 主機ID，不可以重複，範圍 0~4294967295
server-id       = 2

# 需要同步的資料庫，或指定的資料表
replicate_wild_do_table = test.%
replicate_wild_do_table = mydb.category
replicate_wild_do_table = mydb.blog

# 與 Master 斷線後，重新嘗試連接的時間(sec) 
master-connect-retry = 60

匯入資料
mysql -uroot -p test < test.sql

進入 Slave mysql
mysql -uroot -p

停止跟清除之前的 SLAVE 連接
mysql> STOP SLAVE; RESET SLAVE;

設定Slave 與 Master 的連接

mysql> CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='db_sync',
    MASTER_PASSWORD='12345',
    MASTER_LOG_FILE='mysql-bin.000011',
    MASTER_LOG_POS=106;

啟動 SLAVE 的連接
mysql> START SLAVE;

察看連接狀態
mysql> SHOW SLAVE STATUS\G;

Slave_IO_State:	Connecting to master
Master_Host:	master_host
Master_User:	db_sync
Master_Port:	3306
Connect_Retry:	60
Master_Log_File:	mysql-bin.000011
Read_Master_Log_Pos:	106
Relay_Log_File:	master_host-relay-bin.000011
Relay_Log_Pos:	4
Relay_Master_Log_File:	mysql-bin.000011
Slave_IO_Running:	Yes
Slave_SQL_Running:	Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:	test.%,mydb.category,mydb.blog
Replicate_Wild_Ignore_Table:
Last_Errno:	0
Last_Error:
Skip_Counter:	0
Exec_Master_Log_Pos:	106
Relay_Log_Space:	106
Until_Condition:	None
Until_Log_File:
Until_Log_Pos:	0
Master_SSL_Allowed:	No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:	0

接著到 Master 上新增一筆資料測試看看吧！


參考文章：
MYSQL 主从服务器配置
mysql主从同步快速设置
MySQL 設定 Replication (Master – Slave)
多主一从mysql replication同步表的大胆尝试
MySQL复制的数据库过滤若干问题
MYSQL5.1复制参数binlog_format

[MySQL] EXPLAIN 分析報告的意義

轉載自：PHP + MySQL 程式設計研習

• table
  表示所引用的表格名稱。
• type
  表示查詢時的「聯結類型」（join type），以下依序是「最佳」至「最差」的各種類型：
• system
  表格中僅有一列。這是 const 類型的一個特例。
• const
  表格中符合條件的只有一列。因為僅有一列，其值在後續的查詢中可被視為常數。
• eq_ref
  表示在與其它表格的資料列結合時，此表格只有一列會被讀取。當 join 使用到資料表中的所有索引，並索引是 UNIQUE 或 PRIMARY KEY 時才會被用到。
• ref
  表示在與其它表格的資料列結合時，此表格中所有符合的資料列都會被讀出來。這是當 join 只使用到部份鍵值（註），或此鍵非 UNIQUE 或 PRIMARY KEY 時才會用到（依照 join 的條件仍然無法選定單一目標列）。若因此符合的資料列數不多的話，它也算是一種不錯的「聯結類型」。
  註：我們指定「A＋B」欄位為 index key，但查詢時只用到「A」欄位。
• range
  表示將在一定範圍內執行搜尋的動作。
• index
  與 ALL 相同，但只有 index table 會被瀏覽。這通常比 ALL 快，因為 index table 通常比原始資料表來得小。
• All
  表示這項查詢將對整個原始資料表瀏覽一遍，是最不好的類型。
• possible_keys
  表示 MySQL 能夠藉由哪些 index 來搜尋目標。
• key
  表示 MySQL 實際藉由哪個 index 來搜尋目標。
• key_len
  表示 MySQL 實際使用的 key 長度。若 index key 是由兩個欄位以上複合而成的話，您可以在此看見 MySQL 使用了 index 的多少部份。
• ref
  表示哪個欄位（或常數）將被用來與 key 一起比對。
• rows
  表示 MySQL 粗略估計在查詢的過程中，必須瀏覽的資料列數。
• Extra
  顯示 MySQL 在解決這項查詢工作時的一些附加訊息。例如：「where used」表示 where 子句將會限制某些資料列的輸出。

[MySQL] TEMPORARY 臨時表

最近為了解決一個效率太差的問題而找上這個東西，由於有一個資料表的筆數實在太多了，再加上好幾百個 query 都同時在同一個區段做查詢，剛好可以使用臨時表來處理這個問題，原本查詢需要的時間大概超過一分鐘，現在最多只要 10 秒，刷新後約 2 秒。

MySQL 的臨時表只會存在在 Session 期間，當 Session 結束後就會自動刪除，不同的 Session 的表名稱並不會衝突，所以就算用一樣的名稱也不會出現錯誤，臨時表只能用在 MEMORY,MyISAM,MERGE,或者InnoDB 引擎上。

-- 透過 SELECT 建立臨時表
CREATE TEMPORARY TABLE my_temp_table ENGINE=MEMORY
SELECT * FROM my_table WHERE col_1 > 1000

-- 為臨時表建立索引
ALTER TABLE my_temp_table
    ADD INDEX (col_1),
    ADD INDEX (col_1,col_2)

參考資料：
MySQL CREATE TABLE Syntax
mysql temporary table簡介
避免 MySQL 使用 temporary table on disk

[Oracle] PDO-OCI 的 DSN 連接字串

最近重裝我的開發環境，結果連 oracle 一直連不上，出現了以下幾種訊息，最後是因為我的 TNS listener 沒設好，或是我的 DSN 錯誤，整理出我試過的幾個 DSN 連接字串：

ORA-12154: TNS:could not resolve service name
ORA-06401: NETCMN: invalid driver designator
ORA-12514: TNS:listener could not resolve SERVICE_NAME
ORA-12505: TNS:listener could not resolve SID given in connect descriptor

$DSN="oci:dbname=jaxdb;charset=utf-8";

$DSN="oci:dbname=127.0.0.1:1521/jaxdb;charset=utf-8";

$DSN = "oci:dbname=(
    DESCRIPTION = (
        ADDRESS_LIST = (
            ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)
        )
    )(CONNECT_DATA=
        (SID = jaxdb)
    )
);charset=utf8";
    
$DSN = "oci:dbname=(
    DESCRIPTION = (
        ADDRESS_LIST = (
            ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)
        )
    )(CONNECT_DATA=
        (SERVICE_NAME = jaxdb)
    )
);charset=utf8";

[Regedit] Oracle 日期格式及語言編碼設定

上次用到時沒有記下來，結果忘記了，這次先把他記下來。

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Oracle]
"NLS_LANG"="AMERICAN_AMERICA.UTF8"
"NLS_DATE_FORMAT"="YYYY-MM-DD HH24:MI:SS"

[HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ALL_HOMES\ID0]
"NLS_LANG"="AMERICAN_AMERICA.UTF8"
"NLS_DATE_FORMAT"="YYYY-MM-DD HH24:MI:SS"

[HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\HOME0]
"NLS_LANG"="AMERICAN_AMERICA.UTF8"
"NLS_DATE_FORMAT"="YYYY-MM-DD HH24:MI:SS"

使用批次檔開關 Oracle 服務

因為在 NB 上灌了 Oracle 做開發用，可是每次開機都變得很慢，而且不是每天都會用到 Oracle，但常常開開關關服務真的很麻煩，索性找了一下批次檔開關服務的方法，寫了一個簡單的小工具，至少在處理這瑣碎的事可以快樂一點。

@echo off

set /p STATUS=Oracle Service status to [start/stop] : 

net %STATUS% "OracleMTSRecoveryService"
net %STATUS% "OracleOraHome92Agent"
net %STATUS% "OracleOraHome92TNSListener"
net %STATUS% "OracleServiceMYDB"

pause

[MySQL] mysqldump & mysql command for UTF-8 database

最近在 windows 上匯出/匯入資料庫時遇到 UTF-8 編碼問題，奇怪之前在 linux 上執行從未遇到的說，原來 default-character-set 是 latin1，所以在匯出/匯入時要指定成 UTF-8 就沒問題了。

匯出資料庫

PATH=C:\wamp\bin\mysql\mysql5.5.8\bin

REM export 'dbname' database
mysqldump -uroot -p1234 --default-character-set=utf8 dbname > dbname.sql

REM other dbname ...
pause

匯入資料庫

PATH=C:\wamp\bin\mysql\mysql5.5.8\bin

REM import 'dbname' database
mysql -uroot -p1234 -e "DROP DATABASE IF EXISTS `dbname`;"
mysql -uroot -p1234 -e "CREATE DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;"
mysql -uroot -p1234 --default-character-set=utf8 dbname < dbname.sql

REM other dbname ...
pause

用 Eclipse CDT 編譯 CppSQLite3

工作上為了讓 sqlite 可以在 platform 上執行，所以必須從完整的 source code 開始編譯，對於不怎麼熟悉 Makefile 的我這真是一件麻煩的事，還好以前有玩過 CDT，索性就利用他可以自動建立 Makefile 的功能來做。

不過我是使用 Eclipse 3.2 版的 CDT，Eclipse 3.5 的 CDT 裡的 Makefile 我不太會用，也沒有時間去找文章。


這裡我下載了兩個檔案：
SQLite -> sqlite-source-3_7_2.zip
CppSQLite -> CppSQLite_3_1_demo_and_src.zip


首先建立動態連結庫

1. 新增 "Managed Make C++ Project" 專案 -> 名稱 "cppsqlite"
   
   
2. 在 Project Type 中選擇 Shared Library
   
   
3. 在專案下新增 src 資料夾
   
   
4. 複製 sqlite-source-3_7_2.zip 中所有的 source code 至 src 除了 shell.c 與 tclsqlite.c
   
   
5. 再複製 CppSQLite_3_1_demo_and_src.zip 中的 CppSQLite3.h 與 CppSQLite3.cpp
   
   
6. 開啟：專案 -> 內容
   
   
7. 增加 Defined symbols 變數 -> SQLITE_CORE
   
   
   
8. 在 C++ 跟 C 的 Optimization 的參數中增加 -fPIC 最佳化參數
   
   
   
9. 按下『確定』後就會開始編譯，檔案有點多要稍微等一下

再來建立主程式專案

1. 新增 "Managed Make C++ Project" 專案 -> 名稱 "sqlite-test"
   
   
2. 在 Project Type 中選擇 Executable
   
   
3. 在專案下新增 src 資料夾
   
   
4. 複製 CppSQLite_3_1_demo_and_src.zip 中的 CppSQLite3Demo.cpp 至 src
   
   
5. 開啟：專案 -> 內容
   
   
6. 新增 Include paths -> "../../cppsqlite/src"
   這個設定是在告知編譯時額外 Include 的進來 Header(*.h) 的路徑。
   
   
   
7. 新增連結路徑：
   Library search path -> "../../cppsqlite/Debug"
   Libraries -> cppsqlite
   這個設定是給 gcc 在做連結時需要的搜尋路徑，以及需要連結的對象名稱。
   
   
   
8. 按下『確定』後就會開始編譯
   
   
9. 接著要將 cppsqlite.dll 複製到 sqlite-test/Debug 下
   Windows 的 lib 名稱為 "cppsqlite.dll"
   Linux 的 lib 名稱為 "libcppsqlite.so"
   
   
10. 然後就可以執行 sqlite-test.exe 了 (一整個就很快樂)

專案範例：sqlite-test.zip

[Ubuntu] PHP 連接 Oracle 語系設定

PHP 的環境變數必須在 Apache 中設定

開啟：
vim /etc/apache2/envvars

在最後面加入：
export NLS_LANG="AMERICAN_AMERICA.UTF8"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

參考來源：
如何新增apache使用的環境變數