Shulou(Shulou.com)06/01 Report--

Transferred from http://www.cnblogs.com/Aiapple/p/5697229.html

Review what is business audit similar to code review review business Schema and SQL design focus on performance is one of the main entrances for business optimization audit discovers problems in advance, discovers problems through monitoring or inspection after optimization launch, optimizes Schema design audit table and field naming to determine whether field types are compliant, length design is appropriate, table association relationship is reasonable, and update time retention fields meet the requirements Whether the default values and other configurations properly understand the business, the amount of table data, the data access mode of the growth mode, the degree of equilibrium according to business requirements, whether the table needs to be partitioned, and whether there is data in what cycle SQL statements audit whether there is a suitable cost execution mode for single execution of SQL statements on the table. Lock analysis focuses on when the transaction context needs to audit the business development phase and the business version changes before launch. Before online update, new tables and SQL online SQL query conditions change SQL query frequency change business logic leads to existing table data size change business release process SQL audit needs development and application operation and maintenance support to fully communicate, do a good job of necessity explanation and educational work to specify business release process, embed DBA audit link to accumulate experience, constantly improve evaluation methods, slow query optimization, index optimization The optimization of database table structure needs to go hand in hand. Slow query two-step analysis: confirm whether the application requests a large amount of more data than needed from the database. Confirm whether the mysql server layer is dealing with a large number of more than needed data records. Typical case: query unwanted records multi-table association return all columns always take out all columns repeat query the same data mysql is scanning extra After determining that the query returns only the required data Next, you should see if the query scans too much data in order to return the results. Three metrics of mysql query cost: the number of rows returned by the response time scan, these three indicators will be recorded in the slow log of mysql. Index checking slow logging is easy to find queries with too many rows scanned. Response time: execution time and wait time; to determine whether a response time is a reasonable value, you can use "fast upper limit estimation". When analyzing a query, it is helpful to see the number of rows scanned by the query. To some extent, it shows whether the query is efficient in finding the data it needs. If you find that the query needs to scan a large amount of data but returns only a small number of rows, the optimization method: use the index override scan to put all the columns you need into the index. Change the database table structure. For example, rewrite this complex query with a separate summary table, allowing the mysql optimizer to execute the query in a more optimized manner. Sometimes it is necessary to decompose a large query into multiple small queries. The underlying mysql query execution path for query execution

The customer server sends a query to the server to check the cache first. If the cache is hit, the result is returned immediately. Or move on to the next stage. The server carries out SQL parsing, preprocessing, and then the optimizer generates the corresponding execution plan. Mysql invokes the API of the storage engine to execute the query according to the execution plan generated by the optimizer. Return the result to the client mysql client / server communication protocol the communication protocol between the mysql client and the server is "half-duplex". Can only be sent by one party at any time; cannot be sent at the same time; thread status during mysql connection

Mysql > show full processlist +-+-+ | Id | User | Host | db | Command | Time | State | Info | +-+-- -+-- + | 39 | root | localhost | sakila | Sleep | 4 | NULL | | 40 | root | localhost | sakila | Query | 0 | NULL | show full processlist | +-+-- -+-+ 2 rows in set (0.00 sec)

Query optimizer A query can be executed in many ways, all of which return the same result. The role of the optimizer is to find the best execution plan. Mysql uses a cost-based optimizer, which attempts to predict the cost of a query when it uses some kind of execution plan, and selects the one with the lowest cost. The cost of the current query calculated by Mysql is known by querying the value of the last_query_cost for the current session. Mysql > selectcount (*) from film_actor; +-+ | count (*) | +-+ | 5462 | +-+ 1 row inset (sec) mysql > show status like 'last_query_cost' +-+ | Variable_name | Value | +-+-+ | Last_query_cost | 1040.599000 | +-+-+

This result indicates that the mysql optimizer believes that a random search of about 1040 data pages is needed to complete the above query. This is based on a series of statistics: the number of pages per table or index, the cardinality of the index (the number of different values in the index), the length of the index and data rows, and the distribution of the index. The optimizer does not take into account any level of caching when evaluating the cost, and it assumes that a disk Ihand O is required to read any data. The reason why the mysql optimizer chose the wrong execution plan: the statistics are not accurate. The cost estimate in the execution plan is not equal to the actual execution cost. There are plans to read more pages, but they are in the cache. Mysql is most likely different from what you think is the best. For example, you want the execution time to be as short as possible, while mysql is only the optimal execution plan selected based on the cost model. Mysql never considers other queries executed concurrently, which may affect the current query speed. Mysql does not consider the cost of operations beyond its control. Such as the cost optimization strategy of executing stored procedures or user-defined functions: static optimization directly analyzes the parsing tree and completes the optimization. The optimizer converts the where condition into another equivalent form through some simple algebraic transformations. Static optimization is valid after the first completion. It can be thought of as a kind of "compile-time optimization" dynamic optimization dynamic optimization is related to the context of the query. It is also related to many other factors, such as values in where, entries in the index, and so on. Each query is reevaluated, which can be thought of as a type of optimization that the runtime optimization mysql can handle to redefine the order of the associated tables. Convert outer join to inner join use equivalent change rules to merge and reduce some comparisons, remove some constant and untrue judgments and optimize count (), min (), max (), min () directly estimate the leftmost data rows of the BTree tree and convert them into constant expressions to overwrite index scanning subquery optimization to terminate query equivalent propagation in advance to add hint to the query optimizer, prompting the optimizer Limitations of the optimizer 1 the subquery of the associated subquery mysql is poorly implemented The worst kind of query is a subquery statement that contains IN () in the where condition. For example, we want to find information about all the films in the sakila database where the actor actor_id is 1. Naturally, we will follow the following pattern: mysql > select * from film where film_id in (select film_id from film_actor where actor_id = 1)\ G

We generally think that mysql will first find all the film_id of the actor_id=1 in the subquery, and then do external queries, such as select * from film where film_id in (1mem23 and 25106140).

However, mysql does not do this. Mysql presses the relevant outer table into the subquery, which it thinks can find data rows more efficiently. Of course, we can use join instead of subquery to rewrite this SQL to optimize Mysql > explain select*from film f innerjoin film_actor fa where f.film_id=fa.film_id and actor_id = 1 +-+-+ | id | select _ type | table | type | possible_keys | key | key_len | ref | rows | Extra | +-- +-+ -+-+ | 1 | SIMPLE | fa | ref | PRIMARY Idx_fk_film_id | PRIMARY | 2 | const | 19 | | 1 | SIMPLE | f | eq_ref | PRIMARY | PRIMARY | 2 | sakila.fa.film_id | 1 | + -+ 2 rows inset (0.00 sec) how to make good use of related subqueries In many cases, the associated subquery is also a very reasonable, natural, and even the best way to write. Where in () certainly doesn't work, but where exists () sometimes works; 2 union's restrictions sometimes mysql cannot "push" the constraints from the outer layer to the inner layer, which prevents the conditions that could have limited the return of some results from being applied to the optimization of the inner query. If you want each clause of union to take only part of the result set according to limit, or if you want to be able to capture the result set first and then merge the result set, you need to use these clauses in each clause of union. For example: (select first_name,last_name from sakila.actor orderby last_name) unionall (select first_name,last_name from sakila.customer orderby last_name) limit 20; put 200records in actor and 599records in customer in a temporary table, and then take out the first 20 records from the temporary table; and (select first_name,last_name from sakila.actor orderby last_name

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.