Σφάλματα συσκευής 

Βελτιστοποίηση ερωτημάτων sql mysql. Βελτιστοποίηση ερωτημάτων MySql. Όσο λιγότερα ερωτήματα βάσης δεδομένων τόσο το καλύτερο

ΣΕ καθημερινή εργασίαΠρέπει να αντιμετωπίσω αρκετά παρόμοια σφάλματα όταν γράφω ερωτήματα.

Σε αυτό το άρθρο θα ήθελα να δώσω παραδείγματα για το πώς να ΜΗΝ γράφετε ερωτήματα.

  • Επιλέξτε όλα τα πεδία
    ΕΠΙΛΟΓΗ * ΑΠΟ πίνακα

    Όταν γράφετε ερωτήματα, μην χρησιμοποιείτε μια επιλογή από όλα τα πεδία - "*". Καταχωρίστε μόνο τα πεδία που χρειάζεστε πραγματικά. Αυτό θα μειώσει τον όγκο των δεδομένων που λαμβάνονται και αποστέλλονται. Επίσης, μην ξεχνάτε την κάλυψη ευρετηρίων. Ακόμα κι αν πραγματικά χρειάζεστε όλα τα πεδία στον πίνακα, είναι καλύτερα να τα αναφέρετε. Πρώτον, βελτιώνει την αναγνωσιμότητα του κώδικα. Όταν χρησιμοποιείτε έναν αστερίσκο, είναι αδύνατο να γνωρίζετε ποια πεδία βρίσκονται στον πίνακα χωρίς να τον κοιτάξετε. Δεύτερον, με την πάροδο του χρόνου, ο αριθμός των στηλών στον πίνακά σας μπορεί να αλλάξει και αν σήμερα υπάρχουν πέντε στήλες INT, τότε σε ένα μήνα μπορεί να προστεθούν πεδία TEXT και BLOB, τα οποία θα επιβραδύνουν την επιλογή.

  • Αιτήματα σε έναν κύκλο.
    Πρέπει να κατανοήσετε ξεκάθαρα ότι η SQL είναι μια γλώσσα λειτουργίας συνόλου. Μερικές φορές προγραμματιστές που έχουν συνηθίσει να σκέφτονται με όρους διαδικαστικές γλώσσες, είναι δύσκολο να αναδιοργανωθεί η σκέψη στη γλώσσα των συνόλων. Αυτό μπορεί να γίνει πολύ απλά υιοθετώντας έναν απλό κανόνα - "μην εκτελείτε ποτέ ερωτήματα σε βρόχο". Παραδείγματα για το πώς μπορεί να γίνει αυτό:

    1. Δείγματα
    $news_ids = get_list("SELECT news_id FROM today_news ");
    while($news_id = get_next($news_ids))
    $news = get_row("ΕΠΙΛΕΞΤΕ τίτλο, σώμα ΑΠΟ ειδήσεις WHERE news_id = ". $news_id);

    Ο κανόνας είναι πολύ απλός - όσο λιγότερα αιτήματα, τόσο το καλύτερο (αν και υπάρχουν εξαιρέσεις σε αυτό, όπως κάθε κανόνας). Μην ξεχνάτε την κατασκευή IN(). Ο παραπάνω κώδικας μπορεί να γραφτεί σε ένα ερώτημα:
    ΕΠΙΛΕΞΤΕ τίτλο, σώμα ΑΠΟ σήμερα_ειδήσεις Εσωτερική ΕΓΓΡΑΦΗ ειδήσεων USING(news_id)

    2. Ένθετα
    $log = parse_log();
    while($record = next($log))
    query("INSERT INTO logs SET value = ". $log["value"]);!}

    Είναι πολύ πιο αποτελεσματικό να συνενώσετε και να εκτελέσετε ένα ερώτημα:
    INSERT INTO logs (τιμή) VALUES (...), (...)

    3. Ενημερώσεις
    Μερικές φορές χρειάζεται να ενημερώσετε πολλές σειρές σε έναν πίνακα. Εάν η ενημερωμένη τιμή είναι η ίδια, τότε όλα είναι απλά:
    ΕΝΗΜΕΡΩΣΗ ειδήσεων SET title="test" WHERE id IN (1, 2, 3).!}

    Εάν η τιμή που αλλάζει είναι διαφορετική για κάθε εγγραφή, τότε αυτό μπορεί να γίνει με το ακόλουθο ερώτημα:
    ΕΝΗΜΕΡΩΣΗ ΣΕΤ ειδήσεων
    τίτλος = ΠΕΡΙΠΤΩΣΗ
    WHEN news_id = 1 ΤΟΤΕ "aa"
    ΟΤΑΝ news_id = 2 ΤΟΤΕ "bb" ΤΕΛΟΣ
    WHERE news_id IN (1, 2)

    Οι δοκιμές μας δείχνουν ότι ένα τέτοιο αίτημα είναι 2-3 φορές πιο γρήγορο από πολλά ξεχωριστά αιτήματα.

  • Εκτέλεση πράξεων σε πεδία με ευρετήριο
    ΕΠΙΛΕΞΤΕ user_id ΑΠΟ χρήστες WHERE blogs_count * 2 = $value

    Αυτό το ερώτημα δεν θα χρησιμοποιήσει το ευρετήριο, ακόμα κι αν έχει ευρετηριαστεί η στήλη blogs_count. Για να χρησιμοποιηθεί ένα ευρετήριο, δεν πρέπει να εκτελούνται μετασχηματισμοί στο πεδίο του ευρετηρίου στο ερώτημα. Για τέτοια αιτήματα, μετακινήστε τις συναρτήσεις μετατροπής σε άλλο τμήμα:
    SELECT user_id ΑΠΟ χρήστες WHERE blogs_count = $value / 2;

    Παρόμοιο παράδειγμα:
    SELECT user_id ΑΠΟ χρήστες WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS (εγγραφή)<= 10;

    Δεν θα χρησιμοποιήσει ευρετήριο στο καταχωρημένο πεδίο, ενώ
    SELECT user_id ΑΠΟ χρήστες WHERE εγγραφή >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
    θα.

  • Λήψη σειρών μόνο για την καταμέτρηση του αριθμού τους
    $result = mysql_query("SELECT * FROM table", $link);
    $num_rows = mysql_num_rows($result);
    Εάν πρέπει να επιλέξετε τον αριθμό των σειρών που ικανοποιούν μια συγκεκριμένη συνθήκη, χρησιμοποιήστε το ΕΠΙΛΟΓΗ ερωτήματος COUNT(*) FROM πίνακα, αντί να επιλέξετε όλες τις σειρές μόνο για να μετρήσετε τον αριθμό τους.
  • Λήψη επιπλέον σειρών
    $result = mysql_query("SELECT * FROM table1", $link);
    while($row = mysql_fetch_assoc($result) && $i< 20) {
    …
    }
    Εάν χρειάζεστε μόνο n σειρές ανάκτησης, χρησιμοποιήστε LIMIT αντί να απορρίψετε τις επιπλέον σειρές στην εφαρμογή.
  • Χρήση ORDER BY RAND()
    ΕΠΙΛΟΓΗ * ΑΠΟ Πίνακα ΣΕΙΡΑ ΚΑΤΑ RAND() LIMIT 1;

    Εάν ο πίνακας έχει περισσότερες από 4-5 χιλιάδες σειρές, τότε το ORDER BY RAND() θα λειτουργήσει πολύ αργά. Θα ήταν πολύ πιο αποτελεσματικό να εκτελέσετε δύο ερωτήματα:

    Εάν ο πίνακας έχει auto_increment" πρωτεύον κλειδίκαι χωρίς κενά:
    $rnd = rand(1, ερώτημα ("SELECT MAX(id) FROM table"));
    $row = ερώτημα("SELECT * FROM table WHERE id = ".$rnd);

    Ή:
    $cnt = ερώτημα ("SELECT COUNT(*) FROM πίνακα");
    $row = ερώτημα ("SELECT * FROM table LIMIT ".$cnt.", 1");
    η οποία, ωστόσο, μπορεί επίσης να είναι αργή εάν υπάρχει πολύ μεγάλος αριθμός σειρών στον πίνακα.

  • Χρήση μεγάλη ποσότηταΕΓΓΡΑΦΕΙΤΕ
    ΕΠΙΛΕΓΩ
    v.video_id
    α.όνομα,
    ζ. είδος
    ΑΠΟ
    βίντεο AS v
    ΑΡΙΣΤΕΡΑ ΣΥΜΜΕΤΟΧΗ
    link_actors_videos AS la ON la.video_id = v.video_id
    ΑΡΙΣΤΕΡΑ ΣΥΜΜΕΤΟΧΗ
    ηθοποιοί AS a ON a.actor_id = la.actor_id
    ΑΡΙΣΤΕΡΑ ΣΥΜΜΕΤΟΧΗ
    link_genre_video AS lg ON lg.video_id = v.video_id
    ΑΡΙΣΤΕΡΑ ΣΥΜΜΕΤΟΧΗ
    είδη AS g ON g.genre_id = lg.genre_id

    Πρέπει να θυμάστε ότι όταν συνδέετε πίνακες ένα προς πολλά, ο αριθμός των σειρών στην επιλογή θα αυξάνεται με κάθε επόμενο JOIN παρόμοιες περιπτώσειςΜπορεί να είναι πιο γρήγορο να χωρίσετε ένα τέτοιο αίτημα σε πολλά απλά.

  • Χρήση LIMIT
    ΕΠΙΛΟΓΗ… ΑΠΟ Πίνακας LIMIT $start, $per_page

    Πολλοί άνθρωποι πιστεύουν ότι ένα τέτοιο ερώτημα θα επιστρέψει $per_page εγγραφών (συνήθως 10-20) και επομένως θα λειτουργήσει γρήγορα. Θα λειτουργήσει γρήγορα για τις πρώτες σελίδες. Αλλά αν ο αριθμός των εγγραφών είναι μεγάλος και πρέπει να εκτελέσετε ένα ερώτημα SELECT... FROM πίνακα LIMIT 1000000, 1000020 ερώτημα, τότε για να εκτελέσει ένα τέτοιο ερώτημα, η MySQL θα επιλέξει πρώτα 1000020 εγγραφές, θα απορρίψει τα πρώτα εκατομμύρια και θα επιστρέψει 20. μπορεί να μην είναι καθόλου γρήγορο. Δεν υπάρχουν ασήμαντοι τρόποι επίλυσης του προβλήματος. Πολλοί απλώς περιορίζουν την ποσότητα διαθέσιμες σελίδες λογικό νούμερο. Μπορείτε επίσης να επιταχύνετε παρόμοια αιτήματαχρησιμοποιώντας καλυπτικά ευρετήρια ή λύσεις τρίτων(για παράδειγμα σφίγγα).

  • Δεν χρησιμοποιείται ΕΝΗΜΕΡΩΣΗ ΔΙΠΛΟΥ ΚΛΕΙΔΙΟΥ
    $row = ερώτημα ("SELECT * FROM table WHERE id=1");

    Αν ($σειρά)
    ερώτημα ("UPDATE SET table = στήλη + 1 WHERE id=1")
    αλλού
    query("INSERT INTO TABLE SET στήλη = 1, id=1");

    Μια παρόμοια κατασκευή μπορεί να αντικατασταθεί με ένα ερώτημα, με την προϋπόθεση ότι υπάρχει ένα πρωτεύον ή μοναδικό κλειδί για το πεδίο id:
    INSERT INTO πίνακα SET στήλη = 1, id=1 ON DUPLICATE KEY UPDATE στήλη = στήλη + 1

Ανάγνωση

Η διαχείριση των ευρετηρίων —δηλαδή του πώς δημιουργούνται και διατηρούνται— μπορεί να επηρεάσει σημαντικά την απόδοση των ερωτημάτων SQL.

Πολύ συχνά μπορούν να εφαρμοστούν οι ακόλουθες βελτιστοποιήσεις:

  • αφαιρέστε αχρησιμοποίητα ευρετήρια
  • προσδιορίζει αχρησιμοποίητα και αναποτελεσματικά ευρετήρια
  • βελτίωση των δεικτών
  • αποφύγετε εντελώς τα ερωτήματα sql!
  • απλοποιώ ερωτήματα sql
  • και μαγικές επιλογές προσωρινής αποθήκευσης

Συνδυασμός ερωτημάτων DDL

Τα ερωτήματα που αλλάζουν τη δομή δεδομένων συνήθως μπλοκάρουν τον πίνακα. Ιστορικά, εκτέλεση ALTER αίτημααπαιτήθηκε η δημιουργία ενός νέου αντιγράφου του πίνακα, το οποίο μπορεί να είναι πολύ χρονοβόρο και δαπανηρό όσον αφορά την ποσότητα δεδομένων στο δίσκο. Επομένως, αντί για τρία ερωτήματα με μικρές αλλαγές, είναι πολύ πιο κερδοφόρο να εκτελέσετε ένα συνδυασμένο. Αυτό μπορεί να εξοικονομήσει σημαντικό χρόνο σε εργασίες διαχείρισης βάσης δεδομένων.

Αφαίρεση διπλών ευρετηρίων

Τα διπλότυπα ευρετήρια είναι επιβλαβή για δύο λόγους: όλα τα αιτήματα για αλλαγή δεδομένων θα είναι πιο αργά, επειδή γίνεται διπλή εργασία για να διατηρηθεί η πληρότητα του ευρετηρίου. Επιπλέον, αυτό δημιουργεί περιττό άγχος σύστημα αρχείων, καθώς το μέγεθος της βάσης δεδομένων γίνεται φυσικά μεγάλο και οδηγεί σε αύξηση του χρόνου που απαιτείται για τη δημιουργία αντιγράφων ασφαλείας και τον χρόνο επαναφοράς.

Μερικοί απλές συνθήκεςμπορεί να οδηγήσει σε διπλά ευρετήρια. Για παράδειγμα, η mysql δεν χρειάζεται ευρετήριο σε ΠΡΩΤΑ πεδία.

Μπορεί επίσης να υπάρχει διπλό ευρετήριο εάν αριστερή πλευράένα από τα ευρετήρια είναι εντελώς πανομοιότυπο με το άλλο ευρετήριο.

Το βοηθητικό πρόγραμμα pt-duplicate-key-checker από το perkona-toolkit είναι ένα απλό και γρήγορος τρόποςελέγξτε τη δομή της βάσης δεδομένων σας για περιττά ευρετήρια.

Αφαίρεση αχρησιμοποίητων ευρετηρίων

Εκτός από τα ευρετήρια που δεν χρησιμοποιούνται ποτέ επειδή είναι διπλότυπα, μπορεί να υπάρχουν μη διπλότυπα ευρετήρια που απλά δεν χρησιμοποιούνται ποτέ. Τέτοια ευρετήρια έχουν τον ίδιο αντίκτυπο με τα διπλά ευρετήρια. ΣΕ τυπική mysqlΔεν υπάρχει τρόπος να προσδιορίσετε ποια ευρετήρια δεν χρησιμοποιούνται, αλλά ορισμένες εκδόσεις έχουν αυτήν τη δυνατότητα, π.χ. χρησιμοποιώντας το GoogleΕνημερωμένη έκδοση κώδικα MySQL.

Αυτή η ενημέρωση κώδικα εισήγαγε μια δυνατότητα: SHOW INDEX_STATISTICS.

Και στην κανονική mysql, πρέπει πρώτα να συλλέξετε όλα τα ερωτήματα sql που χρησιμοποιούνται, να τα εκτελέσετε και να δείτε το σχέδιο εκτέλεσης, ενώ συλλέγετε πληροφορίες σχετικά με τα ευρετήρια που χρησιμοποιούνται σε κάθε περίπτωση και τα φέρνετε σε έναν ενιαίο πίνακα. Σε κάθε περίπτωση, είναι μια χρήσιμη εμπειρία.

Βελτιστοποίηση πεδίων ευρετηρίου.

Εκτός από τη δημιουργία νέων ευρετηρίων για τη βελτίωση της απόδοσης, μπορείτε να βελτιώσετε την απόδοση με πρόσθετες βελτιστοποιήσειςδομές. Αυτές οι βελτιστοποιήσεις περιλαμβάνουν τη χρήση προσαρμοσμένων δεδομένων και τύπων πεδίων. Κέρδος σε σε αυτή την περίπτωση- αυτό σημαίνει λιγότερο φορτίο δίσκου και μεγαλύτερο όγκο ευρετηρίων που χωρούν στη μνήμη RAM.

Τύποι Δεδομένων

Μερικοί τύποι μπορούν να αντικατασταθούν ανώδυνα στις υπάρχουσες βάσεις.

BIGINT εναντίον INT

Όταν το ΚΥΡΙΟ κλειδί ορίζεται ως ΜΕΓΑΛΗ ΑΥΤΟΜΑΤΗ ΑΥΞΗΣΗ, γενικά δεν υπάρχει λόγος να το χρησιμοποιήσετε. Ο τύπος δεδομένων INT UNSIGNED AUTO_INCREMENT μπορεί να αποθηκεύσει έναν μέγιστο αριθμό έως και 4294967295. Εάν έχετε στην πραγματικότητα περισσότερες εγγραφές από αυτόν τον αριθμό, πιθανότατα θα χρειαστείτε διαφορετική αρχιτεκτονική.

Από μια τέτοια αλλαγή από BIGINT σε INT unsigned, κάθε σειρά του πίνακα αρχίζει να καταλαμβάνει 2 φορές λιγότερο χώροστον δίσκο, επιπλέον, το μέγεθος που καταλαμβάνει το ΚΥΡΙΟ κλειδί μειώνεται από 8 byte σε 4.

Αυτή είναι ίσως μια από τις πιο απτές απλές βελτιώσεις που μπορεί να γίνει αρκετά ανώδυνα.

DATETIME έναντι TIMESTAMP

Όλα είναι απλά εδώ: χρονική σήμανση - 4 byte, ώρα ημερομηνίας - 8 byte.

Πρέπει να χρησιμοποιείται όποτε είναι δυνατόν γιατί:

  • πρόσθετος έλεγχος ακεραιότητας δεδομένων
  • Ένα τέτοιο πεδίο θα χρησιμοποιεί μόνο 1 byte για να αποθηκεύσει 255 μοναδικές τιμές
  • Τέτοια πεδία είναι πιο βολικά για ανάγνωση :)

Ιστορικά, η χρήση πεδίων enum είχε ως αποτέλεσμα η βάση να εξαρτάται από αλλαγές στις πιθανές τιμές στο enum. Αυτό ήταν ένα αίτημα αποκλεισμού DDL. Ξεκινώντας από Εκδόσεις MySQL 5.1 Η προσθήκη νέων παραλλαγών σε ένα enum είναι πολύ γρήγορη και δεν σχετίζεται με το μέγεθος του πίνακα.

NULL vs NOT NULL

Εάν δεν είστε βέβαιοι ότι μια στήλη μπορεί να περιέχει μηδενική τιμή (NULL), είναι προτιμότερο να την ορίσετε ως NOT NULL. Το ευρετήριο σε μια τέτοια στήλη θα είναι μικρότερο σε μέγεθος και ευκολότερο στην επεξεργασία.

Αυτόματες μετατροπές τύπων

Όταν επιλέγετε έναν τύπο δεδομένων για σύνδεση πεδίων, μπορεί να συμβεί ο τύπος δεδομένων του πεδίου να μην έχει καθοριστεί. Η ενσωματωμένη μετατροπή μπορεί να είναι μια εντελώς περιττή επιβάρυνση.

Για ακέραια πεδία, βεβαιωθείτε ότι τα SIGNED και UNSIGNED είναι τα ίδια για μεταβλητούς τύπουςχωράφια, επιπλέον εργασίαΕνδέχεται να υπάρχει μετατροπή κωδικοποίησης κατά την εγγραφή, οπότε φροντίστε να τις ελέγξετε επίσης. Κοινό πρόβλημαΑυτή είναι μια αυτόματη μετατροπή μεταξύ των κωδικοποιήσεων latin1 και utf8.

Τύποι στηλών

Ορισμένοι τύποι δεδομένων αποθηκεύονται συχνά σε λάθος στήλες. Η αλλαγή του τύπου όταν το κάνετε αυτό μπορεί να οδηγήσει σε πιο αποτελεσματική αποθήκευση, ειδικά όταν αυτές οι στήλες περιλαμβάνονται στο ευρετήριο. Ας δούμε μερικά χαρακτηριστικά παραδείγματα.

διεύθυνση IP

Η διεύθυνση IPv4 μπορεί να αποθηκευτεί στο πεδίο INT UNSIGNED, το οποίο διαρκεί μόνο 4 byte. Μια συνηθισμένη κατάσταση παρουσιάζεται όταν η διεύθυνση IP αποθηκεύεται στο πεδίο VARCHAR(15), το οποίο παίρνει 12 byte. Αυτή η μία αλλαγή μπορεί να μειώσει το μέγεθος κατά 2/3. Οι συναρτήσεις INET_ATON() και INET_NTOA χρησιμοποιούνται για τη μετατροπή μεταξύ μιας συμβολοσειράς με διεύθυνση IP και αριθμητικής τιμής.

Για τις διευθύνσεις IPv6, οι οποίες γίνονται ολοένα και πιο δημοφιλείς, είναι σημαντικό να αποθηκεύεται η αριθμητική τους τιμή 128-bit σε πεδία BINARY(16) και να μην χρησιμοποιείται το VARCHAR για μορφή αναγνώσιμη από τον άνθρωπο.

Η αποθήκευση των πεδίων md5 ως CHAR(32) είναι κοινή πρακτική. Εάν χρησιμοποιείτε ένα πεδίο VARCHAR(32), προσθέτετε επίσης ένα επιπλέον μήκος συμβολοσειράς για κάθε τιμή. Ωστόσο, μια συμβολοσειρά md5 είναι μια δεκαεξαδική τιμή - και μπορεί να αποθηκευτεί πιο αποτελεσματικά χρησιμοποιώντας τις συναρτήσεις UNHEX() και HEX(). Σε αυτήν την περίπτωση, τα δεδομένα μπορούν να αποθηκευτούν σε πεδία BINARY(16). Αυτή η απλή ενέργεια θα μειώσει το μέγεθος του πεδίου από 32 byte σε 16 byte. Μια παρόμοια αρχή μπορεί να εφαρμοστεί σε οποιεσδήποτε δεκαεξαδικές τιμές.

Βασισμένο στο βιβλίο του Ρόναλντ Μπράντφορντ.

Η χρήση βάσεων δεδομένων διευκολύνει σημαντικά τη ζωή και την εργασία ενός ατόμου με δεδομένα, επιτρέποντάς του να αποκτήσει βραχυπρόθεσμους όρους απαραίτητες πληροφορίεςαπό τη βάση δεδομένων ή γράψτε σε αυτήν. Ωστόσο, η εργασία με δεδομένα απαιτεί μια σωστή προσέγγιση, ο προγραμματιστής θα πρέπει να λάβει υπόψη ορισμένες πτυχές της αλληλεπίδρασης με τις βάσεις δεδομένων. Προπαντός μιλάμε γιασχετικά με τη MySQL. Στη συνέχεια, ας δούμε μια σύνοψη συμβουλών για τη βελτιστοποίηση της αλληλεπίδρασης με τις βάσεις δεδομένων Δεδομένα MySQL.

Κάντε τα ερωτήματα MySQL φιλικά στην προσωρινή αποθήκευση

Ο ενσωματωμένος μηχανισμός προσωρινής αποθήκευσης ερωτημάτων στον διακομιστή MySQL μπορεί να βελτιώσει σημαντικά την απόδοση. Οι περισσότεροι διακομιστές βάσης δεδομένων MySQL περιλαμβάνουν μηχανισμό προσωρινής αποθήκευσης. Πολλά πανομοιότυπα ερωτήματα με τη βάση δεδομένων σε σύντομο χρονικό διάστημα μπορούν να δημιουργήσουν σημαντικές απώλειες στην απόδοση, ο μηχανισμός προσωρινής αποθήκευσης είναι σε θέση να αποθηκεύσει προσωρινά τέτοια ερωτήματα, επιστρέφοντας δεδομένα από τη μνήμη cache. Υπάρχουν ερωτήματα που η MySQL δεν μπορεί να αποθηκεύσει προσωρινά και συνιστάται να κάνετε αυτά τα ερωτήματα λίγο διαφορετικά.

// αυτό Ερώτημα MySQLδεν θα είναι σε θέση να αποθηκεύσει στην προσωρινή μνήμη $res = mysql_query("SELECT όνομα χρήστη FROM user WHERE signup_date >= CURDATE()"); // μπορείτε να το κάνετε διαφορετικά $today = date("Y-m-d"); $res = mysql_query("ΕΠΙΛΟΓΗ ονόματος χρήστη ΑΠΟ χρήστη WHERE signup_date >= "$σήμερα"");

Το γεγονός είναι ότι στο πρώτο αίτημα χρησιμοποιήθηκε η συνάρτηση CURDATE() η ιδιαιτερότητα της λειτουργίας της δεν επιτρέπει την τοποθέτηση των αποτελεσμάτων του ερωτήματος στην κρυφή μνήμη. Η τιμή ημερομηνίας μπορεί να προεγγραφεί στη συμβολοσειρά ερωτήματος, κάτι που θα εξαλείψει τη χρήση της συνάρτησης CURDATE() στο ερώτημα.
Κατ' αναλογία, υπάρχουν και άλλες συναρτήσεις που δεν αποθηκεύονται στην κρυφή μνήμη Διακομιστής MySQL, μεταξύ αυτών RAND(), NOW() καθώς και άλλες συναρτήσεις των οποίων τα αποτελέσματα δεν είναι ντετερμινιστικά.

Δείτε πώς εκτελείται το ερώτημά σας χρησιμοποιώντας τη σύνταξη EXPLAIN

Μπορείτε να δείτε πώς η MySQL εκτελεί το ερώτημά σας χρησιμοποιώντας τη σύνταξη EXPLAIN. Η χρήση του μπορεί να βοηθήσει στον εντοπισμό αδυναμιών στην απόδοση ερωτημάτων, καθώς και στη δομή του πίνακα. Το EXPLAIN θα επιστρέψει δεδομένα ως αποτέλεσμα ερωτήματος που θα δείχνει ποια ευρετήρια χρησιμοποιούνται, πώς επιλέγονται τα δεδομένα από πίνακες, πώς ταξινομούνται κ.λπ. Για να το κάνετε αυτό, απλώς προσθέστε στην αρχή του ερωτήματος SELECT λέξη-κλειδίΕΞΗΓΗΣΤΕ και μετά θα εμφανιστεί ένας πίνακας με δεδομένα.

Όταν χρειάζεστε μία εγγραφή, ορίστε LIMIT 1

Υπάρχουν αρκετές περιπτώσεις που πρέπει να ελέγξετε την παρουσία τουλάχιστον μίας εγγραφής από έναν πίνακα, σε αυτήν την περίπτωση, συνιστάται να προσθέσετε την παράμετρο LIMIT 1 στο ερώτημα Η μηχανή βάσης δεδομένων θα σταματήσει να λαμβάνει δεδομένα μετά την εύρεση της πρώτης εγγραφής αντί να ανακτήσει όλα τα δεδομένα. Εξοικονομείτε πόρους.

// ερωτήστε την πόλη με τον κώδικα Shymkent από τη βάση δεδομένων $res = mysql_query("SELECT * FROM location WHERE city = "Shymkent""); if (mysql_num_rows($res) > 0)( ) // προσθέστε LIMIT 1 για να βελτιστοποιήσετε το ερώτημα $res = mysql_query("SELECT * FROM location WHERE city = "Shymkent" LIMIT 1"); if (mysql_num_rows($res) > 0)( )

Δημιουργήστε ευρετήριο για τα πεδία που αναζητάτε

Σε μια συγκεκριμένη περίπτωση, ένα ευρετήριο σημαίνει ένα ευρετήριο των πεδίων στα οποία αναζητάτε, αυτό θα βελτιώσει την ταχύτητα αναζήτησης. Παρεμπιπτόντως, ένα κανονικό ευρετήριο δεν μπορεί να λειτουργήσει με συνθήκες με τη μορφή κανονικών εκφράσεων:

// η πόλη ευρετηρίου LIKE 'shym%' θα λειτουργήσει εδώ // το ευρετήριο δεν θα χρησιμοποιηθεί εδώ πόλη LIKE '%shymkent%'

Για να δημιουργήσετε ένα ευρετήριο για τις συνθήκες με κανονικές εκφράσειςθα πρέπει να χρησιμοποιήσετε ή να σκεφτείτε το σύστημα ευρετηρίου σας.

Δημιουργήστε ευρετήριο των πεδίων με τα οποία ενώνονται οι πίνακες

Εάν χρησιμοποιείτε πολλαπλές ενώσεις πινάκων, ίσως θελήσετε να εξετάσετε το ενδεχόμενο να βεβαιωθείτε ότι τα πεδία που συμμετέχουν στην ένωση έχουν ευρετηριαστεί και στους δύο πίνακες. Αυτό το θέμα επηρεάζει τον τρόπο παραγωγής της MySQL εσωτερική βελτιστοποίησηενώσεις πεδίων πίνακα. Τα πεδία ένωσης πρέπει να είναι του ίδιου τύπου και κωδικοποίησης. Εκείνοι. για παράδειγμα, εάν ένα πεδίο είναι τύπου ΔΕΚΑΔΙΚΟ και το άλλο είναι INT, τότε η MySQL δεν θα μπορεί να χρησιμοποιήσει το ευρετήριο.

Βρείτε μια εναλλακτική στο ORDER BY RAND()

Η χρήση τυχαίας ταξινόμησης είναι πράγματι πολύ βολική, και πολλοί αρχάριοι προγραμματιστές έχουν την ίδια άποψη σχετικά με αυτό. Ωστόσο, υπάρχουν παγίδες εδώ, και πολύ σημαντικές, χρησιμοποιώντας μια παρόμοια μέθοδο δειγματοληψίας στα ερωτήματά σας, φεύγετε κώλυμαστην παραγωγικότητα. Εδώ συνιστάται να καταφύγετε πρόσθετος κωδικόςαντί να χρησιμοποιήσετε ORDER BY RAND(), ως εναλλακτική λύση για να απαλλαγείτε αδύνατο σημείοστην απόδοση, η οποία θα το θυμίζει καθώς αυξάνεται ο όγκος των δεδομένων.

Χρησιμοποιήστε την επιλογή συγκεκριμένων πεδίων αντί για SELECT *

Μην τεμπελιάζετε να υποδείξετε συγκεκριμένα υποχρεωτικά πεδίασε ένα ερώτημα κατά την ανάκτηση, αντί να χρησιμοποιείτε το "*" - ανάκτηση όλων των πεδίων, το γεγονός είναι ότι όσο περισσότερα δεδομένα διαβάζονται από τον πίνακα, τόσο πιο αργό γίνεται το ερώτημά σας.

Προσθέστε ένα πεδίο αναγνωριστικού για όλους τους πίνακες

Κάθε τραπέζι, αν γίνει σωστά, θα πρέπει να έχει ένα πεδίο id πληκτρολογήστε INT, που είναι το πρωτεύον κλειδί (PRIMARY_KEY) και AUTO_INCREMENT. Επιπλέον, πρέπει να καθορίσετε την παράμετρο ΜΗ ΥΠΟΓΡΑΦΗ για το πεδίο, πράγμα που σημαίνει ότι η τιμή θα είναι πάντα θετική.
Η MySQL έχει εσωτερικές λειτουργίεςπου μπορεί να χρησιμοποιήσει ένα πρωτεύον κλειδί, αυτό παίζει ρόλο για σύνθετες διαμορφώσειςβάσεις δεδομένων όπως συμπλέγματα, παραλληλοποίηση κ.λπ.
Επιπλέον, εάν υπάρχουν πολλοί πίνακες και πρέπει να εκτελέσετε ένα κοινό ερώτημα, τότε τα αναγνωριστικά πινάκων θα σας φανούν πολύ χρήσιμα.

ENUM ως εναλλακτική λύση στο VARCHAR

Ας φανταστούμε ότι θέλετε να προσθέσετε ένα πεδίο σε έναν πίνακα που θα πρέπει να περιέχει ένα συγκεκριμένο σύνολο τιμών. Παραδοσιακά, πολλοί προγραμματιστές ορίζουν τον τύπο VARCHAR για τα πεδία. Ωστόσο, υπάρχει ένας άλλος τύπος πεδίου που είναι πολύ πιο γρήγορος και πιο συμπαγής. Αξίες σε αυτού του τύπουαποθηκεύεται με τον ίδιο τρόπο όπως το TINYINT, αλλά εμφανίζεται ως τύπος συμβολοσειράς.

Χρησιμοποιήστε το NOT NULL αντί για το NULL

Τα NULL πεδία καταλαμβάνουν περισσότερο χώρο σε μια εγγραφή λόγω της ανάγκης να επισημανθεί αυτό NULL τιμή. Οι πίνακες MyISAM, τα πεδία με NULL αποθηκεύονται με τέτοιο τρόπο ώστε κάθε πεδίο να καταλαμβάνει 1 επιπλέον λίγο, το οποίο στρογγυλοποιείται στο πλησιέστερο byte. Εάν η χρήση του NULL σε ένα πεδίο δεν είναι σημαντική, τότε συνιστάται η χρήση του NOT NULL.

Χρησιμοποιήστε Έτοιμες Δηλώσεις

$res = "UPDATE hosts SET ip = INET_ATON("($_SERVER["REMOTE_ADDR"])") WHERE id = $host_id";

Χρησιμοποιήστε στατικούς πίνακες

Ένας στατικός πίνακας είναι ένας κανονικός πίνακας στη βάση δεδομένων, με τη διαφορά ότι κάθε πεδίο στον πίνακα έχει σταθερό μέγεθος. Εάν ο πίνακας έχει στήλες που δεν έχουν σταθερό μήκος, για παράδειγμα, αυτές θα μπορούσαν να είναι: VARCHAR, TEXT, BLOB, παύει να είναι στατικό και θα υποβληθεί σε επεξεργασία από τη MySQL λίγο διαφορετικά. Οι στατικοί πίνακες, ή μπορούν επίσης να ονομαστούν πίνακες σταθερού μεγέθους, λειτουργούν πιο γρήγορα από τους μη στατικούς. Οι εγγραφές από τέτοιους πίνακες θα προβάλλονται πιο γρήγορα, εάν είναι απαραίτητο, επιλέξτε το επιθυμητό. Συμβολοσειρές MySQLθα υπολογίσει γρήγορα τη θέση του. Εάν το πεδίο δεν έχει σταθερό μέγεθος, τότε σε αυτήν την περίπτωση η αναζήτηση πραγματοποιείται κατά ευρετήριο. Υπάρχουν άλλα πλεονεκτήματα από τη χρήση στατικών πινάκων, το γεγονός είναι ότι αυτοί οι πίνακες αποθηκεύονται πιο εύκολα στην κρυφή μνήμη και μπορούν επίσης να αποκατασταθούν μετά από κατάρρευση της βάσης δεδομένων.

Χρησιμοποιήστε κάθετο διαχωρισμό

Κάθετη κατάτμηση – περιλαμβάνει τη διαίρεση ενός πίνακα σε στήλες προκειμένου να αυξηθεί η απόδοση του πίνακα. Για παράδειγμα, εάν έχετε πεδία στον πίνακά σας που χρησιμοποιούνται πολύ σπάνια ή είναι πεδία με μεταβλητό μήκος, τότε μπορούν να μεταφερθούν ξεχωριστό τραπέζι, έτσι, ξεφορτώνετε το τραπέζι, αυξάνοντας έτσι την ταχύτητα εργασίας μαζί του.

Διαχωρίστε τα μεγάλα ερωτήματα INSERT και DELETE

Η εκτέλεση μεγάλου όγκου ερωτημάτων αυτού του είδους μπορεί να οδηγήσει σε κλείδωμα πίνακα, κάτι που μπορεί να οδηγήσει σε δυσλειτουργίαεφαρμογές γενικά. Μπορούν να δημιουργηθούν παράλληλες αιτήσεις στον διακομιστή web πρόσθετη προσφυγήστο τραπέζι. Εάν ένας πίνακας αποκλειστεί από ένα προηγούμενο αίτημα, τα επόμενα αιτήματα παρατάσσονται σε μια ουρά και ως αποτέλεσμα, αυτό εκδηλώνεται με τη μορφή επιβράδυνσης του ιστότοπου ή ακόμη και κατάρρευσης διακομιστή.
Εάν χρειάζεται να υποβάλετε πολλά αιτήματα, προσπαθήστε να τα ελέγξετε στέλνοντάς τα σε μικρές παρτίδες αντί να ρίχνετε τα πάντα στη βάση δεδομένων. Μπορεί να χρειαστεί περισσότερος χρόνος για την ολοκλήρωση του αιτήματός σας, αλλά αυτό θα έχει μικρότερο αντίκτυπο σε άλλους χρήστες.
Παράδειγμα:

while (1)( mysql_query("DELETE FROM αρχεία καταγραφής WHERE log_date<= "2015-07-20" LIMIT 1000"); if (mysql_affected_rows() == 0){ // записи удалены успешно break; } usleep(50000); // делаем небольшую паузу }

Προσπαθήστε να χρησιμοποιήσετε μικρά πεδία

Όπως γνωρίζετε, τα δεδομένα της βάσης δεδομένων αποθηκεύονται στον σκληρό δίσκο, αυτό μπορεί συχνά να είναι ένα από τα αδύνατα σημεία σε μια εφαρμογή Ιστού. Γεγονός είναι ότι οι δίσκοι μικρού μεγέθους είναι πιο προτιμότεροι, γιατί... Η χρήση τους μειώνει τον φόρτο εργασίας στον σκληρό δίσκο. Εάν είστε βέβαιοι ότι ένας συγκεκριμένος πίνακας θα αποθηκεύσει λίγες σειρές, τότε μια λογική λύση θα ήταν να χρησιμοποιήσετε τύπους πεδίων με τις ελάχιστες δυνατές τιμές. Για παράδειγμα, εάν το κύριο κλειδί είναι τύπου INT και θα αποθηκεύσετε μόνο έναν μικρό όγκο δεδομένων στον πίνακα, τότε είναι προτιμότερο να το κάνετε τύπου MEDIUMINT, SMALLINT ή ακόμα και TINYINT.

Επιλέξτε τον τύπο των πινάκων που ταιριάζουν στις εργασίες σας

Δύο ευρέως γνωστοί τύποι πινάκων σήμερα είναι το MyISAM και το InnoDB, καθένα από αυτά έχει τα δικά του πλεονεκτήματα και μειονεκτήματα. Για παράδειγμα, το MyISAM είναι καλό στην ανάγνωση δεδομένων από πίνακες σε μεγάλες ποσότητες, αλλά είναι πιο αργό όταν γράφει. Έχει επίσης καλή απόδοση σε ερωτήματα όπως το SELECT COUNT(*).
Ο μηχανισμός αποθήκευσης δεδομένων του InnoDB είναι πιο περίπλοκος από αυτόν του MyISAM, ωστόσο, υποστηρίζει το κλείδωμα γραμμών, κάτι που είναι μια θετική πλευρά κατά την κλιμάκωση. Επομένως, είναι αδύνατο να πούμε ότι το ένα είναι καλύτερο από το άλλο και δεν είναι σωστό να επιλέξετε έναν τύπο με βάση τις ανάγκες σας.

9 Οκτωβρίου 2008 στις 11:37 μ.μ

Βελτιστοποίηση ερωτημάτων MySQL

  • MySQL

Στην καθημερινή εργασία, συναντάτε αρκετά παρόμοια σφάλματα κατά τη σύνταξη ερωτημάτων.

Σε αυτό το άρθρο θα ήθελα να δώσω παραδείγματα για το πώς να ΜΗΝ γράφετε ερωτήματα.

  • Επιλέξτε όλα τα πεδία
    ΕΠΙΛΟΓΗ * ΑΠΟ πίνακα

    Όταν γράφετε ερωτήματα, μην χρησιμοποιείτε μια επιλογή από όλα τα πεδία - "*". Καταχωρίστε μόνο τα πεδία που χρειάζεστε πραγματικά. Αυτό θα μειώσει τον όγκο των δεδομένων που λαμβάνονται και αποστέλλονται. Επίσης, μην ξεχνάτε την κάλυψη ευρετηρίων. Ακόμα κι αν πραγματικά χρειάζεστε όλα τα πεδία στον πίνακα, είναι καλύτερα να τα αναφέρετε. Πρώτον, βελτιώνει την αναγνωσιμότητα του κώδικα. Όταν χρησιμοποιείτε έναν αστερίσκο, είναι αδύνατο να γνωρίζετε ποια πεδία βρίσκονται στον πίνακα χωρίς να τον κοιτάξετε. Δεύτερον, με την πάροδο του χρόνου, ο αριθμός των στηλών στον πίνακά σας μπορεί να αλλάξει και αν σήμερα υπάρχουν πέντε στήλες INT, τότε σε ένα μήνα μπορεί να προστεθούν πεδία TEXT και BLOB, τα οποία θα επιβραδύνουν την επιλογή.

  • Αιτήματα σε έναν κύκλο.
    Πρέπει να κατανοήσετε ξεκάθαρα ότι η SQL είναι μια γλώσσα λειτουργίας συνόλου. Μερικές φορές οι προγραμματιστές που έχουν συνηθίσει να σκέφτονται με όρους διαδικαστικών γλωσσών δυσκολεύονται να μετατοπίσουν τη σκέψη τους στη γλώσσα των συνόλων. Αυτό μπορεί να γίνει πολύ απλά υιοθετώντας έναν απλό κανόνα - "μην εκτελείτε ποτέ ερωτήματα σε βρόχο". Παραδείγματα για το πώς μπορεί να γίνει αυτό:

    1. Δείγματα
    $news_ids = get_list("SELECT news_id FROM today_news ");
    while($news_id = get_next($news_ids))
    $news = get_row("ΕΠΙΛΕΞΤΕ τίτλο, σώμα ΑΠΟ ειδήσεις WHERE news_id = ". $news_id);

    Ο κανόνας είναι πολύ απλός - όσο λιγότερα αιτήματα, τόσο το καλύτερο (αν και υπάρχουν εξαιρέσεις σε αυτό, όπως κάθε κανόνας). Μην ξεχνάτε την κατασκευή IN(). Ο παραπάνω κώδικας μπορεί να γραφτεί σε ένα ερώτημα:
    ΕΠΙΛΕΞΤΕ τίτλο, σώμα ΑΠΟ σήμερα_ειδήσεις Εσωτερική ΕΓΓΡΑΦΗ ειδήσεων USING(news_id)

    2. Ένθετα
    $log = parse_log();
    while($record = next($log))
    query("INSERT INTO logs SET value = ". $log["value"]);!}

    Είναι πολύ πιο αποτελεσματικό να συνενώσετε και να εκτελέσετε ένα ερώτημα:
    INSERT INTO logs (τιμή) VALUES (...), (...)

    3. Ενημερώσεις
    Μερικές φορές χρειάζεται να ενημερώσετε πολλές σειρές σε έναν πίνακα. Εάν η ενημερωμένη τιμή είναι η ίδια, τότε όλα είναι απλά:
    ΕΝΗΜΕΡΩΣΗ ειδήσεων SET title="test" WHERE id IN (1, 2, 3).!}

    Εάν η τιμή που αλλάζει είναι διαφορετική για κάθε εγγραφή, τότε αυτό μπορεί να γίνει με το ακόλουθο ερώτημα:
    ΕΝΗΜΕΡΩΣΗ ΣΕΤ ειδήσεων
    τίτλος = ΠΕΡΙΠΤΩΣΗ
    WHEN news_id = 1 ΤΟΤΕ "aa"
    ΟΤΑΝ news_id = 2 ΤΟΤΕ "bb" ΤΕΛΟΣ
    WHERE news_id IN (1, 2)

    Οι δοκιμές μας δείχνουν ότι ένα τέτοιο αίτημα είναι 2-3 φορές πιο γρήγορο από πολλά ξεχωριστά αιτήματα.

  • Εκτέλεση πράξεων σε πεδία με ευρετήριο
    ΕΠΙΛΕΞΤΕ user_id ΑΠΟ χρήστες WHERE blogs_count * 2 = $value

    Αυτό το ερώτημα δεν θα χρησιμοποιήσει το ευρετήριο, ακόμα κι αν έχει ευρετηριαστεί η στήλη blogs_count. Για να χρησιμοποιηθεί ένα ευρετήριο, δεν πρέπει να εκτελούνται μετασχηματισμοί στο πεδίο του ευρετηρίου στο ερώτημα. Για τέτοια αιτήματα, μετακινήστε τις συναρτήσεις μετατροπής σε άλλο τμήμα:
    SELECT user_id ΑΠΟ χρήστες WHERE blogs_count = $value / 2;

    Παρόμοιο παράδειγμα:
    SELECT user_id ΑΠΟ χρήστες WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS (εγγραφή)<= 10;

    Δεν θα χρησιμοποιήσει ευρετήριο στο καταχωρημένο πεδίο, ενώ
    SELECT user_id ΑΠΟ χρήστες WHERE εγγραφή >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
    θα.

  • Λήψη σειρών μόνο για την καταμέτρηση του αριθμού τους
    $result = mysql_query("SELECT * FROM table", $link);
    $num_rows = mysql_num_rows($result);
    Εάν πρέπει να επιλέξετε τον αριθμό των σειρών που ικανοποιούν μια συγκεκριμένη συνθήκη, χρησιμοποιήστε το ερώτημα SELECT COUNT(*) FROM από τον πίνακα αντί να επιλέξετε όλες τις σειρές απλώς για να μετρήσετε τον αριθμό των σειρών.
  • Λήψη επιπλέον σειρών
    $result = mysql_query("SELECT * FROM table1", $link);
    while($row = mysql_fetch_assoc($result) && $i< 20) {
    …
    }
    Εάν χρειάζεστε μόνο n σειρές ανάκτησης, χρησιμοποιήστε LIMIT αντί να απορρίψετε τις επιπλέον σειρές στην εφαρμογή.
  • Χρήση ORDER BY RAND()
    ΕΠΙΛΟΓΗ * ΑΠΟ Πίνακα ΣΕΙΡΑ ΚΑΤΑ RAND() LIMIT 1;

    Εάν ο πίνακας έχει περισσότερες από 4-5 χιλιάδες σειρές, τότε το ORDER BY RAND() θα λειτουργήσει πολύ αργά. Θα ήταν πολύ πιο αποτελεσματικό να εκτελέσετε δύο ερωτήματα:

    Εάν ο πίνακας έχει πρωτεύον κλειδί auto_increment και δεν υπάρχουν κενά:
    $rnd = rand(1, ερώτημα ("SELECT MAX(id) FROM table"));
    $row = ερώτημα("SELECT * FROM table WHERE id = ".$rnd);

    Ή:
    $cnt = ερώτημα ("SELECT COUNT(*) FROM πίνακα");
    $row = ερώτημα ("SELECT * FROM table LIMIT ".$cnt.", 1");
    η οποία, ωστόσο, μπορεί επίσης να είναι αργή εάν υπάρχει πολύ μεγάλος αριθμός σειρών στον πίνακα.

  • Χρήση μεγάλου αριθμού JOIN
    ΕΠΙΛΕΓΩ
    v.video_id
    α.όνομα,
    ζ. είδος
    ΑΠΟ
    βίντεο AS v
    ΑΡΙΣΤΕΡΑ ΣΥΜΜΕΤΟΧΗ
    link_actors_videos AS la ON la.video_id = v.video_id
    ΑΡΙΣΤΕΡΑ ΣΥΜΜΕΤΟΧΗ
    ηθοποιοί AS a ON a.actor_id = la.actor_id
    ΑΡΙΣΤΕΡΑ ΣΥΜΜΕΤΟΧΗ
    link_genre_video AS lg ON lg.video_id = v.video_id
    ΑΡΙΣΤΕΡΑ ΣΥΜΜΕΤΟΧΗ
    είδη AS g ON g.genre_id = lg.genre_id

    Πρέπει να θυμάστε ότι όταν συνδέετε πίνακες ένας προς πολλούς, ο αριθμός των σειρών στην επιλογή θα αυξάνεται με κάθε επόμενο JOIN Σε τέτοιες περιπτώσεις, είναι πιο γρήγορο να χωρίσετε ένα τέτοιο ερώτημα σε πολλές απλές.

  • Χρήση LIMIT
    ΕΠΙΛΟΓΗ… ΑΠΟ Πίνακας LIMIT $start, $per_page

    Πολλοί άνθρωποι πιστεύουν ότι ένα τέτοιο ερώτημα θα επιστρέψει $per_page εγγραφών (συνήθως 10-20) και επομένως θα λειτουργήσει γρήγορα. Θα λειτουργήσει γρήγορα για τις πρώτες σελίδες. Αλλά αν ο αριθμός των εγγραφών είναι μεγάλος και πρέπει να εκτελέσετε ένα ερώτημα SELECT... FROM πίνακα LIMIT 1000000, 1000020 ερώτημα, τότε για να εκτελέσει ένα τέτοιο ερώτημα, η MySQL θα επιλέξει πρώτα 1000020 εγγραφές, θα απορρίψει τα πρώτα εκατομμύρια και θα επιστρέψει 20. μπορεί να μην είναι καθόλου γρήγορο. Δεν υπάρχουν ασήμαντοι τρόποι επίλυσης του προβλήματος. Πολλοί απλώς περιορίζουν τον αριθμό των διαθέσιμων σελίδων σε έναν λογικό αριθμό. Μπορείτε επίσης να επιταχύνετε τέτοια ερωτήματα χρησιμοποιώντας ευρετήρια κάλυψης ή λύσεις τρίτων (για παράδειγμα σφίγγα).

  • Δεν χρησιμοποιείται ΕΝΗΜΕΡΩΣΗ ΔΙΠΛΟΥ ΚΛΕΙΔΙΟΥ
    $row = ερώτημα ("SELECT * FROM table WHERE id=1");

    Αν ($σειρά)
    ερώτημα ("UPDATE SET table = στήλη + 1 WHERE id=1")
    αλλού
    query("INSERT INTO TABLE SET στήλη = 1, id=1");

    Μια παρόμοια κατασκευή μπορεί να αντικατασταθεί με ένα ερώτημα, με την προϋπόθεση ότι υπάρχει ένα πρωτεύον ή μοναδικό κλειδί για το πεδίο id:
    INSERT INTO πίνακα SET στήλη = 1, id=1 ON DUPLICATE KEY UPDATE στήλη = στήλη + 1

Ανάγνωση

Ο βαθμός φόρτωσης του διακομιστή, και επομένως η ταχύτητα φόρτωσης του ιστότοπου, εξαρτάται σε μεγάλο βαθμό από το πόσο καλά είναι βελτιστοποιημένα τα ερωτήματα στη βάση δεδομένων mySQL. Η βελτιστοποίηση των ερωτημάτων mySQL θα βοηθήσει στην ανακούφιση του φόρτου του διακομιστή και στη μείωση του χρόνου φόρτωσης του ιστότοπού σας.

Γιατί να βελτιστοποιήσετε τα ερωτήματα της βάσης δεδομένων;

Οι ιδιοκτήτες ιστότοπων που διαχειρίζονται αυτογραφικά συστήματα διαχείρισης πρέπει απλώς να κατανοούν καλά ποια ερωτήματα στη βάση δεδομένων εκτελούνται γρήγορα και εύκολα και τα οποία αυξάνουν σημαντικά το φόρτο στον διακομιστή και επιβραδύνουν σημαντικά την ταχύτητα φόρτωσης του ιστότοπου.

Το να φτάσουμε στην ουσία του θέματος δεν θα βλάψει εκείνους τους webmasters που χρησιμοποιούν γνωστά συστήματα διαχείρισης και τους αρέσει να συνδέουν κάθε είδους προσθήκες τρίτων, καθώς και να προσαρμόζουν θέματα για τον εαυτό τους, για παράδειγμα, στο πιο δημοφιλές δωρεάν CMS - WordPress .

Ορισμένες ενέργειες μπορούν να εκτελεστούν με διαφορετικούς τρόπους, για παράδειγμα, μπορείτε να μετρήσετε τον αριθμό των εγγραφών που βρέθηκαν σε έναν πίνακα χρησιμοποιώντας τη συνάρτηση mysql_num_rows (αλλά αυτό δεν συνιστάται) ή μπορείτε επίσης να χρησιμοποιήσετε την κατασκευή SELECT COUNT(). Πραγματοποιήσαμε προσωπικά μια μελέτη στην οποία δημιουργήσαμε έναν τεράστιο πίνακα δεδομένων που περιέχει αρκετές εκατοντάδες χιλιάδες εγγραφές και ζυγίζει περισσότερο από ένα gigabyte και, στη συνέχεια, προσπαθήσαμε να μετρήσουμε τον αριθμό των σειρών χρησιμοποιώντας τις καθορισμένες μεθόδους.

Το αποτέλεσμα ήταν ορατό με γυμνό μάτι, γιατί στην περίπτωση χρήσης mysql_num_rows, η σελίδα πάγωσε για 5 δευτερόλεπτα, μετά από τα οποία εμφανίστηκε το αποτέλεσμα. Στη δεύτερη περίπτωση, λάβαμε το αποτέλεσμα με τη μορφή του αριθμού των εγγραφών στον πίνακα σχεδόν αμέσως. Δεν χρειάστηκε καν να μετρήσουμε το χρόνο φόρτωσης του σεναρίου χρησιμοποιώντας μικροχρονόμετρο, γιατί το αποτέλεσμα ήταν κάτι παραπάνω από προφανές.

Το ίδιο ισχύει και για άλλα σχέδια. Ορισμένες λειτουργίες της βάσης δεδομένων μπορούν να εκτελεστούν με δύο, τρεις, τέσσερις ή περισσότερους τρόπους, και καθένας από αυτούς θα διαφέρει σε ταχύτητα, ενώ το αποτέλεσμα σε όλες τις περιπτώσεις θα είναι εξίσου σωστό.

Πώς να βελτιστοποιήσετε τα ερωτήματα της βάσης δεδομένων

Για να κατανοήσουμε ακριβώς πώς να βελτιστοποιούμε τα ερωτήματα και ποιες κατασκευές λειτουργούν πιο γρήγορα και ποιες πιο αργές, θα πραγματοποιήσουμε ξανά ένα μικρό πείραμα και θα το κάνουμε αμέσως τώρα.

Θα πρέπει να απευθυνθούμε στη διεπαφή του δημοφιλούς και πολύ βολικού phpmyadmin για βοήθεια. Για να ξεκινήσουμε, πρέπει να επιλέξουμε μία από τις διαθέσιμες βάσεις δεδομένων και να δημιουργήσουμε έναν πίνακα δοκιμών σε αυτήν. Το όνομά του στην περίπτωσή μας θα είναι αρκετά κοινό - δοκιμαστικό.

ΔΗΜΙΟΥΡΓΙΑ ΠΙΝΑΚΑΣ `test` (`ID` INT NOT NULL AUTO_INCREMENT , `TITLE` VARCHAR(100) SET CHARACTER utf8 COLLATE utf8_unicode_ci NOT NULL , `ANNOUNCEMENT` TEXT CHARACTER SET` NUTFU8T T SET utf8 ΣΥΓΚΕΝΤΡΩΣΗ utf8_unicode_ci ΟΧΙ NULL , PRIMARY KEY (`ID`)) ENGINE = MYISAM ;

Τώρα που έχουμε ήδη έναν πίνακα δοκιμών, πρέπει να τον γεμίσουμε με αφηρημένα δεδομένα. Όπως φαίνεται από τη δομή του πίνακα που μόλις δημιουργήσαμε, θα χρειαστούμε τα ακόλουθα δεδομένα για να συμπληρώσουμε:

  • Επικεφαλίδα
  • Ανακοίνωση
  • Πλήρες κείμενο

Για αφηρημένα κείμενα, από συνήθεια, θα πάμε στην υπηρεσία Yandex.Abstracts, που δημιουργήθηκε ακριβώς για τέτοιους σκοπούς. Είχαμε την τύχη να σκοντάψουμε στο θέμα «Το φωτόνιο στρέψης στον 21ο αιώνα», οπότε ας το πάρουμε.

Υποδείξαμε ένα τυχαία επιλεγμένο θέμα ως τίτλο, πήραμε μια μέτρια παράγραφο κειμένου ως ανακοίνωση και ως πλήρες κείμενο του άρθρου θα έχουμε ένα κείμενο μήκους 4000 χαρακτήρων με κενά. Για να μετρήσετε τον αριθμό των χαρακτήρων στο κείμενο, χρησιμοποιήσαμε τη δική μας υπηρεσία και σας συνιστούμε να τον μετρήσετε εκεί, επειδή υπάρχει επιλογή να ληφθούν υπόψη κενά ή όχι.

Δεν θα αντιγράψουμε το αίτημα που προκύπτει εδώ, γιατί θα αποτελείται από περισσότερους από 4000 χαρακτήρες μη μοναδικού κειμένου που λαμβάνονται από το ίδιο το Yandex, το οποίο είναι αρκετά τολμηρό και δεν το χρειάζεστε. Είναι καλύτερα να γράψουμε έναν απλό βρόχο στην PHP που θα προσθέτει γρήγορα όσες εγγραφές θέλουμε στη βάση δεδομένων. Αρχικά, θα είναι 100.000 άρθρα.

Όσο λιγότερα ερωτήματα βάσης δεδομένων τόσο το καλύτερο

Ήδη σε αυτό το στάδιο θα σας δείξουμε ένα κοινό λάθος, το οποίο εμείς οι ίδιοι θα κάνουμε τώρα επίτηδες.

Για($i=1;$i<100000;$i++) { mysql_query("INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст")"); }

Ως αίτημα, επικολλήσαμε τον κώδικα που αντιγράφηκε από το phpmyadmin, ο οποίος εμφανίστηκε στην οθόνη μετά την μη αυτόματη προσθήκη του πρώτου άρθρου μία φορά. Θα ήθελα να επισημάνω αμέσως ότι δεν πρέπει να δημιουργείτε ερωτήματα στη βάση δεδομένων με αυτόν τον τρόπο. Το κάναμε μόνο επειδή χρειαζόμασταν να γεμίσουμε γρήγορα τον πίνακα με τυχαία δεδομένα και αυτό το ερώτημα γράφεται πιο γρήγορα από αυτό που είναι πιο βέλτιστο. Σε αυτόν τον κύκλο καταλήξαμε με 99999 ξεχωριστές κλήσεις βάσης δεδομένων (η πρώτη που κάναμε χειροκίνητα από το phpmyadmin), η οποία είναι πολύ κακή.

Μια πολύ πιο σωστή λύση θα ήταν να κάνετε την ίδια λειτουργία χρησιμοποιώντας μόνο μία κλήση στη βάση δεδομένων, παραθέτοντας όλες τις γραμμές διαχωρισμένες με κόμμα.

ΕΙΣΑΓΩΓΗ ΣΤΟ "Δοκιμή" ("ID", "TITLE", "ANNONCMENT", "TEXT") ΤΙΜΕΣ (NULL, "Title", "Announcement", "Full text"), (NULL, "Title", "Announcement" , "Πλήρες κείμενο"), (NULL, "Τίτλος", "Ανακοίνωση", "Πλήρες κείμενο"), ...

Αν επιστρέψουμε στην πρώτη μας μέθοδο, θα μοιάζει με αυτό:

INSERT INTO "test" ("ID", "TITLE", "ANNONCMENT", "TEXT") ΤΙΜΕΣ (NULL, "Title", "Announcement", "Full text") INSERT INTO "test" ("ID", ` ΤΙΤΛΟΣ», «ΑΝΑΚΟΙΝΩΣΗ», «ΚΕΙΜΕΝΟ») ΤΙΜΕΣ (NULL, «Τίτλος», «Ανακοίνωση», «Πλήρες κείμενο») ΕΙΣΑΓΩΓΗ ΣΤΗ «δοκιμή» («Αναγνωριστικό», «ΤΙΤΛΟΣ», «ΑΝΑΚΟΙΝΩΣΗ», «ΚΕΙΜΕΝΟ») ΤΙΜΕΣ (NULL, "Τίτλος", "Ανακοίνωση", "Πλήρες κείμενο") ...

Νιώθεις τη διαφορά; Η επιλογή που χρησιμοποιεί μόνο μία πρόσβαση στη βάση δεδομένων είναι η βέλτιστη. Η ταχύτητα λειτουργίας αυτών των δύο μεθόδων, που οδηγεί στο ίδιο αποτέλεσμα, διαφέρει σημαντικά και είναι ορατή χωρίς καμία μέτρηση με γυμνό μάτι, πιστέψτε με, αυτό είναι πραγματικά έτσι.

Επιλέξτε μόνο τα πεδία που απαιτούνται από το σενάριο

Όλα είναι πολύ απλά εδώ - αυτή ή εκείνη η συνάρτηση χρειάζεται ορισμένα δεδομένα από τον πίνακα προορισμού. Πολύ συχνά αποδεικνύεται ότι πρέπει να αφαιρέσετε όλα τα πεδία εντελώς, ειδικά εάν ο πίνακας είναι αρκετά μεγάλος και υπάρχουν περισσότερα από 10 από αυτά τα πεδία.

ΕΠΙΛΟΓΗ * ΑΠΟ «δοκιμή».

Σε αυτό το ερώτημα, ο αστερίσκος σημαίνει ότι τα δεδομένα θα ανακτηθούν από όλα τα πεδία του πίνακα δοκιμής. Τι γίνεται αν υπάρχουν 20-30 ή περισσότερα από αυτά τα πεδία στον πίνακα; Το σενάριο πιθανότατα χρειάζεται μόνο μερικά από αυτά και όλα τα υπόλοιπα, που δεν θα χρησιμοποιηθούν με κανέναν τρόπο, θα επιλεγούν μάταια. Αυτή η λειτουργία θα είναι πιο αργή από ό,τι αν καθορίσατε μόνο εκείνα τα πεδία που πραγματικά χρειάζεστε αυτήν τη στιγμή, χωρισμένα με κόμματα.

ΕΠΙΛΕΞΤΕ «ID», «ΤΙΤΛΟΣ» ΑΠΟ «Δοκιμή».

Σε αυτό το παράδειγμα, δεν θα αγγίξουμε καθόλου την ανακοίνωση και το πλήρες κείμενο του άρθρου, κάτι που θα επιταχύνει σημαντικά το σενάριο. Έτσι, συμπεραίνουμε ότι η βελτιστοποίηση των ερωτημάτων βάσης δεδομένων σημαίνει επίσης συγκεκριμένα την ένδειξη των απαιτούμενων πεδίων στα ερωτήματα και την εγκατάλειψη της καθολικότητας με τη μορφή αστερίσκου.

Συνδυασμός πολλαπλών αιτημάτων σε ένα

Έχουμε ήδη συζητήσει ότι η καλή βελτιστοποίηση της εργασίας με MySQL περιλαμβάνει τη χρήση του ελάχιστου δυνατού αριθμού ερωτημάτων και δώσαμε ένα παράδειγμα προσθήκης δεδομένων σε έναν πίνακα.

Εκτός από την προσθήκη, αυτή η τεχνική μπορεί και πρέπει να χρησιμοποιηθεί κατά τη δειγματοληψία δεδομένων. Τώρα ας δώσουμε το πιο απλό παράδειγμα. Φανταστείτε ότι έχετε δύο πίνακες στη βάση δεδομένων σας - ο πρώτος πίνακας αποθηκεύει πληροφορίες σχετικά με εγγεγραμμένους χρήστες και ο δεύτερος περιέχει άρθρα που γράφτηκαν από αυτούς τους χρήστες.

Ας υποθέσουμε ότι πρέπει να εμφανίσετε κάποιο τυχαίο άρθρο στην οθόνη και να το υπογράψετε με το όνομα του συγγραφέα στο κάτω μέρος. Η σύνδεση μεταξύ των πινάκων σε αυτήν την περίπτωση είναι προφανής και προκύπτει από το αναγνωριστικό χρήστη, δηλαδή το αναγνωριστικό χρήστη στον πίνακα χρηστών πρέπει να αντιστοιχεί στο πεδίο USER_ID στον πίνακα αναρτήσεων. Αυτή η σύνδεση είναι τυπική και θα πρέπει να είναι κατανοητή σε όλους, χωρίς εξαίρεση.

Έτσι, για να επιλέξετε ένα τυχαίο άρθρο, γράφετε ένα ερώτημα όπως αυτό:

$rs_post = mysql_query("SELECT `ID`, `USER_ID`, `TITLE`, `TEXT` FROM `posts` ORDER by RAND() LIMIT 1");

Ένα άρθρο θα επιλεγεί τυχαία από τον πίνακα αναρτήσεων. Μετά από αυτό οι ενέργειές μας θα φαίνονται κάπως έτσι:

$row_post = mysql_fetch_assoc($rs_post); $userID = $row_post["USER_ID"];

Τώρα η μεταβλητή $userID περιέχει το αναγνωριστικό χρήστη του χρήστη που είναι ο συγγραφέας αυτού του άρθρου και για να λάβετε τα δεδομένα του, για παράδειγμα NAME (όνομα) και ΕΠΙΘΕΤΟ (επώνυμο), θα έχετε πρόσβαση στον πίνακα χρηστών και στο ερώτημα θα μοιάζει κάπως έτσι:

$rs_user = mysql_query("SELECT `NAME`, `SURNAME` FROM `users` WHERE `ID` = "".$row_post["USER_ID"]."" LIMIT 1");

Παρεμπιπτόντως, μην ξεχνάτε να περικλείετε μεταβλητές σε αιτήματα με μονά εισαγωγικά, ειδικά όταν τα δεδομένα προέρχονται από έξω, χρησιμοποιώντας GET ή POST. Αυτό θα δημιουργήσει ένα πρόσθετο εμπόδιο για τους εισβολείς και είναι ένα από τα μέτρα που στοχεύουν στην προστασία από ενέσεις SQL. Ας επιστρέψουμε λοιπόν στο παράδειγμά μας. Αφού γίνει το αίτημα στη βάση δεδομένων, τότε όλα είναι απλά - παίρνουμε το όνομα και το επώνυμο και το εμφανίζουμε ως υπογραφή στο άρθρο. Η εργασία ολοκληρώθηκε.

Αλλά αυτά τα δύο ερωτήματα μπορούν να βελτιστοποιηθούν σε ένα. Για να το κάνουμε αυτό, θα χρησιμοποιήσουμε την κατασκευή LEFT JOIN:

ΕΠΙΛΕΞΤΕ `αναρτήσεις`.`ID`, `posts`.`USER_ID`, `posts`.`TITLE`, `posts`.`TEXT`, `users`.`NAME`, `users`.`SURNAME` FROM ` posts` LEFT JOIN `users` ON ​​'posts`.`USER_ID` = `users`.`ID` ORDER by RAND() LIMIT 1

Όπως μπορείτε να δείτε, δεν υπάρχει τίποτα περίπλοκο στο παραπάνω σχέδιο και όλα είναι διαισθητικά. Το μόνο πράγμα στο οποίο θα ήθελα να επιστήσω την προσοχή σας είναι η ρητή ένδειξη των πινάκων σε συνδυασμό με πεδία, καθώς υπάρχει μια επιλογή από πολλούς πίνακες ταυτόχρονα. Εάν τα ονόματα ορισμένων πεδίων συμπίπτουν, τότε θα πρέπει να χρησιμοποιήσετε τα λεγόμενα ψευδώνυμα mySQL για να μην μπερδευτείτε αργότερα κατά την εμφάνιση του αποτελέσματος.

Σύναψη

Όπως μπορείτε να δείτε, είναι δυνατή και απαραίτητη η βελτιστοποίηση των ερωτημάτων της βάσης δεδομένων. Αν νομίζετε ότι αφού όλα λειτουργούν τόσο γρήγορα για εσάς, τότε δεν έχει νόημα να αλλάξετε τίποτα, περιμένετε έως ότου η βάση δεδομένων του ιστότοπού σας μεγαλώσει αρκετές φορές και μαζί με αυτό αυξάνεται η επισκεψιμότητα. Η υψηλή επισκεψιμότητα συνεπάγεται συχνότερη ταυτόχρονη πρόσβαση στη βάση δεδομένων, το μέγεθος της οποίας επηρεάζει επίσης την ταχύτητα των λειτουργιών.

Η κακή βελτιστοποίηση ερωτημάτων μπορεί να ανακαλυφθεί λίγο αργότερα, όταν ο ιστότοπος έχει αναπτυχθεί αρκετά, και με την πάροδο του χρόνου θα γίνεται όλο και πιο δύσκολο να γίνουν αλλαγές, επειδή τα μεγέθη των αρχείων και ο αριθμός των λειτουργιών αυξάνονται μόνο. Νέες δυνατότητες προστίθενται στον ιστότοπο με στόχο τη διευκόλυνση των χρηστών. Με άλλα λόγια, εάν τα πράγματα φτάσουν σε ένα συγκεκριμένο σημείο βρασμού, ενδέχεται να μην μπορείτε να βρείτε λύσεις και θα χρειαστούν αρκετές ημέρες, ή ίσως και εβδομάδες, για να βελτιστοποιήσετε όλες τις κλήσεις στη βάση δεδομένων, διάσπαρτες σε εκατοντάδες αρχεία. Επομένως, είναι καλύτερο να προσπαθήσετε να τα πάτε καλά αμέσως, για να μην δημιουργήσετε περιττά προβλήματα στον εαυτό σας στο μέλλον.



Ερωτήσεις;

Αναφέρετε ένα τυπογραφικό λάθος

Κείμενο που θα σταλεί στους συντάκτες μας:

Στέλνω