Die zehn besten Optimierungsmethoden für MySQL

1. Wählen Sie das am besten geeignete Feldattribut

Stellen Sie die Breite des Feldes in der Tabelle so klein ein wie Möglich: char Die Obergrenze von Varchar beträgt 255 Byte (fester belegter Speicherplatz), die Obergrenze von Varchar beträgt 65535 Byte (tatsächlich belegter Speicherplatz) und die Obergrenze von Text beträgt 65535. char ist effizienter als varchar.

Versuchen Sie, Felder auf NOT NULL zu setzen. Beim Ausführen von Abfragen muss die Datenbank keine NULL-Werte vergleichen.

2. Verwenden Sie Joins (JOIN) anstelle von Unterabfragen (Sub-Queries)

Der Grund, warum Joins (JOIN) effizienter sind, liegt darin, dass MySQL dies nicht tut Sie müssen eine temporäre Tabelle im Speicher erstellen, um diese logische Abfrage abzuschließen, die zwei Schritte erfordert (gemeinsame Abfragebedingungen plus Indizes sind schneller).

3. Verwenden Sie UNION, um manuell erstellte temporäre Tabellen zu ersetzen.

Eine Abfrage, die zwei oder mehr SELECT-Abfragen kombiniert, die die Verwendung temporärer Tabellen erfordern.

SELECT Name, Phone FROM client UNION SELECT Name, Birthdate FROM author UNION SELECT Name, Lieferant FROM product;

4. Transaktionen

Obwohl wir Sie können Unterabfragen (Sub-Queries), Verbindungen (JOIN) und Unions (UNION) verwenden, um eine Vielzahl von Abfragen zu erstellen, aber nicht alle Datenbankoperationen können mit nur einer oder wenigen SQL-Anweisungen abgeschlossen werden. Häufiger ist eine Reihe von Erklärungen erforderlich, um eine bestimmte Art von Arbeit abzuschließen.

Der Effekt ist: Entweder wird jede Anweisung im Anweisungsblock erfolgreich ausgeführt, oder alle schlagen fehl. Mit anderen Worten: Die Konsistenz und Integrität der Daten in der Datenbank kann gewahrt bleiben. Die Dinge beginnen mit dem Schlüsselwort BEGIN und enden mit dem Schlüsselwort COMMIT. Wenn in diesem Zeitraum ein SQL-Vorgang fehlschlägt, kann der ROLLBACK-Befehl die Datenbank in den Zustand vor dem Start von BEGIN zurückversetzen.

5. Sperren von Tabellen

Obwohl Transaktionen eine sehr gute Möglichkeit sind, die Datenbankintegrität aufrechtzuerhalten, beeinträchtigen sie aufgrund ihrer Exklusivität manchmal die Datenbankleistung . Da die Datenbank während der Ausführung der Transaktion gesperrt wird, können andere Benutzeranforderungen nur warten, bis die Transaktion beendet ist.

LOCK TABLE inventory WRITE 
SELECT Quantity FROM inventory 
WHEREItem='book'; 
... 
UPDATE inventory SET Quantity=11 
WHEREItem='book'; 
UNLOCK TABLES

Hier verwenden wir eine SELECT-Anweisung, um durch einige Berechnungen die Anfangsdaten zu erhalten, und verwenden eine UPDATE-Anweisung, um die neuen Werte in der Tabelle zu aktualisieren. Die LOCK TABLE-Anweisung mit dem Schlüsselwort WRITE stellt sicher, dass vor der Ausführung des UNLOCK TABLES-Befehls kein weiterer Zugriff auf den Bestand zum Einfügen, Aktualisieren oder Löschen erfolgt.

6. Die Verwendung von Fremdschlüsseln

zum Sperren der Tabelle kann die Integrität der Daten aufrechterhalten, die Relevanz der Daten kann jedoch nicht garantiert werden. Zu diesem Zeitpunkt können wir Fremdschlüssel verwenden. Beispielsweise kann ein Fremdschlüssel sicherstellen, dass jeder Verkaufsdatensatz auf einen bestehenden Kunden verweist. Hier kann der Fremdschlüssel die CustomerID in der Tabelle „customerinfo“ der Tabelle „customerID“ in der Tabelle „salesinfo“ zuordnen. Jeder Datensatz ohne gültige CustomerID wird nicht aktualisiert oder in salesinfo eingefügt.

CREATE TABLE customerinfo 
( 
CustomerID INT NOT NULL , 
PRIMARY KEY ( CustomerID ) 
) TYPE = INNODB; 
CREATE TABLE salesinfo 
( 
SalesID INT NOT NULL, 
CustomerID INT NOT NULL, 
PRIMARY KEY(CustomerID, SalesID), 
FOREIGN KEY (CustomerID) REFERENCES customerinfo 
(CustomerID) ON DELETECASCADE 
) TYPE = INNODB;

Beachten Sie den Parameter „ON DELETE CASCADE“ im Beispiel. Dieser Parameter stellt sicher, dass beim Löschen eines Kundendatensatzes in der Tabelle „customerinfo“ auch alle Datensätze, die sich auf den Kunden in der Tabelle „salesinfo“ beziehen, automatisch gelöscht werden. Wenn Sie Fremdschlüssel in MySQL verwenden möchten, denken Sie beim Erstellen der Tabelle unbedingt daran, den Tabellentyp als transaktionssicheren InnoDB-Typ zu definieren. Dieser Typ ist nicht der Standardtyp für MySQL-Tabellen. Es wird durch Hinzufügen von TYPE=INNODB zur CREATE TABLE-Anweisung definiert.

7. Bei Verwendung des Index

ist die Leistungsverbesserung deutlicher, wenn die Abfrageanweisung Befehle wie MAX (), MIN () und ORDERBY enthält.

Indizes sollten auf den Feldern erstellt werden, die für die JOIN-, WHERE-Beurteilung und ORDER BY-Sortierung verwendet werden. Versuchen Sie, kein Feld in der Datenbank zu indizieren, das eine große Anzahl doppelter Werte enthält. Bei einem Feld vom Typ ENUM ist es sehr wahrscheinlich, dass es eine große Anzahl doppelter Werte gibt, wie zum Beispiel das Feld „Provinz“. Die Erstellung eines Indexes für ein solches Feld ist im Gegenteil nicht hilfreich Reduzieren Sie die Datenbankleistung.

Die einzige Aufgabe eines normalen Index (ein durch die Schlüsselwörter KEY oder INDEX definierter Index) besteht darin, den Zugriff auf Daten zu beschleunigen. Daher sollten Indizes nur für die Datenspalten erstellt werden, die am häufigsten in Abfragebedingungen (WHEREcolumn=) oder Sortierbedingungen (ORDERBYcolumn) vorkommen.

Die Vorteile des eindeutigen Index: Erstens vereinfacht MySQL die Verwaltung dieses Index und der Index wird effizienter. Zweitens überprüft MySQL automatisch die neuen Datensätze, wenn neue Datensätze in die Datentabelle eingefügt werden Der Wert dieses Felds des Datensatzes ist bereits in diesem Feld eines Datensatzes enthalten. Wenn dies der Fall ist, weigert sich MySQL, den neuen Datensatz einzufügen. Mit anderen Worten: Ein eindeutiger Index kann die Einzigartigkeit von Datensätzen sicherstellen. In vielen Fällen besteht der Zweck der Erstellung eines eindeutigen Index nicht darin, die Zugriffsgeschwindigkeit zu verbessern, sondern lediglich darin, Datenduplizierung zu vermeiden.

8. Optimierte Abfrageanweisungen

SELECT FROM order WHERE YEAR(OrderDate)<2001; 
SELECT FROM order WHERE OrderDate<"2001-01-01";
SELECT FROM inventory WHERE Amount/7<24; 
SELECT FROM inventory WHERE Amount<24*7;

Lassen Sie MySQL keine automatische Typkonvertierung in der Abfrage durchführen, da der Konvertierungsprozess auch den Index ineffektiv macht.

9. Indexfehler

like 以 % 开头，索引无效；当 like 前缀没有 %，后缀有 % 时，索引有效。

or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有当 or 左右查询字段均为索引时，才会生效。

组合索引，不是使用第一列索引，索引失效。

数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描。

在索引字段上使用 not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

当全表扫描速度比索引速度快时，mysql 会使用全表扫描，此时索引失效。

应尽量避免在 where 子句中使用 or,and,in,not in 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，合理使用 union all（允许重复的值，请使用 UNION ALL）。

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10    
union all    
select id from t where num=20

10. 引擎的选取

MyISAM 索引文件在数据库中存放的对应表的磁盘文件有.frm,.MYD,*.MYI 结尾的三个文件：

frm 文件是存放的表结构，表的定义信息；

MYD 文件是存放着表中的数据；

MYI 文件存放着表的索引信息；

InnoDB 存储引擎在磁盘中存放的对应的表的磁盘文件有.frm,.ibd 这两个文件；

frm 文件是存放表结构，表的定义信息；

ibd 文件是存放 表中的数据、索引信息；

详细出处参考：https://blog.csdn.net/jinxingfeng_cn/article/details/16878355

性能方面的优化：

explain 执行计划 ==>https://blog.csdn.net/yhl_jxy/article/details/88570154

一、分表的分类（单表记录条数达到百万到千万级别时就要使用分表）

1. 纵向分表

文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。

浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。

首先存储引擎的使用不同，冷数据使用 MyIsam 可以有更好的查询数据。活跃数据，可以使用 Innodb , 可以有更好的更新速度。

就是把原来一张表里的字段，冷数据的字段和活跃数据的字段分别建立 2 张表来管理。

2. 横向分表

把大的表结构，横向切割为同样结构的不同表，如，用户信息表，user_1,user_2 等，表结构是完全一样。

二、慢查询

show variables like 'slow%';
show global status like 'slow%';

使用 mysqlreport；

正确使用索引：explain 分析查询语句，组合索引，索引副作用（占空间、update）

开启慢查询日志、使用慢查询分析工具 mysqlsla；

索引缓存、索引代价（插入更新索引）；

表锁，行锁，行锁副作用（update 多时候变慢），在 select 和 update 混合的情况下，行锁巧妙解决了读写互斥的问题；

开启使用查询缓存；

修改临时表内存空间；

开启线程池；

MySQL Query 语句优化的基本思路和原则

1、优化需要优化的 Query；

2、定位优化对象的性能瓶颈；

3、明确优化目标；

4、从 Explaing 入手；

5、多使用 Profile；

6、永远用小结果集推动大的结果集；

7、尽可能在索引中完成排序；

8、只取自己需要的 Columns；

9、仅仅使用最有效的过滤条件；

10、尽可能避免复杂的 Join 和子查询。

推荐教程：《MySQL教程》

Das obige ist der detaillierte Inhalt vonDie zehn besten Optimierungsmethoden für MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!