Detail MySQL Index

This article from how to build MySQL index and introduce the type of MySQL index, and then talk about the pros and cons of MySQL index, as well as the place to be aware of when building an index

First of all: first assume that there is a table, the table data has 10W data, one of the data is Nickname= ' CSS ', if you want to take this data, the need for some SQL is a SELECT * from award WHERE nickname = ' css '

In general, in the absence of indexing, MySQL needs to scan the entire table and scan 10W data to find this data, if I nickname on the index, then MySQL only need to scan a row of data and for us to find the nickname= ' CSS ' data, is not the feeling of performance improved a lot of ....

The MySQL index is divided into single-column indexes (primary key index, index only, normal index) and combined index.

Single-column index: An index consists of only one row, and one table can have multiple single-column indexes.

Composite index: A combined index contains two or more than two columns.

Table of cases used in this article

CREATE TABLE ' award ' (   ' id ' int (one) not NULL auto_increment COMMENT ' user ID ',   ' aty_id ' varchar () NOT NULL DEFAULT ' COMMENT ' Active scene id ',   ' nickname ' varchar (1) NOT null DEFAULT ' COMMENT ' user nickname ',   ' is_awarded ' tinyint  Default 0 COMMENT ' user is receiving ',   ' award_time ' int (one) not null DEFAULT 0 COMMENT ' bonus time ',   ' account ' varchar (a) NOT NULL Default ' COMMENT ' account ',   ' password ' char (+) NOT null DEFAULT ' COMMENT ' password ',   ' message ' varchar (255) NOT NULL DE  FAULT ' COMMENT ' prize information ',   ' created_time ' int (one) not null DEFAULT 0 COMMENT ' creation time ',   ' updated_time ' int (one) NOT NULL Default 0 COMMENT ' Update time ',   PRIMARY KEY (' id ')) engine=innodb auto_increment=1 default Charset=utf8 comment= ' award-winning information table ';

(i) Creation of indexes

1. Single-Column indexing

1-1) Normal index, this is the most basic index,

Its SQL format is CREATE index indexname on ' TableName ' (' Field name ' (length)) or ALTER TABLE TableName ADD INDEX indexname (' field name ' (length))

The first way:

  CREATE INDEX account_index on ' award ' (' Account ');

The second way:

ALTER TABLE Award ADD INDEX account_index (' account ')

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

1-2) A unique index, similar to a normal index, except that the unique index requires that the values of all classes be unique, as is the primary key index. But he allowed a null value,

Its SQL format is CREATE UNIQUE INDEX indexname on ' TableName ' (' Field name ' (length)); or ALTER TABLE TableName ADD UNIQUE (column_list)

CREATE UNIQUE INDEX account_unique_index on ' award ' (' Account ');

1-3) primary key index, no null values are allowed, (the primary key index plays a critical role in the InnoDB engine in B+tree)

A primary key index establishes a rule that an int is better than a varchar, typically created at the time of a table, preferably a column that is not related to other fields of the table or a column that is not related to a business. Typically set to int and are auto_increment self-increment type

2. Combined Index

A table with multiple single-column indexes does not represent a composite index, a common point of the composite index is: contains multiple fields but only the index name

Its SQL format is CREATE INDEX indexname on ' TableName ' (' Field name ' (length), ' field name ' (length),...);

CREATE INDEX nickname_account_createdtime_index on ' award ' (' Nickname ', ' Account ', ' created_time ');

If you set up a composite index (NICKNAME_ACCOUNT_CREATEDTIME_INDEX) then he actually contains 3 indexes (nickname) (Nickname,account) (Nickname,account, Created_time)

To follow the "leftmost prefix" of the MySQL composite index when using a query, let's analyze what is the leftmost prefix: and the index where the condition should be sorted by the time the index is indexed

1, do not start the query by the leftmost column index (multi-column index) such as index (' C1 ', ' C2 ', ' C3 ') where ' c2 ' = ' AAA ' does not use the index, where ' c2 ' = ' aaa ' and ' C3 ' = ' sss ' cannot use the index

2, the query in a column has a scope query, the right of all the columns can not use the query (multi-column query)

Where c1= ' xxx ' and c2 like = ' aa% ' and c3= ' SSS ' query will only use the first two columns in the index because like is a range query

3, can not skip a field to query, so that the use of the index, such as my SQL is

Explain SELECT * from ' award ' where nickname > ' rsuqfzpkdz3r ' and account = ' Dyxjoqzq2rd7 ' and created_time = 144956782 2; At this point he cannot use his combined index.

Because my index is (nickname, account, Created_time), if the first field appears to look for a range symbol, then the index will not be used, and if I am the second or third field using the lookup of a range symbol, then he will use the index, which is ( Nickname),

Because it says "set up a composite index (nickname, account, Created_time), three indexes will appear

(3) Full-text indexing

Text field if the normal index is established, only the characters in front of the field contents of the text are indexed, and the character size is specified according to the size declared when the index is indexed.

If more than one character is present in the text, and you need to find it, the condition can only be where column lick '%xxxx% ' will invalidate the index

This time the full-text index is praying for a role.

ALTER TABLE tablename ADD fulltext (Column1, Column2)

With full-text indexing, you can use the Select query command to retrieve data records that contain one or more given words.

Elect * from Tablenamewhere MATCH (Column1, Column2) against (' xxx′, ' sss′, ' ddd′ ')

This command will query all data records for XXX, SSS, and DDD in the Column1 and Column2 fields.

(ii) deletion of the index

Delete indexed MySQL format:D ORP index indexname on ' TableName '

(iii) Advantages of using indexes

1. You can guarantee the uniqueness of each row of data in a database table by creating a unique index or a primary key index.
2. Indexing can greatly improve the retrieved data and reduce the number of rows retrieved by the table
3. Connection conditions in the table connection can speed up the table directly connected to the table
4. Data retrieval in grouping and sorting words can reduce the time spent in grouping and sorting in query time (database records are reordered)
5. Indexing, using indexes in queries can improve performance

(iv) Disadvantages of using indexes

1. Creating indexes and maintaining indexes can be time consuming, increasing as the amount of data increases
2. The index file takes up physical space, and each index takes up a certain amount of physical space in addition to the data tables that occupy physical space
3. When the data of the table is Insert,update,delete, the index is also maintained dynamically, which slows down the maintenance of the data, and indexes the index file that consumes disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell quickly.

(v) Areas to be aware of when using indexes

When indexing, you should consider that indexes should be built on some columns in a database table which indexes need to be established and which are redundant.
Generally speaking
1. You can speed up indexing on columns that you often need to search
2. Column uniqueness can be ensured on the primary key column
3. Adding indexes on table-to-table and join conditions can speed up the connection query
4. When sorting (order by) is often required, indexing on the distinct column (group by) can speed up the time to sort the query (the individual order by cannot be indexed, the index considers adding where or adding limit)
5. In some where after < <= > >= between in and a case like to establish the index of the field (B-tree)

6.like statement If you set an index on the nickname field. When the query is nickname Lick '%abc% ', then this index will not work. and nickname Lick ' abc% ' would be useful for indexing

7. The index does not contain a null column, if the column contains null values will not be included in the index, if there is a column in the composite index has a null value then this combined index will be invalidated, generally need to give the default value of 0 or ' ' string

8. Using a short index, if one of your fields is char (32) or int (32), specify the prefix length when creating the index, such as the first 10 characters (provided that the majority value is unique ...) A short index can improve query speed and can reduce disk space and reduce i/0 operations.

9. Do not perform calculations on the columns, which will invalidate the MySQL index and also perform a full table scan

10. The smaller the data type, the better, because typically smaller data types typically occupy little space in disk, memory, CPU, cache, and are processed faster

(vi) Under what circumstances do not create an index

1. Columns that are seldom used in the query should not create an index, but will also degrade MySQL performance and increase space requirements if an index is established.
2. Columns with few data should not be indexed, such as a gender field of 0 or 1, in a query, the data in the result set accounts for a large proportion of the data rows in the table, the number of rows that MySQL needs to scan, and the addition of indexes, does not improve efficiency
3. Columns defined as text and image and bit data types should not increase the index.
4. When the table modification (update,insert,delete) operation is much larger than the search (SELECT) operation, the index should not be created, and the two operations are mutually exclusive relationships

Detail MySQL Index