Sammlung häufig verwendeter SQL Server-Spezifikationen
- 巴扎黑Original
- 2017-05-01 11:25:151154Durchsuche
Auswahl des allgemeinen Feldtyps
1. Es wird empfohlen, den Datentyp varchar/nvarchar für den Zeichentyp
zu verwenden
2. Es wird empfohlen, für die Betragswährung den Datentyp „Geld“ zu verwenden
3. Es wird empfohlen, für die wissenschaftliche Notation den numerischen Datentyp zu verwenden
4. Es wird empfohlen, den Bigint-Datentyp für sich selbst vergrößernde Logos zu verwenden (die Datenmenge ist groß und kann nicht mit dem Int-Typ geladen werden, und die Transformation wird dann in Zukunft problematisch sein)
5. Als Zeittyp wird der Datentyp datetime
empfohlen
6. Es ist verboten, die alten Datentypen text, ntext und image zu verwenden
7. Es ist verboten, den XML-Datentyp varchar(max), nvarchar(max)
zu verwenden Einschränkungen und Indizes
Jede Tabelle muss einen Primärschlüssel
haben Jede Tabelle muss einen Primärschlüssel haben, der zur Durchsetzung der Entitätsintegrität verwendet wird
Eine einzelne Tabelle kann nur einen Primärschlüssel haben (leere und doppelte Daten sind nicht zulässig)
Versuchen Sie, Einzelfeld-Primärschlüssel zu verwenden
Fremdschlüssel sind nicht erlaubt
Fremdschlüssel erhöhen die Komplexität von Tabellenstrukturänderungen und Datenmigration
Fremdschlüssel wirken sich auf die Leistung von Einfügungen und Aktualisierungen aus. Sie müssen die primären Fremdschlüsseleinschränkungen überprüfen
Die Datenintegrität wird vom Programm kontrolliert
NULL-Attribut
Für die neu hinzugefügte Tabelle ist NULL in allen Feldern verboten
(Warum erlaubt die neue Tabelle kein NULL?
Das Zulassen von NULL-Werten erhöht die Komplexität der Anwendung. Sie müssen spezifischen Logikcode hinzufügen, um verschiedene unerwartete Fehler zu verhindern
Bei der dreiwertigen Logik müssen alle Abfragen mit Gleichheitszeichen ("=") ein isnull-Urteil hinzufügen.
Null=Null, Null!=Null, not(Null=Null), not(Null!=Null) sind alle unbekannt, nicht wahr)
Lassen Sie uns dies anhand eines Beispiels veranschaulichen:
Wenn die Daten in der Tabelle wie in der Abbildung dargestellt sind:
Sie möchten alle Daten außer dem Namen finden, der aa entspricht, und verwenden dann versehentlich SELECT * FROM NULLTEST WHERE NAME<>’aa’
Das Ergebnis war anders als erwartet. Tatsächlich wurde nur der Datensatz mit name=bb gefunden, nicht jedoch der Datensatz mit name=NULL
Wie finden wir dann alle Daten außer dem Namen gleich aa? Wir können nur die ISNULL-Funktion
verwenden SELECT * FROM NULLTEST WHERE ISNULL(NAME,1)<>’aa’
Sie wissen jedoch möglicherweise nicht, dass ISNULL zu schwerwiegenden Leistungsengpässen führen kann. Daher ist es in vielen Fällen am besten, die Benutzereingaben auf Anwendungsebene zu begrenzen, um sicherzustellen, dass Benutzer vor der Abfrage gültige Daten eingeben.
Neue Felder, die der alten Tabelle hinzugefügt werden, müssen NULL sein dürfen (um Datenaktualisierungen in der gesamten Tabelle und Blockierungen durch langfristige Sperren zu vermeiden) (dies dient hauptsächlich der Berücksichtigung der Transformation der vorherigen Tabelle)
Richtlinien zur Indexgestaltung
Indizes sollten für Spalten erstellt werden, die häufig in WHERE-Klauseln
verwendet werden Indizes sollten für Spalten erstellt werden, die häufig zum Verknüpfen von Tabellen verwendet werden
Indizes sollten für Spalten erstellt werden, die häufig in ORDER BY-Klauseln
verwendet werden Indizes sollten nicht für kleine Tabellen (Tabellen, die nur wenige Seiten verwenden) erstellt werden, da ein vollständiger Tabellenscanvorgang möglicherweise schneller ist als eine Abfrage mithilfe eines Index
-
Die Anzahl der Indizes in einer einzelnen Tabelle überschreitet nicht 6
Erstellen Sie keine einspaltigen Indizes für Felder mit geringer Selektivität
Nutzen Sie die einzigartigen Einschränkungen voll aus
Der Index enthält nicht mehr als 5 Felder (einschließlich Include-Spalten)
Erstellen Sie keine einspaltigen Indizes für Felder mit geringer Selektivität
SQL SERVER stellt Anforderungen an die Selektivität von Indexfeldern. Wenn die Selektivität zu niedrig ist, gibt SQL SERVER die Verwendung von
- Felder, die nicht für die Indexerstellung geeignet sind: Geschlecht, 0/1, WAHR/FALSCH
- Für die Erstellung von Indizes geeignete Felder: ORDERID, UID usw.
- Indizes beschleunigen Abfragen, wirken sich jedoch auf die Schreibleistung aus
- Der Index einer Tabelle sollte umfassend erstellt werden, indem alle mit der Tabelle verbundenen SQL-Anweisungen kombiniert werden, und versuchen Sie,
- zusammenzuführen Das Prinzip des kombinierten Index besteht darin, dass die Felder mit besserer Filterbarkeit höher platziert werden
- Zu viele Indizes verlängern nicht nur die Kompilierungszeit, sondern beeinträchtigen auch die Fähigkeit der Datenbank, den besten Ausführungsplan auszuwählen
- Es ist verboten, komplexe Operationen in der Datenbank durchzuführen
SELECT *
ist verboten Verwenden Sie keine Funktionen oder Berechnungen für indizierte Spalten
Cursor sind verboten
Auslöser verboten
Es ist verboten, den Index
anzugeben Der Variable-/Parameter-/zugehörige Feldtyp muss mit dem Feldtyp
übereinstimmen Parametrisierte Abfrage
Begrenzen Sie die Anzahl der JOINs
Begrenzen Sie die Länge von SQL-Anweisungen und die Anzahl der IN-Klauseln
Vermeiden Sie große Transaktionsvorgänge
Durch Deaktivieren der betroffenen Zeilenanzahlinformationen wird
zurückgegeben Sofern nicht erforderlich, muss die SELECT-Anweisung mit NOLOCK
hinzugefügt werden Verwenden Sie UNION ALL, um UNION zu ersetzen
Fragen Sie große Datenmengen mithilfe von Paging oder TOP ab
Einschränkungen auf rekursiver Abfrageebene
NOT EXISTS ersetzt NOT IN
Temporäre Tabellen und Tabellenvariablen
Verwenden Sie lokale Variablen, um einen mittleren Ausführungsplan auszuwählen
Vermeiden Sie die Verwendung des ODER-Operators
Mechanismus zur Behandlung von Transaktionsausnahmen hinzufügen
Die Ausgabespalten verwenden das zweiteilige Namensformat
Es ist verboten, komplexe Operationen in der Datenbank durchzuführen
XML-Analyse
String-Ähnlichkeitsvergleich
String-Suche (Charindex)
Komplexe Vorgänge werden programmseitig erledigt
Es ist verboten, SELECT *
zu verwenden Reduzieren Sie den Speicherverbrauch und die Netzwerkbandbreite
Geben Sie dem Abfrageoptimierer die Möglichkeit, die erforderlichen Spalten aus dem Index zu lesen
Wenn sich die Tabellenstruktur ändert, kann es leicht zu Abfragefehlern kommen
Es ist verboten, Funktionen oder Berechnungen für Indexspalten zu verwenden
Es ist verboten, Funktionen oder Berechnungen für Indexspalten zu verwenden
Wenn in der where-Klausel der Index Teil der Funktion ist, verwendet der Optimierer den Index nicht mehr und führt einen vollständigen Tabellenscan durch
Unter der Annahme, dass ein Index für Feld Col1 vorhanden ist, wird der Index in den folgenden Szenarios nicht verwendet:
ABS[Col1]=1
[Col1]+1>9
Lassen Sie uns ein weiteres Beispiel geben
Eine Abfrage wie die obige kann den PrintTime-Index für die O_OrderProcess-Tabelle nicht verwenden, daher verwenden wir die folgende SQL-Abfrage
Es ist verboten, Funktionen oder Berechnungen für Indexspalten zu verwenden
Unter der Annahme, dass ein Index für Feld Col1 vorhanden ist, wird in den folgenden Szenarios der Index verwendet:
[Spalte1]=3,14
[Spalte1]>100
[Col1] ZWISCHEN 0 UND 99
[Col1] LIKE ‚abc%‘
[Col1] IN(2,3,5,7)
Indexproblem der LIKE-Abfrage
1.[Spalte1] wie „abc%“ – Indexsuche Dies verwendet eine Indexabfrage
2.[Spalte1] wie „%abc%“ – Index-Scan Und dies verwendet keine Indexabfrage
3.[Spalte1] wie „%abc“ – Index-Scan Hierbei wird auch keine Indexabfrage verwendet
Ich denke, aus den obigen drei Beispielen sollte jeder verstehen, dass es am besten ist, vor der LIKE-Bedingung keinen Fuzzy-Matching zu verwenden, da sonst die Indexabfrage nicht verwendet wird.
Die Verwendung von Cursorn ist verboten
Relationale Datenbanken eignen sich für Mengenoperationen, d. h. Mengenoperationen werden für die Ergebnismenge ausgeführt, die durch die WHERE-Klausel und die Auswahlspalte bestimmt wird. Der Cursor ist eine Möglichkeit, Nichtmengenoperationen bereitzustellen. Unter normalen Umständen entspricht die von einem Cursor implementierte Funktion häufig der Funktion, die von einer Schleife auf der Clientseite implementiert wird.
Der Cursor platziert die Ergebnismenge im Serverspeicher und verarbeitet die Datensätze einzeln in einer Schleife, was viele Datenbankressourcen verbraucht (insbesondere Speicher- und Sperrressourcen).
(Außerdem sind Cursor sehr kompliziert und schwierig zu verwenden, also verwenden Sie sie so wenig wie möglich)
Die Verwendung von Triggern ist verboten
Auslöser sind für die Anwendung undurchsichtig (die Anwendungsebene weiß weder, wann der Auslöser ausgelöst wird, noch weiß sie, wann er auftritt. Es fühlt sich unerklärlich an ...)
Es ist verboten, in der Abfrage
einen Index anzugeben With(index=XXX) (In Abfragen verwenden wir im Allgemeinen With(index=XXX), um den Index anzugeben)
Wenn sich die Daten ändern, ist die durch die Abfrageanweisung angegebene Indexleistung möglicherweise nicht optimal
Der Index sollte für die Anwendung transparent sein. Wenn der angegebene Index gelöscht wird, führt dies zu einem Abfragefehler, der der Fehlerbehebung nicht förderlich ist
Der neu erstellte Index kann nicht sofort von der Anwendung verwendet werden und muss veröffentlicht werden, um wirksam zu werden
Der Variable-/Parameter-/zugehörige Feldtyp muss mit dem Feldtyp konsistent sein (das ist etwas, worauf ich vorher nicht viel geachtet habe)
Vermeiden Sie den zusätzlichen CPU-Verbrauch der Typkonvertierung, der bei großen Tabellenscans besonders schwerwiegend ist
Nachdem ich mir die beiden Bilder oben angesehen habe, glaube ich nicht, dass ich es erklären muss, jeder sollte es bereits wissen.
Wenn der Datenbankfeldtyp VARCHAR ist, ist es am besten, den Typ in der Anwendung als AnsiString anzugeben und seine Länge eindeutig anzugeben
Wenn der Datenbankfeldtyp CHAR ist, ist es am besten, den Typ in der Anwendung als AnsiStringFixedLength anzugeben und seine Länge eindeutig anzugeben
Wenn der Datenbankfeldtyp NVARCHAR ist, ist es am besten, den Typ in der Anwendung als String anzugeben und seine Länge eindeutig anzugeben
Parametrisierte Abfrage
Abfrage-SQL kann auf folgende Weise parametrisiert werden:
sp_executesql
Vorbereitete Abfragen
Gespeicherte Prozeduren
Lass es mich mit einem Bild erklären, haha.
Begrenzen Sie die Anzahl der JOINs
Die Anzahl der Tabellen-JOINs in einer einzelnen SQL-Anweisung darf 5
nicht überschreiten Zu viele JOINs führen dazu, dass der Abfrageanalysator in den falschen Ausführungsplan wechselt
Zu viele JOINs verschlingen viel Geld bei der Erstellung des Ausführungsplans
Begrenzen Sie die Anzahl der Bedingungen in der IN-Klausel
Das Einschließen einer sehr großen Anzahl von Werten (Tausende) in die IN-Klausel kann Ressourcen verbrauchen und den Fehler 8623 oder 8632 zurückgeben. Die Anzahl der Bedingungen in der IN-Klausel muss auf 100 begrenzt werden
Versuchen Sie, große Transaktionsvorgänge zu vermeiden
Starten Sie Transaktionen nur, wenn Daten aktualisiert werden müssen, wodurch die Haltezeit der Ressourcensperre verkürzt wird
Vorverarbeitungsmechanismus zur Erfassung von Transaktionsausnahmen hinzufügen
Die Verwendung verteilter Transaktionen in der Datenbank ist verboten
Verwenden Sie Bilder zur Erklärung
Mit anderen Worten, wir sollten Tran nicht festschreiben, nachdem alle 1.000 Datenzeilen aktualisiert wurden. Überlegen Sie, ob Sie beim Aktualisieren dieser 1.000 Datenzeilen Ressourcen monopolisieren, was dazu führt, dass andere Transaktionen nicht verarbeitet werden können.
Schließen Sie die Informationen zur Anzahl der betroffenen Zeilen und geben Sie
zurück Zeigen Sie Set Nocount On in der SQL-Anweisung an, brechen Sie die Rückgabe der betroffenen Zeilenanzahlinformationen ab und reduzieren Sie den Netzwerkverkehr
Sofern nicht erforderlich, muss die SELECT-Anweisung mit NOLOCK
hinzugefügt werden Sofern nicht erforderlich, versuchen Sie, alle ausgewählten Anweisungen mit NOLOCK
zu versehen Gibt an, dass Dirty Reads zulässig sind. Es werden keine gemeinsamen Sperren ausgegeben, um zu verhindern, dass andere Transaktionen die von der aktuellen Transaktion gelesenen Daten ändern, und von anderen Transaktionen festgelegte exklusive Sperren verhindern nicht, dass die aktuelle Transaktion die gesperrten Daten liest. Das Zulassen von Dirty Reads kann zu mehr gleichzeitigen Vorgängen führen, der Preis hierfür sind jedoch Datenänderungen, die später durch andere Transaktionen rückgängig gemacht werden. Dies kann dazu führen, dass bei Ihrer Transaktion ein Fehler auftritt, die Benutzerdaten angezeigt werden, die nie festgeschrieben wurden, oder dass der Benutzer den Datensatz zweimal sieht (oder den Datensatz überhaupt nicht sieht)
Verwenden Sie UNION ALL, um UNION zu ersetzen
Verwenden Sie UNION ALL, um UNION zu ersetzen
UNION wird die SQL-Ergebnismenge neu ordnen und den Verbrauch von CPU, Speicher usw. erhöhen.
Um große Datenmengen abzufragen, verwenden Sie Paging oder TOP
Begrenzen Sie die Anzahl der Datensatzrückgaben angemessen, um Engpässe bei E/A und Netzwerkbandbreite zu vermeiden
Einschränkungen auf rekursiver Abfrageebene
Verwenden Sie MAXRECURSION, um zu verhindern, dass unangemessener rekursiver CTE in eine Endlosschleife gerät
Temporäre Tabellen und Tabellenvariablen
Verwenden Sie lokale Variablen, um einen durchschnittlichen Ausführungsplan auszuwählen
In einer gespeicherten Prozedur oder Abfrage führt der Zugriff auf eine Tabelle mit sehr ungleichmäßiger Datenverteilung häufig dazu, dass die gespeicherte Prozedur oder Abfrage einen suboptimalen oder sogar schlechten Ausführungsplan verwendet, was zu Problemen wie hoher CPU-Auslastung und einer großen Anzahl von E/A-Lesevorgängen führt Verhindern Sie falsche Ausführungspläne.
Bei Verwendung lokaler Variablen kennt SQL beim Kompilieren den Wert dieser lokalen Variablen nicht. Zu diesem Zeitpunkt „errät“ SQL einen Rückgabewert basierend auf der allgemeinen Datenverteilung in der Tabelle. Unabhängig davon, welche Variablenwerte der Benutzer beim Aufruf der gespeicherten Prozedur oder Anweisung ersetzt, ist der generierte Plan derselbe. Ein solcher Plan ist im Allgemeinen moderater und möglicherweise nicht der beste Plan, aber im Allgemeinen auch nicht der schlechteste Plan
Wenn die lokale Variable in der Abfrage den Ungleichheitsoperator verwendet, verwendet der Abfrageanalysator eine einfache 30-%-Berechnung, um
zu schätzen
Geschätzte Zeilen =(Gesamtzeilen * 30)/100
Wenn die lokale Variable in der Abfrage den Gleichheitsoperator verwendet, verwendet der Abfrageanalysator: Präzision * Gesamtzahl der zu schätzenden Tabellendatensätze
Geschätzte Zeilen = Dichte * Gesamtzeilen
Vermeiden Sie die Verwendung des ODER-Operators
Für den OR-Operator wird normalerweise ein vollständiger Tabellenscan verwendet. Erwägen Sie die Aufteilung in mehrere Abfragen und die Implementierung von UNION/UNION ALL. Hier müssen Sie bestätigen, dass die Abfrage zum Index gehen und eine kleinere Ergebnismenge zurückgeben kann > Mechanismus zur Behandlung von Transaktionsausnahmen hinzufügen
Die Anwendung sollte Unfälle gut bewältigen und rechtzeitig ein Rollback durchführen.
Verbindungseigenschaften festlegen „set xact_abort on“
Die Ausgabespalte verwendet das zweiteilige Namensformat
Zweistufiges Benennungsformat: Tabellenname.Feldname
In TSQL mit einer JOIN-Beziehung muss das Feld angeben, zu welcher Tabelle das Feld gehört. Andernfalls kann es nach einer zukünftigen Änderung der Tabellenstruktur zu Programmkompatibilitätsfehlern mit mehrdeutigen Spaltennamen kommen
Architekturdesign
-
Lese- und Schreibtrennung
- Trennung von Lesen und Schreiben
-
Die Trennung von Lesen und Schreiben wird von Beginn des Entwurfs an berücksichtigt. Auch wenn dieselbe Bibliothek gelesen und geschrieben wird, fördert sie eine schnelle Erweiterung
Bei der Trennung von Lesen und Schreiben sollte das automatische Umschalten auf die Schreibseite berücksichtigt werden, wenn das Lesen nicht möglich ist- Schema-Entkopplung
Datenbankübergreifender JOIN ist verboten
Datenlebenszyklus
Je nach Häufigkeit der Datennutzung werden große Tabellen in regulären Unterdatenbanken archiviert
Physische Trennung Hauptbibliothek/Archivbibliothek
Protokolltyptabellen sollten partitioniert oder Untertabellen sein
Bei großen Tabellen ist eine Partitionierung erforderlich, bei der die Tabelle und der Index in mehrere Partitionen unterteilt werden. Dies kann alte und neue Partitionen schnell ersetzen, die Datenbereinigung beschleunigen und den E/A-Ressourcenverbrauch erheblich reduzieren Häufig geschriebene Tabellen erfordern Partitionierung oder Untertabellen
Selbstwachstum und Verriegelung
Der Latch wird intern vom SQL Server angewendet und gesteuert. Er wird verwendet, um die Konsistenz der Datenstruktur im Speicher sicherzustellen
Das obige ist der detaillierte Inhalt vonSammlung häufig verwendeter SQL Server-Spezifikationen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!