Tips:
1. How to detect inefficient statements?
Under MySQL, by setting --log-slow-queries=[file name] in the startup parameters, you can record SQL statements whose execution time exceeds long_query_time (default is 10 seconds) in the specified log file. You can also modify the long query time in the startup configuration file, such as:
# Set long query time to 8 seconds
long_query_time=8
2. How to query the index of a table?
You can use the SHOW INDEX statement, such as:
SHOW INDEX FROM [table name]
3. How to query the index usage of a certain statement?
You can use the EXPLAIN statement to see the index usage of a certain SELECT statement. If it is an UPDATE or DELETE statement, it needs to be converted into a SELECT statement first.
4. How to export the contents of the INNODB engine to the error log file?
We can use the SHOW INNODB STATUS command to view a lot of useful information about the INNODB engine, such as the current process, transactions, foreign key errors, deadlock problems and other statistics. . How to enable this information to be recorded in the log file? As long as you create the innodb_monitor table using the following statement, MySQL will write 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, just delete This table can be:
DROP TABLE innodb_monitor;
5. How to delete huge log files regularly?
Just set the log expiration time in the startup configuration file:
expire_logs_days=10
Notes:
1. Focus on the index
. The following uses the table TSK_TASK as an example to illustrate the SQL statement optimization process. The TSK_TASK table is used to save system monitoring tasks. The relevant fields and indexes are as follows:
ID: primary key;
MON_TIME: monitoring time; index built;
STATUS_ID: task status; foreign key relationship established with SYS_HIER_INFO.ID.
Note: MySQL will automatically create indexes for foreign keys. During this optimization process, it was found that these automatically created foreign key indexes will cause unnecessary interference to the efficiency of SQL statements. Special attention is required!
First, we found in the log file that the execution of the following statement was relatively slow, 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';
It turns out that it is necessary to find 295 records that meet the conditions among 88143 records, which is of course slow. Quickly use the EXPLAIN statement to check 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 | Using where |
+----+-------------+----------+------+--------- --It
can be seen that there are two indexes available: FK_task_status_id_TO_SYS_HIER_INFO, TSK_TASK_KEY_MON_TIME, and the foreign key index on STATUS_ID is used when the statement is finally executed.
Let’s take another look at the index of the TSK_TASK table:
+----------+---------------------------- --------
| Table | Key_name | Column_name | Cardinality |
+----------+------------+-------- ---------------
| TSK_TASK | PRIMARY | ID | 999149 |
|TSK_TASK
| FK_task_status_id_TO_SYS_HIER_INFO | STATUS_ID | 16
|
---+------------------------------------
Under Oracle or other relational databases, WHERE conditions The order of the fields in the index plays an important role in the selection of the index. Let's adjust the field order, put STATUS_ID at the end, and EXPLAIN again:
EXPLAIN select * from TSK_TASK WHERE MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23' and STATUS_ID = 1064;
but it has no effect , MySQL still uses the STATUS_ID foreign key index created by the system.
After careful analysis, it seems that the Cardinality attribute (that is, the number of unique values in the index) plays an extremely important role in the selection of the index. MySQL selects the index with the smaller number of unique values in the index as the index of the entire statement.
For this statement, if you use FK_task_status_id_TO_SYS_HIER_INFO as an index and the TSK_TASK table stores data for many days, the number of scanned records will be large and the speed will be slow. There are several optimization solutions available:
If there are not many tasks in a day, we delete the index FK_task_status_id_TO_SYS_HIER_INFO, then MySQL will use the index TSK_TASK_KEY_MON_TIME, and then scan the records with STATUS_ID 1064 in the data of that day, which is not slow;
If there are many tasks in a day, we need to delete the indexes FK_task_status_id_TO_SYS_HIER_INFO and TSK_TASK_KEY_MON_TIME, and then create a joint index of STATUS_ID, MON_TIME, which will definitely be very efficient.
Therefore, it is recommended not to use foreign keys for tables with a large number of records to avoid serious reduction in performance efficiency.
2. Try to control the number of records in each table.
When the number of records in a table is large, management and maintenance will be very troublesome. For example, index maintenance will take a long time, which will cause great disruption to the normal operation of the system. interference.
For tables whose data volume continues to grow over time, we can distinguish real-time data and historical data based on time. We can use the background service program to regularly move the data in the real-time table to the historical table, thereby controlling the number of records in the real-time table and improving query performance. and operational efficiency. But note that the time of each move should be short enough so as not to affect the data writing of the normal program. If it takes too long, it may cause a deadlock problem.
3. Data hashing (partition) strategy:
When the number of customers reaches a certain scale, a single database will not be able to support higher concurrent access. At this time, you can consider hashing (partitioning) the customer data into multiple databases to share the load. Improve the overall performance and efficiency of the system.