[SQL] MySQL Performance Optimization

[SQL] MySQL performance optimization 1. Optimize the query cache. Most MySQL servers have enabled the query cache. This is one of the most effective ways to improve performance, and it is processed by the MySQL database engine. When many identical queries are executed multiple times, these query results are stored in a cache, the cache results are directly accessed for the same query in the future without having to operate the table. 2. EXPLAIN your SELECT query uses the EXPLAIN keyword to let you know how MySQL processes your SQL statement. This helps you analyze the performance bottleneck of your query statement or table structure. The EXPLAIN query results also show you how your index primary key is used and how your data tables are searched and sorted ...... And so on. 3. use LIMIT 1 when only one row of data is required. When you query a table, you know that only one result is returned, but you may need to remove the fetch cursor, or you may check the number of returned records. In this case, adding LIMIT 1 can increase performance. In this way, the MySQL database engine will stop searching after finding a piece of data, rather than continuing to query the next piece of data that matches the record. 4. Creating an index for a search field is not necessarily a primary key or a unique field. If a field in your table is always used for search, create an index for it. 5. when you Join a table, use an equivalent type of example and index it. If your application has many JOIN queries, you should confirm that the Join fields in the two tables are indexed. In this way, MySQL will launch a mechanism to optimize the Join SQL statement for you. In addition, these fields 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 their indexes. For those STRING types, the same character set is required. (The character sets of the two tables may be different.) 6. Do not use order by rand () to disrupt the returned data rows? Pick a random data? I really don't know who invented this method, but many new users like it. But you do not know how terrible the performance is. If you really want to disrupt the returned data rows, you have N methods to achieve this purpose. This only causes an exponential decline in the performance of your database. The problem here is that MySQL will have to execute the RAND () function (which consumes CPU time), and this is to record rows for each row of records, and then sort them. Even if you use Limit 1, it will not help (because you want to sort it) 7. Avoid the more data you read from the database by using SELECT *, the slower the query. In addition, if your database server and WEB server are two independent servers, this will increase the network transmission load. Therefore, you should develop a good habit of taking what 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 (UNSIGNED is recommended ), and set the AUTO_INCREMENT flag automatically added. Even if your users table has a primary key field "email", you should not make it a primary key. When the VARCHAR type is used, the primary key performance decreases. In addition, in your program, you should use the table ID to construct your data structure. In addition, some operations in the MySQL Data Engine require primary keys. In these cases, the performance and settings of primary keys become very important, such as clusters, partitions ...... 9. Using ENUM instead of varchar enum is fast and compact. In fact, it stores TINYINT, but its appearance is displayed as a string. In this way, it is quite perfect to use this field for some option lists. 10. getting advice from procedure analyse () will allow MySQL to help you analyze your fields and actual data, and give you some useful suggestions. Only when the table has actual data can these suggestions become useful, because to make some big decisions, we need data as the basis. For example, if you create an INT field as your primary key, but there is not much data, procedure analyse () suggests that you change the field type to MEDIUMINT. Or you use a VARCHAR field. Because there is not much data, you may get a suggestion that you change it to ENUM. These suggestions may be due to insufficient data, so decision-making is not accurate enough. In phpmyadmin, you can click "Propose table structure" to view the suggestions 11. use not null as much as possible. Unless you use NULL for a special reason, you should always keep your field not null. This seems a bit controversial. Please refer to it. First, ask yourself what is the difference between "Empty" and "NULL" (if it is an INT, It is 0 and NULL )? If you think there is no difference between them, you should not use NULL. (Do you know? In Oracle, the strings of NULL and Empty are the same !) Do not think that NULL requires no space. It requires additional space. In addition, when you compare, your program will be more complex. Of course, this does not mean that you cannot use NULL. The reality is very complicated. In some cases, you still need to use NULL values. 12. Prepared Statements is similar to a stored procedure. It is a collection of SQL statements running in the background. We can obtain many benefits from using prepared Statements, whether it is a performance issue or a security issue. Prepared Statements can check some variables that you have bound to protect your program against "SQL injection" attacks. Of course, you can also manually check your variables. However, manual checks are prone to problems and are often forgotten by programmers. When we use some frameworks or ORM, this problem will be better. In terms of performance, when the same query is used multiple times, this will bring you considerable performance advantages. You can define some parameters for these Prepared Statements, while MySQL only parses them once. Although the latest version of MySQL uses binary data to transmit Prepared Statements, this makes network transmission very efficient. 13. normally, when you execute an SQL statement in your script, your program will stop there until no such SQL statement is returned, then your program continues to run. You can use unbuffered queries to change this behavior. 14. Saving the IP address as an unsigned int many programmers will create a VARCHAR (15) field to store the IP address in string format instead of an integer IP address. If you use an integer to store data, you only need 4 bytes and you can have a fixed length field. In addition, this will bring you query advantages, especially when you need to use the WHERE condition: IP between ip1 and ip2. We must use the unsigned int, because the IP address will use the entire 32-bit UNSIGNED integer. 15. A table with a fixed length will be faster. If all the fields in the table are "fixed length", the whole table will be considered as "static" or "fixed-length ". For example, the table does not have the following types of fields: VARCHAR, TEXT, BLOB. As long as you include one of these fields, this table is not a "static table with a fixed length". In this way, the MySQL engine will use another method for processing. A fixed-length table improves performance because MySQL searches faster, because these fixed-length tables are easy to calculate the offset of the next data, so reading will naturally be fast. If the field is not fixed, the program needs to find the primary key for each query. In addition, tables with a fixed length are more easily cached and rebuilt. However, the only side effect is that a field with a fixed length will waste some space, because a field with a fixed length will be allocated so much space no matter you use it. 16. vertical segmentation is a way to convert 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 goal of optimization. 17. split large DELETE or INSERT statements. If you need to execute a large DELETE or INSERT query on an online website, you need to be very careful, avoid stopping your website from performing operations. Because these two operations lock the table, once the table is locked, other operations cannot be performed. Apache has many sub-processes or threads. Therefore, it works very efficiently, and our server does not want to have too many sub-processes, threads, and database connections, which greatly occupy server resources, especially memory. 18. Smaller columns will be faster. For most database engines, hard disk operations may be the biggest bottleneck. Therefore, it is very helpful to compact your data because it reduces access to the hard disk. 19. Selecting the Correct storage engine has two storage engines MyISAM and InnoDB in MySQL. Each engine has advantages and disadvantages. MyISAM is suitable for some applications that require a large number of queries, but it is not very good for a large number of write operations. Even if you only need to update a field, the entire table will be locked. Other processes, even the read process, cannot operate until the read operation is complete. In addition, MyISAM is extremely computation over select count. The trend of InnoDB is that it is a very complex storage engine. For some small applications, it will be slower than MyISAM. It supports "Row locks", so it will be better when there are many write operations. In addition, it supports more advanced applications, such as transactions. 20. Using an Object Relational Mapper Using ORM (Object Relational Mapper), you can gain reliable performance growth. All the things that An ORM can do can also be written manually. However, this requires a high-level expert. The most important thing about ORM is "Lazy Loading". That is to say, it is only necessary to remove the value. However, you also need to be careful about the side effects of this mechanism, because it is likely that the performance will be reduced by creating many small queries. ORM can also package your SQL statements into a transaction, which is much faster than executing them separately. 21. Be careful when "permanent link" and "permanent link" is used to reduce the number of times the MySQL link is re-created. When a link is created, it will always be connected, even if the database operation is completed. Moreover, since Apache began to reuse its sub-processes-that is to say, the next HTTP request will reuse Apache sub-processes and reuse the same MySQL link.