Interviewer: Kennen Sie sich mit SQL-Optimierung aus? Ich kenne nur 20 Arten, aber es gibt weit mehr ...

Während des Interviews fragt der Interviewer gerne:

Kennen Sie sich mit SQL-Optimierung aus?

Haben Sie keine Angst, wenn Sie mit solchen Problemen konfrontiert werden. Bruder Tian hat die folgenden 52SQL-AnweisungenStrategien zur Leistungsoptimierung für Sie vorbereitet. Wenn es nicht klappt, merken Sie sich einfach noch ein paar Punkte und es wird immer noch kein Problem sein, sich um Ihre unmittelbaren Bedürfnisse zu kümmern.

„Optimierungsstrategie“

1 Um die Abfrage zu optimieren, sollten Sie versuchen, vollständige Tabellenscans zu vermeiden. Sie sollten zunächst in Betracht ziehen, Indizes für die an WHERE und ORDER BY beteiligten Spalten zu erstellen.

2. Vermeiden Sie die NULL-Wertbeurteilung von Feldern in der WHERE-Klausel. In den meisten Fällen sollten Sie jedoch NOT NULL oder einen Sonderwert wie 0, -1 verwenden als Standardwert.

3. Vermeiden Sie die Verwendung der Operatoren != oder a8093152e673feb7aba1828c43532094 MySQL verwendet Indizes nur für die folgenden Operatoren: 23735d90c0339e974c9b8bbfdf9ee4cb, >=, BETWEEN, IN und manchmal LIKE.

4. Versuchen Sie, die Verwendung von OR in der WHERE-Klausel zu vermeiden, da die Engine sonst die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt. Sie können UNION verwenden, um die Abfrage zusammenzuführen:

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

5 IN sollte ebenfalls mit Vorsicht verwendet werden, da es sonst zu einem vollständigen Tabellenscan kommt. Verwenden Sie für kontinuierliche Werte nicht IN, wenn Sie BETWEEN verwenden können:

select id from t where num between 1 and 3。

6. Die folgende Abfrage führt ebenfalls zu einem vollständigen Tabellenscan:

select id from t where name like‘%abc%’

oder

select id from t where name like‘%abc’

Um die Effizienz zu verbessern, können Sie eine Volltextsuche in Betracht ziehen. Und

select id from t where name like‘abc%’

才用到索引。

7、如果在 WHERE 子句中使用参数，也会导致全表扫描。

8、应尽量避免在 WHERE 子句中对字段进行表达式操作，应尽量避免在 WHERE 子句中对字段进行函数操作。

9、很多时候用 EXISTS 代替 IN 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

10、索引固然可以提高相应的 SELECT 的效率，但同时也降低了 INSERT 及 UPDATE 的效。因为 INSERT 或 UPDATE 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

11、应尽可能的避免更新 clustered 索引数据列， 因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

12、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

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

14、最好不要使用返回所有：select from t ，用具体的字段列表代替 “*”，不要返回用不到的任何字段。

15、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

16、使用表的别名（Alias）：当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个 Column 上。这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。

17、使用“临时表”暂存中间结果 ：

简化 SQL 语句的重要方法就是采用临时表暂存中间结果。但是临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在 tempdb 中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

18、一些 SQL 查询语句应加上 nolock，读、写是会相互阻塞的，为了提高并发性能。对于一些查询，可以加上 nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据。

使用 nolock 有3条原则：

• 查询的结果用于“插、删、改”的不能加 nolock；
• 查询的表属于频繁发生页分裂的，慎用 nolock ；
• 使用临时表一样可以保存“数据前影”，起到类似 Oracle 的 undo 表空间的功能，能采用临时表提高并发性能的，不要用 nolock。

19、常见的简化规则如下：

不要有超过 5 个以上的表连接（JOIN），考虑使用临时表或表变量存放中间结果。少用子查询，视图嵌套不要过深，一般视图嵌套不要超过 2 个为宜。

20、将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段，例如医院的住院费计算。

21、用 OR 的字句可以分解成多个查询，并且通过 UNION 连接多个查询。他们的速度只同是否使用索引有关，如果查询需要用到联合索引，用 UNION all 执行的效率更高。多个 OR 的字句没有用到索引，改写成 UNION 的形式再试图与索引匹配。一个关键的问题是否用到索引。

22、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。

23、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。

存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的 SQL 语句，是控制流语言的集合，速度当然快。反复执行的动态 SQL，可以使用临时存储过程，该过程（临时表）被放在 Tempdb 中。

24、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用配制线程数量1b837df401709d65ad33b953e9387142=”，不要使用 “>”。

28、索引的使用规范：

索引的创建要与应用结合考虑，建议大的 OLTP 表不要超过 6 个索引；尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过 index index_name 来强制指定索引；避免对大表查询时进行 table scan，必要时考虑新建索引；在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用；要注意索引的维护，周期性重建索引，重新编译存储过程。

29、下列 SQL 条件语句中的列都建有恰当的索引，但执行速度却非常慢：

SELECT * FROM record WHERE substrINg(card_no, 1, 4) = '5378' --13秒 
SELECT * FROM record WHERE amount/30 < 1000 --11秒 
SELECT * FROM record WHERE convert(char(10), date, 112) = &#39;19991201&#39; --10秒

分析：

WHERE 子句中对列的任何操作结果都是在 SQL 运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引。

如果这些结果在查询编译时就能得到，那么就可以被 SQL 优化器优化，使用索引，避免表搜索，因此将 SQL 重写成下面这样：

SELECT * FROM record WHERE card_no like &#39;5378%&#39; -- < 1秒 
SELECT * FROM record WHERE amount < 1000*30 -- < 1秒 
SELECT * FROM record WHERE date = &#39;1999/12/01&#39; -- < 1秒

30、当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新。

31、在所有的存储过程中，能够用 SQL 语句的，我绝不会用循环去实现。

例如：列出上个月的每一天，我会用 connect by 去递归查询一下，绝不会去用循环从上个月第一天到最后一天。

32、选择最有效率的表名顺序（只在基于规则的优化器中有效）：

Oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表（基础表 driving table）将被最先处理，在 FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

如果有 3 个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。

33、提高 GROUP BY 语句的效率，可以通过将不需要的记录在 GROUP BY 之前过滤掉。下面两个查询返回相同结果，但第二个明显就快了许多。

低效：

SELECT JOB, AVG(SAL) 
FROM EMP 
GROUP BY JOB 
HAVING JOB = 'PRESIDENT' 
OR JOB = 'MANAGER'

高效：

SELECT JOB, AVG(SAL) 
FROM EMP
WHERE JOB = 'PRESIDENT' 
OR JOB = 'MANAGER' 
GROUP BY JOB

34、SQL 语句用大写，因为 Oracle 总是先解析 SQL 语句，把小写的字母转换成大写的再执行。

35、别名的使用，别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快 1.5 倍。

36、避免死锁，在你的存储过程和触发器中访问同一个表时总是以相同的顺序；事务应经可能地缩短，在一个事务中应尽可能减少涉及到的数据量；永远不要在事务中等待用户输入。

37、避免使用临时表，除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替。大多数时候（99%），表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在 TempDb 数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。

38、最好不要使用触发器：

触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程；如果能够使用约束实现的，尽量不要使用触发器；不要为不同的触发事件（Insert、Update 和 Delete）使用相同的触发器；不要在触发器中使用事务型代码。

39、索引创建规则：

Der Primärschlüssel und der Fremdschlüssel der Tabelle müssen über Indizes verfügen. Tabellen mit mehr als 300 Datenmengen sollten über Indizes für die Verbindungsfelder verfügen, die häufig in der WHERE-Klausel erscheinen. Insbesondere sollten Indizes auf Feldern in großen Tabellen erstellt werden. Indizes sollten nicht auf Feldern mit großem Text erstellt werden. Auch bei der Erstellung zusammengesetzter Indizes ist Vorsicht geboten Versuchen Sie stattdessen die Verwendung eines Einzelfeldindexes in Betracht zu ziehen. Wählen Sie im Allgemeinen ein Feld mit besserer Selektivität aus, da in der WHERE-Klausel im AND-Modus häufig mehrere Felder angezeigt werden gleichzeitig? Gibt es wenige oder keine Einzelfeldabfragen? Wenn ja, können Sie einen zusammengesetzten Index erstellen. Wenn die im zusammengesetzten Index enthaltenen Felder häufig einzeln in der WHERE-Klausel vorkommen, teilen Sie ihn in mehrere Einzelfeldindizes auf 3 Felder, dann prüfen Sie sorgfältig die Notwendigkeit und erwägen Sie, die Anzahl der zusammengesetzten Felder zu reduzieren. Wenn für diese Felder sowohl Einzelfeldindizes als auch zusammengesetzte Indizes vorhanden sind, können Sie im Allgemeinen nicht zu viele Tabellen erstellen, die häufig Daten ausführen Indizes: Löschen Sie nutzlose Indizes, um negative Auswirkungen auf die Ausführungspläne zu vermeiden. Jeder in der Tabelle erstellte Index erhöht den Verarbeitungsaufwand für Einfügungs-, Lösch- und Aktualisierungsvorgänge. Darüber hinaus sind zu viele zusammengesetzte Indizes im Allgemeinen wertlos, wenn Einzelfeldindizes vorhanden sind. Im Gegenteil, sie verringern auch die Leistung beim Hinzufügen und Löschen von Daten, insbesondere bei häufig aktualisierten Tabellen. Die negativen Auswirkungen sind noch größer . Versuchen Sie, kein Feld in der Datenbank zu indizieren, das eine große Anzahl doppelter Werte enthält.

40. Zusammenfassung der MySQL-Abfrageoptimierung:

Verwenden Sie langsame Abfrageprotokolle, um langsame Abfragen zu erkennen, verwenden Sie Ausführungspläne, um festzustellen, ob Abfragen normal ausgeführt werden, und testen Sie Ihre Abfragen stets, um festzustellen, ob sie optimal ausgeführt werden.

Die Leistung ändert sich im Laufe der Zeit immer. Vermeiden Sie die Verwendung von count(*) für die gesamte Tabelle, da dadurch möglicherweise die gesamte Tabelle gesperrt wird. Machen Sie die Abfrage konsistent, sodass nachfolgende ähnliche Abfragen den Abfragecache verwenden können. Verwenden Sie stattdessen in geeigneten Situationen GROUP BY Verwenden Sie bei DISTINCT indizierte Spalten in den Klauseln WHERE, GROUP BY und ORDER BY, halten Sie Indizes einfach und schließen Sie nicht dieselbe Spalte in mehrere Indizes ein.

Manchmal verwendet MySQL den falschen Index. Verwenden Sie in diesem Fall USE INDEX und überprüfen Sie das Problem der Verwendung von SQL_MODE=STRICT. Bei Indexfeldern mit weniger als 5 Datensätzen ist die Verwendung von LIMIT in UNION kein OR.

Um SELECT vor der Aktualisierung zu vermeiden, verwenden Sie INSERT ON DUPLICATE KEY oder INSERT IGNORE, verwenden Sie nicht UPDATE zur Implementierung, verwenden Sie keine MAX-Indexfelder und ORDER BY-Klausel. LIMIT M, N kann die Abfrage in einigen Fällen tatsächlich verlangsamen. Verwenden Sie sparsam, verwenden Sie UNION in der WHERE-Klausel anstelle von Unterabfragen. Denken Sie vor dem Neustart von MySQL daran, Ihre Datenbank aufzuwärmen, um sicherzustellen, dass sich die Daten im Speicher befinden und Abfragen schnell sind. Erwägen Sie dauerhafte Verbindungen anstelle mehrerer Verbindungen, um den Overhead zu reduzieren.

Benchmark-Abfragen, einschließlich der Auslastung des Servers. Manchmal kann sich eine einfache Abfrage auf andere Abfragen auswirken, wenn die Auslastung des Servers zunimmt. Verwenden Sie SHOW PROCESSLIST, um langsame und problematische Abfragen in den gespiegelten Daten zu erkennen verdächtige Anfragen.

41. MySQL-Backup-Prozess:

Sicherung vom sekundären Replikationsserver; stoppen Sie die Replikation während der Sicherung, um Inkonsistenzen bei Datenabhängigkeiten und Fremdschlüsseleinschränkungen zu vermeiden; wenn Sie MySQL-Dump für die Sicherung verwenden, führen Sie bitte gleichzeitig eine Sicherung durch Binäre Protokolldateien – stellen Sie sicher, dass die Replikation nicht unterbrochen wird; vertrauen Sie nicht LVM-Snapshots, die wahrscheinlich zu Dateninkonsistenzen führen, die Ihnen in Zukunft Probleme bereiten werden, und exportieren Sie Daten in Tabelleneinheiten – falls dies der Fall ist anders als andere Tisch isoliert. Verwenden Sie –opt, wenn Sie mysqldump verwenden; überprüfen und optimieren Sie Tabellen vor dem Sichern; für einen schnelleren Import deaktivieren Sie vorübergehend Fremdschlüsseleinschränkungen während des Imports. ; Um den Import zu beschleunigen, deaktivieren Sie vorübergehend die Größe der Datenbank, der Tabelle und des Index, um das Wachstum der Datengröße besser zu überwachen und Backups regelmäßig durchzuführen.

42. Der Abfragepuffer verarbeitet Leerzeichen nicht automatisch, wenn Sie SQL-Anweisungen schreiben, insbesondere die Leerzeichen am Anfang und Ende von SQL (da der Abfragecache die Leerzeichen nicht automatisch abfängt). Anfang und Ende).

43. Kann ein Mitglied „mid“ als Standard verwenden, um die Tabelle zur einfachen Abfrage in Tabellen zu unterteilen? In allgemeinen Geschäftsanforderungen wird der Benutzername grundsätzlich als Abfragebasis verwendet. Normalerweise sollte der Benutzername als Hash-Modul zum Teilen von Tabellen verwendet werden.

Wenn es um die Aufteilung von Tabellen geht, erledigt dies die Partitionsfunktion von MySQL und ist für den Code transparent; es erscheint unangemessen, sie auf Codeebene zu implementieren.

44. Wir sollten eine ID als Primärschlüssel für jede Tabelle in der Datenbank festlegen, am besten einen INT-Typ (UNSIGNED wird empfohlen) und das automatisch erhöhte Flag AUTO_INCREMENT setzen.

45. Setzen Sie SET NOCOUNT ON am Anfang aller gespeicherten Prozeduren und Trigger und setzen Sie SET NOCOUNT OFF am Ende. Es ist nicht erforderlich, nach jeder Anweisung gespeicherter Prozeduren und Trigger eine DONE_IN_PROC-Nachricht an den Client zu senden.

46. MySQL-Abfrage kann einen Hochgeschwindigkeits-Abfrage-Cache ermöglichen. Dies ist eine der effektivsten MySQL-Optimierungsmethoden zur Verbesserung der Datenbankleistung. Wenn dieselbe Abfrage mehrmals ausgeführt wird, ist es viel schneller, die Daten aus dem Cache abzurufen und direkt aus der Datenbank zurückzugeben.

47. Die EXPLAIN SELECT-Abfrage wird verwendet, um den Anzeigeeffekt zu verfolgen:

Mit dem EXPLAIN-Schlüsselwort können Sie erfahren, wie MySQL Ihre SQL-Anweisung verarbeitet. Dies kann Ihnen bei der Analyse der Leistungsengpässe Ihrer Abfrageanweisungen oder Tabellenstrukturen helfen. Die Ergebnisse der EXPLAIN-Abfrage zeigen Ihnen auch, wie Ihre Indexprimärschlüssel verwendet werden und wie Ihre Datentabellen durchsucht und sortiert werden.

48. Verwenden Sie LIMIT 1, wenn nur eine Datenzeile vorhanden ist:

Manchmal wissen Sie beim Abfragen einer Tabelle bereits, dass das Ergebnis nur ein Ergebnis sein wird, aber möglicherweise müssen Sie den Cursor abrufen, oder Sie können es tun Überprüfen Sie die zurückgegebene Anzahl der Datensätze.

In diesem Fall kann das Hinzufügen von LIMIT 1 die Leistung steigern. Auf diese Weise stoppt die MySQL-Datenbank-Engine die Suche, nachdem sie ein Datenelement gefunden hat, anstatt weiter nach dem nächsten Datenelement zu suchen, das mit dem Datensatz übereinstimmt.

49. Wählen Sie die entsprechende Speicher-Engine für die Tabelle aus:

myisam: Die Anwendung konzentriert sich hauptsächlich auf Lese- und Einfügevorgänge mit nur wenigen Aktualisierungen und Löschungen und stellt keine hohen Anforderungen für Transaktionsintegrität und Parallelität Hoch. InnoDB: Transaktionsverarbeitung und Datenkonsistenz unter gleichzeitigen Bedingungen erforderlich. Neben Einfügungen und Abfragen umfasst es auch viele Aktualisierungen und Löschungen. (InnoDB reduziert effektiv Sperren, die durch Löschungen und Aktualisierungen verursacht werden). Bei Tabellen vom Typ InnoDB, die Transaktionen unterstützen, besteht der Hauptgrund für die Geschwindigkeit darin, dass die Standardeinstellung von AUTOCOMMIT aktiviert ist und das Programm BEGIN nicht explizit aufruft, um die Transaktion zu starten, was dazu führt, dass jede Einfügung automatisch übermittelt wird, was ernsthafte Auswirkungen auf die Geschwindigkeit hat Geschwindigkeit. Sie können begin vor der Ausführung von SQL aufrufen (auch wenn Autocommit aktiviert ist), was die Leistung erheblich verbessert. myisam：应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。InnoDB：事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。（InnoDB 有效地降低删除和更新导致的锁定）。对于支持事务的 InnoDB类 型的表来说，影响速度的主要原因是 AUTOCOMMIT 默认设置是打开的，而且程序没有显式调用 BEGIN 开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行 SQL 前调用 begin，多条 SQL 形成一个事物（即使 autocommit 打开也可以），将大大提高性能。

50、优化表的数据类型，选择合适的数据类型：

原则

50. Optimieren Sie den Datentyp der Tabelle und wählen Sie den entsprechenden Datentyp:

Prinzip: kleiner ist normalerweise besser, einfach ist besser, alle Felder müssen Standardwerte haben und versuchen, dies zu vermeiden NULL.

Zum Beispiel: Verwenden Sie beim Entwerfen von Datenbanktabellen so viele kleinere Ganzzahltypen wie möglich, um weniger Speicherplatz zu beanspruchen. (mediumint ist besser geeignet als int)

Zum Beispiel Zeitfelder: datetime und timestamp. Datum/Uhrzeit belegt 8 Bytes, Zeitstempel belegt 4 Bytes, nur die Hälfte wird verwendet. Der durch den Zeitstempel dargestellte Bereich liegt zwischen 1970 und 2037 und eignet sich für die Aktualisierungszeit.

MySQL kann den Zugriff auf große Datenmengen durchaus unterstützen, aber im Allgemeinen gilt: Je kleiner die Tabelle in der Datenbank, desto schneller werden die darauf ausgeführten Abfragen.

Um eine bessere Leistung zu erzielen, können wir daher beim Erstellen einer Tabelle die Breite der Felder in der Tabelle so klein wie möglich einstellen.

Zum Beispiel: Wenn das Feld „Postleitzahl“ definiert wird und es auf CHAR(255) eingestellt ist, wird der Datenbank offensichtlich unnötiger Speicherplatz hinzugefügt. Sogar die Verwendung des Typs VARCHAR ist überflüssig, da CHAR(6) die Aufgabe problemlos erfüllt. 🎜🎜Ebenso sollten wir, wenn möglich, MEDIUMINT anstelle von BIGIN verwenden, um Ganzzahlfelder zu definieren, und versuchen, das Feld auf NOT NULL zu setzen, damit die Datenbank bei zukünftigen Abfragen keine NULL-Werte vergleichen muss. 🎜

Für einige Textfelder, wie zum Beispiel „Provinz“ oder „Geschlecht“, können wir sie als ENUM-Typ definieren. Denn in MySQL wird der ENUM-Typ als numerische Daten behandelt und numerische Daten werden viel schneller verarbeitet als Texttypen. Auf diese Weise können wir die Leistung der Datenbank verbessern.

51. String-Datentyp: char, varchar, text Wählen Sie den Unterschied.

52. Jede Operation an der Spalte führt zu einem Tabellenscan, der Datenbankfunktionen, Berechnungsausdrücke usw. umfasst. Bei der Abfrage sollte die Operation so weit wie möglich auf die rechte Seite des Gleichheitszeichens verschoben werden.

「Zusammenfassung」

In diesem Artikel werden insgesamt 52 SQL-Optimierungsstrategien beschrieben. Wenn Sie mehr als 10 nennen können, beweist dies, dass das Interview immer noch interessant ist Wenn Sie weiter reden, sind Sie zu diesem Zeitpunkt bereits sehr beeindruckt.

Das obige ist der detaillierte Inhalt vonInterviewer: Kennen Sie sich mit SQL-Optimierung aus? Ich kenne nur 20 Arten, aber es gibt weit mehr .... Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!