MySQL detailed-----------massive data recommendations

Here are some of the more important suggestions:
1. Choose the right storage engine
in MySQL, for example, there are two storage engines MyISAM and InnoDB, each with a few 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 and other processes will be unable to manipulate the read process 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, and for some small applications it will be slower than MyISAM. But it supports "row lock", so when the write operation is more, it will be more excellent. Also, he supports more 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 it's very helpful to have your data compact, because it reduces access to the hard drive.
If a table has only a few columns (for example, a dictionary table, a configuration table), then we have no reason to use INT to master the keys, using Mediumint, SMALLINT or smaller TINYINT will be more economical. If 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. Add an index to the search field
The index does not necessarily give the primary key or the unique field. If you have a field in your table that you will always use to do a search, it is best to index it, unless the field you are searching for is a large text field, you should create a full-text index.
4. Avoid using SELECT *The more data you read from the database, the slower the query becomes. And, if your database server and Web server are two separate servers, this also increases 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, the use of the built-in provided field exclusion definition may be able to bring more convenience.
5. Use ENUM instead of VARCHAR
the ENUM type is very fast and compact. In fact, it holds the TINYINT, but it appears as a string on its appearance. In this way, 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 compare it. Of course, this is not to say that you cannot use NULL, the reality is very complex, there will still be cases where you need to use a null value.
7, fixed-length table will be faster
if all the fields in the table are fixed length, the entire table is considered "static" or "Fixed-length". For example, there are no fields of the following type in the table: Varchar,text,blob. As long as you include one of these fields, the table is not a fixed-length static table, so the MySQL engine will handle it in a different way.
fixed-length tables can improve performance because MySQL searches faster because these fixed lengths are easy to calculate the offset of the next data, so the nature of reading will be fast. And if the field is not fixed, then every time you want to find the next one, you need the program to find the primary key.
also, fixed-length tables are more likely to be cached and rebuilt. However, the only side effect is that a fixed-length field wastes some space, because the field is set to allocate so much space whether you use it or not.
using the "vertical split" technique, you can split your table into two that are fixed-length and one that is indefinite.
8, Vertical Division"Vertical Segmentation" 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, there is a field in the user table that is a home address, which is an optional field, and you do not need 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 make your table better performance, we think is not, a lot of time, I for the user table, only the user ID, user name, password, user role, etc. will be used frequently. A smaller table will always have good performance.
In addition, you need to note that these separated fields form the table, you do not regularly join them, otherwise, this performance will be worse than not split, and, it will be a drop of magnitude.
9, EXPLAIN your SELECT query;
Use the EXPLAIN keyword to let you know how MySQL handles your SQL statements. This can help you analyze the performance bottlenecks of your query statement or table structure. EXPLAIN's query results will also tell you how your index primary key is being leveraged, how your data tables are searched and sorted ... Wait, wait.
In general, we can add the keyword explain to the previous SELECT statement that is more complex, especially when it comes to multiple tables.

optimizations for inserting large amounts of data :

1. Use multiline insertion instead of a single-line insert operation. Much faster than a single insert, plus, increase the value of 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 that has 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. If the non-empty table, use ALTER TABLE table_name disable keys, and then load data infile, after you have finished importing it, execute:

ALTER TABLE table_name enable keys. If this is an empty table, you do not need this operation because the MyISAM table imports data in an empty table and then establishes Indexs.

Example examples

As in the following section of code, close the transaction commit, wait for the update to complete the one-time commit, you can turn the original 10 hours of work into 10 minutes. Read here is a more than 7 million-line file, updated record 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 ();    };

The first time to start is to execute SQL, so the speed is very slow! Set autocommit = 0, and then commit, the speed is greatly improved.

Copyright NOTICE: Welcome to reprint, hope to reprint the same time add the original address, thank you for your cooperation, learning happy!

MySQL detailed-----------massive data recommendations