Mysql optimization experience

Mysql optimization experience

One day, O & M suddenly said that the channel page interface of the wireless terminal had a large amount of traffic, and the memcache cache could not handle it. As a result, the mysql concurrent query volume was too large, causing the server to go down without stopping and restarting the machine. Unfortunately, the O & M does not tell mysql the specific query volume, for example, the number of queries per second ...].

To solve this problem, some colleagues suggested changing the mysql + memcache architecture and using redis for better storage. But what is the real cause of the problem? Can mysql handle hundreds of concurrent queries in one second? With questions, I asked O & M personnel to provide slow query logs.

Oh, my god... There are too many slow query records, all of which are more than one second, but basically the same statement query. Explain:

The SQL statement contains order by zj_lastupdate, which clearly creates an index on this field, but why not? [this table has too many joint indexes, so zj_lastupdate is ignored ], therefore, this query uses the temporary table "using temporary" and "usingfilesort ].

The solution is to add the use index (zj_lastupdate) in the SQL statement to remind the mysql engine to use the specified index field. Explain again:

Obviously, this query engine will use zj_lastupdate.

The optimization effect is quite obvious, from the previous 1.5 seconds to 0.015 seconds, a performance improvement of times. Of course, after this problem is solved, there will be no downtime, and we have not changed the architecture.

Another mysql optimization experience is that two tables are connected and one-to-one. The previous SQL statement is probably selectdistinct (movieid) as id ..., The explain result is:

Obviously, you do not need to add the distinct keyword to a one-to-one table. After removing the distinct keyword, explain it again:

The performance is improved by about 10 times before and after optimization.

Mysql has many basic theories for query optimization, including query statements, table structure [Table sharding, field redundancy], field types, indexes, storage engines, caches, and system kernel parameters, disk IO and other considerations, but it is very important to write a specific SQL statement, specific optimization for this specific statement, of course, the premise is to ensure that the results are accurate.