MySQL Summary of differences and applicability of different indexes

MySQL currently has the following types of indexes:

• Normal index allows the same indexed content to appear (normal)

• Unique indexes unique can not appear the same value, can have a null value
• Primary key index Promary key does not allow the same value (uniqueness, and only one)
• A composite index is essentially a Jianjian of multiple words into an index, and the combination of column values must be unique

• Full-Text Indexing Fulltext index can target a word in a value, but is inefficient (not recommended, can be implemented by adding a keyword association column)

Build table when building
CREATE TABLE table_name[col_name Data type][unique|fulltext][index|  Key][index_name] (col_name[length]) [Asc|desc]

Note: Composite indexes are used with index names and associated column one by one, and if you want to index a column individually, you need to create a separate index

Description

1.unique| Fulltext is an optional parameter that represents a unique index, a full-text index, respectively 2.index and key are synonyms, both of which have the same effect and are used to specify the creation of index 3. col_name is the field column for which you want to create an index, and the column must be selected from multiple columns in that definition in the datasheet 4.index_name Specifies the name of the index, which is an optional parameter, and if not specified, the default col_name is the index value 5. Length is an optional parameter, which indicates the index size, only a field of type string can specify the index length 6.ASC or desc Specifies that an ascending or descending index value is stored

Creation, modification, deletion of indexes

1. Create an index using the ALTER TABLE statement

Apply to add indexes after table creation is complete

key, fulltext,index) [index name] (field name)

// Normal Index ALTER TABLE table_name ADD index index_name (column_list); // Unique index ALTER TABLE table_name add unique (column_list); // PRIMARY KEY Index Key (column_list);

Description
ALTER table can be used to create a normal index, a unique index, and a primary key index in 3 index formats.
table_name is the name of the table to increase the index,column_list indicates which columns to index, and columns are separated by commas.
Index name index_name optional , by default, MySQL assigns a name based on the first indexed column. In addition, ALTER TABLE allows you to change multiple tables in a single statement, so you can create multiple indexes at the same time.

2. Add index to table using CREATE index

The CREATE index can be used to add a normal index or a unique index to a table, which can be used when building a table.

If it is a Char,varchar type, length can be less than the actual length of the field, and length must be specified if it is a blob and text type.

// Create can only add these two indexes; CREATE INDEX index_name on table_name (column_list) Create UNIQUE INDEX index_name on table_name (column_list)
TABLE_NAME, index_name, and column_list have the same meaning as in the ALTER TABLE statement, and the index name is not selectable . In addition, the primary key index cannot be created with the CREATE INDEX statement .

3. Delete Index

Deleting an index can be accomplished by using the ALTER TABLE or the DROP INDEX statement. DROP index can be handled as a statement inside the ALTER TABLE.

key ;

In the previous two statements, the index index_name in table_name was removed. In the last statement, it is only used in the Delete primary key index, because a table may have only one primary key index , so you do not need to specify the index name. If the primary key index is not created, but the table has one or more unique indexes, MySQL deletes the first unique index.

If a column is removed from the table, the index is affected. For multiple-column combinations of indexes, if one of the columns is deleted, the column is also removed from the index. If you delete all the columns that make up the index, the entire index is deleted.

4. Modify the Index

MySQL does not have built-in modify index operations, you need to perform a delete operation to re-establish an index

Combined index and prefix index

Combined indexes and prefix indexes are a salutation to indexing techniques, not types of indexes.

 create table dm_user (ID int not  null  auto_increment comment ' primary key ',  login_name varchar () not null  comment ' login name ',  PASSWORD varchar ( 30) Not null  comment ' password ',  City varchar (30) not null  comment ' city ',  age int  Not  null  comment ' age ',  SEX int Not  null  Comment ' gender (0: Female 1: male) ',  primary 
     key   (ID)), comment means to add comments (note the problem of Chinese garbled, You need to set the encoding format of the table to UTF -8) 

Build a composite index that will be built into an index login_name,city

When the table is built, the length of the login_name is 30, which is 16, because in general, the length of the name does not exceed 16, which speeds up the index query, reduces the size of the index file, and increases the insert,update update speed.

It is worth mentioning that the MySQL composite index is based on the "leftmost prefix" in the form of index results. The simple understanding is that the combined index is used only from the far left, not as long as the query that contains these columns will use it. That is to say Index_name (column1 (length), Column2,column3 ...) Indexes are left-to-right, and if there is no left-front index, MySQL does not perform index queries .

ALTER TABLE table_name ADD index index_name (column1,column2,column3,// the equivalent of creating several composite indexes separately Column1,column2,column3,column4column1, Column2,column3column1,column2column1

If the index column length is too long, this column index will produce a large index file, not easy to operate, can be indexed using the prefix index, the prefix index should be controlled at a suitable point, control at 0.31 gold value (greater than this value can be created).

COUNT (DISTINCT (' column_name ', ten))/COUNT(*) from TABLE_NAME; --this value is greater than 0.31 to create the prefix index,distinct to repeat alter TABLE ' table_name ' ADD index ' uname ' (column_name (10)); --Increase the prefix index SQL, set the index of the column name to 10, which can reduce the index file size, speed up the index query speed MySQL string intercept function: Left (), right (), substring (), Substring_index () 

Index type

1,Fulltext

Full-text indexing, currently only supported by the MyISAM engine, and also supported by the InnoDB engine after MySQL 5.6. It can be used in CREATE TABLE, ALTER table, create INDEX, but currently only CHAR, VARCHAR, text column can be created on the text index.

Note: When data is large, the data is placed in a table without a full-text index and then created with the CREATE INDEX, which is much faster than establishing a fulltext for a table and then writing the data fulltext.

The full-text index appears to solve the problem that the where name like "%keyword%" is less efficient for text-based fuzzy queries.

It is extremely time-consuming to query when the data is large, and if there is no asynchronous IO processing, the process will be hijacked and wasted. Want to know about asynchronous Io, self-Google.

Use of full-text indexes:

Create ALTER TABLE table ADD INDEX ' Fullindex ' USING fulltext (' cname1 ' [, cname2 ...]);

Use SELECT * FROM table WHERE MATCH (cname1[,cname2 ...]) Against (' keyword ' MODE);

Where mode is the search mode (in BOOLEAN mode, in NATURAL LANGUAGE mode, in NATURAL LANGUAGE mode with query Expansion/with query Expans ION).

About these three kinds of search methods, simply divided into:

• Boolean mode, which allows Word to have some special characters to mark some specific requirements, such as + indicates must have,-indicates must not, * denotes generic match, similar to regular;
• Natural language mode is a simple word matching;
• Natural language patterns with expressions are first processed with natural language patterns, and the returned results are then matched to the expression.

The fulltext index is also indexed according to the word segmentation principle. In Latin, most of the alphabet, Word segmentation can be easily separated by the space.

But very Chinese can not be in this way to do participle. And what about that? The use of MySQL Chinese word-breaker mysqlcft, you can Chinese word segmentation, mysqlcft details.

2, HASH

A hash is a (key=>value) Form of a key-value pair, such as a function map in mathematics, that allows multiple keys to correspond to the same value, but does not allow a key to correspond to more than one value. It is because of this feature, hash is very suitable for indexing, a column or a number of columns to build a hash index, it will use this column or a few columns of value through a certain algorithm to calculate a hash value, corresponding to a row or a few rows of data (here conceptually and function mapping is different, do not confuse). In the Java language, each class has its own hashcode () method, and none of the display definitions are inherited from the object class, which makes each object unique and plays an important role in equal comparisons between objects and in serialized transmissions.

There are many ways to generate hash, which can guarantee the uniqueness of hash code. As in MongoDB, each document has a unique objectid that the system generates for it (including timestamps, host hash values, process PID, and self-increment IDs) is also a hash representation.

Because the hash index can be positioned one at a time, it does not need to be looked up by layer as a tree index, so it is highly efficient.

So why do you need other tree-shaped indexes?

Here is simply the difference between a tree index btree and a Hash index:

(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.
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 records that satisfy a hash key value, can not be from the hash The query is completed directly in the 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.
For low-selectivity index keys, if a hash index is created, then there will be a large number of record pointer information associated with the same hash value. This can be very cumbersome to locate a record, wasting multiple table data access and resulting in poor overall performance.

Hash index process, when we build a hash index for a column or column (currently only the memory engine explicitly supports such an index), a file similar to the following will be generated on the hard disk:

Hash value	Storage Address
1db54bc745a1	77#45b5
4bca452157d4	76#4556,77#45cc ...

...

The hash value is calculated by a specific algorithm by the specified column data, the disk address is the address where the data row is stored on the hard disk (there may be other storage address, in fact, memory will be the hash table into RAM).

When the where num= 18 query, 18 through the same algorithm to calculate a hash value ==> in the hash table to find the corresponding storage address ==> based on the storage address to obtain data.

Therefore, each time the query is to traverse the hash table, until the corresponding hash value, as described in the difference (4), the amount of data, the hash table will become large, performance degradation, traverse time increases, such as the difference between (5).

3, BTREE

Btree Tree Index is a kind of index value according to a certain algorithm, into a tree-shaped data structure, learning data structure for binary tree this data structure should be familiar with it. Like a binary tree, each query is started from the root of the tree's portal, traversing node in turn to get the leaf.

Btree in MyISAM form and InnoDB slightly different (although both use B+tree as the index structure, but there are some differences, database optimization-mysql the difference between InnoDB and myiasm engine)

In InnoDB, there are two forms: the first is the primary key form, and its leaf node holds the data, and not only the data of the index key, but also the data of the other fields. The second is secondary index, whose leaf node is similar to the normal btree, but also contains information that points to the primary key.

And in MyISAM, the primary key is not much different from the others. But the InnoDB is not the same as in the MyISAM, leaf node is not the primary key information, but point to the data file in the corresponding data row address information.

4, RTREE

Rtree is rarely used in MySQL, only supports Geometry data types (geometry data) and supports this type of storage engine with only MyISAM, BDb, InnoDb, NDb, archive.

The advantage with respect to Btree,rtree is the range lookup .

Disadvantages of the index:

• Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on tables. Because when you update a table, you not only save the data, but you also save the index file.
• Index files that create indexes that consume disk space. The general situation is not very serious, but if you create multiple combinations of indexes on a large table, the index file will grow quickly.
  Indexing is just one factor in efficiency, and if you have a table with large data volumes, you need to spend time studying to build the best indexes, or to refine the query statements.

Considerations and Design Tips:

1. 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 a null value. So we don't want the default value of the field to be null when the database is designed.

2. 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.

3. Index column sorting

A query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.

4.like Statement Operations
In general, it is not recommended to use the like operation, if not used, how to use is also a problem. Like "%aaa%" does not use the index and like "aaa%" can use the index.

5. Do not perform calculations on columns

This will cause the index to fail and perform a full table scan

6. Do not use not and <> to determine the operation

Not in, <>,! = Do not use indexes, but <,<=,=,>,>=,between,in are available for indexing.

Not in and <> this causes the index to fail, consider using exists or not exists instead of in and not (note that the in operation is available for indexing).

In the MySQL in statement is the appearance and the inner table as a hash connection, and the EXISTS statement is the external loop loop, each loop loop and then query the internal table. It is not accurate to say that exists is more efficient than the in statement. This is to distinguish the environment.

In short, the number of internal query loops (that is, the query table data less than the outside query table or the situation is not small) use exists efficiency; Conversely, the number of external query loops (that is, the outer query table is far less than the internal query table data) using in high efficiency.

Not-in and not-exists if the query statement uses not-in to perform a full-table scan of the outer surface, the index is not used, and the index on the table is still used by not Extsts's subquery. so no matter the table is large, using not exists is faster than not.

Use the between and interval instead of <> to determine the operation.

7. Explain can help developers analyze SQL issues.

Explain shows how MySQL uses indexes to process SELECT statements and join tables to help select better indexes and write more optimized query statements. Using the method, add explain to the SELECT statement.

8. The index should be built on a field with a unique value

9. The index is to be established on the field where the select operation is frequently performed.

This is because if these columns are seldom used, then there is no index that can significantly change the query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement.

10. For columns that are defined as text, image, and bit data types, the index should not be incremented. Because the amount of data in these columns is either quite large or has very little value.

11. Columns that appear in where and join need to be indexed

12. If a function is used in the query condition of the WHERE clause (for example: where year (column) = ...), MySQL will not be able to use the index

13. In a join operation (when data needs to be extracted from multiple data tables), MySQL can use the index only if the data type of the primary key and foreign key is the same , otherwise the index will not be used if it is established in time.

14, when there is a string in the where condition compared with the number (the same data type) do not use the index, or keyword will not use the index

Usage of various indexes

(1) For btree this MySQL default index type, has the universal applicability

(2) Because Fulltext is not very good for Chinese support, in the absence of plug-ins, it is best not to use. In small blog applications, when data is collected, a keyword list is created for it, and the keyword index can also play a role in the type.

(3) For some search engine-level applications, Fulltext is also not a good method of processing, MySQL full-text indexing file is relatively large, and the efficiency is not very high. Apache's Lucene may be your choice.

(4) It is because the hash table has an unparalleled advantage in handling smaller amounts of data, so hash indexes are good for caching (in-memory databases). such as the memory version of MySQL database memsql, the use of a wide range of caching tools Mencached,nosql database Redis, etc., all use a hash index this form. The memory engine of MySQL can also meet this demand.

What fields in the table should be selected as indexes during the actual operation?

To make indexes more efficient to use, when creating indexes, you must consider which fields to create indexes on and what types of indexes to create, there are 7 principles:

1. Select a Uniqueness Index
2. Index fields that often require sorting, grouping, and Union operations
3. Indexing a field that is frequently used as a query condition
4. Limit the number of indexes
5. Try to use an index with a small amount of data
6. Use prefixes as far as possible to index
7. Delete indexes that are no longer used or used infrequently

MySQL Summary of differences and applicability of different indexes