The following article mainly describes the MySQL database simple and practical optimization of the implementation of the specific methods, including how to regularly analyze and check the table, and how to properly optimize the table, the following is the description of the specific plan, I hope that in your future study will help.
1. Regular analysis table and checklist
The syntax of the analysis table is as follows:
Copy Code code as follows:
ANALYZE [Local | No_write_to_binlog] TABLE tb1_name[, Tbl_name] ...
The above statement is used to analyze and store the keyword distribution of the table, and the results of the analysis will enable the system to obtain accurate statistics that enable SQL to generate the correct execution plan. If the user feels that the actual execution plan is not the expected execution plan, performing a profiling table may resolve the problem. During profiling, a table is locked with a read lock. This is useful for myisam,dbd and InnoDB tables.
For example, analyzing a data table
Copy Code code as follows:
The syntax of the checklist is as follows:
Copy Code code as follows:
CHECK TABLE Tb1_name[,tbl_name] ... [option]...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
The role of the checklist is to check if one or more tables have errors, check table is useful for MyISAM and InnoDB tables, and for MyISAM tables, keyword statistics are updated
Check table can also check for errors in the view, such as the table referenced in the view definition does not exist.
2. Regular optimization table
The syntax of the tuning table is as follows:
Copy Code code as follows:
OPTIMIZE [Local | No_write_to_binlog] TABLE tb1_name [, Tbl_name] ...
If you delete a large portion of a table, or if you have made more changes to a table containing a variable-length row (a table with VARCHAR, blob, or text columns), you should use the Optimize Table command for table optimization. This command merges space debris in a table and eliminates space waste due to deletions or updates, but the Optimize Table command works only on MyISAM, BDB, and InnoDB tables.
For example: Optimize table table_name
Note: The table will be locked during analyze, check, and optimize execution, so be sure to take the relevant action when the MySQL database is not busy.
Common SQL Optimizations
The SQL statements we use most often in development are inserts, GroupBy, and so on. How do we optimize these SQL statements?
1. Insert data in large quantities
When importing data with the load command, the appropriate settings can increase the speed of the import.
For MyISAM storage engine tables, you can quickly import large amounts of data in the following ways
Copy Code code as follows:
ALTER TABLE tb1_name DISABLE KEYS;
Loading the data
ALTER TABLE tb1_name ENABLE KEYS;
The DISABLE keys and the ENABLE keys are used to turn on or off updates to MyISAM tables that are not unique indexes. When you import a large amount of data into a non-empty MyISAM table, you can increase the efficiency of the import by setting both commands.
For importing large amounts of data to an empty MyISAM table, the default is to import the data before the index is created, and the index is not set.
Copy Code code as follows:
Load data infile '/home/mysql/text_txt ' into table text
This approach does not improve the efficiency of importing data for tables of type InnoDB, but there are several ways to optimize tables for innodb types.
1. Because InnoDB types of tables are saved in the order of the primary key, the imported data is sorted in order of the primary key, which can effectively improve the efficiency of importing data.
2. Execute set unique_checks=0 before importing data, turn off uniqueness checksum, perform set Unique_checks=1 after the import is complete, restore uniqueness checksum, can improve import efficiency.
3. If the application uses autocommit, it is recommended to execute set autocommit=0 before importing, turn off Autocommit, execute set autocommit=1 after the import is finished, turn on autocommit, and improve import efficiency.
Optimizing INSERT Statements
When you insert data, you can consider the following ways to optimize
1. If you insert many rows from one customer at the same time, use INSERT statements with multiple value tables as much as possible, which will greatly shorten the cost of linking, shutting down, and so on for the client and MySQL database, making the efficiency faster than a single INSERT statement executed separately.
For example:
Copy Code code as follows:
INSERT into test values (1,2)
INSERT into test values (3,4)
INSERT into test values (5,6)
Replace the above three sentences with the following: INSERT into test values (1,2), (3,4), (5,6) ...
2. If you insert many rows from different customers, you can get a higher speed by using the Insert delayed statement.
The meaning of the delayed is that the INSERT statement is executed immediately, in fact, the data is placed in the memory queue, and is not actually written to disk, which is more quickly than each statement inserted, low_priority just the opposite, after all other users of the table read and write after the insert.
3. Storing index files and data files on separate disks
4. If you do a bulk INSERT, you can increase the Bulk_insert_buffer_size variable value to increase the speed, however, this can only be used for MyISAM tables.
5. When loading a table from a text file, use the load DATA INFILE. This is usually about 20 times times faster than using many INSERT statements.
The following is a MySQL performance optimization of some other places to note, we combine and, give full play to the performance of MySQL.
MySQL database query optimization
MySQL optimization strategy-related database commands
Optimization of My.ini in MySQL database 2G memory for station multi-pressure type setting
