About the index composite index type created by mysql

About the index composite index type created by mysql

In the past two days, I have a strong feeling that I can't hold my mind in some special circumstances, and my mind is easy to empty or I don't know how to analyze the problem. For example, q: "What is the difference between hash and btree indexes?" Is that difficult? You only need to have a slight analysis of the two data structures to get the answer. The result is that you don't know where to start. Let's start with the question. What is the difference between the two.

1. the hash index can locate data at a time. Of course, if there is a large number of collisions, the performance will also decrease. The btree index has to be searched on the node. It is obvious that the efficiency of the hash index is higher than that of the btree in precise data search;

2. Therefore, inaccurate search is also obvious. Because the hash algorithm is based on the equivalent calculation, it is not supported to search for hash indexes in a range such as "like;

3. hash is not supported for the optimal prefix of the combined indexes supported by B-tree. Fields in the combined index are either all or all. When we mention the optimal prefix, it seems that sometimes it is too confusing to leave it empty;

4. hash does not support index sorting. The size of the index value and the calculated hash value are not necessarily the same.

Be diligent and empty.

An index is an object created on a database table or view to accelerate the query of a table or view.

Data is stored in clustered and non-clustered indexes.
Indexes can be maintained and managed as follows: Unique indexes, composite indexes, and automatically created indexes.

The index structure is from: Root Node ---> non-leaf node ---> leaf node

1. Clustered Index: The data stored in the table is stored in the order of indexes. the retrieval efficiency is higher than that of normal indexes, but it has a greater impact on the addition, modification, and deletion of data. The logical order determinesPhysical order.
Features:
(1) A table can create a maximum of 249 indexes.
(2) create a clustered index before creating a non-clustered Index
(3) Non-clustered index data and index order
(4) data and indexes are in different locations
(5) The index is stored on the leaf node. On the leaf node, there is a "Pointer" pointing directly to the data area to be queried.
(6) data will not be rearranged based on the order of index keys

(7) If range query is performed on this field, or the table is rarely added, deleted, or modified

Syntax for creating clustered indexes:
Create nonclustered index idximpID on emp (empID)
2. Non-clustered Index: The data storage sequence in the table is not affected. The retrieval efficiency is lower than that of clustered indexes, and there is little impact on the addition, modification, and deletion of data.
. YesBinary TreeThe data structure:
(1) No index and data disorder
(2) There is an index, and the data is in the same order as the index
(3) data will be rearranged according to the order of index keys
(4) One table can have only one index.
(5) The data pointed to by the pointer of the leaf node is also stored in the same location.
Syntax:
Create clustered index idxempID on emp (empID)
3. Unique index:The unique index ensures that the index column does not contain duplicate values.
Multiple columns can be used, but the index ensures that each value combination in the index column is unique.
Last name
Li 'er
Zhang San
Wang Wu
Syntax: create unique index idxempid on emp (surname, name)

4. Composite Index: If you create an index on more than two columns, it is called a composite index.
Then, there cannot be two rows with duplicate surnames.
Syntax:
Create index indxfullname on addressbook (firstname, lastname)

Note:: If the composite clustered index fields are queried separately.
With this problem, let's take a look at the following query speed (the result set contains 0.25 million pieces of data): (the date column fariqi is first placed in the starting column of the composite clustered index, and the username neibuyonghu is placed in the back column ):
(1) select gid, fariqi, neibuyonghu, title from Tgongwen where fariqi> ''2017-5-5''
Search speed: 2513 Ms
(2) select gid, fariqi, neibuyonghu, title from Tgongwen where fariqi> ''2004-5-5'' and neibuyonghu = ''''
Search speed: 2516 Ms
(3) select gid, fariqi, neibuyonghu, title from Tgongwen where neibuyonghu = ''''
Search speed: 60280 Ms
From the above experiment, we can see that the query speed is almost the same if only the starting column of the clustered index is used as the query condition and all columns of the composite clustered index are used at the same time, it is even a little faster than using all the composite index columns (when the number of query result sets is the same ).If only the non-starting column of the composite clustered index is used as the query condition, this index does not play any role.. Of course, the query speed of statements 1 and 2 is the same because the number of queried items is the same. If all columns of the composite index are used and the query results are few, the index Overwrite will be formed ", therefore, the performance can be optimal. Remember:Whether or not you frequently use other columns of the aggregate index, the most frequently used column must be the leading column.

5. system self-built indexes:When you use the T_ SQL statement to create a table using the PRIMARY KEY or UNIQUE constraint, a UNIQUE index is automatically created on the table.
Automatically created indexes cannot be deleted.
Syntax:
Create table ABc
(EmpID int primary key,
Firstname varchar (50) UNIQUE,
Lastname varchar (50) UNIQUE,
)
In this case, three indexes are created, but only one clustered index is available.


6. How to create an index:
1. Enterprise Manager
(1) Right-click a table, all tasks --- manage indexes, open manage indexes, and click "new" to create indexes.
(2) Design tables and manage indexes/keys in Design tables
(3) In the graph, after adding a table, right-click a table in the graph and there is an index/key"
(4) use the wizard, database --- index creation wizard
(5) statement through T-SQL
2. You can use the index optimization Wizard to optimize the index. You can use the Wizard to determine which columns are selected as index columns.
　
II,When to use clustered or non-clustered Indexes

The following table summarizes when to use clustered or non-clustered indexes (important ):

Action Description	Use clustered Index	Use non-clustered Index
Columns are sorted by group.	Ying	Ying
Returns data within a certain range.	Ying	Should not
One or few different values	Should not	Should not
Different decimal values	Ying	Should not
Different values of large numbers	Should not	Ying
Frequently updated Columns	Should not	Ying
Foreign key column	Ying	Ying
Primary Key Column	Ying	Ying
Frequently modify index Columns	Should not	Ying