Mysql optimization

1. In general, changing a nullable column to NOT NULL will NOT improve performance, but if you plan to create indexes on a column, you should set the column to NOT NULL. 2. It is useless to specify the width of an integer such as INT(11). INT uses 32 bits (4 bytes) of storage space, so its range of representation is already defined, so INT(1) and INT(20) are the same for storage and computation. 3.UNSIGNED indicates that negative values are not allowed and can roughly double the upper limit of positive numbers. For example, TINYINT stores in the range of -128 to 127, while UNSIGNED TINYINT stores in the range of 0-255. 4. In general, there is not much need to use DECIMAL data types. You can still use BIGINT even when you need to store financial data. For example, to be accurate to 1/10,000, you can multiply the data by a million and store it using BIGINT. This avoids the problems of inaccurate floating-point calculations and costly accurate DECIMAL calculations. 5. TIMESTAMP uses 4 bytes and DATETIME uses 8 bytes. Thus, TIMESTAMP can only represent the years 1970-2038, a much smaller range than DATETIME, and the value of TIMESTAMP varies from time zone to time zone. 6. In most cases, there is no need to use enumerated types. One drawback is that the list of enumerated strings is fixed, and adding and deleting strings (the enumeration option) must use ALTER TABLE (if you only append elements to the end of the list, you do not need to rebuild the TABLE). 7. Don’t have too many columns in your schema. The reason is that the storage engine API works by copying data between the server layer and the storage engine layer in row buffer format, and then decoding the cached content into columns at the server layer, a very expensive conversion process. If there are too many columns and too few columns are actually used, the CPU usage can be too high. MySQL > ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE Especially if memory is low and the table is large and has large indexes, it takes longer. Of course, there are some strange techniques can solve this problem, interested to consult.

B. Create high-performance indexes index categories

• PRIMARY KEY: PRIMARY KEY, which means that the index value must be unique and cannot be NULL.
• UNIQUE: The value used to create the index must be UNIQUE (which may occur multiple times in addition to NULL).
• INDEX: indicates a common INDEX. The INDEX value can appear multiple times.
• FULLTEXT: Used for full-text indexing.

Considerations for using indexes:

1. The primary and foreign keys of a table must have indexes
2. Fields with where sentences often appear
3. Columns that are frequently queried
4. Fields with high selectivity
5. Fields that are often sorted by users
6. The data is over 300
7. You are advised not to have more than 5 indexes in a table

C. Select a proper data type and storage engine based on service requirements

Description and difference of engine

ISAM engine: Reads data quickly and does not occupy a large amount of memory and storage resources. But ISAM does not support transactions, does not support foreign keys, is not fault tolerant, and does not support indexes. If the hard drive crashes, data files cannot be recovered, so if ISAM is used for critical tasks, real-time data must be backed up frequently.

MyISAM engine: MyISAM emphasizes fast read operations. (1) Do a lot of count calculations; (2) Insert is not frequent, query is very frequent; (3) No transaction. Static MyISAM: The server will automatically select this table type if the columns in the database are pre-fixed in length. (1) Each record in the table occupies the same space, so the efficiency of access and update is very high. (2) It is also easier to restore data when it is damaged. Dynamic MyISAM: If a VARCHAR, XXXtex, or xxxBLOB field appears in a data table, the server automatically selects this table type. Compared to static MyISAM, this table has less storage space. Compressed MyISAM: This type of table further reduces the storage footprint, but the table cannot be modified after being compressed. Memory (heap) fits: The heap allows temporary tables that reside only in memory. Because data is only stored in memory, data access is faster than ISAM and MYISAM. Index with HASH (remember, delete tables when you’re done with them)

InnoDB engine: Error safe storage engine with commit, rollback, and crash recovery capabilities. (1) High reliability requirements, or transaction requirements; (2) table update and query are quite frequent, and the opportunity to lock the table is relatively large. InnoDB is designed for maximum performance when handling large amounts of data. Its CPU efficiency is probably unmatched by any other disk-based relational database engine. The only engine that supports foreign keys.

Archive engine: Logging and aggregation analysis aspects. Archive does not support indexes and only supports INSERT and SELECT statements.