1. เลือกแอตทริบิวต์ของฟิลด์ที่เกี่ยวข้องมากที่สุด
MySQL สามารถรองรับการเข้าถึงข้อมูลจำนวนมากได้ดี แต่โดยทั่วไป ยิ่งตารางในฐานข้อมูลมีขนาดเล็กเท่าใด การสืบค้นก็จะเร็วขึ้นเท่านั้น ดังนั้น เมื่อสร้างตาราง เพื่อให้ได้ประสิทธิภาพที่ดีขึ้น เราสามารถตั้งค่าความกว้างของฟิลด์ในตารางให้เล็กที่สุดได้ ตัวอย่างเช่น เมื่อกำหนดฟิลด์รหัสไปรษณีย์ หากคุณตั้งค่าเป็น CHAR(255) จะเป็นการเพิ่มพื้นที่ที่ไม่จำเป็นให้กับฐานข้อมูลอย่างชัดเจน แม้ว่าการใช้ประเภท VARCHAR จะซ้ำซ้อน เนื่องจาก CHAR(6) ถือว่าเสร็จสิ้นแล้ว ในทำนองเดียวกัน หากเป็นไปได้ เราควรใช้ MEDIUMINT แทน BIGIN เพื่อกำหนดฟิลด์จำนวนเต็ม
อีกวิธีในการปรับปรุงประสิทธิภาพคือการตั้งค่าฟิลด์ให้เป็น NOT NULL เมื่อเป็นไปได้ เพื่อให้ฐานข้อมูลไม่จำเป็นต้องเปรียบเทียบค่า NULL เมื่อดำเนินการค้นหาในอนาคต
สำหรับช่องข้อความบางช่อง เช่น "จังหวัด" หรือ "เพศ" เราสามารถกำหนดเป็นประเภท ENUM ได้ เนื่องจากใน MySQL ประเภท ENUM จะถือเป็นข้อมูลตัวเลข และข้อมูลตัวเลขจะถูกประมวลผลเร็วกว่าประเภทข้อความมาก ด้วยวิธีนี้เราสามารถปรับปรุงประสิทธิภาพของฐานข้อมูลได้
2. ใช้การรวม (JOIN) แทนแบบสอบถามย่อย (แบบสอบถามย่อย)
MySQL รองรับแบบสอบถามย่อย SQL เริ่มต้นจาก 4.1 เทคนิคนี้ช่วยให้คุณใช้คำสั่ง SELECT เพื่อสร้างคอลัมน์เดียวของผลลัพธ์คิวรี จากนั้นใช้ผลลัพธ์นี้เป็นเงื่อนไขตัวกรองในคิวรีอื่น ตัวอย่างเช่น หากเราต้องการลบลูกค้าที่ไม่มีคำสั่งซื้อใดๆ ในตารางข้อมูลลูกค้าพื้นฐาน เราสามารถใช้แบบสอบถามย่อยเพื่อดึงข้อมูล ID ของลูกค้าทั้งหมดที่ออกคำสั่งซื้อจากตารางข้อมูลการขายก่อน จากนั้นจึงส่งผลลัพธ์ไปที่ ข้อความค้นหาหลัก ดังที่แสดงด้านล่าง :
DELETE FROM customerinfo
WHERE CustomerID ไม่อยู่ใน (เลือก CustomerID จาก salesinfo)
การใช้แบบสอบถามย่อยสามารถดำเนินการ SQL จำนวนมากที่ต้องใช้หลายขั้นตอนในการดำเนินการในคราวเดียวในทางตรรกะ นอกจากนี้ยังสามารถหลีกเลี่ยงธุรกรรมหรือการล็อกตารางได้ และยังง่ายต่อการเขียนอีกด้วย อย่างไรก็ตาม ในบางกรณี แบบสอบถามย่อยสามารถแทนที่ได้ด้วยการเข้าร่วมที่มีประสิทธิภาพมากขึ้น (JOIN) ตัวอย่างเช่น สมมติว่าเราต้องการดึงข้อมูลผู้ใช้ทั้งหมดที่ไม่มีบันทึกการสั่งซื้อ เราสามารถใช้แบบสอบถามต่อไปนี้:
SELECT * FROM customerinfo
WHERE CustomerID ไม่อยู่ใน (เลือก CustomerID จาก salesinfo)
หากคุณใช้การเชื่อมต่อ (JOIN).. เพื่อตอบแบบสอบถามนี้ให้เสร็จสิ้น ความเร็วจะเร็วขึ้นมาก โดยเฉพาะอย่างยิ่งหากมีดัชนีบน CustomerID ในตาราง salesinfo ประสิทธิภาพจะดีกว่าดังนี้:
SELECT * FROM customerinfo
เข้าร่วมทางซ้าย salesinfoON customerinfo.CustomerID=salesinfo
รหัสลูกค้า
WHERE salesinfo.CustomerID IS NULL
Connection (JOIN).. สาเหตุที่ทำให้มีประสิทธิภาพมากกว่าก็คือ MySQL ไม่จำเป็นต้องสร้างตารางชั่วคราวในหน่วยความจำเพื่อทำการสืบค้นแบบลอจิคัลสองขั้นตอนนี้ให้เสร็จสิ้น
3. ใช้ยูเนี่ยน (UNION) เพื่อแทนที่ตารางชั่วคราวที่สร้างขึ้นด้วยตนเอง
MySQL รองรับการสืบค้น UNION ที่เริ่มต้นจากเวอร์ชัน 4.0 ซึ่งสามารถรวมการสืบค้น SELECT สองรายการขึ้นไปที่ต้องใช้ตารางชั่วคราวให้เป็นการสืบค้นเดียว เมื่อเซสชันการสืบค้นของลูกค้าสิ้นสุดลง ตารางชั่วคราวจะถูกลบโดยอัตโนมัติเพื่อให้แน่ใจว่าฐานข้อมูลเป็นระเบียบเรียบร้อยและมีประสิทธิภาพ เมื่อใช้ UNION เพื่อสร้างแบบสอบถาม เราจำเป็นต้องใช้ UNION เป็นคีย์เวิร์ดเพื่อเชื่อมต่อคำสั่ง SELECT หลายรายการ โปรดทราบว่าจำนวนฟิลด์ในคำสั่ง SELECT ทั้งหมดจะต้องเท่ากัน ตัวอย่างต่อไปนี้แสดงให้เห็นถึงแบบสอบถามโดยใช้ UNION
เลือกชื่อ โทรศัพท์จากไคลเอนต์
ยูเนี่ยน
เลือกชื่อ วันเกิด จากผู้เขียน
ยูเนี่ยน
SELECT Name, Supplier FROM product
4. Transactions
แม้ว่าเราจะสามารถใช้ sub-queries, การเชื่อมต่อ (JOIN) และ union (UNION) เพื่อสร้าง queries ต่างๆ ได้หลากหลาย แต่การดำเนินการฐานข้อมูลทั้งหมดอาจไม่สามารถทำได้ด้วยเพียงข้อมูลเดียว หรือสามารถทำได้ด้วยเพียงข้อมูลเดียว คำสั่ง SQL บางส่วน บ่อยครั้งที่จำเป็นต้องใช้ชุดคำสั่งเพื่อให้งานบางประเภทเสร็จสมบูรณ์ แต่ในกรณีนี้ เมื่อคำสั่งบางอย่างในบล็อกคำสั่งนี้ทำงานไม่ถูกต้อง การดำเนินการของบล็อคคำสั่งทั้งหมดจะไม่แน่นอน ลองนึกภาพว่าคุณต้องการแทรกข้อมูลบางอย่างลงในตารางที่เกี่ยวข้องกันสองตารางพร้อมกัน สิ่งนี้อาจเกิดขึ้น: หลังจากที่ตารางแรกได้รับการอัปเดตสำเร็จ สถานการณ์ที่ไม่คาดคิดก็เกิดขึ้นในฐานข้อมูล ทำให้การดำเนินการในตารางที่สองไม่เสร็จสมบูรณ์ ด้วยวิธีนี้ข้อมูลจะไม่สมบูรณ์และแม้กระทั่งข้อมูลในฐานข้อมูลก็จะถูกทำลาย เพื่อหลีกเลี่ยงสถานการณ์นี้ คุณควรใช้ธุรกรรม หน้าที่ของมันคือ: ทุกคำสั่งในบล็อกคำสั่งจะสำเร็จหรือล้มเหลว กล่าวอีกนัยหนึ่งสามารถรักษาความสอดคล้องและความสมบูรณ์ของข้อมูลในฐานข้อมูลได้ สิ่งต่างๆ เริ่มต้นด้วยคีย์เวิร์ด BEGIN และลงท้ายด้วยคีย์เวิร์ด COMMIT หากการดำเนินการ SQL ล้มเหลวในช่วงเวลานี้ คำสั่ง ROLLBACK สามารถกู้คืนฐานข้อมูลเป็นสถานะก่อนที่ BEGIN จะเริ่มทำงาน
เริ่ม;
แทรกลงในข้อมูลการขาย SET CustomerID=14;
อัปเดตจำนวนชุดสินค้าคงคลัง = 11
WHERE item='หนังสือ';
ธุรกรรม
คือเมื่อผู้ใช้หลายคนใช้แหล่งข้อมูลเดียวกันในเวลาเดียวกัน สามารถใช้วิธีการล็อคฐานข้อมูลเพื่อให้ผู้ใช้มีวิธีการเข้าถึงที่ปลอดภัย ซึ่งสามารถมั่นใจได้ว่าการดำเนินงานของผู้ใช้จะไม่ถูกบล็อก โดยผู้ใช้รายอื่นรบกวน
5. การล็อกตาราง
แม้ว่าธุรกรรมจะเป็นวิธีที่ดีมากในการรักษาความสมบูรณ์ของฐานข้อมูล เนื่องจากมีความเฉพาะตัว แต่บางครั้งอาจส่งผลต่อประสิทธิภาพของฐานข้อมูล โดยเฉพาะอย่างยิ่งในระบบแอปพลิเคชันขนาดใหญ่ เนื่องจากฐานข้อมูลจะถูกล็อคในระหว่างการทำธุรกรรม คำขอของผู้ใช้อื่น ๆ จึงสามารถรอได้จนกว่าธุรกรรมจะสิ้นสุดลงเท่านั้น หากมีการใช้ระบบฐานข้อมูลโดยผู้ใช้เพียงไม่กี่ราย ผลกระทบของธุรกรรมจะไม่กลายเป็นปัญหาใหญ่ แต่หากผู้ใช้หลายพันรายเข้าถึงระบบฐานข้อมูลพร้อมๆ กัน เช่น การเข้าถึงเว็บไซต์อีคอมเมิร์ซ ก็จะทำให้เกิดปัญหาร้ายแรง การตอบสนองล่าช้า
ที่จริงแล้ว ในบางกรณีเราสามารถได้รับประสิทธิภาพที่ดีขึ้นได้โดยการล็อคโต๊ะ ตัวอย่างต่อไปนี้ใช้วิธี lock table เพื่อทำหน้าที่ธุรกรรมให้สมบูรณ์ในตัวอย่างก่อนหน้านี้
ล็อครายการสินค้าคงคลังตาราง
เลือกจำนวนจากสินค้าคงคลัง
WHEREItem='หนังสือ';
-
อัปเดตจำนวนชุดสินค้าคงคลัง = 11
WHEREItem='หนังสือ';
ปลดล็อคตาราง
ที่นี่เราใช้คำสั่ง SELECT เพื่อดึงข้อมูลเริ่มต้น และผ่านการคำนวณบางอย่าง ใช้คำสั่ง UPDATE เพื่ออัปเดตค่าใหม่ลงในตาราง คำสั่ง LOCK TABLE ที่มีคีย์เวิร์ด WRITE ช่วยให้มั่นใจได้ว่าจะไม่มีการเข้าถึงพื้นที่โฆษณาอื่น ๆ ที่จะแทรก อัปเดต หรือลบ ก่อนที่จะดำเนินการคำสั่ง UNLOCK TABLES
6.
วิธีการใช้คีย์นอกเพื่อล็อคตารางสามารถรักษาความสมบูรณ์ของข้อมูลได้ แต่ไม่สามารถรับประกันความเกี่ยวข้องของข้อมูลได้ ในเวลานี้เราสามารถใช้คีย์ต่างประเทศได้ ตัวอย่างเช่น คีย์นอกสามารถรับประกันได้ว่าบันทึกการขายแต่ละรายการชี้ไปที่ลูกค้าปัจจุบัน ที่นี่ Foreign Key สามารถแมป CustomerID ในตาราง customerinfo กับ CustomerID ในตาราง Salesinfo บันทึกใดๆ ที่ไม่มี CustomerID ที่ถูกต้องจะไม่ได้รับการอัปเดตหรือแทรกลงใน Salesinfo
สร้างข้อมูลลูกค้าตาราง
-
รหัสลูกค้า INT ไม่เป็นโมฆะ
คีย์หลัก (รหัสลูกค้า)
) TYPE = INNODB;
สร้างข้อมูลการขายของตาราง
-
SalesID INT ไม่เป็นโมฆะ
รหัสลูกค้า INT ไม่เป็นโมฆะ
คีย์หลัก (รหัสลูกค้า, รหัสการขาย)
รหัสต่างประเทศ (รหัสลูกค้า) อ้างอิงข้อมูลลูกค้า
(รหัสลูกค้า) บน DELETECASCADE
) TYPE = INNODB;
สังเกตพารามิเตอร์ "ON DELETE CASCADE" ในตัวอย่าง พารามิเตอร์นี้ช่วยให้แน่ใจว่าเมื่อบันทึกลูกค้าในตารางข้อมูลลูกค้าถูกลบ บันทึกทั้งหมดที่เกี่ยวข้องกับลูกค้าในตารางข้อมูลการขายจะถูกลบโดยอัตโนมัติด้วย หากคุณต้องการใช้คีย์นอกใน MySQL คุณต้องจำไว้ว่าต้องกำหนดประเภทตารางเป็นประเภท InnoDB ที่ปลอดภัยต่อธุรกรรมเมื่อสร้างตาราง ประเภทนี้ไม่ใช่ประเภทเริ่มต้นสำหรับตาราง MySQL วิธีการที่กำหนดไว้คือการเพิ่ม TYPE=INNODB ให้กับคำสั่ง CREATE TABLE ดังแสดงในตัวอย่าง
7. การใช้ดัชนี
เป็นวิธีการทั่วไปในการปรับปรุงประสิทธิภาพของฐานข้อมูล ช่วยให้เซิร์ฟเวอร์ฐานข้อมูลดึงข้อมูลแถวที่ต้องการได้เร็วกว่าที่ไม่มีดัชนี โดยเฉพาะอย่างยิ่งหากคำสั่งการสืบค้นประกอบด้วย MAX(), MIN() และ ORDERBY การปรับปรุงประสิทธิภาพมีความชัดเจนมากขึ้น แล้วฟิลด์ไหนควรได้รับการจัดทำดัชนี? โดยทั่วไปแล้ว ดัชนีควรถูกสร้างขึ้นบนฟิลด์ที่จะใช้สำหรับ JOIN การพิจารณา WHERE และการเรียงลำดับ ORDER BY พยายามอย่าสร้างดัชนีเขตข้อมูลในฐานข้อมูลที่มีค่าซ้ำกันจำนวนมาก สำหรับฟิลด์ประเภท ENUM มีค่าที่ซ้ำกันจำนวนมาก เช่น ฟิลด์ "จังหวัด".. ในข้อมูลลูกค้า การสร้างดัชนีในฟิลด์ดังกล่าวจะไม่เป็นประโยชน์ ในทางกลับกัน เป็นไปได้ ลดประสิทธิภาพของฐานข้อมูล เราสามารถสร้างดัชนีที่เหมาะสมในเวลาเดียวกันเมื่อสร้างตาราง หรือใช้ ALTER TABLE หรือ CREATE INDEX เพื่อสร้างดัชนีในภายหลัง นอกจากนี้ MySQL ยังรองรับการจัดทำดัชนีข้อความแบบเต็มและการค้นหาที่เริ่มต้นด้วยเวอร์ชัน 3.23.23 ดัชนีข้อความแบบเต็มเป็นดัชนีประเภท FULLTEXT ใน MySQL แต่สามารถใช้ได้กับตารางประเภท MyISAM เท่านั้น สำหรับฐานข้อมูลขนาดใหญ่ การโหลดข้อมูลลงในตารางโดยไม่มีดัชนี FULLTEXT จะทำได้เร็วมาก จากนั้นใช้ ALTER TABLE หรือ CREATE INDEX เพื่อสร้างดัชนี แต่ถ้าคุณโหลดข้อมูลลงในตารางที่มีดัชนี FULLTEXT อยู่แล้ว กระบวนการดำเนินการจะช้ามาก
8. คำสั่งการสืบค้นที่ปรับให้เหมาะสม
ในกรณีส่วนใหญ่ การใช้ดัชนีสามารถปรับปรุงความเร็วของการสืบค้นได้ แต่หากใช้คำสั่ง SQL อย่างไม่เหมาะสม ดัชนีจะไม่สามารถมีบทบาทตามสมควรได้ ต่อไปนี้เป็นประเด็นหลายประการที่ควรให้ความสนใจ ขั้นแรก วิธีที่ดีที่สุดคือดำเนินการเปรียบเทียบระหว่างฟิลด์ประเภทเดียวกัน ก่อน MySQL เวอร์ชัน 3.23 นี่เป็นเงื่อนไขที่จำเป็นด้วยซ้ำ ตัวอย่างเช่น ไม่สามารถเปรียบเทียบฟิลด์ INT ที่จัดทำดัชนีกับฟิลด์ BIGINT ได้ แต่เป็นกรณีพิเศษ เมื่อฟิลด์ประเภท CHAR และฟิลด์ประเภท VARCHAR มีขนาดเท่ากัน ก็สามารถเปรียบเทียบได้ ประการที่สอง พยายามอย่าใช้ฟังก์ชันเพื่อดำเนินการกับฟิลด์ที่จัดทำดัชนีไว้
ตัวอย่างเช่น เมื่อใช้ฟังก์ชัน YEAE() ในฟิลด์ประเภท DATE ดัชนีจะไม่ทำงานอย่างที่ควรจะเป็น ดังนั้น แม้ว่าแบบสอบถามสองรายการต่อไปนี้จะส่งกลับผลลัพธ์เดียวกัน แต่แบบสอบถามหลังจะเร็วกว่าแบบสอบถามแรกมาก
SELECT * จากคำสั่งซื้อ WHERE YEAR(OrderDate)＜2001;
SELECT
*
FROM
order WHERE OrderDate＜"2001-01-01";
* จากสินค้าคงคลัง WHERE Amount<24*7;
แบบสอบถามสองรายการด้านบนยังส่งคืนผลลัพธ์เดียวกัน แต่แบบสอบถามหลังจะเร็วกว่าแบบสอบถามก่อนหน้ามาก ประการที่สาม เมื่อค้นหาฟิลด์อักขระ บางครั้งเราใช้ LIKE คีย์เวิร์ดและไวด์การ์ด แม้ว่าวิธีการนี้จะง่าย แต่ก็ทำให้ประสิทธิภาพของระบบลดลงด้วย ตัวอย่างเช่น แบบสอบถามต่อไปนี้จะเปรียบเทียบทุกระเบียนในตาราง
SELECT * จากหนังสือ
โดยที่ชื่อเช่น "MySQL%"
แต่ถ้าคุณใช้แบบสอบถามต่อไปนี้ผลลัพธ์ที่ส่งคืนจะเหมือนกัน แต่ความเร็วจะเร็วกว่ามาก:
SELECT * FROM books
WHERE name>="MySQL" และ name<"MySQM"
สุดท้ายนี้ คุณควรระมัดระวังเพื่อหลีกเลี่ยงไม่ให้ MySQL ทำการแปลงประเภทอัตโนมัติในแบบสอบถาม เนื่องจากกระบวนการแปลงจะทำให้ดัชนีไม่มีประสิทธิภาพเช่นกัน