System optimization of front-end and background in database

This article describes the system optimization, mainly for the front-end and background of these two aspects (the background of the main SQL statements and data storage optimization), the following we will introduce some optimization techniques and experience.

Skills:

1. How to detect inefficient statements?

Under MySQL, set the--log-slow-queries=[file name in the startup parameter], you can record an SQL statement that executes longer than Long_query_time (default 10 seconds) in the specified log file. You can also modify the time of long query in the startup configuration file, such as:

# Set Long query time to 8 seconds

Long_query_time=8

2. How do I query the index of a table?

You can use the show index statement, such as:

Show INDEX from [table name]

3. How do I query the index usage of a statement?

You can use the explain statement to see the index usage of a SELECT statement. If you are an UPDATE or DELETE statement, you need to convert to a SELECT statement first.
4. How do I export the contents of the InnoDB engine to the error log file?

We can use the show INNODB Status command to view a number of useful information about the INNODB engine, such as current processes, transactions, foreign key errors, deadlock issues, and other statistical data. How do I get this information to be recorded in a log file? Whenever you create a Innodb_monitor table using the following statement, MySQL writes the system to the error log file every 15 seconds:

CREATE TABLE Innodb_monitor (a INT) Engine=innodb;

If you no longer need to export to the error log file, simply delete the table:

DROP TABLE Innodb_monitor;

5. How to delete a large log file on a regular basis?

Just set the log expiration time in the startup configuration file:

expire_logs_days=10

Precautions:

1. Focus on indexing

The following example illustrates the SQL statement optimization process with the table Tsk_task table. The Tsk_task table is used to save system monitoring tasks, and the relevant fields and indexes are as follows:

ID: Primary key;

Mon_time: monitoring time; index;

STATUS_ID: The task state; Sys_hier_info. ID establishes a foreign key relationship.

Note MySQL automatically indexed for foreign keys, in this optimization process, found that these automatically established foreign key index on the efficiency of the SQL statement will cause unnecessary interference, need special attention!

First of all, we found the following statement in the log file slow to execute, more than 10 seconds:

# query_time:18 lock_time:0 rows_sent:295 rows_examined:88143

SELECT * from tsk_task WHERE status_id = 1064 and mon_time >= ' 2007-11-22 ' and Mon_time < ' 2007-11-23 ';

In the original 88,143 records to find out the conditions of the 295 records, of course, slow. Quickly use the explain statement to look at the index usage:

+----+-------------+----------+------+----------

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+----------+------+-----------

| 1 | Simple | Tsk_task | Ref | Fk_task_status_id_to_sys_hier_info,tsk_task_key_mon_time | Fk_task_status_id_to_sys_hier_info | 9 | Const | 276168 | Using where |

+----+-------------+----------+------+-----------

As you can see, there are two indexes available fk_task_status_id_to_sys_hier_info,tsk_task_key_mon_time, while the final execution of the statement takes the foreign key index on status_id.

Look at the index of the Tsk_task table again:

+----------+------------------------------------

| Table | Key_name | column_name | Cardinality |

+----------+------------+-----------------------

| Tsk_task | PRIMARY | ID | 999149 |

| Tsk_task | Fk_task_status_id_to_sys_hier_info | status_id | 16 |

| Tsk_task | Tsk_task_key_mon_time | Mon_time | 13502 |

+----------+------------------------------------

In Oracle or other relational databases, the order of fields in the Where condition plays an important role in the selection of indexes. We adjust the order of the fields, put the status_id in the back, and then explain:

EXPLAIN SELECT * from Tsk_task WHERE mon_time >= ' 2007-11-22 ' and Mon_time < ' 2007-11-23 ' and status_id = 1064;

But no effect, MySQL is also selected system to establish the STATUS_ID foreign key index.

With a careful analysis, it seems that the cardinality attribute, the number of unique values in the index, plays an extremely important role in the selection of the index, which is the index of the entire statement by selecting the index with a small number of unique index values.

For this statement, if you use Fk_task_status_id_to_sys_hier_info to do the index, and the Tsk_task table for many days of data, the number of scanned records will be many, slower. There are several optimization scenarios:

If the number of tasks a day is not much, we delete the index fk_task_status_id_to_sys_hier_info, that MySQL will use the index Tsk_task_key_mon_time, and then in the day of the data in the scan Status_ ID 1064 record, that speed is not slow;

If the number of tasks in a day is much, we need to delete the index fk_task_status_id_to_sys_hier_info and Tsk_task_key_mon_time, and then establish the Status_id,mon_time Joint index. This will certainly be very efficient.

Therefore, it is recommended that you do not use a foreign key for those tables that have more than a few records to avoid a significant reduction in performance efficiency.

2. Try to control the number of records per sheet

When a table has a large number of records, management and maintenance can be cumbersome, such as index maintenance will take a long time, so that the normal operation of the system will cause great interference.

On the table with the increasing amount of data over time, we can distinguish the real-time data and historical data according to the time, and can use the background service program to move the data in the real-time table to the history table periodically, thus control the record number of the real-time table and improve the query and operation efficiency. But note that each move time is short enough, do not affect the normal program data writing. If it takes too long, it can cause a deadlock problem.

3. Data hashing (partition) policy

When the number of customers reaches a certain scale, a single database will not be able to support higher concurrent access, at this time can consider the customer data hash (partition) to multiple databases to share the load, improve the overall performance and efficiency of the system.