Several suggestions for reducing MySQL to save disk space and reducing mysql

Several suggestions for reducing MySQL to save disk space and reducing mysql

In our work, we often encounter some customers with low TPS \ QPS, but the disk usage is very large. Once a single instance has too much space, such as memory, network, CPU, and backup will increase the corresponding overhead. It may be because the space is not enough that we have to resize. The following methods are provided for your reference. If yes, you will be encouraged.
1. Table Structure Design
1) Does the character set follow the minimization principle? (Gbk is not required if you can use latin. Utf8 is not required if you can use gbk)
2) Is there any misuse of indexes? (Fields that are not used at all to create indexes, fields that are not suitable for creating indexes, duplicate indexes, or prefix indexes cannot be well used)
3) Are there too many redundant fields? (Unnecessary in each table or too many redundant fields)
4) is the field type incorrect? (A few bytes are required for a single byte, such as enumeration and status)
5) How many long fields or fields can be combined as the primary key? (Mysql auto-increment is recommended for primary keys)
The example is as follows:

Create table 'class _ meta' ('class _ name' varchar (128) not null comment 'class name', 'class _ desc' varchar (2048) default ''comment' class description ', 'class _ status' char (20) default 'test1' COMMENT 'test1, test2', primary key ('class _ name '), unique key 'cm _ cn_uk '('class _ name'), KEY 'cm _ cd_ind' ('class _ dessc '(767 )), KEY 'cm _ cs_ind '('class _ status'), KEY 'cm _ cdcn_ind' ('class _ desc' (767), 'class _ name ')) ENGINE = InnoDB default charset = latin1 COMMENT = 'meta information ';

The above table structure shows that the following is not suitable:
1. The primary key and unique index are obviously repeated, and the index cm_cd_ind and the index cm_cdcn_ind are repeated (this situation often occurs and you should pay attention to it)
2. If the two statuses are evenly distributed, cm_cs_ind is obviously not suitable for index creation.
3. Because class_desc is descriptive, it is not suitable for index creation.
4. It is best to use auto-increment as the primary key to reduce the space of the entire table.
5. The class_status column can be obviously stored using tinyint, saving 19 bytes

2. Storage content
1) are images, videos, music, and other big data stored in tables? (It is best to keep the path instead of the actual file content in the table)
3. Data Retention
1) Is there any expired data that has not been deleted? (Promptly clear or archive invalid data)
4. Post-Maintenance
1) Whether to maintain frequently deleted tables (optimize table)

Suggestion:
1. In cases with low performance requirements (with low concurrency), you can consider using a compressed table. Generally, the compression rate is between 30%-70%, and the benefits are considerable.
2. Optimize tables that are frequently deleted to reduce table fragments. Improves query and write performance.
3. In terms of table structure design, we must carry forward the spirit of "overhead" and use 1 byte to indicate that we do not need 2 bytes.
4. Minimize the use of large fields.

Ps: When you often review the table structure with development, you may often be told that DBAs are too stingy. When the data volume is small, you may not think of it, but when your data level is T or P, Even if you save several bytes, It is very impressive. I will give you a simple account, if we reduce the number of fields in a 0.5 billion-record table from 100 bytes to 60 bytes (it should be easy to do so), it will save about 18 GB of space for indexing.