MySQL performance optimization

1. Optimize your query for query caching

Most MySQL servers have query caching turned on. This is one of the most effective ways to improve sex, and this is handled by the MySQL database engine. When many of the same queries are executed multiple times, the results of these queries are placed in a cache so that subsequent identical queries do not have to manipulate the table directly to access the cached results.

2. EXPLAIN your SELECT query

Use the EXPLAIN keyword to let you know how MySQL handles your SQL statements. This can help you analyze the performance bottlenecks of your query statement or table structure.

　　EXPLAIN 's query results will also tell you how your index primary key is being leveraged, how your data tables are searched and sorted ... Wait, wait.

3. use LIMIT 1 when only one row of data is used

When you query a table, you already know that the result will only have one result, but because you might need to fetch the cursor, or you might want to check the number of records returned.

In this case, adding LIMIT 1 can increase performance. This way, theMySQL database engine stops searching after it finds a piece of data, instead of continuing to look for the next record-compliant data.

4. Jianjian index for search words

The index does not necessarily give the primary key or the unique field. If you have a field in your table that you will always use to do a search, then index it.

5. use a fairly typed example in the Join table and index it

If your application has many join queries, you should confirm that the fields of join in two tables are indexed. In this way,MySQL internally initiates the mechanism for you to optimize the SQL statement for Join .

Also, the fields that are used for Join should be of the same type. For example, if you want to join a DECIMAL field with an INT field,MySQL cannot use its index. For those STRING types, you also need to have the same character set. ( Two tables may not have the same character set )

6. never ORDER by RAND ()

Want to disrupt the data rows returned ? Pick a random data ? I don't know who invented this usage, but many novices like it. But you do not understand how horrible the performance problem is.

If you really want to disrupt the data rows that you return, there are N ways you can achieve this. This use only degrades the performance of your database exponentially. The problem here is thatMySQL will have to execute the RAND () function ( which consumes CPU time ), and this is done for each row of records to be recorded and then sorted. Even if you use Limit 1 is useless ( because to sort )

7. Avoid SELECT *

The more data you read from the database, the slower the query becomes. And, if your database server and WEB Server are two separate servers, this also increases the load on the network transport.

So, you should develop a good habit of taking whatever you need.

8. always set an ID for each table

We should set an ID for each table in the database as its primary key, and the best is an INT type ( recommended to use UNSIGNED), and set the automatically added auto_increment logo.

Even if you have a field in the Users table that has a primary key called "email", you don't have to make it a primary key. Use the VARCHAR type to degrade performance when the primary key is used. In addition, in your program, you should use the ID of the table to construct your data structure.

Also, under the MySQL data Engine, there are some operations that need to use primary keys, in which case the performance and settings of the primary key become very important, such as clustering, partitioning ...

9. use ENUM instead of VARCHAR

　　The ENUM type is very fast and compact. In fact, it holds the TINYINT, but it appears as a string on its appearance. In this way, using this field to make a list of options becomes quite perfect.

recommendations from PROCEDURE analyse ()

　　PROCEDURE Analyse () will let MySQL help you analyze your fields and their actual data, and will give you some useful advice. These suggestions will only become useful if there is actual data in the table, because it is necessary to have data as a basis for making some big decisions.

For example, if you create an INT field as your primary key, but not too much data, thenPROCEDURE analyse () will suggest you change the type of the field to Mediumint . Or you use a VARCHAR field, because there is not much data, you might get a suggestion that you change it to an ENUM . These suggestions are probably because the data is not enough, so the decision-making is not accurate.

In phpMyAdmin , you can view these suggestions by clicking "propose table Structure" while viewing the table.

One by one . use not NULL whenever possible

Unless you have a very special reason to use null values, you should always keep your fields not NULL. This may seem a bit controversial, please look down.

First, ask yourself how big the difference is between "Empty" and "null" ( if it's INT, that's 0 and null)? If you feel that there is no difference between them, then you should not use NULL. ( Do you know ? ) In Oracle ,NULL and Empty strings are the same !)

Do not assume that NULL does not require space, that it requires extra space, and that your program will be more complex when you compare it. Of course, this is not to say that you cannot use null , the reality is very complex, there will still be cases where you need to use a null value.

Prepared Statements

　　Prepared Statements is much like a stored procedure, a collection of SQL statements running in the background, and we can derive many benefits from using Prepared statements , Whether it's a performance issue or a security issue.

　　Prepared Statements can check some of the variables you've bound so that you can protect your program from "SQL Injection" attacks. Of course, you can also manually check these variables, however, manual checks are prone to problems and are often forgotten by programmers. When we use some framework or ORM , this problem is better.

In terms of performance, this gives you a considerable performance advantage when the same query is used multiple times. You can define some parameters for these Prepared statements , and MySQL will parse only once.

While the latest version of MySQL in the transmission Prepared statements is using the binary situation, this makes the network transfer very efficient.

non-buffered queries

Normally, when you execute an SQL statement in your script, your program will stop there until the SQL statement is returned, and your program continues to execute. You can use unbuffered queries to change this behavior.

Save the IP address as UNSIGNED INT

Many programmers will create a VARCHAR field to hold IP in the form of a string instead of a shaped IP. If you use plastic to store it, you only need 4 bytes, and you can have a fixed-length field. And, this will bring you the advantage of querying, especially when you need to use such a WHERE condition:IP between Ip1 and Ip2.

We have to use UNSIGNED INT, because the IP address uses the entire four-bit unsigned shaping.

fixed-length tables are faster

If all the fields in the table are fixed length, the entire table is considered "static" or "fixed-length". For example, there are no fields of the following types in the table: VARCHAR,TEXT,BLOB. As long as you include one of these fields, the table is not a fixed-length static table, so theMySQL engine will handle it in a different way.

Fixed-length tables can improve performance because MySQL searches faster because these fixed lengths are easy to calculate the offset of the next data, so the nature of reading will be fast. And if the field is not fixed, then every time you want to find the next one, you need the program to find the primary key.

Also, fixed-length tables are more likely to be cached and rebuilt. However, the only side effect is that a fixed-length field wastes some space, because the field is set to allocate so much space whether you use it or not.

Vertical Segmentation

"Vertical Segmentation" is a method of turning a table in a database into several tables, which reduces the complexity of the table and the number of fields for optimization purposes.

. splitting a large DELETE or INSERT statement

If you need to perform a large DELETE or INSERT query on an online website, you need to be very careful to avoid your actions to keep your entire site from stopping accordingly. Because these two operations will lock the table, the table is locked, the other operations are not in.

　　Apache will have a lot of child processes or threads. So, it works quite efficiently, and our servers don't want to have too many child processes, threads and database links, which is a huge amount of server resources, especially memory.

the smaller the column, the quicker it will be.

For most database engines, hard disk operations can be the most significant bottleneck. So it's very helpful to have your data compact, because it reduces access to the hard drive.

Select the correct storage engine

There are two storage engines MyISAM and InnoDBin MySQL , each with a few pros and cons. Cool Shell before the article "mysql:innodb or MyISAM? " "Discussion and this matter.

　　MyISAM is suitable for applications that require a large number of queries, but it is not very good for a lot of write operations. Even if you just need to update a field, the entire table will be locked and other processes will be unable to manipulate the read process until the read operation is complete. In addition,MyISAM 's calculations for SELECT COUNT (*) are extremely fast.

　　The InnoDB trend will be a very complex storage engine, and for some small applications it will be slower than MyISAM . He is it supports "row lock", so in the writing operation more time, will be more excellent. Also, he supports more advanced applications, such as: transactions.

A . using an Object-relational mapper (relational Mapper)

With ORM (Object relational Mapper), you can gain reliable performance gains. All the things an ORM can do, can be written manually. However, this requires a senior expert.

　　The most important thing about ORM is "Lazy Loading", that is to say, only when the need to take the value of the time to really do. But you also need to be careful about the side-effects of this mechanism, because this is likely to degrade performance by creating many, many small queries.

　　ORM can also package your SQL statements into a single transaction, which is much faster than executing them alone.

Watch out for "permanent links"

The purpose of the permanent link is to reduce the number of times the MySQL link is recreated. When a link is created, it will always be in a connected state, even if the database operation is finished. And since our Apache has started reusing its child processes-that is, the next HTTP request will reuse Apache 's subprocess and reuse the same MySQL link.

MySQL performance optimization