Off-topic: a system on-line, run the performance bottleneck for many reasons, to conduct system optimization is a complex task, because you have to understand all aspects of the system, such as your hardware resources, your network and so on. As a development officer, mainly responsible for applying procedures of Kai Fa, as the operation and maintenance personnel responsible for application deployment and monitoring, application Mei You may sometimes, but deployed to online Zhihou, including database Lianjiechaoshi, send a request Scoket Cuowu and other weird problems, and this requires a good communication between the two sides, while both sides try to achieve their optimization.
Practice shows that, as a developer, optimization of the database should have some understanding, because it's Foundation as information support and core, Wang Wang yes an important aspect of system bottlenecks.
The database is a system that itself has a number of configuration parameters, these configuration parameters in different environments should have a different configuration, different configurations that will bring a different performance, for the MySQL database server, the most important parameters for configuration in the installation directory my.ini file, which the two most important parameters and variables are key_buffer_size table_cache, these two values you need to configure the machine's hardware resources and database tables as well as your database operations to be carried out to make a decision, another of this configuration file there are many other configurations, the need to make decisions based on the actual situation.
Sometimes may cause system bottlenecks related to your hardware resources, such as the transmission performance of disk, CPU processing power, memory size. Of course, as the production environment, server, get the current server configuration is concerned, these are not a problem, but we need to know it is a factor causing the performance bottleneck.
The operating system you use to play the best performance on the MySQL also has a significant impact, general Internet applications, we often use the Linux system, but to pay attention to the best version with the Linux kernel after version 2.4, because the linux before version of the file size of the maximum support 2G, if the database file larger than 2G, there is a problem.
MySQL server to have a query optimizer, and its duty is to search for an optimal SQL query program, the average single-table and the data field and only a few inquiries inquiries programs often lead to less, but a complex sql query will be cause a variety of query options, MySQL query optimizer would need to assess the optimal number of options in the program, which takes some time, generally, the optimization program to search for less, it compiles a query takes more time less. On the other hand, because the optimizer skips some plans, it may miss an optimal plan, so sometimes they need a reasonable compromise under the circumstances. This work on the MySQL optimizer, the developer, I think for the time being can not understand, and with the MySQL server constantly upgrade and improve its optimization will be better.
In fact, the majority of developers, direct contact is the optimization of SQL statements for SQL optimization, the most concern is query optimization, the basic principle is: to optimize data access. Query performance with low basic reason is to access the data too much, often Tongguojianshao data access ways of boosting performance, although sometimes inevitable Chaxun lot of data, but this is the actual application Program uncommon Therefore:
1, to determine whether your application needs access to more than a column, for example, you select * from tblName, do not need not to check out (no one does not know, but I see that there is such a written program).
2, to determine whether you need to access the data server requests if you do not need the data first, and then discarded in the application, this will result in an additional burden on the server, increasing the network overhead and CPU memory resources, when application procedures and the database server is not on the same host, the network overhead will be more large (no do not know, but I see there are so many programs written in).
When you get that they need the number of rows and columns, then the query should start thinking whether you check too many rows, the query is very simple to measure the indicators is:
1. Execution time;
2. Check the number of rows;
Execution time: often because you checked the number of lines caused by too much, but it may be related to your hardware environment to a certain extent, we do not over-emphasize this one, but you must pay attention to the execution time of those who are affected factors.
Check the number of lines: the ideal situation is to return the rows and check the line as much, but almost impossible in practical applications, for example, many multi-table join query can not return to the line and check the line as much. Typically, the inspection line and return line ratio: between 1:1 to 10:1 is normal.
We have several access methods for each query to return the required data line
1. Full table scan
2. Index Scan
3. Range scan
4. The only index scan
5. Constant scanning these types of data access methods to access the speed gradually increased.
Write SQL statements in MySQL, please always use the EXPLAIN statement to analyze the query, in particular, more frequent operation query, use the EXPLAIN statement is proposed to analyze.
For example: EXPLAIN SELECT * FROM USER;
After the implementation of such a statement, MySQL server will return a detailed description of implementation, according to description, we can see that the efficiency of this statement, for example, if the access type type: all, this means that a full table scan, you're the best Add an index to avoid full table scan. Sometimes, after you add the index is also not a good access type, you have to consider is not the database tables are designed to be a very reasonable.
Sometimes you may need to optimize reconstruction of your query, the complexity of your query split into a few simple queries, according to the traditional concept, we are as little as possible to the database query, but the network communication, clear much better than before, but the real application and database servers are usually deployed network, including dedicated channels, the network connection is not a problem, so do not be afraid multiple queries, MySQL on a simple query response is very good. But you also know, connect to the database server is much slower than the query execution, so you have to make reasonable under the circumstances to compromise, to see if the necessary split a complex query.
Network on a lot about the SQL statement optimization principles, I find that some optimization methods on Oracle application, but may not apply to MySQL, which could yes because the MySQL optimizer optimize Guize caused by different.
Developers who want to learn more about SQL optimization knowledge database, DBA, or an understanding of operation and maintenance of the database server optimized for more knowledge, this is two forces combined to develop a basis for high-performance application systems and security.
And all of this constantly growing practice.