MySQL database design common mistakes and impact on performance

1. Excessive anti-normalization to create too many columns for a table

When we design the structure of the database, it is easy to make the first mistake is that the table is excessively anti-normalization design, which makes the table too many columns, although MySQL allows a table to build a lot of columns, but because of the MySQL plug-in architecture, the previous blog has been introduced, MySQL's server layer and storage engine layer is separate, the MySQL storage Engine API work requires the server layer and the storage engine layer through the buffer format to copy data, and then at the server layer to parse the buffer layer of data into columns, this process cost is very high, Especially for MyISAM's variable-length structure, and InnoDB this line structure must also be converted at the time of parsing, the cost of this conversion depends on the number of columns, so if a table has too many columns, the use of this table will result in excessive CPU consumption. So in the design of the table must pay attention to, do not put all the table related columns in a table, but to follow the normalization of the appropriate table to split, about what is normalization, will be described in detail.

2. Excessive use of the paradigm design causes too many table associations

The design of the database over the use of the paradigm of the design of the idea, for any query to be associated with many tables, through the previous introduction, we know that MySQL Table association query cost is very high, and the performance will also be increased with the association table, so the number of MySQL table association is limited, MySQL can only associate a maximum of 61 tables, this restriction is enough for most applications, but we have to minimize the associated table for MySQL performance, the number of associated tables preferably within 10, which requires us to design the database in the appropriate anti-normalization design, It is helpful to improve the performance of the database and the performance of SQL queries by synthesizing a large table of two small tables that are used frequently.

3. Using inappropriate partition tables in an OLTP environment

Partition table is a good thing, can help us to put a large table in the physical storage according to partition key into a number of small tables, here to note that the partition table and we often say that the sub-database table is different, the partition table is in the same database instance, The physical storage is divided into smaller tables but logically or using a table when used, while the partitioning table is not only physically split but also logically splits multiple tables, and multiple tables after the sub-database are usually not in a DB instance. When using partitioned tables, the choice of partitioning keys is critical, and if the partitioning key selection is inappropriate, it causes queries to be queried across multiple partitions, not only to improve the performance of the database, but also to reduce the query performance of the database, so it is recommended that you use partitioned tables in an OLTP environment. Partitioned tables are best used in an OLAP environment, or are more appropriate for some log tables.

4. Using FOREIGN KEY constraints to ensure transactional integrity

We all know that the InnoDB storage engine is a transactional storage engine that supports transactions and foreign keys, so many developers prefer to use foreign key constraints to ensure the integrity of the data, but this is very inefficient, because when modifying a table using a foreign key, MySQL will check for foreign key constraints. This brings additional lock overhead, reduces the efficiency of database modification, and the use of foreign keys, in the database backup, recovery, or manual data archiving maintenance can also be problematic, for example: we can not use TRUNCATE table to quickly empty the tables, can only use the delete From, so that in the context of the master-slave replication environment for a large table of data Environment cleanup complexity will become very high, it is strongly recommended not to use foreign key constraints, but it is necessary to establish related indexes on the association key

MySQL database design common mistakes and impact on performance