เราเริ่มด้วยโครงสร้างฐานข้อมูลกัน ฐานข้อมูลนี้มีเพียงตารางเดียว ใช้ในการเก็บข้อมูลการใข้งานอินเตอร์เน็ทของนักศึกษา เริ่มด้วยโครงสร้างตารางกันก่อน
MYSQL
- CREATE TABLE `inet_usage` (
- `no` INT(4) NOT NULL,
- `id` VARCHAR(10) NOT NULL,
- `DATE` VARCHAR(8) NOT NULL,
- `USAGE` TEXT NOT NULL,
- `acc` TEXT NOT NULL,
- `LEFT` TEXT NOT NULL,
- `opt` TEXT NOT NULL,
- `workstation` TEXT NOT NULL
- ) ENGINE=MyISAM;
- no เก็บลำดับของ record
- id เก็บรหัสนักศึกษา
- date วันที่บันทึก
- usage จำนวนเวลาที่เข้าใช้
- acc ประเภท account
- left จำนวน account ที่เหลือ
- opt Information
- workstation หมายเลขเครื่อง
| no | id | date | usage | acc | left | opt | workstation |
| 44 | 5021040076 | 01/11/50 | 1 h | 8000 h | 7979 h 43 min | NetMaster | iNet58 |
| 45 | 5021040068 | 01/11/50 | 1 h | 8000 h | 7987 h 20 min | NetMaster | iNet32 |
ซึ่งมีข้อมูลประมาณ 4,500+ records จะเห็นว่าเป็นการเก็บข้อมูลในลักษณะของ log แต่ว่าต้องการสร้างรายงาน เพื่อใช้ในการวิเคราะห์ข้อมูลต่อไป เรามาวิเคราะห์การเลือกชนิดข้อมูลกัน
เริ่มจาก no ที่ใช้เก็บลำดับของ record นั้นโดยปกติแล้วการเก็บข้อมูลแบบ log นั้นไม่จำเป็นต้องมีลำดับก็ได้เนื่องจากเราไม่จำเป็นต้องใช้ในการ index เพื่อแก้ไขเป็นราย record แต่ในกรณีนี้ เมื่อต้องการสร้างให้มีแต่ไม่มีการระบุว่าเป็น auto_increment ทำให้เราต้องระบุเลขลงไปเองโดยการคำนวนจาก record ทั้งหมดแล้ว +1 แล้วจึงบันทึกลงไป อย่างน้อยก็ต้องทำการนับ record ทั้งหมดเพิ่มอีก 1 query และถ้าเป็นเลขลำดับ record แล้วก็ไม่มีการระบุว่าเป็น PRIMARY KEY ซึ่งจะช่วยเรื่องความเร็วในการอ่านข้อมูล ซึ่งจะทดลองในตอนท้าย
date จากชื่อ field เป็นชื่อ Function ควรจะหลีกเลี่ยงแต่ก็สามารถใช้ได้ และชนิดข้อมูลที่เป็น Varchar และยังเก็บเป็นปี พ.ศ. ซึ่งไม่ใช่มาตรฐาน
usage เก็บเป็น Text ที่ไม่สามารถ Convert มาคำนวนได้ ซึ่งความจะเก็บเพียงตัวเลข (INT) การใช้งานจะเป็นนาทีหรือวินาทีก็ได้ เพื่ออาจจะใช้ในการคำนวนได้
acc เก็บเป็นชนิด Text จำนวนชั่วโมงในการใช้งาน ควรจะเก็บเป็นตัวเลข (INT) นาทีหรือวินาทีแทน
left เก็บเป็นชนิด Text จำนวนเวลาที่เหลือ ควรเก็บเป็นตัวเลข (INT) ของจำนวนนาทีหรือวินาทีก็ได้ เพื่อใช้ในการคำนวนได้
ทำไมควรเปลี่ยนข้อมูลจาก Varchar, Text เป็น INT?
เนื่องจาก Varchar และ Text เก็บตามจำนวนตัวอักษร +1 bytes ดังนั้นจะใช้พื้นที่ในการเก็บอย่างน้อย 7 bytes ใน field acc แต่ถ้าเป็น MEDIUMINT จะใช้เพียง 3 Bytes หรือ INT ก็ใช้เพียง 4 Bytes ต่างกันอย่างน้อย 3 Bytes/Column/Record ถ้าคำนวนเพียง Column acc เพียง field เดียว ประมาณ 4,500 Record จะได้ผลต่าง 4,500*3=13,500 Bytes (13.18 KB) นี่เพียงแค่ Column เดียว ทั้งยังง่ายต่อการสร้างเป็น INDEX ในการค้นหาด้วย
ความสัมพันธ์ระหว่าง id (รหัสนักศึกษา) และ acc นั้นเป็นเพียงข้อมูลคงที่ ไม่มีการเปลี่ยนแปลงในตารางเดียวกัน เราสามารถจับคู่ id, acc ในอีกตารางเพื่อใช้ในการอธิบายความสัมพันธ์ก็ได้ โดยใช้รหัสนักศึกษาเป็น PRIMARY KEY เพื่อลดความซ้ำซ้อนของข้อมูล ลดขนาดของตารางนี้ลงไปได้อีกประมาณ 4,500*7=31,500 Bytes (30.76 KB)
จากระบบเดิมนั้นเป็นการ Query เพื่อแสดงผลแต่ละค่าจะใช้ 1 query ทำให้ใช้ query ประมาณ 50+ queries ซึ่งเพิ่มภาระในการทำงานทั้งของ MySQL และ Network จากการ query จะใช้ประมาณ 0.008 วินาที/query จะใช้เวลาเฉพาะการ query ประมาณ 50*0.008=0.4 วินาที
เงื่อนไขที่ทางคุณ nobuts ต้องการคือข้อมูลรายงานจำนวนความการใช้งาน โดยแยกเป็นชั้นปีแผนกในเดือนที่ระบุ ก่อนอื่นเราต้องหาวิธีการในหาระบุวันที่เพื่อให้ง่ายต่อการค้นหา
เริ่มด้วยการแปลงวันที่ที่มีอยู่ในรูปแบบ Varchar ให้เป็นชนิดที่ทำการค้นหาได้ วิธีที่ดีที่สุดคือเปลี่ยนเป็นชนิด Date โดยการแบ่ง String ออกมาเป็นส่วนๆแล้วนำมาสร้างเป็นข้อมูล Date จึงสร้างเป็น subquery ได้ดังนี้
MYSQL
- SELECT no, STR_TO_DATE(CONCAT(SUBSTR(s.DATE, 1, 2 ), '/', SUBSTR(s.DATE, 4, 2 ), '/', (CAST(SUBSTR(s.DATE, 7, 2 ) AS UNSIGNED) - 43)), '%d/%m/%y') AS udate FROM inet_usage
เราเรียก field no ออกมาด้วยเพื่อใช้ในการหาความสัมพันธ์ของข้อมูล และข้อมูลวันที่เก็บไว้ใน alias udate ครับ และเราสามารถระบุชั้นปีและแผนกได้โดย
MYSQL
- SELECT SUBSTR(id, 1, 3) AS uYear, SUBSTR(id, 4, 3) AS uDept FROM inet_usage
โดยอักษร 3 ตัวแรกเป็นตัวบอกชั้นปี และ 3 ตัวถัดมาเป็นตัวบอกแผนก เมื่อนำมารวมกันเราจะได้
MYSQL
- SELECT u.udate, SUBSTR(i.id, 1, 3) AS uYear, SUBSTR(i.id, 4,3) AS uDept, COUNT(i.no) AS total FROM inet_usage i INNER JOIN (SELECT no, STR_TO_DATE(CONCAT(SUBSTR(s.DATE, 1, 2 ), '/', SUBSTR(s.DATE, 4, 2 ), '/', (CAST(SUBSTR(s.DATE, 7, 2 ) AS UNSIGNED) - 43)), '%d/%m/%y') AS udate FROM inet_usage s) AS u ON u.no = i.no WHERE MONTH(u.udate)=11 AND YEAR(u.udate)=2007 GROUP BY uYear, uDept, u.udate
จะได้ข้อมูลสรุปเป็นรายวันภายในเดือนพฤศจิกายนปี 2007 โดยแยกเป็นชั้นปีและแผนก การใช้ function COUNT() นั้นจะต้องใช้คู่กับ GROUP BY เท่านั้น แต่ยังไม่จบเนื่องจากผมจากการ query ใช้เวลา 3.7989 วินาที ช้ามากหากใช้ใน Application เนื่องจากตารางนี้ยังไม่มี INDEX ใดๆ เราจึงสร้าง INDEX ให้กับตารางโดยใน field ที่เราใช้ในการค้นหาเป็นหลัก และสร้าง PRIMARY KEY ที่ควรจะมีไว้ที่ field no หลังจากการสร้าง INDEX จะได้ผลความเร็วที่ 0.0965 วินาที เร็วขึ้น 3.7024 วินาที (97.46%)
เรายังไม่หยุดโดยการสร้าง VIEW ขึ้นมาเพื่อให้เร็วขึ้นอีก ทั้งยังง่ายต่อการใช้งานในอนาคตอื่นๆอีก โดยใช้คำสั่ง
MYSQL
- CREATE VIEW udate AS SELECT no, STR_TO_DATE(CONCAT(SUBSTR(DATE, 1, 2), '/', SUBSTR(DATE, 4, 2), '/', (CAST(CUBSTR(DATE, 7, 2) AS UNSIGNED) - 43)), '%d/%m/%y') AS udate FROM inet_usage
เราจะได้ VIEW ในชื่อ udate เอามาใช้แทน subquery ของคำสั่งเดิม และก็สร้าง VIEW สำหรับการแสดงข้อมูลทั้งหมด โดยใช้คำสั่ง
MYSQL
- CREATE VIEW v_usage AS SELECT udate.udate, SUBSTR(i.id, 1, 3) AS uYear, SUBSTR(i.id, 4,3) AS uDept, COUNT(i.no) AS total FROM inet_usage i INNER JOIN udate ON udate.no = i.no GROUP BY uYear, uDept, udate.udate
จะได้ VIEW ชื่อ v_usage เพื่อการแสดงผลทั้งหมด เราทดลองความเร็วด้วยการหาข้อมูลเดียวกันเพื่อเปรียบเทียบ โดยใช้คำสั่ง
MYSQL
- SELECT * FROM v_usage WHERE MONTH(udate)=11 AND YEAR(udate)=2007
ผลที่ได้คือ 0.0809 วินาที เร็วขึ้นอีก 0.0156 วินาที (16.17%) หรือเร็วเพิ่มขึ้นก่อนการ Optimize จำนวน 3.718 วินาที (97.87%) ผลการทดลองทั้งหมดเกิดจากข้อมูลน้อยๆ จะเห็นผลความแตกต่างเพิ่มขึ้นเมื่อจำนวน record เพิ่มขึ้น
คำเตือน เนื้อหาต่างๆ ในบทความ รวมถึงรูปภาพทั้งหมดในบทความนี้ เป็นความเห็นส่วนตัวของผู้เขียนแต่ละคน ซึ่งแต่ละคนได้ทำการลงทะเบียน และเขียนบทความลงใน Modoeye Articles นี้โดยไม่มีค่าธรรมเนียมใดๆ บทความเหล่านี้เป้าหมายเพื่อการศึกษา และความบันเทิงเท่านั้น การนำส่วนหนึ่งส่วนใดของบทความไปใช้งาน ควรทำการอ้างอิงถึงผู้เขียนและแหล่งที่มาด้วย