In view of the problems in the last exam system, when the traffic volume of data is too large and there is too much dynamic interaction with the data, the server memory and CPU usage are high.
The instance diagram is as follows:
According to the teacher's inspiration, I found the basic reason. The main reason is that there is too much data in a table and the data is accumulated gradually, resulting in excessive redundant data during subsequent access, in this way, the query will consume too much, resulting in a long delay for login or query. Or the query times out.
Taking this into consideration, I thought of optimizing the database for the data volume. Of course, the optimization is not only the optimization of data, but also the optimization of front-end web services. Here, only SQL queries are optimized.
Some databases frequently query large amounts of data. We can optimize some tables or some table records stored separately in different data files. Information in tables that can be frequently accessed is stored in a dynamic array and stored in the memory. This avoids the consumption of frequently accessed databases.
First, let's take a look at the overall optimization methods one by one.
1. design appropriate data types
The data type is a required type in the database, and each data type has a different character length. If the data type is too large, too many fields will be used for the moment, and overflow will occur when the data type is missing, or the conversion of different data may cause excessive memory usage, and the CPU usage will be slow. Therefore, ensuring the accuracy of the data type is essential.
2. Separate data and log files on different Disks
Operations on data files and log files generate a large amount of I/O. If possible, log files should be stored on a hard disk different from the data files where the data and indexes are located to distribute I/O, and facilitate database disaster recovery.
3. Optimize table partitions and index partitions
Why Table Partitioning?
What is the most important thing we want to do when the data volume of a table is too large? Split the table into two or more points, but the table is still the same, just store its content separately, so reading is faster than N times.
Principle: Table data cannot be stored in files, but file groups can be stored in files, and tables can be placed in file groups. In this way, table data is indirectly stored in different files. Partition storage includes tables, indexes, and large object data.
SQL Server 2005 introduces the table partition concept. As the data volume in the table increases, the data query speed slows down and the application performance degrades, in this case, we should consider partition the table. When there is a lot of data in a table, we can split it into multiple tables, because the data to be scanned becomes less, and the query can run faster, this operation greatly improves the performance. After the table is partitioned, the logical table is still a complete table, but the data in the table is physically stored in multiple tablespaces (physical files ), in this way, the whole table is not scanned every time when data is queried.
When to use a partition table:
The size of Table A exceeds 2 GB.
Table B contains historical data. The new data is added to the new partition table.
Advantages and disadvantages of Table Partitioning:
Advantages:
- Improves query performance. You can query partition objects by searching only partitions in your own relationship to improve data retrieval.
- Enhance availability. If a table partition fails, data in other partitions is still available.
- Easy Maintenance: If a partition in the table fails, you only need to repair the data;
- Balanced I/O: You can map different partitions to disks to balance I/O and improve the overall system performance.
Disadvantages:
Partition Table: an existing table cannot be directly converted to a partition table. However, Oracle provides the online table redefinition function.
4 Distributed Database Design
The distributed database system is developed on the basis of a centralized database system. It is easy to understand, that is, to separate and distribute the entire database to various places. In essence, distributed database systems are divided into two types:
A. data is logically unified, but physically scattered. a distributed database is logically unified as a whole, physically, they are stored on different physical nodes. Generally, distributed databases are like
B. the logic is distributed and physically distributed. This is also a federated distributed database. Because the sub-database systems that constitute the Federation are relatively "autonomous, this system can accommodate a variety of databases with different purposes and large differences, and is suitable for database integration in a wide range.
The above are just a few questions about database Optimization for large data volumes, not about database table optimization.