Summary of MySQL table creation optimization policies

Directory
1. Character Set Selection 1
2. Primary Key 1
3. Foreign key 2
4. index 2
4.1 The following conditions are suitable for creating an index 2
4.2. Index 3 is not suitable in the following cases
4.3. Joint index 3
4.4. Index length 4
5. Special Field 4
5.1. Redundant field 4
5.2. Split field 4
5.3. BLOB and CLOB 5
6. Special 5
6.1. Table Partitioning 5
6.2. Use non-transaction table type 5

1. Character Set Selection
If it is confirmed that all characters are Chinese and do not contain any characters that cannot be expressed in multiple languages or Chinese, GBK is the first choice.
Using UTF-8 encoding occupies 3 bytes, while gbk only needs 2 bytes.
2. Primary Key
Use a primary key with a shorter length as much as possible
The auto-increment type of the system is AUTO_INCREMEN, rather than uuid. It is better if you can use a foreign key as the primary key. For example, in the relationship, the id of the primary table is used as the primary key of the slave table.
The Field Length of the primary key needs to be specified as needed.
Tinyint powers 7 from 2-1:-128 to 127
Smallint from the 15th power of 2-1:-32768 to 32767
Mediumint indicates 2's 23th power-1: From-8388608 to 8388607
Int indicates the power 31-1 of 2.
Bigint indicates power 63-1 of 2

You do not need to create an independent index on the primary key because the system creates a clustered index for the primary key.
Other Indexes can contain primary key columns.
3. Foreign key
Foreign keys affect the insertion and update performance. For batch reliable data insertion, we recommend that you disable the foreign key check first.
For tables with a large amount of data, we recommend that you remove the foreign key and use the application to perform data integrity check.
Use the primary key of the corresponding primary table as the foreign key whenever possible to avoid using the unique key of the primary table with a large length as the foreign key.
The foreign key is indexed by default.
4. Index
To create an index, you must create an appropriate number of indexes in the appropriate table and columns. Balance between Query priority and update priority.
4.1 The following conditions are suitable for creating an index:
You can speed up the search on columns that frequently need to be searched.
In a column that acts as a primary key, force the uniqueness of the column and the data arrangement structure in the organization table
These columns are usually used in connection columns. These columns are mainly foreign keys, which can speed up the connection.
Create an index on a column that often needs to be searched by range. Because the index has been sorted, the specified range is continuous.
Create an index on the columns that need to be sorted frequently, because the index has been sorted, so that the query can use the index sorting to speed up the sorting query time.
Create an index on the columns in the WHERE clause frequently to speed up condition judgment.

4.2. indexes cannot be created in the following cases
Indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, there is an index or no index, and the query speed cannot be improved. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements.
Indexes should not be added to columns with only few data values. This is because these columns have very few values, such as gender columns in the personnel table. In the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the proportion of data rows to be searched in the table is large. Adding indexes does not significantly accelerate the search speed.
Indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is either large or small.
When the modification performance is much higher than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent.
If the table data is very small, for example, the city-based tables in each province are generally less than 2000, and the data volume remains unchanged. In this case, adding indexes does not help query performance, but will greatly affect the update performance.

When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when the modification performance requirement is far greater than the retrieval performance, you should not create an index.
4.3. Joint Index
In a specific query, the combined index is more effective than multiple single indexes, because MySQL can only use one of these indexes when multiple indexes are available.
In the query, the first several column names contained in the Union index will use the Union Index. Otherwise, the Union Index will be partially or not used. For example, we have a multiple-column index on the firstname, lastname, and age columns. We call this index fname_lname_age. When the search condition is a combination of the following columns, MySQL uses the fname_lname_age index:
Firstname, lastname, age
Firstname, lastname
Firstname
On the other hand, it is equivalent to the index created on the combination of columns (firstname, lastname, age), (firstname, lastname), and (firstname.
4.4. Index Length
For CHAR or Varchar columns, the index can be used to create an index based on the data distribution.
Create index idx_t_main on t_main (name (3 ));
The first three characters of the specified name participate in the index, instead of all
The maximum length allowed is 1000 bytes, And the GBK encoding is 500 Chinese characters.
5. Special fields
5.1. Redundant Fields
It is to exchange space for time. If you want to join a base table frequently in a large table query, and the data remains unchanged, such as the person's name and city name. Once the base table changes, you must update all the redundant tables involved.
5.2. Split Fields
If you frequently retrieve and aggregate a certain part of a field (substring (), consider separating this part.
For example, to count the number of people with each surname in a name, you can save the number by name rather than by field.
In addition, some lower-level structure implementations can also be considered to put different levels in different fields.
5.3. BLOB and CLOB
This type of field generally has a large amount of data. We recommend that you design a database to only store its external connections, and save the data in other ways, such as system files.
6. Special
6.1. Table Partitioning
If a table has many columns, but there are not many columns involved in queries and aggregation at ordinary times, you can split the table into two tables. One is a common field, another field is rarely used.
6.2. Use non-transaction table types
MySQL supports multiple table types. The InnoDB type supports transactions, while the MyISAM type does not, but MyISAM is faster. For data that cannot be involved in transactions, such as geographic administrative division and ethnicity, you can use a MyISAM table.
However, InnoDB tables cannot use MyISAM table data for foreign key constraints.
For transactions involving MyISAM tables, the InnoDB table can be submitted and rolled back normally, but does not affect MyISAM tables.