تستكشف هذه المقالة أفكارًا لتحسين أداء قاعدة بيانات MySQL وتوفر حلولًا محددة من 8 جوانب.
1. حدد سمات الحقل الأكثر قابلية للتطبيق.
يمكن لـ MySQL أن تدعم الوصول إلى كميات كبيرة من البيانات، ولكن بشكل عام، كلما كان الجدول أصغر في قاعدة البيانات، كلما كانت الاستعلامات التي يتم تنفيذها عليه أسرع. لذلك، عند إنشاء جدول، من أجل الحصول على أداء أفضل، يمكننا ضبط عرض الحقول في الجدول ليكون صغيرًا قدر الإمكان. على سبيل المثال، عند تحديد حقل الرمز البريدي، إذا قمت بتعيينه على CHAR(255)، فمن الواضح أنه سيضيف مساحة غير ضرورية إلى قاعدة البيانات. حتى أن استخدام نوع VARCHAR يعد زائدًا عن الحاجة، لأن المهمة CHAR(6) جيدة. وبالمثل، إذا أمكن، يجب علينا استخدام MEDIUMINT بدلاً من BIGIN لتحديد حقول الأعداد الصحيحة.
هناك طريقة أخرى لتحسين الكفاءة وهي تعيين الحقول على NOT NULL عندما يكون ذلك ممكنًا، بحيث لا تحتاج قاعدة البيانات إلى مقارنة القيم NULL عند تنفيذ الاستعلامات في المستقبل.
بالنسبة لبعض الحقول النصية، مثل "المقاطعة" أو "الجنس"، يمكننا تعريفها كأنواع ENUM. لأنه في MySQL، يتم التعامل مع نوع ENUM كبيانات رقمية، وتتم معالجة البيانات الرقمية بشكل أسرع بكثير من أنواع النص. وبهذه الطريقة، يمكننا تحسين أداء قاعدة البيانات.
2. استخدم الصلات (JOIN) بدلاً من الاستعلامات الفرعية (Sub-Queries)
التي تدعم MySQL استعلامات SQL الفرعية بدءًا من 4.1. تسمح لك هذه التقنية باستخدام عبارة SELECT لإنشاء عمود واحد من نتائج الاستعلام، ثم استخدام هذه النتيجة كشرط تصفية في استعلام آخر. على سبيل المثال، إذا أردنا حذف العملاء الذين ليس لديهم أي طلبات في جدول معلومات العملاء الأساسي، فيمكننا استخدام استعلام فرعي لاسترداد معرفات جميع العملاء الذين أصدروا الطلبات من جدول معلومات المبيعات أولاً، ثم تمرير النتائج إلى الاستعلام الرئيسي، كما هو موضح أدناه:
حذف من معلومات العميل
حيث لا يوجد معرف العميل (حدد معرف العميل من معلومات المبيعات)
يمكن أن يؤدي استخدام الاستعلامات الفرعية إلى إكمال العديد من عمليات SQL التي تتطلب منطقيًا خطوات متعددة لإكمالها في وقت واحد، ويمكن أيضًا تجنب أقفال المعاملات أو الجداول، كما أنه من السهل كتابتها. ومع ذلك، في بعض الحالات، يمكن استبدال الاستعلامات الفرعية بصلات أكثر كفاءة (JOIN). على سبيل المثال، لنفترض أننا نريد استرداد كافة المستخدمين الذين ليس لديهم سجلات الطلب، فيمكننا استخدام الاستعلام التالي:
SELECT * FROM customerinfo
حيث لا يوجد معرف العميل (اختر معرف العميل من معلومات المبيعات)
إذا كنت تستخدم الاتصال (JOIN).. لإكمال هذا الاستعلام، ستكون السرعة أسرع بكثير. خاصة إذا كان هناك فهرس على معرف العميل في جدول معلومات المبيعات، فسيكون الأداء أفضل، ويكون الاستعلام كما يلي:
SELECT * FROM customerinfo
الانضمام الأيسر إلى salesinfoON customerinfo.CustomerID=salesinfo.
معرف العميل
حيث يكون salesinfo.CustomerID 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 يبدأ
1146 /etc/rc.d/init.d/mysql.server. توقف عن
تكوين HA عالي التوفر، ولا تقم بتعيين تثبيت NFS وتشغيل خدمة mysql تلقائيًا عند بدء تشغيل الخادم
5. قفل الجداول
على الرغم من أن المعاملات هي طريقة جيدة جدًا للحفاظ على سلامة قاعدة البيانات، نظرًا لتفردها، فإنها تؤثر أحيانًا على أداء قاعدة البيانات، خاصة في فترات زمنية طويلة جدًا في أنظمة التطبيقات الكبيرة. نظرًا لأنه سيتم قفل قاعدة البيانات أثناء تنفيذ المعاملة، فيمكن لطلبات المستخدمين الأخرى الانتظار حتى تنتهي المعاملة فقط. إذا تم استخدام نظام قاعدة البيانات من قبل عدد قليل من المستخدمين، فلن يصبح تأثير المعاملات مشكلة كبيرة، ولكن إذا قام آلاف المستخدمين بالوصول إلى نظام قاعدة البيانات في نفس الوقت، مثل الوصول إلى موقع ويب للتجارة الإلكترونية، فسيتسبب ذلك في حدوث مشكلات خطيرة؛ تأخير الاستجابة.
في الواقع، في بعض الحالات يمكننا الحصول على أداء أفضل عن طريق قفل الطاولة. يستخدم المثال التالي طريقة جدول القفل لإكمال وظيفة المعاملة في المثال السابق.
قفل الجدول الجرد الكتابة
حدد الكمية من المخزون
WHEREItem='book';
...
تحديث كمية مجموعة المخزون = 11
WHEREItem='book';
فتح الجداول
هنا نستخدم عبارة SELECT لاسترداد البيانات الأولية، ومن خلال بعض الحسابات نستخدم عبارة UPDATE لتحديث القيم الجديدة في الجدول. تضمن عبارة LOCK TABLE التي تحتوي على الكلمة الأساسية WRITE أنه لن يكون هناك وصول آخر إلى المخزون لإدراجه أو تحديثه أو حذفه قبل تنفيذ أمر UNLOCK TABLES.
6.
يمكن أن تحافظ طريقة استخدام المفاتيح الخارجية لقفل الجدول على سلامة البيانات، لكنها لا تضمن ملاءمة البيانات. في هذا الوقت يمكننا استخدام المفاتيح الخارجية. على سبيل المثال، يمكن أن يضمن المفتاح الخارجي أن كل سجل مبيعات يشير إلى عميل حالي. هنا، يمكن للمفتاح الخارجي تعيين معرف العميل في جدول معلومات العميل إلى معرف العميل في جدول معلومات المبيعات، ولن يتم تحديث أي سجل بدون معرف عميل صالح أو إدراجه في معلومات المبيعات.
إنشاء جدول معلومات العملاء
(
معرف العميل ليس فارغًا،
المفتاح الأساسي (معرف العميل)
) TYPE = INNODB؛
إنشاء معلومات مبيعات الجدول
(
معرف المبيعات INT ليس فارغًا،
معرف العميل ليس فارغًا،
المفتاح الأساسي (معرف العميل، معرف المبيعات)،
يشير المفتاح الخارجي (معرف العميل) إلى معلومات العميل
(معرف العميل) على DELETECASCADE
) النوع = INNODB؛
لاحظ المعلمة "ON DELETE CASCADE" في المثال. تضمن هذه المعلمة أنه عند حذف سجل عميل في جدول معلومات العميل، سيتم أيضًا حذف جميع السجلات المرتبطة بالعميل في جدول معلومات المبيعات تلقائيًا. إذا كنت تريد استخدام المفاتيح الخارجية في MySQL، فيجب أن تتذكر تحديد نوع الجدول كنوع InnoDB آمن للمعاملات عند إنشاء الجدول. هذا النوع ليس هو النوع الافتراضي لجداول MySQL. الطريقة المحددة هي إضافة TYPE=INNODB إلى عبارة CREATE TABLE. كما هو موضح في المثال.
7. يعد استخدام الفهارس
طريقة شائعة لتحسين أداء قاعدة البيانات، فهو يسمح لخادم قاعدة البيانات باسترداد صفوف معينة بشكل أسرع بكثير من عدم وجود فهرس، خاصة إذا كانت عبارة الاستعلام تحتوي على MAX() وMIN() وORDERBY عند تنفيذ الأمر. تحسين الأداء أكثر وضوحا. إذن ما هي الحقول التي يجب فهرستها؟ بشكل عام، يجب إنشاء الفهارس على الحقول التي سيتم استخدامها للفرز JOIN وأين الحكم والترتيب حسب الترتيب. حاول عدم فهرسة حقل في قاعدة البيانات يحتوي على عدد كبير من القيم المكررة. بالنسبة لحقل من نوع 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، لن يعمل الفهرس كما ينبغي. لذلك، على الرغم من أن الاستعلامين التاليين يعرضان نفس النتائج، إلا أن الأخير أسرع بكثير من الأول.
حدد * من الطلب حيث السنة (تاريخ الطلب) <2001؛
SELECT * FROM order WHERE OrderDate<"2001-01-01";
سيحدث نفس الموقف أيضًا عند حساب الحقول الرقمية:
SELECT * FROM المخزون WHERE Amount/7<24;
SELECT * FROM المخزون WHERE Amount<24*7;
يُرجع الاستعلامان أعلاه أيضًا نفس النتائج، لكن الاستعلام الأخير سيكون أسرع بكثير من الاستعلام السابق. ثالثًا، عند البحث عن حقول الأحرف، نستخدم أحيانًا الكلمات الأساسية وأحرف البدل LIKE. على الرغم من أن هذا الأسلوب بسيط، إلا أنه يأتي أيضًا على حساب أداء النظام. على سبيل المثال، سيقوم الاستعلام التالي بمقارنة كل سجل في الجدول.
اختر * من الكتب
حيث يكون الاسم مثل "MySQL%"
ولكن إذا كنت تستخدم الاستعلام التالي، فإن النتائج التي تم إرجاعها هي نفسها، ولكن السرعة أسرع بكثير: ..
SELECT * FROM books
WHERE name>="MySQL" وname<"MySQM"
وأخيرًا، يجب أن تكون حريصًا على تجنب السماح لـ MySQL بإجراء تحويل تلقائي للنوع في الاستعلام، لأن عملية التحويل ستجعل الفهرس أيضًا غير فعال.