プログラマは MySQL 使用仕様マニュアルを知っておく必要があります

mysql チュートリアル コラムの紹介MySQL 使用仕様マニュアル

その他の関連する無料学習の推奨事項: mysql チュートリアル (ビデオ)

時間が経ち、プラットフォームやシステムのユーザー数が増加するにつれて、データベースの操作が遅くなる傾向があります。Java アプリケーション開発では、データベースが特に重要です。多くの場合、データベースの性能がプログラムの性能を決定しますが、初期の段階で穴を埋めてしまうと、後の段階でデータベースがシステム全体のボトルネックになってしまうため、MySQL の利用をより標準化する必要があります。開発には欠かせないもの。

1. MySQL データベースの命名規則

1. データベース内のすべてのテーブルのプレフィックスはプロジェクト名の頭字語を使用します;

2. データベース内のすべてのオブジェクト名は小文字を使用します文字、単語はアンダースコアで区切られます;

3. MySQL の予約語とキーワードは、すべてのデータベース オブジェクト名で禁止されています。キーワードを含む SQL クエリでは、キーワードを一重引用符で囲む必要があります。

4データベース内のすべてのオブジェクトの名前は 32 文字を超えてはならず、名前付けは名前がわかるという原則に従う必要があります;

5. データベース一時テーブルには、接頭辞として pro_tmp_ を付け、接尾辞として日付 ## を付ける必要があります。 #20190917、バックアップ テーブルには pro_bac という接頭辞と末尾にタイムスタンプを付ける必要があります; (pro はプロジェクト名の頭字語です)

6. 同じものを格納するデータベース内のすべての列名と列の種類データは一貫している必要があります。

2. MySQL データベースの基本設計仕様

1. 特に指定がない限り、テーブルの作成時には Innodb ストレージ エンジンが使用されます。

適切なエンジンを選択すると、InnoDB や MyISAM などのデータベースのパフォーマンスが向上します。InnoDB と MyISAM は、多くの人が MySQL を使用するときに最も一般的に使用される 2 つのテーブル タイプです。どちらのテーブル タイプにも、要件に応じて独自の長所と短所があります。アプリケーションによって異なります;

基本的な違いは次のとおりです: MyISAM タイプはトランザクション処理などの高度な処理をサポートしませんが、InnoDB タイプはサポートします; MyISAM タイプのテーブルはパフォーマンスとその実行時間を重視しますInnoDB タイプよりも高速ですが、トランザクション サポートは提供しませんが、InnoDB はトランザクション サポートと外部キーなどの高度なデータベース機能を提供します;

したがって、トランザクション処理のサポート、外部キーのサポート、クラッシュ修復機能と同時実行制御は、テーブル エンジンを構築するときに推奨されるストレージです。

2. データベースとテーブルの文字セットは一律 UTF8 を使用します

データベースとテーブルの文字セットは一律 utf8 を使用します絵文字表現などを格納する必要があるフィールドがある場合は、テーブルまたはフィールドは utf8mb4 になります。utf8 は、トランスコーディングを必要とせず、文字化けの危険がなく、スペースを節約できるユニバーサル コードとして知られており、utf8mb4 は utf8 と下位互換性があるためです。

3. データベースを設計するときは、すべてのテーブルとフィールドにコメントを追加する必要があります。

Comment 句を使用してテーブルと列に関するコメントを追加するか、コメント列に直接コメントを追加します。プロジェクトの最初からデータベース接続ツールを使用し、データ ディクショナリのメンテナンスを実行します。

Comment 句を使用して次のようなコメントを追加します:

-- 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. データ サイズ単一テーブル 500 万以内に制御します

単一テーブル内のデータ サイズを制御するようにしてください。500 万以内に制御することをお勧めします。500 万は MySQL データベースの制限ではありませんが、500 万以内に制御することをお勧めします。多くのデータはテーブル構造の変更やバックアップ、データの復元に適さないため、サブデータベースやサブテーブルなどの方法を適切に使用して 1 つのテーブル内のデータのサイズを制御します。

5. MySQL パーティション テーブルを使用する場合は注意してください

パーティション化とは、テーブルのデータを、月ごとなど、特定の方法で管理しやすい複数の小さなデータに分割することです。一部ではありますが、論理的には 1 つのテーブルです。パーティション テーブルは物理的には複数のファイルとして見えますが、論理的には同じテーブルとして見えます。パーティション キーは慎重に選択する必要があります。パーティション間のクエリ効率が低下する可能性があります。物理パーティションを使用することをお勧めします。テーブルやその他の方法でビッグ データを管理します。

6. ホット データとコールド データを分離し、テーブルの幅を縮小してみてください。

MySQL では、各テーブルに最大 4096 列を格納できるように制限されており、データの各行のサイズはこの上限を超えません。 65535 バイト。テーブルの幅が広いほど、メモリ バッファ プールにテーブルをロードするときに占有されるメモリが大きくなり、より多くの IO スレッドが使用されるため、ディスク IO スレッドのオーバーヘッドを減らすには、テーブルの幅を適切に制御する必要があります。さらに、ホット データのメモリ キャッシュ ヒット率を確保するには、キャッシュをより効果的に使用して無駄なコールド データの読み取りを回避し、頻繁に使用される列を同じテーブルに配置して不必要な相関操作を避けるようにしてください。

7. 予約フィールドを設定するときは注意してください

友人の中には、データベース テーブルを設計するとき、現在必要なフィールドを設計するだけでなく、バ​​ックアップとしていくつかのフィールドを確保しておきます。たとえば、名前 (Name)、性別 (Sex)、生年月日 (birthday) などを含むさまざまな必要なフィールドを追加した個人テーブル (person) を設計しました。

念のため、たとえば、Person テーブルには大学院、勤務単位、結婚状況、写真などが含まれる可能性があるため、Text1、Text2...Text5 という 5 つの varchar2 フィールドが追加されます。この操作は次のようになります。これは予防措置ですが、実際には必要ありません。多数の予約フィールドはスペースを無駄にし、予約フィールドは名前で認識できず、格納されているデータ型を確認できず、フィールド型を変更するのが難しいためです。テーブルロックなどの問題が発生する可能性があります。

この状況では、次の 2 つの解決策を参照できます。

1. 量が少なく、情報の性質が元のテーブルと密接に関連している場合は、その後、それを元のテーブルに直接追加できます。テーブルにフィールドを追加し、関連するデータを更新します。

2. #数値が大きい場合、または元のテーブルの重要な属性ではない場合table オブジェクトを作成し、新しいテーブルを追加し、キー値を介してテーブルを接続します。

8. 画像やファイルなどの大きなバイナリ データをデータベースに保存することは禁止されています

ファイルをデータベース テーブルに保存すると、ファイルは通常非常に大きくなります。データベースが読み取り操作を実行すると、大量のランダム IO 操作が実行されます。ファイルが大きいと、IO 操作に時間とパフォーマンスがかかります。 -消費量が多く、データ量が短期間で急速に増大するため、通常、写真やファイルはファイル サーバーに保存され、データベースはファイル アドレス情報を保存するためにのみ使用されます。

3. MySQL データベース フィールドの設計仕様

1. ストレージのニーズを満たす最小のデータ型を優先します。

主にインデックスのパフォーマンスを考慮します。列のフィールドが大きくなるほど、インデックスの構築に必要な領域が大きくなり、ページに格納できるインデックス ノードの数が少なくなります。トラバース時 必要な IO 時間が増えるほど、インデックスのパフォーマンスは低下します。

2. TEXT および BLOB データ型の使用を避ける

TEXT および BLOB データ型の使用を避ける。最も一般的な TEXT 型は 64K データを保存できます。MySQL メモリ一時テーブルは TEXT および BLOB をサポートしません。ビッグ データ タイプ。クエリにそのようなデータが含まれる場合、並べ替えなどの操作を実行するときにメモリ一時テーブルを使用できません。操作を実行するには、ディスク一時テーブルを使用する必要があります。

TEXT および BLOB タイプはプレフィックス インデックス (インデックスが非常に長い文字列である場合、インデックスは多くのメモリを消費し、非常に遅くなります。このとき、プレフィックス インデックスが使用されます。いわゆるプレフィックス インデックスは、文字列の最初の数文字を使用します。インデックスはインデックスとして機能しますが、インデックスの繰り返し率を下げる必要があるため、プレフィックス インデックスの繰り返し率も判断する必要があります;)。MySQL のインデックス フィールドの長さは制限されているため、TEXT 型は接頭辞インデックスを使用し、TEXT 列にデフォルト値を設定することはできません。

これを使用する必要がある場合は、BLOB 列または TEXT 列を別の拡張テーブルに分割し、## を使用しないことをお勧めします。 #select * クエリを実行するときは、必要な列を取り出すだけです。

3. ENUM 列挙型の使用を避ける

ENUM 値を変更するには、ALTER ステートメントを使用する必要があります。

ENUM 型の ORDER BY 操作は非効率です。

禁止 ENUM の列挙値として数値を使用します。

4. すべての列のデフォルト値は NOT NULL として定義されています。

データベース内のすべての NULL 列には追加の保存領域が必要であるため、より多くの領域を占有します。

データベースは、比較や計算を実行するときに特に NULL 値を処理する必要があります。

5. TIMESTAMP (4 バイト) または DATETIME (8 バイト) タイプを使用して時間を保存します

TIMESTAMP 保存される時間の範囲は次のとおりです: 1970-01-01 00:00:01 ~ 2038 - 01-19-03:14:07;

TIMESTAMP は 4 バイトを占有し、INT と同じですが、INT 型よりも読みやすくなっています。TIMESTAMP 値の範囲を超える場合は、DATETIME 型ストレージを使用してください;

文字列型を使用して時刻を保存するデメリット: 日付関数は比較計算に使用できず、文字列の保存にはより多くのスペースが必要になります。

6. 財務関連の金額データは 10 進数タイプを使用する必要があります

正確な浮動小数点: 10 進数

非正確な浮動小数点: float、double

Decimal type は正確な浮動小数点数であり、計算中に精度が失われることはありません。占有されるスペースは、定義された幅によって決まります。4 バイトごとに 9 桁を格納でき、小数点も 1 バイトを占有します。さらに、Decimal 型は次のことを行うことができます。 bigint よりも大きなデータ型のストレージに使用されます。

4. MySQL インデックスの設計仕様

1. 各テーブルのインデックスの数は 5 を超えてはなりません

インデックスはクエリの効率を向上させますが、挿入や挿入も削減します。効率によってはクエリの効率が低下する場合もあるため、多ければ多いほど良いとは限らず、数を制御する必要があります。

2. 各 Innodb テーブルには主キーが必要です。

Innodb はインデックス構成テーブルであり、データ ストレージの論理順序はインデックスの順序と同じです;

各テーブルには複数のインデックスを持つことができますが、テーブルの格納順序は 1 つだけです。Innodb は主キー インデックスの順序でテーブルを編成するため、頻繁に更新される列、UUID、MD5、HASH、文字列列は使用しないでください。これらの列はデータの連続的な増加を保証できないため、主キーには自動インクリメントされる ID 値を使用することをお勧めします。

3. 外部キー制約の使用は避けるようにしてください。

外部キー制約の使用は推奨されませんが、テーブル間の関連キーにインデックスを作成する必要があります。

外部キーはデータの参照整合性を保証できますが、外部キーは親テーブルと子テーブルの書き込み操作にも影響を与えるため、パフォーマンスが低下し、テーブルの結合が強化されるため、ビジネス側で実装することをお勧めします。

五、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 af1c1993ef39498a90c973a43161c636 查询

因为使用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 最初に元のテーブルと同じ構造を持つ新しいテーブルを作成し、新しいテーブルのテーブル構造を変更してから、元のテーブルのデータを新しいテーブルにコピーします。 . を作成し、元のテーブルにいくつかのトリガーを追加します。次に、元のテーブルの新しいデータを新しいテーブルにコピーします。行内のすべてのデータをコピーした後、新しいテーブルに元のテーブルという名前を付け、元のテーブルを削除します。元の DDL 操作を複数の小さなバッチに分割して実行します。

3. プログラムが使用するアカウントにスーパー権限を付与することは禁止されています。

接続の最大数に達した場合、スーパー権限を持つユーザーを実行して接続できるのは、スーパー権限のみです。問題の処理は DBA に任せてください。使用するアカウント。

4. プログラムがデータベース アカウントに接続するには、最小権限の原則に従います

プログラムで使用されるデータベース アカウントは 1 つのデータベースでのみ使用でき、原則として、プログラムで使用されるアカウントにはドロップ権限が付与されていません。

以上がプログラマは MySQL 使用仕様マニュアルを知っておく必要がありますの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。