tag:blogger.com,1999:blog-59465307047421309702024-03-06T16:20:07.273+08:00Jax 的工作紀錄除了在整理學習上的經驗,同時也能幫助其他需要的人Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.comBlogger44125tag:blogger.com,1999:blog-5946530704742130970.post-8444822696862903002020-07-31T20:01:00.002+08:002020-07-31T23:08:58.175+08:00資料庫設計原則以前跟同事一起訂下的資料庫設計原則,來減少一開始設計不好造成事後要進行大改的風險。<br />
<br />
實體關聯的設計是最需要謹慎的,這裡一旦與規格差太多,程式可能就要重寫。<br />
<br />
欄位名稱建議在系統中是唯一的,這樣可以減少 JOIN 時還需要換名稱,FK 使用跟 PK 一樣的名稱也可以增加維護性。<br />
<br />
<br />
<hr /><br />
<h3>資料庫設計流程</h3><br />
(Instance 層級)<br />
<ul><li>依照 Instance 建立 ER 圖,須清楚描述關聯與實體</li>
<li>審視並釐清實體對於規格的含蓋範圍</li>
</ul><br />
(Table 層級)<br />
<ul><li>依照釐清後的含蓋範圍,修正 ER 圖</li>
<li>審視並確認完整 ER圖</li>
</ul><br />
(Column 層級)<br />
<ul><li>定義各資料表欄位,建立 OrgTableSchema.sql</li>
<li>審視並確認完整 Schema</li>
<li>有問題重複上述步驟</li>
</ul><br />
(Develop 層級)<br />
<ul><li>維護 OrgTableSchema.sql</li>
<li>使用 DbSchemaTool 工具產生 Schema.sql</li>
<li>建立資料庫</li>
<li>修正 Dbml</li>
<li>更新 DB 專案</li>
</ul><br />
<br />
<h3>資料表與欄位定義</h3><ul><li>Id 是 identity 去尾的縮寫,所以 d 要小寫</li>
<li>No 改成 Num 會比較好,因為會跟 Yes, No 混淆</li>
<li>Id 是流水號,其他的建議用 Num 或 Code 表示</li>
<li>避免用單單字做欄位名,盡量用多單字 ( Ex. IssueType, CompanyCode, SettingId, CompanyName )</li>
<li>盡量用 流水號 或 Guid 這類無意義的 id 做 PK</li>
<li>除了多對多的中間表,PK 都必須是單一欄位</li>
<li>資料來源為 OptionSetting, 值為 OptionId 者,欄位名稱命名須加尾贅詞 Id,型態為 INT</li>
<li>資料來源為 Enum, 欄位型態則為 Nvarchar(N)</li>
<li>盡量避免資料來源為 bool 對應 bit,因為擴充性太低,資料來源改用 Enum 對應 Nvarchar(N)</li>
<li>歷史檔與主檔的差異</li>
<ul><li>主檔的 ModifiedBy ModifierdDate 為歷史檔的 CreatedBy CreatedDate,歷史檔無 ModifiedBy ModifierdDate</li>
<li>新增歷史檔的 PK , 主檔的 PK 設定為 FK</li>
<li>其餘欄位應該與主檔相同</li>
</ul><li>每一個 FK 需預設建立 IX,其餘調整於開發完成後,依照使用者回報進行調整</li>
<li>多對多的中間表如果超過 3 個附加欄位,必須用一般的方式設計</li>
</ul><br />
<br />
<h3>PK 必須使用 Guid 的情況</h3><ul><li>如果只保留短期資料,但會有大量新增或刪除(每天一萬筆新增)</li>
<li>如果有多系統都可以新增資料,最後要合流的狀況</li>
</ul><br />
<br />
<h3>設計 Table 的欄位順序</h3><ol><li>PK</li>
<li>FK [主檔]</li>
<li>FK [選項]</li>
<li>AK</li>
<li>其餘不重要的資料欄位</li>
<li>CreatedBy</li>
<li>CreatedDate</li>
<li>ModifiedBy</li>
<li>ModifierdDate</li>
</ol><br />
<br />
<h3>資料型態</h3><ul><li>文字 : NVARCHAR 長度為預定輸入的兩倍</li>
<li>日期 : DATETIMEOFFSET 具有時區紀錄(MSDN 建議)</li>
<li>時間 : TIME</li>
<li>整數 : INT</li>
<li>Enum : NVARCHAR (32)</li>
<li>Guid : UNIQUEIDENTIFIER</li>
<li>金錢 : MONEY</li>
<li>精確浮點數 : DECIMAL (18, 4)</li>
</ul><br />
<br />
<h3>鍵值規則</h3><ul><li>Table 規則 {ProjectName}_{TableName}<br />
Ex: WMS_Carrier</li>
<li>Foreign Key 規則 FK_{ProjectName}_{TableName}_{Columns}<br />
Ex: FK_WMS_CarrierMaterial_CarrierId</li>
<li>Unique 規則 AK_{ProjectName}_{TableName}_{Columns}<br />
Ex: FK_WMS_CarrierMaterial_CarrierId</li>
<li>Index 規則 IX_{ProjectName}_{TableName}_{Columns}<br />
Ex: IX_WMS_CarrierMaterial_CarrierId</li>
</ul><br />
<br />
<h3>縮寫解釋</h3><ul><li>PK: Primary Key</li>
<li>FK: Foreign Key</li>
<li>AK: Alternate Key</li>
<li>IX: IndeX</li>
<li>CK: ChecK</li>
<li>DF: DeFault</li>
</ul><br />
<br />
<h3>MSSQL 定序設定</h3><br />
定序 Chinese_Taiwan_Stroke_CS_AI<br />
<br />
_CS 區分大小寫<br />
_CI 不區分大小寫<br />
<br />
_AS 區分腔調 a != á<br />
_AI 不區分腔調<br />
<br />
_KS 區分日文假名字元<br />
_WS 區分全形與半形字元<br />
<br />
定序會影響查詢與唯一值的判定,例如不區分大小的定序在 WHERE 'abc' = 'ABC' 會是 true。<br />
<br />
定序選擇並沒有標準答案,我個人是採用區分大小的定序,如果規格是不區分的時候,再用程式轉大寫或小寫,這部分可以在 DAO 統一完成,雖然也會有遺漏的情況,但至少是可以由程式掌控,如果出現只有某些不區分,而大部分還是區分的時候,還是由程式掌控會比較好。<br />
<br />
<br />
<br />
Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-23506363444405207432015-02-21T18:10:00.000+08:002015-02-24T15:47:45.289+08:00[轉載] Get Tables And Columns Details In Schema Using JDBC轉載自:<a href="http://www.javaroots.com/2013/09/print-tables-details-in-schema-jdbc.html" target="_blank">JavaRoots: Get Tables And Columns Details In Schema Using JDBC</a><br />
<br />
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.<br />
<br />
<pre class="java:nogutter:nocontrols" name="code">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();
}
}
}
</pre><br />
This program will print tables along with colmuns like this :<br />
<pre class="none:nogutter:nocontrols" name="code">TABLE1( ID,NAME,STATUS,CREATED_AT,UPDATED_AT)
-----------------------------------
TABLE2( ID,NAME,STATUS,CREATED_AT,UPDATED_AT)
</pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-23030989327272617592013-08-25T13:56:00.000+08:002014-10-08T13:29:31.867+08:00[轉載] 調校 SQL 以徹底改善應用程式效能轉載自:<a target="_blank" href="http://blog.xuite.net/j2ee/code/15120677">調校 SQL 以徹底改善應用程式效能</a><br />
<br />
有些程式員在撰寫前端的應用程式時,會透過各種 OOP 語言將存取資料庫的 SQL 陳述式串接起來,卻忽略了 SQL 語法的效能問題。版工曾聽過某半導體大廠的新進程式員,所兜出來的一段 PL/SQL 跑了好幾分鐘還跑不完;想當然爾,即使他前端的 AJAX 用得再漂亮,程式效能頂多也只是差強人意而已。以下是版工整理出的一些簡單心得,讓長年鑽究 ASP.NET / JSP / AJAX 等前端應用程式,卻無暇研究 SQL 語法的程式員,避免踩到一些 SQL 的效能地雷。<br />
<br />
<br />
<strong>1、資料庫設計與規劃</strong><br />
<ul><li>Primary Key 欄位的長度儘量小,能用 small integer 就不要用 integer。例如員工資料表,若能用員工編號當主鍵,就不要用身分證字號。</li>
<li>一般欄位亦同。若該資料表要存放的資料不會超過 3 萬筆,用 small integer 即可,不必用 integer。</li>
<li>文字資料欄位若長度固定,如:身分證字號,就不要用 varchar 或 nvarchar,應該用 char 或 nchar。</li>
<li>文字資料欄位若長度不固定,如:地址,則應該用 varchar 或 nvarchar。除了可節省儲存空間外,存取磁碟時也會較有效率。</li>
<li>設計欄位時,若其值可有可無,最好也給一個預設值,並設成「不允許 NULL」(一般欄位預設為「允許 NULL」)。因為 SQL Server 在存放和查詢有 NULL 的資料表時,會花費額外的運算動作 [2]。</li>
<li>若一個資料表的欄位過多,應垂直切割成兩個以上的資料表,並用同名的 Primary Key 一對多連結起來,如:Northwind 的 Orders、Order Details 資料表。以避免在存取資料時,以叢集索引掃描時會載入過多的資料,或修改資料時造成互相鎖定或鎖定過久。</li>
</ul><br />
<br />
<hr /><strong>2、適當地建立索引</strong><br />
<ul><li>記得自行幫 Foreign Key 欄位建立索引,即使是很少被 JOIN 的資料表亦然。</li>
<li>替常被查詢或排序的欄位建立索引,如:常被當作 WHERE 子句條件的欄位。</li>
<li>用來建立索引的欄位,長度不宜過長,不要用超過 20 個位元組的欄位,如:地址。</li>
<li>不要替內容重複性高的欄位建立索引,如:性別;反之,若重複性低的欄位則適合建立索引,如:姓名。</li>
<li>不要替使用率低的欄位建立索引。</li>
<li>不宜替過多欄位建立索引,否則反而會影響到新增、修改、刪除的效能,尤其是以線上交易 (OLTP) 為主的網站資料庫。</li>
<li>若資料表存放的資料很少,就不必刻意建立索引。否則可能資料庫沿著索引樹狀結構去搜尋索引中的資料,反而比掃描整個資料表還慢。</li>
<li>若查詢時符合條件的資料很多,則透過「非叢集索引」搜尋的效能,可能反而不如整個資料表逐筆掃描。</li>
<li>建立「叢集索引」的欄位選擇至為重要,會影響到整個索引結構的效能。要用來建立「叢集索引」的欄位,務必選擇「整數」型別 (鍵值會較小)、唯一、不可為 NULL。</li>
</ul><br />
<br />
<hr /><strong>3、適當地使用索引</strong><br />
<ul><li>有些書籍會提到,使用 LIKE、% 做模糊查詢時,即使您已替某個欄位建立索引 (如下方例子的 CustomerID),但以常數字元開頭才會使用到索引,若以萬用字元 (%) 開頭則不會使用索引,如下所示:<br />
<pre class="sql:nogutter:nocontrols" name="code">USE Northwind;
GO
SELECT * FROM Orders WHERE CustomerID LIKE 'D%'; --使用索引
SELECT * FROM Orders WHERE CustomerID LIKE '%D'; --不使用索引
</pre><br />
執行完成後按 Ctrl+L,可檢閱如下圖的「執行計畫」。<br />
<br />
<img src="https://lh3.googleusercontent.com/-KBw56MgqA20/UhmXa87iZmI/AAAAAAAANHg/8VzDH18EwyM/s512/mssql-optimization-1.jpg" style="border:none;" /><br />
圖 1 可看出「查詢最佳化程式」有使用到索引做搜尋<br />
<br />
<img src="https://lh3.googleusercontent.com/-VfW8IGvmdn0/UhmXa8a2ClI/AAAAAAAANHg/X2XWjCDM5DA/s512/mssql-optimization-2.jpg" style="border:none;" /><br />
圖 2 在此的叢集索引掃描,並未直接使用索引,效能上幾乎只等於掃描整個資料表<br />
<br />
但經版工反覆測試,這種語法是否會使用到索引,抑或會逐筆掃描,並非絕對的。仍要看所下的查詢關鍵字,以及欄位內儲存的資料內容而定。但對於儲存資料筆數龐大的資料表,最好還是少用 LIKE 做模糊查詢。<br />
<br />
</li>
<li>以下的運算子會造成「負向查詢」,常會讓「查詢最佳化程式」無法有效地使用索引,最好能用其他運算子和語法改寫 (經版工測試,並非有負向運算子,就絕對無法使用索引):<br />
<code>NOT 、 != 、 <> 、 !> 、 !< 、 NOT EXISTS 、 NOT IN 、 NOT LIKE</code></li>
<li>避免讓 WHERE 子句中的欄位,去做字串串接或數字運算,否則可能導致「查詢最佳化程式」無法直接使用索引,而改採叢集索引掃描 (經版工測試並非絕對)。</li>
<li>資料表中的資料,會依照「叢集索引」欄位的順序存放,因此當您下 BETWEEN、GROUP BY、ORDER BY 時若有包含「叢集索引」欄位,由於資料已在資料表中排序好,因此可提升查詢速度。</li>
<li>若使用「複合索引」,要注意索引順序上的第一個欄位,才適合當作過濾條件。</li>
</ul><br />
<br />
<hr /><strong>4、避免在 WHERE 子句中對欄位使用函數</strong><br />
<br />
對欄位使用函數,也等於對欄位做運算或串接的動作,一樣可能會讓「查詢最佳化程式」無法有效地使用索引。但真正對效能影響最重大的,是當您的資料表內若有 10 萬筆資料,則在查詢時就需要呼叫函數 10 萬次,這點才是真正的效能殺手。程式員應注意,在系統開發初期可能感覺不出差異,但當系統上線且資料持續累積後,這些語法細節所造成的效能問題就會逐步浮現。<br />
<br />
<pre class="sql:nogutter:nocontrols" name="code">SELECT * FROM Orders WHERE DATEPART(yyyy, OrderDate) = 1996 AND DATEPART(mm, OrderDate)=7
-- 可改成
SELECT * FROM Orders WHERE OrderDate BETWEEN '19960701' AND '19960731'
</pre><br />
<pre class="sql:nogutter:nocontrols" name="code">SELECT * FROM Orders WHERE SUBSTRING(CustomerID, 1, 1) = 'D'
-- 可改成
SELECT * FROM Orders WHERE CustomerID LIKE 'D%'
</pre><br />
注意當您在下 UPDATE、DELETE 陳述式時,若有採用 WHERE 子句,也應符合上述原則。<br />
<br />
<br />
<hr /><strong>5、AND 與 OR 的使用</strong><br />
<br />
在 AND 運算中,「只要有一個」條件有用到索引 (如下方的 CustomerID),即可大幅提升查詢速度,如下圖 3 所示:<br />
<br />
<pre class="sql:nogutter:nocontrols" name="code">SELECT * FROM Orders WHERE CustomerID='VINET' AND Freight=32.3800
--使用索引,會出現下圖 3 的畫面
</pre><br />
<pre class="sql:nogutter:nocontrols" name="code">SELECT * FROM Orders WHERE Freight=32.3800
--不使用索引,會出現上圖 2 的畫面
</pre><br />
<img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigWdMoBqfL-DZyFX9iHyM6ExH9ciZB8u86JuWTJJSLnoJ7auH2UUgh93GyPxpwrJrMb8-M_0Fswvw-UUbLv2TB2fCEqpZB-b_uQ51Xu3Clafxj4YY4OUVus9TPilPhdX6LDqsgCfVx7JE8/s512/mssql-optimization-3.jpg" style="border:none;" /><br />
圖 3<br />
<br />
但在 OR 運算中,則要「所有的」條件都有可用的索引,才能使用索引來提升查詢速度。因此 OR 運算子的使用必須特別小心。<br />
<br />
若您將上方 AND 的範例,邏輯運算子改成 OR 的話,如下所示:<br />
<br />
<pre class="sql:nogutter:nocontrols" name="code">SELECT * FROM Orders WHERE CustomerID='VINET' OR Freight=32.3800
</pre><br />
由於無法有效地使用索引,也會出現圖 2 的畫面。<br />
<br />
在使用 OR 運算子時,只要有一個條件 (欄位) 沒有可用的索引,則其他所有的條件 (欄位) 都有索引也沒用,只能如圖 2 般,把整個資料表或整個叢集索引都掃描過,以逐筆比對是否有符合條件的資料。<br />
<br />
<br />
據網路上文件的說法 [1],上述的 OR 運算陳述式,我們還可用 UNION 聯集適當地改善,如下:<br />
<br />
<pre class="sql:nogutter:nocontrols" name="code">SELECT * FROM Orders WHERE CustomerID='VINET'
UNION
SELECT * FROM Orders WHERE Freight=32.3800
</pre><br />
此時您再按 Ctrl+L 檢閱「執行計畫」,會發現上半段的查詢會使用索引,但下半段仍用叢集索引掃描,對效能不無小補。<br />
<br />
<br />
<hr /><strong>6、適當地使用子查詢</strong><br />
<br />
相較於「子查詢 (Subquery)」,若能用 JOIN 完成的查詢,一般會比較建議使用後者。原因除了 JOIN 的語法較容易理解外,在多數的情況下,JOIN 的效能也會比子查詢較佳;但這並非絕對,也有的情況可能剛好相反。<br />
<br />
我們知道子查詢可分為「獨立子查詢」和「關聯子查詢」兩種,前者指子查詢的內容可單獨執行,後者則無法單獨執行,亦即外層查詢的「每一次」查詢動作都需要引用內層查詢的資料,或內層查詢的「每一次」查詢動作都需要參考外層查詢的資料。<br />
<br />
以下我們看一個比較極端的例子 [2]。若我們希望所有查詢出來的資料,都能另外給一個自動編號,版工我在之前的文章「用 SQL Server 2005 新增的 ROW_NUMBER 函數撰寫 GridView 分頁」中有介紹過,可用 SQL Server 2005 中新增的 ROW_NUMBER 函數輕易地達成,且 ROW_NUMBER 函數還能再加上「分群 (PARTITION BY)」等功能,而且執行效能極佳。<br />
<br />
<img src="https://lh6.googleusercontent.com/-kuVFN51A_XQ/UhmXbQA3D5I/AAAAAAAANHg/TSuJR5s2sMA/s512/mssql-optimization-4.jpg" style="border:none;" /><br />
圖 4 將 Orders 資料表的 830 筆資料都撈出來,並在右側給一組自動編號<br />
<br />
現在我們要如上圖 4 般,將 Northwind 中 Orders 資料表的 830 筆資料都撈出來,並自動給一組編號,若用 ROW_NUMBER 函數的寫法如下所示,而且效能極佳,只要 2 ms (毫秒),亦即千分之二秒。<br />
<br />
<pre class="sql:nogutter:nocontrols" name="code">SET STATISTICS TIME ON
SELECT OrderID, ROW_NUMBER() OVER(ORDER BY OrderID) AS 編號
FROM dbo.Orders
</pre><br />
但如果是在舊版的 SQL Server 2000 中,我們可能得用以下的「子查詢」寫法:<br />
<br />
<pre class="sql:nogutter:nocontrols" name="code">SET STATISTICS TIME ON
SELECT OrderID,
(SELECT COUNT(*) FROM dbo.Orders AS 內圈
WHERE 內圈.OrderID <= 外圈.OrderID) AS 編號
FROM dbo.Orders AS 外圈
ORDER BY 編號
</pre><br />
但這種舊寫法,會像先前所提到的,外層查詢的「每一次」查詢動作都需要引用內層查詢的資料。以上方例子而言,外層查詢的每一筆資料,都要等內層查詢「掃描整個資料表」並作比對和計數,因此 830 筆資料每一筆都要重複掃描整個資料表 830 次,所耗用的時間也因此爆增至 170 ms。<br />
<br />
若您用相同的寫法,去查詢 AdventureWorks 資料庫中,有 31,465 筆資料的 Sales.SalesOrderHeader 資料表,用 ROW_NUMBER 函數要 677 ms,還不到 1 秒鐘;但用子查詢的話,居然要高達 225,735 ms,將近快 4 分鐘的時間。<br />
<br />
雖然這是較極端的範例,但由此可知子查詢的撰寫,在使用上不可不慎,尤其是「關聯子查詢」。程式員在程式開發初期、資料量還很少時感受不到此種 SQL 語法的重大陷阱;但等到系統上線幾個月或一兩年後,可能就會有反應遲緩的現象。<br />
<br />
<br />
<hr /><strong>7、其他查詢技巧</strong><br />
<ul><li>DISTINCT、ORDER BY 語法,會讓資料庫做額外的計算。此外聯集的使用,若沒有要剔除重複資料的需求,使用 UNION ALL 會比 UNION 更佳,因為後者會加入類似 DISTINCT 的演算法。</li>
<li>在 SQL Server 2005 版本中,存取資料庫物件時,最好明確指定該物件的「結構描述 (Schema)」,也就是使用兩節式名稱。否則若呼叫者的預設 Schema 不是 dbo,則 SQL Server 在執行時,會先尋找該使用者預設 Schema 所搭配的物件,找不到的話才會轉而使用預設的 dbo,會多耗費尋找的時間。例如若要執行一個叫做 dbo.mySP1 的 Stored Procedure,應使用以下的兩節式名稱:<br />
<pre class="sql:nogutter:nocontrols" name="code">EXEC dbo.mySP1
</pre></li>
</ul><br />
<br />
<hr /><strong>8、儘可能用 Stored Procedure 取代前端應用程式直接存取資料表</strong><br />
<br />
Stored Procedure 除了經過事先編譯、效能較好以外,亦可節省 SQL 陳述式傳遞的頻寬,也方便商業邏輯的重複使用。再搭配自訂函數和 View 的使用,將來若要修改資料表結構、重新切割或反正規化時亦較方便。<br />
<br />
<br />
<hr /><strong>9、儘可能在資料來源層,就先過濾資料</strong><br />
<br />
使用 SELECT 語法時,儘量避免傳回所有的資料至前端而不設定 WHERE 等過濾條件。雖然 ASP.NET 中 SqlDataSource、ObjectDataSource 控制項的 FilterExpression 可再做篩選,GridView 控制項的 SortExpression 可再做排序,但會多消耗掉資料庫的系統資源、Web server 的記憶體和網路頻寬。最好還是在資料庫和資料來源層,就先用 SQL 條件式篩選出所要的資料。<br />
<br />
<br />
<hr /><strong>結論:</strong><br />
<br />
本文的觀念,不管是寫 SQL statement、Stored Procedure、自訂函數或 View 皆然。本文只是挑出程式員較容易犯的 SQL 語法效能問題,以期能在短時間瀏覽過本文後,在寫 ADO.NET 程式時能修正以往隨興的 SQL 撰寫習慣。文中提到的幾點,只不過是 SQL 語法效能議題的入門篇。後續有時間的話,版工會再補充在本帖的回應留言,或另開新主題。<br />
<br />
<br />
<hr /><strong>參考文件:</strong><br />
<br />
[1] SQL查詢最佳化 (網際烏托邦):<br />
<a target="_blank" href="http://www.ithome.com.tw/plog/index.php?op=ViewArticle&articleId=5421&blogId=620">http://www.ithome.com.tw/plog/index.php?op=ViewArticle&articleId=5421&blogId=620</a><br />
<br />
<strong>參考書籍:</strong><br />
<br />
[2] SQL Server 2005 Performance Tuning 效能調校:<br />
作者:胡百敬、姚巧枚、劉承修<br />
出版社:悅知出版社<br />
<a target="_blank" href="http://tlsj.tenlong.com.tw/WebModule/BookSearch/bookSearchViewAction.do?isbn=9789866761225&sid=41966">http://tlsj.tenlong.com.tw/WebModule/BookSearch/bookSearchViewAction.do?isbn=9789866761225&sid=41966</a><br />
<br />
[3] SQL Server 2005 完全實戰:<br />
作者:章立民<br />
出版社:碁峰出版社<br />
<br />
<strong>相關文件:</strong><br />
<br />
[4] 臺大醫院資料庫分割疏失,系統幾近停擺 (ITHome):<br />
<a target="_blank" href="http://www.ithome.com.tw/itadm/article.php?c=43597">http://www.ithome.com.tw/itadm/article.php?c=43597</a><br />
<br />
[5] 當DataGrid遇見100萬筆資料:<br />
<a target="_blank" href="http://blog.sina.com.tw/4907/article.php?pbgid=4907&entryid=3921">http://blog.sina.com.tw/4907/article.php?pbgid=4907&entryid=3921</a><br />
<br />
[6] ASP.NET 2.0 GridView 範例集 - 「4-8-4、GridView的效能」:<br />
<a target="_blank" href="http://blog.csdn.net/Code6421/archive/2007/12/22/1958167.aspx">http://blog.csdn.net/Code6421/archive/2007/12/22/1958167.aspx</a><br />
<br />
[7] 有關開啟頁面時,一次載入數千筆資料的效能問題:<br />
<a target="_blank" href="http://www.blueshop.com.tw:80/board/show.asp?subcde=BRD200709141021458MV">http://www.blueshop.com.tw:80/board/show.asp?subcde=BRD200709141021458MV</a><br />
<br />
Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-118903930618998982013-08-07T00:01:00.000+08:002013-08-17T15:01:12.638+08:00[MySQL] 計算漢明距離之前寫了用 PHP 計算圖片的 hash code,接著要怎麼去搜尋相似的圖片呢?<br />
<br />
資料庫是一個很好的選擇,首先建立一個簡單的 Table:<br />
<pre class="sql:nogutter:nocontrols" name="code">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;
</pre><br />
<br />
再來要怎麼將 hash code 寫進資料表呢?我們可以利用 b'010101' 的 bit 表示語法將 hash code 64位元字串存進 BIGINT 裡面。<br />
<pre class="sql:nogutter:nocontrols" name="code">INSERT INTO image_hash_info (filepath, hash)
VALUES('/home/jax/12.jpg', b'10100111...');
</pre><br />
<br />
資料已經備齊了,最重要的就是怎麼計算漢明距離了,MySQL 提供了 <code style="color: red; font-weight: bold;">BIT_COUNT()</code> 的函數,可以計算數字中位元 1 的個數,所以剩下的就是 XOR 的運算了。<br />
<pre class="sql:nogutter:nocontrols" name="code">SELECT BIT_COUNT(29), BIT_COUNT(b'101010');
-- -> 4, 3
</pre><br />
<br />
找尋與某一個 hash code 相似的圖片<br />
<pre class="sql:nogutter:nocontrols" name="code">SELECT * FROM image_hash
WHERE BIT_COUNT(hash ^ b'1010011110...') <= 10
</pre><br />
<br />
參考來源:<br />
<a target="_blank" href="http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html">MySQL 5.0 Bit Functions</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com6tag:blogger.com,1999:blog-5946530704742130970.post-41852202603771854022013-06-05T22:58:00.000+08:002013-06-10T22:21:55.621+08:00[轉載][MySQL] 修復工具 mysqlcheck轉載自:<a target="_blank" href="http://saplingidea.iteye.com/blog/482055">mysql修复工具mysqlcheck</a><br />
<br />
<pre class="sh" name="code">mysqlcheck -a -c -o -r -m --all-databases -uroot -p
</pre><br />
即可最佳化所有db<br />
參數含意:<br />
<ul><li>-a = Analyse given tables.</li>
<li>-c = Check table for errors</li>
<li>-o = Optimise table</li>
<li>-r = Can fix almost anything except unique keys that aren't unique</li>
<li>-m = --medium-check</li>
</ul><br />
<br />
mysqlcheck 客戶端可以檢查和修復 MyISAM 資料表。它還可以優化和分析資料表。<br />
<br />
mysqlcheck 的功能類似 myisamchk,但其工作不同。主要差別是當 mysqld 服務器在運行時必須使用mysqlcheck,<strong>而 myisamchk 應用於服務器沒有運行時</strong>。使用 <strong>mysqlcheck 的好處是不需要停止服務器來檢查或修復資料表</strong>。使用 <strong>myisamchk 修復失敗是不可逆的</strong>。<br />
<br />
Mysqlcheck 為用戶提供了一種方便的使用 SQL 語句 <strong>CHECK TABLE、REPAIR TABLE、ANALYZE TABLE和OPTIMIZE TABLE</strong> 的方式。它確定在要執行的操作中使用使用哪個語句,然後將語句發送到要執行的服務器上。<br />
<br />
<br />
有3種方式來調用mysqlcheck:<br />
shell> mysqlcheck [options] db_name [tbl_name ...]<br />
shell> mysqlcheck [options] --databases db_name ...<br />
shell> mysqlcheck [options] --all-databases<br />
<br />
如果沒有指定任何資料表或使用 --databases 或 --all-databases 選項,則檢查整個數據庫。<br />
<br />
同其它客戶端比較,mysqlcheck 有一個特殊特性。重新命名二進制可以更改檢查資料表的默認行為(--check)。如果你想要一個工具默認可以修復資料表的工具,只需要將 mysqlcheck 重新復制為 mysqlrepair,或者使用一個符號鏈接 mysqlrepair 鏈接 mysqlcheck。如果調用 mysqlrepair,可按照命令修復資料表。<br />
<br />
<br />
下面的名可用來更改 mysqlcheck 的默認行為:<br />
<table class="table_list"><tr><td>mysqlrepair</td><td>默認選項為 --repair</td></tr>
<tr><td>mysqlanalyze</td><td>默認選項為 --analyze</td></tr>
<tr><td>mysqloptimize</td><td>默認選項為 --optimize</td></tr>
</table><br />
<br />
mysqlcheck支持下面的選項:<br />
<dl><dt>-A, --all-databases</dt>
<dd>檢查所有數據庫中的所有資料表。與使用---database選項相同,在命令行中命名所有數據庫。</dd>
<dt>-a, --analyze</dt>
<dd>分析資料表。</dd>
<dt>-1, --all-in-1</dt>
<dd>不是為每個表發出一個語句,而是為命名數據庫中待處理的所有表的每個數據庫執行一個語句。</dd>
<dt>--auto-repair</dt>
<dd>如果某個被檢查的表破壞了,自動修復它。檢查完所有表後自動進行所有需要的修復。</dd>
<dt>--character-sets-dir=name</dt>
<dd>字符集的安裝目錄。參見5.10.1節,"數據和排序用字符集"。</dd>
<dt>-c, --check</dt>
<dd>檢查資料表的錯誤。</dd>
<dt>-C, --check-only-changed</dt>
<dd>只檢查上次檢查以來已經更改的或沒有正確關閉的資料表。</dd>
<dt>--compress</dt>
<dd>壓縮在客戶端和服務器之間發送的所有信息(如果二者均支持壓縮)。</dd>
<dt>-B, --databases</dt>
<dd>處理數據庫中命名的所有資料表。使用該選項,所有字名參量被看作數據庫名,而不是資料表名稱。</dd>
<dt>-#, --debug[=#]</dt>
<dd>寫調試日誌。 debug_options字符串通常為'd:t:o,file_name'。</dd>
<dt>--default-character-set=name</dt>
<dd>使用charsetas默認字符集。參見5.10.1節,"數據和排序用字符集"。</dd>
<dt>-F, --fast</dt>
<dd>只檢查沒有正確關閉的資料表。</dd>
<dt>-f, --force</dt>
<dd>即使出現SQL錯誤也繼續。</dd>
<dt>-e, --extended</dt>
<dd>如果你正使用該選項來檢查資料表,可以確保它們100%地一致,但需要很長的時間。 如果你正使用該選項來修復資料表,則運行擴展修復,不但執行的時間很長,而且還會產生大量的垃圾行!</dd>
<dt>-?, --help</dt>
<dd>顯示幫助消息並退出。</dd>
<dt>-h, --host=name</dt>
<dd>連接給定主機上的MySQL服務器。</dd>
<dt>-m, --medium-check</dt>
<dd>執行比--extended操作更快的檢查。只能發現99.99%的錯誤,在大多數情況下這已經足夠了。</dd>
<dt>-o, --optimize</dt>
<dd>優化資料表。</dd>
<dt>-p, --password[=name]</dt>
<dd>當連接服務器時使用的密碼。如果使用短選項形式(-p),選項和密碼之間不能有空格。 如果在命令行中--password或-p選項後面沒有密碼值,則提示輸入一個密碼。</dd>
<dt>-P, --port=#</dt>
<dd>用於連接的TCP/IP端口號。</dd>
<dt>--protocol=name</dt>
<dd>使用的連接協議。</dd>
<dt>-q, --quick</dt>
<dd>如果你正使用該選項在檢查資料表,它防止掃描行以檢查錯誤鏈接的檢查。這是最快的檢查方法。 如果你正使用該選項在修復資料表,它嘗試只修復索引樹。這是最快的修復方法。</dd>
<dt>-r, --repair</dt>
<dd>執行可以修復大部分問題的修復,只是唯一值不唯一時不能修復。</dd>
<dt>-s, --silent</dt>
<dd>沉默模式。只打印錯誤消息。</dd>
<dt>-S, --socket=name</dt>
<dd>用於連接的套接字文件。</dd>
<dt>--tables</dt>
<dd>覆蓋---database或-B選項。選項後面的所有參量被視為資料表名稱。</dd>
<dt>-u, --user=name</dt>
<dd>當連接服務器時使用的MySQL用戶名。</dd>
<dt>-v, --verbose</dt>
<dd>冗長模式。打印關於各階段程序操作的信息。</dd>
<dt>-V, --version</dt>
<dd>顯示版本信息並退出。</dd> </dl>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-59839398578621841702012-09-05T11:46:00.000+08:002013-06-10T22:24:47.475+08:00[轉載] [MySQL] QueryCache原理轉載自:<a target="_blank" href="http://calos-tw.blogspot.tw/2012/09/mysql-querycache.html">PHP 程式 學習 筆記本 [Mysql]QueryCache原理</a><br />
<br />
<strong>原理 </strong><br />
QueryCache(下面簡稱QC)是根據SQL語句來cache的。一個SQL查詢如果以select開頭,那麼MySQL服務器將嘗試對其使用QC。每個Cache都是以SQL文本作為key來存的。在應用QC之前,SQL文本不會被作任何處理。也就是說,兩個SQL語句,只要相差哪怕是一個字符(例如大小寫不一樣;多一個空格等),那麼這兩個SQL將使用不同的一個CACHE。 <br />
<br />
不過SQL文本有可能會被客戶端做一些處理。例如在官方的命令行客戶端裡,在發送SQL給服務器之前,會做如下處理: <br />
<ul><li>過濾所有註釋</li>
<li>去掉SQL文本前後的空格,TAB等字符。注意,是文本前面和後面的。中間的不會被去掉。</li>
</ul><br />
<br />
下面的三條SQL裡,因為SELECT大小寫的關係,最後一條和其他兩條在QC裡肯定是用的不一樣的存儲位置。而第一條和第二條,區別在於後者有個註釋,在不同客戶端,會有不一樣的結果。所以,保險起見,請儘量不要使用動態的註釋。在PHP的mysql擴展裡,SQL的註釋是不會被去掉的。也就是三條SQL會被存儲在三個不同的緩存裡,<b>雖然它們的結果都是一樣的</b>。 <br />
<div style="color: blue;">SELECT * FROM people where name='surfchen';</div><div style="color: blue;">SELECT * FROM people where /*hey~*/name='surfchen';</div><div style="color: blue;">SELECT * FROM people where name='surfchen';</div><br />
<br />
目前只有select語句會被cache,其他類似show,use的語句則不會被cache。 <br />
<br />
因為QC是如此前端,如此簡單的一個緩存系統,所以如果一個表被更新,那麼和這個表相關的SQL的所有QC都會被失效。假設一個聯合查詢裡涉及到了表A和表B,如果表A或者表B的其中一個被更新(update或者delete),這個查詢的QC將會失效。 <br />
<br />
也就是說,如果一個表被頻繁更新,那麼就要考慮清楚究竟是否應該對相關的一些SQL進行QC了。一個被頻繁更新的表如果被應用了QC,可能會加重數據庫的負擔,而不是減輕負擔。我一般的做法是默認打開QC,而對一些涉及頻繁更新的表的SQL語句加上<b style="color: red;">SQL_NO_CACHE</b>關鍵詞來對其禁用CACHE。這樣可以儘可能避免不必要的內存操作,儘可能保持內存的連續性。 <br />
<br />
那些查詢很分散的SQL語句,也不應該使用QC。例如用來查詢用戶和密碼的語句——「select pass from user where name='surfchen'」。這樣的語句,在一個系統裡,很有可能只在一個用戶登陸的時候被使用。每個用戶的登陸所用到的查詢,都是不一樣的SQL文本,QC在這裡就幾乎不起作用了,因為緩存的數據幾乎是不會被用到的,它們只會在內存裡佔地方。 <br />
<br />
<br />
<strong>存儲塊 </strong><br />
在本節裡「存儲塊」和「block」是同一個意思 <br />
QC緩存一個查詢結果的時候,一般情況下不是一次性地分配足夠多的內存來緩存結果的。而是在查詢結果獲得的過程中,逐塊存儲。當一個存儲塊被填滿之後,一個新的存儲塊將會被創建,並分配內存(allocate)。單個存儲塊的內存分配大小通過<b><span style="color: red;">query_cache_min_res_unit</span></b>參數控制,默認為<b><span style="color: blue;">4KB</span></b>。最後一個存儲塊,如果不能被全部利用,那麼沒使用的內存將會被釋放。如果被緩存的結果很大,那麼會可能會導致分配內存操作太頻繁,系統系能也隨之下降;而如果被緩存的結果都很小,那麼可能會導致內存碎片過多,這些碎片如果太小,就很有可能不能再被分配使用。 <br />
<br />
除了查詢結果需要存儲塊之外,每個SQL文本也需要一個存儲塊,而涉及到的表也需要一個存儲塊(表的存儲塊是所有線程共享的,每個表只需要一個存儲塊)。存儲塊總數量=查詢結果數量*2+涉及的數據庫表數量。也就是說,第一個緩存生成的時候,至少需要三個存儲塊:表信息存儲塊,SQL文本存儲塊,查詢結果存儲塊。而第二個查詢如果用的是同一個表,那麼最少只需要兩個存儲塊:SQL文本存儲塊,查詢結果存儲塊。 <br />
<br />
通過觀察<span style="color: red;">Qcache_queries_in_cache</span>和<span style="color: red;">Qcache_total_blocks</span>可以知道平均每個緩存結果占用的存儲塊。它們的比例如果接近<b style="color: blue;">1:2</b>,則說明當前的query_cache_min_res_unit參數已經足夠大了。如果Qcache_total_blocks比Qcache_queries_in_cache多很多,則需要增加query_cache_min_res_unit的大小。 <br />
<br />
<b style="color: red;">Qcache_queries_in_cache</b> * <b style="color: red;">query_cache_min_res_unit</b>(sql文本和表信息所在的block佔用的內存很小,可以忽略)如果遠遠大於<b style="color: red;">query_cache_size-Qcache_free_memory</b>,那麼可以嘗試<b style="color: red;"><span style="color: blue;">減小</span>query_cache_min_res_unit</b>的值。 <br />
<br />
<br />
<strong>調整大小 </strong><br />
如果<b style="color: red;">Qcache_lowmem_prunes</b>增長迅速,意味著很多緩存因為內存不夠而被釋放,而不是因為相關表被更新。嘗試<b style="color: red;"><span style="color: blue;">加大</span>query_cache_size</b>,儘量使Qcache_lowmem_prunes零增長。 <br />
<br />
<br />
<strong>啟動參數</strong> <br />
show variables like 'query_cache%' 可以看到這些信息。 <br />
<b style="color: red;">query_cache_limit</b>:如果單個查詢結果大於這個值,則不Cache <br />
<b style="color: red;">query_cache_size</b>:分配給QC的內存。如果設為0,則相當于禁用QC。要注意QC必須使用大約40KB來存儲它的結構,如果設定小於40KB,則相當于禁用QC。QC存儲的最小單位是1024 byte,所以如果你設定了一個不是1024的倍數的值,這個值會被四捨五入到最接近當前值的等於1024的倍數的值。 <br />
<b style="color: red;">query_cache_type</b>:<br />
<span style="color: blue;">0 完全禁止QC</span>,不受SQL語句控制(另外可能要注意的是,即使這裡禁用,上面一個參數所設定的內存大小還是會被分配);<span style="color: blue;"></span><br />
<span style="color: blue;">1啟用QC,可以在SQL語句使用SQL_NO_CACHE禁用</span>;<span style="color: blue;"></span><br />
<span style="color: blue;">2可以在SQL語句使用SQL_CACHE啟用。</span><br />
<span style="color: blue;"></span> <br />
<b style="color: red;">query_cache_min_res_unit</b>:每次給QC結果分配內存的大小 <br />
<br />
<br />
<strong>狀態</strong> <br />
show status like 'Qcache%' 可以看到這些信息。 <br />
<b style="color: red;">Qcache_free_blocks</b>:當一個表被更新之後,和它相關的cache blocks將被free。但是這個block依然可能存在隊列中,除非是在隊列的尾部。這些blocks將會被統計到這個值來。可以用<b style="color: blue;">FLUSH QUERY CACHE</b>語句來清空free blocks。 <br />
<br />
其他幾個狀態變量的意義:<br />
<b style="color: red;">Qcache_free_memory</b> 表示查詢緩存區現在還有多少的可用內存,如果很小,考慮增加query_cache_size <br />
<b style="color: red;">Qcache_hits</b> 表示查詢緩存區的命中個數,也就是直接從查詢緩存區作出響應處理的查詢個數<br />
<b style="color: red;">Qcache_inserts</b> 表示查詢緩存區此前總過緩存過多少條查詢命令的結果<br />
<b style="color: red;">Qcache_lowmem_prunes</b> 表示查詢緩存區已滿而從其中溢出和刪除的查詢結果的個數<br />
<b style="color: red;">Qcache_not_cached</b> 自mysql進程啟動起,沒有被cache的只讀查詢數量(包括select,show,use,desc等) <br />
<b style="color: red;">Qcache_queries_in_cache</b> 當前被cache的SQL數量 <br />
<b style="color: red;">Qcache_total_blocks</b>:在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文本,查詢結果。 <br />
<br />
<b>優化提示:</b>如果Qcache_lowmem_prunes 值比較大,表示查詢緩存區大小設置太小,需要增大。如果Qcache_free_blocks 較多,表示內存碎片較多,需要清理,flush query cache<br />
<br />
根據我看的 《High Performance MySQL》中所述,關於query_cache_min_res_unit大小的調優,書中給出了一個計算公式,可以供調優設置參考:<br />
<b>query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache</b><br />
<br />
還要注意一點的是,FLUSH QUERY CACHE 命令可以用來整理查詢緩存區的碎片,改善內存使用狀況,但不會清理查詢緩存區的內容,這個要和RESET QUERY CACHE相區別,不要混淆,後者才是清除查詢緩存區中<br />
的所有的內容。Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-51102446211992833672012-04-11T21:09:00.001+08:002013-08-06T22:42:04.178+08:00[MySQL] 整數型態的大小<table class="table_list" cellspacing="0" cellpadding="4" border="1"><tr class="header"><th>類型</th><th>Byte</th><th>肯定位數</th><th>最大位數</th><th>最大值</th><th>無符號最大值</th></tr>
<tr><td><b>TINYINT</b></td><td>1</td><td>2</td><td>3</td><td>127</td><td>255</td></tr>
<tr><td><b>SMALLINT</b></td><td>2</td><td>4</td><td>5</td><td>32767</td><td>65535</td></tr>
<tr><td><b>MEDIUMINT</b></td><td>3</td><td>7</td><td>8</td><td>8388607</td><td>16777215</td></tr>
<tr><td><b>INT</b></td><td>4</td><td>9</td><td>10</td><td>2147483647</td><td>4294967296</td></tr>
<tr><td><b>BIGINT</b></td><td>8</td><td>19</td><td>20</td><td>9223372036854775807</td><td>18446744073709551615</td></tr>
</table>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-61697428906592415472012-03-14T14:48:00.003+08:002013-06-10T22:38:24.354+08:00[Ubuntu 11] 安裝 Redmine 與 SVN<strong>安裝 LAMP & Redmine</strong><br />
<pre class="sh" name="code"># 安裝 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
</pre><br />
<br />
<strong>安裝 Redmine 套件</strong><br />
<pre class="sh" name="code">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
</pre><br />
<br />
<strong>建立SVN庫</strong><br />
<pre class="sh" name="code">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
</pre><br />
<br />
<strong>建立SVN認證時需要的DB使用者(redmine_svn_auth)</strong><br />
<pre class="sql" name="code">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';
</pre><br />
<br />
<strong>設定 Apache2 Site</strong> <br />
<pre class="sh" name="code">cd /etc/apache2/sites-available/
cp default redmine_svn
vim redmine_svn
</pre><br />
<pre class="xml" name="code"><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>
</pre><br />
<br />
<strong>重新啟動 Apache</strong> <br />
<pre class="sh" name="code">a2dissite default
a2ensite redmine_svn
service apache2 restart
</pre><br />
<br />
<strong>測試網址</strong> <br />
<ul><li>Redmine: <a target="_blank" href="http://localhost/">http://localhost/</a></li>
<li>SVN: <a target="_blank" href="http://localhost/svn/team1/">http://localhost/svn/team1/</a></li>
</ul><br />
<br />
參考來源:<br />
<a target="_blank" href="http://blog.longwin.com.tw/2011/03/redmine-debian-ubuntu-linux-2011/">將 Redmine 安裝於 Debian、Ubuntu Linux</a><br />
<a target="_blank" href="http://www.redmine.org/projects/redmine/wiki/HowTo_Install_Redmine_in_Ubuntu">Ubuntu 10.04 using Passenger</a><br />
<a target="_blank" href="http://www.pigo.idv.tw/archives/851">svn 使用和 redmine 相同帳號進行認證</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com4tag:blogger.com,1999:blog-5946530704742130970.post-7912197072555984552012-03-07T14:06:00.002+08:002013-06-10T22:40:22.436+08:00[Ubuntu] 安裝 Oracle Client 與 PDO_OCI於 <a target="_blank" href="http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html">Oracle Database Instant Client</a> 下載 Client/SDK <br />
(Version 10.2.0.4 Instant Client Package - Basic, Instant Client Package - SDK)<br />
<strong>oracle-instantclient-basic-10.2.0.4-1.i386.rpm</strong><br />
<strong>oracle-instantclient-devel-10.2.0.4-1.i386.rpm</strong><br />
<br />
<pre class="sh" name="code"># 安裝套件轉換器
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
</pre><br />
參考來源:<br />
<a target="_blank" href="http://blog.longwin.com.tw/2008/06/debian_php_oracle_ext_pdo_oci_2008/">Debian 安裝設定 PHP 連 Oracle extension 使用 PDO(PDO_OCI)</a><br />
<a target="_blank" href="https://help.ubuntu.com/community/Oracle%20Instant%20Client">Oracle Instant Client</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-40506982560465812332011-12-16T15:04:00.003+08:002023-02-25T21:41:23.871+08:00Sphinx 增量索引的方法之前有寫過一篇 <a href="http://jax-work-archive.blogspot.com/2009/05/mysql-sphinx.html">MySQL 全文檢索引擎 - Sphinx</a> 的文章,最近又把它拿出來用了,不過當時即時更新索引的問題,如今則找到解決的方法了,透過更新增量索引的方式達到即時更新。<br />
<br />
簡單的說就是利用兩個索引表的合併查詢來做到,一個是完整的索引表,一個是針對當日資料變動的增量索引。<br />
<br />
部分的設定檔如下:<br />
<pre class="cfg" name="code"># ...
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
}
# ...
</pre><br />
<br />
<strong>建立 SphinxSE 表</strong><br />
特別注意在 <strong>CONNECTION</strong> 中所指定索引表為 people。<br />
<pre class="sql" name="code">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搜尋連接介面';
</pre><br />
<br />
<strong>SQL 的查詢測試</strong><br />
這裡使用 INNER JOIN 方式作查詢,這樣對於刪除資料的變動,就不會出現在查詢結果中。<br />
<pre class="sql" name="code">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'
</pre><br />
<br />
<strong>透過 PHP 更新增量索引</strong><br />
在資料 INSERT 或 UPDATE 時,去呼叫索引更新,這樣在第一時間就可以更新索引。 <br />
<pre class="sql" name="code">shell_exec('sudo indexer --quiet --rotate people_delta 2>&1');
</pre><br />
如果 Server 是 Ubuntu,請記得在 <strong>vim /etc/sudoers</strong> 中賦予 apache 使用 indexer 的權限。<br />
<code>www-data ALL=(root) NOPASSWD: /usr/bin/indexer</code><br />
<br />
<br />
<strong>在 crontab 中加上排程</strong><br />
利用離峰時間來更新完整的索引表,由於刪除資料的變動需要更新完整索引表才有辦法移除。<br />
<code>00 00 * * * indexer --quiet --rotate --all > /dev/null 2>&1</code>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-47356931129022017612011-12-16T13:01:00.003+08:002023-02-25T21:46:11.718+08:00[轉載][MySQL] Replication option binlog_format轉載自:<a target="_blank" href="http://wangwei.cao.blog.163.com/blog/static/1023625262010424113726914/">MYSQL5.1复制参数binlog_format</a><br />
<br />
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 模式是默認的。 <br />
<br />
<br />
在運行時可以動態低改變binlog的格式,除了以下幾種情況: <br />
<ol><li>存儲過程或者觸發器中間 </li>
<li>啟用了NDB </li>
<li>當前會話試用RBR 模式,並且已打開了臨時表 </li>
</ol><br />
<br />
如果binlog採用了MIXED 模式,那麼在以下幾種情況下會自動將binlog的模式由SBR 模式改成RBR 模式。 <br />
<ol><li>當DML語句更新一個NDB表時 </li>
<li>當函數中包含UUID() 時 </li>
<li>2個及以上包含AUTO_INCREMENT 字段的表被更新時 </li>
<li>行任何INSERT DELAYED 語句時 </li>
<li>用UDF 時 </li>
<li>視圖中必須要求使用RBR 時,例如創建視圖是使用了UUID() 函數 </li>
</ol><br />
<br />
設定主從復制模式的方法非常簡單,只要在以前設定複製配置的基礎上,再加一個參數: <br />
<pre class="cfg" name="code">binlog_format="STATEMENT"
#binlog_format="ROW"
#binlog_format="MIXED"
</pre><br />
<br />
當然了,也可以在運行時動態修改binlog的格式。 例如 <br />
<pre class="sh" name="code">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';
</pre><br />
<br />
現在來比較以下SBR 和RBR 2中模式各自的優缺點<br />
<br />
<strong>SBR 的優點:</strong><br />
<ol><li>歷史悠久,技術成熟 </li>
<li>binlog文件較小 </li>
<li>binlog中包含了所有數據庫更改信息,可以據此來審核數據庫的安全等情況 </li>
<li>binlog可以用於實時的還原,而不僅僅用於復制 </li>
<li>主從版本可以不一樣,從服務器版本可以比主服務器版本高 </li>
</ol><br />
<strong>SBR 的缺點:</strong><br />
<ol><li>不是所有的UPDATE語句都能被複製,尤其是包含不確定操作的時候。 </li>
<li>調用具有不確定因素的UDF 時復制也可能出問題 </li>
<li>使用以下函數的語句也無法被複製:<ul><li>LOAD_FILE()</li>
<li>UUID()</li>
<li>USER()</li>
<li>FOUND_ROWS()</li>
<li>SYSDATE() (除非啟動時啟用了--sysdate-is-now 選項)</li>
</ul></li>
<li>INSERT ... SELECT 會產生比RBR 更多的行級鎖 </li>
<li>複製需要進行全表掃描(WHERE 語句中沒有使用到索引)的UPDATE 時,需要比RBR 請求更多的行級鎖 </li>
<li>對於有AUTO_INCREMENT 字段的InnoDB表而言,INSERT 語句會阻塞其他INSERT 語句 </li>
<li>對於一些複雜的語句,在從服務器上的耗資源情況會更嚴重,而RBR 模式下,只會對那個發生變化的記錄產生影響 </li>
<li>存儲函數(不是存儲過程)在被調用的同時也會執行一次NOW() 函數,這個可以說是壞事也可能是好事 </li>
<li>確定了的UDF 也需要在從服務器上執行 </li>
<li>數據表必須幾乎和主服務器保持一致才行,否則可能會導致複製出錯 </li>
<li>執行複雜語句如果出錯的話,會消耗更多資源 </li>
</ol><br />
<br />
<strong>RBR 的優點:</strong><br />
<ol><li>任何情況都可以被複製,這對複制來說是最安全可靠的 </li>
<li>和其他大多數數據庫系統的複制技術一樣 </li>
<li>多數情況下,從服務器上的表如果有主鍵的話,複製就會快了很多 </li>
<li>複製以下幾種語句時的行鎖更少:<ul><li>INSERT ... SELECT</li>
<li>包含AUTO_INCREMENT 字段的INSERT</li>
<li>沒有附帶條件或者並沒有修改很多記錄的UPDATE 或DELETE 語句</li>
</ul></li>
<li>執行INSERT,UPDATE,DELETE 語句時鎖更少 </li>
<li>從服務器上採用多線程來執行複製成為可能 </li>
</ol><br />
<strong>RBR 的缺點:</strong><br />
<ol><li> binlog 大了很多 </li>
<li>複雜的回滾時binlog 中會包含大量的數據 </li>
<li>主服務器上執行UPDATE 語句時,所有發生變化的記錄都會寫到binlog 中,而SBR 只會寫一次,這會導致頻繁發生binlog 的並發寫問題 </li>
<li>UDF 產生的大BLOB 值會導致複製變慢 </li>
<li>無法從binlog 中看到都複製了寫什麼語句 </li>
<li>當在非事務表上執行一段堆積的SQL語句時,最好採用SBR 模式,否則很容易導致主從服務器的數據不一致情況發生 </li>
</ol><br />
<br />
另外,針對系統庫 mysql 裡面的表發生變化時的處理規則如下: <br />
<ol><li>如果是採用INSERT,UPDATE,DELETE 直接操作表的情況,則日誌格式根據binlog_format 的設定而記錄 </li>
<li>如果是採用GRANT,REVOKE,SET PASSWORD 等管理語句來做的話,那麼無論如何都採用SBR 模式記錄 </li>
</ol><br />
注:採用RBR 模式後,能解決很多原先出現的主鍵重複問題Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-66619390259319908362011-12-16T12:41:00.003+08:002023-02-26T14:13:52.756+08:00[MySQL] Replication Master to Slave<strong>Master上的設定</strong><br />
<br />
my.cnf<br />
<pre class="cfg" name="code"># 主機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
</pre><br />
進入Master mysql<br />
<code>mysql -u root -p</code><br />
<br />
建立同步連接帳號 db_sync,密碼12345,給 slave_host,<br />
<code>mysql> GRANT REPLICATION SLAVE ON *.* TO 'db_sync'</code><code>'slave_host' IDENTIFIED BY '12345';</code><br />
<br />
鎖定資料庫寫入<br />
<code>mysql> FLUSH TABLES WITH READ LOCK;</code><br />
<br />
取得 MASTER 上的 Log File 名稱與 Position 編號,這個編號代表當前的 Log 戳記<br />
<code>mysql> SHOW MASTER STATUS\G;</code><br />
<table><tr><th style="text-align:right;">File:</th><th style="text-align:left;">mysql-bin.000010</th></tr>
<tr><th style="text-align:right;">Position:</th><th style="text-align:left;">106</th></tr>
<tr><td style="text-align:right;">Binlog_Do_DB:</td><td>test,mydb</td></tr>
<tr><td style="text-align:right;">Binlog_Ignore_DB:</td><td></td></tr>
</table><br />
離開 mysql<br />
<code>mysql> quit</code><br />
<br />
匯出 MASTER 上的資料 <br />
<code>mysqldump -uroot -p --flush-logs --opt --master-data test > test.sql</code><br />
<br />
解除鎖定<br />
<code>mysql -u root -p -e UNLOCK TABLES;</code><br />
<br />
<br />
<br />
<strong>Slave上的設定</strong><br />
<br />
my.cnf<br />
<pre class="cfg" name="code"># 主機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
</pre><br />
匯入資料<br />
<code>mysql -uroot -p test < test.sql</code><br />
<br />
進入 Slave mysql<br />
<code>mysql -uroot -p</code><br />
<br />
停止跟清除之前的 SLAVE 連接 <br />
<code>mysql> STOP SLAVE; RESET SLAVE;</code><br />
<br />
設定Slave 與 Master 的連接<br />
<pre class="sh" name="code">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;
</pre><br />
啟動 SLAVE 的連接 <br />
<code>mysql> START SLAVE;</code><br />
<br />
察看連接狀態<br />
<code>mysql> SHOW SLAVE STATUS\G;</code><br />
<table><tr><td style="text-align:right;">Slave_IO_State:</td><td>Connecting to master</td></tr>
<tr><td style="text-align:right;">Master_Host:</td><td>master_host</td></tr>
<tr><td style="text-align:right;">Master_User:</td><td>db_sync</td></tr>
<tr><td style="text-align:right;">Master_Port:</td><td>3306</td></tr>
<tr><td style="text-align:right;">Connect_Retry:</td><td>60</td></tr>
<tr><td style="text-align:right;">Master_Log_File:</td><td>mysql-bin.000011</td></tr>
<tr><td style="text-align:right;">Read_Master_Log_Pos:</td><td>106</td></tr>
<tr><td style="text-align:right;">Relay_Log_File:</td><td>master_host-relay-bin.000011</td></tr>
<tr><td style="text-align:right;">Relay_Log_Pos:</td><td>4</td></tr>
<tr><td style="text-align:right;">Relay_Master_Log_File:</td><td>mysql-bin.000011</td></tr>
<tr><th style="text-align:right;">Slave_IO_Running:</th><th style="text-align: left;">Yes</th></tr>
<tr><th style="text-align:right;">Slave_SQL_Running:</th><th style="text-align: left;">Yes</th></tr>
<tr><td style="text-align:right;">Replicate_Do_DB:</td><td></td></tr>
<tr><td style="text-align:right;">Replicate_Ignore_DB:</td><td></td></tr>
<tr><td style="text-align:right;">Replicate_Do_Table:</td><td></td></tr>
<tr><td style="text-align:right;">Replicate_Ignore_Table:</td><td></td></tr>
<tr><td style="text-align:right;">Replicate_Wild_Do_Table:</td><td>test.%,mydb.category,mydb.blog</td></tr>
<tr><td style="text-align:right;">Replicate_Wild_Ignore_Table:</td><td></td></tr>
<tr><td style="text-align:right;">Last_Errno:</td><td>0</td></tr>
<tr><td style="text-align:right;">Last_Error:</td><td></td></tr>
<tr><td style="text-align:right;">Skip_Counter:</td><td>0</td></tr>
<tr><td style="text-align:right;">Exec_Master_Log_Pos:</td><td>106</td></tr>
<tr><td style="text-align:right;">Relay_Log_Space:</td><td>106</td></tr>
<tr><td style="text-align:right;">Until_Condition:</td><td>None</td></tr>
<tr><td style="text-align:right;">Until_Log_File:</td></tr>
<tr><td style="text-align:right;">Until_Log_Pos:</td><td>0</td></tr>
<tr><td style="text-align:right;">Master_SSL_Allowed:</td><td>No</td></tr>
<tr><td style="text-align:right;">Master_SSL_CA_File:</td><td></td></tr>
<tr><td style="text-align:right;">Master_SSL_CA_Path:</td><td></td></tr>
<tr><td style="text-align:right;">Master_SSL_Cert:</td><td></td></tr>
<tr><td style="text-align:right;">Master_SSL_Cipher:</td><td></td></tr>
<tr><td style="text-align:right;">Master_SSL_Key:</td><td></td></tr>
<tr><th style="text-align:right;">Seconds_Behind_Master:</th><th style="text-align: left;">0</th></tr>
</table><br />
接著到 Master 上新增一筆資料測試看看吧!<br />
<br />
<br />
參考文章:<br />
<a target="_blank" href="http://www.d5s.cn/archives/95">MYSQL 主从服务器配置</a><br />
<a target="_blank" href="http://www.ooso.net/archives/547">mysql主从同步快速设置</a><br />
<a target="_blank" href="http://blog.longwin.com.tw/2008/03/mysql_replication_master_slave_set_2008/">MySQL 設定 Replication (Master – Slave)</a><br />
<a target="_blank" href="http://blog.chinaunix.net/space.php?uid=220350&do=blog&id=149202">多主一从mysql replication同步表的大胆尝试</a><br />
<a target="_blank" href="http://bbs.chinaunix.net/viewthread.php?tid=3566946">MySQL复制的数据库过滤若干问题</a><br />
<a target="_blank" href="http://wangwei.cao.blog.163.com/blog/static/1023625262010424113726914/">MYSQL5.1复制参数binlog_format</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-84007558425881008432011-11-23T00:38:00.005+08:002013-06-10T22:50:38.283+08:00[MySQL] EXPLAIN 分析報告的意義轉載自:<a target="_blank" href="http://chensh.loxa.edu.tw/php/C_7.php">PHP + MySQL 程式設計研習</a><br />
<br />
<ul><li><b>table</b><br />
表示所引用的表格名稱。</li>
<li><b>type</b><br />
表示查詢時的「聯結類型」(join type),以下依序是「最佳」至「最差」的各種類型:</li>
<ul><li><b>system</b><br />
表格中僅有一列。這是 const 類型的一個特例。</li>
<li><b>const</b><br />
表格中符合條件的只有一列。因為僅有一列,其值在後續的查詢中可被視為常數。</li>
<li><b>eq_ref</b><br />
表示在與其它表格的資料列結合時,此表格只有一列會被讀取。當 join 使用到資料表中的所有索引,並索引是 UNIQUE 或 PRIMARY KEY 時才會被用到。</li>
<li><b>ref</b><br />
表示在與其它表格的資料列結合時,此表格中所有符合的資料列都會被讀出來。這是當 join 只使用到部份鍵值(註),或此鍵非 UNIQUE 或 PRIMARY KEY 時才會用到(依照 join 的條件仍然無法選定單一目標列)。若因此符合的資料列數不多的話,它也算是一種不錯的「聯結類型」。<br />
<small>註:我們指定「A+B」欄位為 index key,但查詢時只用到「A」欄位。</small></li>
<li><b>range</b><br />
表示將在一定範圍內執行搜尋的動作。</li>
<li><b>index</b><br />
與 ALL 相同,但只有 index table 會被瀏覽。這通常比 ALL 快,因為 index table 通常比原始資料表來得小。</li>
<li><b>All</b><br />
表示這項查詢將對整個原始資料表瀏覽一遍,是最不好的類型。</li>
</ul><li><b>possible_keys</b><br />
表示 MySQL 能夠藉由哪些 index 來搜尋目標。</li>
<li><b>key</b><br />
表示 MySQL 實際藉由哪個 index 來搜尋目標。</li>
<li><b>key_len</b><br />
表示 MySQL 實際使用的 key 長度。若 index key 是由兩個欄位以上複合而成的話,您可以在此看見 MySQL 使用了 index 的多少部份。</li>
<li><b>ref</b><br />
表示哪個欄位(或常數)將被用來與 key 一起比對。</li>
<li><b>rows</b><br />
表示 MySQL 粗略估計在查詢的過程中,必須瀏覽的資料列數。</li>
<li><b>Extra</b><br />
顯示 MySQL 在解決這項查詢工作時的一些附加訊息。例如:「where used」表示 where 子句將會限制某些資料列的輸出。</li>
</ul>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-40659303160085745362011-11-23T00:14:00.001+08:002013-06-10T22:50:43.580+08:00[MySQL] TEMPORARY 臨時表最近為了解決一個效率太差的問題而找上這個東西,由於有一個資料表的筆數實在太多了,再加上好幾百個 query 都同時在同一個區段做查詢,剛好可以使用臨時表來處理這個問題,原本查詢需要的時間大概超過一分鐘,現在最多只要 10 秒,刷新後約 2 秒。<br />
<br />
MySQL 的臨時表只會存在在 Session 期間,當 Session 結束後就會自動刪除,不同的 Session 的表名稱並不會衝突,所以就算用一樣的名稱也不會出現錯誤,臨時表只能用在 MEMORY,MyISAM,MERGE,或者InnoDB 引擎上。<br />
<br />
<pre class="sql" name="code">-- 透過 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)
</pre><br />
參考資料:<br />
<a target="_blank" href="http://dev.mysql.com/doc/refman/5.1/en/create-table.html">MySQL CREATE TABLE Syntax</a><br />
<a target="_blank" href="http://homeserver.com.tw/mysql/mysql-%E8%87%A8%E6%99%82%E8%A1%A8temporary-table/">mysql temporary table簡介</a><br />
<a target="_blank" href="http://fcamel-life.blogspot.com/2010/11/mysql-temporary-table-on-disk.html">避免 MySQL 使用 temporary table on disk</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-28242200963367864992011-04-19T09:54:00.000+08:002012-02-12T23:32:39.084+08:00[Oracle] PDO-OCI 的 DSN 連接字串最近重裝我的開發環境,結果連 oracle 一直連不上,出現了以下幾種訊息,最後是因為我的 TNS listener 沒設好,或是我的 DSN 錯誤,整理出我試過的幾個 DSN 連接字串:<br />
<br />
ORA-12154: TNS:could not resolve service name<br />
ORA-06401: NETCMN: invalid driver designator<br />
ORA-12514: TNS:listener could not resolve SERVICE_NAME<br />
ORA-12505: TNS:listener could not resolve SID given in connect descriptor<br />
<br />
<pre class="php" name="code">$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";
</pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-21184720711783309322011-01-19T16:38:00.000+08:002012-02-12T23:32:39.086+08:00[Regedit] Oracle 日期格式及語言編碼設定上次用到時沒有記下來,結果忘記了,這次先把他記下來。<br />
<br />
<pre class="sh" name="code">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"
</pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-6679719024598504972011-01-19T02:36:00.001+08:002012-02-12T23:32:39.088+08:00使用批次檔開關 Oracle 服務因為在 NB 上灌了 Oracle 做開發用,可是每次開機都變得很慢,而且不是每天都會用到 Oracle,但常常開開關關服務真的很麻煩,索性找了一下批次檔開關服務的方法,寫了一個簡單的小工具,至少在處理這瑣碎的事可以快樂一點。<br />
<br />
<pre class="sh" name="code">@echo off
set /p STATUS=Oracle Service status to [start/stop] :
net %STATUS% "OracleMTSRecoveryService"
net %STATUS% "OracleOraHome92Agent"
net %STATUS% "OracleOraHome92TNSListener"
net %STATUS% "OracleServiceMYDB"
pause
</pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-4539080718719165652011-01-19T01:38:00.007+08:002012-02-12T23:32:57.040+08:00[MySQL] mysqldump & mysql command for UTF-8 database最近在 windows 上匯出/匯入資料庫時遇到 UTF-8 編碼問題,奇怪之前在 linux 上執行從未遇到的說,原來 default-character-set 是 latin1,所以在匯出/匯入時要指定成 UTF-8 就沒問題了。<br />
<br />
匯出資料庫<br />
<pre class="sh" name="code">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
</pre><br />
匯入資料庫<br />
<pre class="sh" name="code">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
</pre>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-41888335618211310102010-09-03T03:28:00.009+08:002014-01-19T14:34:53.830+08:00用 Eclipse CDT 編譯 CppSQLite3工作上為了讓 sqlite 可以在 platform 上執行,所以必須從完整的 source code 開始編譯,對於不怎麼熟悉 Makefile 的我這真是一件麻煩的事,還好以前有玩過 CDT,索性就利用他可以自動建立 Makefile 的功能來做。<br /><br />不過我是使用 Eclipse 3.2 版的 CDT,Eclipse 3.5 的 CDT 裡的 Makefile 我不太會用,也沒有時間去找文章。<br /><br /><br />這裡我下載了兩個檔案:<br /><a href="http://www.sqlite.org/download.html">SQLite</a> -> <a href="http://www.sqlite.org/sqlite-source-3_7_2.zip">sqlite-source-3_7_2.zip</a><br /><a href="http://www.codeproject.com/KB/database/CppSQLite.aspx">CppSQLite</a> -> <a href="http://www.codeproject.com/KB/database/CppSQLite/CppSQLite_3_1_demo_and_src.zip">CppSQLite_3_1_demo_and_src.zip</a><br /><br /><br /><span style="font-weight: bold;">首先建立動態連結庫</span><ol><li>新增 "Managed Make C++ Project" 專案 -> 名稱 "cppsqlite"<br /><br /></li><li>在 Project Type 中選擇 <span style="color: rgb(255, 0, 0);">Shared Library</span><br /><br /></li><li>在專案下新增 src 資料夾<br /><br /></li><li>複製 sqlite-source-3_7_2.zip 中所有的 source code 至 src 除了 <span style="color: rgb(255, 0, 0);">shell.c</span> 與 <span style="color: rgb(255, 0, 0);">tclsqlite.c</span><br /><br /></li><li>再複製 CppSQLite_3_1_demo_and_src.zip 中的 <span style="color: rgb(255, 0, 0);">CppSQLite3.h</span> 與 <span style="color: rgb(255, 0, 0);">CppSQLite3.cpp</span><br /><br /></li><li>開啟:專案 -> 內容<br /><br /></li><li>增加 Defined symbols 變數 -> <span style="color: rgb(255, 0, 0);">SQLITE_CORE</span><br /><a href="http://lh5.ggpht.com/_b8lN_UbLoEc/TIIP-Gx6E-I/AAAAAAAAHrk/NJOKCIUoyMk/s1600-h/cppsqlite3-1.png"><img src="http://lh5.ggpht.com/_b8lN_UbLoEc/TIIP-Gx6E-I/AAAAAAAAHrk/NJOKCIUoyMk/s256/cppsqlite3-1.png" alt="" id="BLOGGER_PHOTO_ID_5512986453501154274" border="0" /></a><br /><br /></li><li>在 C++ 跟 C 的 Optimization 的參數中增加 <span style="color: rgb(255, 0, 0);">-fPIC</span> 最佳化參數<br /><a href="http://lh5.ggpht.com/_b8lN_UbLoEc/TIIP-RQddXI/AAAAAAAAHro/Hqn5DptWRl4/s1600-h/cppsqlite3-2.png"><img src="http://lh5.ggpht.com/_b8lN_UbLoEc/TIIP-RQddXI/AAAAAAAAHro/Hqn5DptWRl4/s256/cppsqlite3-2.png" alt="" id="BLOGGER_PHOTO_ID_5512986456313656690" border="0" /></a><br /><br /></li><li>按下『確定』後就會開始編譯,檔案有點多要稍微等一下</li></ol><br /><br /><br /><span style="font-weight: bold;">再來建立主程式專案</span><ol><li>新增 "Managed Make C++ Project" 專案 -> 名稱 "sqlite-test"<br /><br /></li><li>在 Project Type 中選擇 <span style="color: rgb(255, 0, 0);">Executable</span><br /><br /></li><li>在專案下新增 src 資料夾<br /><br /></li><li>複製 CppSQLite_3_1_demo_and_src.zip 中的 <span style="color: rgb(255, 0, 0);">CppSQLite3Demo.cpp</span> 至 src<br /><br /></li><li>開啟:專案 -> 內容<br /><br /></li><li>新增 Include paths -> <span style="color: rgb(255, 0, 0);">"../../cppsqlite/src"</span><br />這個設定是在告知<span style="color: rgb(255, 0, 0);">編譯</span>時額外 Include 的進來 <span style="color: rgb(255, 0, 0);">Header(*.h)</span> 的路徑。<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgS_sPYCgOpEypPgrvgOX_OrmlGt_LQ_VBG2tpRgPAijQz5VgEqxdF3IzcgWZg-ASAKsfxRhYd-K95A4dckCUQQprPX4IjFm-a1FUqOt1jRblLwMxRT5HXKdM3ejPlFmBVNw9ZSKnieGYAM/s1600-h/cppsqlite3-3.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgS_sPYCgOpEypPgrvgOX_OrmlGt_LQ_VBG2tpRgPAijQz5VgEqxdF3IzcgWZg-ASAKsfxRhYd-K95A4dckCUQQprPX4IjFm-a1FUqOt1jRblLwMxRT5HXKdM3ejPlFmBVNw9ZSKnieGYAM/s256/cppsqlite3-3.png" alt="" id="BLOGGER_PHOTO_ID_5512986460108193122" border="0" /></a><br /><br /></li><li>新增連結路徑:<br />Library search path -> <span style="color: rgb(255, 0, 0);">"../../cppsqlite/Debug"</span><br />Libraries -> <span style="color: rgb(255, 0, 0);">cppsqlite</span><br />這個設定是給 gcc 在做<span style="color: rgb(255, 0, 0);">連結</span>時需要的搜尋路徑,以及需要連結的<span style="color: rgb(255, 0, 0);">對象名稱</span>。<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrneIIAaN6Mo-HpfGZp0Vyt0u7NZgyiS-KmHkZD86SU2iBq5A2aWbT03ANRF9tTcFeJWTmlhpLezs_cKHYiBhiISQL7uJyvtDGW_CR97RqZX1m5LIEdA9kN4aAK_nuBPizGVbcBE0vMe6j/s1600-h/cppsqlite3-4.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrneIIAaN6Mo-HpfGZp0Vyt0u7NZgyiS-KmHkZD86SU2iBq5A2aWbT03ANRF9tTcFeJWTmlhpLezs_cKHYiBhiISQL7uJyvtDGW_CR97RqZX1m5LIEdA9kN4aAK_nuBPizGVbcBE0vMe6j/s256/cppsqlite3-4.png" alt="" id="BLOGGER_PHOTO_ID_5512986459036050930" border="0" /></a><br /><br /></li><li>按下『確定』後就會開始編譯<br /><br /></li><li>接著要將 cppsqlite.dll 複製到 sqlite-test/Debug 下<br /><span style="color: rgb(204, 0, 0);">Windows 的 lib 名稱為 "cppsqlite<span style="color: rgb(0, 0, 153);">.dll</span>"</span><br /><span style="color: rgb(204, 0, 0);">Linux 的 lib 名稱為 "<span style="color: rgb(0, 0, 153);">lib</span>cppsqlite<span style="color: rgb(0, 0, 153);">.so</span>"</span><br /><br /></li><li>然後就可以執行 sqlite-test.exe 了 <span style="color: rgb(102, 102, 102);font-size:85%;" >(一整個就很快樂)</span></li></ol><span style="font-weight: bold;">專案範例:</span><a style="font-weight: bold;" href="https://sites.google.com/site/weskerjax/code-demo/sqlite-test.zip?attredirects=0&d=1">sqlite-test.zip</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-54728118763539296252010-07-16T18:42:00.007+08:002013-06-10T23:25:22.711+08:00[Ubuntu] PHP 連接 Oracle 語系設定PHP 的環境變數必須在 Apache 中設定<br />
<br />
開啟:<br />
vim /etc/apache2/envvars<br />
<br />
在最後面加入:<br />
export NLS_LANG="AMERICAN_AMERICA.UTF8"<br />
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"<br />
<br />
參考來源:<br />
<a target="_blank" href="http://tony.strongniche.com.tw/linux/contents.php?sn=534">如何新增apache使用的環境變數</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com2tag:blogger.com,1999:blog-5946530704742130970.post-75151881781516744462010-07-16T16:38:00.002+08:002013-08-06T22:48:49.283+08:00[Oracle] 修改 SESSION 的日期格式<pre class="sql" name="code">ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
</pre><br />
<table class="table_list" cellspacing="0" cellpadding="3" border="1"><tbody>
<tr class="header"><th width="93">Parameter</th><th>Explanation</th></tr>
<tr><td>YEAR</td><td>Year, spelled out</td></tr>
<tr><td>YYYY</td><td>4-digit year</td></tr>
<tr><td>YYY<br />
YY<br />
Y</td><td>Last 3, 2, or 1 digit(s) of year.</td></tr>
<tr><td>IYY<br />
IY<br />
I</td><td>Last 3, 2, or 1 digit(s) of ISO year.</td></tr>
<tr><td>IYYY</td><td>4-digit year based on the ISO standard</td></tr>
<tr><td>Q</td><td>Quarter of year (1, 2, 3, 4; JAN-MAR = 1).</td></tr>
<tr><td>MM</td><td>Month (01-12; JAN = 01).</td></tr>
<tr><td>MON</td><td>Abbreviated name of month.</td></tr>
<tr><td>MONTH</td><td>Name of month, padded with blanks to length of 9 characters.</td></tr>
<tr><td>RM</td><td>Roman numeral month (I-XII; JAN = I).</td></tr>
<tr><td>WW</td><td>Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.</td></tr>
<tr><td>W</td><td>Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.</td></tr>
<tr><td>IW</td><td>Week of year (1-52 or 1-53) based on the ISO standard.</td></tr>
<tr><td>D</td><td>Day of week (1-7).</td></tr>
<tr><td>DAY</td><td>Name of day.</td></tr>
<tr><td>DD</td><td>Day of month (1-31).</td></tr>
<tr><td>DDD</td><td>Day of year (1-366).</td></tr>
<tr><td>DY</td><td>Abbreviated name of day.</td></tr>
<tr><td>J</td><td>Julian day; the number of days since January 1, 4712 BC.</td></tr>
<tr><td>HH</td><td>Hour of day (1-12).</td></tr>
<tr><td>HH12</td><td>Hour of day (1-12).</td></tr>
<tr><td>HH24</td><td>Hour of day (0-23).</td></tr>
<tr><td>MI</td><td>Minute (0-59).</td></tr>
<tr><td>SS</td><td>Second (0-59).</td></tr>
<tr><td>SSSSS</td><td>Seconds past midnight (0-86399).</td></tr>
<tr><td>FF</td><td>Fractional seconds.</td></tr>
</tbody></table>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-56310585762989407632010-05-22T23:32:00.004+08:002012-02-12T23:32:39.092+08:00[Oracle] Sequence 取號函數<pre class="sql" name="code"><br />-- [新增]<br />CREATE SEQUENCE my_sequence<br /> INCREMENT BY 1<br /> START WITH 1<br /> MAXVALUE 99999999999<br /> NOCYCLE<br /> CACHE 10<br />;<br /><br />-- 參數說明:<br />INCREMENT BY n -- 每次疊加的值(正負數)<br /><br />START WITH n -- 指定初始值,建立後將無法透過 ALTER 修改<br /><br />MAXVALUE n -- 最大編號<br />NOMAXVALUE -- (預設)不設置最大值,由系統上限決定<br /><br />MINVALUE n -- 最小編號<br />NOMINVALUE -- (預設 1)不設置最小值<br /><br />CYCLE -- 當取至最大值後,是否循環再由最小值開始<br />NOCYCLE -- (預設)不設置循環,當超過限制時將返回異常<br /><br />CACHE n -- (預設 20)系統會一次取出 n 個數作為快取,但會造成跳號的現象<br />NOCACHE -- 不使用快取,保證在產生的編號中沒有跳號,但這樣會降低性能.<br /><br /><br /><br />-- [修改]<br />ALTER SEQUENCE my_sequence<br /> INCREMENT BY 10 <br /> MAXVALUE 10000<br /> CYCLE<br /> NOCACHE<br />;<br /><br /><br /><br />-- [刪除]<br />DROP SEQUENCE my_sequence;<br /><br /><br /><br />-- [使用方式]<br />my_sequence.CURRVAL -- 返回序號的當前值<br />my_sequence.NEXTVAL -- 增加序號的值,然後返回序號值<br /><br /></pre><br /><br />參考來源:<br /><a href="http://proxy.gtn.com.tw/forum/index.php?topic=25.0">Oracle 產生自動編號方式</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-57420022102342594902010-05-15T02:12:00.004+08:002012-02-12T23:32:39.094+08:00[Oracle] 基礎 Table Schema 指令<pre class="sql" name="code"><br />-- 建立資料表<br />CREATE TABLE table_name(<br /> column1 NUMBER,<br /> column2 CHAR,<br /> column3 VARCHAR2,<br /> column4 DATE,<br /> <br /> -- 主鍵(PRIMARY KEY)<br /> CONSTRAINT pk_table_name PRIMARY KEY(column1,column2),<br /> -- 唯一鍵(UNIQUE KEY)<br /> CONSTRAINT unique_table_name UNIQUE(column1,column2),<br /> -- 外來鍵(FOREIGN KEY)<br /> CONSTRAINT fk_table_name_column1<br /> FOREIGN KEY(column1, column2)<br /> REFERENCES parent_table(column1, column2)<br /> [ON DELETE [CASCADE|SET NULL]]<br /> -- 當參考鍵刪除時,相對應做的處理:<br /> -- 預設 UPDATE No Action & DELETE No Action<br /> -- ON DELETE CASCADE: 刪除參考鍵時連同刪除<br /> -- ON DELETE SET NULL: 刪除參考鍵時將外鍵設為 NULL<br />);<br /><br /><br />-- 為資料表標示註釋<br />COMMENT ON TABLE table_name IS '資料表註釋';<br /><br />-- 為資料欄位標示註釋<br />COMMENT ON COLUMN table_name.column1 IS '資料欄位註釋';<br /><br /><br />-- 附加主鍵(PRIMARY KEY)<br />ALTER TABLE table_name<br />ADD CONSTRAINT pk_table_name PRIMARY KEY(column1,column2);<br /><br /><br />-- 附加唯一鍵(UNIQUE KEY)<br />ALTER TABLE table_name<br />ADD CONSTRAINT unique_table_name UNIQUE(column1,column2);<br /><br /><br />-- 附加外來鍵(FOREIGN KEY)<br />ALTER TABLE table_name<br />ADD CONSTRAINT fk_table_name_column1<br /> FOREIGN KEY(column1, column2)<br /> REFERENCES parent_table(column1, column2)<br /> [ON DELETE [CASCADE|SET NULL]];<br /><br /><br />-- 建立索引(Index) <br />CREATE [UNIQUE] INDEX idx_table_name ON table_name(column1, column2);<br /></pre><br />最近剛剛接觸 Oracle<br />很多觀念一時轉不過來<br />雖然 SQL 都大同小異<br />但就是這些被這些小異搞翻掉 <br /><br />整體架構跟 MySQL 差蠻多<br />只能說 MySQL 真的很容易上手<br />而且社群跟文獻都很多<br />所以要找參考資料比較容易<br /><br />參考連結:<br /><a href="http://ss64.com/ora/">Oracle Commands</a><br /><a href="http://www.adp-gmbh.ch/ora/sql/">Oracle SQL</a><br /><a href="http://www.techonthenet.com/oracle/index.php">Oracle/PLSQL Topics</a><br /><a href="http://blog.miniasp.com/post/2007/10/Oracle-Development-Note.aspx">Oracle 開發筆記</a><br /><a href="http://www.builder.com.cn/2007/0828/474815.shtml">Oracle基本语法集锦</a><br /><a href="http://blog.chinaunix.net/u/20571/showart_1316507.html">oracel 數據完整性</a>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-64235130607324390492010-05-15T01:12:00.005+08:002012-02-12T23:32:39.095+08:00[Oracle] 快速建立使用者<pre class="sql" name="code"><br />CREATE USER user_name IDENTIFIED BY user_password; -- 建立使用者<br />GRANT dba TO user_name; -- 授與最大權力<br /></pre><br />為了這個簡單的指令上 google 找半天<br />最後還是去請教 MIS 的前輩Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0tag:blogger.com,1999:blog-5946530704742130970.post-49885689248752137452010-04-20T14:07:00.005+08:002012-02-12T23:32:57.042+08:00MySQL 在做 JOIN 時對索引關連的小發現當兩個 Table 在做 JOIN 時<br />如果在對應上有其他判斷式<br />便不會使用索引關連<br /><pre class="sql" name="code"><br />SELECT * FROM<br /> a<br />INNER JOIN<br /> b<br />ON a.id=b.id &&(b.num=a.num || b.num=0)<br /></pre><br /><br />當我將判斷式移到 WHERE 上去<br />在 ON 上留下有索引的鍵值對應<br />這樣的效率居然比原本的快兩倍<br /><pre class="sql" name="code"><br />SELECT * FROM<br /> a<br />INNER JOIN<br /> b<br />ON a.id=b.id<br />WHERE b.num=a.num || b.num=0<br /></pre><br /><br />結論就是:<br /><span style="color: rgb(255, 0, 0);">在設定關連邏輯時,最好使用單純的鍵值對應</span>Jax Huhttp://www.blogger.com/profile/01953021685585893658noreply@blogger.com0