Perkara Utama yang Anda Mesti Tahu Apabila Mencipta Jadual dalam MySQL

Untuk pembangun bahagian belakang, mengakses pangkalan data adalah penting.

Data pengguna teras biasanya disimpan dengan selamat dalam pangkalan data seperti MySQL atau Oracle.

Tugas harian selalunya melibatkan penciptaan pangkalan data dan jadual untuk memenuhi keperluan perniagaan, tetapi jadual dibuat dengan lebih kerap.

Artikel ini akan menumpukan pada penciptaan jadual kerana mengabaikan butiran penting boleh membawa kepada isu yang mahal dalam penyelenggaraan selepas penggunaan.

Sebenarnya, amalan reka bentuk pangkalan data yang lemah juga boleh menyebabkan API anda bertindak balas dengan perlahan semasa konkurensi tinggi. Imej berikut menunjukkan keputusan ujian prestasi API menggunakan alat EchoAPI.

Hari ini, mari kita bincangkan 18 petua untuk mencipta jadual dalam pangkalan data.

Banyak butiran yang dinyatakan dalam artikel ini berpunca daripada pengalaman dan cabaran saya sendiri yang dihadapi semasa bekerja, dan saya harap ia akan membantu anda.

1. Menamakan

Apabila membuat jadual, medan dan indeks, memberi mereka nama yang baik adalah sangat penting.

1.1 Nama Bermakna

Nama berfungsi sebagai muka jadual, medan dan indeks, meninggalkan kesan pertama.

Nama yang baik adalah ringkas dan menggambarkan diri, menjadikan komunikasi dan penyelenggaraan lebih mudah.

Nama yang buruk adalah samar-samar dan mengelirukan, membawa kepada huru-hara dan kekecewaan.

Contoh Buruk:

Nama medan seperti abc, abc_name, name, user_name_123456789 akan membuatkan anda bingung.

Contoh Baik:

Nama medan sebagai nama_pengguna.

Peringatan lembut: nama juga tidak boleh terlalu panjang, idealnya disimpan dalam 30 aksara.

1.2 Kepekaan Kes

Sebaik-baiknya gunakan huruf kecil untuk nama, kerana ia lebih mudah dibaca secara visual.

Contoh Buruk:

Nama medan seperti PRODUCT_NAME, PRODUCT_name tidak intuitif. Gabungan huruf besar dan kecil kurang menyenangkan untuk dibaca.

Contoh Baik:

Nama medan sebagai nama_produk kelihatan lebih selesa.

1.3 Pemisah

Selalunya, nama mungkin mengandungi berbilang perkataan untuk pemahaman yang lebih baik.

Apakah pemisah yang harus digunakan antara berbilang perkataan?

Contoh Buruk:

Nama medan seperti nama produk, Nama produk, nama produk atau nama produk tidak disyorkan.

Contoh Baik:

Nama medan sebagai nama_produk.

Menggunakan garis bawah _ antara perkataan adalah sangat dinasihatkan.

1.4 Nama Jadual

Untuk nama jadual, adalah disyorkan untuk menggunakan nama yang bermakna dan ringkas bersama-sama dengan awalan perniagaan.

Untuk jadual berkaitan pesanan, tambahkan nama jadual dengan order_, seperti order_pay, order_pay_detail.

Untuk jadual berkaitan produk, tambahkan dengan product_, seperti product_spu, product_sku.

Amalan ini membantu dalam mengkategorikan jadual yang berkaitan dengan perniagaan yang sama bersama-sama dengan cepat.

Selain itu, jika perniagaan bukan pesanan mungkin perlu membuat jadual bernama bayar, ia boleh dibezakan dengan mudah sebagai finance_pay, mengelakkan konflik nama.

1.5 Nama Medan

Nama medan membenarkan fleksibiliti maksimum tetapi boleh menyebabkan kekeliruan dengan mudah.

Sebagai contoh, menggunakan bendera untuk menandakan status dalam satu jadual semasa menggunakan status dalam jadual lain boleh mewujudkan ketidakkonsistenan.

Penstandardan kepada status untuk mewakili negeri adalah dinasihatkan.

Apabila jadual menggunakan kunci utama jadual lain, tambahkan _id atau _sys_no pada penghujung nama medan, contohnya, product_spu_id atau product_spu_sys_no.

Selain itu, standardkan masa penciptaan sebagai masa_buat dan masa pengubahsuaian sebagai masa_kemas kini, dengan status pemadaman ditetapkan sebagai status_padam.

Medan biasa lain juga harus mengekalkan konvensyen penamaan seragam merentas jadual yang berbeza untuk kejelasan yang lebih baik.

1.6 Nama Indeks

Dalam pangkalan data, terdapat pelbagai jenis indeks, termasuk kunci utama, indeks biasa, indeks unik dan indeks komposit.

Jadual biasanya mempunyai satu kunci utama, biasanya dinamakan id atau sys_no.

Indeks biasa dan komposit boleh menggunakan awalan ix_, contohnya, ix_product_status.

Indeks unik boleh menggunakan awalan ux_, seperti ux_product_code.

2. Jenis Medan

Apabila mereka bentuk jadual, kebebasan yang mencukupi wujud dalam memilih jenis medan.

Medan berformat masa boleh menjadi tarikh, masa tarikh atau cap masa, dsb.

Jenis data aksara termasuk varchar, char, teks, dll.

Jenis angka terdiri daripada int, bigint, smallint dan tinyint.

Memilih jenis medan yang sesuai adalah penting.

Menilai terlalu tinggi jenis (mis., menggunakan bigint untuk medan yang hanya akan menyimpan nilai antara 1 dan 10) membazir ruang; tinyint sudah memadai.

Sebaliknya, meremehkan (cth., menggunakan int untuk ID 18 digit) akan menyebabkan kegagalan storan data.

Berikut ialah beberapa prinsip untuk memilih jenis medan:

• Memilih saiz storan kecil sambil memenuhi keperluan perniagaan biasa, memilih daripada kecil kepada besar.
• Gunakan char untuk panjang rentetan tetap atau serupa, dan varchar untuk panjang yang berbeza-beza.
• Gunakan bit untuk medan boolean.
• Gunakan tinyint untuk medan penghitungan.
• Pilih bigint untuk medan kunci utama.
• Gunakan perpuluhan untuk medan kewangan.
• Gunakan cap masa atau masa tarikh untuk medan masa.

3. Panjang Medan

Selepas mentakrifkan nama medan dan memilih jenis medan yang sesuai, tumpuan harus beralih kepada panjang medan, seperti varchar(20) atau bigint(20).

Apakah yang ditunjukkan oleh varchar dari segi panjang—bait atau aksara?

Jawapannya: Dalam MySQL, varchar dan char mewakili panjang aksara, manakala kebanyakan jenis lain mewakili panjang bait.

Sebagai contoh, bigint(4) menentukan panjang paparan, bukan panjang storan, yang kekal 8 bait.

Jika sifat zerofill ditetapkan, nombor kurang daripada 4 bait akan dipadatkan, tetapi walaupun diisi, storan data asas kekal pada 8 bait.

4. Bilangan Medan

Apabila mereka bentuk jadual, adalah penting untuk mengehadkan bilangan medan.

Saya telah melihat jadual dengan berdozen atau bahkan ratusan medan, yang membawa kepada volum data yang besar dan kecekapan pertanyaan yang rendah.

Jika situasi ini timbul, pertimbangkan untuk membahagikan jadual besar kepada yang lebih kecil sambil mengekalkan kunci utama biasa.

Sebagai peraturan, pastikan bilangan medan setiap jadual di bawah 20.

5. Kekunci Utama

Buat kunci utama semasa menyediakan jadual.

Kunci utama sememangnya disertakan dengan indeks kunci utama, menjadikan pertanyaan lebih cekap, kerana ia tidak memerlukan carian tambahan.

Dalam satu pangkalan data, kunci utama boleh menggunakan AUTO_INCREMENT untuk pertumbuhan automatik.

Untuk pangkalan data yang diedarkan, terutamanya dalam seni bina sharded, sebaiknya gunakan algoritma luaran (seperti Snowflake) untuk memastikan ID unik di peringkat global.

Selain itu, pastikan kunci utama bebas daripada nilai perniagaan untuk mengurangkan gandingan dan memudahkan pengembangan masa hadapan.

Walau bagaimanapun, untuk perhubungan satu dengan satu, seperti jadual pengguna dan jadual sambungan pengguna, anda boleh menggunakan terus kunci utama daripada jadual pengguna.

6. Enjin Storan

Sebelum MySQL 8, enjin storan lalai ialah MyISAM; dari MySQL 8 dan seterusnya, kini InnoDB.

Secara sejarah, terdapat banyak perdebatan tentang enjin storan yang hendak dipilih.

MyISAM mengasingkan indeks dan storan data, meningkatkan prestasi pertanyaan tetapi tidak mempunyai sokongan untuk transaksi dan kunci asing.

InnoDB, walaupun sedikit perlahan dalam pertanyaan, menyokong transaksi dan kunci asing, menjadikannya lebih teguh.

Sebelum ini dinasihatkan untuk menggunakan MyISAM untuk membaca-berat dan InnoDB untuk senario menulis-berat.

Walau bagaimanapun, pengoptimuman dalam MySQL telah mengurangkan perbezaan prestasi, jadi menggunakan enjin storan InnoDB lalai dalam MySQL 8 dan yang lebih baru adalah disyorkan tanpa sebarang pengubahsuaian tambahan.

7. BUKAN NULL

Apabila mencipta medan, tentukan sama ada ia boleh menjadi NULL.

Mentakrifkan medan sebagai BUKAN NULL apabila boleh adalah dinasihatkan.

Kenapa?

Dalam InnoDB, menyimpan nilai NULL memerlukan ruang tambahan dan ia juga boleh menyebabkan kegagalan indeks.

Nilai NULL hanya boleh disoal menggunakan IS NULL atau IS NOT NULL, kerana menggunakan = sentiasa mengembalikan palsu.

Oleh itu, takrifkan medan sebagai BUKAN NULL di mana-mana sahaja yang boleh dilakukan.

Walau bagaimanapun, apabila medan ditakrifkan secara langsung sebagai BUKAN NULL, dan nilai dilupakan semasa input, ia akan menghalang pemasukan data.

Ini boleh menjadi situasi yang boleh diterima apabila medan baharu ditambahkan dan skrip dijalankan sebelum menggunakan kod baharu, yang membawa kepada ralat tanpa nilai lalai.

Untuk medan NOT NULL yang baru ditambah, menetapkan nilai lalai adalah penting:

ALTER TABLE product_sku ADD COLUMN brand_id INT(10) NOT NULL DEFAULT 0;

8. Kunci Asing

Kunci asing dalam MySQL berfungsi untuk memastikan ketekalan dan integriti data.

Contohnya:

CREATE TABLE class (
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(15)
);

Ini mencipta jadual kelas.

Kemudian, jadual pelajar boleh dibina yang merujuknya:

CREATE TABLE student(
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(15) NOT NULL,
  gender VARCHAR(10) NOT NULL,
  cid INT,
  FOREIGN KEY (cid) REFERENCES class(id)
);

Di sini, cid dalam jadual pelajar merujuk id dalam jadual kelas.

Percubaan untuk memadam rekod dalam pelajar tanpa mengalih keluar rekod cid yang sepadan dalam kelas akan menimbulkan ralat kekangan kunci asing:

kekangan kunci asing gagal.

Oleh itu, konsistensi dan integriti terpelihara.

Perhatikan bahawa kunci asing hanya boleh digunakan dengan enjin storan InnoDB.

If only two tables are linked, it might be manageable, but with several tables, deleting a parent record requires synchronously deleting many child records, which can impact performance.

Thus, for internet systems, it is generally advised to avoid using foreign keys to prioritize performance over absolute data consistency.

In addition to foreign keys, stored procedures and triggers are also discouraged due to their performance impact.

9. Indexes

When creating tables, beyond specifying primary keys, it’s essential to create additional indexes.

For example:

CREATE TABLE product_sku(
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  spu_id INT(10) NOT NULL,
  brand_id INT(10) NOT NULL,
  name VARCHAR(15) NOT NULL
);

This table includes spu_id (from the product group) and brand_id (from the brand table).

In situations that save IDs from other tables, a regular index can be added:

CREATE TABLE product_sku (
  id INT(10) PRIMARY KEY AUTO_INCREMENT,
  spu_id INT(10) NOT NULL,
  brand_id INT(10) NOT NULL,
  name VARCHAR(15) NOT NULL,
  KEY `ix_spu_id` (`spu_id`) USING BTREE,
  KEY `ix_brand_id` (`brand_id`) USING BTREE
);

Such indexes significantly enhance query efficiency.

However, do not create too many indexes as they can hinder data insertion efficiency due to additional storage requirements.

A single table should ideally have no more than five indexes.

If the number of indexes exceeds five during table creation, consider dropping some regular indexes in favor of composite indexes.

Also, when creating composite indexes, always apply the leftmost matching rule to ensure the indexes are effective.

For fields with high duplication rates (like status), avoid creating separate regular indexes. MySQL may skip the index and choose a full table scan instead if it’s more efficient.

I’ll address index inefficiency issues in a separate article later, so let’s hold off on that for now.

10. Time Fields

The range of types available for time fields in MySQL is fairly extensive: date, datetime, timestamp, and varchar.

Using varchar might be for API consistency where time data is represented as a string.

However, querying data by time ranges can be inefficient with varchar since it cannot utilize indexes.

Date is intended only for dates (e.g., 2020-08-20), while datetime and timestamp are suited for complete date and time.

There are subtle differences between them.

Timestamp: uses 4 bytes and spans from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07. It’s also timezone-sensitive.

Datetime: occupies 8 bytes with a range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59, independent of time zones.

Using datetime to save date and time is preferable for its wider range.

As a reminder, when setting default values for time fields, avoid using 0000-00-00 00:00:00, which can cause errors during queries.

11. Monetary Fields

MySQL provides several types for floating-point numbers: float, double, decimal, etc.

Given that float and double may lose precision, it’s recommended to use decimal for monetary values.

Typically, floating numbers are defined as decimal(m,n), where n represents the number of decimal places, and m is the total length of both integer and decimal portions.

For example, decimal(10,2) allows for 8 digits before the decimal point and 2 digits after it.

12. JSON Fields

During table structure design, you may encounter fields needing to store variable data values.

For example, in an asynchronous Excel export feature, a field in the async task table may need to save user-selected query conditions, which can vary per user.

Traditional database fields don’t handle this well.

Using MySQL’s json type enables structured data storage in JSON format for easy saving and querying.

MySQL also supports querying JSON data by field names or values.

13. Unique Indexes

Unique indexes are frequently used in practice.

You can apply unique indexes to individual fields, like an organization’s code, or create composite unique indexes for multiple fields, like category numbers, units, specifications, etc.

Unique indexes on individual fields are straightforward, but for composite unique indexes, if any field is NULL, the uniqueness constraint may fail.

Another common issue is having unique indexes while still producing duplicate data.

Due to its complexity, I’ll elaborate on unique index issues in a later article.

When creating unique indexes, ensure that none of the involved fields contain NULL values to maintain their uniqueness.

14. Character Set

MySQL supports various character sets, including latin1, utf-8, utf8mb4, etc.

Here’s a table summarizing MySQL character sets:

Character Set	Description	Encoding Size	Notes
latin1	Encounters encoding issues; rarely used in real projects	1 byte	Limited support for international characters
utf-8	Efficient in storage but cannot store emoji	3 bytes	Suitable for most text but lacks emoji support
utf8mb4	Supports all Unicode characters, including emoji	4 bytes	Recommended for modern applications

It’s advisable to set the character set to utf8mb4 during table creation to avoid potential issues.

15. Collation

When creating tables in MySQL, the COLLATE parameter can be configured.

For example:

CREATE TABLE `order` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(20) COLLATE utf8mb4_bin NOT NULL,
  `name` VARCHAR(30) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_code` (`code`),
  KEY `un_code_name` (`code`,`name`) USING BTREE,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

The collation determines how character sorting and comparison are conducted.

Character collation depends on the character set, which for utf8mb4 would also start with utf8mb4_. Common types include utf8mb4_general_ci and utf8mb4_bin.

The utf8mb4_general_ci collation is case-insensitive for alphabetical characters, while utf8mb4_bin is case-sensitive.

This distinction is important. For example, if the order table contains a record with the name YOYO and you query it using lowercase yoyo under utf8mb4_general_ci, it retrieves the record. Under utf8mb4_bin, it will not.

Choose collation based on the actual business needs to avoid confusion.

16. Large Fields

Special attention is warranted for fields that consume substantial storage space, such as comments.

A user comment field might require limits, like a maximum of 500 characters.

Defining large fields as text can waste storage, thus it’s often better to use varchar for better efficiency.

For much larger data types, like contracts that can take up several MB, it may be unreasonable to store directly in MySQL.

Instead, such data could be stored in MongoDB, with the MySQL business table retaining the MongoDB ID.

17. Redundant Fields

To enhance performance and query speed, some fields can be redundantly stored.

For example, an order table typically contains a userId to identify users.

However, many order query pages also need to display the user ID along with the user’s name.

If both tables are small, a join is feasible, but for large datasets, it can degrade performance.

In that case, creating a redundant userName field in the order table can resolve performance issues.

While this adjustment allows direct querying from the order table without joins, it requires additional storage and may lead to inconsistency if user names change.

Therefore, carefully evaluate if the redundant fields strategy fits your particular business scenario.

18. Comments

When designing tables, ensure to add clear comments for tables and associated fields.

For example:

CREATE TABLE `sys_dept` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` VARCHAR(30) NOT NULL COMMENT 'Name',
  `pid` BIGINT NOT NULL COMMENT 'Parent Department',
  `valid_status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Valid Status: 1=Valid, 0=Invalid',
  `create_user_id` BIGINT NOT NULL COMMENT 'Creator ID',
  `create_user_name` VARCHAR(30) NOT NULL COMMENT 'Creator Name',
  `create_time` DATETIME(3) DEFAULT NULL COMMENT 'Creation Date',
  `update_user_id` BIGINT DEFAULT NULL COMMENT 'Updater ID',
  `update_user_name` VARCHAR(30)  DEFAULT NULL COMMENT 'Updater Name',
  `update_time` DATETIME(3) DEFAULT NULL COMMENT 'Update Time',
  `is_del` TINYINT(1) DEFAULT '0' COMMENT 'Is Deleted: 1=Deleted, 0=Not Deleted',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_pid` (`pid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='Department';

Detailed comments clarify the purpose of tables and fields.

Particularly for fields representing statuses (like valid_status), it immediately conveys the intent behind the data, such as indicating valid versus invalid.

Avoid situations where numerous status fields exist without comments, leading to confusion about what values like 1, 2, or 3 signify.

Initially, one might remember, but after a year of operation, it’s easy to forget, potentially leading to significant pitfalls.

Thus, when designing tables, meticulous commenting and regular updates of these comments are essential.

That wraps up the technical section of this article,If you have a different opinion, let me know?.

Atas ialah kandungan terperinci Perkara Utama yang Anda Mesti Tahu Apabila Mencipta Jadual dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!