บทความนี้สำรวจแนวคิดในการปรับปรุงประสิทธิภาพฐานข้อมูล MySQL และนำเสนอโซลูชันเฉพาะจาก 8 ด้าน
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
ตั้งค่าเส้นทางข้อมูลและวางไฟล์ข้อมูลฐานข้อมูลในไดเร็กทอรี NFS ที่ใช้ร่วมกัน (เซิร์ฟเวอร์ NAS)
ต้องวางไฟล์ PID และ innioDB ในไดเร็กทอรีภายในเครื่องของเซิร์ฟเวอร์เพื่อเริ่มและหยุดบริการตามปกติ:
1125 vi /etc/ my.cnf
[mysqld]
#จะติดตั้งข้อมูลฐานข้อมูลได้ที่ไหน
datadir=/data/mysqldata
#จะติดตั้งเอ็นจิ้น innoDB ได้ที่ไหน
innodb_data_home_dir = /usr/local/mysql/data
innodb_log_group_home_dir= /usr/local/mysql/data
innodb_data_file_path=ibdata1: 50M;ibdata2:50M:autoextend
1106 cp ./support-files/mysql.server /etc/rc.d/init.d/
vi /etc/rc.d/init.d/mysql.server
การคอมไพล์ที่เกี่ยวข้อง รายการเริ่มต้นจาก 222 บรรทัดสองบรรทัดวางไฟล์ PID ไว้ในไดเร็กทอรีท้องถิ่นของเซิร์ฟเวอร์:
pid_file=/usr/local/mysql/data/mysqlmanager-`/bin/hostname`.pid
server_pid_file=/usr/local/mysql /data/`/bin/ hostname`.pid
ติดตั้งฐานข้อมูลพื้นฐานของ MySQL:
1123 mount 10.4.66.251:/data /data
1124 mkdir /data/mysqldata
1127 ./scripts/mysql_install_db --user=mysql
1145 chown -R mysql .mysql /data/mysqldata/
หากเป็นปกติ คุณจะเห็นว่า mysql เริ่มทำงานตามปกติ
1146 /etc/rc.d/init.d/mysql.server start
1146 /etc/rc.d/init.d/mysql.server หยุด
กำหนดค่าความพร้อมใช้งานสูงของ HA อย่าตั้งค่าการเมานต์ NFS และบริการ mysql จะทำงานโดยอัตโนมัติเมื่อเริ่มต้นเซิร์ฟเวอร์
5. การล็อคตาราง
แม้ว่าธุรกรรมจะเป็นวิธีที่ดีมากในการรักษาความสมบูรณ์ของฐานข้อมูล เนื่องจากความพิเศษเฉพาะตัวของมัน ประสิทธิภาพของฐานข้อมูลโดยเฉพาะในระยะเวลาที่ยาวนานมาก เนื่องจากฐานข้อมูลจะถูกล็อคในระหว่างการทำธุรกรรม คำขอของผู้ใช้อื่น ๆ จึงสามารถรอได้จนกว่าธุรกรรมจะสิ้นสุดลงเท่านั้น หากมีการใช้ระบบฐานข้อมูลโดยผู้ใช้เพียงไม่กี่ราย ผลกระทบของธุรกรรมจะไม่กลายเป็นปัญหาใหญ่ แต่หากผู้ใช้หลายพันรายเข้าถึงระบบฐานข้อมูลพร้อมๆ กัน เช่น การเข้าถึงเว็บไซต์อีคอมเมิร์ซ ก็จะทำให้เกิดปัญหาร้ายแรง การตอบสนองล่าช้า
ที่จริงแล้ว ในบางกรณีเราสามารถได้รับประสิทธิภาพที่ดีขึ้นได้โดยการล็อคโต๊ะ ตัวอย่างต่อไปนี้ใช้วิธี lock table เพื่อทำหน้าที่ธุรกรรมให้สมบูรณ์ในตัวอย่างก่อนหน้านี้
ล็อครายการสินค้าคงคลังตาราง
เลือกจำนวนจากสินค้าคงคลัง
WHEREItem='หนังสือ';
-
อัปเดตจำนวนชุดสินค้าคงคลัง = 11
WHEREItem='หนังสือ';
ปลดล็อคตาราง
ที่นี่ เราใช้คำสั่ง SELECT เพื่อดึงข้อมูลเริ่มต้น และใช้คำสั่ง UPDATE เพื่ออัปเดตค่าใหม่ลงในตารางผ่านการคำนวณบางอย่าง คำสั่ง LOCK TABLE ที่มีคีย์เวิร์ด WRITE ช่วยให้มั่นใจได้ว่าจะไม่มีการเข้าถึงพื้นที่โฆษณาอื่น ๆ ที่จะแทรก อัปเดต หรือลบ ก่อนที่จะดำเนินการคำสั่ง UNLOCK TABLES
6.
วิธีการใช้คีย์นอกเพื่อล็อคตารางสามารถรักษาความสมบูรณ์ของข้อมูลได้ แต่ไม่สามารถรับประกันความเกี่ยวข้องของข้อมูลได้ ในเวลานี้เราสามารถใช้คีย์ต่างประเทศได้ ตัวอย่างเช่น คีย์นอกสามารถรับประกันได้ว่าบันทึกการขายแต่ละรายการชี้ไปที่ลูกค้าปัจจุบัน ที่นี่ Foreign Key สามารถแมป CustomerID ในตาราง customerinfo กับ CustomerID ในตาราง Salesinfo บันทึกใดๆ ที่ไม่มี CustomerID ที่ถูกต้องจะไม่ได้รับการอัปเดตหรือแทรกลงใน Salesinfo
สร้างข้อมูลลูกค้าตาราง
-
CustomerID INT ไม่เป็นโมฆะ
คีย์หลัก (รหัสลูกค้า)
) TYPE = INNODB;
สร้างข้อมูลการขายของตาราง
-
SalesID INT ไม่เป็นโมฆะ
รหัสลูกค้า INT ไม่เป็นโมฆะ
คีย์หลัก (รหัสลูกค้า, รหัสการขาย)
รหัสต่างประเทศ (รหัสลูกค้า) อ้างอิงข้อมูลลูกค้า
(รหัสลูกค้า) บน DELETECASCADE
) ประเภท = 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 * จากคำสั่งซื้อโดยที่ปี (วันที่สั่งซื้อ) <2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01";
สถานการณ์เดียวกันนี้จะเกิดขึ้นเมื่อคำนวณฟิลด์ตัวเลข:
SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;
สองแบบสอบถามด้านบนยังส่งคืนผลลัพธ์เดียวกัน แต่แบบสอบถามหลังจะเร็วกว่าแบบสอบถามก่อนหน้ามาก ประการที่สาม เมื่อค้นหาฟิลด์อักขระ บางครั้งเราใช้ LIKE คีย์เวิร์ดและไวด์การ์ด แม้ว่าวิธีการนี้จะง่าย แต่ก็ทำให้ประสิทธิภาพของระบบลดลงด้วย ตัวอย่างเช่น แบบสอบถามต่อไปนี้จะเปรียบเทียบทุกระเบียนในตาราง
SELECT * จากหนังสือ
โดยที่ชื่อเช่น "MySQL%"
แต่ถ้าคุณใช้แบบสอบถามต่อไปนี้ผลลัพธ์ที่ส่งคืนจะเหมือนกัน แต่ความเร็วจะเร็วกว่ามาก: ..
SELECT * FROM books
WHERE name>="MySQL" และ name<"MySQM"
สุดท้ายนี้ คุณควรระมัดระวังเพื่อหลีกเลี่ยงไม่ให้ MySQL ทำการแปลงประเภทอัตโนมัติในแบบสอบถาม เนื่องจากกระบวนการแปลงจะทำให้ดัชนีไม่มีประสิทธิภาพเช่นกัน