MySQL database technology (23) [group chart] _ MySQL

MySQL database technology (23) [group chart] 4.4 effectively load data

Most of the time, you are concerned about optimizing SELECT queries because they are the most common queries and it is not always straightforward to determine how to optimize them. Relatively speaking, it is straightforward to load data into the database. However, there are also policies to improve the efficiency of data loading operations. The basic principles are as follows:

■ Batch loading is faster than single-row loading, because after each record is loaded, you do not need to refresh the index cache; you can refresh the index only after batch record loading.

■ It is faster to load a table without indexing than to load it after indexing. If there is an index, you must not only add records to the data file, but also modify each index to reflect the new records added.

■ Short SQL statements are faster than long SQL statements because they involve less analysis on the server side and are sent from the client to the server over the network. Some of these factors seem insignificant (especially the last one), but if you want to load a large amount of data, even a small factor will produce very different results. We can use the general principles described above to derive several practical conclusions on how to load data as quickly as possible:

■ Load data (including all its forms) is more efficient than I N S E RT because it loads rows in batches. Few indexes are refreshed, and the server only needs to analyze and explain one statement instead of several statements.

■ Load data is more efficient than load data local. When using load data, the FILE must be located on the server and have the FILE permission, but the server can directly read the FILE from the disk. Using load data local, the client reads the file and sends it to the server over the network, which is very slow.

■ If I N S E RT must be used, multiple rows can be specified in a single statement. for example:

The more rows you can specify in a statement, the better. This reduces the number of required statements and the index refresh volume. If you use mysqldump to generate a database backup file, you should use the -- extended-insert option to include multiple lines of I N S E RT statements. You can also use-o p t (optimized) to enable the -- extended-insert option.

Otherwise, you should avoid using the -- complete-insert option of mysqldump. this option will cause the I N S E RT statement to be a single row and the execution time is longer, it requires more analysis than Statements generated without the -- complete-insert option.

■ Use the compressed client/server protocol to reduce network data traffic. For most MySQL clients, you can use the -- compress command line option to specify. It is generally used only for slow networks, because compression takes a lot of processing time.

■ Insert the default value for MySQL. do not specify columns that will be given the default value in any way in the I N S E RT statement. On average, the statement will be shorter, reducing the number of characters transmitted to the server over the network. In addition, if the statement contains less values, the server performs less analysis and conversion.

■ If the table is indexed, you can use batch insert (load data or multi-row I N S E RT statement) to reduce the index overhead. This will minimize the impact of index updates, because indexes must be refreshed only when all rows are processed, rather than after each row is processed.

■ If you need to load a large amount of data into a new table, you should create the table and load the data before creating the index. this is faster. One index creation (instead of one index per row) is faster.

■ If you delete or disable indexes before loading, re-create or enable indexes after loading data may make loading faster. If you want to use a deletion or disabling policy for data loading, you must perform some experiments to check whether this is worthwhile (if you load a small amount of data into a large table, rebuilding and indexing may take longer than loading data ).

You can use drop index and c r e ate index to delete and recreate indexes. Another option is to use myisamchk or isamchk to disable and enable indexes. This requires an account on the MySQL server host and the permission to write data to the table file. To disable table indexes, you can enter the corresponding Database Directory and execute one of the following commands: