Detailed introduction to data optimization

Recently there is a data table with 20 million records that needs to be optimized and migrated. 20 million data is very embarrassing for MySQL, because the index creation speed is still very fast, and no matter how much optimization is done, the speed cannot be improved much. However, these data have a large number of redundant fields and error information, which is extremely inconvenient for statistics and analysis. So I need to create a new table, take out the data from the old table one by one, optimize it, and put it back into the new table; 1. Clear redundant data and optimize the field structure. In the 2000W data, we know in advance the fields that can be used as query conditions. Therefore, create a new field separately for this part of data, and reasonably change the field structure for regular data, such as ID card is varchar(18). For unimportant data, we merge it and there is a field with a text structure. We need to calculate some related data. Common ones such as ID card type can obtain accurate gender, birthplace, birthday, and age. 2. Data migration: We take out an old piece of data from the database, then obtain the desired new data through calculation and processing, and finally insert the new data into the new table. However, the following problems were encountered when obtaining new data. The amount of data is too large to be obtained at one time (2000W data is thrown into

1. How to optimize and migrate 20 million data in MySQL

Introduction: Recently, there is a data table with 2000W records that needs to be optimized and migrated. 2000W data is embarrassing for MySQL because of the reasonable index creation speed. It's still quite fast, and no matter how much optimization is done, the speed will not be improved much. However, there is a large amount of data...

##2. Mysql Optimization Experiment (1) - Partitioning.

Introduction: The concept of optimization is always mentioned during the development project. This article is about Mysql An exploration journey of data optimization practice, a brief introduction to the reasons, methods, partition table management methods and a simple practice #MySQL 20 million data optimization& Migration

##Introduction: Recently there is a data table with 2000W records that needs to be optimized and migrated. It's embarrassing for MySQL, because the index creation speed is still very fast, and the speed cannot be improved much no matter how you optimize it. However, there is a large amount of data...##4.

Thinkphp where() method's search data optimization

Introduction: The first way {code...} The second way {code...} This Both methods can achieve the operation I want, but will the first method put pressure on the server or database? The second method can be written more this line of code {code...} Confused (⊙﹏⊙) 5.

An example of deduplication optimization for a certain XXX mobile phone company

Introduction: This is a SQL developed and written by one of them. The purpose is to delete duplicate data, and the row with the smallest id is not deleted: deletefromjd_chapterawherea.`id`in(select`id`fromjd_chaptergroup&nbs..6.

Oracle Optimization Statistics Histograms of data

Introduction: Histograms are a method of classifying and storing data according to the frequency of occurrence. In Oracle, histograms are used to describe The distribution of column data in the table. Each sql must be processed before being executed. ##7. How to optimize web pages with HTML5 microdata?

8.

mysql database-mysql problem: query data optimization from three tables

Introduction: I have three tables, three All the data in the tables are queried and then sorted. The data in the three tables are all very large. How to query can save resources and be optimal.

9. mysql-MySql Data Statistics Update Select Optimization

Introduction: I would like to ask everyone for advice. The problem encountered in mysq update data optimization is as follows. Table a has 2 fields, ready_count, download_count. Table b is a statistical table with type and id. The storage engines of both tables are MYISAM. It is necessary to update the data of table b to table a after count(1) group by(type). in those two fields. My current SQL is like this: update a _t1 set _t1.ready_count = ( SELECT count(1) FROM b _t2 WHERE _t2.tid = ...

10. MySQL write and insert data optimization configuration_MySQL

Introduction: MySQL write and insert data optimization configuration

[Related Q&A recommendations] ：

mysql Compare two table data optimization issues

Thinkphp where() method to find data optimization

The above is the detailed content of Detailed introduction to data optimization. For more information, please follow other related articles on the PHP Chinese website!