Considerations for MySQL memory usage and Optimization

Considerations for MySQL memory usage and Optimization
1. read data from memory in microseconds. Read data from a disk in milliseconds. They differ by an order of magnitude. So to optimize the database, the first thing to do is to optimize io.
2. The index of the myisam table is cached in the memory area size set by key_buffer_size [global. Because myisam only caches indexes in the memory and does not cache data in the memory, if the memory permits this parameter, make sure it can accommodate all indexes of myisam to improve performance. In addition, in myisam tables, try to restrict all query conditions to indexes, so that the cache can improve the search efficiency for us.
3. bulk_insert_buffer_size [thread] is only used in myisam and used to temporarily cache data during data insertion. When we use the following Write statement, we will use this memory area to help batch write data files:
Insert... select...
Insert into... values...
Load data infile......
4. innodb_buffer_pool_size [global] When we use the innodb engine, this parameter may be the most critical parameter that affects performance. It is used to set the index of the cache innodb and the memory size of the data block.
Simply put, when we operate the innodb table, all the data returned and all the indexes used in the process of searching for the data will go through this memory block.
5. innodb_additional_mem_pool_size [global] sets the innodb Storage engine to store data dictionary information and the memory size of some internal data structures. Therefore, when a mysql instance
When there are many database objects (such as many tables), you need to adjust the size of this parameter to ensure that all data is in the memory to ensure efficiency. It is easy to know whether the memory of this parameter is sufficient. Because when I was a child
Mysql records data from warning to error log.
6. innodb_log_buffer_size [global] memory used by innodb. When writing transaction logs, innodb writes the logs to the cache before writing them to the logfile to improve performance.
7. innodb_max_dirty_pages_pct [global] is used to control the buffer pool in innodb. You do not need to write the dirty page of the data file (it has been modified but has not been written to the dirty data of the data file).
The larger the value, the write operations from memory to disk will be reduced. Therefore, disk I/O can be reduced to a certain extent. However, if the database crash value is large, the restart may take a long time. Because
A large amount of transaction data needs to be recovered from the log file and written to the data file. At the same time, a large proportion value will also cause the flush operation to write too much data when the limit is reached, resulting in dramatic performance fluctuations.
8. When we want to retrieve most of the data from the entire table, index scanning is not necessarily better than full table scanning.
9. mysql is a row-based database, while data reading is page-based. Each page contains rows. If the data volume of each row decreases, the number of rows stored in each page increases. I/O can retrieve more rows.
In turn, when processing the same data, the page will be reduced. That is, the decrease in io count. Directly improve performance. In addition, because our memory size is limited, the number of rows on each page increases, which is equal to the increase.
The cache data volume of each data block can also increase the hit rate.
10. We cannot change what data we want to store, but we can spend some time thinking about how to store data.
1) number type. Do not use the double type. Besides occupying a large amount of space, there are also precision problems. Similarly, do not use decimal for decimal with a fixed precision. We recommend that you multiply it by a fixed multiple and convert it to an integer for storage.
It saves storage space and does not require any additional maintenance costs. We recommend that you separate tinyint/int/bigint for integer storage. There is a gap between the storage space and the storage space.
2) character type. Do not use the text type. It is less efficient than char and varchar. The char type is recommended for fixed length fields. Use varchar to change the length. Varchar cannot be arbitrarily given a large length. Because of the different length ranges, mysql may have different processing methods. In this blog article, we will introduce the varchar processing method. If varchar (1000) is declared, when mysql stores the data on the disk, assuming that the data length is 45, the disk occupies about 45 space. However, when the data is in the memory, it still takes up 1000 space. A lot of waste.
3) event type. Use timestamp whenever possible. The storage space is only half of the datetime type. We recommend that you use the date type for data that needs to be accurate to a specific day. Because it requires three bytes for storage. Less than timestamp.
Int is not recommended to store a unix timestamp, which is not intuitive and will not bring any benefits.
4) redundant fields in the table. For example, put the abstract of an article together with the article information table, rather than the detailed content table of the article.