- Why queries are slow
- Slow Query Basics: Optimizing data access
- Verifying that an application is retrieving a large amount of data that is more than needed usually means accessing too many rows, but sometimes it may be too many columns to access
- Querying for data that is not needed
- Return all columns When multiple table associations are associated
- Always take out all columns
- Querying the same data repeatedly
- Verify that the MySQL server layer is analyzing a large number of data rows that are more than needed
- Three metrics to measure query overhead
- Number of rows scanned
- Number of records returned
- Response time
- The response time is just a surface value
- Response time is a sum of two parts: service time and queue time
- Number of rows scanned and number of rows returned
- When you parse a query, it is helpful to view the line dozens of scanned by the query, which in part demonstrates the high efficiency of finding data in the query
- Number of rows scanned and type of access
- From slow to fast: Full table scan, index Scan, unique index scan, constant reference, scan row count is also large to small
- In general, MySQL is able to apply the where condition, from good to bad, in the following three ways:
- Use the Where condition in the index to filter for mismatched records, which is done at the storage engine level
- Use an index overlay scan to return records, filter unwanted records directly from the index, and return hit results, which is done at the MySQL engine layer, but does not need to be returned to the table to query the records
- Return data from the data table and filter for records that do not meet the criteria (Useng where), which is done at the MySQL server layer, and MySQL needs to read the records from the data table and then filter
- How to Refactor queries
- A complex query or multiple simple queries
- This is a question to consider when designing queries, whether to divide a query into multiple simple queries
- Slice Query
- Sometimes for a big query we want to ' divide and conquer ', the big query cut into small query, each query function exactly the same, only a small part, each time only a small number of query results returned
- Explode an associated query
- Many high-performance applications decompose associated queries. Simply, you can make a single query on each table, and then associate the results in the application
- Advantage:
- Make the cache more efficient
- After the query is decomposed, executing a single query can reduce lock contention
- Correlation at the application level makes it easier to split the database, making it easier to perform high-performance and scalable
- The query itself may also improve in efficiency
- Queries that can reduce redundant records
- Further, this bear is implemented as a hash association in the application, rather than using the nested Loop Association of MySQL
- Basis for query execution
- Customer first sends a query to the server
- The server checks the cache first, and if the cache is hit, returns the results stored in the cache immediately, otherwise entering the next stage
- The server side performs SQL parsing, preprocessing, and then generates the corresponding execution plan by the optimizer
- MySQL executes the query using the storage engine's API, based on the execution plan generated by the optimizer
- Return the results to the client
- MySQL client/server communication protocol
- Query status
- Sleep
- Query
- Locked
- Analyzing and statistics threads, statistics on the phone storage engine, and generating execution plans for queries
- Copying to TMP table [on disk]
- sorting result
- Sending data
- Query cache
- Query and cache queries do not match cached results even if only one byte is different
- Query optimization processing
- The next step in the life cycle of a query is to convert a SQL into an execution plan, and MySQL interacts with the execution plan and the storage engine.
- Syntax Parser and preprocessing
- Query optimizer
- There are a number of reasons why the MySQL optimizer chooses the wrong execution plan:
- Inaccurate statistical information
- The cost estimate in the execution plan is not equal to the actual cost of execution
- MySQL only chooses the optimal execution plan based on its cost model, and sometimes these are not the quickest way to execute.
- MySQL never considers other concurrently executed queries, which can affect the speed of the current query
- MySQL is not always a cost-based optimization, and sometimes is based on some fixed rules
- MySQL does not consider the cost of operations that are not under its control
- The optimizer is sometimes unable to estimate all possible execution plans, so he may miss the actual optimal execution plan
- The optimization strategy can be easily divided into two types, one is static optimization, the other is dynamic optimization
- The static optimization can analyze the analytic number directly and complete the optimization
- Static optimizations do not depend on special values, such as some constants that are brought into a where condition, etc.
- Static optimizations remain in effect after the first completion, and do not change even if the query is repeated with different parameters
- Dynamic optimization is related to the query context and may be related to many other factors
- The difference between dynamic optimization and static optimization is important when executing statements and stored procedures
- Types of optimizations MySQL can handle
- To redefine the order of associated tables
- Converting an outer connection into an inner connection
- Using equivalent transformation rules
- Optimize count (), Min (), and Max ()
- Estimating and converting to constant expressions
- Overwrite Index Scan
- Sub-query optimization
- Early termination of queries
- Equivalent propagation
- Comparison of list in ()
- Statistics for data and indexes
- Statistical information is implemented by the storage engine, and different storage engines may have different statistics stored
- How MySQL executes an association query (join)
- The relevance of MySQL is more broadly understood than in general, and MySQL considers any query to be an association--not just a query that needs to be matched to two tables, so in MySQL, every query, every fragment (including subqueries, Single-table-based select) can be associated
- MySQL performs a nested loop association operation on any association, that is, MySQL now loops through a single table of data and then nests it into the next table to look for matching rows, one at a time, until the matching behavior of all tables is found.
- Execution plan
- MySQL generates a command tree for the query and then executes it through the storage engine to complete the instruction tree and return the results
- The MySQL execution plan is a tree with a left-hand depth priority
- Association query Optimizer
- The associated query optimizer chooses a least-cost association order by evaluating the cost in different order
- Sorting optimization
- Single transmission sequencing
- All the columns required by the query are read first, then sorted according to the given column, and the result is sorted directly
- Query execution engine
- Returns the result to the client
- Limitations of the query optimizer
- Correlated subqueries
- Limitations of Union
- If you want the words of the Union to be able to take only a subset of the result set based on the limit, or if you want to be able to first order and merge the result set, you need to use these clauses separately in the various clauses of the Union
- Index consolidation Optimization
- When a WHERE clause contains multiple complex conditions, MySQL can access multiple indexes of a single table to locate the rows that need to be found by merging and cross-filtering
- Equivalent transfer
- Parallel execution
- Hash Association
- Loose index Scanning
- Optimization of maximum and minimum values
- Querying and updating on the same table
- Hints for the query optimizer (hint)
- Refine a specific type of query
- Optimize count () queries
- The role of Count ()
- Count () is a special function that has two different functions: it can count the number of values of a column or count the number of rows counted without counting null
- Optimizing Associated Queries
- Make sure there is an index on the column in the on or using clause
- Make sure that any expression in group by and order by involves only the columns in one table, so that the index can be used to optimize the process
- Refine subqueries
- Optimizing GROUP BY and distinct
- Optimize Limit Paging
- Optimize sql_calc_found_rows
- Optimizing Union queries
- Static query Analysis
- Using user-defined variables
- A user-defined variable is a temporary container for storing content that persists throughout the entire process of connecting to MySQL.
- The following scenarios cannot be used
- Queries that use custom variables cannot use the query cache
- You cannot use a custom variable where a constant or identifier is used
- The lifetime of a user-defined variable is valid in a connection, so they cannot be used to communicate between connections
- If you use a connection pool or persist a connection, custom variables may interact with seemingly unrelated code
- You cannot explicitly declare the type of a custom variable
- The MySQL optimizer may optimize these variables in some scenarios, which may cause the code not to run as expected
- The order of assignment and the point in time of assignment are not always fixed
- Assignment notation: = has a very low priority, so the assignment expression should use explicit parentheses
- Using undefined variables does not result in any syntax errors
- Optimize ranking Statements
- Avoid repeating queries for newly updated data
- Statistics update inserted values
- Determining the order in which values are taken
- Write a lazy Union
- Other uses of user-defined variables
- Query run-time calculation totals and averages
- function first () and last () in the simulation group statement
- Do some data calculations on a large amount of data
- Calculates the MD5 hash value of a large table
- Write a sample processing function to change the value of a sample to 0 when it exceeds a certain boundary value.
- Analog read/write Cursors
- Adding variable values in the WHERE clause of the show statement
mysql--Query Performance optimization
