Big Data processing is a troublesome problem, especially when up to technical standards not professional DBA when, for some aspects of the problem was a rogue database. So it is necessary to understand some of the databases of skills, of course, everyone has Air Jordan Outlet their own databases of skill, but the Eight Immortals, the use of different martial arts only. I put my summary of the most common ways to share with you, we have more of the database design and optimization techniques, try to append to review, and sometimes a complete blog comment more exciting than the theme. Method 1: Using table partitioning technology. The first time I heard the table partition, is a former oracle training. Since there is table partitioning oracle, mssql think whether there is the partition table, when I go home to a handful of google, data is still there, in that my children just to make one promotion, so that more people understand and apply these technologies ʱ?? Table partitioning is a large amount of data tables, some way the data is divided into several small tables to store physically, from a logical point of view or a big table. First to a structure: the figure although it is not very clear expression during the execution of the partition table, but you can see the partition table to use those objects, such as data files, groups of files, partition scheme, the partition function and the like. We have a user table (TestUser) as an example, Mens Nike Free 3.0 Wool Skin Shoes Blue Yellow assume that the table to be used to store data of some Chinese citizens, each data record the provinces (Area) for each person belongs, and the name of each person (UserName), following figure shows. When the amount of data up to 1 million when the query is slower, and this time the data optimization is urgent. Before optimization, according to the structure, the data read and write operations, etc., it will certainly put forward a number of solutions. Here on a program-partitioned tables to optimize queries of the database, here in the region respectively storing data, such as Guangdong citizens stored in AreaFile01.MDF file, Hunan citizen files stored in AreaFile02.MDF citizens Sichuan store In AreaFile03.MDF file, so other provinces, in order to achieve this we have to do partitioning scheme. In doing partitioning scheme, the first to find out the partition scheme to relate to the four objects: file group, file, partition function, partition scheme. a: the file group, used to organize data files (.MDF) of a virtual name, a Mens Nike Free Run 3 Shoes Black Red 3 file group can add multiple data files (.MDF). Open SQL Manager, locate specific database, then right [attribute] to enter the [File group] tab, add Area01, Area02, Area03, Area04 four groups of files. As shown: b: and then select the [File] tab, add AreaFile01, AreaFile02, AreaFile03, AreaFile04, AreaFile05, AreaFile06 six data file (.MDF), and then specify each file belonging to the file group (group a Nike Free TR Fit file can be stored a plurality of data files), as well as the physical path of the file. Here we have to see to understand, these data files, the data is physically split up a data table. That there is a table of 2015 Nike Free 5.0 data may be stored in AreaFile01, it is also possible to store in AreaFile02, as long as some way to specify their storage rule on the line. c: partition function is to store data specified by the rule. Is to tell SQL, to how the new data partition. Create a partition function can be used to lower the SQL statement to achieve. CREATE PARTITION FUNCTION partitionFunArea (nvarchar (50)) AS RANGE Left FOR VALUES ('Guangdong', 'Hunan', 'Sichuan') d: hard to create the file, and assign groups of files, but also to build a partition function, purpose only one, it is to create a partition scheme. Partitioning scheme can use the following code to create. CREATE PARTITION SCHEME partitionSchemeAreaAS PARTITION partitionFunAreaTO (Area01, Area02, Mens Nike Free 3.0 V2 Shoes White Black Red Area03, Area04) after intense four steps, a partition scheme on the show Nike Air Max in our eyes. The next thing is how are we going to spend the partition scheme. First we create one common table, then this table to specify a partitioning scheme. The following code. CREATE TABLE TestUser ([Id] [int] IDENTITY (1,1) NOT NULL, [Area] nvarchar (50), [UserName] nvarchar (50)) ON partitionSchemeArea ([Area]) in order to see results, then insert some data. INSERT TestUser ([Area], [UserName]) Values ('Sichuan', 'Shaw a'); INSERT TestUser ([Area], [UserName]) Values ('Sichuan', 'Shaw two'); INSERT TestUser ([ Area], [UserName]) Values ('Sichuan', 'Shaw three'); INSERT TestUser ([Area], [UserName]) Values ('Sichuan', 'Shaw four'); INSERT TestUser ([Area], [ UserName]) Values ('Guangdong', 'Zhang'); INSERT TestUser ([Area], [UserName]) Values ('Guangdong', 'Zhang two'); INSERT TestUser ([Area], [UserName]) Values ('Guangdong', 'Joe Smith'); INSERT TestUser ([Area], [UserName]) Values ('Hunan', 'Yang'); INSERT TestUser ([Area], [UserName]) Values ('Hunan' 'Yang Er'); to query all the data, you can use select * from TestUser; by partition query: to use the following method: select $ PARTITION.partitionFunArea ([Area]) as the partition number, count (id) as the number of records from 535807 103 Jordan CP3.VI White Black Game Royal Sport Red CP3 Shoes 2013 Sale TestUser group by $ PARTITION.partitionFunArea ([Area]) select * from TestUser where $ PARTITION.partitionFunArea ([Area]) = 1select * from TestUser where $ PARTITION.partitionFunArea ([Area]) = 2select * from TestUser Nike Air Max 95 where $ PARTITION. partitionFunArea ([Area]) = 3select * from TestUser where $ PARTITION.partitionFunArea ([Area]) = 4 renderings: Look me a simple partition table is not already done. Oh, of course, in practice, only to grasp this point is Nike Air Max 2015 Men not enough, such as adding a partition on the original partitioning scheme, delete a partition. Method 2: xml type from the table instead of the main design, so as to improve query performance. Optimize and improve the performance of the database, it is a good database design from the beginning. In a conference reservation system, for example, a book conference system includes a meeting time, meeting place, presenters, participants, informed people, recorder and other related information. In the TDD, DDD model dominated era, here, in order to better express my wish to elaborate on the issue, or to a table-driven model for development. User needs: a: a conference may have multiple hosts, although this situation is relatively small, but there might be. b: a plurality of participants in Mens Nike Free Run 3 Shoes Black Red 3 a conference, this is not difficult to understand. c: a meeting likely to Mens Nike Free 3.0 Wool Skin Shoes Blue Yellow let someone know, this person can participate or not participate in Nike Air Max 95 the meeting, usually the top. d: a meeting might have zero or more recorders. e: a need for remote video conference, projector, computer, microphone and other conference facilities in some of the equipment. f: After meeting reservation, or important information modification meeting time, location, etc., e-mail notification participants. General database design: a: to build a Meeting of the main table to hold the meeting name, meeting location, meeting time and other relevant information. b: to build a MeetingUser table storage moderator, Nike Air Max 2015 Women participants, informed people, recorder. c: Similarly, the equipment required by the Conference MeetingDevice table used to store related information. Figure: this table structure is relatively conventional design methods, but in practice, you will find some problems to be improved. For example: a: extract the relevant information at the time of a meeting, it will join multiple tables query. Such a query on a large program affects database performance. b: In doing enough to choke modify operation, the first to amend the relevant information of the 2015 Nike KD 7 Kids Basketball Shoes Sport Turq Charcoal Atomic Green Outlet main table, and then the main table associated with the child table to delete all information inserted once again, whether such operations enough to vomit blood. Of course, some improvement, it would be more to modify the data before and after modification, and then add, delete, modify means to achieve sub-meter data updates. Such operations in some ORM operation has been achieved, but their own code when the code to achieve, especially in a number of code, I felt always that worry. Tucao so much, is there a better solution? Of course, in SQL, we can eliminate the main XML data type from the table design. Figure: The above table structure design, is not there a little fresh feeling? Obviously, we can put a design flaw first table to eliminate. A conference-related information are stored in a record of a table, so that data appears to be not more intuitive it? a: a book for more information meeting, I do not need to connect to query multiple tables, I have to do is just use C # to parse the Linq.Xml check out the XML string can be. b: When modifying the operation, I just need to regroup XML data, an Update to update the information relevant to the meetings, the operation is not much easier. On the surface of this design has been perfect, but the user's needs are endless, one day, you receive a demand, a query for all users involved in the meeting (that is, as long as the moderator, participants, or record who including the user, we put out those records gave queries), Oh! My God