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

This article will explain in detail the example analysis of mysql hint optimization for you. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

SELECT t.oldcontractno, t.startdate, t.enddate, sum (confrec.confamt)

FROM (

SELECT ca1.contractno, f1.startdate, f1.enddate, ca1.oldcontractno

FROM contract c1, contractinapprove ca1, framepolicy f1

WHERE c1.contractinapproveid = ca1.contractid

AND ca1.contractid = f1.contractid

AND c1.contracttype = 4

) t, contract c2, confrec

WHERE

(

(substring (c2.oldcontractno, 1, locate ('-', c2.oldcontractno)-1) = t.oldcontractno)

OR

(c2.oldcontractno = t.oldcontractno)

)

AND confrec.contractid = c2.contractid

AND c2.customersitecode NOT IN ('JP',' BD')

AND confrec.confdate BETWEEN t.startdate AND t.enddate

GROUP BY t.oldcontractno

The execution plan is as follows:

+- -+

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+- -+

| | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 57 | Using temporary; Using filesort |

| | 1 | PRIMARY | confrec | ALL | contractid | NULL | NULL | NULL | 1007935 | Using where |

| | 1 | PRIMARY | c2 | eq_ref | PRIMARY | PRIMARY | 4 | ap_db.confrec.contractid | 1 | Using where |

| | 2 | DERIVED | F1 | ALL | FK28E4C8DF253521AD | NULL | NULL | NULL | 262 | |

| | 2 | DERIVED | C1 | ref | contractinapproveid | contractinapproveid | 5 | ap_db.f1.contractid | 1 | Using where |

| | 2 | DERIVED | ca1 | eq_ref | PRIMARY | PRIMARY | 4 | ap_db.c1.contractinapproveid | 1 | Using where |

+- -+

From the execution plan analysis, the biggest problem is the full table scan of confrec, but the join between confrec and contract uses indexes. Look at the execution plan is that confrec has a full table scan, while contract uses indexes. Now modify it. Confrec (million level) uses indexes and contract (3w) uses full table scans. As the amount of data in contract is much smaller and more efficient, it is obvious from the estimated number of rows in the execution plan.

Sql hint is used to control the join order of the table to solve this problem.

SELECT t.oldcontractno, t.startdate, t.enddate, sum (confrec.confamt)

FROM (

SELECT ca1.contractno, f1.startdate, f1.enddate, ca1.oldcontractno

FROM contract c1, contractinapprove ca1, framepolicy f1

WHERE c1.contractinapproveid = ca1.contractid

AND ca1.contractid = f1.contractid

AND c1.contracttype = 4

) t, contract c2 straight_join confrec

WHERE

(

(substring (c2.oldcontractno, 1, locate ('-', c2.oldcontractno)-1) = t.oldcontractno)

OR

(c2.oldcontractno = t.oldcontractno)

)

AND confrec.contractid = c2.contractid

AND c2.customersitecode NOT IN ('JP',' BD')

AND confrec.confdate BETWEEN t.startdate AND t.enddate

GROUP BY t.oldcontractno

Revised execution plan:

+- -+

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+- -+

| | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 57 | Using temporary; Using filesort |

| | 1 | PRIMARY | c2 | ALL | PRIMARY | NULL | NULL | NULL | 13135 | Using where |

| | 1 | PRIMARY | confrec | ref | contractid | contractid | 4 | ap_db.c2.contractid | 45 | Using where |

| | 2 | DERIVED | F1 | ALL | FK28E4C8DF253521AD | NULL | NULL | NULL | 262 | |

| | 2 | DERIVED | C1 | ref | contractinapproveid | contractinapproveid | 5 | ap_db.f1.contractid | 1 | Using where |

| | 2 | DERIVED | ca1 | eq_ref | PRIMARY | PRIMARY | 4 | ap_db.c1.contractinapproveid | 1 | Using where |

+- -+

It is obvious that there is a decrease in the number of connected rows.

Ref: http://hi.baidu.com/veyroner/blog/item/c72827fd9403d3f7fd037f77.html

[@ more@]

If the amount of data and visits to an ordinary MySQL is small, it is fast enough, but when the amount of data and visits increases sharply, then you will obviously find that MySQL is very slow, or even down, so we should consider optimizing our MySQL.

Optimization is nothing more than three angles:

The first one is from the hardware, increase the hardware, increase the server

The second is to optimize our MySQL server to increase the cache size, open multi-ports, and separate read and write.

The third is our application optimization, building indexes, optimizing SQL query statements, setting up caches, and so on.

I will simply talk about the optimization of SQL query statements. Because if we have more Web servers or better performance than database servers, we can completely shift the pressure of the database to the Web server, because if a single MySQL or database server based on Master/Slave architecture has a heavy burden, then we can consider putting the operation of MySQL on the Web server. Of course, if your Web server is worse than the database server, put the pressure on the database server, hehe.

If you put the pressure on the MySQL server on the Web server, then a lot of operations need to be performed by our program, for example, all the Web programs are handed over to the PHP script to process the data. If you have a single MySQL server, if the query, update, insert and delete are all on the same server, you will obviously find table locking when you have a large number of visits. When you update and delete a table, you will reject other operations, which will lead to table locking. The simplest and direct way to solve this problem is to take two MySQL servers, one responsible for query (select) operation. The other one is responsible for update/delete/insert and then synchronizing, which avoids table locking. If there are more servers, it is easier to handle. We can use distributed database architecture and data hash storage. We will talk about it briefly below.

I. Optimization of SQL and matters needing attention

Now let's assume that we have only one MySQL server, and all the select/update/insert/delete operations are done on this, and we have three Web servers that are accessed through DNS rounds, so how do we optimize our application and SQL.

1. Where condition

In the query, the WHERE condition is also a more important factor, as few and reasonable where conditions are very important. When writing each where condition, we should carefully consider it. When there are multiple conditions, put the condition that will extract as little data as possible in front, so that the query time of the latter where condition will be reduced.

Sometimes some where conditions cause the index to be invalid, and the index will be invalid when the Mysql function is used, for example: select * from tbl1 where left (name, 4) = 'hylr', then the index is invalid at this time, and when LIKE is used for search matching, such a statement index is invalid: select * from tbl1 where name like'% xxx%', but this index is valid: select * from tbl1 where name like 'xxx%' So it's important to write your SQL carefully.

two。 Associative query and subquery

A very important feature of the database is the association query, LEFT JOIN and full association, especially multiple tables, because each associated table query is scanned in the order of magnitude of Cartesian product, and the number of scans is very large. If you really need an association operation, please index the conditions of where or on.

The associated operation may also be left to the application to operate, depending on the amount of data. If the amount of data is not very large, such as less than 100000 pieces, then it can be handed over to the program to deal with (totododo puts forward a pen error and hereby corrects). The program extracts the data from the left and right tables respectively, and then scans the left and right tables and returns the results. This process also consumes the resources of the Web server very much. Then it depends on whether you are willing to put pressure on the Web server or the database server.

Subquery is a feature supported in mysql5, such as select * from tbl1 where id in (select id from tbl1), which is very inefficient, so try to avoid using subquery, if I, absolutely not, hehe.

3. Some time-and resource-consuming operations

Some wasteful operations in SQL statements, such as DISTINCT, COUNT, GROUP BY, various MySQL functions. These operations are resource-consuming. I think the most frequently used words are count. If you use count, try not to count (*). It is best to count a field, such as count (id), or count (1), (according to the totododo test efficiency is actually the same), can also play a statistical role. If it is not necessary, try not to use the distinct operation, which is to extract the unique value. You can leave this operation to the script sequence to extract the unique value, reducing the burden of MySQL. Group by operation is the same, if you really need to group, please operate carefully, if it is a small batch of data, you can consider giving it to the script to do.

As for MySQL functions, it is estimated that many commonly used, for example, some people like to intercept strings to MySQL to operate, or time conversion operations, using more functions such as SUBSTR (), CONCAT (), DATE_FORMAT (), TO_DAYS (), MAX (), MIN (), MD5 (), etc., these operations can be given to scripts to do, reduce the burden of MySQL.

4. Reasonable establishment of index

A very important means to improve the speed of the index, the index is quite effective on some fields that often perform select operations and have unique values, such as the id field of the primary key, the unique name name field, and so on.

But the index does not make much sense for fields with fewer unique values, such as gender gender fields, few category fields, and so on, because there is a 50% chance that gender is 50%, and the index is almost meaningless. For update/delete/insert very frequent tables, the establishment of the index should be carefully considered, because these frequent operations are also very heavy for the maintenance of the index, and in the end, the loss outweighs the gain, which needs to be carefully considered. The more indexes, the better. Appropriate indexes will play a key role. Inappropriate indexes will reduce the efficiency of maintenance and increase the burden of index maintenance.

5. Monitor sql execution efficiency

Using the EXPLAIN sentence before the select statement can view the execution of the current select sentence, including what operation is used, how much probability is returned, how the index is used, and so on, which can effectively analyze the execution efficiency and reasonable degree of the SQL statement.

In addition, the use of MySQL's own slow query log: slow-log can also record the SQL statements that spend more time in the query, so as to optimize and rewrite the corresponding statements.

In addition, under the MySQL terminal, using the show processlist command can effectively view the current thread in MySQL, including the status of the thread, whether to lock the table, etc., can view the implementation of SQL in real time, and optimize some table locking operations at the same time.

Second, the structure and distribution idea of database server.

For the architecture design of the server, this is actually quite important, a reasonable design can make the application run better. Of course, the design of the architecture depends on your application and the actual situation of your hardware. I will briefly talk about several different ways of database architecture design, right should be a personal idea, I hope it can be helpful.

1. Multiple processes and ports open on a single server

A single MySQL server, if you use long links, etc., can not solve the problem of too much load and too many connections. Consider using the method of using multiple ports to open multiple MySQL daemons on a MySQL to relieve the pressure. Of course, the premise is that your application must support multiple ports, and you have enough cpu and memory to run multiple daemons.

The advantage is that it can relieve the pressure on the temporary server, put different operations on different ports, or put different project modules on different ports to operate, and well share the pressure of a single daemon.

The disadvantage is that the data may be disordered and may lead to many unknown inexplicable errors. He he

two。 Server structure using Master/Slave

Mysql itself has a synchronization feature, which you can take advantage of. To build the master-slave server structure of Master/Slave, at least two MySQL servers are needed. We can update the Master server users, including update/delete/insert, use the Slave server for query operations, including select operations, and then synchronize the two machines.

The advantage is that it can reasonably share the pressure of update and query, and can avoid the problem of locking tables.

The disadvantage is that the update department is real-time, if the network is busy, there may be a delay problem, and any server down is very troublesome.

3. Use distributed hash storage

This structure is suitable for a large amount of data, and the load is relatively large, and then the server is relatively sufficient. Distributed storage structure, simple can be multiple servers, each server function is similar, but the data stored is different, such as a user system, then the user ID within 1-100000 stored in A server, user ID stored in 10-200000 B server, 20-30 million stored in C server, and so on. If each user does not have enough servers to access, you can build a group server, that is, each group of users has multiple servers. For example, you can set up two MySQL servers in a user group, one Master and one Slave, which can also separate their update and query operations, or can be designed for two-way synchronization. At the same time, your application must support cross-database and cross-server operations.

The advantage is that the load of the server is reasonably shared, and each server is responsible for some users. If a server down is dropped, it will not affect the normal access of other users'ID users. At the same time, it is relatively easy to add nodes, if you add another 100000 users, then you can add a node server, upgrade is very convenient.

The disadvantage is that any database server down or data loss, then this part of the server users will be very depressed, the data is gone, of course, this requires a good backup mechanism.

This is the end of the sample analysis on mysql hint optimization. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.