One mysql optimization experience and one mysql optimization experience

One mysql optimization experience and one 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.

Best mysql optimization skills

1. select the most suitable field attribute

MySQL can support access to large data volumes, but generally, the smaller the table in the database, the faster the query will be executed on it. Therefore, when creating a table, we can set the field width in the table as small as possible to achieve better performance. For example, if you set it to CHAR (255) when defining the zip code field, it is obvious that unnecessary space is added to the database, and even the VARCHAR type is redundant, because CHAR (6) can well complete the task. Similarly, if possible, we should use MEDIUMINT instead of BIGIN to define integer fields.

Another way to improve efficiency is to set the field to not null whenever possible, so that the database does NOT need to compare NULL values during future queries.

Some text fields, such as "Province" or "gender", can be defined as ENUM. In MySQL, The ENUM type is processed as the numeric data, and the numeric data is processed much faster than the text type. In this way, we can improve the database performance.

2. Use JOIN instead of Sub-Queries)

MySQL supports SQL subqueries from 4.1. This technique can use the SELECT statement to create a single column query result, and then use this result as a filter condition in another query. For example, if you want to delete a customer who has no orders in the basic customer information table, you can use the subquery to retrieve the customer IDs of all orders from the sales information table, then pass the result to the primary query, as shown below:

Delete from customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

Subqueries can be used to complete SQL operations that require multiple logical steps at a time. At the same time, transactions or tables can be prevented from being locked and can be easily written. However, in some cases, subqueries can be replaced by more efficient JOIN. For example, if we want to retrieve all users without order records, we can use the following query:

SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

If you use JOIN... to complete this query, the speed will be much faster. Especially when the salesinfo table has an index on CustomerID, the performance will be better. The query is as follows:

SELECT * FROM customerinfo
Left join salesinfoON customerinfo. CustomerID = salesinfo.
CustomerID
WHERE salesinfo. CustomerID IS NULL

JOIN... it is more efficient because MySQL does not need to create a temporary table in the memory to perform the query in two steps.

3. Use UNION instead of creating a temporary table manually

MySQL 4.0 and later versions support UNION queries. It can merge two or more SELECT queries in a temporary table. When the query Session on the client ends, the temporary table is automatically deleted to ensure the database is neat and efficient. When using UNION to create a query, we only need to use UNION as the keyword to connect multiple SELECT statements. Note that the number of fields in all SELECT statements must be the same. The following example demonstrates a query using UNION.

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author ...... remaining full text>

How to optimize mysql

Don't optimize it for the sake of optimization, which means optimizing it for the problem. It depends on the performance of your database, such as small bufferpool, large io, or sort overflow. Do not optimize several parameters at a time. The general principles of different databases are similar.