dried food! MySQL optimization principle analysis and optimization scheme summary, springcloud actual combat project

Posted by njdirt on Fri, 24 Dec 2021 17:07:54 +0100

Terminate the query in advance (for example, when Limit is used, the query will be terminated immediately after finding a satisfactory number of result sets)
Optimized sorting (in the old version, MySQL will use two transfer sorts, that is, first read the row pointer and the fields to be sorted, sort them in memory, and then read the data rows according to the sorting results. In the new version, it uses a single transfer sort, that is, read all the data rows at one time, and then sort them according to the given columns. For I/O-Intensive applications, the efficiency will be much higher.)

With the continuous development of MySQL, the optimization strategies used by the optimizer are also evolving. Here are only a few commonly used and easy to understand optimization strategies. Please refer to other optimization strategies by yourself.

Query execution engine

After the parsing and optimization phase is completed, MySQL will generate the corresponding execution plan, and the query execution engine will execute step by step according to the instructions given by the execution plan to obtain the results. Most of the operations in the whole execution process are completed by calling the interfaces implemented by the storage engine, which are called the handler API. Each table in the query process is represented by a handler instance. In fact, MySQL creates a handler instance for each table in the query optimization stage. The optimizer can obtain the relevant information of the table according to the interfaces of these instances, including all column names and index statistics of the table. The storage engine interface provides very rich functions, but there are only dozens of interfaces at the bottom. These interfaces complete most operations of a query like building blocks.

Return results to the client

The last stage of query execution is to return the results to the client. Even if the data cannot be queried, MySQL will still return the relevant information of the query, such as the number of rows affected by changing the query and the execution time.

If the query cache is opened and the query can be cached, MySQL will also store the results in the cache.

Returning the result set to the client is an incremental and step-by-step process. It is possible that when MySQL generates the first result, it starts to gradually return the result set to the client. In this way, the server does not need to store too many results and consume too much memory, and the client can also get the returned results at the first time. It should be noted that each row in the result set will be sent with a data packet meeting the communication protocol described in ①, and then transmitted through TCP protocol. During the transmission process, MySQL data packets may be cached and then sent in batches.

To summarize the whole query execution process of MySQL, it is generally divided into six steps:

The client sends a query request to the MySQL server
The server first checks the query cache. If it hits the cache, it immediately returns the results stored in the cache. Otherwise, proceed to the next stage
The server performs SQL parsing, preprocessing, and then the optimizer generates the corresponding execution plan
MySQL calls the API of the storage engine to execute the query according to the execution plan
Return the results to the client and cache the query results

02. Optimization scheme

1. Optimization suggestions

After reading the above principles, you must be well aware of the operation principle of MySQL. Next, I will optimize it from these aspects.

dba

We need to pay attention to these points: first select the SQL to be optimized, generally start from Explain and Profile, always use small result sets to drive large result sets, sort in the index, use the smallest Columns and use the most effective filter conditions to avoid complex joins and sub queries. The specific optimization is as follows:

Analyze SQL execution frequency

show status For example, analyze whether reading is the main task or writing is the main task

Locate inefficient SQL

Slow query log location-log-slow-queries = xxx((specify file name) SHOW PROCESSLIST View the current thread in progress, including thread status and whether to lock the table

Analyze SQL execution plan

explain "your sql"desc "your sql"- Partial parameter analysis select_type: SIMPLE Simple tables without table joins or subqueries PRIMARY The main query is the query of the outer layer UNION SUBQUER First subquery selecttype: ALL Full table scan index Index full scan range Index range scan ref Prefix scan with non unique index or unique index eq_ref similar ref，The index used is unique const/system There can be at most one matching row in a single table NULL You can get the result directly without accessing the table or index

show profile analysis SQL

select @@have_profiling Is it supported select @@profiling Start execution "your sql"show profiles show profile block io for QUERY 17

Index optimization

Here I analyze from three aspects: type, method and creation:

Examples are as follows:

Storage classification of index

B-TREE Index: common, most of them support HASH Index: only memory Engine support R-TREE Indexes: spatial indexes are MyISAM A special index type, mainly used for geospatial data types full-text Index: full text index, MyISAM A special index type of, innodb From 5.6 Start support

Index creation and deletion

Add index ALTER Table `table_name` ADD PRIMARY KEY(`column`)ALTER Table `table_name` ADD UNIQUE(`column`)ALTER Table `table_name` ADD INDEX(`column`)ALTER Table `table_name` ADD FULLTEXT(`column`)delete ALTER Table `table_name` drop index index_name

MySQL can use indexes

Match full value match value range query match the leftmost prefix only query the index (overlay query) match the column prefix (add prefix index) exactly+Partial scope

When an index cannot be used

with%Switched like Implicit conversion of query data type. Composite index query criteria do not include the leftmost part. Using index is still slower than full table scanning or Split condition

Statement optimization

Periodic optimization table

optimize table table_name Merge tablespace fragments, right MyISAM,BDB,INNODB Valid. If the prompt is not supported, you can use mysql --skip-new perhaps mysql --safe-mode To restart for other engines to support

Common optimization

Try to avoid full table scanning, right where and orderby Avoid indexing columns where use != or <>Try to avoid where Clause use or Misuse of connection conditions%Avoid full table scanning as far as possible where Clause to avoid expression operations on fields where Clause to perform functional operations on the fields, overwrite the query, return the required fields, and optimize the nested query. The associated query is better than the sub query combined index or composite index. The leftmost index principle is used exist replace in When the index column has a large amount of duplicate data, SQL Queries may not take advantage of indexes

Optimization of JOIN

JOIN principle

stay mysql Used in Nested Loop Join To achieve join；   A JOIN B: adopt A The result set of the table is used as the basis of the cycle. One by one, the data in the result set is used as the filter condition to query the data in the next table, and then the results are merged

JOIN optimization principle

1，Minimize Join In a statement Nested Loop The total number of cycles, using a small result set to drive a large result set; 2. Give priority to optimization Nested Loop Inner circulation of; 3. Guarantee Join Statement on the driven table Join The condition field has been indexed; 4. Expansion join buffer The size of the;

Database object optimization

Optimize table data types

PROCEDURE ANALYSE (16,256) Exclude more than 16 and more than 256 bytes ENUM proposal"your sql" PROCEDURE ANALYSE ()

Table split

Vertical splitting aims at horizontal splitting tables in which some columns are commonly used and some columns are not commonly used. The data in the table is independent and can be simply classified. A variety of media need to be stored in the table

Anti paradigm

Add redundant columns, add derived columns, regroup tables, and split tables

Use intermediate table

Large amount of data query, data statistics and analysis scenarios

Optimize MySQL server

MySQL engine comparison:

Table engine commands:

show engines; see myql Supported storage engines show variables like '%storage_engine'; see mysql Default storage engine show create table table_name View the storage engine used by the specific table

InnoDB

1\. Provide transaction, rollback, system crash repair capability and multi version concurrency control transaction 2\. Support self addition 3\. Support foreign key 4\. Support transactions and transaction related functions 5\. support mvcc Row level lock

MyISAM

1\. Transaction and row level locks are not supported. Only concurrent inserted table locks are supported. They are mainly used for high load select2\. It supports three different storage structures: static, dynamic and compressed

MySQL concurrent parameter adjustment

MySQL concurrency parameters

last

Even job hopping is a learning process. Only comprehensive review, can we better enrich ourselves, arm ourselves, and make our interview road no longer rough! Today, I'd like to share with you a comprehensive collection of Java interview questions on Github, which helped me win the Offer of a large factory and raise my monthly salary to 30K!

Data collection method: Blue portal

I am also the first time to share it with you. I hope I can help you go to your favorite factory! Prepare for gold, silver and four!
There are 20 topics of knowledge points in total, which are: