MYSQL performance tuning [Summary]

2011-08-16  来源:本站原创  分类:Database  人气:178 

1, sub-library sub-table

Obviously, a main table (the table is very important, for example, the user table) unlimited growth is bound to seriously affect performance, and sub-sub-library table is a very good solution, that is, performance optimization approach, the present case is We have more than 10 million records a user table members, the query is very slow, and colleagues to practice is to hash table 100, respectively, from members0 to members99, then mid distribute records to these tables, the cattle force of the code is probably like this:



        //echo "CREATE TABLE db2.members{$i} LIKE db1.members<br>";

 "INSERT INTO members{$i}
 SELECT * FROM members WHERE mid%100={$i}



2, stopping the machine to modify mysql table structure

The same table, or members, pre-designed table structure is not reasonable, as the database is continually running, the redundant data is a huge growth and colleagues used the following method to deal with:

First create a temporary table:

/* Create temporary table */

 members_tmp LIKE

Then modify members_tmp the table structure for the new structure, then use the above that for loop to export the data, because 10 million of the data once exported is not right, mid is the primary key, a range of a range of guidance, is basically an export 50,000 right , omitted here, and then rename the new table to replace up:

/* This is a rather classic statement */

 members TO
members_tmp TO

Is so basic can be done without loss, with no downtime to update the table structure, but in fact the table is locked during RENAME, so little choice when online operation is a skill. After this operation, making more than the original 8G table, suddenly became more than 2G.

It was also mentioned in the mysql field type float occurs when the strange phenomenon, that is, pma can not see digital as a condition to the query, thanks zj fresh students to share.

6 useful MySQL statements

1. Calculated the number of years

You want to calculate the birthday person how old.

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y' ) + 0;

(2) the difference between the two time

Made the difference between two datetime values. Assuming dt1 and dt2 are datetime type, the format 'yyyy-mm-dd hh: mm: ss', then the number of seconds difference between them is:


Divided by 60 is the difference between the number of minutes divided by 3600 is the difference between the number of hours divided by 24 is the difference between the number of days.

3 shows a column appeared in the value of N times


FROM tbl



4 calculation of days between two dates

In addition to the so-called working day is Saturday, Sunday and holidays.


FROM calendar



AND holiday=0;

5 primary key lookup table

SELECT k.column_name

FROM information_schema.table_constraints t

JOIN information_schema.key_column_usage k

USING (constraint_name,table_schema,table_name)

WHERE t.constraint_type= 'PRIMARY KEY'

AND t.table_schema= 'db'

AND t.table_name=tbl'

6. See how much you have several libraries


table_schema AS 'Db Name' ,

Round( Sum ( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Db Size (MB)' ,

Round( Sum ( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)'

FROM information_schema.tables

GROUP BY table_schema ;

Today, the operation of the database application performance is increasingly becoming the bottleneck, and this is particularly evident for Web applications. On database performance, this is not just DBA need to worry about things, and this is our programmers need to pay attention to things. When we go to design a database table structure, the operation database (look-up table, especially when the SQL statement), we all need to pay attention to the performance of data manipulation. Here, we would not have said more to optimize the SQL statement, but just for this Web application up MySQL databases. Hope that following these optimization tips useful to you.

1 for the query cache to optimize your query

Most of the MySQL query cache is enabled on both servers. This is to improve one of the most effective way, and this is the MySQL database engine to handle. When many of the same query is executed many times, these results will be placed in a cache, so subsequent queries do not have the same operating table and direct access to the cached result.

The main problem here is that, for programmers, this thing is very easily overlooked. Because some of our query MySQL will not use the cache. Consider the following example:

// The query cache does not turn on

$r = mysql_query( "SELECT username FROM user WHERE signup_date >= CURDATE()" );

// Opens the 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 for this function does not work. So, like NOW () and RAND (), or other SQL functions such as these will not open the query cache, because these functions will return uncertain variable. So, you need to do is to use a variable instead of MySQL functions to enable caching.

2. EXPLAIN to your SELECT query

Use EXPLAIN keyword lets you know how to handle your MySQL is a SQL statement. This can help you analyze your query or table structure of the performance bottleneck.

EXPLAIN results will tell you that your primary key is how to use the index, your table is to be searched and sorted ... ... so on and so on.

Pick one of your SELECT statement (recommended selection of the most complex, multiple table joins), the keyword EXPLAIN added to the front. You can use phpmyadmin to do this thing. Then you will see a form. Following this example, we forget to add a group_id index, and there are table join:

MYSQL performance tuning [Summary]

When we add the index after the group_id field:

MYSQL performance tuning [Summary]

We can see that the first search of the 7883 results show a row, then a search of only two tables 9 and 16 lines. Allows us to see the rows column to find potential performance problems.

3 When one line of data using LIMIT 1

When you query a table sometimes, you already know the result will only be one outcome, but because you may need to fetch the cursor, or you might go check the number of records returned.

In this case, coupled with LIMIT 1 can increase performance. Such as, MySQL database engine will stop after finding a data search, rather than continue to check back a little line under the record data.

The following example, only to find to see if there are "China" user, it is clear, the latter is more efficient than the previous. (Note that the first is Select *, the second is Select 1)

// Inefficient :

$r = mysql_query( "SELECT * FROM user WHERE country = 'China'" );

if (mysql_num_rows( $r ) > 0) {

// ...


// Efficient :

$r = mysql_query( "SELECT 1 FROM user WHERE country = 'China' LIMIT 1" );

if (mysql_num_rows( $r ) > 0) {

// ...


4 construction of the index for the search field

Index is not necessarily a primary key or unique to the field. If your table, you always have to have a field often used for search, then please be indexed it.

MYSQL performance tuning [Summary]

You can see from the figure that the search string "last_name LIKE 'a%'", an index is built, one is no index, the performance difference of about 4 times.

In addition, you should also need to know what kind of search can not use a normal index. For example, when you need a big article in the search for a word, such as: "WHERE post_content LIKE '% apple%'", the index may not be meaningful. You may need to use MySQL full-text index or an index their own (for example: what search words or Tag)

5, when used in the Join Table equivalent type of cases, and index

If your application has many JOIN query, you should confirm Join two tables in the field is to be built over the index. This, MySQL will start for you within the SQL statement to optimize Join mechanism.

Moreover, these were used to Join the field, should be the same type. For example: If you want an INT field DECIMAL field and Join together, MySQL can not use their index. For those who STRING type, but also have the same character set the job. (Two tables of character sets may be different)

// Look in your State company

$r = mysql_query("SELECT company_name FROM users

LEFT JOIN companies ON (users.state = companies.state)

WHERE = $user_id ");

// Two state index fields should be built , And be very type, the same character set .

6 Do not ORDER BY RAND ()

Want to disrupt the rows returned? Pick a random data? I really do not know who invented this use, but like so many novice to use. But you really do not know how terrible performance problems.

If you really want to upset the rows of data returned, you have N ways to achieve this goal. This database allows you to use only the performance of exponential decline. The problem here is: MySQL will have to perform the RAND () function (a waste of CPU time), and this is to remember to record each line of the line, and then sort them. Even if you use the Limit 1 will not help (because you want to sort)

The following example is a randomly selected record

// Millions don't do :

$r = mysql_query( "SELECT username FROM user ORDER BY RAND() LIMIT 1" );

// This would be better to :

$r = mysql_query( "SELECT count(*) FROM user" );

$d = mysql_fetch_row( $r );

$rand = mt_rand(0, $d [0] - 1);

$r = mysql_query( "SELECT username FROM user LIMIT $rand, 1" );

7 Avoid SELECT *

Read from the database the more data, then the query becomes slower. And, if your database server and WEB server is two separate servers, then it will increase network load.

So, what you should cultivate a good habit to get something.

// It is not recommended

$r = mysql_query( "SELECT * FROM user WHERE user_id = 1" );

$d = mysql_fetch_assoc( $r );

echo "Welcome {$d['username']}" ;

// Recommended

$r = mysql_query( "SELECT username FROM user WHERE user_id = 1" );

$d = mysql_fetch_assoc( $r );

echo "Welcome {$d['username']}" ;

8 always set an ID for each table

We should have a database where each table an ID as its primary key, but the best is an INT type (recommended UNSIGNED), and set the AUTO_INCREMENT automatically increases on the sign.

Even if you have a users table primary key called "email" field, you do not let it become the primary key. Use the VARCHAR type to use when the primary key will have performance degradation. In addition, your program, you should use the ID to construct your table data structure.

Moreover, under the MySQL database engine, there are some operations need to use the primary key, in these cases, the primary key and set performance becomes very important, for example, clustering, partitioning ... ...

Here, only one case is an exception, that is, "association table" and "foreign key", that is, the table's primary key, through a number of individual key of the table form. We call this condition is called "foreign key." For example: There is a "student sheet," a student's ID, there is a "curriculum" courses have ID, then the "result table" is the "association table", and its associated tables of students and curriculum, in the results table, student ID and course ID called "foreign key" its common form the primary key.

9. VARCHAR instead of using ENUM

ENUM type is very fast and compact. In fact, its preservation is TINYINT, but its appearance on the show as a string. In this way, use this field to do some of the options list has become quite perfect.

If you have a field, such as "sex", "state" and "nation", "state" or "department", you know the values of these fields is limited and fixed, then you should not use ENUM VARCHAR.

MySQL also has a "recommended" (see Article) tells you how to re-organize your table structure. When you have a VARCHAR field, this proposal will tell you to change it to ENUM type. Use PROCEDURE ANALYSE () You can get related proposals.

10 from PROCEDURE ANALYSE () to obtain suggestions

PROCEDURE ANALYSE () MySQL will help you to analyze your field and the actual data, and will give you some useful suggestions. Only the table with actual data, these recommendations will be useful, as do some big decisions is the need for data as a basis.

For example, if you create an INT field as your primary key, but not too much data, then, PROCEDURE ANALYSE () would suggest that you type into this field MEDIUMINT. Or you use a VARCHAR field, because the data much, you might get a let you put it into ENUM recommendations. These recommendations are possible because the data are not enough, so the decision did not enough potential.

In phpmyadmin, you can view the table, click on "Propose table structure" to see these proposals

MYSQL performance tuning [Summary]

We must note that these are only suggestions, and only when your table of data rises, these recommendations will become accurate. Always remember, you are the final decision of the people.

11. As far as possible the use of NOT NULL

Unless you have a very special reason to use NULL values, you should always get to keep your fields NOT NULL. It looks like a little controversy, please read on.

First, ask yourself "Empty" and "NULL" how much difference (if it is INT, that is, 0 and NULL)? If you feel that there is no difference between them, then you do not use NULL. (Did you know? In Oracle, NULL and Empty string is the same!)

Do not think NULL do not need space, the need for additional space, and, when you compare your program will be more complicated. Of course, there is not to say you can not use NULL, and the reality is very complex, still in some cases, you need to use NULL values.

The following is taken 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."

12. Prepared Statements

Prepared Statements like stored procedures, running in the background is a collection of SQL statements, we can get many benefits with prepared statements, whether performance problems or security issues.

Prepared Statements can check some of the good you bind variables, so you can protect your application will not be "SQL injection" attacks. Of course, you can also check your manual to these variables, however, error-prone manual inspection, and it is often forgotten by the programmer. When we use some framework or ORM, when this problem will be better.

In terms of performance, when a query is to use the same number of times, it will bring you significant performance advantages. You can give them some of the parameters defined in Prepared Statements, and MySQL will parse time.

Although the latest version of MySQL Prepared Statements in the transfer situation is to use binary, so this will make the network very efficiently.

Of course, there are some cases, we need to avoid using Prepared Statements, because it does not support the query cache. However, after the 5.1 version is said to support.

In PHP to use prepared statements, you can view the user manual: mysqli extension or use a database abstraction layer, such as: PDO .

// Create prepared statement

if ( $stmt = $mysqli ->prepare( "SELECT username FROM user WHERE state=?" )) {

// Binding parameters

$stmt ->bind_param( "s" , $state );

// Implementation of

$stmt ->execute();

// Binding results

$stmt ->bind_result( $username );

// Move the cursor

$stmt ->fetch();

printf( "%s is from %s\n" , $username , $state );

$stmt ->close();


13. Unbuffered query

Under normal circumstances, when you are in your script when you execute a SQL statement, your program will not stop there until the return of this SQL statement, then your program and then continue down. You can use unbuffered query to change this behavior.

On this matter, in the PHP documentation has a very good description: mysql_unbuffered_query () function:

"Mysql_unbuffered_query () sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query () does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don't have to wait until the complete SQL query has been performed. "

The above sentence is translated, said, mysql_unbuffered_query () sends a SQL statement to MySQL and not as mysql_query () to automatically fethch and cache the same results. This will be quite a lot of significant memory savings, especially those who will produce a large number of query results, and you do not need to wait until all results are returned, only the first row of data returned, you can begin to immediately start working on the inquiry results.

However, there will be some restrictions. Because you either go all the lines read, or you want to check before making the next call mysql_free_result () clear result. Moreover, mysql_num_rows () or mysql_data_seek () will not be used. So, whether you use unbuffered queries need to be carefully considered.

14. The IP address saved as UNSIGNED INT

Many programmers will create a VARCHAR (15) field to store the string in the form of IP, rather than shaping the IP. If you use plastic to store, only 4 bytes, and you can have a fixed-length fields. And this will bring you the advantage on the query, especially when you need to use such a WHERE condition: IP between ip1 and ip2.

We will need to use the UNSIGNED INT, because the IP address will be used throughout the 32-bit unsigned integer.

But your query, you can use INET_ATON () to convert a string into an integer IP forwarding and use INET_NTOA () to convert an integer to a string IP. In PHP, this is also the function ip2long () and long2ip () .

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id" ;

15 Fixed-length table will be faster

If all the fields in the table are "fixed length", the entire table will be considered "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, then the table would not be "fixed length static table", and such, MySQL engine will use another way to deal with.

Fixed-length table would improve performance, because MySQL will get faster search, because the fixed length is very easy to calculate the offset of the next data, so naturally will soon read. And if the field is not fixed-length, then, each time looking for the next one, then the program needs to find the primary key.

Also, fixed-length tables are also more likely to be cached and reconstruction. However, the only side effect is that fixed-length fields will waste some space, because the length of the field, whether you use do not, he is so much space to allocate.

Using the "vertical split" technology (see below one), you can split your table into a fixed length of two, is a variable length.

16 vertical split

"Vertical split" is a table in the database column into a few tables by the method, so the table can reduce the complexity and number of fields, so as to achieve the purpose of optimization. (Previously, the bank has done the project, seen more than 100 fields in a table, very frightening)

Example 1: in the Users table has a field is the home address, this field is optional, compared, and when you operate the database in addition to personal information, you do not need to read or rewritten frequently this field. So why not put him into another one table as well? So make your table has better performance, we think is not, a lot of time, I am the user table, only the user ID, user name, password, user roles, etc. will be frequently used. Smaller table will always be a good performance.

Example 2: You have called "last_login" field, it will be updated each time a user logs on. However, each update will cause the table's query cache is cleared. So you can put this field into another table, so it will not affect your user ID, user name, user roles kept reading, because the query cache will help you a lot of performance increase.

In addition, you need to note that these are points out of the field formed by the table, you do not regularly go to Join them, otherwise, this performance will be worse than no segmentation, and would be very logarithmic decline.

17 split large DELETE or INSERT statement

If you need an online site up to perform a large DELETE or INSERT queries, you need to be very careful to avoid the operation of your entire site to make your stop accordingly. Because these two operations will lock the table, the table a locked, other operations have not come up.

Apache will have a lot of child processes or threads. Therefore, the work is pretty efficient, and our servers do not want too many sub-processes, threads, and database links, this is a great account of what server resources, especially memory.

If you lock your table for some time, such as 30 seconds, then for a high-traffic sites, this 30 seconds to access the accumulated process / thread, database links, open files, may not only WEB services only let you park Crash, may also make your whole server immediately hung up.

So, if you have a big deal, you must be to break it you, use the LIMIT condition is a good way. Here is an example:

while (1) {

// Only 1000 at a time

mysql_query( "DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000" );

if (mysql_affected_rows() == 0) {

// Did not have deleted, exit !

break ;


// Every time you want to take a break



18. The smaller the column faster

For most of the database engine, the drive operation may be the most significant bottleneck. So, your data will become a compact case is very helpful because it reduces the disk access.

See the MySQL documentation Storage Requirements to view all data types.

If a table column fills only a few (such as the dictionary table, the configuration table), then we have no reason to call the shots using the INT key, use MEDIUMINT, SMALLINT, or TINYINT will be a smaller more economical. If you do not need to record the time, use DATE much better than DATETIME.

Of course, you also need to leave enough headroom, or, later on you do this thing, you will die is difficult to see, see an example of Slashdot (November 6, 2009), a simple ALTER TABLE statement took three more than an hour, because there are sixteen million data.

19 Choosing the right storage engine

There are two in the MySQL storage engine MyISAM and InnoDB, both pros and cons of each engine. Cool shells before the article " MySQL: InnoDB or MyISAM? "discussion and this matter.

MyISAM for applications that require a large number of queries, but for a large number of write operations is not very good. Even if you just need to update a field, the entire table will be locked up, while other processes, the process can not even read operation until the read is complete. In addition, MyISAM for SELECT COUNT (*) such calculations are extremely fast.

InnoDB trend will be a very complex storage engine, for some small applications, it will be slower than MyISAM. He is its support for "line lock", then write more in time, will be better. And he also supports more advanced applications, such as: business.

Here is the MySQL manual

20 using an object-relational mapper (Object Relational Mapper)

Use ORM (Object Relational Mapper), you can get reliable performance were increased. An ORM can do all things, can also be written out manually. However, this requires a high-level experts.

ORM is the most important "Lazy Loading", that is, only when needed to value the real will to do it. But you also need to be careful of side effects of this mechanism, because it is likely going to create many, many small because of the query but will reduce performance.

ORM can also package your SQL statements into a transaction, which is much faster than them separately.

Currently, the personal favorite of PHP ORM is: Doctrine .

21. Be careful "permanent link"

"Permanent link" is intended to reduce the number of times to re-create the MySQL link. When a link is created, it will always be in a state of the connection, even if the database operation is over. And, since we started the Apache child process after its reuse - that is, the next HTTP request will reuse the Apache child process, both use the same MySQL link.

In theory, this sounds very good. But from personal experience (and most people's) point of view, this feature made out of trouble more. Because you have only a limited number of links, memory problems, file handles, and so on.

Moreover, Apache is running parallel in the extreme environment, will create many, many of the process. That is why this "permanent link" mechanism working to bad reasons. Before you decide to use the "permanent link", you need to properly consider the structure of your entire system.

  • MYSQL performance tuning [Summary] 2011-08-16

    1, sub-library sub-table Obviously, a main table (the table is very important, for example, the user table) unlimited growth is bound to seriously affect performance, and sub-sub-library table is a very good solution, that is, performance optimizatio

  • MySQL performance tuning experience the best 21 2010-03-27

    MySQL performance tuning experience the best 21 1. For the query cache to optimize your query MySQL's query cache does not work on this function. So, like CURDATE () NOW () and RAND (), or other SQL functions such as these will not open the query cac

  • Oracle Sql Performance Tuning Summary 2010-10-03

    Oracle Sql Performance Tuning Summary (1) Choose the most efficient sequence table name ( Only rule-based optimizer in the effective ): ORACLE Parser in accordance with the order processing from right to left in the FROM clause of table ,FROM Clause

  • Mysql performance tuning and architecture - notes 2010-11-11

    Login mysql host: mysql-uadm-pbbnd_adm - prompt = "\ \ u @ \ \ h \ \ Y: \ \ m: \ \ r: \ \ m: \ \ s>" - tee = operat.log - Tee helping the copy screen - Prompt prompt marked that which is currently mysql, display account, ip, etc. View the sta

  • MYSQL performance tuning and architecture design study notes book 1 2011-01-23

    "MYSQL performance tuning and architecture books," Ali Baba's famous masterpiece MYSQL DBA Jane sunrise, most recently in reading, Feel very good note of: mysql reading notes 1 1 mysqladmin-u xxxx-p password-h localhost ping Mysqld with the ping

  • Baidu mysql performance tuning ppt 2010-09-14

    Keywords: Mysql, performance optimization, Baidu InfoQ last weekend listening to Bin Baidu engineers to optimize the performance of mysq l speech, although still less than the content so high, but understanding the large amount of data, there is a la

  • Colleagues this afternoon to share the MYSQL performance tuning 2010-11-05

    Afternoon of sharing, co-workers about the next mysql performance optimization on several experiences, it makes sense, posted 1, sub-library sub-table Obviously, a main table (the table is very important, for example, the user table) unlimited growth

  • MySQL Performance Tuning must 25 2011-08-29

    Today, the operation of the database application performance is increasingly becoming the bottleneck, and this is particularly evident for Web applications. On database performance, this is not just DBA need to worry about things, and this is our pro

  • MySQL Performance Tuning must 25 (rpm) 2011-09-02

    Today, the operation of the database application performance is increasingly becoming the bottleneck, and this is particularly evident for Web applications. On database performance, this is not just DBA need to worry about things, and this is our pro

  • MySQL Performance Tuning must 25 (reproduced) 2011-09-13

    Original Address: Today, the operation of the database application performance is increasingly becoming the bottleneck, and this is particularly evident for Web applications. On database performance, this is not jus

  • MYSQL performance tuning Overview - Undercover Personal sorting 2010-10-18

    1, the hardware optimization a> memory - Large memory, large memory bits wide, try not to use the SWAP; b> HDD - 15000RPM, RAID5, raid10 c> CPU - 64-bit, high-frequency, high-cache, high parallelism d> Network - the Gigabit Ethernet standard i

  • MYSQL performance tuning 2011-04-01

    ORIGINAL: 1 business needs <br /> Description: The unreasonable demand of input and output of resources is inversely proportional to the product developers need to make the needs assessment, or do not affect the overal

  • Twenty real MySQL performance tuning Experience Optimization 2010-10-26

    1. For the query cache to optimize your query Most of the MySQL query cache is enabled on both servers. This is to improve one of the most effective way, and this is the MySQL database engine to be processed. When there is a lot of the same query is

  • Performance Tuning Summary 2010-11-10

    1, for the middleware application system for the tomcat, the tomcat as far as possible to deploy higher version (eg tomcat6), and open the nio module tomcat6 2, using its own connection pool tomcat http connection to the distribution, not by hand the

  • mysql performance tuning methods of inspection and 2010-11-17

    This switched: I have been using this database mysql software, more stable work and efficiency is also high. Performance in the face of serious problems, in general, so several possibilities: 1, the index is n

  • MySQL performance tuning experience of the best 20 + section 2010-12-07

    Today, the entire operation of the database application performance is increasingly becoming a bottleneck, and this is particularly evident for Web applications. Performance on the database, it is not just a matter DBA need to worry about, and this i

  • MySQL Performance Tuning Best Practices 21 2010-11-30

    Today, the operation of the database application performance is increasingly becoming the bottleneck, and this is particularly evident for Web applications. On database performance, this is not just DBA need to worry about things, and this is our pro

  • The system variables to set up the MySQL performance tuning 2011-09-01

    How to set the MySQL system variables to tune performance? (1), back_log: Requires some number of connections MySQL can. When the main MySQL thread in a very short period of time to get a lot of connection requests, which play a role, then the main t

  • The autocommit mysql performance tuning 2010-06-16


  • MySQL performance tuning experience of the best 21 2011-08-15 1 for the query cache to optimize your query 2.EXPLAIN your SELECT query 3 When one line of data using LIMIT1 4 construction of the index for the search field 5, when used in the Join Table