Programmierer müssen das MySQL-Nutzungsspezifikationshandbuch kennen

MySQL-TutorialSpalte stellt das MySQL-Nutzungsspezifikationshandbuch vor

Weitere verwandte kostenlose Lernempfehlungen: MySQL-Tutorial(Video)

Mit der Zeit und der Anzahl der Benutzer steigt eine Plattform oder ein System Datenbankoperationen neigen dazu, langsamer zu werden; und Datenbanken sind in der Java-Anwendungsentwicklung besonders wichtig. In den meisten Fällen bestimmt die Leistung der Datenbank die Leistung des Programms des gesamten Systems in der späteren Phase, daher ist eine stärker standardisierte Verwendung von MySQL in der Entwicklung unabdingbar.

1. Namenskonvention für MySQL-Datenbanken

1. Alle Tabellenpräfixe in der Datenbank verwenden Kleinbuchstaben und Wörter werden durch Unterstriche getrennt . Die Verwendung von für MySQL reservierten Wörtern und Schlüsselwörtern in der Datenbank ist nicht zulässig.

4 Alle Objektnamen in der Datenbank dürfen nicht länger als 32 Zeichen sein des Namens.

5. Der temporären Datenbanktabelle muss das Datum

2. Grundlegende Designspezifikationen für die MySQL-Datenbank

-- 1、创建表：
CREATE TABLE t1(id varchar2(32) primary key,name VARCHAR2(8) NOT NULL,age number);
-- 2、添加表注释：
Comment on table t1 is '个人信息';
-- 3、添加字段注释：
comment on column t1.id is 'id';
comment on column t1.nameis '姓名';
comment on column t1.age is '年龄';

4. Die Datengröße einer einzelnen Tabelle sollte innerhalb von 5 Millionen kontrolliert werden.

Versuchen Sie, die zu kontrollieren Die Größe der Daten in einer einzelnen Tabelle wird empfohlen. 5 Millionen sind nicht die Grenze der MySQL-Datenbank, aber zu viele Daten sind nicht förderlich für eine Änderung der Tabellenstruktur, Sicherung und Wiederherstellung Es ist sinnvoll, Methoden wie Unterdatenbank und Untertabelle zu verwenden, um die Größe der Daten in einer einzelnen Tabelle zu steuern.

5. Seien Sie vorsichtig, wenn Sie MySQL-partitionierte Tabellen verwenden. Bei der Partitionierung werden die Daten einer Tabelle auf eine bestimmte Weise in mehrere kleinere und einfacher zu verwaltende Teile aufgeteilt, z. B. nach dem Monat, aber das ist immer noch der Fall Logisch gesehen erscheint eine partitionierte Tabelle physisch als mehrere Dateien, erscheint jedoch logisch immer noch als dieselbe Tabelle. Die Effizienz der partitionsübergreifenden Abfrage muss möglicherweise geringer sein. Es wird daher empfohlen, physische Partitionstabellen zu verwenden und andere Methoden zur Verwaltung großer Datenmengen.

6. Versuchen Sie, heiße und kalte Daten zu trennen und die Breite der Tabellen zu reduzieren.

Für den Fall, dass die Personentabelle in Zukunft Informationen wie Schulabschluss, Arbeitsplatz, Heiratsstatus, Fotos usw. enthalten könnte, habe ich 5 Varchar2-Felder mit den Namen Text1, Text2 ... Text5 hinzugefügt Schritt Der Vorgang scheint eine Vorsichtsmaßnahme zu sein, ist aber tatsächlich nicht unbedingt so, da eine große Anzahl reservierter Felder Platz verschwendet, die reservierten Felder nicht namentlich bekannt sein können und der gespeicherte Datentyp der reservierten Felder nicht bestätigt werden kann , und das Ändern seines Feldtyps kann ebenfalls Probleme wie Tabellensperren verursachen.

Für diese Situation können Sie auf die folgenden beiden Lösungen zurückgreifen:

1. Wenn die Menge gering ist und die Art der Informationen eng mit der Originaltabelle verknüpft ist, können Sie Felder direkt zur Originaltabelle hinzufügen und aktualisieren die relevanten Daten. ;

2. Wenn die Zahl groß ist oder kein entscheidendes Attribut des ursprünglichen Tabellenobjekts ist, können Sie eine neue Tabelle hinzufügen und diese über Schlüsselwerte verbinden Es ist verboten, Bilder und Dateien in der Datenbank zu speichern. Wenn Sie Dateien mit großen Binärdaten in einer Datenbanktabelle speichern und die Dateien normalerweise sehr groß sind, wird eine große Anzahl zufälliger E/A-Vorgänge ausgeführt, wenn die Datenbank einen Lesevorgang ausführt Dateien machen E/A-Vorgänge sehr zeit- und leistungsintensiv, was dazu führt, dass die Datenmenge mit der Zeit schnell zunimmt. Daher werden Bilder und Dateien normalerweise auf Dateiservern gespeichert und die Datenbank wird nur zum Speichern von Dateiadressinformationen verwendet.

3. Spezifikationen für das MySQL-Datenbankfelddesign: Priorisieren Sie den kleinsten Datentyp, der den Speicheranforderungen entspricht.

Berücksichtigen Sie hauptsächlich die Leistung des Index, denn je größer das Feld der Spalte, desto größer ist der zum Erstellen des Index erforderliche Speicherplatz, sodass die Anzahl der Indexknoten, die auf einer Seite gespeichert werden können, kleiner ist und die Anzahl der Während der Durchquerung sind weniger E/As erforderlich. Je mehr E/As vorhanden sind, desto schlechter ist die Indexleistung.

2. Vermeiden Sie die Verwendung der Datentypen TEXT und BLOB. Der gängigste TEXT-Typ kann keine großen Datentypen wie TEXT und BLOB speichern Enthält solche Daten, können Sie beim Ausführen von Vorgängen wie Sortieren keine temporären Speichertabellen verwenden und müssen zum Ausführen von Vorgängen temporäre Tabellen auf der Festplatte verwenden.

TEXT- und BLOB-Typen können nur Präfixindizes verwenden (wenn der Index eine sehr lange Zeichenfolge ist). dieser Index Es wird viel Speicher beanspruchen und sehr langsam sein. Zu diesem Zeitpunkt wird der sogenannte Präfixindex verwendet, um die ersten paar Buchstaben des Index zu verwenden, aber die Duplizierung zu reduzieren Rate des Index, wir müssen auch die Wiederholungsrate des Präfixindex beurteilen;), da MySQL eine begrenzte Länge für Indexfelder hat, sodass der TEXT-Typ nur den Präfixindex verwenden kann und es keinen Standardwert für die TEXT-Spalte geben kann

Wenn Sie es verwenden müssen, wird empfohlen, die BLOB- oder TEXT-Spalte in eine separate erweiterte Tabelle aufzuteilen und beim Abfragen nicht

select *Die Datenbank muss bei der Ausführung NULL-Werte verarbeiten Vergleiche und Berechnungen. Besondere Handhabung.

5. Verwenden Sie den Typ TIMESTAMP (4 Bytes) oder DATETIME (8 Bytes), um die Zeit zu speichern

TIMESTAMP Der gespeicherte Zeitbereich ist: 1970-01-01 00:00:01 ~ 2038-01-19-03:14: 07;

TIMESTAMP belegt die gleichen 4 Bytes wie INT, ist aber besser lesbar als der INT-Typ. Wenn er den TIMESTAMP-Wertebereich überschreitet, verwenden Sie den DATETIME-Typ zum Speichern der Zeit. Es kann nicht verwendet werden. Datumsfunktionen führen Vergleichsberechnungen durch und die Zeichenfolgenspeicherung nimmt mehr Platz in Anspruch.

6. Finanzbezogene Betragsdaten müssen den Dezimaltyp verwenden. Präzise Gleitkommazahl: Dezimal. Ungenaue Gleitkommazahl: Gleitkommazahl, doppelt. Es nimmt Platz ein. Die Größe wird durch die definierte Breite bestimmt. Alle 4 Bytes können 9 Ziffern speichern, und der Dezimalpunkt belegt außerdem ein Byte. Darüber hinaus kann der Typ Decimal zum Speichern von Datentypen verwendet werden, die größer als Bigint sind. 4. MySQL-Index-Design-Spezifikationen . , mehr ist also nicht besser, die Menge muss kontrolliert werden.

2. Jede Innodb-Tabelle muss einen Primärschlüssel haben.

Innodb ist eine indizierte Tabelle, und die logische Reihenfolge der Datenspeicherung ist dieselbe wie die Reihenfolge des Index Tabelle Es kann nur eine Speicherreihenfolge geben. Innodb organisiert Tabellen in der Reihenfolge der Primärschlüsselindizes. Verwenden Sie daher keine häufig aktualisierten Spalten, UUID-, MD5-, HASH- und Zeichenfolgenspalten als Primärschlüssel. Empfehlungen für Primärschlüssel Verwenden Sie automatisch inkrementierende ID-Werte.

3. Versuchen Sie, Fremdschlüsseleinschränkungen zu vermeiden.

Es wird nicht empfohlen, Fremdschlüsseleinschränkungen zu verwenden. Sie müssen jedoch einen Index für den zugehörigen Schlüssel zwischen Tabellen erstellen.

Obwohl Fremdschlüssel die vollständige Referenz der Daten gewährleisten können Leistung, aber Fremdschlüssel wirken sich auch auf die Schreibvorgänge der übergeordneten und untergeordneten Tabelle aus, wodurch die Leistung verringert wird und die Tabellen stärker gekoppelt werden. Es wird empfohlen, dies auf der Geschäftsseite zu implementieren.

五、MySQL数据库SQL开发规范

1、建议使用预编译语句进行数据库操作

预编译语句可以重复使用，相同的SQL语句可以一次解析，多次使用，减少SQL编译所需要的时间，提高处理效率；此外，还可以有效解决动态SQL带来的SQL注入问题。

2、避免数据类型的隐式转换

隐式转换如：SELECT 1 + "1"；数值型 + 字符型 的隐式转换有可能会导致索引失效，以及一些意想不到的结果等。

3、充分利用表中存在的索引

1）避免使用双%号的查询条件

如 WHERE first_name like '%James%'，若无前置%，只有后置%，则执行SQL语句时会用到列上的索引，双%号则不会使用列上的索引。

2）一条SQL语句只能使用复合索引中的一列进行范围查询

例如有weight、age、sex三列的联合索引，在查询条件中有weight列的范围查询，则在age和sex列上的索引将不会被使用；因此，在定义联合索引时，若某列需要用到范围查询，则将该列放到联合索引的右侧。

3）使用not exists 代替not in

因为not in 在SQL语句中执行时会导致索引失效。

4、杜绝使用SELECT * ，必须使用SELECT <字段列表> 查询

因为使用SELECT * 查询会消耗更多的CPU、IO和网络宽带资源，并且查询时无法使用覆盖索引。

5、禁止使用不含字段列表的INSERT 语句

如：INSERT into table_name values ('1','2','3'); 改为带字段列表的INSERT 语句：INSERT into table_name('c1','c2','c3') values ('1','2','3');

6、避免使用子查询，可以把子查询优化为join 关联操作

但是，通常子查询在in 子句中，且子查询中为简单SQL(即不包含union、group by、order by、limit从句)时，才可以把子查询转化为join关联查询进行优化；

子查询性能差的原因：

• 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；

• 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

7、避免使用JOIN 关联太多表

1）在Mysql中，对于同一个SQL关联（join）多个表，每个join 就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大；

2）如果程序中大量的使用了多表关联的操作，同时join_buffer_size(MySQL允许关联缓存的个数)设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响服务器数据库性能的稳定性；

3）此外，对于关联操作来说，会产生临时表影响查询效率，而Mysql最多允许关联61个表，建议不超过5个；

8、对同一列对象进行or 判断时，使用in 替代or

in 的值只要涉及不超过500个，则in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

9、禁止使用order by rand() 进行随机排序

10、禁止在WHERE 从句中对列进行函数转换和计算

因为在WHERE 从句中对列进行函数转换或计算时会导致索引无法使用。

No推荐：

where date(end_time)='20190101'

推荐：

where end_time >= &#39;20190101&#39; and end_time < &#39;20190102&#39;

11、在明显不会有重复值时使用UNION ALL 而不是UNION

1）UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作；

2）UNION ALL 不会再对结果集进行去重操作；

12、把复杂、较长的SQL 拆分为为多个小SQL 执行

1）大SQL在逻辑上比较复杂，是需要占用大量CPU 进行计算一条SQL语句；

2）在MySQL中，一条SQL 语句只能使用一个CPU 进行计算；

3）SQL拆分后可以通过并行执行来提高处理效率。

六、MySQL数据库行为规范

1、超过100万行数据的批量操作(update delete insert)，分多次进行

大批量操作可能回造成严重的主从延迟；

binlog日志为row格式时会产生大量的日志；

避免产生大事物操作。

2、对于大表使用pt-online-schema-change 修改表结构

1）避免大表修改产生的主从延迟、避免在对表字段进行修改时进行锁表；

2) pt-online-schema-change Es erstellt zunächst eine neue Tabelle mit derselben Struktur wie die Originaltabelle, ändert die Tabellenstruktur in der neuen Tabelle und kopiert dann die Daten in der Originaltabelle in die neue Tabelle Fügen Sie der Originaltabelle einige Trigger hinzu. Kopieren Sie dann die neuen Daten in die neue Tabelle. Benennen Sie die neue Tabelle als Originaltabelle und löschen Sie sie Der ursprüngliche DDL-Vorgang wird zur Ausführung in mehrere kleine Stapel aufgeteilt.

3. Es ist verboten, dem vom Programm verwendeten Konto Superberechtigungen zu erteilen.

Wenn die maximale Anzahl von Verbindungen erreicht ist, kann ein Benutzer mit Superberechtigungen nur noch für das verwendete Konto reserviert werden vom DBA beauftragt, das Problem zu beheben.

4. Damit das Programm eine Verbindung zum Datenbankkonto herstellen kann, befolgen Sie das Prinzip der geringsten Berechtigungen.

Das vom Programm verwendete Datenbankkonto kann nur unter einer Datenbank verwendet werden, und das vom Programm verwendete Konto gewährt grundsätzlich keine Berechtigungen Berechtigungen löschen.

Das obige ist der detaillierte Inhalt vonProgrammierer müssen das MySQL-Nutzungsspezifikationshandbuch kennen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!