mysql IS NULL mit Indexfallerklärung

Einführung

Die Verwendung von „ist null“, „ist nicht null“ und „!=“ in den SQL-Abfrageanweisungen von MySQL hat keine Auswirkungen auf den Index. Der Index wird nicht ungültig und die vollständige Tabelle wird aufgrund der Verwendung von „ist null“, „ist nicht null“ und „!=“ in der Where-Bedingung nicht gescannt.

In der offiziellen MySQL-Dokumentation wird außerdem eindeutig darauf hingewiesen, dass „null“ keinen Einfluss auf die Verwendung des Index hat.

MySQL kann für col_name IS NULL dieselbe Optimierung durchführen, die es für col_name = constant_value verwenden kann. MySQL kann beispielsweise Indizes und Bereiche verwenden, um mit IS NULL nach NULL zu suchen.

Tatsächlich liegt der Grund für das Fehlschlagen des Index und des vollständigen Tabellenscans normalerweise darin, dass in einer Abfrage zu viele Tabellen zurückgegeben werden. MySQL geht davon aus, dass der Zeitaufwand bei der Verwendung eines Index höher ist als bei einem vollständigen Tabellenscan. Daher scannt MySQL lieber die gesamte Tabelle, als einen Index zu verwenden.

Fall

CREATE TABLE `user_info` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  `Alter` int(4) DEFAULT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `index_name` (`name`) MIT BTREE
)ENGINE=InnoDB STANDARD-CHARSET=utf8mb4;

INSERT INTO `user_info` (`id`, `name`, `alter`) VALUES ('1', 'tom', '18');
INSERT INTO `user_info` (`id`, `name`, `alter`) VALUES ('2', null, '19');
INSERT INTO `user_info` (`id`, `name`, `alter`) VALUES ('3', 'Katze', '20');

Beim Ausführen von SQL-Abfragen habe ich „ist null“ und „ist unnull“ verwendet und festgestellt, dass die Indexabfrage weiterhin verwendet wurde und kein Problem mit der Indexungültigkeit vorlag.

analysieren

Um das obige Phänomen zu analysieren, müssen Sie das Funktionsprinzip des MySQL-Index und der Indexdatenstruktur im Detail verstehen. Als Nächstes analysieren wir die MySQL-Indexdatenstruktur mithilfe von zwei Methoden: Tool-Parsing und direktes Anzeigen der Binärdatei.

Werkzeuganalyse

innodb_ruby ist ein sehr leistungsfähiges MySQL-Analysetool, mit dem sich die .ibd-Dateien von MySQL problemlos analysieren und ein tieferes Verständnis der Datenstruktur von MySQL erlangen lassen.

Installieren Sie zuerst das Tool innodb_ruby:

yum install -y rubygems ruby-deve
gem install innodb_ruby

innodb_ruby hat viele Funktionen. Hier müssen wir es nur verwenden, um die Indexstruktur von MySQL zu analysieren, daher ist nur der folgende Befehl erforderlich. Weitere Funktionen und Befehle finden Sie im Wiki.

innodb_space -s ibdata1 -T sakila/film -I PRIMARY index-recurse

Analysieren des Primärschlüsselindexes:

$ innodb_space -s /usr/soft/mysql-5.6.31/data -T test/user_info -I PRIMARY index-recurse
ROOT NODE #3: 3 Datensätze, 89 Bytes
  AUFZEICHNUNG: (id=1) → (name="tom", Alter=18)
  AUFZEICHNUNG: (id=2) → (name=:NULL, alter=19)
  AUFZEICHNUNG: (id=3) → (name="Katze", Alter=20)

Analysieren Sie den gemeinsamen Index Indexname:

$ innodb_space -s /usr/soft/mysql-5.6.31/data -T test/user_info -I index_name index-recurse
ROOT NODE #4: 3 Datensätze, 38 Bytes
  AUFZEICHNUNG: (Name=:NULL) → (ID=2)
  AUFZEICHNUNG: (name="cat") → (id=3)
  AUFZEICHNUNG: (name="tom") → (id=1)

Durch die Analyse der Indexstruktur von MySQL können wir feststellen, dass Nullwerte auch im Indexbaum gespeichert sind und Nullwerte zum kleinsten Wert verarbeitet und auf der linken Seite des Indexbaums index_name platziert werden.

Binärdateien

Suchen Sie die physische Datei user_info.ibd, die der user_info-Tabelle entspricht, öffnen Sie sie mit einer Software wie UltraEdit und suchen Sie direkt die 5. Datenseite (MySQL verwendet standardmäßig eine Datenseite von 16 KB).

Wie in der Abbildung dargestellt, sind diese Binärdaten die Indexseitendaten, die dem Index index_name entsprechen. Nur die Indexdatensätze werden ausgewählt und wie folgt erweitert:

Mindestdatensatz 0x00010063

01 B2 01 00 02 00 29 Datensatzkopfinformationen 69 6E 66 69 6D 75 6D Minimaler Datensatz (fester Wert infimum)

Maximaler Datensatz 0x00010070

00 04 00 0B 00 00 Datensatzkopfinformationen 73 75 70 72 65 6D 75 6D Maximaler Datensatz (Festwert-Supremum)

ID 1 Index 0x0001007f

03 00 00 00 10 FF F1 Datensatzkopfinformationen 74 6F 6D Der Wert des Feldnamens: tom
80 00 00 01 RowID: Der Wert der Primärschlüssel-ID ist 1

ID 2 Index 0x0001008c

01 00 00 18 00 0B Datensatzkopfinformationen Feldname Wert: null
80 00 00 02 RowID: Der Wert der Primärschlüssel-ID ist 2

ID 3 Index 0x00010097

03 00 00 00 20 FF E8 Datensatzkopfinformationen 63 61 74 Feldname Wert: cat
80 00 00 03 RowID: Der Wert der Primärschlüssel-ID ist 3

Die letzten 2 Bytes der Datensatzkopfinformationen des Mindestdatensatzes sind 00 29 -> 0x00010063 Offset 0x0029 -> 0x0001008C, was die Indexposition der ID 2 ist;

Die letzten 2 Bytes der Datensatzkopfinformationen von ID 2 sind 00 0B -> 0x0001008C Offset 0x000B -> 0x00010097, was die Indexposition von ID 3 ist;

Die letzten 2 Bytes der Datensatzkopfinformationen von ID 3 sind FF E8 -> 0x00010097 Offset 0xFFE8 -> 0x0001007F, was die Indexposition von ID 1 ist;

Die letzten 2 Bytes der Datensatzkopfinformationen der ID 1 sind FF F1 -> 0x0001007F, Offset 0xFFF1 -> 0x00010070, die Datensatzposition des größten Datensatzes;

Es ist ersichtlich, dass die Indexdatensätze über eine unidirektional verknüpfte Liste in Reihe verbunden und nach Indexwerten sortiert sind. Der Nullwert wird zum kleinsten Wert verarbeitet und am Anfang der indexierten Liste platziert, was die äußerste linke Position im Indexbaum darstellt. Das Ergebnis stimmt mit dem vom Tool innodb_ruby analysierten Ergebnis überein.

Gründe für Missverständnisse

Warum verstehen die Leute falsch, dass is null, is not null, != und andere Beurteilungsbedingungen dazu führen, dass der Index fehlschlägt und die gesamte Tabelle gescannt wird?

Der Grund für das Fehlschlagen des Index und des vollständigen Tabellenscans liegt normalerweise darin, dass in einer Abfrage zu viele Tabellen zurückgegeben werden. MySQL geht davon aus, dass der Zeitaufwand bei der Verwendung eines Indexes höher ist als bei einem vollständigen Tabellenscan. Daher scannt MySQL lieber die gesamte Tabelle, als einen Index zu verwenden. Der Zeitaufwand für die Verwendung eines Indexes ist höher als der kritische Wert eines vollständigen Tabellenscans, der sich einfach mit etwa 20 % einprägen lässt.

Einen detaillierten Analysevorgang finden Sie in einem anderen Blogbeitrag des Autors: MySQL-Tabellenrückgabe verursacht Indexfehler.

Das heißt, wenn der durch eine Abfrageanweisung verursachte Tabellenrückgabebereich 20 % aller Datensätze überschreitet, wird der Index ungültig. Beurteilungsbedingungen wie „ist null“, „ist nicht null“ und „!=“ treten häufig in Szenarien auf, in denen der Bereich der zurückgegebenen Tabelle groß ist. Es kann zu Missverständnissen kommen, dass diese Beurteilungsbedingungen dazu führen, dass der Index ungültig wird.

Wiederkehrender Indexfehler

Um den Indexfehler zu reproduzieren, müssen Sie nur einen Tabellenbereich zurückgeben, der mehr als 20 % aller Datensätze umfasst. Fügen Sie 1000 Datensätze ungleich Null wie folgt ein.

Trennzeichen //
PROZEDUR ERSTELLEN init_user_info() 
BEGINNEN 
	DECLARE IndexNr. INT;
	Setzen Sie Indexnummer = 0.
	WHILE IndexNr. < 1000 DO
		TRANSAKTION STARTEN; 
			in user_info(Name, Alter) Werte einfügen (concat(floor(rand()*1000000000)),floor(rand()*100));
			SETZE IndexNr = IndexNr + 1;
		BEGEHEN; 
	ENDE WÄHREND;
ENDE //
Trennzeichen ;
rufen Sie init_user_info() auf;

Zu diesem Zeitpunkt gibt es insgesamt 1003 Datensätze in der Tabelle „user_info“, von denen nur ein Datensatz einen Namenswert von Null hat. Dann wird nur 1/1003 der durch die „ist null“-Beurteilungsanweisung zurückgegebenen Datensätze den kritischen Wert nicht überschreiten, während 1002/1003 der durch die „ist nicht null“-Beurteilungsanweisung zurückgegebenen Datensätze den kritischen Wert bei weitem überschreiten werden, was zu einem Indexfehler führt.

Wie aus den folgenden beiden Abbildungen ersichtlich, wird bei „is null“ der Index immer noch normal verwendet, während bei „is not null“ aufgrund der hohen Tabellenrückgaberate erwartungsgemäß ein vollständiger Tabellenscan der Verwendung des Index vorgezogen wird.

Verwenden Sie die Optimierungsablaufverfolgung von MySQL (unterstützt von MySQL 5.6), um den SQL-Ausführungsplan zu analysieren:

SET optimizer_trace="aktiviert=ein";
Erläutern Sie „select * from user_info“, wobei der Name nicht null ist.
WÄHLEN SIE * AUS INFORMATION_SCHEMA.OPTIMIZER_TRACE;

Der durch die Optimierer-Verfolgung ausgegebene Ausführungsplan zeigt, dass bei dieser Abfrage der Zeitaufwand für einen vollständigen Tabellenscan 206,9 beträgt, während der Zeitaufwand für die Verwendung eines Indexes 1203,4 beträgt, was viel höher ist als bei einem vollständigen Tabellenscan. Daher beschließt MySQL schließlich, die gesamte Tabelle zu scannen, was zu einem Indexfehler führt.

{
    "Zeilenschätzung": [
        {
            "Tabelle": "`Benutzerinfo`",
            "Bereichsanalyse": {
                "Tabellenscan": {
                    "rows": 1004, // Für den vollständigen Tabellenscan müssen 1004 Datensätze gescannt werden. "cost": 206.9 // Für den vollständigen Tabellenscan sind Kosten von 206.9 erforderlich.
                },
                "potenzielle_Bereichsindizes": [
                    {
                        "index": "PRIMÄR",
                        "verwendbar": falsch,
                        "Ursache": "nicht zutreffend"
                    },
                    {
                        "index": "Indexname",
                        "verwendbar": wahr,
                        "Schlüsselteile": [
                            "Name",
                            "Ausweis"
                        ]
                    }
                ],
                "setup_range_conditions": [],
                "Gruppenindexbereich": {
                    "ausgewählt": falsch,
                    "Ursache": "nicht_gruppiert_nach_oder_unterscheidbar"
                },
                "Analysebereich_Alternativen": {
                    "Bereichsscan-Alternativen": [
                        {
                            "index": "Indexname",
                            "Bereiche": [
                                "NULL < Name"
                            ],
                            "index_dives_for_eq_ranges": wahr,
                            "rowid_ordered": falsch,
                            "using_mrr": falsch,
                            "index_only": falsch,
                            "rows": 1002, // Der Index muss 1002 Datensätze scannen "cost": 1203.4, // Die Kosten des Index betragen 1203.4
                            "ausgewählt": falsch,
                            "Ursache": "Kosten"
                        }
                    ],
                    "analyzing_roworder_intersect": {
                        "verwendbar": falsch,
                        "Ursache": "zu wenige Zeilenreihenfolgescans"
                    }
                }
            }
        }
    ]
}

Dies ist das Ende dieses Artikels über die Verwendung des mysql IS NULL-Indexfalls. Weitere relevante Inhalte zur Verwendung von mysql IS NULL finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!