Optimization of the system described in this article, mainly for both front and back (back in the main on the SQL statements and data storage optimized), below, we will introduce some optimization techniques and experience.
1 How to find out the low efficiency of the statement?
In MySQL, the starting parameter set - log-slow-queries = [file name], you can specify the log file takes longer than long_query_time (default is 10 seconds) the SQL statement. You can also modify the configuration file at startup time to long query, such as:
# Set long query time to 8 seconds
long_query_time = 8
(2) how to query a table index?
You can use SHOW INDEX statement, such as:
SHOW INDEX FROM [table name]
3 How to check the index of a statement of use?
EXPLAIN statement can look at the index of a SELECT statement use. If it is UPDATE or DELETE statement, you need to convert the SELECT statement.
4. INNODB engine how to export the contents of the error log file?
We can use SHOW INNODB STATUS command to see the INNODB engine a lot of useful information, such as the current process, transactions, foreign key error, deadlock and other statistical data. How to make the information can be recorded in the log file? Just use the following statement to create innodb_monitor table, MySQL will be every 15 seconds to the system write to the error log file:
CREATE TABLE innodb_monitor (a INT) ENGINE = INNODB;
If you no longer need to export to the error log file, simply delete the table you can:
DROP TABLE innodb_monitor;
5 How to delete large log files on a regular basis?
As long as the startup configuration file to set the expiration time to the log:
expire_logs_days = 10
1 focus on the index to the table TSK_TASK table below an example SQL statement optimization. TSK_TASK table to save the system monitoring tasks, related field and index are as follows:
ID: primary key;
MON_TIME: monitoring time; built index;
STATUS_ID: task status; with SYS_HIER_INFO.ID established a foreign key relationship.
Note MySQL automatically indexes for foreign keys, in this optimization process, found that the foreign key index is automatically created SQL statement would be unnecessary interference with the efficiency, need special attention!
First, we found in the log file, execute the following statement is slower, 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';
The original record in the 88,143 eligible to be found 295 records, of course, slow. EXPLAIN statement quickly look at the index with the use of it:
| 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 |
It can be seen, there are two indexes available FK_task_status_id_TO_SYS_HIER_INFO, TSK_TASK_KEY_MON_TIME, and eventually execute a statement on the use of STATUS_ID foreign key index.
Take a look TSK_TASK table index of it:
| 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 |
Oracle or other relational database in the next, WHERE conditions in the field order of the index selection plays an important role. We adjust the field order, the STATUS_ID on the back, and then EXPLAIN this:
EXPLAIN select * from TSK_TASK WHERE MON_TIME> = '2007-11-22 'and MON_TIME <'2007-11-23' and STATUS_ID = 1064;
However, no effect, MySQL, or use the system established STATUS_ID foreign key index.
Careful analysis, it appears Cardinality properties (ie the index number of unique values) of the index selection played an extremely important role, MySQL index value is selected a small number of unique values of that index as a whole statement index.
For this statement, if you do use FK_task_status_id_TO_SYS_HIER_INFO index, TSK_TASK many days of data stored in the table, then scan the number of records will be a lot slower. Can have the following optimization program:
If the small number of tasks a day, we remove the index FK_task_status_id_TO_SYS_HIER_INFO, then MySQL will use index TSK_TASK_KEY_MON_TIME, then in the days of data in the scan STATUS_ID 1064 records, that speed is not slow;
If the number of multi-day task, then we need to remove the index FK_task_status_id_TO_SYS_HIER_INFO and TSK_TASK_KEY_MON_TIME, then create STATUS_ID, MON_TIME joint index, so that efficiency will certainly be very high.
Therefore suggested that the number of those records more than a table, it is recommended not to use foreign keys in order to avoid serious degradation of performance efficiency.
(2) try to control the number of records in each table a table when a large number of records, the management and maintenance will be a lot of trouble, such as index maintenance will take a long time, and thus the normal operation of the system will cause great interference.
Over time the amount of data on the growing table, we can distinguish between real-time data and historical data, you can use daemons periodically moving real-time data in the table to the history table, to control the number of records in real-time table to improve query and operational efficiency. But pay attention to each movement of time short enough not to affect the normal process of data writing. If you take too long, may cause a deadlock.
3 data hash (partition) strategy when the number of clients reaches a certain size, a single database will not be able to support more concurrent access, time to consider the customer data hash (partition) to multiple databases to share the load, improve overall system performance and efficiency.