mysql-Reading notes 1

There are some pitfalls in MySQL schema design, and this is just about the problem with MySQL implementation.

1 Too many columns

The MySQL storage engine communicates with the server layer by copying the data in a row-buffered format and then decoding the buffered content into individual columns at the server layer. The speed of decoding depends on whether the column is a fixed-length field and the number of columns in the row. To resolve too many columns in a row, you need to split the entity multiple properties into several tables. The so-called "entity-attribute-value" design pattern, that is, the EAV design pattern. The EAV design pattern is used to solve too many column problems in a field, where only a few property values are present in an entity, and the remaining values are null. EAV design mode is a bad design for moss .... What ...?

2 Enumeration

When a field is enumerated enum type, only one value can be selected. You can select one or more values when the set type is set.

The way and the inverse way

In the full paradigm, each data is stored only once. Complete inverse paradigm Each data may appear multiple times. Usually, it is the mutual collection of paradigm and inverse paradigm.

The cache table represents a table that stores data that can be simply fetched from other tables in the schema, but is slower each time, and therefore is stored in the cache table.

A summary table is a table that holds aggregated data from a group by statement.

A shadow table creates an identical table, changes it, and then copies the data to the table. The original table is then rename as a new table, and the shadow table rename the current table.

Materialized views: By pre-calculating and saving more time-consuming results such as table links or aggregation, you can avoid these actions when you execute a query, and be aware of the application transparency. Materialized views are actually pre-computed and stored on disk tables that can be refreshed and updated through a variety of policies. It is made up of the following sections. Change data capture capabilities.

Counter table: Create a new table with the number of clicks saved. This table needs to be updated every time the site is clicked. A row lock may be added at the time of the update, so the concurrency is not good when you need to add a mutex when updating concurrently. To solve this problem, just add one line, select the slot, select a slot to update randomly via rand () *100, and when slot is 1 it is the initial problem, and when the slot is enlarged, it is better to update concurrency. At this point the number of Web sites only needs to sum the number of access times for all slots.

Alter table is a complex operation whose operation is to create a table table2 like Table1, then modify the table2 and then copy the number from table1 to table2. Very inefficient operation.

The default value of the table's Value property is stored in the child. frm file. You only need to modify the. frm file, which is what ALTER column does.

The way to speed up the ALTER TABLE hacker is to modify the. frm table.

OK, it's here today, let's see how to create a high-performance index tomorrow, and look at Python.

mysql-Reading notes 1