MySQL statement Optimization

1. Establish a benchmark, establish a benchmark, and establish a benchmark! If we need to make a decision, we need to talk about data. What kind of query is the worst? What are the bottlenecks? Under what circumstances will I write a bad query? Benchmark Testing allows you to simulate high-pressure situations and use performance evaluation tools to detect Database Configuration errors. Such tools include supersmack, AB, and SysBench. These tools can directly test your database (such as supersmack) or simulate network traffic (such as AB ). 2. Performance Testing, performance testing, and performance testing! Www.2cto.com, when you are able to establish some high-pressure situations, you need to find out the configuration errors. This is what the performance evaluation tool can do for you. It can help you find bottlenecks in the configuration, whether in memory, CPU, network, hard disk I/O, or above. The first thing you need to do is to enable the slow query log (slow query log) and install mtop. In this way, you can obtain information about malicious intruders. Does the query statement that needs to run for 10 seconds destroy your application? These guys will show you how their query statements are written. After finding slow query statements, you need to use MySQL tools, such as EXPLAIN, show status, and show processlist. They will tell you where resources are consumed and where the query statement is defective. For example, if a query statement with three join subqueries is sorted in memory, or on the hard disk. Of course, you should also use evaluation tools such as top, procinfo, and vmstat to obtain more system performance information. 3. Reduce Your schema before you start writing query statements, you need to design the schema. Remember, the space required to load a table into memory is roughly the size of the number of rows * a row. Unless you think that everyone in the world will register 2 MB and 800 billion times on your website, you do not need to use BITINT as your user_id. Similarly, if a text column is of a fixed size (for example, a US zip code, usually in the form of a "XXXXX-XXXX"), using VARCHAR will add extra bytes to each line. Www.2cto.com some people disagree with database standardization. They say this will form a very complex schema. However, proper normalization will reduce redundant data. (Proper normalization) means sacrificing a little performance in exchange for less footprint as a whole, which is very common in computer science. The best method is IMO, that is, to normalize the data first, and then normalize the data if the performance is needed. Your database will be more logical and you do not need to optimize it too early. (Note: I am not very familiar with this paragraph. It may be wrong. Please correct it .) 4. Split your table. Generally, some tables have only some columns and you often need to update them. For example, for a blog, you need to display the title (such as the list of recent articles) in many different places, and only display the summary or full text on a specific page. Horizontal Vertical Split is very helpful: create table posts (id int unsigned not null AUTO_INCREMENT, author_id int unsigned not null, title varchar (128), created timestamp not null, primary key (id); www.2cto.com create table posts_data (post_id int unsigned not null, teaser text, body text, primary key (post_id )); the preceding schema optimizes the read data. Frequently accessed data is stored in one table, and infrequently accessed data is stored in another table. After being split, less frequently accessed Data occupies less memory. You can also optimize data writing. Frequently updated data is stored in one table, but not updated in another table. This makes the cache more efficient, because MySQL does not need to remove unupdated data from the cache. 5. Do not overuse the artificial primary key, because they make fewer schema changes. If we store the geographic information in a table based on the U.S. zip code, if the zip code system suddenly changes, then we will have a lot of trouble. On the other hand, using natural key is sometimes great. For example, when we need to join multiple-to-many Relational Tables, we should NOT do this: create table posts_tags (relation_id int unsigned not null AUTO_INCREMENT, post_id int unsigned not null, tag_id int unsigned not null, primary key (relation_id), unique index (post_id, tag_id); www.2cto.com artificial key is completely redundant, in addition, the number of post-tag relationships is limited by the system maximum value of integer data. Create table posts_tags (post_id int unsigned not null, tag_id int unsigned not null, primary key (post_id, tag_id); 6. it is important to learn the quality of the index you choose. Otherwise, the database may be damaged. For those who have not yet studied the database, the index can be considered as hash Sorting. For example, if we use the query statement SELECT * FROM users WHERE last_name = 'goldstein ', and last_name has no index, the DBMS will query each row to see if it is equal to "Goldstein ". The index is usually B-tree (there are other types), which can speed up the comparison. You need to add an index to the select, group, order, and join columns. Obviously, the space required for each index is proportional to the number of rows in the table, so the more indexes, the more memory occupied. In addition, indexes also affect data writing because the corresponding indexes are updated each time data is written. You need to take a balance point, depending on the needs of each system and the implementation code. 7. SQL is not C. It is a classic process language. For a programmer, C is also a trap, the mistake is that SQL is also a process language (of course, SQL is neither a functional language nor object-oriented ). Instead of operating on data, you need to imagine a group of data and the relationship between them. The usage of subqueries is often incorrect. Www.2cto.com SELECT. id, (select max (created) FROM posts WHERE author_id =. id) AS latest_postFROM authors a because this subquery is coupled, The subquery should use external query information, and we should use join instead. SELECT. id, MAX (p. created) AS latest_postFROM authors aINNER JOIN posts p ON (. id = p. author_id) group by. id8. understand that your engine MySQL has two storage engines: MyISAM and InnoDB. They have their own performance characteristics and considerations. In general, MyISAM is suitable for reading a lot of data, while InnoDB is suitable for writing a lot of data, but in many cases it is the opposite. The biggest difference is how they process the COUNT function. MyISAM caches table meta-data, such as the number of rows. This means that COUNT (*) does not consume much resources for a well-structured query. There is no such cache for InnoDB. For example, if you have a SELECT * FROM users LIMIT 5 or 10 Statement and run select count (*) FROM users LIMIT 5 or 10, for MyISAM to be completed quickly, and for InnoDB it takes the same time as the first statement. MySQL has the SQL _CALC_FOUND_ROWS option, which tells InnoDB to calculate the number of rows when running the query statement, and then obtain it from SELECT FOUND_ROWS. This is unique to MySQL. However, using InnoDB is sometimes necessary and you can obtain some functions (such as row locking and stord procedure ). The MySQL shortcut for www.2cto.com 9. MySQL provides many extensions for ease of use. For example, INSERT... SELECT, INSERT... On duplicate key update and REPLACE. I don't hesitate to use them because they are very convenient and can play a good role in many cases. However, MySQL also has some dangerous keywords and should be used less. For example, insert delayed indicates that MySQL does not need to INSERT data immediately (for example, when writing logs ). However, if the data volume is high, insertion may be delayed indefinitely, resulting in the insertion queue being full. You can also use the index prompt of MySQL to identify which indexes need to be used. MySQL runs well most of the time. However, if the schema design is poor or the statements are not well written, MySQL may perform poorly. Author: wuyueyul.pdf