MySQL (1)---Index

Tag: Equals condition database design shows Ash SQ nbsp execution Info

Index

Basic concepts of indexing

You can understand the index as a special kind of directory, its existence is convenient for us to quickly query data.

I. Classification of the index

MySQL main types of indexes:1. Normal index, 2. Unique index, 3. Primary key index, 4. Composite index, 5. Full-text index.

1. General Index
Is the most basic index and it has no limitations.

2. Unique index
Like a normal index, the difference is that the value of the indexed column must be unique, but it allows for a null value. If it is a composite index, the combination of column values must be unique

3. Primary KEY index
is a special unique index, a table can have only one primary key, and no null value is allowed.

the difference between a primary key index and a unique index :
The primary key must be unique, but the unique index is not necessarily the primary key;
You can have only one primary key on a table, but you may have one or more unique indexes.  

4. Combined Index
An index contains multiple columns, and a composite index is recommended for actual development.

composite Index main features :
If we create a composite index (name, AGE,XB) , then it is actually equivalent to creating (name, AGE,XB), (name, age), (name) three indexes, which is called the best left prefix
Characteristics. Therefore, when creating a composite index, the columns that are most commonly used as constraints should be placed on the leftmost, decreasing in turn.

MySQL InnoDB build composite index a,b,c; So the query condition where a =xxx and c= XXX can use the index?
Answer: Yes.

Precautions :
1, for the composite index, when the query is used, it is best to put the order of the conditions in the index to find the highest efficiency;
SELECT * FROM table1 where col1=a and Col2=b and Col3=d
2. If you use where col2=b and col1=a or where col2=b will not use the index

5. Full-Text Indexing
The index of the full-text search.
Fulltext works best when searching for a very long article. Used in relatively short text, if the one or two lines of the word, the normal INDEX can also.

Comprehensive small Case Understanding:

For example, you are making a membership card system for a shopping mall.
This system has a membership table
The following fields are available:

Member ID INT
Member name VARCHAR (10)
Member ID number VARCHAR (18)
Member Phone VARCHAR (10)
Member Address VARCHAR (50)
Member Note Information TEXT

Then this membership number, as the primary key, using PRIMARY
Member name if you want to index, then it is the Normal index
Member ID number if you want to index, then you can choose Unique (unique, not allowed to repeat)
Member notes information, if need to build index, you can choose Fulltext, full-text search.
Fulltext , however, works best when it comes to searching for a long post.
Used in relatively short text, if the one or two lines of the word, the normal INDEX can also.

two. Advantages and disadvantages of indexes

Advantages:

First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.
Second, it can greatly speed up the retrieval of data, which is the main reason for creating indexes.
Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data.
Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.
By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

Disadvantages:

(1) When the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.
(2) The index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger

third, when to use the index

1: How to determine if you need to create an index

(1. A field that is more frequent as a query condition should create indexes that require regular GROUP by and ORDER by columns.

(2. Fields with poor uniqueness are not suitable for creating indexes individually, even if frequently as query criteria such as gender, ethnicity, political appearance
What are the key fields that are too unique? Data stored in these fields, such as Status fields, type fields, and so on, can be reused for a total of several or dozens of values, each of which exists in thousands or more records. There is absolutely no need to create a separate index for this type of field
(3. Fields that are updated very frequently are not suitable for creating indexes

Iv. Considerations for Indexing (optimization)

1. Use as few fuzzy queries as possible, and if you want to use the wildcard% can appear at the end, not at the beginning.
such as: Name like ' sheet% ', index valid
Instead: Name like '% Sheet ', index invalid, full table query

2:or will cause a full table scan.

3: Do not use not,! =, not in, not like, etc.

4. Use as few select* as possible, and select the fields that need to be displayed according to your needs

5. The index does not contain columns with null values
This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.

6. Do not perform calculations on columns, which will cause the index to fail and perform a full table scan

7. Using a short index
Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

8, the Union is not absolutely more efficient than or execution

We've talked about using or in the WHERE clause to cause a full table scan, generally, the data I've seen is recommended to use Union instead of or. It turns out that this argument is applicable to most of them.
One thing does not apply: If the query columns on or both sides are the same, then the Union is much worse than the execution speed with or, although here the Union scans the index, or the full table is scanned.
1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi= ' 2004-9-16 ' or fariqi= ' 2004-2-5 '
Spents: 6423 milliseconds. Scan count 2, logic read 14,726 times, physical read 1 times, pre-read 7,176 times.

Select Gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi= ' 2004-9-16 '
Union
Select Gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi= ' 2004-2-5 '
Spents: 11640 milliseconds. Scan Count 8, logic read 14,806 times, physical read 108 times, pre-read 1144 times.

v. Index mode (method) (storage engine)

There are two ways of MySQL:Hash and BTree.

Hash index
The so-called hash index, when we want to add an index to a column of a table, this column of the table is hashed algorithm, the hash value, and sorted on the hash array. So the hash index can be positioned one at a time, its efficiency is high, and the Btree index needs to go through multiple disk IO.
Because the hash index compares the value of the hash calculation, the efficiency of the tower is very high when the = in <=> (security equals), but our development generally chooses Btree,hash to have the following disadvantages.

(1) hash index can only meet "=", "in" and "<=>" query, can not use range query.
Because the hash index comparison is the hash value after the hash operation, so it can only be used for the equivalent of filtering, can not be used for range-based filtering, because the corresponding hash algorithm after processing the hash value of the size of the relationship, and can not be guaranteed and hash before the exact same.

(2) Hash index cannot be used to avoid sorting operations of data.
Because the hash index is stored in the hash after the hash value, and the size of the hash value is not necessarily the same as the key value before the hash operation, so the database can not use the index data to avoid any sorting operations;

(3) hash index cannot use partial index key query.
For the composite index, the hash index in the calculation of the hash value when the combination index key merge and then calculate the hash value together, rather than calculate the hash value alone, so by combining the index of the previous or several index key query, the Hash index can not be exploited.

(4) Hash index cannot avoid table scan at any time.
As already known, the hash index is the index key through the hash operation, the hash value of the result of hashing and the corresponding line pointer information stored in a hash table, because the different index keys exist the same hash value, so even if the number of data that satisfies a hash key value of the record bar, also can not The query is completed directly from the hash index, or the actual data in the table is accessed, and the corresponding results are obtained.

(5) When a hash index encounters a large number of equal hash values, performance is not necessarily higher than the B-tree index.

BTREE
The B-tree index is the most frequently used index type in a MySQL database. Simply understood, the tower is like a tree, b-tree the index needs to be able to access specific data from the root node to the point of the page node.
The Btree index speeds up access to data because the storage engine no longer needs to perform a full table scan to get the data needed, instead of searching from the root node of the index, which holds pointers to child nodes in the slot of the root node, and the storage engine looks down the bottom layer based on these pointers. By comparing the values of the node pages and the values you are looking for, you can find the appropriate pointers to the next level of child nodes, which actually define the upper and lower limits of the values in the child node pages, and the final storage engine either finds the corresponding value or the record does not exist.

The B-tree index can be used for column comparisons using =,;, >=, <, <=, or between operators. This index can also be used if the like parameter is a constant string that does not start with a wildcard character.

In addition, for clustered and nonclustered indexes, two articles are collected here:

1. Fast comprehension of clustered and nonclustered indexes

2. Is the primary key a clustered index?

think too much, do too little, the middle of the gap is trouble. Want to have no trouble, either don't think, or do more. Captain "14"

MySQL (1)---Index