Suggestions on reducing MySQL to save disk space

In our work, we often meet some customers whose TPS\QPS is not too high, but takes up a lot of disk. Once the single instance space is too large, the overhead of memory, network, CPU and backup will increase accordingly. Maybe it is because the space is not satisfied that we have to expand the capacity. The following methods are for your reference. If the shoe fits, wear it.

1, table structure design

Does the 1) character set follow the principle of minimization? If you know Latin, you don't need gbk. If you can use gbk, you don't need utf8)

2) Is the index abused? (Fields that are completely unused for indexing, fields that are not suitable for indexing, repeated indexing, or not making good use of prefix indexing, etc. )

3) Are there too many redundant fields? (Unused or redundant fields in each table)

4) The field type is incorrect? (If you can use 1 byte, you have to use several bytes, such as enumeration class and status class. )

5) Use a long field or a combination of several fields as the primary key? (The primary key should be added by mysql)

Specific examples are as follows:

Create table ` class_meta (

Class _ name' varchar (128) notnull annotation' class name',

Class _ desc' varchar(2048) describes the default'' annotation'' class',

Class _ status `char (20) default' test 1' Notes' test1,test2',

Primary key (` class_name),

Unique key' cm _ cn _ uk' ('class _ name'),

Key' cm _ CD _ ind' ('class _ desc' (767)),

Keywords' cm _ cs _ ind' ('class _ status'),

Keywords ` cm_cdcn_ind` (`class_desc`(767), ` class_name)

) engine = innodb default charset = Latin1comment =' meta information';

Through the table structure above, we can see that the following places are not suitable.

1, the primary key and unique index are obviously duplicated, and the index cm_cd_ind and the index cm_cdcn_ind are duplicated (this often happens, please pay attention).

2. If the two states are evenly distributed, cm_cs_ind is obviously not suitable for indexing.

3. Because class_desc is descriptive, it is not suitable for indexing.

4. Self-increment is the best primary key, which can reduce the space of the whole table.

5. Obviously, the class_status column can be stored in tinyint, which can save 19 bytes.

2. On the stored content

1) Do you store big data such as pictures, videos and music in the table? (It is best to keep only the path in the table, not the actual file content)

3. Data retention

1) Is there any expired data that has not been deleted? (Clear invalid data in time or archive history)

4. Post maintenance

1) Whether to maintain frequently deleted tables (optimized tables)

Suggestion:

1. In the case of low performance requirements (low concurrency), you can consider using compressed tables. Generally, the compression ratio is between 30% and 70%, and the income is very considerable.

2. Regularly optimize the frequently deleted tables to reduce the fragmentation in the tables. Improve the performance of query and writing.

3. In the design of table structure, we must carry forward the spirit of "haggle over every ounce", which can be expressed by 1 byte instead of 2 bytes.

4. Use as few large fields as possible.

Ps: When developing the evaluation table structure, people often joke that DBA is too stingy and not atmospheric at all. When the amount of data is small, you may not think so, but when your data reaches T or P, even a few extra bytes will be considerable. Let's make a simple calculation. If we reduce the field of a table with 500 million records from 100 bytes to 60 bytes (which should be easy to do), then we will save about 18G without counting indexes.

Articles you may be interested in: How does SqlServer get the information of processor (CPU), memory, disk and operating system through SQL statements? The solution that mysql can't start due to insufficient disk space under ubuntu? MyISAM (table damaged, inaccessible, insufficient disk space): How to close Mysql log to protect disk space under lnmp? Mysql InnoDB method releases disk space after deleting data. sql statements in Mysql query the size of disk space occupied by all databases and the size of all tables in a single database. mssql monitors disk space alarm implementation method SQL Server obtains disk space usage.