MySQL Experience in optimization

1. Start from database structure 1. The definition of a field type follows the following rules: 1. Select the minimum field length of 2. Priority use of fixed-length type 3. As far as possible, define "not NULL" 4. Avoid the use of "Zerofill" 5 in numeric fields. If the data to be stored is a string, and the value may be known and limited, use Enum or set 2 preferentially. Optimization of indexes is critical (if there are no special instructions below, refer to query-intensive situations) (http://www.002pc.com)

1. The smaller the length of the indexed field, the higher the efficiency of the index is 2. The less the value is duplicated in the field being indexed, the higher the efficiency of the index is 3. In a query statement, if you use the "group" clause, establish a multiple-field index of 4 based on the order in which the fields appear. In a query statement, if "distinct" is used, a multiple-field index of 5 is established according to the order in which the fields appear. In the WHERE clause, a multiple-field index of 6 is established in the order in which the fields appear, when the "and" conditions for multiple different fields in the same table appear. In the WHERE clause, a single field index 7 is established for fields with the fewest duplicate values when an OR condition occurs for multiple different fields in the same table. When making an internal/external connection query, index 8 to connection fields. The "unique" index for the primary key is meaningless, do not use 9. The indexed field uses the Not NULL property 10 as much as possible. For write-intensive tables, minimize indexing, especially "multiple-field index" and "unique" index 2. Optimization of query statements 1. A lot of "explain" query index usage, in order to find the best query statement writing and index setup Scenario 2. Careful with "SELECT *", query only select the Required field 3. When a query uses an index, the fewer index bars are traversed, the smaller the index field length, the higher the query efficiency (you can use "explain" to query index usage)

4. Avoid using the MySQL function to process the results of the query, these processing to the client program is responsible for 5. When using "limit", try to make the "limit" part in the front of the entire result set so that the query is faster and has a lower system resource overhead of 6. When you use the and criteria for multiple fields in a WHERE clause, each field appears in the order that it corresponds to the order in the Multiple-field index 7. When you use "like" in the "where" clause, index 8 is used only if the wildcard character does not present the leftmost edge of the condition. In MySQL version 4.1 above, avoid using subqueries, try to use "internal/external connection" to achieve this feature 9. Reduce the use of functions and, if possible, replace 10 with simple expressions. It is more efficient to avoid the "or" Criteria query for different fields in the WHERE clause, and to split the query into multiple single fields.

Attached: Mysql Field length Description table

Mysql Field Length Description
TINYINT	1 bytes	SMALLINT	2 bytes
Mediumint	3 bytes	INTEGER:	4 bytes
BIGINT	8 bytes	DOUBLE	8 bytes
FLOAT (X)	X <=24:4 bytes X > 24:8 bytes	DECIMAL (M,D)	m<d:d+2 bytes M>=d:m bytes
DATE	3 bytes	Datetime	8 bytes
TIMESTAMP	4 bytes	Time	4 bytes
Year	1 bytes
CHAR (M)	M bytes	VARCHAR (M)	Value length + 1 bytes
Tinyblob	Value length + 1 bytes	Tinytext	Value length + 1 bytes
Blob	Value length + 2 bytes	TEXT	Value length + 2 bytes
Mediumblob	Value length + 3 bytes	Mediumtext	Value length + 3 bytes
Longblob	Value length + 4 bytes	Longtext	Value length + 4 bytes
Enum	1 or 2 bytes Depends on the number of enumerated values	SET	1,2,3,4, 8 Depending on the number of members