Tips for DBAs: 10 suggestions for making the database faster _ MySQL-mysql tutorial

Tips for DBAs: 10 suggestions for making the database faster bitsCN.com

The content of most websites is stored in databases and accessed by users through requests. The database is very fast. There are a lot of skills that allow you to optimize the database speed, so that you do not waste server resources. This article contains ten tips for optimizing the database speed.

0. carefully design the database

The first trick may seem to be taken for granted, but in fact most database problems come from poorly designed database structures.

For example, I used to store client information and payment information in the same database column. This is terrible for system and database developers.

When creating a database, you should store the information in different tables and tables, adopt the standard naming method, and adopt the primary key.

1. know where you need optimization

If you want to optimize a query statement, it is very helpful to know the result of this statement clearly. Using the EXPLAIN statement, you will get a lot of useful information. The following is an example:

Explain select * FROM ref_table, other_table WHERE ref_table.key_column = other_table.column;

2. the fastest query statement... Are the statements you haven't sent

Every time you send a statement to the database, you will use a lot of server resources. Therefore, the best way to cache your query statements on a website with high traffic is.

There are many cache statement methods, which are listed below:

AdoDB: AdoDB is a simplified PHP database. With it, you can choose different database systems (MySQL, PostGreSQL, Interbase, and so on), and it is designed for speed. AdoDB provides a simple but powerful cache system. Also, AdoDB has a BSD license and you can use it for free in your project. For commercial projects, it also has LGPL licenses.

Memcached: Memcached is a distributed memory cache system that reduces database load and accelerates dynamic database-based websites.

CSQL Cache: CSQL Cache is an open-source data Cache architecture. I have never tried it, but it looks great.

3. don't select what you don't need

To obtain the desired data, a very common way is to use the * character, which will list all columns.

SELECT * FROM wp_posts;

However, you should only list the columns you need, as shown below. If you visit a very small website, for example, one user per minute, there may be no difference. However, if a large-volume website such as Cats Who Code is used, this saves a lot of trouble for the database.

SELECT title, excerpt, author FROM wp_posts;

4. use LIMIT

It is very common to obtain only data of a specific number of rows. For example, a blog displays only 10 articles per page. In this case, you should use LIMIT to LIMIT the number of data rows you want to select.

If there is no LIMIT and the table has 100,000 rows of data, you will traverse all the rows, which is unnecessary for the server.

SELECT title, excerpt, author FROM wp_posts LIMIT 10;

5. avoid loop queries

When using SQL in PHP, you can place the SQL statement in a loop statement. But doing so adds load to your database.

The following example illustrates the problem of "nested query statements in loop statements:

Foreach ($ display_order as $ id => $ ordinal ){

$ SQL = "UPDATE categories SET display_order = $ ordinal WHERE id = $ id ";

Mysql_query ($ SQL );

}

You can do this:

UPDATE categories

SET display_order = CASE id

WHEN 1 THEN 3

WHEN 2 THEN 4

WHEN 3 THEN 5

END

WHERE id IN (1, 2, 3)

6. use join to replace subqueries

Programmers may like to use subqueries or even abuse them. The following subqueries are very useful:

SELECT a. id,

(Select max (created)

FROM posts

WHERE author_id = a. id)

AS latest_post FROM authors

Although a subquery is useful, a join statement can be used to replace it, and the join statement can be executed more quickly.

SELECT a. id, MAX (p. created) AS latest_post

FROM authors

Inner join posts p

ON (a. id = p. author_id)

Group by a. id

7. be careful when using wildcards

Wildcard is very easy to use. when searching data, you can use a wildcard to replace one or more characters. I am not saying that it cannot be used, but should be used with caution, and do not use full wildcard. prefix or post-wildcard can complete the same task.

In fact, using full-word wildcard characters to search for millions of data sets your database as a machine.

# Full wildcard

SELECT * from table where column like '% hello % ';

# Postfix wildcard

SELECT * from table where column like 'Hello % ';

# Prefix wildcard

SELECT * from table where column like '% hello ';

8. use UNION to replace OR

The following example uses the OR statement:

SELECT * FROM a, B WHERE a. p = B. q or a. x = B. y;

UNION statement. you can combine the results of two or more select statements. The results returned in the following example are the same as above, but the speed is faster:

SELECT * FROM a, B WHERE a. p = B. q UNION

SELECT * FROM a, B WHERE a. x = B. y

9. use indexes

The database index is similar to the index you see in the library: it allows you to quickly obtain the desired information, and the index in the library allows readers to quickly find the desired book.

You can create an index on one or more columns. An index is a data structure that organizes values of one or more columns in a table in a specific order.

The following statement creates an index on the Model column of the Product table. The index name is idxModel.

Create index idxModel ON Product (Model );

BitsCN.com