Primary key, combined primary key, clustered index, nonclustered index, unique index

Foreword: Based on the Oracle database to talk about the index of the problem, and under what index, the combination of the primary key, how to customize their own index according to the actual business needs, the application of the primary key to improve the performance of the system. 1: Primary key? Unique in the table, is also a clustered index. Acts on fast queries. The column is unique. Java code Copy Code collection code1. ID Number ( -,0) PRIMARY KEY not NULL,2: Combine primary keys? Multiple fields in a table are unique in the table and are clustered indexes. Acts on fast queries. The combined column is unique. Java code Copy Code collection code1. CREATE TABLE TAB (2. ID Number (Ten),  3. NAMES VARCHAR2 ( -,0),  4. Age VARCHAR2 (2,0),  5. CONSTRAINT pk_tab PRIMARY KEY (names,age)6.); 7. ALTER TABLE TAB ADD <aclass="Baidu-highlight"href="https://www.baidu.com/s?wd=constraint&tn=44039180_cpr&fenlei= Mv6quakxtzn0izrqihckpjm4nh00t1y3uyd4n1n4nhbyrhwhuwfs0zwv5hcvrjm3rh6spfkwumw85hfynjn4nh6sgvpst6k1tl0qnfk1tl0z5hd0igf _5y9yiz0lqzqlpa-bmyt8mh7guzr8mvqvql7dugpypyq8q1cdn1d3rhtdn0"target="_blank">constraint</a> pk_name PRIMARY <aclass="Baidu-highlight"href="https://www.baidu.com/s?wd=key&tn=44039180_cpr&fenlei= Mv6quakxtzn0izrqihckpjm4nh00t1y3uyd4n1n4nhbyrhwhuwfs0zwv5hcvrjm3rh6spfkwumw85hfynjn4nh6sgvpst6k1tl0qnfk1tl0z5hd0igf _5y9yiz0lqzqlpa-bmyt8mh7guzr8mvqvql7dugpypyq8q1cdn1d3rhtdn0"target="_blank">key (names,age)--table created and modified </a>3: Clustered index? function to improve query speed (for example, through a table's primary key query), when the Query field update operation is less than the conditional read operation, the use of clustered index! By creating a composite primary key in the table, the primary key is also creating a clustered index. Java code Copy Code collection code1. Create clustered index index name on table (field)4: Nonclustered indexes? function to improve the query speed, when the query field update operation is greater than the conditional read operation, the use of nonclustered index! (some say the unique index is also a nonclustered index, there is controversy!) Please leave a message~)5: Unique index? Improve query speed. Java code Copy Code collection code1.--Unique index2. CREATE UNQIUE index index name on table (field)3.--Normal Index4. CREATE index index name on table (field6: The effect of creating an index, the advantage of using an index:1, unique index to ensure data uniqueness2and speed up the retrieval of data3, speeding up the connection between tables4, reducing grouping and sorting times5, using the optimizer to improve system performance two, the principle of using indexes:1, creating an index on a column that needs to be searched frequently2, create an index on the primary key3, creating indexes on columns that are often used for connections4, you often need to create an index on a column that searches by scope5, frequently need to sort columns on the CREATE INDEX6, the principle of creating indexes on columns that are often used for where clauses, without creating indexes:1, queries rarely used and referenced columns are not indexed2, do not index columns with only a few values3, columns defined as text, image, bit are not indexed4, you should not build indexes when you need update performance much higher than select performance. Common commands:1, Sp_helpindex: Index information on a report table or view2, DBCC SHOWCONTIG: Displays fragmentation information for data and indexes for a specified table3, DBCC DBREINDEX: rebuilding one or more indexes in a specified database4, DBCC INDEXDEFRAG: Defragment a clustered or secondary index that specifies a table or view five, optimize the index:1, rebuilding indexes (DBCC DBREINDEX)2, Index Tuning Wizard3, organizes clustered indexes and secondary index fragments for the specified table or view (DBCC INDEXEFRAG)7: The difference between the index and the primary key must be an index, the index is not necessarily the primary key, the primary key must be unique, the index is not necessarily unique (so the index is unique) a table can have only one primary key, but there may be multiple indexes8: The difference between a clustered index and a nonclustered index read operation is greater than a write operation with a clustered index, and a write operation is greater than a read operation with a nonclustered index. Verbose: Clustered and nonclustered indexes. The clustered index indicates that the data stored in the table is stored in the order of the index, and the retrieval efficiency is higher than that of the nonclustered index, but it has a large impact on the data update. A nonclustered index indicates that the data is stored in one place, the index is stored in another place, the index has a location where the pointer points to the data, and the nonclustered index is less efficient than a clustered index, but has less impact on data updates. A popular example that shows a clustered index, a nonclustered index. In fact, the body of our Chinese dictionary is itself a clustered index. For example, we have to check the word "Ann", it will be very natural to open the first few pages of the dictionary, because "ann" Pinyin is "an", and alphabetical order of Chinese characters in the dictionary is the English letter "a" beginning and "Z", then the word "Ann" naturally ranked in the front of the dictionary. If you have turned over all the parts that begin with "a" and still cannot find the word, then it means that you do not have the word in your dictionary, and if you look up the word "Zhang", you will also turn your dictionary into the last part, because the pinyin of "Zhang" is "Zhang". That is, the body part of the dictionary is itself a directory, and you do not need to look up other directories to find what you need to find.　   We refer to this body of content itself as a directory of certain rules, called a "clustered index." If you know a word, you can quickly check it out automatically. But you may also encounter the words you do not know, do not understand its pronunciation, at this time, you can not follow the method to find the word you want to check, and need to go to the "radicals" to find the word you are looking for, and then according to the page number after the word directly to a page to find the word you are looking for. But the sort of words you find in combination with the "radicals" and "gept" is not really the sort method of the body, for example, you check the word "Zhang", we can see in the Gept table after the Radicals "Zhang" page number is 672 pages, gept table "Zhang" above is "Chi" word, but the page number is 63 pages, "Zhang" below is "crossbow "Word, page is 390 pages. Obviously, these words are not really in the "Zhang" the word of the upper and lower side, now you see the continuous "Chi, Zhang, crossbow" three words is actually their order in the nonclustered index, is the dictionary body of words in the non-clustered index mapping. We can find the words you need in this way, but it takes two procedures to find the results in the catalog and then turn to the page numbers you need. We put this kind of directory purely as a directory, the body is purely the sort of body is called "nonclustered index". Action description Using a clustered index using a nonclustered index column is often sorted by grouping should be one or very few different values should not be returned a range of data should not be a small number of different values should not be the different values of large data should not be frequently updated columns should not be frequently modified index columns should not be Should be the primary, the foreign key column should be         

get "" Java background Framework SPRINGMVC Integration MyBatis Framework source bootstrap HTML5 MySQL Oracle

Primary key, combined primary key, clustered index, nonclustered index, unique index