Summary:

1) mysql query optimization

Tables with a large amount of search queries are generally designed as static tables, which exchange space for time

 

 

Row of Mysql_ Format - jiangxiaobo - blog Park https://www.cnblogs.com/jiangxiaobo/p/10846694.html

#Row of Mysql_ Format (fixed and dynamic)
In mysql, if a table does not contain varchar, text and its deformed, blob and its deformed fields, the table is also called a static table, that is, the row of the table_ Format is fixed, which means that each record occupies the same bytes. It has the advantages of fast reading and the disadvantages of wasting additional space.

If a table contains varchar, text and its deformation, blob and its deformation fields, the table is also called a dynamic table, that is, the row of the table_ Format is dynamic, which means that the bytes occupied by each record are dynamic. Its advantages save space and disadvantages increase the time cost of reading.
Therefore, tables with a large amount of search queries are generally designed as static tables by exchanging space for time.

ROW_ The values of format are as follows:

DEFAULT

FIXED

DYNAMIC

COMPRESSED

REDUNDANT

COMPACT

 

MySQL row_ A case triggered by format - markqian86 - C + + blog http://www.cppblog.com/markqian86/archive/2019/08/14/216685.aspx

front768Bytes, anddynamicIn the format, the overflow column is only stored in the front20Byte. Once a row overflow occurs, dynamicIn fact, a pointer is stored, and the data is placed in the overflow page,dynamicRepresents a long field(Row overflow occurred)completely off-page Storage.

Row_ A case where format throws an exception:
There was a problem in MYSQL production a few days ago. When entering data, the whole line of data could not be recorded, and the following error was reported:

Cause:java.sql.SQLException: com.taobao.tddl.common.exception.TddlException:java.sql.SQLException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:Row size too large (> 8126). Changing some columns to TEXT or BLOB or usingROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format,BLOB prefix of 768 bytes is stored inline.; nested exception iscom.ibatis.common.jdbc.exception.NestedSQLException:  

This table is a product introduction detail table. There are more than 20 TEXT fields. It happens to encounter a product, and the data entered in each field is very long,
There is a limitation in mysql. A page (where pagesize is 16K) must store at least two rows, that is, the storage length of each row must be less than or equal to 8192. With so many TEXT fields, a row cannot be saved, that is, overflow will occur. In other words, overflow will occur, and each column will still store the first 768 bytes (the row_formart of the table is compact), More than one field still exceeds 8192, so an error is reported and cannot be inserted.
Finally, the row of the table_ Changing format to dynamic can be solved. alter table … row_format=dynamic;

Therefore, if you encounter many large TEXT or VARCHAR fields in some tables and it is difficult to disassemble, you may need to consider the length of the overflow column. If the length of the overflow column is still too large, you should look at the row of the table_ format :

show table status like '%xxx%'\G  

If necessary, it needs to be set to dynamic, such as:

create table test(id int,name text,...... ) row_format=dynamic; or alter table test row_format=dynamic;

 

 

 

 

front768Bytes, anddynamicIn the format, the overflow column is only stored in the front20Byte. Once a row overflow occurs, dynamicIn fact, a pointer is stored, and the data is placed in the overflow page,dynamicRepresents a long field(Row overflow occurred)completely off-page Storage.

Row_ A case where format throws an exception:
There was a problem in MYSQL production a few days ago. When entering data, the whole line of data could not be recorded, and the following error was reported:

Cause:java.sql.SQLException: com.taobao.tddl.common.exception.TddlException:java.sql.SQLException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:Row size too large (> 8126). Changing some columns to TEXT or BLOB or usingROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format,BLOB prefix of 768 bytes is stored inline.; nested exception iscom.ibatis.common.jdbc.exception.NestedSQLException:  

This table is a product introduction detail table. There are more than 20 TEXT fields. It happens to encounter a product, and the data entered in each field is very long,
There is a limitation in mysql. A page (where pagesize is 16K) must store at least two rows, that is, the storage length of each row must be less than or equal to 8192. With so many TEXT fields, a row cannot be saved, that is, overflow will occur. In other words, overflow will occur, and each column will still store the first 768 bytes (the row_formart of the table is compact), More than one field still exceeds 8192, so an error is reported and cannot be inserted.
Finally, the row of the table_ Changing format to dynamic can be solved. alter table … row_format=dynamic;

Therefore, if you encounter many large TEXT or VARCHAR fields in some tables and it is difficult to disassemble, you may need to consider the length of the overflow column. If the length of the overflow column is still too large, you should look at the row of the table_ format :

show table status like '%xxx%'\G  

If necessary, it needs to be set to dynamic, such as:

create table test(id int,name text,...... ) row_format=dynamic; or alter table test row_format=dynamic;