Transaction with Stored Procedure in MySQL Server
Stored Procedure in MySQL Server is not atomic by default. To ensure atomicity, you need to write all your SQL Statements within START TRANSACTION .. COMMIIT Block. Moreover, you need to declare EXIT HANDLER for SQLEXCEPTION and SQLWARNINGS to ROLLBACK all of the SQL Statements in START TRANSACTION .. COMMIIT Block.
==========================================
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
==========================================
You need to keep in mind those two important issues while using Transaction and Exit Handler in MySQL Server.
# Never declare any Variable within START TRANSACTION .. COMMIIT Block.
# Exit Handler Declaration should be your last Declaration just ahead of START TRANSACTION .. COMMIIT Block.
Let's do some coding. In the following code snippet, I write "sp_cheque_leaves_posting" Procedure that accepts two Parameters. Second one is OUT Parameter that returns 1 on succeess of this Stored Procedure Routine.
============================================================================
CREATE PROCEDURE sp_cheque_leaves_posting(IN param_requisition_id INT, OUT param_sp_success TINYINT)
BEGIN
DECLARE details_id_loop INT DEFAULT 0;
DECLARE starting_serial_no INT;
DECLARE record_not_found INT DEFAULT 0;
DECLARE cursor_details_id CURSOR FOR SELECT details_id FROM khan_trial.table_requisition_details WHERE requisition_master_id = param_requisition_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
START TRANSACTION;
SET param_sp_success = 0;
SELECT MAX(ending_serial_no) INTO starting_serial_no FROM table_cheque_leaves_posting;
IF starting_serial_no IS NULL THEN
SET starting_serial_no = 1;
ELSE
SET starting_serial_no = starting_serial_no + 1;
END IF;
OPEN cursor_details_id;
all_details_id:LOOP
FETCH cursor_details_id INTO details_id_loop;
IF record_not_found THEN
LEAVE all_details_id;
END IF;
INSERT INTO table_cheque_leaves_posting
VALUES(details_id_loop,starting_serial_no,starting_serial_no+9,12,12,'A Rahim Khan');
SET starting_serial_no = starting_serial_no + 10;
END LOOP all_details_id;
CLOSE cursor_details_id;
UPDATE table_requisition_master SET cheque_leaves_posting = 1
WHERE requisition_id = param_requisition_id;
SET param_sp_success = 1;
COMMIT;
END
=======================================================
Database has three tables i.e. “table_cheque_leaves_posting”, “table_requisition_details”, “table_requisition_master”. “table_cheque_leaves_posting” does not allow duplicate “details_id”. Call the "sp_cheque_leaves_posting" routine in the following way.
===============================
CALL sp_cheque_leaves_posting(1,@a);
SELECT @a;
Output:
@a
--------
0
================================
That is the Stored Procedure has not completed successfully. Inserting duplicate value for “details_id” Field in “table_cheque_leaves_posting” has resulted in SQLEXCEPTION and SQL Statements within the START TRANSACTION .. COMMIIT Block has not commited as well.
ถ้ายัง ลองนี่ดูครับว่าใช้ได้มั้ยคัรบ
ประกาศการสร้าง stored procedure ด้วยนะครับ
BEGIN
declare exit handler for not found rollback;
declare exit handler for sqlexception rollback;
declare exit handler for sqlwarning rollback;
START TRANSACTION;
Bla .. Bla .. Bla .. อะไรก็ว่ากันไปคัรบ;
COMMIT;
END
วันอังคารที่ 17 สิงหาคม พ.ศ. 2553
วันจันทร์ที่ 16 สิงหาคม พ.ศ. 2553
แก้ WGA Window XP แบบสมบูรณ์
ทำแบบนี้ได้ 100 % โดยไม่ต้องใช้โปรแกรมใดๆครับ
แก้ WGA Window XP แบบสมบูรณ์
แล้วทำวิธีนี้มันต่างกันยังไงหรอ ผมลองทำแล้วมันได้อะ ไม่ต้องโหลดโปรแกรมอะไรเลย
จากที่หลาย ๆ คนได้โดนเจ้าตัว WGA ของทาง Microsoft เข้าไปทำให้หลายท่านเกิดความรำคาญ แต่วันนี้เรามีวิธีที่เด็ดขาดโดยมันจะไม่กลับมารบกวนท่านอีกโดยการทำให้มันเป็นของแท้ซะเลย ทำให้เจ้า WGA ไม่มากวนใจท่านอีกต่อไป และยังสามารถติดตั้งซอฟแวร์ของไมโครซอฟท์ได้โดยที่ตัวตรวจจับมันเห็นเป็นของแท้ เรามาเริ่มกันเลยดีกว่า
1. พิมพ์ regedit ในช่อง run แล้วเข้าไปที่ HKey_Local_Machine\Software\Microsoft\WindowsNT\Current Version\WPAEvents,
ทางด้านขวา ดับเบิ้ลคลิ๊กที่ oobetimer แล้วลบตัวเลขที่อยู่ในนั้นให้หมด (จะเหลือเลข 0 อยู่สี่ตัว ลบไม่ได้)
อันนี้เป็นการล้างค่าที่ไมโครซอฟท์ใช้ตรวจสอบวินโดวส์ขอเราครับ เสร็จแล้วกด OK แล้วปิดไปได้เลย
2. จากนั้นพิมพ์ %systemroot%\system32\oobe\msoobe.exe /a ลงในช่อง run แล้วกด Enter
จะปรากฎหน้าต่างของ Activate Windows ขึ้นมา จากนั้นให้เลือกที่
Yes, I want to telephone a customer service representative to activate Windows แล้วคลิ๊กที่ Next
3. จากนั้นคลิ๊กที่ Change Product Key โดยที่ไม่ต้องใส่อะไรทั้งนั้นในหน้านี้
4. จากนั้นให้ใส่ Product Key B3P7V-Q2WTH-CRK4R-YHJRF-39H4M
แล้วคลิ๊กที่ Update เมื่อเสร็จเรียบร้อยแล้วก็ให้ปิดหน้าต่างนี้ไปได้เลย โดยคลิ๊กที่ X ที่มุมขวาบน
5. รีสตาร์ทเครื่องหนึ่งครั้ง แล้งลองพิมพ์ %systemroot%\system32\oobe\msoobe.exe /a แล้วกด Enter
จะปรากฎคำว่า Windows is already activated ถ้าขึ้นตามนี้ก็แสดงว่าทุกอย่างเรียบร้อยแล้วครับ
6. ทดสอบโดยการตรวจสอบกับทางไมโครซอฟท์ โดยการเปิด Windows Explorer แล้วเลือกที่ Help
แล้วเลือกที่ ตรวจสอบลิจสิทธิ์วินโดวส์ ถ้ามันบอกว่าเป็นของแท้ก็ลุย อัพเดท โหลดและติดตั้งโปรแกรมฟรีของไมโครซอฟท์
ทั้ง Windows Defeder , WMP11 และอื่น ๆ ได้เลย
แก้ WGA Window XP แบบสมบูรณ์
แล้วทำวิธีนี้มันต่างกันยังไงหรอ ผมลองทำแล้วมันได้อะ ไม่ต้องโหลดโปรแกรมอะไรเลย
จากที่หลาย ๆ คนได้โดนเจ้าตัว WGA ของทาง Microsoft เข้าไปทำให้หลายท่านเกิดความรำคาญ แต่วันนี้เรามีวิธีที่เด็ดขาดโดยมันจะไม่กลับมารบกวนท่านอีกโดยการทำให้มันเป็นของแท้ซะเลย ทำให้เจ้า WGA ไม่มากวนใจท่านอีกต่อไป และยังสามารถติดตั้งซอฟแวร์ของไมโครซอฟท์ได้โดยที่ตัวตรวจจับมันเห็นเป็นของแท้ เรามาเริ่มกันเลยดีกว่า
1. พิมพ์ regedit ในช่อง run แล้วเข้าไปที่ HKey_Local_Machine\Software\Microsoft\WindowsNT\Current Version\WPAEvents,
ทางด้านขวา ดับเบิ้ลคลิ๊กที่ oobetimer แล้วลบตัวเลขที่อยู่ในนั้นให้หมด (จะเหลือเลข 0 อยู่สี่ตัว ลบไม่ได้)
อันนี้เป็นการล้างค่าที่ไมโครซอฟท์ใช้ตรวจสอบวินโดวส์ขอเราครับ เสร็จแล้วกด OK แล้วปิดไปได้เลย
2. จากนั้นพิมพ์ %systemroot%\system32\oobe\msoobe.exe /a ลงในช่อง run แล้วกด Enter
จะปรากฎหน้าต่างของ Activate Windows ขึ้นมา จากนั้นให้เลือกที่
Yes, I want to telephone a customer service representative to activate Windows แล้วคลิ๊กที่ Next
3. จากนั้นคลิ๊กที่ Change Product Key โดยที่ไม่ต้องใส่อะไรทั้งนั้นในหน้านี้
4. จากนั้นให้ใส่ Product Key B3P7V-Q2WTH-CRK4R-YHJRF-39H4M
แล้วคลิ๊กที่ Update เมื่อเสร็จเรียบร้อยแล้วก็ให้ปิดหน้าต่างนี้ไปได้เลย โดยคลิ๊กที่ X ที่มุมขวาบน
5. รีสตาร์ทเครื่องหนึ่งครั้ง แล้งลองพิมพ์ %systemroot%\system32\oobe\msoobe.exe /a แล้วกด Enter
จะปรากฎคำว่า Windows is already activated ถ้าขึ้นตามนี้ก็แสดงว่าทุกอย่างเรียบร้อยแล้วครับ
6. ทดสอบโดยการตรวจสอบกับทางไมโครซอฟท์ โดยการเปิด Windows Explorer แล้วเลือกที่ Help
แล้วเลือกที่ ตรวจสอบลิจสิทธิ์วินโดวส์ ถ้ามันบอกว่าเป็นของแท้ก็ลุย อัพเดท โหลดและติดตั้งโปรแกรมฟรีของไมโครซอฟท์
ทั้ง Windows Defeder , WMP11 และอื่น ๆ ได้เลย
วันอาทิตย์ที่ 18 กรกฎาคม พ.ศ. 2553
Flow control (IF/CASE) ใน Mysq
Flow control (IF/CASE) ใน Mysql
Flow control ใน Mysql
อ้างอิงจาก http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
คืออะไร? ทำไมต้องใช้?
บางครั้งแม้มันจะง่ายและเร็วกว่า ที่จะทำส่วนคำนวณใน Php ก่อนแล้วค่อยมาทำ Query ใน Mysql แต่ในบางกรณีเล็กๆน้อย หรือบางครั้งที่ต้องการการ Customize โดยไม่ต้องเขียนโค้ดเพิ่ม การเขียน IF/Case เข้าไปใน Mysql ก็สร้างความสะดวกได้มาก
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.
mysql> SELECT IF(1>2,2,3); -> 3
mysql> SELECT IF(1<2,’yes’,'no’); -> ‘yes’
mysql> SELECT IF(STRCMP(’test’,'test1′),’no’,'yes’); -> ‘no’
เป็น IF แบบอย่างง่าย โดยจะมี 3 parameter คือ
- expr1 = Condition โดยให้ใส่เป็นเงื่อนไขต่างๆเช่น a>1 , b < a หรืออื่นๆ ที่ return ค่าเป็น boolean (true/false)
- expr2 = จะทำงานเป็น Condition เป็น true โดย Function IF จะ return ค่า expr2 กลับมา
- expr3 = จะทำงานเป็น Condition เป็น false โดย Function IF จะ return ค่า expr3 กลับมา
โดย IF แบบนี้ จะใช้งานเหมือนกับ Function ทั่วไปเลย ซึ่งข้อเสียคือทำให้ทำ Nested If (If หลายๆเงื่อนไข) ค่อนข้างลำบาก
CASE
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result …] [ELSEresult] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END
mysql> SELECT CASE 1 WHEN 1 THEN ‘one’
-> WHEN 2 THEN ‘two’ ELSE ‘more’ END;
-> ‘one’
mysql> SELECT CASE WHEN 1>0 THEN ‘true’ ELSE ‘false’ END;
-> ‘true’
mysql> SELECT CASE BINARY ‘B’
-> WHEN ‘a’ THEN 1 WHEN ‘b’ THEN 2 END;
-> NULL
Case ของ Mysql จะต่างกับ IF ตรงที่ดูเป็นโครงสร้างภาษา ไม่ใช่เป็น Function
โดยรูปแบบจะเหมือนกับ Switch/Select ของภาษาอื่น คือ จะเข้า Case เมื่อ value/condition มีค่าเท่ากับ compare_value
Trick สำหรับการทำ IF เงื่อนไขแบบเป็นช่วง สามารถดัดแปลงใช้ CASE ช่วยได้
Ex.
(CASE TRUE WHEN point_all<20 THEN 1 WHEN point_all<100 THEN 2 WHEN point_all<500 THEN 3 WHEN point_all<5000 THEN 4 ELSE 5 END) as point_all_lv,
โดยหลักการก็ง่ายๆ ให้ Value ตั้งต้นเป็น TRUE และ ใน WHEN ก็ใส่เป็น Condition ไปแทน ทีนี้เราก็ได้ IF ที่น่าตาอ่านง่ายกว่าแบบ Function
Flow control ใน Mysql
อ้างอิงจาก http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
คืออะไร? ทำไมต้องใช้?
บางครั้งแม้มันจะง่ายและเร็วกว่า ที่จะทำส่วนคำนวณใน Php ก่อนแล้วค่อยมาทำ Query ใน Mysql แต่ในบางกรณีเล็กๆน้อย หรือบางครั้งที่ต้องการการ Customize โดยไม่ต้องเขียนโค้ดเพิ่ม การเขียน IF/Case เข้าไปใน Mysql ก็สร้างความสะดวกได้มาก
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.
mysql> SELECT IF(1>2,2,3); -> 3
mysql> SELECT IF(1<2,’yes’,'no’); -> ‘yes’
mysql> SELECT IF(STRCMP(’test’,'test1′),’no’,'yes’); -> ‘no’
เป็น IF แบบอย่างง่าย โดยจะมี 3 parameter คือ
- expr1 = Condition โดยให้ใส่เป็นเงื่อนไขต่างๆเช่น a>1 , b < a หรืออื่นๆ ที่ return ค่าเป็น boolean (true/false)
- expr2 = จะทำงานเป็น Condition เป็น true โดย Function IF จะ return ค่า expr2 กลับมา
- expr3 = จะทำงานเป็น Condition เป็น false โดย Function IF จะ return ค่า expr3 กลับมา
โดย IF แบบนี้ จะใช้งานเหมือนกับ Function ทั่วไปเลย ซึ่งข้อเสียคือทำให้ทำ Nested If (If หลายๆเงื่อนไข) ค่อนข้างลำบาก
CASE
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result …] [ELSEresult] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END
mysql> SELECT CASE 1 WHEN 1 THEN ‘one’
-> WHEN 2 THEN ‘two’ ELSE ‘more’ END;
-> ‘one’
mysql> SELECT CASE WHEN 1>0 THEN ‘true’ ELSE ‘false’ END;
-> ‘true’
mysql> SELECT CASE BINARY ‘B’
-> WHEN ‘a’ THEN 1 WHEN ‘b’ THEN 2 END;
-> NULL
Case ของ Mysql จะต่างกับ IF ตรงที่ดูเป็นโครงสร้างภาษา ไม่ใช่เป็น Function
โดยรูปแบบจะเหมือนกับ Switch/Select ของภาษาอื่น คือ จะเข้า Case เมื่อ value/condition มีค่าเท่ากับ compare_value
Trick สำหรับการทำ IF เงื่อนไขแบบเป็นช่วง สามารถดัดแปลงใช้ CASE ช่วยได้
Ex.
(CASE TRUE WHEN point_all<20 THEN 1 WHEN point_all<100 THEN 2 WHEN point_all<500 THEN 3 WHEN point_all<5000 THEN 4 ELSE 5 END) as point_all_lv,
โดยหลักการก็ง่ายๆ ให้ Value ตั้งต้นเป็น TRUE และ ใน WHEN ก็ใส่เป็น Condition ไปแทน ทีนี้เราก็ได้ IF ที่น่าตาอ่านง่ายกว่าแบบ Function
วันอังคารที่ 6 กรกฎาคม พ.ศ. 2553
ทำไมการใช้ index จึงทำให้ query ข้อมูลได้ไวขึ้น?
ทำไมการใช้ index จึงทำให้ query ข้อมูลได้ไวขึ้น?
การทำ index ในฐานข้อมูล ก็คลายกับการทำ index ในท้ายเล่มของหนังสือ การที่เราจะหาเนื้อหาในหนังสือเราก็เปิดไปที่ index แล้วก็หาว่าตัวเนื้อหาที่เราต้องการอ่านนั้นอยู่หน้าไหนบ้าง เราก็เปิดไปยังหน้านั้นได้เลย โดยที่ไม่ต้องมาเปิดหนังสือดูทุกหน้าว่าเนื้อหาที่เรากำลังหานั้นอยู่หน้าไหน
ในฐานข้อมูลการทำ index ก็จะทำให้กับ field หรือคอลัมน์ที่เรามีเงื่อนไขในการ query บ่อยๆ การดึงข้อมูลก็จะไปดูที่ index แล้วกระโดดไปยังตำแหน่งที่เก็บข้อมูลเลยโดยไม่ต้องค้นหาทุกแถวในตาราง
ตัวอย่างเช่น การทำ index ให้กับตาราง employees ที่คอลัมน์ emp_id
1 rowid
2 rowid
3 rowid
. .
. .
. .
เมื่อมีการ query
SELECT *
FROM employees
WHERE emp_id = 3;
Database ก็จะวิ่งไปดึงข้อมูลยังตำแหน่งที่เก็บข้อมูลของ emp003 มาแสดง โดยที่ไม่ต้องวิ่งไปหาทุกๆ แถวในตาราง employees
Oracle Database มี Index อยู่หลายประเภทแต่ที่ถูกใช้กันทั่วไปคือ B-Tree Indexes
(รูปภาพจาก www.oracle.com)
จากรูปจะเห็นว่า B-tree Index นั้นมี block อยู่สองประเภทด้วยกันคือ Branch blocks ไว้สำหรับการค้นหา และ leaf blocks สำหรับเก็บค่า ในการค้นหาก็จะแบ่งเป็นช่วงๆ ตามขอบเขตแต่ละ block ทำให้การค้นหานั้นมีประสิทธิภาพ
ซึ่ง การใช้ index นั้น ยังมีรายละเอียดเสริมเพื่อเพิ่มความเข้าใจอีก โดย คุณ Siamnobita ได้อธิบายเกี่ยวกับ index ด้วยกัน 5 ข้อดังต่อไปนี้
1. สิ่งที่ทำให้เราสามารถค้นหาใน index ได้เร็วนั้นเนื่องจากมีการ sort ตามค่าในคอลัมน์ด้วย เคยมีคนตั้งคำถามใน narisa ประมาณว่าทำไม oracle ถึงไม่เรียงลำดับข้อมูลในตารางซะเลย จะได้ไม่ต้องใช้ index คำตอบก็คือการจัดเก็บแบบเรียงลำดับนั้นทำได้ยากกว่าและเกิดต้นทุนตามมาเช่น เวลาที่ใช้เมื่อมีการเพิ่ม record ใหม่ พื้นที่ว่างเมื่อเกิดการ split block เป็นต้น อย่างไรก็ดีหากเราไม่มีปัญหากับต้นทุนเหล่านี้ เราก็สามารถจัดเก็บข้อมูลในตารางแบบเรียงลำดับได้เลย นั่นคือใช้ index-organized table (IOT) ซึ่งถือเป็นวิธีที่เร็วที่สุดในการค้นหาข้อมูลตาม primary key
2. ปกติเวลา oracle อ่านข้อมูลจะอ่านทีละ block ไม่ใช่ทีละแถว ดังนั้นจะดูว่าเร็วหรือช้า จะนับจากว่า oracle ต้อง access ข้อมูลจำนวนกี่ block เช่น
ถ้าดูจากรูปด้านบน index มี 3 level
เมื่อเรา select * from ... where index_column = ??
oracle จะอ่านข้อมูลทั้งสิ้น 4 block คือ root block ตัวบนสุด, branch block ตัวกลาง, leaf block ตัวล่างสุด, table block ซึ่งรู้ได้ทันทีเมื่อรู้ค่า rowid จำนวน level ที่น้อยที่สุดที่เป็นไปได้คือ 1 level คือเก็บ rowid ไว้ใน root block เลย ซึ่งจะเกิดในกรณีที่ข้อมูลมีจำนวนไม่มาก ( โดย default ขนาดของ block ประมาณ 8K )
3. คำถามคือ หากข้อมูลมีขนาดเล็ก ๆ เช่น ตารางมีขนาดแค่ block เดียว การใช้ index ยังมีประโยชน์อยู่หรือไม่ เดิมผมเคยเข้าใจว่าไม่มีประโยชน์เหมือนกัน แต่เมื่อได้อ่าน blog ของคุณ richard foote ซึ่งทำการทดสอบให้เห็นชัด ๆ ไปเลย พบว่า index ก็ยังมีประโยชน์อยู่ดี เนื่องจาก
3.1 ในการ full table scan นอกจากตัว block ที่เก็บ data แล้วยังต้องอ่าน header block ด้วยจึงมี cost ที่เกิดขึ้นไม่ใช่แค่ 1 I/O เมื่อเทียบกับ index ที่ใช้ 2 I/O (root block + data block) ก็พอ ๆ กัน
3.2 ในการ full table scan จะเก็บ data ที่อ่านมาบน buffer cache ด้าน LRU ซึ่งจะอยู่ใน memory ได้ไม่นาน ขณะที่ index scan จะวางไว้ด้าน MRU ทำให้มีโอกาสใช้ประโยชน์จากการอ่านจาก memory โดยตรงได้มากกว่า โดยเฉพาะเมื่อมีการเรียกใช้ข้อมูลจาก table บ่อย ๆ
4. ในข้อ 3 เขาทดสอบเฉพาะกรณี index unique scan เช่นค้นตาม primary key เท่านั้น หากเป็น index range scan จะเป็นอีกกรณีหนึ่ง ซึ่งทุก rowid ที่เจอใน index ก็จะต้องมีการ access table block หนึ่งครั้ง แม้ว่า block นั้นจะอยู่บน memory แล้วก็ยังเป็น cost อยู่ดี ดังนั้น full table scan ก็อาจจะคุ้มกว่าขึ้นกับจำนวนแถวที่ต้องการ
5. ประโยชน์ของ index อีกข้อ คือโดยปกติจำนวน column ใน index จะน้อยกว่า column ใน table มาก ๆ ดังนั้นขนาดของ index ก็จะเล็กกว่า table มาก ๆ ด้วย หากเราต้องการดูเฉพาะข้อมูลที่อยู่ใน index อยู่แล้ว เราก็อาจ full scan ที่ index แทน table ไปเลยก็ได้ ซึ่งกรณีนี้จะเป็นการ fast full scan ซึ่งอ่าน index แบบ multi block เหมือน table scan ( full scan ใน index มี 2 แบบ full scan เฉย ๆ กับ fast full scan แบบแรกอ่านทีละ block ซึ่งช้ากว่า แต่ข้อดีคือ ผลลัพธ์มีการ sort ตาม index แบบหลังจะไม่มีการเรียงลำดับ
ปล. ต้องขอบคุณคำอธิบายดี ๆ จาก คุณ Siamnobita มาก ๆ ครับ
การทำ index ในฐานข้อมูล ก็คลายกับการทำ index ในท้ายเล่มของหนังสือ การที่เราจะหาเนื้อหาในหนังสือเราก็เปิดไปที่ index แล้วก็หาว่าตัวเนื้อหาที่เราต้องการอ่านนั้นอยู่หน้าไหนบ้าง เราก็เปิดไปยังหน้านั้นได้เลย โดยที่ไม่ต้องมาเปิดหนังสือดูทุกหน้าว่าเนื้อหาที่เรากำลังหานั้นอยู่หน้าไหน
ในฐานข้อมูลการทำ index ก็จะทำให้กับ field หรือคอลัมน์ที่เรามีเงื่อนไขในการ query บ่อยๆ การดึงข้อมูลก็จะไปดูที่ index แล้วกระโดดไปยังตำแหน่งที่เก็บข้อมูลเลยโดยไม่ต้องค้นหาทุกแถวในตาราง
ตัวอย่างเช่น การทำ index ให้กับตาราง employees ที่คอลัมน์ emp_id
1 rowid
2 rowid
3 rowid
. .
. .
. .
เมื่อมีการ query
SELECT *
FROM employees
WHERE emp_id = 3;
Database ก็จะวิ่งไปดึงข้อมูลยังตำแหน่งที่เก็บข้อมูลของ emp003 มาแสดง โดยที่ไม่ต้องวิ่งไปหาทุกๆ แถวในตาราง employees
Oracle Database มี Index อยู่หลายประเภทแต่ที่ถูกใช้กันทั่วไปคือ B-Tree Indexes
(รูปภาพจาก www.oracle.com)
จากรูปจะเห็นว่า B-tree Index นั้นมี block อยู่สองประเภทด้วยกันคือ Branch blocks ไว้สำหรับการค้นหา และ leaf blocks สำหรับเก็บค่า ในการค้นหาก็จะแบ่งเป็นช่วงๆ ตามขอบเขตแต่ละ block ทำให้การค้นหานั้นมีประสิทธิภาพ
ซึ่ง การใช้ index นั้น ยังมีรายละเอียดเสริมเพื่อเพิ่มความเข้าใจอีก โดย คุณ Siamnobita ได้อธิบายเกี่ยวกับ index ด้วยกัน 5 ข้อดังต่อไปนี้
1. สิ่งที่ทำให้เราสามารถค้นหาใน index ได้เร็วนั้นเนื่องจากมีการ sort ตามค่าในคอลัมน์ด้วย เคยมีคนตั้งคำถามใน narisa ประมาณว่าทำไม oracle ถึงไม่เรียงลำดับข้อมูลในตารางซะเลย จะได้ไม่ต้องใช้ index คำตอบก็คือการจัดเก็บแบบเรียงลำดับนั้นทำได้ยากกว่าและเกิดต้นทุนตามมาเช่น เวลาที่ใช้เมื่อมีการเพิ่ม record ใหม่ พื้นที่ว่างเมื่อเกิดการ split block เป็นต้น อย่างไรก็ดีหากเราไม่มีปัญหากับต้นทุนเหล่านี้ เราก็สามารถจัดเก็บข้อมูลในตารางแบบเรียงลำดับได้เลย นั่นคือใช้ index-organized table (IOT) ซึ่งถือเป็นวิธีที่เร็วที่สุดในการค้นหาข้อมูลตาม primary key
2. ปกติเวลา oracle อ่านข้อมูลจะอ่านทีละ block ไม่ใช่ทีละแถว ดังนั้นจะดูว่าเร็วหรือช้า จะนับจากว่า oracle ต้อง access ข้อมูลจำนวนกี่ block เช่น
ถ้าดูจากรูปด้านบน index มี 3 level
เมื่อเรา select * from ... where index_column = ??
oracle จะอ่านข้อมูลทั้งสิ้น 4 block คือ root block ตัวบนสุด, branch block ตัวกลาง, leaf block ตัวล่างสุด, table block ซึ่งรู้ได้ทันทีเมื่อรู้ค่า rowid จำนวน level ที่น้อยที่สุดที่เป็นไปได้คือ 1 level คือเก็บ rowid ไว้ใน root block เลย ซึ่งจะเกิดในกรณีที่ข้อมูลมีจำนวนไม่มาก ( โดย default ขนาดของ block ประมาณ 8K )
3. คำถามคือ หากข้อมูลมีขนาดเล็ก ๆ เช่น ตารางมีขนาดแค่ block เดียว การใช้ index ยังมีประโยชน์อยู่หรือไม่ เดิมผมเคยเข้าใจว่าไม่มีประโยชน์เหมือนกัน แต่เมื่อได้อ่าน blog ของคุณ richard foote ซึ่งทำการทดสอบให้เห็นชัด ๆ ไปเลย พบว่า index ก็ยังมีประโยชน์อยู่ดี เนื่องจาก
3.1 ในการ full table scan นอกจากตัว block ที่เก็บ data แล้วยังต้องอ่าน header block ด้วยจึงมี cost ที่เกิดขึ้นไม่ใช่แค่ 1 I/O เมื่อเทียบกับ index ที่ใช้ 2 I/O (root block + data block) ก็พอ ๆ กัน
3.2 ในการ full table scan จะเก็บ data ที่อ่านมาบน buffer cache ด้าน LRU ซึ่งจะอยู่ใน memory ได้ไม่นาน ขณะที่ index scan จะวางไว้ด้าน MRU ทำให้มีโอกาสใช้ประโยชน์จากการอ่านจาก memory โดยตรงได้มากกว่า โดยเฉพาะเมื่อมีการเรียกใช้ข้อมูลจาก table บ่อย ๆ
4. ในข้อ 3 เขาทดสอบเฉพาะกรณี index unique scan เช่นค้นตาม primary key เท่านั้น หากเป็น index range scan จะเป็นอีกกรณีหนึ่ง ซึ่งทุก rowid ที่เจอใน index ก็จะต้องมีการ access table block หนึ่งครั้ง แม้ว่า block นั้นจะอยู่บน memory แล้วก็ยังเป็น cost อยู่ดี ดังนั้น full table scan ก็อาจจะคุ้มกว่าขึ้นกับจำนวนแถวที่ต้องการ
5. ประโยชน์ของ index อีกข้อ คือโดยปกติจำนวน column ใน index จะน้อยกว่า column ใน table มาก ๆ ดังนั้นขนาดของ index ก็จะเล็กกว่า table มาก ๆ ด้วย หากเราต้องการดูเฉพาะข้อมูลที่อยู่ใน index อยู่แล้ว เราก็อาจ full scan ที่ index แทน table ไปเลยก็ได้ ซึ่งกรณีนี้จะเป็นการ fast full scan ซึ่งอ่าน index แบบ multi block เหมือน table scan ( full scan ใน index มี 2 แบบ full scan เฉย ๆ กับ fast full scan แบบแรกอ่านทีละ block ซึ่งช้ากว่า แต่ข้อดีคือ ผลลัพธ์มีการ sort ตาม index แบบหลังจะไม่มีการเรียงลำดับ
ปล. ต้องขอบคุณคำอธิบายดี ๆ จาก คุณ Siamnobita มาก ๆ ครับ
วันอังคารที่ 29 มิถุนายน พ.ศ. 2553
การใช้ JOIN ใน SQL
การใช้ JOIN ใน SQL
การทำ JOIN ใน SQL คือการนำตาราง 2 ตารางมารวมกัน โดยเงื่อนไขการรวมจะมีดังนี้
INNER JOIN เลือกเฉพาะข้อมูลที่มี key ตรงกันเท่านั้นมาแสดง
OUTER JOIN นำข้อมูลทั้งหมดมาแสดงแม้ว่า key ไม่ตรงกัน
LEFT JOIN ทำการ JOIN ข้อมูลกับตารางที่อยู่ทางซ้าย
RIGHT JOIN ทำการ JOIN ข้อมูลกับตารางที่อยู่ทางขวา
ไวยากรณ์ของการ JOIN มีดังนี้
SELECT columns_list
FROM left_table { INNER | OUTER | LEFT | RIGHT } JOIN right_table
ON join_condition
WHERE select_condition
ตัวอย่าง กำหนดให้ตาราง A และ B มีข้อมูลดังนี้
TABLE_A
------------------------
id Name
------------------------
1 John
2 Susan
3 Tony
TABLE_B
------------------------
id Salary
------------------------
1 23,000
2 12,000
5 32,100
ตัวอย่าง SQL Statement:
SELECT *
FROM TABLE_A INNER JOIN TABLE_B
ON TABLE_A.id = TABLE_B.id
ผลการการ JOIN แต่ละแบบจะได้ผลดังนี้:
INNER JOIN: ผลที่ได้จะเห็นว่าข้อมูลที่ไม่ตรงทั้งใน TABLE_A และ TABLE_B จะไม่แสดง
------------------------
id Name Salary
------------------------
1 John 23,000
2 Susan 12,000
OUTER JOIN: จะได้ข้อมูลทั้งหมด เท่าที่จะแสดงได้ เช่น ใน TABLE_A มีข้อมูลของ Tony แต่ TABLE_B ไม่มีข้อมูลของ Tony (id 3) ดังนั้นข้อมูลที่ได้ จะมีข้อมูลของ Tony แสดงแต่ไม่มีข้อมูลเงินเดือนของ Tony
------------------------
id Name Salary
------------------------
1 John 23,000
2 Susan 12,000
3 Tony
5 32,100
LEFT JOIN: ข้อมูลที่ได้จะยึดข้อมูลในตารางทางซ้ายเป็นหลัก ในที่นี้ให้ TABLE_A เป็น TABLE ด้านซ้าย จะเห็นได้จากข้อมูลใน TABLE_A ซึ่งมีรายชื่อของ John, Susan และ Tony ผลที่ได้ก็จะแสดงข้อมูลของพนักงานทั้ง 3 คน แต่ข้อมูลของ Tony จะไม่มีเงินเดือนแสดง เนื่องจากไม่มีข้อมูลของ Tony อยู่ในตาราง TABLE_B
------------------------
id Name Salary
------------------------
1 John 23,000
2 Susan 12,000
3 Tony
RIGHT JOIN: หลักการจะเหมือนกับ LEFT JOIN แต่จะต่างกันตรงที่ข้อมูลที่ได้จะยึดเอาข้อมูลในตารางด้ายขวาเป็นหลัก
------------------------
id Name Salary
------------------------
1 John 23,000
2 Susan 12,000
5 32,100
เพิ่มเติม
ในกรณีที่ต้องการ Join ข้อมูลตั้งแต่ 3 table ขึ้นไป ให้เลือก Table ขึ้นมาสองอันแล้ว Join กันก่อน จากนั้นจึงค่อย Join กับ Table ที่เหลือ เช่น
SELECT Table1.id, Table2.salary, Table3.position
FROM (Table1 INNER JOIN Table2 ON Table1.id = Table2.id)
INNER JOIN Table3 ON Table1.id = Table3.id;
การทำ JOIN ใน SQL คือการนำตาราง 2 ตารางมารวมกัน โดยเงื่อนไขการรวมจะมีดังนี้
INNER JOIN เลือกเฉพาะข้อมูลที่มี key ตรงกันเท่านั้นมาแสดง
OUTER JOIN นำข้อมูลทั้งหมดมาแสดงแม้ว่า key ไม่ตรงกัน
LEFT JOIN ทำการ JOIN ข้อมูลกับตารางที่อยู่ทางซ้าย
RIGHT JOIN ทำการ JOIN ข้อมูลกับตารางที่อยู่ทางขวา
ไวยากรณ์ของการ JOIN มีดังนี้
SELECT columns_list
FROM left_table { INNER | OUTER | LEFT | RIGHT } JOIN right_table
ON join_condition
WHERE select_condition
ตัวอย่าง กำหนดให้ตาราง A และ B มีข้อมูลดังนี้
TABLE_A
------------------------
id Name
------------------------
1 John
2 Susan
3 Tony
TABLE_B
------------------------
id Salary
------------------------
1 23,000
2 12,000
5 32,100
ตัวอย่าง SQL Statement:
SELECT *
FROM TABLE_A INNER JOIN TABLE_B
ON TABLE_A.id = TABLE_B.id
ผลการการ JOIN แต่ละแบบจะได้ผลดังนี้:
INNER JOIN: ผลที่ได้จะเห็นว่าข้อมูลที่ไม่ตรงทั้งใน TABLE_A และ TABLE_B จะไม่แสดง
------------------------
id Name Salary
------------------------
1 John 23,000
2 Susan 12,000
OUTER JOIN: จะได้ข้อมูลทั้งหมด เท่าที่จะแสดงได้ เช่น ใน TABLE_A มีข้อมูลของ Tony แต่ TABLE_B ไม่มีข้อมูลของ Tony (id 3) ดังนั้นข้อมูลที่ได้ จะมีข้อมูลของ Tony แสดงแต่ไม่มีข้อมูลเงินเดือนของ Tony
------------------------
id Name Salary
------------------------
1 John 23,000
2 Susan 12,000
3 Tony
5 32,100
LEFT JOIN: ข้อมูลที่ได้จะยึดข้อมูลในตารางทางซ้ายเป็นหลัก ในที่นี้ให้ TABLE_A เป็น TABLE ด้านซ้าย จะเห็นได้จากข้อมูลใน TABLE_A ซึ่งมีรายชื่อของ John, Susan และ Tony ผลที่ได้ก็จะแสดงข้อมูลของพนักงานทั้ง 3 คน แต่ข้อมูลของ Tony จะไม่มีเงินเดือนแสดง เนื่องจากไม่มีข้อมูลของ Tony อยู่ในตาราง TABLE_B
------------------------
id Name Salary
------------------------
1 John 23,000
2 Susan 12,000
3 Tony
RIGHT JOIN: หลักการจะเหมือนกับ LEFT JOIN แต่จะต่างกันตรงที่ข้อมูลที่ได้จะยึดเอาข้อมูลในตารางด้ายขวาเป็นหลัก
------------------------
id Name Salary
------------------------
1 John 23,000
2 Susan 12,000
5 32,100
เพิ่มเติม
ในกรณีที่ต้องการ Join ข้อมูลตั้งแต่ 3 table ขึ้นไป ให้เลือก Table ขึ้นมาสองอันแล้ว Join กันก่อน จากนั้นจึงค่อย Join กับ Table ที่เหลือ เช่น
SELECT Table1.id, Table2.salary, Table3.position
FROM (Table1 INNER JOIN Table2 ON Table1.id = Table2.id)
INNER JOIN Table3 ON Table1.id = Table3.id;
วันอาทิตย์ที่ 27 มิถุนายน พ.ศ. 2553
เพิ่มความเร็วการ SELECT ด้วย MySQL Query Cache
เพิ่มความเร็วการ SELECT ด้วย MySQL Query Cache
บทความนี้ขอแนะนำอีกวีธีแต่จะมีผลทั้งเซิร์ฟเวอร์ฐานข้อมูล (Database Server) เลย ด้วยการเปิดคุณสมบัติ Query Cache ของ MySQL ให้มีการเก็บ SELECT statement และผลลัพธ์ที่ได้ไว้ใน cache ซึ่งถ้าเรารันคำสั่ง SELECT ครั้งต่อไปที่มี statement เหมือนกัน MySQL จะดึงผลลัพธ์การ query มาจาก cache โดยตรง ไม่ต้องไป query จาก table มาใหม่ ทำให้ผลการค้นหาเร็วขึ้นมาก
ถ้าข้อมูลใน table มีการเปลี่ยนแปลง cache ที่เก็บไว้จะถูกลบออกไป เพื่อให้ผลลัพธ์ในการค้นหาครั้งต่อไปถูกต้อง
ทดสอบก่อนการเปิดใช้ MySQL Query Cache
ในบทความนี้จะทดสอบกับ Fedora 10 โดย MySQL ที่ติดตั้งมาในแผ่นดีวีดี ไม่ได้เปิดคุณสมบัติ Query Cache ไว้ สามารถตรวจสอบได้จากคำสั่ง SHOW Variables ใน mysql
mysql> SHOW Variables WHERE Variable_name RLIKE 'query_cache';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
การที่จะใช้ Query Cache ได้นั้น ตัวแปร have_query_cache ต้องเท่ากับ ‘YES’ ตัวแปร ‘query_cache_type’ เท่ากับ ‘ON’ นอกจากนี้ต้องคอนฟิกค่า query_cache_size ด้วย เพื่อจองขนาด memory เพื่อใช้เก็บ query cache ค่า 0 คือการปิดคุณสมบัติ query cache
ทดสอบรัน SELECT ก่อนการเปิด query cache
หมายเหตุ
ตัวอย่างที่ทดสอบนี้ ไม่มีการสร้าง INDEX หรือคีย์ของฟิลด์ที่ชื่อ item_name
table ที่ใช้ทดสอบมีข้อมูลประมาณ 300,000 rows
mysql> SELECT item_id, item_name FROM items WHERE item_name = 'GK809A0';
+---------+-----------+
| item_id | item_name |
+---------+-----------+
| 261351 | GK809A0 |
+---------+-----------+
1 row in set (0.21 sec)
ลองรัน SELECT หลายๆ ครั้ง ด้วยคำสั่งเหมือนกัน เวลาที่ใช้จะใกล้เคียงกันประมาณ 0.21 วินาที
คอนฟิกค่า query_cache_size
แก้ไขไฟล์ /etc/my.cnf โดยเพิ่มคอนฟิก query_cache_size ลงไป ให้อยู่ภายใต้คอนฟิกของ [mysqld] เช่นต้องการจอง memory ขนาด 32 Mbytes สำหรับทำเป็น cache ตัวอย่างคอนฟิกไฟล์จะเป็นดังนี้
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
query_cache_size=32M
หลังจากแก้ไขคอนฟิกไฟล์ /etc/my.cnf รันคำสั่ง service เพื่อรีสตาร์ต MySQL Server ใหม่
[root@db-server ~]# service mysql restart
Shutting down MySQL. [ OK ]
Starting MySQL. [ OK ]
ทดสอบรัน SELECT ครั้งแรกหลังเปิดการใช้ query cache
mysql> SELECT item_id, item_name FROM items WHERE item_name = 'GK809A0';
+---------+-----------+
| item_id | item_name |
+---------+-----------+
| 261351 | GK809A0 |
+---------+-----------+
1 row in set (0.23 sec)
ยังคงใช้เวลา 0.23 วินาที เพราะว่าครั้งแรกนี้ยังไม่มีข้อมูลใน cache เลย
สามารถใช้คำสั่ง SHOW STATUS เพื่อดูสถิติการใช้ cache ของ MySQL
mysql> SHOW STATUS WHERE Variable_name RLIKE 'Qcache';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33535344 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)
ค่าตัวแปร Qcache_not_cached เพิ่มเป็น 1 คือการ SELECT ครั้งนี้ไม่ได้ดึงข้อมูลจาก cache และค่า Qcache_inserts เป็น 1 คือเริ่มมีการใส่ผลลัพธ์เข้าไปใน cache
ทดลองรัน SELECT อีกครั้งนึง โดยพิมพ์ statement ให้เหมือนเดิมทุกอย่าง ทั้งตัวพิมพ์ใหญ่ พิมพ์เล็ก การเว้นวรรค ต้องเหมือนกันหมด ย้ำอีกที ต้องเหมือนกันหมด เพราะ MySQL ใช้ในการเปรียบเทียบกับ statement ที่เก็บไว้ใน cache
mysql> SELECT item_id, item_name FROM items WHERE item_name = 'GK809A0';
+---------+-----------+
| item_id | item_name |
+---------+-----------+
| 261351 | GK809A0 |
+---------+-----------+
1 row in set (0.00 sec)
เวลาที่ใช้กลายเป็น 0.00 วินาทีไปเลย เพราะว่าผลลัพธ์จากการ SELECT ครั้งนี้ MySQL ไปดึงมาจาก cache แทน
เราสามารถตรวจสอบว่าผลลัพธ์จากการ SELECT มาจาก cache โดยดูค่า Qcache_hits ที่เพิ่มขึ้น จากคำสั่ง SHOW STATUS
mysql> SHOW STATUS WHERE Variable_name RLIKE 'Qcache';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33535344 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)
บทความนี้ขอแนะนำอีกวีธีแต่จะมีผลทั้งเซิร์ฟเวอร์ฐานข้อมูล (Database Server) เลย ด้วยการเปิดคุณสมบัติ Query Cache ของ MySQL ให้มีการเก็บ SELECT statement และผลลัพธ์ที่ได้ไว้ใน cache ซึ่งถ้าเรารันคำสั่ง SELECT ครั้งต่อไปที่มี statement เหมือนกัน MySQL จะดึงผลลัพธ์การ query มาจาก cache โดยตรง ไม่ต้องไป query จาก table มาใหม่ ทำให้ผลการค้นหาเร็วขึ้นมาก
ถ้าข้อมูลใน table มีการเปลี่ยนแปลง cache ที่เก็บไว้จะถูกลบออกไป เพื่อให้ผลลัพธ์ในการค้นหาครั้งต่อไปถูกต้อง
ทดสอบก่อนการเปิดใช้ MySQL Query Cache
ในบทความนี้จะทดสอบกับ Fedora 10 โดย MySQL ที่ติดตั้งมาในแผ่นดีวีดี ไม่ได้เปิดคุณสมบัติ Query Cache ไว้ สามารถตรวจสอบได้จากคำสั่ง SHOW Variables ใน mysql
mysql> SHOW Variables WHERE Variable_name RLIKE 'query_cache';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
การที่จะใช้ Query Cache ได้นั้น ตัวแปร have_query_cache ต้องเท่ากับ ‘YES’ ตัวแปร ‘query_cache_type’ เท่ากับ ‘ON’ นอกจากนี้ต้องคอนฟิกค่า query_cache_size ด้วย เพื่อจองขนาด memory เพื่อใช้เก็บ query cache ค่า 0 คือการปิดคุณสมบัติ query cache
ทดสอบรัน SELECT ก่อนการเปิด query cache
หมายเหตุ
ตัวอย่างที่ทดสอบนี้ ไม่มีการสร้าง INDEX หรือคีย์ของฟิลด์ที่ชื่อ item_name
table ที่ใช้ทดสอบมีข้อมูลประมาณ 300,000 rows
mysql> SELECT item_id, item_name FROM items WHERE item_name = 'GK809A0';
+---------+-----------+
| item_id | item_name |
+---------+-----------+
| 261351 | GK809A0 |
+---------+-----------+
1 row in set (0.21 sec)
ลองรัน SELECT หลายๆ ครั้ง ด้วยคำสั่งเหมือนกัน เวลาที่ใช้จะใกล้เคียงกันประมาณ 0.21 วินาที
คอนฟิกค่า query_cache_size
แก้ไขไฟล์ /etc/my.cnf โดยเพิ่มคอนฟิก query_cache_size ลงไป ให้อยู่ภายใต้คอนฟิกของ [mysqld] เช่นต้องการจอง memory ขนาด 32 Mbytes สำหรับทำเป็น cache ตัวอย่างคอนฟิกไฟล์จะเป็นดังนี้
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
query_cache_size=32M
หลังจากแก้ไขคอนฟิกไฟล์ /etc/my.cnf รันคำสั่ง service เพื่อรีสตาร์ต MySQL Server ใหม่
[root@db-server ~]# service mysql restart
Shutting down MySQL. [ OK ]
Starting MySQL. [ OK ]
ทดสอบรัน SELECT ครั้งแรกหลังเปิดการใช้ query cache
mysql> SELECT item_id, item_name FROM items WHERE item_name = 'GK809A0';
+---------+-----------+
| item_id | item_name |
+---------+-----------+
| 261351 | GK809A0 |
+---------+-----------+
1 row in set (0.23 sec)
ยังคงใช้เวลา 0.23 วินาที เพราะว่าครั้งแรกนี้ยังไม่มีข้อมูลใน cache เลย
สามารถใช้คำสั่ง SHOW STATUS เพื่อดูสถิติการใช้ cache ของ MySQL
mysql> SHOW STATUS WHERE Variable_name RLIKE 'Qcache';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33535344 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)
ค่าตัวแปร Qcache_not_cached เพิ่มเป็น 1 คือการ SELECT ครั้งนี้ไม่ได้ดึงข้อมูลจาก cache และค่า Qcache_inserts เป็น 1 คือเริ่มมีการใส่ผลลัพธ์เข้าไปใน cache
ทดลองรัน SELECT อีกครั้งนึง โดยพิมพ์ statement ให้เหมือนเดิมทุกอย่าง ทั้งตัวพิมพ์ใหญ่ พิมพ์เล็ก การเว้นวรรค ต้องเหมือนกันหมด ย้ำอีกที ต้องเหมือนกันหมด เพราะ MySQL ใช้ในการเปรียบเทียบกับ statement ที่เก็บไว้ใน cache
mysql> SELECT item_id, item_name FROM items WHERE item_name = 'GK809A0';
+---------+-----------+
| item_id | item_name |
+---------+-----------+
| 261351 | GK809A0 |
+---------+-----------+
1 row in set (0.00 sec)
เวลาที่ใช้กลายเป็น 0.00 วินาทีไปเลย เพราะว่าผลลัพธ์จากการ SELECT ครั้งนี้ MySQL ไปดึงมาจาก cache แทน
เราสามารถตรวจสอบว่าผลลัพธ์จากการ SELECT มาจาก cache โดยดูค่า Qcache_hits ที่เพิ่มขึ้น จากคำสั่ง SHOW STATUS
mysql> SHOW STATUS WHERE Variable_name RLIKE 'Qcache';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33535344 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)
การเพิ่มความเร็วในการหาข้อมูลของ MySQL ด้วยวิธีการทำ Index
การเพิ่มความเร็วในการหาข้อมูลของ MySQL ด้วยวิธีการทำ Index
สวัสดีครับ ผมเขียนบทความนี้ขึ้นมาเนื่องจาก พบว่า Database ที่ผมใช้งานอยู่ Select ข้อมูลมาค่อนข้างช้า และผมก็ใช้วิธีการ Set Index เพิ่มเข้าไป มันก็สามารถทำงานได้เร็วขึ้นมากทีเดียว ก็เลยลองเขียนมาให้อ่านกันดู เผื่อใครเจอปัญหาทำนองนี้อยู่
ปกติแล้วหลังจากเขียนโปรแกรม php ที่ใช้กับ database MySQL เสร็จเรียบร้อยแล้ว ก็มักจะจบตรงนั้น พอใช้งานซักพัก ก็จะเริ่มพบปัญหาว่าทำไม ใช้ไปเรื่อยๆ มันก็ช้ามากขึ้น ซึ่งก็ต้องมาทำการจูนกันนิดหน่อย โดยการสร้าง Index ในบทความนี้ผมขอยกตัวอย่างว่า ผมใช้ table 1 อันก็แล้วกัน โดยมี field ดังข้างล่าง
แล้วเกิดผมอยากจะ Select ข้อมูลว่าให้แสดงพนักงานที่มีเงินเดือนมากกว่า 5,000 บาทโดยใช้คำสั่งข้างล่าง
select * from emp where salary>5000;
ในกรณีนี้ MySQL จะทำ full table scan คือ จะทำการอ่านข้อมูลจากทุก record ว่าตรงตามเงื่อนไขหรือเปล่า ซึ่งเราสามารถตรวจสอบดูได้ โดยใช้คำสั่ง
explain select * from emp where salary>5000;
ผลลัพธ์ที่ได้จะเป็นดังนี้
โดยช่อง table จะแสดงว่าชื่อ table ของ record ที่ถูกดึงข้อมูลมาคือ table อะไร ในตัวอย่างนี้คือ table ที่ชื่อ emp
ช่อง type จะแสดง วิธีการอ่านข้อมูลจาก table นั้น ในที่นี้ ALL หมายถึง database จะอ่านมาทุก record เพื่อนำมาเปรียบเทียบกับเงื่อนไข where
rows หมายถึงจำนวน row ที่ถูกอ่านขึ้นมา
ซึ่งหลังจากที่เรามีการ set index ที่ field salary แล้วทำการตรวจสอบอีกครั้งด้วยคำสั่ง
explain select * from emp where salary>5000;
จะได้ผลลัพธ์ดังนี้
โดย type จะเปลี่ยนจาก ALL มาเป็น ref ซึ่งหมายถึง database จะดึงมาเฉพาะ row ที่มีเงื่อนไขตรงกับ index ที่ set ไว้
possible_keys คือ index key ที่อาจจะใช้สำหรับการทำคำสั่งนี้
key คือ index key ที่ใช้จริงๆสำหรับการทำคำสั่งนี้
rows คือ จำนวน row ที่อ่านขึ้นมา ซึ่งพบว่าในกรณีนี้จะมีการ row ขี้นมาน้อยกว่าตอนที่อ่านขึ้นมาทั้งหมด ซึ่งก็จะช่วยให้ database ติดต่อกับ I/O น้อย ลง ก็จะช่วยให้การทำงานเร็วขึ้น
จริงๆแล้ว เนื้อหาเกี่ยวกับการใช้คำสั่ง explain ก็มีอยู่อีกพอสมควร เช่น ในกรณีที่มีการทำ join กันหลายๆ table ซึ่งในส่วนนี้จะมีการกล่าวเอาไว้ ใน manual ของ MySQL อยู่แล้ว
สวัสดีครับ ผมเขียนบทความนี้ขึ้นมาเนื่องจาก พบว่า Database ที่ผมใช้งานอยู่ Select ข้อมูลมาค่อนข้างช้า และผมก็ใช้วิธีการ Set Index เพิ่มเข้าไป มันก็สามารถทำงานได้เร็วขึ้นมากทีเดียว ก็เลยลองเขียนมาให้อ่านกันดู เผื่อใครเจอปัญหาทำนองนี้อยู่
ปกติแล้วหลังจากเขียนโปรแกรม php ที่ใช้กับ database MySQL เสร็จเรียบร้อยแล้ว ก็มักจะจบตรงนั้น พอใช้งานซักพัก ก็จะเริ่มพบปัญหาว่าทำไม ใช้ไปเรื่อยๆ มันก็ช้ามากขึ้น ซึ่งก็ต้องมาทำการจูนกันนิดหน่อย โดยการสร้าง Index ในบทความนี้ผมขอยกตัวอย่างว่า ผมใช้ table 1 อันก็แล้วกัน โดยมี field ดังข้างล่าง
| id | INT10 | (Primary) |
| fname | VARCHAR30 | (Primary) |
| lname | VARCHAR30 | (Primary) |
| salary | INT10 | (Primary) |
แล้วเกิดผมอยากจะ Select ข้อมูลว่าให้แสดงพนักงานที่มีเงินเดือนมากกว่า 5,000 บาทโดยใช้คำสั่งข้างล่าง
select * from emp where salary>5000;
ในกรณีนี้ MySQL จะทำ full table scan คือ จะทำการอ่านข้อมูลจากทุก record ว่าตรงตามเงื่อนไขหรือเปล่า ซึ่งเราสามารถตรวจสอบดูได้ โดยใช้คำสั่ง
explain select * from emp where salary>5000;
ผลลัพธ์ที่ได้จะเป็นดังนี้
| table | type | possible_keys | key | key_len | ref | rows | Extra |
| emp | ALL | 2073 | where used |
โดยช่อง table จะแสดงว่าชื่อ table ของ record ที่ถูกดึงข้อมูลมาคือ table อะไร ในตัวอย่างนี้คือ table ที่ชื่อ emp
ช่อง type จะแสดง วิธีการอ่านข้อมูลจาก table นั้น ในที่นี้ ALL หมายถึง database จะอ่านมาทุก record เพื่อนำมาเปรียบเทียบกับเงื่อนไข where
rows หมายถึงจำนวน row ที่ถูกอ่านขึ้นมา
ซึ่งหลังจากที่เรามีการ set index ที่ field salary แล้วทำการตรวจสอบอีกครั้งด้วยคำสั่ง
explain select * from emp where salary>5000;
จะได้ผลลัพธ์ดังนี้
| table | type | possible_keys | key | key_len | ref | rows | Extra |
| emp | ref | salary | salary | 10 | const | 1247 |
โดย type จะเปลี่ยนจาก ALL มาเป็น ref ซึ่งหมายถึง database จะดึงมาเฉพาะ row ที่มีเงื่อนไขตรงกับ index ที่ set ไว้
possible_keys คือ index key ที่อาจจะใช้สำหรับการทำคำสั่งนี้
key คือ index key ที่ใช้จริงๆสำหรับการทำคำสั่งนี้
rows คือ จำนวน row ที่อ่านขึ้นมา ซึ่งพบว่าในกรณีนี้จะมีการ row ขี้นมาน้อยกว่าตอนที่อ่านขึ้นมาทั้งหมด ซึ่งก็จะช่วยให้ database ติดต่อกับ I/O น้อย ลง ก็จะช่วยให้การทำงานเร็วขึ้น
จริงๆแล้ว เนื้อหาเกี่ยวกับการใช้คำสั่ง explain ก็มีอยู่อีกพอสมควร เช่น ในกรณีที่มีการทำ join กันหลายๆ table ซึ่งในส่วนนี้จะมีการกล่าวเอาไว้ ใน manual ของ MySQL อยู่แล้ว
สมัครสมาชิก:
บทความ (Atom)