更改innodb預設的資料庫存放位置

ubuntu上預設存放innodb資料庫的位置是/va/lib/mysql，這真不是一個存放資料的好位置，如果作業系統是32位元的，單一檔案的上限是2G，最好是使用整個分割區當做innodb資料庫的存放位置，這樣就沒有2G的限制，因為這是分割區，而且效率會比較好。

試了幾個方式都試不出來，花了一整天，真是煩人，不過用google上網找資料，二分鐘內就解決了，感謝google，用起來真方便說，參考網走如下:
http://www.ubuntugeek.com/how-to-change-the-mysql-data-default-directory.html

整個步驟如下:
1.開啟/etc/apparmor.d/usr.sbin.mysqld
2.將/目錄名稱/ r,及目錄名稱/** rwk,加入以/va/lib/mysql開頭的指令行下面
3./etc/init.d/apparmor reload
4./etc/init.d/mysql restart

這樣就可以將innodb的資料存放到不同位置，而不會產生錯誤訊息，使mysql無法啟動

mssql的分頁

http://blog.blueshop.com.tw/blackncc/archive/2007/01/06/49123.aspx

memcached的使用

http://dev.mysql.com/doc/refman/5.0/en/index.html
http://tw2.php.net/manual/en/book.memcache.php

Memcached

introduction
在一般的IT環境下，如果要擴增程式的使用人數，最大的問題會卡在程式的執行速度，經常存取的資訊，如果使用mysql做為後端存取的資料庫，速度就會變得慢，因為資訊的存取，必須透過執行sql語法，取得資料庫內相關的資料，而拖慢執行速度。

memcached是一個簡單的、具有高度伸縮性的key-based快取，快取來源不受限制，他也是一個備份的RAM，可以讓應用程式進行非常快速地存取，要使用memcached，必須在一台或多台主機上執行memcached，讓使用者共享快取內儲存的物件，因為每台主機都是使用RAM儲存資訊，存取的速度遠遠快過從硬碟上進行資訊的存取，這樣的話，相對於從資料庫存取資料，效能的增進是非常明顯的。快取只是容納資訊的空間，你可以在快取內儲存任何資料，如果儲存的是複雜的資料結構，可以在資料存進快取前，先進行複雜的資料庫操作，再將資料放進快取中，可以大幅減少mysql server的負擔。

使用memcached的一般性作法是修改應用程式，讓應用程式改為從memcached讀取資料，如果需要的資訊不在memcached裡頭，程式就改由mysql讀取資料，再將資料寫入memcached裡頭，以後如果要存取相同資料，就可以發揮memcached資料快速存取的優勢。

在memcached的架構中，所有的client可以送出key與任何一台memcached伺服器連繫，在這個架構中，每個client可以與圖中的任何伺服器連繫，在client上，當送出要求時，key會先被hash，這個hash值會被用來選取memcached伺服器，memcached伺服器由client決定，可以使程式維持輕量化。

當client送出key時，會執行同一套演算法，相同的key，會產生相同的hash值，同一部memcached伺服器就會被選為資料來源，使用這個方法，快取資料被分散放置在所有的memcached伺服器上，而這些快取資訊可以被所有的client存取，結果就是，以記憶體做為資料儲存處的分散式快取，這類型的快取尤其適合複雜的資料結構，存取速度遠遠快過直接從資料庫存取資料。

在memcached伺服器中的資料永遠不會放在磁碟上，記憶體中的快取可以從後端的資料庫(mysql)取得資料，如果memcached伺服器當掉了，可以改為由mysql取得資料，當然，速度會變得比較慢。


Installing memcached

在ubuntu上安裝memcached非常輕鬆，只要輸入下面這道指令，再按enter就安裝好了

apt-get install memcached

memcached的啟動

在ubuntu上啟動memcached伺服器

/etc/init.d/memcached start

參數配置

參考/etc/memcached.conf設定檔的說明會更清楚

-u 執行身份
-m 記憶體容量(快取容量，單位為MB)
-p 指定port
-d 以daemon方式執行
-t 指定執行緒的數量
-l 指定執行的網路介面


memcached deployment

memcached有許多不同的佈署方式及策略，正確的佈署方式取決於你的應用程式及環境，當在系統中佈署memcached，你必須考量下列的注意事項

*memcached只是一項快取機制，如果資料很重要，在存取的過程中不能因為漏失，再從不同的地方存取，就不能使用memcached。

*memcached沒有內建的安全機制，至少，你必須確定，memcached伺服器只能在內部網路存取，而且memcached伺服器使用的port不能被外部網路存取。如果memcached含有敏感性資料，這些資料在存進memcached前要先被加密。

*memcached不提供復原機制，因為memcached之間沒有任何的溝通協定，如果一台memcached故障了，你必須將這台memcached從列表中移除，再重新載入資料，再將資料寫入另一台memcached伺服器中，

*如果client及memcached分別在不同的機器上，資料傳送延遲性如果造成問題，可以把memcached服務移到client端的機器上。

*key的長度是由memcached伺服器決定，預設的最大長度是250byte

*只使用一台memcached絕不是一個好的想法，尤其是服務多個client時，最少提供二台
memcached，才可以恰當地管理機器當機的狀況，如果可以的話，應該多建置幾台memcached
伺服器。當增加或移除memcached伺服器時，key/value的配置及hash值就會受到影響，如果要避免這些問題，必須研究memcached的hash type。


Memory allocation within memcached

當你第一次啟動memcached伺服器，memcached參數指定的記憶體數量不會自動配置給memcached，相反地，當開始將資料存進memcached的快取時，才會進行記憶體的分配。

當你開始將資料存在快取中，memcached不會為每個存入快取的資料一一配置記憶體空間，而是一次配置一個區塊，以進行記憶體空間的有效利用，避免快取資料過期時，造成記憶體空間配置的零碎化。

memcached裡頭的快取記憶體配置，以page為單位，每一page預設是1M，一個或多個page組成一個slab。

當memcached啟動時，不會進行slab及page的配置，只有當資料存進快取時，才會在page中切出適當大小的chunk，把資料存進chunk中，每一個chunk，會儲存一筆資料的value跟key，而page中每筆chunk的大小都必須一樣。

例如，memcached啟動時，有一筆資料的大小是800k，須要存入快取，這時就會建立一個slab，這個slab中page的chunk大小就是1M，一個page就只有一個chunk。

如果存入快取的資料是250K，那麼，就會建立一個slab，這個slab會再建立一個page，這個page會再切成4個大小相同的chunk。如果還有6筆250K的資料要存入快取，這個slab會再建立第二個相同的page進行資料的儲存。

資料如果超過1M，由於chunk預設是不能超過一M，而且資料必須塞進chunk中，因此超過一M的資料就會無法存入快取當中。


Using namespaces

memcached是非常簡單的key/value型式的儲存系統，無法自動將資料進行分類，例如，如果你使用mysql資料表傳回的id當做存入memcached快取的key時，就有可能有二筆由mysql資料表傳回的資料，雖然內容不同，id卻相同，存入快取時這二筆資料就會相互覆蓋，造成資料讀取的錯誤。

一些程式的API會在資料存入快取時，自動建立命名空間，實務上，這些命名空間，只是在存取資料時，在key前加上區別的字串。

你可以自己實做命名空間，只要存取快取資料時，在key前加上自定的字串就可以了，如加上”user_”字串。


Data Expiry

memcached快取資料的過期有二種狀況，如果要插入新的資料到快取中，但是沒有適當地slab可以存資料時，最近最少使用的資料會被移除，以騰出空間讓新的資料存入。

這個方法可以確保被移除的資料，是不再被使用的，或是已經很久不被使用的，但如果memcached的快取容量，比正常狀況使用時小很多時，你就會看到很多尚在使用的資料被移除了。

啟動memcached時啟用 -M參數，會在記憶體不足時發出警告，而不是自動刪除舊有的資料。

第二種快取過期的狀況，是直接刪除快取資料，或是設定資料過期的時間。

一種典型的應用就是設定使用者儲存的session資料，快取過期的時間。


在php中使用memcached

api的安裝

apt-get install php5-memcache


程式範例

程式名稱：a.php
class Test{
public $model;
public $color;
}

$test=new Test();
$test->model='bmw';
$test->color='red';

$cache=new Memcache();
$cache->connect('localhost',11211);
$cache->set('car',$test);


程式名稱：b.php
class Test{
public $model;
public $color;
}

connect('localhost',11211);
$car=$cache->get('car');

echo $car->model;
echo “\n”;
echo $car->color;
echo “\n”;

跑完a.php程式後，再跑b.php，就會跑出下面的結果
bmw
red

程式中物件的二個屬性都是經由serialize後，由a.php存在快取中，如果其他的程式要取用這二個屬性，只要有快取的key值，就可以得到這個物件的屬性。

輸出mysql的schema

這是在mysql workbench的說明文件上看到的一個方法，如果要輸出資料庫裡頭已建好的資料庫架構，可以下這道指令，把整個資料庫的schema都輸出成sql指令檔，再用mysql workbench的輸入功能，將sql指令檔輸入到mysql workbench，就可以看到完整的資料庫架構圖。

指令用法：
mysqldump 資料庫名稱 --no-data>輸出檔名

stored procedure

Stored procedure
http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

什麼是stored procedure
stored procedure是由一群sql敘述及流程控制的語法組成，本質上就是mysql中的程式語言，只是stored procedure的程式語法，跟php的不一樣，能使用的環境只限於mysql資料庫中，專為處理資料庫的操作，為了處理資料庫中的資料，有一些語法與php這種泛用型的程式語言，語法方面就稍微有些不同。就像戰機有雖然都有戰鬥功能，但為了不同的目的，有不同設計，以發揮團隊作戰的整體力量，有的戰機是專用對付入侵的戰機，是屬於空優型戰機，有的戰機，武裝不強，但體型龐大，有匿蹤功能，可以攜帶大量彈藥轟炸敵軍，屬於轟炸機。
mysql的stored procedure的語法遵循SQL:2003的語法標準，IBM的DB2也一樣遵循這套標準，遵循相同的標準有相當明顯的好處，如果要轉換資料庫，stored procedure不同重新撰寫，除錯，可以輕易的進行轉換，不會因為換了資料庫，必須重頭學習另一種語法，所有的基礎重頭來過。
使用stored procedure可以減少php程式碼的數量並增加程式的效能，php程式中只要呼叫stored procedure，stored procedure便會執行stored procedure中所有的sql敘述，並將結果傳回php程式中，php不用再一一呼叫sql statement，執行各自獨立的sql statement，再一一判斷結果，進行處理，這些sql statement及判斷統統都包裝在stored procedure裡頭，php及mysql之間傳遞的資料數量大幅減少，由於php與mysql之間等待資料傳送的時間及次數減少，可以加快程式的速度，也由於只要呼叫stored procedure，而不用呼叫各自獨立的sql statement，也減少了程式碼的數量。
目前，stored procedure不支援遞迴呼叫，如果要使用遞迴呼叫，必須將mysql變數：max_sp_recursion_depth 設定為非零的數字，預設是零，不允許遞迴，設為1，允許遞迴一次，設為2，允許遞迴二次，以此類推。

stored procedure的管理
stored procedure都存在mysql資料庫中的proc資料表中(mysql.proc)，這個資料表儲存所有的stroed procedure，這些資料透過管理工具，可以運作正常，但如果手動修改，就有可能發生不可預期的後果，絕對不要手動修改這些資料，而是要透過create procedure，alter proceudre或是drop procedure來進行管理。

要建立stored procedure就必須有create routine的權限

要修改stored procedure就必須有alter routine的權限

要執行stored procedure就必須有execute的權限

有create routine的權限就自動會有alter routine及execute的權限

建立stored procedure的語法
create [definer={usercurrent_user}] procedure sp_name([sp_parameter],[sp_parameter]...)
[characteristic]
stored procedure body
[characteristic]
language sql
目前沒有作用
[not] deterministic
如果沒設，預設為 not deterministic
{ CONTAINS SQL NO SQL READS SQL DATA MODIFIES SQL DATA }
目前沒有強制性，mysql只會當做註解
sql security {definerinvoker}
預設為definer
comment '註解內容'

修改
alter procedure sp_name ...

刪除
drop procedure sp_name;

執行stored procedure
call 預存程序名稱(參數);
以實際範例操作會比較清楚。
/**
*建立store procedure，名稱為sp_test
*/
delimiter //
create definer=current_user procedure sp_test(out version varchar(60),out day varchar(60))
language sql
not deterministic
contains sql
sql security definer
begin
select version() into version;
select curdate() into day;
end;
//
delimiter ;
/**
*執行stored procedure，stored procedure的名稱為sp_test
*/
call sp_test(@version,@day);
select @version,@day;

變數的處理
變數宣告
declare i int default 1;
變數的設定
set i=3;
select 4*5 into i;

例外處理
例外訊息列表
http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
看下面的實例比較快
delimiter //
create procedure sp_h()
begin
declare no_table condition for 1146;
declare continue handler for no_table
begin
select 'hi!! world!';
end;
select * from a;
end
//
delimiter ;

cursor
只能讀
只能單向移動，不能跳過任一列
create procedure test()
begin
declare var_name varchar(30);
declare cur1 cursor for select name from a;
declare exit handler for sqlstate '02000' begin end;
open cur1;
repeat
fetch cur1 into var_name;
select var_name;
until 0 end repeat;
close cur1;
end;

宣告的順序
變數及condition
cursor
handler

流程控制
mysql的stored procedure流程控制語法跟一般的程式語言相同，有迴圈及判斷式的二類。迴圈及判斷式的用法與php的用法相同，只是語法不一樣，寫幾個具體範例熟悉一下迴圈及判斷式的語法會比較較快進入狀況，manual上的說明，範例太少了，自己出幾個題目，動手寫一些小程式會比較快熟悉stored procedure的迴圈判斷式的語法。
Mysql的stored procedure的iterate及leave是用在迴圈的語法，iterate的功能與php中的continue相同，leave就跟php中的break相同，都是控制迴圈進行的語法，讓程式能依照判斷式跳出迴圈或繼續重頭執行迴圈中的程式碼。
底下是使用stored procedure的語法寫的簡易程式，最好是使用記本先打好，再匯入mysql或是使用mysql的圖形化介面的管理工具，如navicat、Mysql Administrator，進行stored procedure的撰寫，才會比較快速有效率，不然是很難進行除錯的。工欲善其事，必先善其器，好的工具及方法，可以加快你進入stored procedure世界的速度。
程式很簡單，只要有程式基礎，看一下manual就知道程式是怎麼運作，但學習方法比較容易被忽略，如果只是看看過去，沒有實際撰寫的話，不容易深入了解stored procedure的運作。最好自己出題，實際寫幾個stored procedure，搭配manual上的說明，才能對stored procedure有完整的認識。
以loop迴圈寫的九九乘法表
delimiter //
create procedure sp_a()
begin
declare i int default 1;
declare j int default 1;
label_a: loop
if i<=9 then
set j=1;
label_b: loop
if j<=9 then
select i,j,i*j;
set j=j+1;
iterate label_b;
end if;
leave label_b;
end loop label_b;
set i=i+1;
iterate label_a;
end if;
leave label_a;
end loop label_a;
end;
//
delimiter ;

以repeat迴圈寫的九九乘法表
delimiter //
create procedure sp_b()
begin
declare i int default 1;
declare j int default 1;
repeat
set j=1;
repeat
select i,j,i*j;
set j=j+1;
until j>9 end repeat;
set i=i+1;
until i>9 end repeat;
end;
//
delimiter ;

以while迴圈寫的九九乘法表
delimiter //
create procedure sp_c()
begin
declare i int default 1;
declare j int default 1;
while i<=9 do
set j=1;
while j<=9 do
select i,j,i*j;
set j=j+1;
end while;
set i=i+1;
end while;
end;
//
delimiter ;

if判斷式的使用
delimiter //
create procedure sp_d(in age int)
begin
if age<18 then
select 'you are young man';
elseif age>=18 and age<40 then
select 'you have many life experiences';
else
select 'you ard old man';
end if;
end;
//
delimiter ;

case判斷式的使用
delimiter //
create procedure sp_e(in selector int)
begin
case selector
when 1 then
select 'yellow';
when 2 then
select 'black';
when 3 then
select 'red';
else
select 'no color selected';
end case;
end;
//
delimiter ;

workbench的使用

http://dev.mysql.com/downloads/workbench/5.0.html

MySQL Workbench可以很方便的畫出EER(enhance entity relationship)圖，圖形介面的操作方式非常容易上手，可以很快的畫出資料表跟資料表之間的關係，要修改資料表或資料庫的設定也非常容易，用滑鼠點一點、按一按，就修改好了，這種針對資料庫的專用工具，比起自己用freemind或dia畫EER圖，更有效率，由於Workbench將資料庫層面的物件關係加在設計裡頭，也更容易看出資料表之間的關連，不用自己發揮想像力，克難地使用不適當地工具，進行資料庫的設計。

用workbench畫好EER圖後，這張EER圖可以依使用者的需求輸出成pdf、圖檔或是sql命令，最方便地部份就是輸出成sql命令了，將資料庫設計好後，直接將sql命令匯到mysql server後，整個資料庫就在server上全部建立完成了。只不過根據我自己的測試，workbench有一項小缺點，他不會檢查foreign key的型態與referenced key的型態是否一致，這二個如果不一致是沒辦法在mysql裡頭建立資料表的，但workbench不會檢查這項錯誤，如果將這類的sql命令匯入mysql中，資料表會無法建立，致於還有沒有其他的缺點，就不清楚了。

workbench目前只有window版，沒有linux版，有點不方便，我家裡的電腦已經換成ubuntu了，如果沒有linux版，在家裡就沒辦法用了，不過網站上的roadmap，指出今年會推出linux版，看來需要耐心等待linux版的推出，不然就要再想想別的辦法了。

prepared statement

引用網址：
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

prepared statement

prepared statement是什麼？

Prepared statement 可以讓你在第一次執行時sql時，先設定sql敘述，讓以後相同的sql，只要帶入不同的參數，就重複執行相同的sql，而不用每次執行相同的sql時都讓伺服器耗費時間設定sql敘述。

例子：底下是一個prepared statement的實際範例。

select * from news where id=?

符號? 叫做placeholder

執行sql時，必須代入參數，取代 ? 號，才能真正執行sql敘述。

為什麼要使用prepared statement
程式中使用prepared statement 可以增進安全性及效能。

Prepared statement 把sql的邏輯語句及代入的資料分離，可以增加安全性，這樣的做法，可以避免sql隱碼的攻擊，如果使用一般的sql敘述，沒有分離sql邏輯及代入資料，你必須非常小心處理使用者輸入的資料，通常會使用一些特定的函數，跳脫單引號、雙引號、倒斜線等特殊字元。但是當使用prepared statement時，根本不用使用特殊的函數來避免sql隱碼的攻擊，你可以直接使用prepared statement，不會造成任何sql隱碼的漏洞。

效能的提升來自prepared statement的一些特殊之處，首先，prepared statement中的sql敘述只要解析一次，之後執行的sql敘述就不用再做這些解析的動作，如果你要執行同一個sql敘述很多次，就可以增加執行的速度。

效能提升的第二個原因，是prepared statement使用了binary protocol，傳統的mysql會將資料轉換成字串，再進行傳送，但使用binary protocol後，不會進行資料的轉換，而是直接將資料以原來的binary的型式進行傳送，減低了cpu的負荷，也減少了網路的使用率(轉換成字串，資料量會變大)。

使用prepared statement 的時機
prepared statement只能用在DML(insert,update,delete,replace),create table,select敘述上，額外的支援會再慢慢加上去。

Prepared statement因為要解析兩次，會比一般的sql敘述慢，如果sql敘述只會執行一次，你就要考量，是否值得使用prepared statement增強安全性，而犧牲了整體的執行速度。

如何使用prepared statement
php 5的mysqli extention支援prepared statement，其他的語言我不熟。

Mysql也有使用prepared statement的interface，不過這些interface不支援binary protocol，除非程式語言的api不支援，不然，mysql的prepared statement interface通常當做測試用。

Mysql的prepared statement的interface的用法

create table people(name,sex);

insert into people(name,sex) values('Hi','f'),('a','b');

set @query='select * from people where name=? And sex=?';

set @name='Hi';

set @sex='f';

prepare stmt from @query;

execute stmt using @name,@sex;

trigger

引用網址

http://dev.mysql.com/doc/refman/5.0/en/triggers.html#nolinkhere

trigger

概論：
trigger是資料庫中的一種物件，trigger物件的運作與資料表息息相關，當對資料表執行insert、update或delete的動作時才會觸發指定的trigger物件，執行trigger內的sql敘述，底下以實例說明會比較清楚：

#建立一個資料表，名稱是employ

create table employ(hour int,net_pay int);


#建立一個與employ資料表相關的trigger，名稱是tri_cal
#這個trigger的作用是當插入資料到資料表employ中時，變數sum的值會與資料表employ
#中的新揷入的欄位net_pay的值相加。

create trigger tri_cal
before insert
on employ
for each row
set @sum=@sum+new.net_pay;



#變數sum的值會變成2

set @sum=0;
insert imploy(hour,net_pay) values(1,2);
select @sum;


#變數sum的值會變成4

insert imploy(hour,net_pay) values(1,2);
select @sum;


語法說明：
create [definer={usercurrent_user}] trigger trigger_name
trigger_time trigger_event
on table_name
for each row
trigger_stmt


trigger_time
before
after

trigger_event
insert
update
delete


table_name
指定觸發trigger的table


trigger_stmt
觸發後執行的sql敘述