SQL Server Topic One: Index (medium)

write in front of the nonsense : Index This knowledge point, I do not know how many sides to see, the article on the internet a variety of, make me dizzy, make a good point is to test the benefits of the index, but also make a lot of test data out, interesting? Does MS itself not test? Is this test interesting? These tests I want to know to prove what ... Obviously bullying us these don't know the index of the cock silk. Reasoning, self-clothed! Index This thing is a very abstract stuff, there are many articles on the internet with the same abstract, let me this cock silk ugly down, not to mention understand, it seems to see, as I write this blog, I only know the index into clustered and nonclustered index ... Ha ha! Helpless Ah! I will start with a few questions, for my own doubts! Updating changes ... There are errors below!

1. Why index is introduced

2, why the index classification

3, the structure of the index is how to build

4. Index maintenance cost and correct use

First question? Why index is introduced

A big data table, find one of the data, how to find? Nonsense, of course, look for a few! will be tired Ah! What to do, a smart person like me began to think, the data to the specification, or order what, the search is simple, but, how to sort it? Sort all the data in a table? Every time new data comes in and inserts new data into the sorted place, this is a stupid way! I can make another ' watch ' like this, record the physical address of the new data inserted , so that on the basis of the original table, my new ' table ' has a much smaller amount of data, this small ' Table ' is the index, the data is very useful to find data, theoretically need two fields, the first: the physical address of the real data; second: The primary key ( which is the physical address of the data ); This is a small table, but what do we do with the data before the index is built? There is no way to find, find the key two more fields to insert the index is OK! So it's hard to index a table that already has big data! Summary: To find the data you need quickly!

second question? Why the index is classified

& nbsp   for the above-mentioned stupid method, is actually a clustered index, sorting data, MS is sorted according to a very good balance binary tree algorithm, sorted, the data is very standardized together, called the clustered index, the same data as the same algorithm sorting results, index clustered index only one! So it seems, on the ground gas more, what algorithm, not on the order? Westerners, like to make a noun, but also is just like Edison one by one of the test, which is the fastest sort of big data, use that algorithm! All say this is stupid method, ms The default index is not a clustered index , who is idle to sort out big data! People are smart, of course, the use of clever way! Nonclustered index is, that is, the practice of the small table above, do not move big data, in a small table to record the necessary information can be found in the primary key corresponding physical address on the small table OK, take the physical address, directly find the real data! Such a small table can have multiple, the real data of the primary key is unique, but there are many fields are unique but not selected as the primary key, such a field can also be built with the physical address a new table, so the nonclustered index can have multiple, because there is no sort of real data, Real data is not in accordance with a certain rule next to each other, so it is called non-aggregation! summary: In order to find the data quickly, a clever man and the fool to come up with different ways! In addition, the table is organized in two ways, B-tree (Balance tree) or heap! And you know why! B-Tree table because the table data has been engaged.

third question? How the structure of the index is constructed this is the play

To solve the second question, the problem follows, logically clustered indexes and nonclustered indexes are simple, but how to do in the real database? The result of a clustered index is a data table for a B-tree structure, but how does the implementation process be? What about nonclustered indexes? true clustered and nonclustered indexes what the hell is this thing? first of all, do not take care of MS or other database developers, how to solve, think for yourself?

First of all, the clustered index, since the big data is ordered, first there is the sorting algorithm, this well-known (http://www.cnblogs.com/lwzz/archive/2011/07/27/2119021.html) recognized B-tree algorithm, but so much data, How to listen to my words, according to the algorithm line up, I am not his father, why listen to me? There is the premise that people line up, there is extra space to move it!

Http://www.cnblogs.com/lwzz/archive/2012/08/05/2620824.html

Http://www.cnblogs.com/knowledgesea/p/3672099.html#top

A summary of the B algorithm is attached:

B-Tree: Two fork tree, each node only stores a keyword, equal to hit, less than the left node, more than the right node;

B-Tree: Multi-path search tree, each node storage M/2 to M keywords, non-leaf node storage points to the key range of sub-nodes;

All keywords appear in the whole tree, and only once, non-leaf nodes can hit;

B + Tree: On the basis of the tree, the leaf nodes are added to the list pointers, all the keywords appear in the leaf nodes, the non-leaf nodes as the index of the leaf nodes; B + The tree is always hit by the leaf knot.

SQL Server Topic One: Index (medium)