MySQL specific explanation (+)-----------Massive data recommendations

Here are some of the more important suggestions:
1. Choose the right storage engine
Take MySQL for example. Contains two storage engines MyISAM and InnoDB, each with its own pros and cons.
MyISAM is suitable for applications that require a large number of queries, but it is not very good for a lot of write operations. Even if you just need to update a field, the entire table will be locked up. In other processes, the read process cannot be manipulated until the read operation is complete.

In addition, MyISAM's calculations for SELECT COUNT (*) are extremely fast.
The InnoDB trend will be a very complex storage engine. For some small applications. It will be slower than MyISAM.

However, it supports "row locks". So when the writing operation is more than the time. will be more excellent. In addition, he supports many other advanced applications, such as transactions.

2. Optimize the data type of the field
Remember a principle, the smaller the column the faster. For most database engines, hard disk operations can be the most significant bottleneck.

So. Getting your data into a compact situation can be very helpful. This reduces the access to the hard drive.
Suppose a table has just a few columns (for example, a dictionary table). Configuration table), then we have no reason to use INT for the master key. Using Mediumint, SMALLINT or smaller TINYINT will be more economical. Suppose you don't need to record time, using date is much better than DATETIME. Of course, you also need to leave enough room for expansion.

3. Index The search field
The index does not necessarily give the primary key or the unique field. Suppose you have a field in your table that you always use to do a search, so it's best to index it. Unless the field you are searching for is a large text field. That should establish a full-text index.
4. Avoid using SELECT * the more data is read from the database. The more slowly the query becomes.

And. Assuming that your database server and webserver are two separate servers, this also adds the load on the network transport. Even if you want to query all the fields of the data table, try not to use the * wildcard character, use the built-in provided field exclusion definition may bring a lot of other convenience.
5. Use ENUM instead of VARCHAR
The ENUM type is fast and compact. In fact, it holds the TINYINT, but it appears as a string on its appearance.

So. Using this field to make a list of options becomes quite perfect. For example, the values for these fields, such as gender, ethnicity, department, and status, are limited and fixed, so you should use ENUM instead of VARCHAR.
6. Use not NULL as much as possible
Unless you have a very special reason to use null values, you should always keep your fields not NULL. Null actually requires extra space, and your program will be more complex when you do it.

Of course, this is not to say you cannot use NULL, the reality is very complex, there will still be some cases. You need to use a null value.
7, fixed-length table will be faster
Assuming that all the fields in the table are fixed-length, the entire table will be considered "static" or "Fixed-length".

For example, there are no fields in the table such as the following types: Varchar,text,blob.

Just because you include one of these fields, the table is not a fixed-length static table, so the MySQL engine will have a way to handle it.
Fixed-length tables can improve performance, because MySQL searches faster, because these fixed lengths are very easy to calculate the offset of the next data, so the nature of reading will be very fast. If the field is not fixed-length, then each time you want to find the next one, you need to find the primary key.
And. Fixed-length tables are also easier to cache and rebuild.

Just, the only side effect is that fixed-length fields will waste some space, due to the length of the field whether you use it or not. He has to allocate so much space.

Using the "vertical cut" technique, you can cut your table into two one that is fixed length. One is a variable length.
8, Vertical cutting "Vertical Cutting" is a method of turning a table in a database into several tables, which reduces the complexity of the table and the number of fields for optimization purposes.
For example, a field in the user table is a home address. This field is an optional field. You do not have to read or rewrite this field frequently in addition to your personal information when working in a database. So, why not put him in another table? This will give your table a better performance. People think is not, a lot of time, I for the user table, only real user id,username, password. User roles are often used.

A smaller table will always have good performance.
Also, you need to be aware that the tables formed by the fields that have been split are not often used to join them, otherwise the performance will be worse than not cutting. And it's going to be a drop in the extreme number of levels.

9, EXPLAIN your SELECT query.
Using EXPLAIN keyword allows you to know how MySQL handles your SQL statements. This can help you analyze the performance bottlenecks of your query statements or table structures. EXPLAIN's query results will also tell you how your index primary key is being used, how your data tables are searched and sorted ... Wait, wait.
Usually we can add keywordexplain to the front by comparing the more complex SELECT statements, especially those involving multiple tables.

optimizations for inserting large amounts of data :

1.

Replaces a single-line insert operation with multiline inserts. is much faster than a single insert. Other than that. Increase the value of the bulk_insert_buffer_size. For example, set to 64M (under the MyISAM engine)

INSERT into T values (), (), ();

2. Use load DATA INFILE .... Into TABLE. Faster than inserting an INSERT statement with the same number of rows

3. For tables that use the InnoDB storage engine, you can complete the insert operation in a transaction so that the Inodb will refresh at the end of the transaction. Instead of every INSERT statement, the change is refreshed. The same operation can be applied to the update.

4. Assuming a non-empty table, use the ALTER TABLE table_name disable keys, then load data infile and run the following import:

ALTER TABLE table_name enable keys. Suppose it is an empty table. There is no need for this operation. Because the MyISAM table imports data in an empty table, the data is first imported and then established Indexs.

Example examples

such as the following code, close the transaction commit, wait for the update and then a one-time commit, to the original 10 hours of work into 10 minutes. This reads a more than 7 million-line file. Update records about 300多万条.

My $db _handle = Dbi->connect ("dbi:mysql:database= $database; host= $host", $db _user, $db _pass, {' RaiseError ' = + 1, autocommit = 0}) | | Die "Could not connect to database: $DBI:: Errstr";    eval {while        (!eof ($FD))        {            $CloudID = < $FD >; chomp $CloudID;            $CRC _code = < $FD > chomp $CRC _code;            My $sql = "call ' Room_match '. ' Crcwritecode ' ($CloudID, ' $CRC _code ');";            My $affect _rows = $db _handle->do ($sql);        }        $db _handle->commit ();    };

It starts with SQL running for the first time, which is very slow!

Set autocommit = 0, and then commit, the speed is greatly improved.

MySQL specific explanation (+)-----------Massive data recommendations