Mysql optimization and mysql Optimization

Mysql optimization and mysql Optimization
Mysql Optimization

I 've been writing a blog on csdn for a long time. Recently, I 've been learning mysql optimization at Yan 18, and this teacher is still talking about it, today, I just have time to summarize what I learned during this period.

First, go to a flowchart (this figure is taken from the tutorial of Yan 18)

When there is a problem with a db server, first of all, it is not to look at the code where there is a problem, think about whether the SQL statement is written, whether the table structure is reasonable or not; but from a macro perspective, we need to look at where there are problems.

The first step is to find out whether the server problem is caused by a hardware bottleneck. If a server hardware itself is not good, it can only handle 200 M of io reads and writes. If you want it to provide up to M of io, so even if you want to optimize it, we need to first install sysbench in the benchmark test. It provides performance tests such as cpu, Io, memory, and mysql ,;
1. cpu test sysbench -- test = cpu -- cpu-max-prime = 2000000 run 2. io test

sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw preparesysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw runsysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw cleanup

3. OLTP Test
sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=test --mysql-host=localhost --mysql-password=test prepare
After passing these tests, you will not be able to know the capabilities of your server. If you find that the server has good performance but still cannot meet your needs, it will only be a software problem, you need to locate the problem.
Step 2: Observe the connection status of mysql in a certain period of time. If the processing status is not big, we need to observe the status of mysql. Generally, this status cannot be fixed at half past one, all of them need to write a script to record the pressure value record of mysql in a certain cycle in the background, for example, one day, one week is a cycle; the command to view the mysql status is show status; this command returns several hundred lines of things, but we only need to pay attention to three lines 1. queries, the current query has occurred (you can use the number of Queries in two time periods to subtract the number of Queries in the time range) 2. threads_connected: How many connections are currently connected to mysql3. Threads_running. Several threads are currently running, which is usually Threads_connected> = Threads_running. It may be blocked because mysql does not have to work when connected, pending. we write a script to read these three numbers every second and append them to mysql. status File 2. simulate access with the AB tool, send 20000 requests with 50 concurrent requests (each request on this page will visit mysql multiple times ), in this way, the above script will get the result AB-c 50-n 2000 http: // 59.69.128.203/JudgeOnline/nyistoj/index. php/Problem/index let's check this mysql. in the status file, we use the first value of the previous row minus the first value of the next row to obtain the number of mysql accesses per second, which is about 1000 +, it can also be seen that there are basically 50 connections, with two threads processing requests on average; you can write a script again for processing to get the number of processes per second, A little more than 1000, it seems that the results are not good. 1. the frequency of accessing mysql is very stable (for example), so we can optimize it from other aspects of mysql, such as the table structure, SQL statement optimization, mysql configuration, and engine selection, index optimization 2. the Access Frequency of mysql changes cyclically (for example), and is optimized from the peak. For example, if memcatch is periodically invalid, the random mode can be used to make the failure more even, or let him expire at around 3 o'clock in the evening. The access traffic is not large at this time, and the memcatch buffer is also basically set up in the next day; or it is optimized from the business perspective, such as 12306 of the votes, you can release tickets in batches in different provinces and time periods, which avoids the ultra-high peak value caused by collective ticketing across the country. You can also enable slow queries during peak hours, analyze specific SQL statements using tools such as processlist;
III. view the status of the mysql process. If you need to know the overall situation of the mysql process for processing SQL statements, you need to use the show processlist tool, this tool is mainly used to record every SQL Execution process. We write a script to capture the status, and then take a look at what the mysql process is basically doing; show processlist \ G

The Status may be in many situations, but we mainly focus on the following states: 1. create tmp table; Create a temporary table. For example, a temporary table is created after right join. sending Data; send Data, such as limit 1, 1000; then it will send a large amount of Data and spend time, you can limit a little less 3. sortIng for Group; the Group is being sorted. In this case, the optimization is generally based on composite index 4. copying to tmp table on desk; the hard disk is being copied to the memory table, mainly because the table is too large. For example, if you join the table, a large table can only be stored on the hard disk, avoiding join5. Locked; data Locking and transaction optimization. 6. converting HEAP to MyISAM; the query result is too large and you are trying to store the result on the hard disk. The optimization is to try to read less data at a time, such as reading the news list at a time, the reader rarely reads several hundred entries at a time; then we write a script to capture these statuses:

Then process mysql. process;

The following result is displayed:

It can be seen that many times the Copying to tmp table, Sending data, and Sort result are spent many times. You can roughly know that the business logic leads to a large amount of data to be retrieved, it can change the business or buffer server to block the mysql front;
Check Copying to tmp table; first open profiles;
When monitoring is enabled, You can take snapshots of each stage of SQL Execution. In this way, you can clearly find out the SQL Execution Process and the specific stage in which it takes time, further targeted Optimization

Then the SQL statement will be recorded,

Use show profiles to obtain the statement id;

The statement id is 27, which takes more than 6 seconds to view the specific content of id 26:


Now we know that this SQL statement takes time to copy data to the hard disk and sort data. Because we have three joins, and these joins are sorted by titles at the same time, indexes cannot be overwritten, therefore, the data in the hard disk needs to be returned, which leads to a very large table and cannot be put into the memory. Instead, the data can only be placed on the hard disk. Then, this SQL statement can be implemented through targeted optimization; conclusion: After the above steps, we can gradually locate the problem where our server is located, either because the server itself is not strong enough or is periodically faulty, or your own code or table structure is not good enough, or business logic and other issues. We will mainly optimize the specific problems later. This is the content of the next article.

Mysql query statement Optimization

It can be optimized on the premise that the programming master + source code is complete, otherwise it is impossible to implement it.

The simplest optimization is to delete historical data and split: deleting historical data and redundant data is the most effective. Splitting another large database into several small databases is also a good result.

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>