Summary of common MySQL table design errors

Mistake 1: Too many columns of data

The MySQL storage engine API copies data from the server to the storage engine in a row-buffered manner. The server decodes the buffer data into data columns. However, converting the format of the row buffer to the columns of the data row data structure can be expensive. MyISAM uses a fixed row format that matches the server, so no conversion is required. However, the variable row format of MyISAM and the row format of InnoDB always require conversion. The cost of the conversion depends on the number of columns. If the number of columns in a data table exceeds hundreds, it will cause high CPU resource consumption - even if only a few columns are used. I once read an article that referred to a multi-language solution, which simply and crudely represented the languages ​​supported by the system with corresponding columns, for example:

CREATE TABLE t_multi_language_news (
  id INT PRIMARY KEY,
  title_cn VARCHAR(32),
  title_en VARCHAR(32),
  title_it VARCHAR(32),
  ...
  content_cn VARVHAR(256),
  content_en VARCHAR(256),
  conntent_it VARCHAR(256),
);

This approach will eventually lead to a serious performance degradation as the system supports more languages ​​and the number of columns in the data table increases. If you design a data table with more than 100 columns, you need to consider whether your design is reasonable. **Response method: **First, consider whether the design of the business itself is reasonable. If an entity does require many fields to describe it, you can split the data table and do it by extending the information table. For example, for information data tables, because the content generally occupies a large space, but will not be directly viewed in the list, it can be split into an information main table and an information detail table. The main table stores the information to be viewed in the list, such as title, time, summary, thumbnail attachment id, etc. The information details can store information such as the content, source, original link, etc.

Misunderstanding 2: Too many joint queries

MySQL can only query a maximum of 61 tables at a time. Some designs advocate not to design redundant fields, which will result in the need to connect multiple tables for query in complex business. Even if the number of joined tables is less than 61, performance will be degraded and maintenance of the entire SQL statement will become very difficult. As a first principle of design, if you want to pursue speed, do not perform joint queries across too many data tables in one query, especially when facing high concurrency scenarios. **Response method: **First, for fields that are certain not to change, you can consider reducing joint queries by using redundant fields. For example, the province information of a company can make the province code and province name redundant, and there is no need to query the province name through the province code. Secondly, if you really need to query other tables, you can consider using a step-by-step query method to complete the data assembly through the application. This method will be more efficient when there are many data tables, and the code will be easier to maintain. Misunderstanding 3: Universal enumeration For example, the following table design:

CREATE TABLE t_countries (
  ...
  country ENUM('', '1', '2', ..., '45'),
  ...
);

This could have been implemented using a dictionary lookup table with integer keys. If an enumeration is added for business purposes, it means that the entire table needs to be updated using ALTER TABLE. If you use a lookup table in application code, you only need to add new key-value pairs. **How ​​to deal with it:** If the enum is known to be immutable (e.g. gender), then it’s fine. If the enumeration may be increased, then it is best to do so through the application if possible.

Misunderstanding 3: Abuse of SET instead of ENUM

The value of an enumeration ENUM type column in a data table can only be one of a set of values, while the SET type column can have one or more values. If you are sure that a column will only have one value, you should use enumerations instead of collections. For example, the following example is a typical abuse:

CREATE TABLE t_payment_way (
  ...
  is_default SET('Y', 'N') NOT NULL DEFAULT 'N',
  ...
);

Obviously, is_default is either Y or N, so ENUM should be used here. **Solution: **Consider from a business perspective whether the column may have multiple values. If there is only one optional value, use enumeration ENUM.

Myth 4: Avoiding NULL

Many articles have discussed avoiding NULL as much as possible. This is a good design for most scenarios. We can represent null values ​​by 0, empty string, agreed value, etc. However, don't be fooled by this; if the value itself is meaningless, then using NULL may be more appropriate. For example, if -1 represented a meaningless integer, it would lead to complicated code and might even cause bugs. For example, the following example:

CREATE TABLE t_person (
  birthday DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  ...,
);

It would be strange to set the default value of a DATETIME type to all 0s. Suppose we want to calculate the average age of people, it will cause inexplicable problems. In this scenario, using NULL will not include it in the statistics. You can avoid this situation by setting the MySQL SQL_MODE parameter to prohibit the use of meaningless dates. **Coping method: **When designing a table, you can try to use NOT NULL to avoid null values, but don't be too rigid. For some fields, when the default value does not make sense in the table name or does not match the actual situation, you can also choose to use a NULL column. However, you need to be careful not to use NULL in the index column. In reality, most index columns are unlikely to be NULL.

Myth 5: Using integers instead of timestamps

I have mentioned before how to choose the time format. In fact, some developers use integers to store timestamps. Their reason is that this is more efficient. In a sense, it may improve efficiency a little, but it is not of much help because DATETIME and TIMESTAMP are stored as integers in MySQL. If you use integers to store time, it means that time conversion needs to be done in the application, or the SQL statement needs to convert the time of the specified field, and the benefits may not outweigh the costs. **Solution: **Use DATETIME to store time as much as possible. If you need to store time with a precision below the second level, consider using BIGINT to store it.

Mistake 6: Forgetting the maximum storage range of a field

In practice, when designing a table, people often forget the storage range of the data type. For example, TINYINT(2) does not limit the storage of two-digit integers. The actual storage range of TINYINT(2) is -128-127. Store integers greater than 255. This error is prone to occur when using integer types. When inserting an integer, MySQL does not check the actual number of integer digits, but stores it according to the range of corresponding storage bytes. In this case, if you are not careful, meaningless values ​​will be stored. For example, the following INSERT operation will succeed, and we may mistakenly believe that TINYINT(2) can only store 2-bit integers:

CREATE TABLE t_int_test (
    id INT PRIMARY KEY,
    number TINYINT(2)
);

INSERT INTO t_int_test (id, number) VALUES (3,123);

Solution: Perform data verification in the application.

Conclusion:

In the actual process of designing a data table, in addition to considering the data type of each field, you also need to consider the size of the storage space. For some commonly used fields, such as time, title, notes, etc., it is best to form certain standards internally and everyone should follow the standards. Adding verification can avoid many problems.

The above is the detailed summary of common MySQL data table design errors. For more information about MySQL data table design errors, please pay attention to other related articles on 123WORDPRESS.COM!