MySQL optimization strategy (recommended)

In summary:

1. 1. Consider performance when designing the database and creating tables
2. 2. SQL writing needs to be optimized
3. 3. Partition, table, and database

When designing a table:

1. 1. Avoid null values ​​in fields. Null values ​​are difficult to query and optimize and take up extra index space. It is recommended to use the default number 0 instead of null.
2. 2. Try to use INT instead of BIGINT. If it is non-negative, add UNSIGNED (this will double the value capacity). Of course, it is better to use TINYINT, SMALLINT, or MEDIUM_INT.
3. 3. Use enumeration or integer instead of string type
4. 4. Try to use TIMESTAMP instead of DATETIME
5. 5. Do not have too many fields in a single table. It is recommended to limit the number of fields to 20.
6. 6. Use integer to store IP

index:

1. 1. The more indexes, the better. You need to create indexes based on the query. Consider creating indexes for the columns involved in the WHERE and ORDER BY commands. You can use EXPLAIN to check whether the index is used or the full table scan is performed.
2. 2. Avoid using NULL values ​​for fields in the WHERE clause, otherwise the engine will abandon the use of indexes and perform a full table scan.
3. 3. Fields with very sparse value distribution are not suitable for indexing, such as "gender" fields with only two or three values.
4. 4. Create only prefix index for character fields
5. 5. It is best not to use character fields as primary keys
6. 6. No foreign keys are needed, constraints are guaranteed by the program
7. 7. Try not to use UNIQUE, and let the program ensure the constraints
8. 8. When using multi-column indexes, keep the order and query conditions consistent and delete unnecessary single-column indexes

Summary: Use the appropriate data type and choose the appropriate index

The writing of sql needs to be optimized:

Use limit to limit the records in the query results

Avoid select * and list the fields you need to search for.

Use joins instead of subqueries

Split large delete or insert statements

You can find out the slow SQL by turning on the slow query log

No column operations: SELECT id WHERE age + 1 = 10. Any operation on the column will result in a table scan, including database tutorial functions, calculation expressions, etc. When querying, try to move the operation to the right of the equal sign.

The SQL statement should be as simple as possible: one SQL statement can only be executed on one CPU; large statements should be split into small statements to reduce the lock time; one large SQL statement can block the entire database

Rewrite OR to IN: OR has an efficiency of n, while IN has an efficiency of log(n). The number of INs is recommended to be controlled within 200.

No functions or triggers are needed to implement

Avoid %xxx-style queries

Use JOIN sparingly

Use the same type for comparison, such as '123' and '123', 123 and 123

Try to avoid using the != or <> operators in the WHERE clause, otherwise the engine will abandon the index and perform a full table scan.

For consecutive values, use BETWEEN instead of IN: SELECT id FROM t WHERE num BETWEEN 1 AND 5

Don't use the entire table for list data. Use LIMIT to split the data into pages. The number of pages should not be too large.

engine:

MyISAM

Row locks are not supported. All tables that need to be read are locked when reading, and exclusive locks are added to the tables when writing.

No support for transactions

Foreign keys are not supported

Does not support safe recovery after a crash

Support inserting new records into the table while the table is being read and queried

Supports indexing of the first 500 characters of BLOB and TEXT, and full-text indexing

Supports delayed update of indexes, greatly improving write performance

For tables that will not be modified, compression is supported to greatly reduce disk space usage

InnoDB

Support row locks and use MVCC to support high concurrency

Support Affairs

Support foreign keys

Supports safe recovery after crash

No full-text indexing support

In general, MyISAM is suitable for SELECT-intensive tables, while InnoDB is suitable for INSERT- and UPDATE-intensive tables.

Partition, table, and database (read-write separation)

The above is a detailed explanation and integration of MySQL optimization strategies introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!