MySQL データベースの設計仕様を要約して整理する

この記事では、mysql に関する関連知識を提供します。主にデータベースの設計仕様に関連する問題を紹介します。MySQL データベースを使用するときは、特定の仕様に従い、長所を活かし、短所を避ける必要があります。 RD、QA、OP、その他の技術担当者がオンライン ビジネスに適したデータベース設計を作成できるよう支援または指導することができます。

推奨学習: mysql ビデオ チュートリアル

仕様の背景と目的

MySQL データベースと Oracle、SQL Server他のデータベースと比較すると、独自の主要な利点と欠点があります。 MySQL データベースを使用する場合は、特定の基準に従い、長所を最大限に高め、短所を回避する必要があります。この仕様は、RD、QA、OP、およびその他の技術担当者がオンライン ビジネスに適したデータベース設計を行うのを支援またはガイドすることを目的としています。データベースの変更や処理手順、データベースのテーブル設計、SQLの記述などを標準化し、企業の業務システムの安定的かつ健全な運用を確保します。

データベース設計

以下のすべての仕様は、[高リスク]、[必須]、[推奨]の 3 つのレベルに従ってマークされ、準拠の優先度は高から低になります。

[高リスク] と [必須] の 2 つのレベルを満たさないデザインの場合、DBA はデザインを強制的に差し戻し、変更を要求します。

一般的な命名規則

• [必須] 入力速度を向上させ、大文字と小文字の区別によるエラーを回避するには、小文字を使用します。

• [必須] スペースは使用できません。代わりにアンダースコアを使用してください。

• [必須] 名前には数字は含まれず、英字のみが使用されます。

• [必須] 有効でわかりやすい名前。

• [必須] 名前は一目瞭然である必要があります。

• [必須] 名前は 32 文字以内にしてください。

• [必須] プレフィックスの使用は避けてください。

ライブラリ

• [必須] 上記の一般的な命名規則をすべてに従ってください。

• [必須] 単数を使用します。

• [必須] ライブラリ名の形式: 業務システム名_サブシステム名。

• [必須] 通常、サブライブラリ名の命名形式は、ライブラリ ワイルドカード名_番号で、northwind_001 のように 0 から番号が増加します。時間に基づくライブラリのワイルドカードです。

• [必須] データベースの作成時に文字セットを明示的に指定する必要があります。文字セットは utf8 または utf8mb4 のみです。データベース SQL の作成例:

create database db_name default character set utf8;

Table

• [必須] 上記の一般的な命名規則をすべて遵守します。

• [必須] 単数を使用します。

• 【必須】userテーブルやuser_loginテーブルなど、関連モジュールのテーブル名間の結合関係を反映するようにしてください。

• [必須] テーブル作成時に文字セットを utf8 または utf8mb4 として明示的に指定する必要があります。

• [必須] テーブルの作成時にテーブル ストレージ エンジンの種類を明示的に指定する必要があります。特別な要件がない場合、常に InnoDB になります。 InnoDB/MyISAM/Memory 以外のストレージ エンジンを使用する必要がある場合は、運用環境で使用する前に DBA のレビューに合格する必要があります。 InnoDB テーブルは、トランザクション、行ロック、ダウンタイム回復、MVCC などのリレーショナル データベースの重要な機能をサポートしているため、業界で最も使用されている MySQL ストレージ エンジンです。これは他のほとんどのストレージ エンジンにはない機能なので、最初に InnoDB を推奨します。

• 【必須】テーブル作成時にコメントを記載する必要があります。

• [必須] 主キーについて: (1) id という名前を付け、int または bigint と入力し、auto_increment を入力します。(2) 各行の主題を識別するフィールドを設定しないでください。テーブル内で主キーとして使用する場合は、user_id、order_id などの他のフィールドに設定し、一意のキー インデックスを作成することをお勧めします。これが主キーとして設定され、主キーの値がランダムに挿入されると、InnoDB 内部のページ分割と大量のランダム I/O が発生し、パフォーマンスが低下するためです。

• [推奨事項] コア テーブル (ユーザー テーブル、お金関連テーブルなど) には、トラブルシューティングを容易にするために、行データの作成時刻フィールド create_time と最終更新時刻フィールド update_time が必要です。

• [推奨事項] テーブル内のすべてのフィールドは NOT NULL 属性を持つ必要があり、企業は必要に応じて DEFAULT 値を定義できます。 NULL 値を使用すると、各行が追加の記憶領域を占有するなどの問題が発生するため、データ移行でエラーが発生しやすくなり、集計関数の計算結果に偏りが生じます。

• [推奨事項] テーブル内の BLOB やテキストなどの大きなフィールドを他のテーブルに垂直に分割し、これらのオブジェクトを読み取る必要がある場合にのみ選択することをお勧めします。

• [提案] アンチパラダイム設計: 結合クエリを頻繁に必要とするフィールドを他のテーブルに冗長的に追加します。たとえば、username 属性は user_account、user_login_log、およびその他のテーブルで冗長であるため、結合クエリが減少します。

• [必須] 中間テーブルは中間結果セットを保持するために使用され、名前は tmp_ で始まる必要があります。バックアップ テーブルは、ソース テーブルのバックアップまたはスナップショットのキャプチャに使用され、名前は bak_ で始まる必要があります。中間テーブルとバックアップテーブルは定期的にクリーンアップされます。

• 【强制】对于超过 100W 行的大表进行 alter table，必须经过 DBA 审核，并在业务低峰期执行。因为 alter table 会产生表锁，期间阻塞对于该表的所有写入，对于业务可能会产生极大影响。

字段

• 【强制】遵守以上全部一般命名规则。

• 【建议】尽可能选择短的或一两个单词。

• 【强制】避免使用保留字作为字段名称：order，date，name 是数据库的保留字，避免使用它。可以为这些名称添加前缀使其易于理解，如 user_name，signup_date 等。

• 【强制】避免使用与表名相同的字段名，这会在编写查询时造成混淆。

• 【强制】在数据库模式上定义外键。

• 【强制】避免使用缩写或基于首字母缩写词的名称。

• 【强制】外键列必须具有表名及其主键，例如：blog_id 表示来自表博客的外键 id。

字段数据类型优化

• 【建议】表中的自增列（auto_increment 属性），推荐使用 bigint 类型。因为无符号 int 存储范围为 0~4,294,967,295（不到 43 亿），溢出后会导致报错。

• 【建议】业务中选择性很少的状态 status、类型 type 等字段推荐使用 tinytint 或者 smallint 类型节省存储空间。

• 【建议】业务中 IP 地址字段推荐使用 int 类型，不推荐用 char(15)。因为 int 只占 4 字节，可以用如下函数相互转换，而 char(15) 占用至少 15 字节。

select inet_aton('192.168.2.12');
select inet_ntoa(3232236044);

Java 保存字符串ip 转 int 类型

public static long ipToLong(String addr)
{
    String[] addrArray = addr.split("\\.");
    long num = 0;
    for (int i = 0; i < addrArray.length; i++)
    {
        int power = 3 - i;
        num += ((Integer.parseInt(addrArray[i]) % 256 * Math.pow(256, power)));
    }
    return num;
}
public static String longToIp(long i){
    return ((i >> 24) & 0xFF) + "." +
           ((i >> 16) & 0xFF) + "." +
           ((i >> 8) & 0xFF) + "." +
           (i & 0xFF);
}

• 【建议】不推荐使用 enum，set。 因为它们浪费空间，且枚举值写死了，变更不方便。推荐使用 tinyint 或 smallint。

• 【建议】不推荐使用 blob，text 等类型。它们都比较浪费硬盘和内存空间。在加载表数据时，会读取大字段到内存里从而浪费内存空间，影响系统性能。建议和 PM、RD 沟通，是否真的需要这么大字段。InnoDB 中当一行记录超过 8098 字节时，会将该记录中选取最长的一个字段将其 768 字节放在原始 page 里，该字段余下内容放在 overflow-page 里。不幸的是在 compact 行格式下，原始 page 和 overflow-page 都会加载。

• 【建议】存储金钱的字段，建议用 int 以分为单位存储，最大数值约 4290 万，程序端乘以 100 和除以 100 进行存取。因为 int 占用 4 字节，而 double 占用 8 字节，空间浪费。

• 【建议】文本数据尽量用 varchar 存储。因为 varchar 是变长存储，比 char 更省空间。MySQL server 层规定一行所有文本最多存 65535 字节，因此在 utf8 字符集下最多存 21844 个字符，超过会自动转换为 mediumtext 字段。而 text 在 utf8 字符集下最多存 21844 个字符，mediumtext 最多存 2^24/3 个字符，longtext 最多存 2^32 个字符。一般建议用 varchar 类型，字符数不要超过 2700。

• 【建议】时间类型尽量选取 timestamp。因为 datetime 占用 8 字节，timestamp 仅占用 4 字节，但是范围为 1970-01-01 00:00:01 到 2038-01-01 00:00:00。更为高阶的方法，选用 int 来存储时间，使用 SQL 函数 unix_timestamp() 和 from_unixtime() 来进行转换。

索引设计

• 【强制】InnoDB 表必须主键为 id int/bigint auto_increment，且主键值禁止被更新。

• 【建议】主键的名称以 pk_ 开头，唯一键以 uk_ 开头，普通索引以 ix_ 开头，一律使用小写格式，以表名/字段的名称或缩写作为后缀。

• 【强制】InnoDB 和 MyISAM 存储引擎表，索引类型必须为 BTREE；MEMORY 表可以根据需要选择 HASH 或者 BTREE 类型索引。

• 【强制】单个索引中每个索引记录的长度不能超过 64KB。

• 【建议】单个表上的索引个数不能超过 7 个。

• 【建议】在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面。如列 user_id 的区分度可由 select count(distinct user_id) 计算出来。

• 【建议】在多表 join 的 SQL 里，保证被驱动表的连接列上有索引，这样 join 执行效率最高。

• 【建议】建表或加索引时，保证表里互相不存在冗余索引。对于 MySQL 来说，如果表里已经存在 key(a, b)，则 key(a) 为冗余索引，需要删除。

• 【建议】如果选择性超过 20%，那么全表扫描比使用索引性能更优，即没有设置索引的必要。

分库分表、分区表

• 【强制】分区表的分区字段（partition-key）必须有索引，或者是组合索引的首列。

• 【强制】单个分区表中的分区（包括子分区）个数不能超过 1024。

• 【强制】上线前 RD 或者 DBA 必须指定分区表的创建、清理策略。

• 【强制】访问分区表的 SQL 必须包含分区键。

• 【建议】单个分区文件不超过 2G，总大小不超过 50G。建议总分区数不超过 20 个。

• 【强制】对于分区表执行 alter table 操作，必须在业务低峰期执行。

• 【强制】采用分库策略的，库的数量不能超过 1024。

• 【强制】采用分表策略的，表的数量不能超过 4096。

• 【建议】单个分表不超过 500W 行，ibd 文件大小不超过 2G，这样才能让数据分布式变得性能更佳。

• 【建议】水平分表尽量用取模方式，日志、报表类数据建议采用日期进行分表。

字符集

• 【强制】数据库本身库、表、列所有字符集必须保持一致，为 utf8 或 utf8mb4。

• 【强制】前端程序字符集或者环境变量中的字符集，与数据库、表的字符集必须一致，统一为 utf8。

程序层 DAO 设计建议

• 【建议】新的代码不要用 model，推荐使用手动拼 SQL + 绑定变量传入参数的方式。因为 model 虽然可以使用面向对象的方式操作 db，但是其使用不当很容易造成生成的 SQL 非常复杂，且 model 层自己做的强制类型转换性能较差，最终导致数据库性能下降。

• 【建议】前端程序连接 MySQL 或者 Redis，必须要有连接超时和失败重连机制，且失败重试必须有间隔时间。

• 【建议】前端程序报错里尽量能够提示 MySQL 或 Redis 原生态的报错信息，便于排查错误。

• 【建议】对于有连接池的前端程序，必须根据业务需要配置初始、最小、最大连接数，超时时间以及连接回收机制，否则会耗尽数据库连接资源，造成线上事故。

• 【建议】对于 log 或 history 类型的表，随时间增长容易越来越大，因此上线前 RD 或者 DBA 必须建立表数据清理或归档方案。

• 【建议】在应用程序设计阶段，RD 必须考虑并规避数据库中主从延迟对于业务的影响。尽量避免从库短时延迟（20 秒以内）对业务造成影响，建议强制一致性的读开启事务走主库，或更新后过一段时间再去读从库。

• 【建议】多个并发业务逻辑访问同一块数据（InnoDB 表）时，会在数据库端产生行锁甚至表锁导致并发下降，因此建议更新类 SQL 尽量基于主键去更新。

• 【建议】业务逻辑之间加锁顺序尽量保持一致，否则会导致死锁。

• 【建议】对于单表读写比大于 10:1 的数据行或单个列，可以将热点数据放在缓存里（如 Memcached 或 Redis），加快访问速度，降低 MySQL 压力。

一个规范的建表语句示例

一个较为规范的建表语句为：

create table user
(
    `id`            bigint(11) not null auto_increment,
    `user_id`       bigint(11) not null comment '用户 ID',
    `username`      varchar(45) not null comment '登录名',
    `email`         varchar(30) not null comment '邮箱',
    `nickname`      varchar(45) not null comment '昵称',
    `avatar`        int(11) not null comment '头像',
    `birthday`      date not null comment '生日',
    `gender`        tinyint(4) default '0' comment '性别',
    `intro`         varchar(150) default null comment '简介',
    `resume_url`    varchar(300) not null comment '简历存放地址',
    `register_ip`   int not null comment '用户注册时的源 IP',
    `review_status` tinyint not null comment '审核状态，1-通过，2-审核中，3-未通过，4-尚未提交审核',
    `create_time`   timestamp not null comment '记录创建的时间',
    `update_time`   timestamp not null comment '资料修改的时间',
    primary key (`id`),
    unique key `idx_user_id` (`user_id`),
    key `idx_username`(`username`),
    key `idx_create_time`(`create_time`, `review_status`)
)
engine = InnoDB
default charset = utf8
comment = '用户基本信息';

DML 语句

• 【强制】select 语句必须指定具体字段名称，禁止写成 *。因为 select * 会将不该读的数据也从 MySQL 里读出来，造成网卡压力。

• 【强制】insert 语句指定具体字段名称，不要写成 insert into t1 values(…)，道理同上。

• 【建议】insert into … values(xx),(xx),(xx)…，这里 xx 的值不要超过 5000 个。值过多虽然上线很快，但会引起主从同步延迟。

• 【建议】select 语句不要使用 union，推荐使用 union all，并且 union 子句个数限制在 5 个以内。因为 union all 不需要去重，节省数据库资源，提高性能。

• 【建议】in 值列表限制在 500 以内。例如 select … where user_id in(…500 个以内…)，这么做是为了减少底层扫描，减轻数据库压力从而加速查询。

• 【建议】事务里批量更新数据需要控制数量，进行必要的 sleep，做到少量多次。

• 【强制】事务涉及的表必须全部是 InnoDB 表。否则一旦失败不会全部回滚，且易造成主从库同步终端。

• 【强制】写入和事务发往主库，只读 SQL 发往从库。

• 【强制】除静态表或小表（100 行以内），dml 语句必须有 where 条件，且使用索引查找。

• [必須] sql_no_cache、強制インデックス、無視キー、ストレート結合などのヒントを本番環境で使用することは禁止されています。ヒントは、SQL を特定の実行計画に従って強制的に実行するために使用されますが、データの量が変化すると、元の予測が正しいかどうかを保証できないため、MySQL オプティマイザーを信頼する必要があります。

• [必須] where 条件の等号の周囲のフィールド タイプは一貫している必要があり、そうでない場合はインデックスを使用できません。

• [推奨] select|update|delete|replace には where 句が必要で、where 句の条件はインデックス検索を使用する必要があります。

• [必須] 運用データベース内の大きなテーブルに対してフル テーブル スキャンを実行することは強く推奨されませんが、フル テーブル スキャンは、以下のサイズの静的テーブルに対して実行できます。 100行。クエリ データの量はテーブルの行数の 25% を超えてはなりません。25% を超えない場合、インデックスは使用されません。

• [必須] where 句の検索に完全にファジーな条件のみを使用することは禁止されています。他の等しい値または範囲のクエリ条件が必要です。そうでない場合、インデックスは使用できません。

• [推奨事項] インデックス列では関数や式を使用しないでください。使用しないと、インデックスが使用できなくなります。 length(name) = 'admin' または user_id 2 = 10023 など。

• [推奨事項] or ステートメントの使用を減らし、or ステートメントをユニオンに最適化して、各 where 条件にインデックスを作成します。たとえば、a = 1 または b = 2 は、a = 1 … Union … b = 2、key(a)、key(b) に最適化されます。

• [提案] ページング クエリで、制限の開始点が高い場合、フィルター条件を使用して最初にフィルターすることができます。たとえば、select a, b, c from t1 limit 10000, 20; は、select a, b, c from t1 where id > 10000 limit 20; に最適化されます。

複数テーブル接続

• [必須] Cross-DB join ステートメントは禁止されています。これにより、モジュール間の結合が軽減され、データベース分割の強固な基盤が築かれるためです。

• [必須] update t1 join t2… などのビジネス更新 SQL ステートメントでの join の使用は禁止されています。

• [推奨事項] サブクエリの使用は推奨しません、サブクエリ SQL を分割して複数のクエリと組み合わせるか、サブクエリの代わりに結合を使用することをお勧めします。

• [推奨事項] オンライン環境では、複数テーブルの結合は 3 テーブルを超えないようにしてください。

• [推奨事項] 複数テーブル接続クエリにはエイリアスを使用することをお勧めします。エイリアスは、選択リスト、database.table 形式のフィールドを参照するために使用する必要があります。 db1.table1 alias1 から…。

• [推奨事項] 複数テーブル結合では、他のテーブルを結合するための駆動テーブルとして、より小さい結果セットを持つテーブルを選択するようにしてください。

トランザクション

• [推奨事項] トランザクション内の insert|update|delete|replace ステートメント操作の行数は、次の範囲内で制御する必要があります。 2000、where sub 文中のinリストで渡されるパラメータの数は500以内に制御されます。

• 【推奨事項】 データをバッチ操作する場合、トランザクションの処理間隔を制御し、必要なスリープを行う必要があり、一般的に推奨される値は5～10秒です。

• [推奨事項] auto_increment 属性フィールドを持つテーブルへの挿入操作の場合、同時実行数は 200 以内に制御する必要があります。

• [必須] プログラム設計では、ダーティ リード、非反復読み取り、ファントム リードなどの「データベース トランザクション分離レベル」の影響を考慮する必要があります。オンラインでは、トランザクション分離レベルを反復読み取り可能にすることをお勧めします。

• [推奨事項] トランザクションに含める SQL ステートメントは 5 つ以下にしてください (決済ビジネスを除く)。トランザクションが長すぎると、長時間のデータのロック、MySQL の内部キャッシュ、過度の接続消費などの雪崩の問題が発生するためです。

• [推奨事項] トランザクション内の update ステートメントは、update ... where id = XX; のように、できる限り主キーまたは一意のキーに基づいて行う必要があります。それ以外の場合は、ギャップを使用します。ロックが生成され、ロック範囲が内部的に拡張されるため、システムのパフォーマンスが低下し、デッドロックが発生します。

• [推奨事項] トランザクションが長すぎるのを避けるために、Web サービスの呼び出し、ファイル ストレージへのアクセスなど、いくつかの一般的な外部呼び出しをトランザクションの外に移動してみてください。

• [推奨事項] MySQL マスター/スレーブ遅延の影響を厳密に受けやすい select ステートメントの場合は、トランザクションを有効にしてメイン データベースに強制的にアクセスしてください。

並べ替えとグループ化

• [推奨事項] order by の使用を減らし、並べ替えなしで並べ替えできない場合は企業と連絡を取ります。または、並べ替えをターミナル上で実行します。 order by、group by、distinct などのステートメントはより多くの CPU を消費するため、データベースの CPU リソースは非常に貴重です。

• [推奨事項] order by、group by、distinct などの SQL では、インデックスを使用してソートされたデータを直接取得してみてください。たとえば、a = 1 の場合、order by では key(a, b) を使用できます。

• [推奨事項] order by、group by、distinct を含むクエリの場合、where 条件でフィルターされた結果セットは 1,000 行以内に収める必要があります。そうしないと、SQL が非常に遅くなります。

SQL ステートメントはオンラインでの使用が禁止されています

• [高リスク] 更新を無効にする | t1 を削除 ... ここで、a = XX 制限 XX; update制限に関するステートメント。マスターとスレーブの不整合が発生し、データの混乱につながるためです。 PKで注文を追加することをお勧めします。

• [高リスク] update t1 set … where name in (select name from user where …); など、非常に非効率な相関サブクエリの使用は禁止されています。

• [必須] プロシージャ、関数、トリガー、ビュー、イベント、および外部キー制約を無効にします。これらはデータベース リソースを消費し、データベース インスタンスのスケーラビリティを低下させるためです。推奨事項はすべてプログラム側で実装されます。

• [必須] 重複キーの更新時に insert into … を無効にする … 同時実行性の高い環境では、マスターとスレーブの不整合が発生します。

• [必須] update t1, t2 where t1.id = t2.id… などの結合テーブル更新ステートメントは禁止されています。

推奨学習: mysql ビデオ チュートリアル

以上がMySQL データベースの設計仕様を要約して整理するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。