MySQL Specification and optimization

First, the construction of the database to optimize the table

1. Core specification (recommended)

• Table Character Set selection UTF8 (the Expression field is set separately to another character set)
• The storage engine uses InnoDB
• Do not store pictures, files, etc. in the library
• Using variable-length strings (varchar)
• The amount of data per table is controlled below 5000W

2. Field naming specification (recommended)

• The library name, table name, field name, index name use lowercase letters, and the underscore is split
• Non-unique indexes are named according to "idx_ Field name [_ Field name]"
• The unique index is named according to "uniq_ Field name [_ Field name]" (Do not define the index name directly with the field name.) Prevent deletion of fields when indexes are deleted)

3. Field attribute rules (recommended)

• All fields are defined as NOT null (null decreases the index effect; The index generates extra space)
• • Use unsigned to store non-negative integers
• • Use timestamp storage time (the default value of this type can be used for query optimization)

4. Field type rules (recommended)

• Use tinyint instead of enum type
• As much as possible without text, BLOB type
• Convert a character to a number
• varchar (5) is better than varchar (10) when storing "ABCD"

5. Index rules (recommended)

• Select the self-increment column as the primary key
• No more than 5 single-table indexes, no more than 5 single indexed fields
• String can use prefix index, prefix length control 5-8 characters
• Do not index on low cardinality columns, such as: Gender, delete, publish
• Optimize to select Id,name,age without using SELECT * .....
• Do not perform mathematical operations, functions in indexed columns

6. SQL specification

• Avoid implicit conversions
• Avoid using stored procedures, triggers, functions
• Avoid mathematical operations
• Divide large SQL as much as possible

Second, establish efficient index

Purpose: Speed up the query, speed up the sorting, overwrite the index (only need to complete the query in the index, do not need to go back to the table)

1, PRIMARY key: and data stored together.

• Usually select the self-increment column as the primary key
• Advantages:
  • A sequential insertion, no data movement within the data page occurs (insert faster)
  • b data storage is more compact (faster query)
• Disadvantages:
  • More than 4 to 8 bytes of meaningless data

2, Level Two index: separate storage with data

• The secondary index is stored according to the corresponding relationship of the index column + primary key, and one more of each index will have a corresponding relationship. So the larger the number of indexes, the larger the footprint, the slower the insertion and deletion.

3. What kind of fields are suitable for indexing?

• First, you want to satisfy the query criteria for the main function.
• Second, it depends on how many unique values the field is.
• Unique value: SELECT COUNT (Distinct UID)/count (1) from table; The larger the value, the better the index effect.

Type: Recommended types for optimization

• • • system table has only one row
    • Const uses a primary key or a unique index
    Eq_ref a multi-table query, it matches 1 rows and takes advantage of a primary key or unique index
    • Ref matches more than one line, usually using a generic index (if it's a federated unique index, only 1 of them are the same type)
    • Ref_or_null, similar to ref, uses a null search in the condition

• • • All is not used in the index
    • When a type other than those listed above is present, indexes such as range, index, and so on have poor performance

Rows

• • • The number of rows affected by the query, the smaller the value, the better.

Extra　

• • • The details of the query, types include:
    • The using where, using index, using Filesort, etc. are all normal query procedures
    • When using temporary appears, it is necessary to optimize the SQL or index

Ii. optimization of SQL

• When multiple table queries are required, internal (outside) connection queries are not necessarily the best solution, and the proper use of subqueries can be a better choice.
• Change select * To some fields to reduce query time slightly
• The garbage index only affects the efficiency of insertions and deletions, and has less impact on query speed.
• Field uniqueness is too low and the index is inefficient.
• Field uniqueness is very high and the performance of the index is excellent.
• When the time range is large, the index is not used. As far as possible to make the time range of openings and closed, the interval is not too large, based on the amount of data and the earliest time to decide.

MySQL Specification and optimization