1. Optimize your query for the query
Most MySQL servers have query caching enabled. This is one of the most effective ways to improve performance, and this is handled by the MySQL database engine. When many identical queries are executed multiple times, the results of these queries will be placed in a cache, so that subsequent identical queries will directly access the cached results without operating the table.
The main problem here is that for programmers, this matter is easily overlooked. Because, some of our query statements will make MySQL not use the cache.
Please see the following example:
// The query cache is not enabled
$ r = mysql_query ("SELECT username FROM user WHERE signup_date> = CURDATE ()");
// Open query cache
$ today = date ("Y-m-d");
$ r = mysql_query ("SELECT username FROM user WHERE signup_date> = '$ today'");
The difference between the above two SQL statements is CURDATE (). MySQL's query cache has no effect on this function. Therefore, SQL functions such as NOW () and RAND () or other such functions will not enable query caching, because the return of these functions will be variable and variable. So, all you need is to replace MySQL functions with a variable to enable caching.
2. Learn to use EXPLAIN
Using the EXPLAIN keyword allows you to know how MySQL processes your SQL statements.
select id, title, cate from news where cate = 1
Found that the query is slow, and then increase the index on the cate field, it will speed up the query
3. Use LIMIT 1 when there is only one row of data
Sometimes you only need one piece of data when you query the table, please use limit 1.
4. Use the index correctly
The index is not necessarily the only key or unique field. If there is a certain field in your table that you will always use for searching, photographing, and conditions, then please index it.
5. Don't ORDER BY RAND ()
A random query with very low efficiency.
6. Avoid SELECT *
The more data read from the database, the slower the query will become. And, if your database server and WEB server are two independent servers, this will also increase the load of network transmission. You must develop a good habit of taking whatever you need.
7. Use ENUM instead of VARCHAR
The ENUM type is very fast and compact. In fact, it saves TINYINT, but its appearance is displayed as a string. In this way, using this field to do some option lists becomes quite perfect.
If you have a field, such as "gender", "country", "ethnicity", "status" or "department", you know that the values of these fields are limited and fixed, then you should use ENUM instead of VARCHAR.
8. Use NOT NULL
Unless you have a very specific reason to use NULL values, you should always keep your fields NOT NULL. This may seem a bit controversial, please read on.
First, ask yourself how big is the difference between "Empty" and "NULL" (if it is INT, it is 0 and NULL)? If you think there is no difference between them, then you should not use NULL. (Did you know? In Oracle, NULL and Empty strings are the same!)
Don't assume that NULL does not require space, it requires additional space, and, when you compare, your program will be more complicated. Of course, this is not to say that you cannot use NULL. The reality is very complicated. In some cases, you need to use NULL values.
The following excerpt from MySQL's own documentation
"NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte."
9. Save IP address as UNSIGNED INT
Many programmers will create a VARCHAR (15) field to store the IP in the form of a string instead of an integer IP. If you use integer to store, only 4 bytes are needed, and you can have fixed-length fields. Moreover, this will bring you advantages in queries, especially when you need to use such WHERE conditions: IP between ip1 and ip2.
We must use UNSIGNED INT because the IP address will use the entire 32-bit unsigned integer
10. A fixed-length table will be faster
If all fields in the table are "fixed length", the entire table will be 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, then this table is not a "fixed-length static table", so that the MySQL engine will use another method to deal with.
Fixed-length tables will improve performance, because MySQL will search faster, because these fixed lengths are easy to calculate the offset of the next data, so the reading will naturally be fast. And if the field is not fixed-length, then every time you want to find the next item, you need the program to find the primary key.
Also, fixed-length tables are easier to cache and rebuild. However, the only side effect is that a fixed-length field will waste some space, because a fixed-length field will allocate so much space whether you use it or not.
11. Vertical split
"Vertical split" is a method to turn the tables in the database into several tables by column, which can reduce the complexity of the table and the number of fields, so as to achieve the purpose of optimization. It should be noted that the tables formed by these split fields, you will not go to Join them frequently, otherwise, such performance will be worse than that without splitting, and it will be an extreme decline .
12. Split large DELETE or INSERT statements
If you want to execute a large DELETE or INSERT query on an online website, you need to be very careful to avoid your operation to stop your entire website from responding. Because these two operations will lock the table, once the table is locked, no other operations can enter.
Apache will have many child processes or threads. Therefore, it works quite efficiently, and our server does not want to have too many child processes, threads, and database links, which is a very large thing that takes up server resources, especially memory.
If you lock your table for a period of time, such as 30 seconds, then for a site with high traffic, the accumulated access processes / threads, database links, and the number of open files in these 30 seconds may not only be It will only let you crash the web service Crash, and may also cause your entire server to hang up immediately.
13. The smaller the column, the faster
For most database engines, hard disk operation may be the most significant bottleneck. So, making your data compact will be very helpful in this situation because it reduces access to the hard drive.
14. choose the right storage engine
There are two storage engines MyISAM and InnoDB in MySQL, each engine has advantages and disadvantages.
MyISAM is suitable for some applications that require a lot of queries, but it is not very good for a large number of write operations. Even if you just need to update a field, the entire table will be locked, and other processes, even the reading process, cannot operate until the reading operation is completed. In addition, MyISAM is extremely fast for calculations such as SELECT COUNT (*).
The trend of InnoDB will be a very complicated storage engine, and for some small applications, it will be slower than MyISAM. He is that it supports "row lock", so when there are more write operations, it will be better. And, he also supports more advanced applications, such as: transactions.
