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

This article mainly introduces the example analysis of Mysql optimization strategy, the article is very detailed, has a certain reference value, interested friends must read it!

In general:

1. Performance should be considered in database design and table creation.

2. The compilation of sql needs to pay attention to optimization.

3. Partition, subtable, sublibrary

When designing a table:

1. The field avoids null values, which are difficult to query and optimize and take up extra index space. It is recommended that the default number 0 replaces null.

2. Try to use INT instead of BIGINT. If it is non-negative, add UNSIGNED (so the numerical capacity will be doubled). Of course, it is better to use TINYINT, SMALLINT and MEDIUM_INT.

3. Use enumerations or integers instead of string types

4. Try to use TIMESTAMP instead of DATETIME

5. Do not have too many fields in a single table. It is recommended that it be less than 20.

6. Use integers to save IP

Index:

1. The more indexes, the better. To create targeted indexes according to the query, consider indexing the columns involved in the WHERE and ORDER BY commands. You can check whether indexes or full table scans are used according to EXPLAIN.

2. Try to avoid judging the null value of the field in the WHERE clause, otherwise it will cause the engine to give up using the index and do a full table scan.

3. Fields with sparse value distribution are not suitable for indexing, such as "gender", which has only two or three values.

4. Character fields are indexed with prefixes only

5. Character fields are best not to be primary keys.

6. No foreign keys are used, and constraints are guaranteed by the program

7. Try not to use UNIQUE, and the constraint is guaranteed by the program

8. when using multi-column indexes, the order and query conditions are the same, and unnecessary single-column indexes are deleted.

Summary: use the appropriate data type and select the appropriate index

The writing of sql requires attention to optimization:

Use limit to limit the records of query results

Avoid select * and list the fields you need to find

Use join to replace subqueries

Split large delete or insert statements

You can find the slower SQL by opening the slow query log

Do not do column operations: SELECT id WHERE age + 1 = 10, any column operation will result in a table scan, including database tutorial functions, evaluation expressions, etc. When querying, move the operation to the right of the equal sign as much as possible

Sql statements are as simple as possible: a sql can only operate on one cpu; large statements break down small statements to reduce lock time; a large sql can block the entire library

The efficiency of rewriting OR to IN:OR is n, the efficiency of IN is log (n), and the number of in is recommended to be less than 200.

Without functions and triggers, implemented in the application

Avoid% xxx type query

Use less JOIN

Compare with the same type, such as the ratio of '123' to '123', 123 to 123

Try to avoid using the! = or operator in the WHERE clause, otherwise the engine will give up using the index and do a full table scan

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

List data do not take the whole table, use LIMIT to page, the number of each page is not too large

Engine:

MyISAM

Row locks are not supported. Locks are applied to all tables that need to be read when reading and exclusive locks are added to tables when writing

Transactions are not supported

Foreign keys are not supported

Security recovery after crash is not supported

While the table has a read query, new records can be inserted into the table.

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

Support for delayed updating of indexes, greatly improving write performance

For tables that will not be modified, compressed tables are supported to greatly reduce disk space consumption

InnoDB

Supports row locks and uses MVCC to support high concurrency

Support transaction

Foreign keys are supported

Support for security recovery after a crash

Full-text indexing is not supported

Generally speaking, MyISAM is suitable for SELECT-intensive tables, while InnoDB is suitable for INSERT and UPDATE-intensive tables.

Partition, sub-table, sub-library (read-write separation)

The above is all the content of the article "sample Analysis of Mysql Optimization Strategy". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.