About MySQL index composite index type

These two days have a very strong feeling is oneself in some special situation or hold, brain easy to vent or say suddenly don't know how to analyze the problem, for example, ask "hash and btree Index difference", this is very difficult. Just to grasp the two data structures a little analysis can come to an answer, the result is suddenly do not know where to start. Let's get to the chase. What is the difference between the two?

1. Hash index lookup data is basically able to locate data at once. Of course, there are a lot of collisions, performance will also fall.

The Btree index will have to be found on the node next to each other, and it is very obvious that the hash index is more efficient than btree in the exact search for data.

2. It is also very obvious that the hash algorithm is based on the equivalence calculation. Therefore, the "like" range lookup hash index is invalid, not supported;

3. The optimal prefix for Btree supported federated indexes. Hash is also impossible to support. The fields in the federated index are either full or unused. The best prefixes have been thrown into confusion, it seems sometimes too bad.

4. Hash does not support index sorting. The index value is not necessarily the same size as the calculated hash value.

Diligent brain, less emptying.

An index is an object created on a database table or view to speed up queries against a table or view.

Grouped by storage: Clustered vs. nonclustered indexes
In terms of maintenance and management, the index is divided into: Unique indexes, composite indexes, and indexes that the system actively creates.

The structure of the index is by: root node---> Non-leaf node---> Non-leaf node---> leaf node

1. Clustered index : The data stored in the table is stored in the order of the index, the retrieval efficiency is higher than the normal index, but the impact on the new/changed/deleted data is much larger.

The logical order determines the physical order of the corresponding rows in the table.

Characteristics:
(1) A table capable of creating up to 249 indexes
(2) Build a clustered index talent to create a nonclustered index
(3) Non-clustered index data and index different order
(4) Data and index in different locations
(5) The index is stored on the leaf node, and a "pointer" on the leaf node directly points to the data region to be queried
(6) data is not arranged again according to the order of the index keys

      (7) assumes that a range query is made on this field, or that the table has very few additions or deletions to

to create a clustered index syntax:
   Create nonclustered index idximpid on EMP (EmpID)
2 nonclustered index : does not affect the order in which the data is stored in the table, The retrieval efficiency is lower than the clustered index, and there is very little impact on data additions/changes/deletions
.

is through the data structure of the two-tree to describe the narrative, logical sequence , characteristics:
(1) No index. Data unordered
(2) There is an index. Data and index in the same order
(3) data is arranged again according to the order of the index keys
(4) A table can have only one index
(5) The data that the pointer of the leaf node points to is also stored in the same location
Grammar:
Create CLUSTERED INDEX idxempid on EMP (EmpID)
3. Unique index: A unique index ensures that indexed columns do not include repeated values.
Ability to use multiple columns, but indexes ensure that each value combination in an indexed column is unique
Name
Lee
Zhang San
Harry
Syntax: Create unique index idxempid on EMP (surname, first name)

4. Composite Index : If you create an index on more than two columns, it is called a composite index.
So. It's not possible to have two lines of surname and name repeated.
Grammar:
Create INDEX Indxfullname on AddressBook (firstname,lastname)

Note: Assume that the composite clustered index field is queried separately.
With this problem. Let's take a look at the following query speed (the result set is 250,000 data): (the date column Fariqi first in the composite clustered index's starting column.) Usernameneibuyonghu row in the rear column):
(1) Select Gid,fariqi,neibuyonghu,title from Tgongwen where fariqi> ' 2004-5-5 '
Query speed: 2513 ms
(2) Select Gid,fariqi,neibuyonghu,title from Tgongwen where fariqi> ' 2004-5-5 ' and neibuyonghu= ' office '
Query speed: 2516 ms
(3) Select Gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu= ' office '
Query speed: 60280 ms
From the above experiment, we can see that the assumption is that only the starting column of the clustered index is used as the query condition and the query speed of all columns used at the same time for the composite clustered index is almost the same. Even faster than using all of the composite index columns (in the same number of query result sets);assuming that the non-starting column of the composite clustered index is used only as a query condition, the index is not a matter of any purpose.。 Of course, the query speed of statements 1 and 2 is the same as the number of entries queried. Assuming that all columns of the composite index are used, and that the query results are small, an index overlay is formed. Thus, the performance can be optimally achieved. At the same time, please remember:regardless of whether you often use other columns of the aggregated index, the leading column must be the most frequently used column.

5, the system self-built index:When you create a table using the T_SQL statement, use the primary KEY or the unique constraint. Creates a unique index on the table on its own initiative
An index that you create on your own initiative cannot be deleted.
Grammar:
CREATE TABLE ABc
(EmpID int primary KEY,
FirstName varchar (a) UNIQUE,
LastName varchar (a) UNIQUE,
)
This results in three indexes, but there is only one clustered index.


6. How to create an index:
1. In Enterprise Manager
(1) Right-click a table, all Tasks---Manage indexes, open the admin index. Click "New" to create an index
(2) Designing tables in the design table, managing indexes/Keys
(3) in the diagram, when you join a table, right-click a table in the diagram with an index/key
(4) Create an index wizard---the database by using the wizard
(5) through T-SQL statements
2. The wizard that can optimize the index through the Index Optimization wizard. It allows you to decide which columns to select as indexed columns.
　
Twowhen to use clustered or nonclustered indexes

The following table summarizes when to use clustered or nonclustered indexes (very important):

Description of movements	Using Clustered Indexes	Using Nonclustered indexes
Columns are often sorted by grouping	Should	Should
Return data in a range	Should	should not
One or very few different values	should not	should not
A small number of different values	Should	should not
A large number of different values	should not	Should
Columns that are frequently updated	should not	Should
FOREIGN key columns	Should	Should
Primary key columns	Should	Should
frequently changing index columns	should not	Should

About MySQL index composite index type