Several suggestions for reducing Mysql to conserve disk space _mysql

We often encounter some customers at work Tps\qps are not too high, but disk consumption is very large, once the single instance space is too large, like memory, network, CPU and backup will increase the corresponding cost. Perhaps just because the space is not satisfied that we have to expand, the following methods for you to reference. Chance Wuzegamin.
1, the table structure design
1 Does the character set follow the principle of minimization? (You can use Latin without GBK.) You can use GBK without UTF8.
2 is there any abuse on the index? (keywords that are not used at all Jianjian indexes, Jianjian indexes for indexed characters, duplicate indexing, or do not use prefix indexing well)
3 Are there too many redundant fields? (Unused in each table or too many field redundancy)
4 Incorrect field type? (can be used 1 bytes to use a few bytes, such as enumeration class, state analogy is more common)
5 A long field or a combination of several fields as the primary key? (The primary key is best to use MySQL for self increase)
Specific examples are as follows:

CREATE TABLE ' Class_meta ' (
' class_name ' varchar (128) Not NULL COMMENT ' class name ',
' class_desc ' varchar (2048) Default ' Description of ' COMMENT ' class ',
' class_status ' char ' test1 ' COMMENT ' test1,test2 ',
PRIMARY KEY (' class_name '),
UNIQUE KEY ' cm_cn_uk ' (' class_name '),
Key ' Cm_cd_ind ' (' Class_desc ' (767)),
key ' Cm_cs_ind ' (' Class_status '),
key ' Cm_cdcn_ind ' (' Class_desc ' (767), ' class_name ')
engine=innodb DEFAULT charset=latin1 comment= ' meta information ';

By the above table structure can see the following place is not appropriate
1, primary key and unique index obviously duplicate, index Cm_cd_ind and Index Cm_cdcn_ind index Duplicate (this kind of situation often appears, everybody notice)
2, Cm_cs_ind if two state distribution uniformity also obviously unsuitable index
3, Class_desc due to the description of the nature of the index is not suitable
4, the best to be self-added as the primary key, can reduce the whole table space
5, class_status column can be tinyint to save, you can save 19 bytes

2, storage content on
1 is the picture, video, music and other large data stored in the table? (It's best to keep only the path rather than the actual file content)
3, Data retention on
1 Do you have data that has expired without being deleted? (Timely cleanup or historical archiving of invalid data)
4. Post-Maintenance
1 whether the frequently deleted tables are maintained (optimize table)

Suggestions:
1, in the case of low performance requirements (concurrency is not too high), you can consider the use of compressed tables. The general compression rate between 30%-70%, the benefits are very considerable.
2, to delete very frequent tables to be optimized periodically to reduce the fragmentation of the table. Improve query, write performance.
3, in the table structure design, we must carry forward the spirit of "preoccupied", can be expressed in 1 bytes of the resolute do not use 2 bytes.
4, as far as possible to reduce the use of large segments.

PS: Often in the development of the review table structure, often be jokes that the DBA is too stingy, not a little gas. When the amount of data is small, people may not feel that but when your data level to T or P, even a few bytes are very considerable, to give you a simple account, if we will be a 500 million records of the table, the field from 100 bytes to 60 bytes (it should be easy to do), Then not counting the index will save about 18G of space.