Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the role of MySQL compression table". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

MySQL table compression technology can reduce data storage space, reduce IO, improve throughput and improve the utilization of CPU.

Compressed table syntax:

If you use table compression technology, confirm innodb_file_per_table=1 and innodb_file_format=Barracuda first

SET GLOBAL innodb_file_per_table=1; SET GLOBAL innodb_file_format=Barracuda; CREATE TABLE T1 (wyzc INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

The KEY_BLOCK_SIZE parameter determines the size of the table compression page, which is specified when the table is created, which means that innodb will compress the page to the specified size. For example, if key_block_size=8 is set, the value of the compressed keyword blocksize size should be determined according to the length of the record. If the setting is too small, high probability compression failure may occur because one page cannot compress too many rows, resulting in page splitting. However, if it is set to 16k, the compression effect will not be very good.

The default value of KEY_BLOCK_SIZE is 8K

Depending on your load and data set, or specific configuration, you can consider the following factors:

1. There are more duplicate strings in the table

2. The data that has been compressed in the application is not suitable to be stored in the compressed table.

3. MySQL does not have a clear definition of how to compress the table, so the compressed index performance must be tested by like or order by after compression.

4. Table compression is not suitable for compression in application.

5. Workload on the table is a key factor, and if updates are mainly applied to externally stored non-indexed columns with long strings, the overhead of compression may be acceptable. If your load is Imax O bound instead of CPU bound, compression may improve overall performance

6. Compression can reduce IO by consuming CPU. If IO is a relatively scarce resource, it will get better results.

7. The size of the compressed Page should be larger than that of the record, otherwise it may cause a large number of compression failures. Generally speaking, key_block_size=8 is a relatively safe setting.

This is the end of the content of "what is the function of MySQL compression table". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.