MySQL Data Optimization Summary-query Memo

First, optimize the classification

Second, sample test data

Refer to MySQL's official Sakina database.

Third, use the MySQL slow query log to monitor the efficiency problem of SQL

The first one, turn on the slow query log. The second, slow query log storage location. The third one, which is not indexed, is also logged in the slow query log. Fourth, queries that exceed 1 seconds are recorded in the slow query log (typically set to 100ms).

3.1. Parsing slow query log files

3.1.1 Tail command

Tail-50/home/mysql/sql_log/mysql_slow.log, enter the trailing content in the file, which is the 50 rows of data at the end.

Let's take a look at one of them, as shown in.

Query_time, the query takes time (in seconds); Lock_time, the lock table. Rows_sent, the number of rows to send the request, and rows_examined, the number of rows to which the query data results in the scan table.

3.1.2 Official Mysqldumpslow Tool

Mysqldumpslow, which is installed by default with MySQL.

Mysqldumpslow-h, you can query the commands supported by the tool.

Mysqldumpslow-s r-t 10/var/lib/mysql/localhost-slow.log | More, return the result as shown.

3.1.3 Pt-query-digest Tools

More information than the Mysqldumpslow feedback.

Pg-query-digest--help View Help to view the use command.

Pg-query-digest/var/lib/mysql/localhost-slow.log, the query results are as follows.

The first part

Part II

Part III

Iv. how to find problematic SQL by slow-scan logs

V. Query and analyze the SQL execution plan through explain

Const constant Lookup, in general, for primary key and unique index; Eq_reg, general primary key or unique index range lookup; ref, common in connection queries; Range, for index range lookup;

Index, scan for index, all, table scan.

Vi. optimization of Count () and Max ()

1. Max () optimization

Building an index on payment_date

Query results after index is built

As you can see, the maximum date can be queried directly through the index structure. Overwriting an index means that the query results can be obtained entirely by index.

2. Count () optimization

COUNT (*) contains a null value, and COUNT (ID) does not contain

Error wording:

Correct wording:

Vii. optimization of sub-queries

One-to-many subqueries, note dinstinct

Viii. optimization of GROUP BY

Before optimization

Before optimization

After optimization

After optimization

Optimized to reduce IO, increase efficiency and conserve server resources

Flexible use of subqueries and connection queries

IX. Optimization of limit query

Disadvantage: The more the page limit, the more the number of scan lines, the greater the IO operation

Cons: ID continuous. Primary key continues to grow, paging queries are faster

X. How to choose the right column to index

If you are overwriting an index, you can get the data directly from the index structure, which is the quickest; the smaller the index field, the larger the amount of data that is fetched per IO for the database data store in pages.

View the degree of dispersion by using SELECT COUNT (dinstinct customer_id). Columns with large dispersion can be selectively higher.

Xi. methods of index-optimized SQL

Indexes raise queries, but they affect inset,update,delete.

4, database table structure optimization

4.1 Choosing the right data type

Time type, timestamps and int occupy bytes, and NOT null requires extra field storage.

Bigint8 bytes, Varchar15 bytes

Normalization optimization of 4.2 database

4.4 Vertical splitting of tables

For example, splitting the contents of a news sheet into a single table

4.5 Horizontal split of the table

Front desk with split table, background with summary table

Summary of the very casual, purely convenient to view knowledge points

MySQL Data Optimization Summary-query Memo