MySQL Big Data Optimization and MySQL Data Optimization

MySQL Big Data Optimization and MySQL Data Optimization

How to Design the database structure of a system with large data volume:

1. Separate the frequently queried and infrequently used tables in your table, that isHorizontal Split

2. divide different types into several tables,Vertical Split

3. create common connectionsIndex

4. Store several hard disks on the server to store data, logs, and indexes.Storage by diskTo improve the IO throughput.

5. UseOptimizerTo optimize your query

6,Redundancy considerationsTo reduce connections.

7. You can create a statistical table, that isGenerate a total table in real timeTo avoid counting every query.

8. UseLimitTest Data

MySql optimization:

Mysql optimization is a comprehensive technology, mainly including:

1. Table design rationality (in line with 3NF)

2. Add an appropriate index (index). [four types: general index, primary key index, unique index unique, and full-text index]

3. Table sharding Technology (horizontal and vertical)

4. read/write [update, delete, add] Separation

5. Storage Process [Modular programming, improving the speed]

6. Optimize mysql configuration [configure the maximum number of concurrent connections my. ini and adjust the cache size]

7. mysql server hardware upgrade

8. Clear unnecessary data regularly and perform fragment regularly (MyISAM)

 

Why should the field be not null instead of NULL?

The field should be not null, rather than NULL, unless otherwise specified. Why is NULL often misused?

1. NULL is the default value when creating a data table. Beginners or uninformed or troublesome programmers will not pay attention to this.

2. Many people think that not null requires more space. In fact, this is not the focus.

3. The key point is that many Programmers think that NULL does not need to be used for data insertion during development, making it easier and faster to write SQL statements.