0021 | วิธีนับจำนวนแถว (record) ในตาราง

ดองไว้นานเชียะ = =’
ขออภัยด้วยครับ แบบว่าขี้เกียจเขียน เอ้ย งานยุ่งๆ
มหาลัยเปิดเทอมแล้ว ตารางเรียนเห็นแล้วแทบบ้า
เอาวันหยุดของตูคืนมา เอาวันหยุดของตูคืนมา T_T

เข้าประเด็นเลยดีกว่า

ช่วงนี้มีงานประเภทนี้เยอะครับ เห็นแล้วก็เซ็งไอ้คนเขียนเว็บคนเก่าที่ทำระบบมาจริงๆ
คุณพี่เขียนไว้อย่างนี้ครับ….

$sql = mysql_query("SELECT * FROM table");
$records = mysql_num_rows($sql);

ไอ้อันที่จริงมันก็ไม่ผิดอะไรหรอกครับถ้าจะใช้แบบนี้ เพราะ code มันก็ทำงานได้ปกติสุขดีของมัน
แต่… ใครจะรู้มั้ยครับ ว่า mysql มันต้องส่งข้อมูลมากขนาดไหน

สมมติว่า ตารางมีขนาด 1 MB
การ query นี้จะทำให้ mysql ต้องส่งข้อมูลขนาด 1 MB ให้ client ที่ทำการ query
แล้ว client จะเป็นผู้นับจำนวนแถวเอง
ลองคิดดูว่าถ้าตารางเราเกิดมีขนาด 10 MB หรือมีคนเข้าเยอะๆ นะครับ
สมมติ ตารางเดิม ขนาด 1 MB
แต่คนเข้า “แค่” นาทีละ 60 ครั้ง (ตกวินาทีละ 1 ครั้ง)
เท่ากับ Database Server ต้องส่งข้อมูลให้ client ถึงวินาทีละ 1 MB
…
ถ้าขนาดฐานข้อมูลใหญ่ขึ้น
ถ้าคนเข้าเยอะขึ้น… ลองคิดดูครับว่ามันจะเปลืองทรัพยากรระบบขนาดไหน

วิธีนับจำนวน record จริงๆ ที่ใช้กันทั่วไปในระบบใหญ่ๆ เค้าใช้กันแบบนี้ครับ

$sql = mysql_query("SELECT COUNT(*) FROM table");
$res = mysql_fetch_array($sql);
$records = $res[0];

จำนวนบรรทัดเยอะขึ้น 1 บรรทัด แต่การทำงานลดลงฮวบฮาบผิดกันลิบๆ

* คำสั่ง SELECT COUNT(*) ห้ามเอาไปใช้กับตารางประเภท InnoDB โดยเด็ดขาดนะครับ
มิเช่นนั้น database server อาจน็อคเอาได้ง่ายๆ

Tags: mysql, optimize, query

0019 | สารบัญนั้นสำคัญไฉน… (ว่าด้วย MySQL Index)

ดองไว้ตั้งนานแล้วครับ entry นี้
เคยเกริ่นๆ ไว้ตั้งนานแล้วด้วยแหละ

เรื่องนี้ถือเป็นเรื่องใหญ่มากๆ ครับในการออกแบบฐานข้อมูลบน MySQL
เนื่องด้วยมันจะมีผลต่อความเร็วในการค้นหาข้อมูลมาก ถึงมากที่สุด
เคสที่ยังใช้งานน้อยๆ เช่นมีการเรียกใช้แค่ นาทีละไม่ถึงสามสิบครั้ง อะไรอย่างนี้อาจจะไม่ค่อยเห็นผลเท่าไหร่
แต่พอมีการใช้งานเยอะๆ ดูสิครับ… ไม่อยากบรรยายถึงความเละสุดอนาถเลยแหละ

ทีนี้ จะดูยังไงว่าเราควรจะสร้างฐานข้อมูลยังไง ออกแบบยังไงให้มีประสิทธิภาพ
ตรงนี้ต้องดูการเรียกใช้งานของเราครับ เช่น…

อ้อ ลืมไป ยกตัวอย่างไม่ได้ ต้องสร้างตารางก่อน

CREATE TABLE users ( id INT NOT NULL DEFAULT '0' AUTO_INCREMENT, username VARCHAR(24) NOT NULL DEFAULT '', password VARCHAR(32) NOT NULL DEFAULT '', email VARCHAR(64) NOT NULL DEFAULT '', PRIMARY KEY(id) ) Engine=MyISAM;

CREATE TABLE users ( id int NOT NULL DEFAULT '0' auto_increment, username varchar(24) NOT NULL DEFAULT '', password varchar(32) NOT NULL DEFAULT '', email varchar(64) NOT NULL DEFAULT '', PRIMARY KEY(id) ) Engine=MyISAM;

ในการนี้เราจะได้ตาราง users มี 4 field คือ id, username, password และ email
ทีนี้ สมมติเราต้องการดึงค่า email ออกมาจากตาราง โดยหาจาก username ก็ต้อง query แบบนี้

SELECT email FROM users WHERE username = 'xxxxxxx';

SELECT email FROM users WHERE username = 'xxxxxxx';

เงื่อนไขการค้นหาก็จะเป็น ค้นหาจาก ตาราง users โดยดูใน field username ที่มีค่าเท่ากับ ‘xxxxxxx’

กรณีที่เรายังไม่ได้ทำ Index ให้ตารางนี้…
MySQL จะเปิดตารางขึ้นมา(จาก harddisk) จากนั้นก็เริ่มตั้งแต่ record แรกสุด
เรียกข้อมูลใน field username ออกมาเทียบค่า ถ้าไม่ใช่ก็หาต่อไปเรื่อยๆ จนกว่าจะเจอ
ซึ่งกรณีเลวร้ายที่สุด คือค้นหาไม่เจอเลย จะทำให้ MySQL ต้องทำการอ่านข้อมูลจากตารางเยอะมาก
กลายเป็นว่า ต้องอ่านข้อมูลทั้งตารางเพื่อหาว่า user นี้อยู่ที่ record ไหน

สมมติพอทำ Index ปุ๊ป MySQL ก็จะเข้ามาอ่าน Index ที่มีอยู่
ซึ่งโดยปกติ index จะพยายามเก็บในแรมครับ แต่ก็มี file ที่เก็บบน harddisk เหมือนกัน
แล้วก็เทียบค่าไปเรื่อยๆ แต่แรมมันเร็วกว่า hdd เยอะครับ…
จากนั้น พอเจอแล้ว เจ้า index นี่ก็จะเป็นตัวบอกว่า ข้อมูลนี้อยู่ที่ตำแหน่งตรงไหนของแฟ้ม
ก็เลยทำให้ MySQL กระโดดไปเอาข้อมูลมาได้เลยครับ

คำสั่งเพิ่ม index ให้ตารางที่มีอยู่แล้วใช้อย่างนี้ครับ

ALTER TABLE users ADD INDEX (username)

ALTER TABLE users ADD INDEX (username)

.

เดี๋ยวจะมาอธิบายการตรวจสอบ query อีกที ง่วงมากเลยตอนนี้ T_T

Tags: mysql, optimize, query