Summary of optimization strategies for MySQL table building
- 2020-05-09 19:27:39
- OfStack
directory
1. Selection of character set 1
2. The primary key 1
3. The foreign key 2
4. 2 index
4.1. The following are suitable for creating index 2
4.2. Index 3 is not appropriate for the following situations
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 segmentation 5
6.2. Use non-transaction table type 5
1. Selection of character set
If you want to make sure it's all in Chinese and you don't use multiple languages and characters that cannot be represented in Chinese, then GBK is the first choice.
Using UTF-8 takes three bytes, whereas GBK takes only two.
2. The primary key
Use short primary keys whenever possible
The auto-increment type of the system, AUTO_INCREMEN, instead of using a type like uuid(). If you can use foreign keys as primary keys, even better. For example, in a 1:1 relationship, id of the main 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 goes from 2 to the seventh minus 1: minus 128 to 127
smallint goes from 2 ^ 15 -1: -32768 to 32767
mediumint is represented as 2 to the 23 minus 1: from -8388608 to 8388607
int is 2 to the 31st minus 1
bigint is 2 to the 63rd minus 1
There is no need to build a separate index on the primary key, because clustered indexes are built on the primary key within the system.
Allows the inclusion of primary key columns on other indexes.
3. The foreign key
Foreign key will affect insert and update performance, for batch reliable data insert, it is recommended to shield the foreign key check.
For tables with large amounts of data, it is recommended to remove foreign keys and replace them with data integrity checks by the application.
As far as possible, choose the primary key of the corresponding main table as the foreign key, and avoid selecting the large length of the main table only 1 key as the foreign key.
Foreign keys are indexed by default
Index of 4.
To create an index, create the appropriate number of appropriate indexes on the appropriate tables, the appropriate columns. Strike a balance between query first and update first.
4.1. The following are suitable for index creation
You can speed up your search on frequently searched columns
On a column that is the primary key, force the column's uniqueness and organize the arrangement of the data in the table
On columns that are often joined, these columns are mostly foreign keys that speed up the join
An index is created on a column that is often searched by range because the index is sorted and its specified range is contiguous
By creating an index on a column that often needs to be sorted because the index is already sorted, the query can take advantage of the sorting of the index to speed up the sorting of the query
Create indexes on columns that are commonly used in WHERE clauses to speed up the determination of conditions.
4.2. Index creation is not appropriate under the following circumstances
Indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, having or not having an index does not improve query speed. On the contrary, due to the increase of index, the maintenance speed of the system is reduced and the space requirement is increased.
You should also not add indexes to columns that have few data values. This is because, due to the low value of these columns, such as the gender column of the personnel table, the data rows of the result set account for a large proportion of the data rows in the table in the results of the query, that is, the proportion of the data rows that need to be searched in the table is large. Adding indexes does not significantly speed up retrieval.
Columns defined as text, image, and bit data types should not be indexed. This is because the amount of data in these columns is either quite large or takes very few values.
Indexes should not be created when modification performance is much greater than retrieval performance. This is because modifying performance and retrieving performance are in conflict.
If the table data is small, such as each province by the city to do the summary of the table, 1 as less than 2000, and the data volume is basically unchanged. Adding indexes at this point does not help query performance, but it can significantly affect update performance.
When indexes are added, retrieval performance is improved, but modification performance is reduced. When indexes are reduced, modification performance is improved and retrieval performance is reduced. Therefore, indexes should not be created when the requirement for modification performance is much greater than that for retrieval performance.
4.3. Joint index
In a particular query, a federated index is more effective than multiple single 1 indexes, because when multiple indexes are available, MySQL can only use one of them.
In the query, if the first several column names contained in the federated index are used at the same time, the federated index will be used, otherwise it will be partially or not used. For example, we have a multi-column index on the firstname, lastname, age columns, and we call this index fname_lname_age. When the search criteria are a combination of the following columns, MySQL will use the fname_lname_age index:
firstname, lastname, age
firstname, lastname
firstname
On the other hand, it is equivalent to creating an index on a combination of columns (firstname, lastname, age), (firstname, lastname), and (firstname).
4.4. Index length
For columns of CHAR or Varchar, the index can participate in creating the index with part 1 of the column, depending on the distribution of the data.
create index idx_t_main on t_main(name(3));
This is to specify that the first three characters of name participate in the index, not all of them
The maximum allowed length is 1000 bytes, and for GBK, 500 characters
5. Special fields
5.1. Redundant fields
It's the exchange of space for time. If the large table query often requires join some basic table, and this data is basically unchanged, such as the name of the person, the name of the city, etc. Once the underlying table changes, all the redundant tables involved need to be updated.
5.2. Split the field
If retrieving and summarizing a part of a field is a frequent occurrence (substring()), consider separating this part out.
For example, if you want to count the number of people in a given name for each family name, you can consider saving them by their first and last names instead of one field.
There is also the implementation of some hierarchy structure, you can also consider different levels in different fields.
5.3. BLOB and CLOB
This type of field 1 has a large amount of data, and it is recommended that the database be designed to hold only its external connections, while the data is stored in other ways, such as system files.
6. Special
6.1. Table segmentation
If a table has many columns, but not many columns are usually involved in the query and summary, then you can consider splitting the table into two tables, one is a common field, and the other is a rarely used field.
6.2. Use non-transaction table types
MySQL supports multiple table types, of which InnoDB is supported for things and MyISAM is not, but MyISAM is faster. For some data, such as geographical administrative division, ethnic group, etc., which cannot participate in the transaction, MyISAM type table can be considered.
But for InnoDB tables, you will no longer be able to use MyISAM table data as a foreign key constraint.
The MyISAM table participates in transactions in which the InnoDB table can be normally committed and rolled back without affecting the MyISAM table.
1. Selection of character set 1
2. The primary key 1
3. The foreign key 2
4. 2 index
4.1. The following are suitable for creating index 2
4.2. Index 3 is not appropriate for the following situations
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 segmentation 5
6.2. Use non-transaction table type 5
1. Selection of character set
If you want to make sure it's all in Chinese and you don't use multiple languages and characters that cannot be represented in Chinese, then GBK is the first choice.
Using UTF-8 takes three bytes, whereas GBK takes only two.
2. The primary key
Use short primary keys whenever possible
The auto-increment type of the system, AUTO_INCREMEN, instead of using a type like uuid(). If you can use foreign keys as primary keys, even better. For example, in a 1:1 relationship, id of the main 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 goes from 2 to the seventh minus 1: minus 128 to 127
smallint goes from 2 ^ 15 -1: -32768 to 32767
mediumint is represented as 2 to the 23 minus 1: from -8388608 to 8388607
int is 2 to the 31st minus 1
bigint is 2 to the 63rd minus 1
There is no need to build a separate index on the primary key, because clustered indexes are built on the primary key within the system.
Allows the inclusion of primary key columns on other indexes.
3. The foreign key
Foreign key will affect insert and update performance, for batch reliable data insert, it is recommended to shield the foreign key check.
For tables with large amounts of data, it is recommended to remove foreign keys and replace them with data integrity checks by the application.
As far as possible, choose the primary key of the corresponding main table as the foreign key, and avoid selecting the large length of the main table only 1 key as the foreign key.
Foreign keys are indexed by default
Index of 4.
To create an index, create the appropriate number of appropriate indexes on the appropriate tables, the appropriate columns. Strike a balance between query first and update first.
4.1. The following are suitable for index creation
You can speed up your search on frequently searched columns
On a column that is the primary key, force the column's uniqueness and organize the arrangement of the data in the table
On columns that are often joined, these columns are mostly foreign keys that speed up the join
An index is created on a column that is often searched by range because the index is sorted and its specified range is contiguous
By creating an index on a column that often needs to be sorted because the index is already sorted, the query can take advantage of the sorting of the index to speed up the sorting of the query
Create indexes on columns that are commonly used in WHERE clauses to speed up the determination of conditions.
4.2. Index creation is not appropriate under the following circumstances
Indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, having or not having an index does not improve query speed. On the contrary, due to the increase of index, the maintenance speed of the system is reduced and the space requirement is increased.
You should also not add indexes to columns that have few data values. This is because, due to the low value of these columns, such as the gender column of the personnel table, the data rows of the result set account for a large proportion of the data rows in the table in the results of the query, that is, the proportion of the data rows that need to be searched in the table is large. Adding indexes does not significantly speed up retrieval.
Columns defined as text, image, and bit data types should not be indexed. This is because the amount of data in these columns is either quite large or takes very few values.
Indexes should not be created when modification performance is much greater than retrieval performance. This is because modifying performance and retrieving performance are in conflict.
If the table data is small, such as each province by the city to do the summary of the table, 1 as less than 2000, and the data volume is basically unchanged. Adding indexes at this point does not help query performance, but it can significantly affect update performance.
When indexes are added, retrieval performance is improved, but modification performance is reduced. When indexes are reduced, modification performance is improved and retrieval performance is reduced. Therefore, indexes should not be created when the requirement for modification performance is much greater than that for retrieval performance.
4.3. Joint index
In a particular query, a federated index is more effective than multiple single 1 indexes, because when multiple indexes are available, MySQL can only use one of them.
In the query, if the first several column names contained in the federated index are used at the same time, the federated index will be used, otherwise it will be partially or not used. For example, we have a multi-column index on the firstname, lastname, age columns, and we call this index fname_lname_age. When the search criteria are a combination of the following columns, MySQL will use the fname_lname_age index:
firstname, lastname, age
firstname, lastname
firstname
On the other hand, it is equivalent to creating an index on a combination of columns (firstname, lastname, age), (firstname, lastname), and (firstname).
4.4. Index length
For columns of CHAR or Varchar, the index can participate in creating the index with part 1 of the column, depending on the distribution of the data.
create index idx_t_main on t_main(name(3));
This is to specify that the first three characters of name participate in the index, not all of them
The maximum allowed length is 1000 bytes, and for GBK, 500 characters
5. Special fields
5.1. Redundant fields
It's the exchange of space for time. If the large table query often requires join some basic table, and this data is basically unchanged, such as the name of the person, the name of the city, etc. Once the underlying table changes, all the redundant tables involved need to be updated.
5.2. Split the field
If retrieving and summarizing a part of a field is a frequent occurrence (substring()), consider separating this part out.
For example, if you want to count the number of people in a given name for each family name, you can consider saving them by their first and last names instead of one field.
There is also the implementation of some hierarchy structure, you can also consider different levels in different fields.
5.3. BLOB and CLOB
This type of field 1 has a large amount of data, and it is recommended that the database be designed to hold only its external connections, while the data is stored in other ways, such as system files.
6. Special
6.1. Table segmentation
If a table has many columns, but not many columns are usually involved in the query and summary, then you can consider splitting the table into two tables, one is a common field, and the other is a rarely used field.
6.2. Use non-transaction table types
MySQL supports multiple table types, of which InnoDB is supported for things and MyISAM is not, but MyISAM is faster. For some data, such as geographical administrative division, ethnic group, etc., which cannot participate in the transaction, MyISAM type table can be considered.
But for InnoDB tables, you will no longer be able to use MyISAM table data as a foreign key constraint.
The MyISAM table participates in transactions in which the InnoDB table can be normally committed and rolled back without affecting the MyISAM table.