Große Enthüllung! MySQL-Datenbankindex

1. Übersicht

Index ist eine Datenstruktur, die von Speicher-Engines zum schnellen Auffinden von Datensätzen verwendet wird. Als nächstes können wir die Systemzugriffsleistung erheblich verbessern Führen Sie hauptsächlich die Indextypen in der

MySql-Datenbank ein und erfahren Sie, wie Sie sinnvollere und effizientere Indextechniken erstellen.

Hinweis: Der Schwerpunkt liegt hier auf der B+Tree-Indexdatenstruktur der InnoDB-Speicher-Engine

2. Die Vorteile der Indizierung

sind großartig Reduziert die Datenmenge, die der Server scannen muss, und erhöht dadurch die Geschwindigkeit des Datenabrufs

Hilft dem Server, Sortierungen und temporäre Tabellen zu vermeiden

Kann zufällige I/ O in sequentielle E/A

3. Erstellung des Index

3.1. Primärschlüsselindex

rrree

3.2. Eindeutiger Index

ALTER TABLE 'table_name' ADD PRIMARY KEY 'index_name' ('column');

3.3, gewöhnlicher Index

ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column');

3.4, Volltextindex

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column');

3.5, kombinierter Index

ALTER TABLE 'table_name' ADD FULLTEXT 'index_name' ('column');

4. B+Tree-Indexregeln

Erstellen Sie eine Testbenutzertabelle

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column1', 'column2', ...);

Erstellen Sie einen Kombinationsindex: ALTER TABLE user_test ADD INDEX idx_user(user_name, city, age);

4.1. Abfrage mit gültigem Index

4.1.1. Vollständiger Wertabgleich

Der vollständige Wertabgleich bezieht sich auf den Abgleich mit allen Spalten im Index. Wenn Sie beispielsweise den oben erstellten Index verwenden, können Sie (Benutzername, Stadt, Alter) gleichzeitig nach der Wo-Bedingung) sind die Daten der

-Bedingung.

Hinweis: Es hat nichts mit der Reihenfolge der Abfragebedingungen nach „wo“ zu tun. Dies ist eine Stelle, an der viele Schüler

DROP TABLE IF EXISTS user_test;CREATE TABLE user_test(    id int AUTO_INCREMENT PRIMARY KEY,
    user_name varchar(30) NOT NULL,
    sex bit(1) NOT NULL DEFAULT b'1',
    city varchar(50) NOT NULL,
    age int NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

leicht missverstehen 4.1.2. Übereinstimmung mit dem Präfix ganz links

Die Übereinstimmung mit dem Präfix ganz links bedeutet, dass zuerst die Indexspalte ganz links abgeglichen wird. Beispiel: Der oben erstellte Index kann für Abfragebedingungen verwendet werden: (Benutzername), (Benutzername, Stadt). ), (Benutzername, Stadt, Alter)

Hinweis: Die Reihenfolge, die die Abfragebedingungen für das Präfix ganz links erfüllt, hat nichts mit der Reihenfolge der Indexspalten zu tun, wie zum Beispiel: (Stadt, Benutzername), (Alter, Stadt, Benutzername)

4.1.3. Das passende Spaltenpräfix

bezieht sich auf den Anfang des passenden Spaltenwerts , wie zum Beispiel: Fragen Sie alle Benutzer ab, deren Benutzername mit feinik beginnt

SELECT * FROM user_test WHERE user_name = 'feinik' AND age = 26 AND city = '广州';

4.1.4, passender Bereichswert

Zum Beispiel: Fragen Sie alle Benutzer ab, deren Benutzername mit feinik beginnt. Hier wird die erste Spalte des Index verwendet.

4.2 Indexeinschränkungen

1 Die Indexspalte ganz links in der Indexspalte ist nicht in der Where-Abfragebedingung enthalten, z. B.:

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

oder

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

oder

SELECT * FROM user_test WHERE city = '广州';

2. Selbst wenn die Abfragebedingung „wo“ die Indexspalte ganz links ist, kann der Index nicht zum Abfragen von Benutzern verwendet werden, deren Benutzernamen mit feinik

SELECT * FROM user_test WHERE age= 26;

enden. 3. Wenn eine Bereichsabfrage von a vorliegt Bestimmte Spalte in der Where-Abfragebedingung, alle Spalten rechts können nicht mit der Indexoptimierung abgefragt werden, wie zum Beispiel:

SELECT * FROM user_test WHERE city = '广州' AND age = '26';

5. Effiziente Indizierungsstrategie

5.1. Indexspalten dürfen nicht Teil eines Ausdrucks oder Parameters einer Funktion sein, andernfalls kann die Indexabfrage nicht verwendet werden.

SELECT * FROM user_test WHERE user_name like '%feinik';

5.2. Präfixindex

Manchmal ist es notwendig, sehr lange Zeichenspalten zu indizieren, was den Speicherplatz des Index erhöht Aus Effizienzgründen besteht eine Strategie darin, einen Hash-Index zu verwenden, und eine andere darin, einen Präfixindex für

zu verwenden. Der Präfixindex wählt die ersten n Zeichen der Zeichenspalte als Index aus kann Indexplatz erheblich sparen und dadurch die Indizierungseffizienz verbessern.

5.2.1. Selektivität des Präfixindex

Der Präfixindex sollte ein Präfix wählen, das lang genug ist, um eine hohe Selektivität zu gewährleisten, aber gleichzeitig nicht zu lang , können wir den Auswahllängenwert des entsprechenden Präfixindex auf folgende Weise berechnen:

(1)

SELECT * FROM user_test WHERE user_name = 'feinik' AND city LIKE '广州%' AND age = 26;

Hinweis: Berechnet nach der oben genannten Methode Das Selektivitätsverhältnis des Präfixindex. Je höher das Verhältnis, desto effizienter ist der Index.

(2)

SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');

Hinweis: Finden Sie es Schritt für Schritt Durch die obigen Sätze kann das Selektivitätsverhältnis, das dem Präfixindex in (1) am nächsten kommt, verwendet werden, um den Präfixindex mit der entsprechenden Zeichenkürzungslänge zu erstellen

5.2.2 index

SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name; -- index_column代表要添加前缀索引的列

5.2.3. Zu beachtende Punkte bei der Verwendung des Präfixindex

Der Präfixindex ist eine effektive Möglichkeit, den Index kleiner und schneller zu machen , aber MySql kann keine Präfixindizes für ORDER BY und GROUP BY und Präfixindizes für Coverage-Scans

verwenden.

5.3、选择合适的索引列顺序

在组合索引的创建中索引列的顺序非常重要，正确的索引顺序依赖于使用该索引的查询方式，对于组合索引的索引顺序可以通过经验

法则来帮助我们完成：将选择性最高的列放到索引最前列，该法则与前缀索引的选择性方法一致，但并不是说所有的组合索引的顺序

都使用该法则就能确定，还需要根据具体的查询场景来确定具体的索引顺序。

5.4 聚集索引与非聚集索引

1、聚集索引

聚集索引决定数据在物理磁盘上的物理排序，一个表只能有一个聚集索引，如果定义了主键，那么InnoDB会通过主键来聚集数据，如

果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索

引。

聚集索引可以很大程度的提高访问速度，因为聚集索引将索引和行数据保存在了同一个B-Tree中，所以找到了索引也就相应的找到了

对应的行数据，但在使用聚集索引的时候需注意避免随机的聚集索引（一般指主键值不连续，且分布范围不均匀），如使用UUID来作

为聚集索引性能会很差，因为UUID值的不连续会导致增加很多的索引碎片和随机I/O，最终导致查询的性能急剧下降。

2、非聚集索引

与聚集索引不同的是非聚集索引并不决定数据在磁盘上的物理排序，且在B-Tree中包含索引但不包含行数据，行数据只是通过保存在

B-Tree中的索引对应的指针来指向行数据，如：上面在（user_name，city, age）上建立的索引就是非聚集索引。

5.5、覆盖索引

如果一个索引（如：组合索引）中包含所有要查询的字段的值，那么就称之为覆盖索引，如：

SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;

因为要查询的字段（user_name, city, age）都包含在组合索引的索引列中，所以就使用了覆盖索引查询，查看是否使用了覆盖索引可

以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引，覆盖索引可以极大的提高访问性能。

5.6、如何使用索引来排序

在排序操作中如果能使用到索引来排序，那么可以极大的提高排序的速度，要使用索引来排序需要满足以下两点即可。

1、ORDER BY子句后的列顺序要与组合索引的列顺序一致，且所有排序列的排序方向（正序/倒序）需一致

2、所查询的字段值需要包含在索引列中，及满足覆盖索引

通过例子来具体分析

在user_test表上创建一个组合索引

ALTER TABLE user_test ADD INDEX index_user(user_name , city , age);

可以使用到索引排序的案例

1、SELECT user_name, city, age FROM user_test ORDER BY user_name;

2、SELECT user_name, city, age FROM user_test ORDER BY user_name, city;

3、SELECT user_name, city, age FROM user_test ORDER BY user_name DESC, city DESC;

4、SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' ORDER BY city;

注：第4点比较特殊一点，如果where查询条件为索引列的第一列，且为常量条件，那么也可以使用到索引

无法使用索引排序的案例

1、sex不在索引列中

SELECT user_name, city, age FROM user_test ORDER BY user_name, sex;

2、排序列的方向不一致

SELECT user_name, city, age FROM user_test ORDER BY user_name ASC, city DESC;

3、所要查询的字段列sex没有包含在索引列中

SELECT user_name, city, age, sex FROM user_test ORDER BY user_name;

4、where查询条件后的user_name为范围查询，所以无法使用到索引的其他列

SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'feinik%' ORDER BY city;

5、多表连接查询时，只有当ORDER BY后的排序字段都是第一个表中的索引列（需要满足以上索引排序的两个规则）时，方可使用索

引排序。如：再创建一个用户的扩展表user_test_ext，并建立uid的索引。

DROP TABLE IF EXISTS user_test_ext;CREATE TABLE user_test_ext(    id int AUTO_INCREMENT PRIMARY KEY,

 uid int NOT NULL,
 u_password VARCHAR(64) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;ALTER TABLE user_test_ext ADD INDEX 
 index_user_ext(uid);

走索引排序

SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY u.user_name;

不走索引排序

SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY ue.uid;

6、总结

本文主要讲了B+Tree树结构的索引规则，不同索引的创建，以及如何正确的创建出高效的索引技巧来尽可能的提高查询速度，当然了

关于索引的使用技巧不单单只有这些，关于索引的更多技巧还需平时不断的积累相关经验。

Das obige ist der detaillierte Inhalt vonGroße Enthüllung! MySQL-Datenbankindex. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!