Pitfalls to avoid in MySQL schema design

Remember the Scarlet Letter :

1. Beware of too many columns in the table :

The MySQL storage Engine API works by copying data between the server layer and the storage engine layer in a row-buffered format, and then decoding the buffered content into individual columns at the server layer. The cost of converting a coded column from a row buffer into a line data structure is very expensive. The fixed-length line structure of the MyISAM is actually exactly the same as the row structure of the server layer, so no conversion is required. However, MyISAM's variable-length line structure and InnoDB's row structure always need to be converted. The cost of the conversion depends on the number of columns. When we look at a case with very high CPU usage, we find that the customer uses a very wide table (thousands of fields), but only a small number of columns are actually used, and the cost of the conversion is very high. If you plan to use thousands of fields, you must realize that there are some differences in the performance characteristics of the server.

2. Too many associations :

The so-called "entity-attribute-value" (EAV) design pattern is a common bad design pattern, especially when it's not working reliably under MySQL. MySQL restricts a maximum of 61 tables per association operation , but the EAV database requires many self-associations. We've seen a lot of EAV databases that last exceeded this limit. In fact, in many cases with fewer than 61 tables, the cost of parsing and optimizing queries can also be a problem for MySQL. A rough rule of thumb, if you want queries to execute quickly and with good concurrency, a single query is best to correlate within 12 tables .

3. The Almighty enumeration:

　　

Pitfalls to avoid in MySQL schema design