MySQL Database Optimization of some personal thought

2010-08-06  来源:本站原创  分类:Database  人气:173 

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.
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.

  • MySQL Database Optimization of some personal thought 2010-08-06

    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 de

  • MySQL database optimization script 2010-07-12

    Very good script with a mysql database optimization, relative, a bash script to be more than, precisely, is from a different aspect of the state evaluation of the mysql Optimization a good

  • [Change] Xiangjie MySQL Database Optimization 2010-06-22

    Original link: To deal with the analyze, on a regular basis to deal ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [, tbl_name] ... Defined on the table of analyze table ta

  • Optimization mysql database optimization 2011-01-11

    In the overall system is running, the pressure of MySQL database server is the biggest, not only take up a lot of memory and cpu resources and occupies most of the disk io resources, even in the official PHP claimed that 80% of PHP scripts time waiti

  • MySQL Database Optimization Tips Collection 2011-03-29

    First, what we can and should be optimized? Hardware Operating system / software library SQL Server (setup and queries) Application programming interface (API) Applications Second, optimizing the hardware If you need a large database tables (> 2G), y

  • MySQL database optimization practice 2011-07-05

    Transfer: Recently, we have compiled some of Percona, Linux, Flashcache, hardware optimized experience for everyone to share: Hardware 1 open BBWC RAID card has a write cache (Battery Ba

  • MySQL database optimization simple and practical 2010-03-25

    1, regular analysis of the table and check table <br /> analysis table syntax is as follows: Quote ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [, tbl_name] ... The above statement is used to analyze the distribution and storage form of keywo

  • [Change] MySQL database optimization simple and practical method 2010-04-07

    Transfer from: 1, regular analysis of the table and check table of table syntax is as follows: Quote ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [, tbl_name] ... Above statement for analysis and storage tab

  • Mysql database optimization study of the two index optimization (a) 2011-05-29

    Reproduced, please credit: Basic knowledge of the index is to help index and search the data in a MySQL data structure, which is the key to achieving high performance, but people often forget or misunderstand it,

  • Mysql database optimization methods (transfer) 2010-12-10

    1, select the most appropriate field properties MySQL support can be very good access to large amounts of data, but generally speaking, the smaller tables in the database, run queries on it, there would be faster. Therefore, when you create the table

  • MYSQL database optimization methods 2010-12-02

    1, select the most appropriate field properties, the definition of the field to minimize the length of the field is set as NOT NULL, such as 'provinces, gender', the best set to ENUM 2, using the connection (JOIN) to replace the sub-query: a. Remove

  • mysql database optimization 2010-11-30

    follow the principle: 1. Optimize Your Queries For the Query Cache 2. EXPLAIN Your SELECT Queries 3. LIMIT 1 When Getting a Unique Row 4. Index the Search Fields 5. Index and Use Same Column Types for Joins 6. Do Not ORDER BY RAND () 7. Avoid SELECT

  • MySQL Performance Optimization Xiangjie 2010-04-24

    1. MySQL Performance Optimization Introduction In the Web application architecture, data persistence layer (usually a relational database) is a key core of its performance of the system has a very important influence. MySQL is the most widely used op

  • Share the classic limit of MySQL query optimization 2011-01-13

    The following article mainly for MySQL limit the query to optimize the specific content of the introduction, we all know that the MySQL database optimization is very important. Other most commonly used and most need to optimize is the limit. The page

  • [Reserved] MySQL performance optimization 2011-02-23

    Keywords: mysql performance tuning optimization Author: andyao Original link: Please help earn reproduced 1 Introduction In the Web application architecture, data persistence layer (usually a relational datab

  • MySQL database to optimize the specific method described 2011-03-29

    The following article focuses on the practical realization of a simple MySQL database optimization specific methods, and practice what specific steps are worth our attention. The following article describes a simple and practical to optimize the MySQ

  • Optimization of MySQL database server details 2010-08-19

    MySql Server Admin program, in order to use the client program, the program must be running, because the client by connecting to the server to access the database. Let us to the server system variables and state variables as the basis, optimizing our

  • MySQL database performance optimization of the eight methods 2010-09-09

    MySQL database performance optimization of the eight methods From: 1, select the most appropriate field properties MySQL is well supported by a large amount of data access, but generally

  • Optimization of mysql database ibdata 2010-09-16

    Preparation conditions: 1. Prohibition mysql autostart update-rc.d-f mysql disable Optimization steps are as follows: 1. My.cnf file add the following content innodb-file-per-table = 1 2. Mounted hard drive 2.1 off a hard drive to increase 2.2 boot p

  • mysql database engines: MyISAM and InnoDB (performance optimization) 2011-08-02

    mysql optimize the series (a) Mysql database engine performance tests Mysql database, the most common of the two engines is innordb and myisam. Innordb myiasm powerful than a lot of features, but innordb much worse performance than myisam, if your si