MySQL Advanced Learning: Ausführliche Erklärung, wie man effiziente und geeignete Indizes erstellt

Dieser Artikel ist eine fortgeschrittene Studie über MySQL. Er wird Ihnen ein detailliertes Verständnis dafür vermitteln, wie Sie einen besser geeigneten Index erstellen können.

Ich weiß nicht, wie wichtig die Indizierung ist, wenn mehr Daten in der Bibliothek vorhanden sind, geschweige denn, wie wichtig geeignete Indizes sind, wenn mehr Daten in der Bibliothek vorhanden sind. In diesem Artikel erfahren Sie, wie Sie einen effizienten und geeigneten Index erstellen. [Verwandte Empfehlungen: MySQL-Video-Tutorial]

1 Wenn Sie Indexspalten zum Abfragen verwenden, versuchen Sie, keine Ausdrücke zu verwenden und die Berechnung in die Geschäftsschicht statt in die Datenbankschicht einzufügen. Die Ergebnisse der beiden SQLs werden unten angezeigt sind gleich, aber die Ausführungspläne der beiden SQLs sind unterschiedlich als die von „actor_id+4“ in „const“. Die Bedeutung des Typs finden Sie hier zu

explain für eine detaillierte Erklärung

2. Versuchen Sie, Primärschlüsselabfragen anstelle anderer Indizes zu verwenden, was keine Tabellenrückfragen verursacht.

Alle unsere Tabellen verfügen grundsätzlich über Primärschlüssel. Verwenden Sie daher in der normalen Entwicklung Indizes, wenn diese verwendet werden können, und Primärschlüsselindizes, wenn diese verwendet werden können.

3. Präfixindex verwenden

Oft sind unsere Indizes tatsächlich Zeichenfolgen, und es werden unweigerlich lange Zeichenfolgen angezeigt, was dazu führt, dass der Index zu viel Platz einnimmt und seine Effizienz verringert. Besonders für lange Spalten wie Blob, Text und Varchar. Zu diesem Zeitpunkt besteht die Möglichkeit, damit umzugehen, nicht darin, den vollständigen Wert des Felds als Index zu verwenden, sondern nur die erste Hälfte zu verwenden (die Selektivität des ausgewählten Präfixindex liegt nahe an der gesamten Spalte). Dies kann den Indexraum erheblich reduzieren und somit die Effizienz verbessern. Der Nachteil besteht darin, dass die Selektivität des Index verringert wird.

Indexselektivität: Das Verhältnis eindeutiger Indexwerte zur Gesamtzahl der Datentabellendatensätze (#T) im Bereich von 1/#T bis 1. Je höher die Selektivität des Index ist, desto höher ist die Abfrageeffizienz, da die Daten hoch differenziert sind und mehr Zeilen herausgefiltert werden können. Die Selektivität des eindeutigen Index beträgt 1 und seine Leistung ist die beste.

Im E-Mail-Feld der Mitarbeitertabelle eines Unternehmens sind beispielsweise die E-Mail-Suffixe eines Unternehmens alle gleich, z. B.

Tatsächlich ist der einzig gültige Teil bei der Verwendung von E-Mail als Index der xxxx-Teil @qq.com ist dasselbe und für den Index bedeutungslos. Ja, natürlich hat nur die Verwendung von xxxx als Index die gleiche Selektivität wie der gesamte Wert, aber die Verwendung von xxxx als Index verringert offensichtlich den Indexraum.

xxxx@qq.com Unten haben wir die Mitarbeitertabelle als Beispiel genommen (Tabellenstruktur und Daten finden Sie am Ende des Artikels).

Wir nehmen das E-Mail-Feld als Beispiel für die Indizierung:

Die E-Mail-Adresse dieser Daten ist tatsächlich eine mobile Telefonnummer

+@qq.com

als Beispiel, tatsächlich sind die ersten 11 Ziffern und die folgenden alle gleich. Ich verwende die folgende SQL, um die Selektivitätsberechnung dieser Daten zu sehen (nimm die ersten 10, 11 bzw. 12).

-- 当是11个前缀的时候选择性是1，在增加字段长度，选择性也不会变化
select count(distinct left(email,10))/count(*) as e10, count(distinct left(email,11))/count(*) as e11,      count(distinctleft(email,12))/count(*) as e12 from employee;

Auf dem Bild oben können wir sehen, dass die Selektivität der Top 10, Top 11 und Top 12 jeweils 0,14, 1,0 und 1,0 beträgt. An der 11. Stelle ist die Indexselektivität also die höchste 1 Es besteht keine Notwendigkeit, all als Index zu verwenden, wodurch der Indexraum vergrößert wird.

-- 创建前缀索引
alter table employee add key(email(11));

Wir können die Anzahl auch verwenden, um die Häufigkeit für Statistiken zu berechnen (je weniger Vorkommen, desto geringer die Wiederholungsrate und desto größer die Selektivität)

4. Verwenden Sie zum Sortieren den Index-Scan.

Wir haben häufig eine Sortierung für Verwenden Sie je nach Bedarf „Ordnung nach“, aber „Ordnung nach“ wirkt sich auf die Leistung aus. Wenn die Datenmenge zu groß ist und nicht im Speicher gespeichert werden kann, kann sie nur mehrmals verarbeitet werden. Der Index selbst ist jedoch geordnet, und es ist einfacher, die Sortierung direkt über den Index durchzuführen.

Das Scannen des Index selbst ist schnell, da Sie nur von einem Indexdatensatz zum nächsten wechseln müssen. Wenn der Index jedoch nicht alle für die Abfrage erforderlichen Spalten abdecken kann, müssen Sie bei jedem Scan zur Tabelle zurückkehren ein Indexdatensatz Fragen Sie die entsprechende Zeile einmal ab, was im Grunde eine zufällige E/A ist. Daher ist das Lesen von Daten in Indexreihenfolge normalerweise langsamer als ein sequenzieller vollständiger Tabellenscan.

MySQL kann denselben Index verwenden, um sowohl das Sortieren als auch das Durchsuchen von Zeilen zu erfüllen. Bitte erwägen Sie, wenn möglich, einen solchen Index zu erstellen.

Nur wenn die Reihenfolge der Indexspalten vollständig mit der Reihenfolge der Order by-Klausel übereinstimmt und die Sortierrichtung (Rückwärts- oder Vorwärtsreihenfolge) aller Spalten gleich ist, kann MySQL den Index zum Sortieren der Ergebnisse verwenden

. Wenn sich die Abfrage auf mehrere Tabellen beziehen muss, kann die Indexsortierung nur verwendet werden, wenn die Felder in der order by-Klausel alle aus der ersten Tabelle stammen. Die Reihenfolge nach Abfrage muss auch das Präfix ganz links im kombinierten Index erfüllen, andernfalls kann die Indexsortierung nicht verwendet werden.

Tatsächlich gibt es bei der Entwicklung zwei Hauptpunkte, auf die man achten sollte:

• Die Felder in der Where-Bedingung und die Felder in der Reihenfolge nach können kombinierte Indizes sein und erfüllen das Präfix ganz links.
• Die Reihenfolge der Felder in der Reihenfolge muss konsistent sein. Desc und asc dürfen nicht existieren.

5. Union all, in oder all können Indizes verwenden, es wird jedoch empfohlen, in zu verwenden. Wie oben wird Union all zweimal ausgeführt, während in und or nur einmal. Gleichzeitig können wir sehen, dass die Ausführungspläne von or und in gleich sind,

aber wir betrachten ihre Ausführungszeit. Wie unten gezeigt, verwenden Sie set profiling=1, um die detaillierte Zeit anzuzeigen, und verwenden Sie showprofiles, um die spezifische Zeit anzuzeigen. Das Bild unten zeigt, dass die Zeit von or 0,00612000 und die Zeit von in 0,00022800 beträgt. Die Lücke ist immer noch sehr groß (die Testtabellendaten haben nur 200 Zeilen)

union all: Die Abfrage ist in zwei Phasen unterteilt. Tatsächlich gibt es ist auch eine Union, die in der täglichen Entwicklung verwendet werden muss. Es wird empfohlen, Union all beim Unioning zu verwenden, da es bei der Union einen zusätzlichen Schritt der eindeutigen Deduplizierung gibt. Versuchen Sie also, Union All zu verwenden. set profiling=1可以看到详细时间，使用show profiles 查看具体时间。下图看出or的时间0.00612000，in的时间0.00022800，差距还是很大的(测试的表数据只有200行)

union all: 查询分为了两阶段，其实还有一个union，在平时开发中必须使用到union的时候推荐使用union all，因为union中多出了distinct去重的步骤。所以尽量用union all。

6. 范围列可以用到索引

范围的条件：>,>=,

范围列可以用到索引，但是范围列后面的列就无法用到索引了(索引最多用于一个范围列)

比如一个组合索引age+name 如果查询条件是where age>18 and name="纪"后面的name是用不到的索引的。

曾经面试被问到不等于是否能够走某个索引，平时没有注意过也没有回答成功，这次亲自做个实验，关于结论请看文末。

7. 强制类型转换会全表扫描

我在employee表中定义了mobile字段是varchar类型且建立索引，我分别用数字和字符串查询.

看看结果: 两者type是不一样的，而且只有字符串才用到索引。

如果条件的值的类型和表中定义的不一致，那么mysql会强制进行类型转换，但是结果是不会走索引，索引在开发中我们需要根据自己定义的类型输入对应的类型值。

8. 数据区分度不高，更新频繁的字段不宜建立索引

• 索引列更新会变更B+树的，频繁更新的会大大降低数据库性能。
• 类似于性别这类(只有男女，或者未知)，不能有效过滤数据。
• 一般区分度在80%以上就可以建立索引，区分度可以使用count(distinct(列名))/count(*)

9. 创建索引的列不允许为null，可能会得到不符合预期的结果

也就是建立索引的字段尽量不要为空，可能会有些意想不到的问题，但是实际工作中也不太可能不为空，所以根据实际业务来处理吧，尽量避免这种情况。

10. 当需要进行表连接的时候，最好不要超过三张表

表连接其实就是多张表循环嵌套匹配，是比较影响性能的， 而且需要join的字段数据类型必须一致，提高查询效率。关于表连接原理后面专门写一篇吧。

11. 能使用limit的时候尽量使用limit。

limit的作用不是仅仅用了分页，本质作用是限制输出。

limit其实是挨个遍历查询数据，如果只需要一条数据添加 limit 1的限制，那么索引指针找到符合条件的数据之后就停止了，不会继续向下判断了，直接返回。如果没有limit，就会继续判断。

但是如果分页取1万条后的5条limit 10000,10005

6. Bereichsspalten können für die Indizierung verwendet werden

Bereichsbedingungen: >,>=,Bereichsspalten können verwendet werden Index, aber die Spalten nach der Bereichsspalte können den Index nicht verwenden (der Index kann für höchstens eine Bereichsspalte verwendet werden)

Zum Beispiel ein kombinierter Index Alter+Name, wenn die Abfragebedingung ist, wobei Alter>18 und name="Ji" ist ein unbenutzter Index. <p></p> <blockquote>Ich wurde einmal in einem Interview gefragt, ob es bedeutet, dass ich einen bestimmten Index nicht beachtet habe oder ihn nicht erfolgreich beantwortet habe der Abschluss. <p></p> <h3 data-id="heading-6">7. Durch die erzwungene Typkonvertierung wird die gesamte Tabelle gescannt</h3> <p></p>Ich habe das Feld <code>mobile in der Mitarbeitertabelle als Varchar-Typ definiert und einen Index erstellt. Verwenden Sie Zahlen und Zeichenfolgen zum Abfragen.

Sehen Sie sich die Ergebnisse an: Die beiden Typen sind unterschiedlich und nur Zeichenfolgen verwenden Indizes. Wenn der Typ des Bedingungswerts nicht mit dem in der Tabelle definierten übereinstimmt, erzwingt MySQL die Typkonvertierung, aber das Ergebnis wird nicht indiziert. Während der Indexentwicklung müssen wir den entsprechenden Typwert entsprechend dem Typ eingeben von uns selbst definiert.

---

8. Die Datenunterscheidung ist nicht hoch und häufig aktualisierte Felder sollten nicht indiziert werden. Indexspaltenaktualisierungen verändern den B+-Baum und häufige Aktualisierungen verringern die Datenbankleistung erheblich. 🎜Ähnlich wie beim Geschlecht (nur männlich und weiblich oder unbekannt) können Daten nicht effektiv gefiltert werden. 🎜🎜Im Allgemeinen kann ein Index erstellt werden, wenn die Unterscheidung über 80 % liegt. Zur Unterscheidung kann count(distinct(column name))/count(*) verwendet werden🎜🎜

9. So erstellen Sie einen Index: Spalten dürfen nicht null sein, und Sie erhalten möglicherweise unerwartete Ergebnisse. Das heißt, die indizierten Felder sollten so weit wie möglich leer sein. Es kann zu unerwarteten Problemen kommen, aber in der tatsächlichen Arbeit ist dies der Fall Es ist unwahrscheinlich, dass es nicht leer sein wird. Lassen Sie sich daher entsprechend der tatsächlichen Situation um das Geschäft kümmern und versuchen Sie, diese Situation zu vermeiden. 🎜

10. Wenn eine Tabellenverbindung erforderlich ist, ist es am besten, nicht mehr als drei Tabellen zu verwenden. 🎜🎜Bei der Tabellenverbindung handelt es sich tatsächlich um eine Schleifenverschachtelung, die sich auf die Leistung auswirkt. Die Datentypen der Felder, die verknüpft werden müssen, müssen konsistent sein, um die Abfrageeffizienz zu verbessern. Lassen Sie uns später einen speziellen Artikel über das Prinzip der Tabellenverbindung schreiben. 🎜

11 Versuchen Sie, Limit zu verwenden, wenn Sie können. 🎜🎜Die Funktion von limit dient nicht nur dem Paging, sondern ihre wesentliche Funktion besteht darin, die Ausgabe zu begrenzen. 🎜🎜limit durchläuft die Abfragedaten tatsächlich einzeln. Wenn Sie nur ein Datenelement benötigen und das Limit von limit 1 hinzufügen, stoppt der Indexzeiger, nachdem er die Daten gefunden hat, die die Bedingungen erfüllen. und wird nicht weiter nach unten richten. Wenn es keine Begrenzung gibt, wird das Urteil fortgesetzt. 🎜🎜Aber wenn Sie nach 10.000 Artikeln blättern und 5 Artikel abrufen limit 10000,10005, müssen Sie vorsichtig sein, dass 10.000 Artikel durchlaufen und 5 Artikel abgerufen werden, was sehr ineffizient ist. Tipp: Wenn der Primärschlüssel sequentiell ist, können Sie paginierte Daten direkt über den Primärschlüssel abrufen. 🎜🎜12. Versuchen Sie, die Anzahl der Einzeltabellenindizes innerhalb von 5 zu kontrollieren. Das Erstellen/Pflegen von Indizes kostet auch Geld und nimmt Platz in Anspruch. Je mehr Indizes, desto besser müssen Indizes sinnvoll eingesetzt werden. 🎜🎜13. Die Anzahl der Felder in einem einzelnen kombinierten Index sollte 5 nicht überschreiten. 🎜🎜Je mehr Felder, desto größer wird der Index und desto mehr Speicherplatz wird belegt. 🎜🎜🎜Je mehr Indizes, desto besser, und die Indizes müssen nicht entworfen werden, wenn Sie mit der Erstellung der Tabelle beginnen. Eine vorzeitige Optimierung ist kein effizienter Index. Sie müssen das Geschäft verstehen und statistische Kompromisse eingehen Erstellen Sie dann relevante Geschäfts-SQL, damit Sie sorgfältiger nachdenken und effektivere und effizientere Indizes erstellen können. 🎜🎜Das Obige sind die kleinen Details, die der Indexoptimierung entsprechen. Ich hoffe, es kann Ihnen beim Schreiben von Swiss SQL helfen

结论：只有主键会走，唯一键和普通索引都不会走。

我在employee表中建了唯一索引employee_num和联合索引employee_num+name，结果就是下图的执行情况。

---

employee表结构

CREATE TABLE `employee`  (  
`employee_id` bigint(20) NOT NULL AUTO_INCREMENT, 
`employee_num` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工编码',
`name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工姓名',  
`email` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '电子邮件', 
`mobile` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '移动电话', 
`gender` tinyint(1) NOT NULL COMMENT '性别, 0: 男 1: 女',  PRIMARY KEY (`employee_id`) USING BTREE, 
INDEX `email`(`email`(11)) USING BTREE,  INDEX `employee_u1`(`employee_num`, `name`) USING BTREE,
UNIQUE INDEX `employee_u2`(`employee_num`) USING BTREE,  INDEX `employee_u3`(`mobile`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;

employee数据如下：

INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (10, '001', '员工A', '15500000001@qq.com', '15500000001', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (11, '002', '员工B', '15500000002@qq.com', '15500000002', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (12, '003', '员工C', '15500000003@qq.com', '15500000003', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (13, '004', '员工D', '15500000004@qq.com', '15500000004', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (14, '005', '员工E', '15500000005@qq.com', '15500000005', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (15, '006', '员工F', '15500000006@qq.com', '15500000006', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (16, '007', '员工G', '15500000007@qq.com', '15500000007', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (17, '008', '员工H', '15500000008@qq.com', '15500000008', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (18, '009', '员工I', '15500000009@qq.com', '15500000009', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (19, '010', '员工J', '15500000010@qq.com', '15500000010', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (20, '011', '员工K', '15500000011@qq.com', '15500000011', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (21, '012', '员工L', '15500000012@qq.com', '15500000012', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (22, '013', '员工M', '15500000013@qq.com', '15500000013', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (23, '014', '员工N', '15500000014@qq.com', '15500000014', 1);

更多编程相关知识，请访问：编程视频！！

Das obige ist der detaillierte Inhalt vonMySQL Advanced Learning: Ausführliche Erklärung, wie man effiziente und geeignete Indizes erstellt. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!