MySQL database optimization knowledge

I have encountered such a question several times, but I cannot answer the question exactly each time. The answer is as follows: (forget to add)

1. Database Design
Try to occupy less disk space for database design.

1). Use smaller integer types as much as possible. (mediumint is more suitable than int ).
2). Try to define the field as not null unless this field needs to be null.
3) If variable length fields are not used, such as varchar, a fixed-size record format is used, such as char.
4). The primary index of the table should be as short as possible. In this case, each record has a name Mark and is more efficient.
5). Create only the required indexes. Indexing is conducive to record retrieval, but is not conducive to quick record storage. If you always need to search on the combined fields of the table, create an index on these fields. The first part of the index must be the most commonly used field. If you need to use many fields, you should first copy these fields to make the index more compressed.
6). All data must be processed before being saved to the database.
7). All fields must have default values.
8). In some cases, splitting a frequently scanned table into two tables is much faster. When scanning a dynamic table to obtain relevant records, it may use a smaller static table.

2. usage of the system
1) Try to use persistent connections.
2) explain complex SQL statements.
3) If two joined tables are compared, the fields to be compared must be of the same type and length.
4) the LIMIT statement should be followed by order by or distinct as much as possible. This can avoid a full table scan.
5). If you want to clear all records of a table, we recommend that you use truncate table tablename instead of delete from tablename.
6). When the store procedure or user function can be used.
7). Multiple record insertion formats are used in an insert statement, and load data infile is used to import a large amount of data, which is much faster than the pure indert.
8). optimize table is often used to sort fragments.
9). Data of the date type should be saved as quickly as possible in the unsigned int type if compared frequently.

3. system bottlenecks
1). Disk search.
In parallel search, data is stored separately in multiple disks to accelerate the search time.

2). disk read/write (IO)
Data can be read concurrently from multiple media.

3). CPU cycle
Data is stored in the primary memory. In this way, you need to increase the number of CPUs to process the data.

4). memory bandwidth
When the CPU needs to store more data in the CPU cache, the memory bandwidth becomes a bottleneck.