MySQL Big Data optimization

How the database structure of a large data volume system is designed:

1. Separate several tables that are frequently queried and infrequently used in your table, that is, horizontal slicing

2, the different types of divided into several tables, longitudinal segmentation

3. index of commonly used joins

4, the server put a few hard disks, the data, logs, index sub-disk storage , so that can improve the IO throughput rate

5. Optimize your query with optimizer

6, Consider redundancy , this can reduce the connection

7, you can consider the establishment of statistical tables, is to generate a total table in real time , so that you can avoid each query is counted once

8, test with maximal data

MYSQL Optimization:

MySQL optimization is a comprehensive technology that mainly includes:

1, the design of the table is reasonable (in line with 3NF)

2, add appropriate index (index) "Four kinds: normal index, primary key index, unique index unique, full-text index"

3, Sub-table technology (horizontal Division, vertical division)

4, read and write "update, delete, add" separation

5, the storage process "modular programming, can improve speed"

6, configure the MySQL configuration optimization "Configure maximum concurrency number My.ini, adjust the cache size"

7. mysql Server hardware upgrade

8, regular to remove unnecessary data, scheduled to defragment (MyISAM)

Why does the field use NOT NULL as much as possible instead of NULL?

In MySQL, columns with null values are difficult to query optimization because they complicate indexing, index statistics, and comparison operations. You should use 0, a special value, or an empty string instead of a null value.

The field is used as much as NOT NULL, except in special cases. Why is null often misused?

1, NULL is the default when creating a data table, the novice or uninformed or troublesome programmer will not pay attention to this.

2, many people think not null needs more space, in fact, this is not the point.

3, the focus is that many programmers feel that null in the development of not to judge the insertion of data, write SQL statements more convenient and quick.

MySQL Big Data optimization