Zusammenfassung der MySQL-Datenbankoptimierungsvorgänge

Dieser Artikel führt Sie hauptsächlich in die allgemeinen Optimierungsvorgänge von MySQL-Datenbanken ein. Der Artikel fasst meine täglichen Erfahrungen bei der Entwicklung und Verwendung von MySQL-Datenbanken zusammen, einschließlich der Verwendung von weniger SELECT*, EXPLAIN SELECT und dem Aktivieren des Abfragecaches wird für alle, die es brauchen, einen gewissen Referenzwert haben.

Vorwort

Für eine datenzentrierte Anwendung wirkt sich die Qualität der Datenbank direkt auf die Leistung des Programms aus, daher ist die Datenbankleistung entscheidend wichtig. Daher muss jeder die Optimierungsoperationen der MySQL-Datenbank verstehen. In diesem Artikel werden hauptsächlich die allgemeinen Optimierungsoperationen in der MySQL-Datenbank zusammengefasst. Ich werde im Folgenden nicht näher darauf eingehen.

1. Index

Natürlich haben wir diese Optimierungsmethode stillschweigend verwendet Es ist der Primärschlüssel Index. Manchmal ist es uns egal, ob ein geeigneter Index definiert ist, die Leistung (Geschwindigkeit) der Datenbankabfrage wird um ein Vielfaches oder sogar Dutzende Male verbessert.

Der normale Index

wird verwendet, um die Abfragegeschwindigkeit zu verbessern.

Tabelle erstellen, Index erstellen

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
index [索引名] (column_name)
);

Index erstellen

CREATE INDEX index_name ON tab_name (column_name)

Index löschen

DROP INDEX index_name FROM tab_name

Index anzeigen

SHOW index FROM tab_name

Primärschlüsselindex

Die Aufgabe besteht darin, Abfragen und eindeutige Einschränkungen zu beschleunigen

Tabellen erstellen und Indizes erstellen

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
PRIMARY KEY(column_name)
);

Indizes erstellen

ALTER TABLE tab_name ADD PRIMARY KEY(column_name)

Index löschen

ALTER TABLE tab_name DROP PRIMAY KEY(column_name)

Eindeutiger Index

Die Funktion besteht darin, Abfragen und eindeutige Einschränkungen zu beschleunigen

Tabellen erstellen und Indizes erstellen

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
unique [索引名] (column_name)
);

Index erstellen

CREATE UNIQUE INDEX index_name ON tab_name (column_name)

Index löschen

DROP UNIQUE INDEX index_name FROM tab_name

2. Weniger SELECT* verwenden

Wenn einige Leute die Datenbank abfragen, wählen sie möglicherweise aus, was sie abfragen möchten. Dies ist ein unangemessenes Verhalten. Wir sollten die Daten erhalten, die wir verwenden möchten, nicht alle, denn wenn wir sie auswählen, erhöht sich die Belastung des Webservers, die Belastung der Netzwerkübertragung steigt und die Abfragegeschwindigkeit nimmt natürlich ab.

3. EXPLAIN SELECT

Es wird geschätzt, dass viele Menschen diese Funktion noch nie gesehen haben, es wird jedoch dringend empfohlen, sie zu verwenden. EXPLAIN zeigt, wie MySQL Indizes verwendet, um Select-Anweisungen zu verarbeiten und Tabellen zu verknüpfen. Kann dabei helfen, bessere Indizes auszuwählen und optimiertere Abfrageanweisungen zu schreiben. Die Hauptverwendung besteht darin, „explain“ vor „select“ hinzuzufügen.

EXPLAIN SELECT [查找字段名] FROM tab_name ...

4. Abfrage-Cache aktivieren

Auf den meisten MySQL-Servern ist der Abfrage-Cache aktiviert. Dies ist eine der effektivsten Möglichkeiten zur Leistungsverbesserung und wird von der MySQL-Datenbank-Engine verwaltet. Wenn viele der gleichen Abfragen mehrmals ausgeführt werden, werden die Abfrageergebnisse in einem Cache abgelegt, sodass nachfolgende identische Abfragen die Tabelle nicht bedienen müssen, sondern direkt auf die zwischengespeicherten Ergebnisse zugreifen.

Der erste Schritt besteht darin, query_cache_type auf ON zu setzen und dann zu prüfen, ob die Systemvariable have_query_cache verfügbar ist:

show variables like 'have_query_cache'

Danach weisen Sie dem Abfragecache Speichergröße zu und Steuern Sie die maximale Größe der zwischengespeicherten Abfragewerte. Relevante Vorgänge werden in der Konfigurationsdatei geändert.

5. Verwenden Sie NOT NULL

Viele Tabellen enthalten Spalten, die NULL (Nullwert) sein können, auch wenn die Anwendung dies nicht muss Speichern Sie sie. Das Gleiche gilt für NULL, da es die Standardeigenschaft einer Spalte ist, NULL zuzulassen. Normalerweise ist es am besten, Spalten als NOT NULL anzugeben, es sei denn, Sie müssen wirklich NULL-Werte speichern.

Abfragen, die NULL-fähige Spalten enthalten, sind schwieriger für MySQL zu optimieren, da NULL-fähige Spalten Indizes, Indexstatistiken und Wertvergleiche komplexer machen. Spalten, die NULL sein können, benötigen mehr Speicherplatz und erfordern eine spezielle Behandlung in MySQL. Wenn NULL-fähige Spalten indiziert werden, erfordert jeder Indexdatensatz ein zusätzliches Byte, was in MyISAM sogar dazu führen kann, dass ein Index fester Größe (z. B. ein Index mit nur einer Ganzzahlspalte) zu einem Index variabler Größe wird.

Normalerweise bringt das Ändern der NULL-Spalte in NOT NULL nur eine geringe Leistungsverbesserung, sodass (bei der Optimierung) keine Notwendigkeit besteht, diese Situation zuerst im vorhandenen Schema zu suchen und zu ändern, es sei denn, Sie sind sicher, dass dies dazu führt ein Problem. Wenn Sie jedoch vorhaben, einen Index für eine Spalte zu erstellen, sollten Sie vermeiden, die Spalte als NULL zu gestalten. Natürlich gibt es Ausnahmen. Erwähnenswert ist beispielsweise, dass InnoDB ein separates Bit zum Speichern von NULL-Werten verwendet, sodass es eine gute Speicherplatzeffizienz für spärliche Daten bietet. Dies gilt jedoch nicht für MyISAM.

6. Auswahl der Speicher-Engine

Was die Auswahl von MyISAM und InnoDB betrifft: Wenn Sie Transaktionsverarbeitung oder Fremdschlüssel benötigen, dann kann InnoDB dies tun ist ein besserer Weg. Wenn Sie eine Volltextindizierung benötigen, ist MyISAM normalerweise eine gute Wahl, da es in das System integriert ist. Wir testen jedoch nicht sehr oft 2 Millionen Datensatzzeilen. Auch wenn es etwas langsamer ist, können wir mit Sphinx einen Volltextindex von InnoDB erhalten.

数据的大小，是一个影响你选择什么样存储引擎的重要因素，大尺寸的数据集趋向于选择InnoDB方式，因为其支持事务处理和故障恢复。数据库的在小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。而MyISAM可能会需要

几个小时甚至几天来干这些事，InnoDB只需要几分钟。

您操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如： COUNT() 在 MyISAM表中会非常快，而在InnoDB表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快，但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts语句在MyISAM下会快一些，但是updates在InnoDB 下会更快一些——尤其在并发量大的时候。

所以，到底你检使用哪一个呢？根据经验来看，如果是一些小型的应用或项目，那么MyISAM也许会更适合。当然，在大型的环境下使用MyISAM也会有很大成功的时候，但却不总是这样的。如果你正在计划使用一个超大数据量的项目，而且需要事务处理或外键支持，那么你真的应该直接使用InnoDB方式。但需要记住InnoDB的表需要更多的内存和存储，转换100GB的MyISAM 表到InnoDB 表可能会让你有非常坏的体验。

七、避免在 where 子句中使用 or 来连接

如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10
union all
select id from t where Name = 'admin'

八、多使用varchar/nvarchar

使用varchar/nvarchar代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

九、避免大数据量返回

这里要考虑使用limit，来限制返回的数据量，如果每次返回大量自己不需要的数据，也会降低查询速度。

十、where子句优化

where 子句中使用参数，会导致全表扫描,因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。

应尽量避免在 where 子句中对字段进行表达式操作，避免在where子句中对字段进行函数操作这将导致引擎放弃使用索引而进行全表扫描。不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

Das obige ist der detaillierte Inhalt vonZusammenfassung der MySQL-Datenbankoptimierungsvorgänge. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!