One MySQL optimization experience

One day operation and maintenance suddenly said that the wireless Terminal Channel page interface access is very large, memcache cache can not carry, resulting in MySQL concurrency query volume is too large, causing the server to continue to go down, can only keep restarting the machine. Unfortunately, OPS does not tell MySQL how much the query volume is "non-quantifiable, such as how many queries a second ...".

In response to this problem, some colleagues suggested changing the Mysql+memcache architecture and using Redis storage better. But what is the real reason for the problem? MySQL should be able to carry hundreds of concurrent queries in a second. With doubt, I let ops give a slow query log.

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

There is an order by zj_lastupdate in the SQL statement, which is clearly indexed on this field, but why not? "Too many federated indexes have been created on this table, so that zj_lastupdate is ignored", causing the query to use a temporary table "using Temporary "and file sort" Usingfilesort ".

The workaround is to add use Index (zj_lastupdate) to the SQL statement, alerting the MySQL engine to using the specified index field. Explain again:

Obviously, this time the query engine will use Zj_lastupdate.

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

Another MySQL optimization experience, 2 tables connected, one-to-one relationship, the optimization before the SQL statement is probably SELECTDISTINCT (MovieID) as Id...,explain results are:

Obviously, a one-to-one relationship table, without adding the DISTINCT keyword "is the lily of the bar", after removing, then explain:

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

MySQL query optimization has a lot of basic theory, can be from query statements, table structure "table, field redundancy", field type, index, storage engine, cache, system kernel parameters, disk IO and other aspects of consideration, but it is important to write specific SQL statements, for this particular statement for the specific optimization, The premise, of course, is to ensure that the results are accurate.