Shulou(Shulou.com)06/01 Report--

Below, I would like to tell you briefly about the specific operation methods to achieve mysql database optimization. Have you known about similar topics before? If you are interested, let's take a look at this article. I believe it is more or less helpful for everyone to read the specific operation methods to achieve mysql database optimization.

1. Optimize your MySQL query cache

Query on MySQL CVM to enable query caching. Letting the database engine process quietly in the background is one of the most effective ways to improve performance. When the same query is executed multiple times, it is quite fast if the result is extracted from the cache.

But the main problem is that it is so easy to hide that most of our programmers ignore it. In some processing tasks, we can actually prevent query caching from working.

/ / query cache does NOT work$r = mysql_query ("SELECT username FROM user WHERE signup_date > = CURDATE ()"); / / query cache worksmatching activities = date ("Y-m-d"); $r = mysql_query ("SELECT username FROM user WHERE signup_date > ='$today'"); / / query cache does NOT work$r = mysql_query ("SELECT username FROM user WHERE signup_date > = CURDATE ()"); / / query cache worksmatching activities = date ("Y-m-d") $r = mysql_query ("SELECT username FROM user WHERE signup_date > ='$today'")

two。 Use EXPLAIN to make your SELECT query clearer

Using the EXPLAIN keyword is another MySQL optimization technique that allows you to understand what query operations MySQL is doing, which can help you identify bottlenecks and show what went wrong with the query or table structure.

The results of the EXPLAIN query can tell you which indexes are being referenced, how the tables are scanned and sorted, and so on.

Implement a SELECT query (preferably a more complex one with joins mode) and add your keyword explanation to it. Here we can use phpMyAdmin, which will tell you the results in the table. For example, if I forget to add a column to an index when I am executing joins, EXPLAIN can help me find the problem.

After adding an index to group_id field

3. Use LIMIT 1 to get a unique line

Sometimes, when you want to query a table, you know you only need to look at a row. You may go to a very unique record, or just happen to check the number of records that exist, and they all satisfy your WHERE clause.

In this case, adding a LIMIT 1 will make your query more efficient. In this way, the database engine will stop scanning when it finds only 1, instead of scanning the entire table or index.

/ / do I have any users from Alabama? / / what NOT to do: $r = mysql_query ("SELECT * FROM user WHERE state = 'Alabama'"); if (mysql_num_rows ($r) > 0) {/ /...} / / much better: $r = mysql_query ("SELECT 1 FROM user WHERE state =' Alabama' LIMIT 1") If (mysql_num_rows ($r) > 0) {/ /...}

4. Retrieval fields in the index

An index is not just a primary or unique key. If you want to search for any column in the table, you should always point to the index.

5. Ensure that the index of the connection is of the same type

If the application contains multiple join queries, you need to make sure that the columns you link are indexed on both sides of the table. This affects how MySQL optimizes inner join operations.

In addition, the added columns must be of the same type. For example, if you add a DECIMAL column while adding an int column in another table, MySQL will not be able to use at least one of these metrics. Even if the character encoding must be of the same string type.

/ / looking for companies in my state $r = mysql_query ("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id"); / / both state columns should be indexed / / and they both should be the same type and character encoding / / or MySQL might do full table scans

6. Do not use the BY RAND () command

This is a trap that many novice programmers will fall into. You may have unwittingly created a terrible peace. This trap is created when you use the BY RAND () command.

If you really need to display your results at random, there are many better ways to achieve it. It's true that this requires more code, but it avoids performance bottlenecks. The problem is that MySQL may execute the BY RAND () command for each separate row in the table (which consumes the processor's processing power) and then returns you only one row.

/ / what NOT to do: $r = mysql_query ("SELECT username FROM user ORDER BY RAND () LIMIT 1"); / / much better: $r = mysql_query ("SELECT count (*) FROM user"); $d = mysql_fetch_row ($r); $rand = mt_rand (0memento [0]-1); $r = mysql_query ("SELECT username FROM user LIMIT $rand, 1")

7. Avoid SELECT * commands as much as possible

The more data you read from the table, the slower the query becomes. It increases the time required to operate the disk, or when the database cloud server is separated from the WEB cloud server. You will experience a very long network delay simply because data is transferred unnecessarily between cloud servers.

It is a very good habit to always specify the columns you need.

/ / not preferred $r = mysql_query ("SELECT * FROM user WHERE user_id = 1"); $d = mysql_fetch_assoc ($r); echo "Welcome {$d ['username']}"; / / better: $r = mysql_query ("SELECT username FROM user WHERE user_id = 1"); $d = mysql_fetch_assoc ($r); echo "Welcome {$d [' username']}"; / / the differences are more significant with bigger result sets

8. Get advice from PROCEDURE ANALYSE ()

PROCEDURE ANALYSE () allows MySQL's column structure analysis and the actual data in the table to give you some advice. If you already have actual data in your table, it can serve your important decisions.

9. Prepared statement

Prepared statements can help you both in terms of performance optimization and security.

Prepared statements can effectively protect the application from SQL injection attacks by filtering bound variables by default. Of course, you can also filter manually, but because of the forgetful character of most programmers, it is difficult to achieve results.

/ create a prepared statement if ($stmt = $mysqli- > prepare ("SELECT username FROM user WHERE state=?")) {/ / bind parameters $stmt- > bind_param ("s", $state); / / execute $stmt- > execute (); / / bind result variables $stmt- > bind_result ($username); / / fetch value $stmt- > fetch (); printf ("% s is from% s\ n", $username, $state) $stmt- > close ();}

10. Store IP addresses as unsigned integers

Many programmers create a VARCHAR (15) without realizing that they can store IP addresses as integers. When you have an INT type, you only take up 4 bytes of space, which is a fixed size field.

You must make sure that the column you are operating on is of type UNSIGNED INT, because the IP address will use 32-bit unsigned integer.

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

11. Always set an ID for each table

We should set an ID as its primary key for each table in the database, and preferably an int type (UNSIGNED is recommended), and set the auto-added AUTO_INCREMENT flag.

Even if your users table has a field with a primary key called "email", don't let it be the primary key. Using the VARCHAR type as the primary key degrades performance. In addition, in your program, you should use the ID of the table to construct your data structure.

Moreover, under the MySQL data engine, there are some operations that require the use of primary keys, in which case, the performance and settings of primary keys become very important, such as clusters, partitions.

Here, there is only one exception, that is, the "foreign key" of the "associated table", that is, the primary key of this table is formed by the primary key of several individual tables. We call this situation "foreign keys". For example, if there is a "student table" with a student's ID, and a "curriculum schedule" with a course ID, then the "score sheet" is a "related table", which is related to the student table and the curriculum schedule. In the grade table, the student ID and the course ID are called "foreign keys" which together form the primary key.

twelve。 Use ENUM instead of VARCHAR

The ENUM type is very fast and compact. In fact, it holds TINYINT, but it appears as a string on the outside. In this way, it is perfect to use this field to make a list of options.

If you have a field, such as "gender", "country", "nationality", "status" or "department", and you know that the values of these fields are limited and fixed, you should use ENUM instead of VARCHAR.

MySQL also has a "suggestion" (see Article 10) on how to reorganize your table structure. When you have a VARCHAR field, this proposal will tell you to change it to ENUM. Using PROCEDURE ANALYSE (), you can get relevant suggestions.

13. Get advice from PROCEDURE ANALYSE () p programmer station

PROCEDURE ANALYSE () will ask MySQL to help you analyze your fields and their actual data, and will give you some useful advice. These suggestions become useful only if there is actual data in the table, because data is needed to make some big decisions.

For example, if you create an INT field as your primary key, but there is not much data, PROCEDURE ANALYSE () will advise you to change the type of this field to MEDIUMINT. Or if you use a VARCHAR field, because there is not much data, you may get a suggestion to change it to ENUM. These suggestions may not be accurate enough because there is not enough data.

In phpmyadmin, when you look at the table, click "Propose table structure" to view these suggestions.

It is important to note that these are just suggestions, and they will become accurate only when you have more and more data in your table. It's important to remember that you're the one who makes the final decision.

14. Use the NOT NULL php programmer station whenever possible

Unless you have a very special reason to use null values, you should always keep your fields NOT NULL. This seems to be a little controversial. Please read on.

First of all, ask yourself what's the difference between "Empty" and "NULL" (if it's INT, it's 0 and NULL). If you think there is no difference between them, then you should not use NULL. Do you know? In Oracle, the strings of NULL and Empty are the same!)

Don't assume that NULL doesn't need space, it needs extra space, and your program will be more complex when you make comparisons. Of course, this is not to say that you can't use NULL, the reality is very complicated, there will still be some cases, you need to use null values.

The following is an excerpt from MySQL's own documentation:

15. Prepared Statements

Prepared Statements, much like a stored procedure, is a collection of SQL statements that run in the background, and we can get a lot of benefits from using prepared statements, whether it's performance or security issues.

Prepared Statements can check some of the variables you bind to protect your program from "SQL injection" attacks. Of course, you can also check your variables manually, however, manual checks are prone to problems and are often forgotten by programmers. This problem is better when we use some framework or ORM.

In terms of performance, this will give you a considerable performance advantage when the same query is used multiple times. You can define some parameters for these Prepared Statements, and MySQL will only parse it once.

Although the latest version of MySQL uses binary to transmit Prepared Statements, this makes network transmission very efficient.

Of course, there are cases where we need to avoid using Prepared Statements because it does not support query caching. But it is said that version 5.1 is supported. To use prepared statements in PHP, you can check its manual: mysqli extension or database abstraction layer, such as PDO.

16. Unbuffered query

Normally, when you execute a SQL statement in your script, your program will stop there until no SQL statement returns, and then your program will continue to execute. You can use unbuffered queries to change this behavior.

There is a very good note about this in the PHP documentation: the mysql_unbuffered_query () function:

The above sentence translates that mysql_unbuffered_query () sends a SQL statement to MySQL instead of automatically fethch and caching the results as mysql_query () does. This can save a lot of memory, especially those queries that produce a lot of results, and you don't have to wait for all the results to be returned, just the first row of data. you can start working on the query results right away.

However, there will be some limitations. Because you either read all the lines, or you need to call mysql_free_result () to clear the results before making the next query. Also, mysql_num_rows () or mysql_data_seek () will not be available. Therefore, you need to think carefully about whether to use unbuffered queries.

17. Save the IP address as UNSIGNED INT

Many programmers create a VARCHAR (15) field to hold the string IP instead of the shaping IP. If you use shaping to store, it only takes 4 bytes, and you can have fixed-length fields. Moreover, it will give you an advantage in query, especially if you need to use the WHERE condition: IP between ip1 and ip2.

We have to use UNSIGNED INT because the IP address uses the entire 32-bit unsigned shaping.

For your query, you can use INET_ATON () to convert a string IP into an integer and INET_NTOA () to convert an integer into a string IP. In PHP, there are also functions like ip2long () and long2ip ().

18. Fixed length watches will be faster.

If all the fields in the table are "fixed length", the entire table is considered "static" or "fixed-length". For example, there are no fields of the following type in the table: VARCHAR,TEXT,BLOB. As long as you include one of these fields, the table is not a "fixed-length static table", so the MySQL engine will handle it in a different way.

Fixed-length tables improve performance because MySQL searches faster, and because these fixed lengths are easy to calculate the offset of the next data, they are naturally read quickly. If the field is not fixed in length, then every time you want to find the next one, you need the program to find the primary key.

Also, fixed-length tables are easier to cache and rebuild. However, the only side effect is that fixed-length fields waste some space, because fixed-length fields allocate so much space whether you use them or not. Php programmer station

Using the "vertical split" technique (see next), you can split your watch into two, one with fixed length and the other with variable length.

19. Vertical segmentation

"Vertical splitting" is a method of turning tables in a database into several tables according to columns, which can reduce the complexity of tables and the number of fields, thus achieving the purpose of optimization. (in the past, I worked on a project in a bank and saw a table with more than 100 fields, which was terrifying.)

Example 1: there is a field in the Users table that is a home address. This field is optional, compared to, and you do not need to read or rewrite this field frequently except for your personal information when you are operating in the database. So why not put him on another table? This will make your watch better.

What do you think of the specific operation methods to achieve mysql database optimization? what do you think of this article, and whether it has gained anything? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.