MySQL index principle and usage Daquan

Lin Bingwen Evankaka original works. Reprint please specify the source Http://blog.csdn.net/evankaka

First, the index introduction

An index is a structure that sorts the values of one or more columns in a database table. In a relational database, an index is a database structure that is related to a table, which enables the SQL statements that correspond to tables to execute faster. The index acts as a catalog of books, and you can quickly find what you want based on the page numbers in the catalog. When a table has a large number of records, to query the table, the first way to search information is a full table search, is to remove all records one by one, and the query criteria for one by one comparison, and then return to meet the criteria of the record, which will consume a lot of database system time and cause a lot of disk I/O operations; , and then find the index value in the index that matches the criteria of the query, and finally find the corresponding record in the table by ROWID (equivalent page number) saved in the index.
An index is a separate, physical database structure that is a collection of one or more column values in a table and a logical pointer list corresponding to the data pages that physically identify those values in the table. The index provides pointers to the data values stored in the specified columns in the table, and then sorts the pointers according to the sort order that you specify. The database uses the index in the same way that you use an index in a book: it searches the index to find a specific value, and then follows the pointer to the row that contains the value. In a database diagram, you can create, edit, or delete each index type in the Indexes/Keys property page of the selected table. The index is saved in the database when you save the table to which the index is attached, or when you save the diagram where the table is located.

MySQL Index overview

All MySQL column types can be indexed. Using indexes on related columns is the best way to improve the performance of select operations. Defines the maximum number of indexes and the maximum index length per table based on the storage engine. All storage engines support at least 16 indexes per table with a total index length of at least 256 bytes. Most storage engines have a higher limit.

In MySQL 5.1, for MyISAM and InnoDB tables, the prefix can be up to 1000 bytes long. Note that the prefix limit should be measured in bytes, whereas the prefix length in the CREATE TABLE statement is interpreted as the number of characters. When specifying a prefix length for a column that uses multibyte character sets, you must consider it.

You can also create Fulltext indexes. The index can be used for full-text search. Only the MyISAM storage engine supports Fulltext indexes and is only char, varchar, and text columns. Indexes are always made for the entire column, and local (prefix) indexes are not supported. You can also create indexes for spatial column types. Only the MyISAM storage engine supports space types. The spatial index uses the R-tree. The memory (HEAP) storage engine uses a hash index by default, but it also supports B-tree indexing.

Btree Index and Hash index

For Btree and hash indexes, when you use =, <=>, in, is null, or is the NOT NULL operator, the comparison relationship between the key element and the constant value corresponds to a range condition. Hash indexes also have some other characteristics: they are used only for equality comparisons (but soon) using the = or <=> operator. The optimizer cannot use a hash index to speed up the order by operation. (This type of index cannot be used to search for the next entry sequentially). MySQL cannot determine approximately how many rows between two values (this is used by the range optimizer to determine which index to use). If you change a MyISAM table to a memory table of hash-index, some queries will be affected. You can only use the entire keyword to search for a row. (with a B-tree index, the leftmost prefix of any keyword can be used to find rows).

For Btree indexes, when using >, <, >=, <=, between,! =, or <>, or like ' pattern ' (where ' pattern ' does not start with a wildcard) operator, The comparison relationship between the key element and the constant value corresponds to a range condition. "Constant value" means a constant in the query string, a const in the same join, or a column in the system table, the result of an unrelated subquery, and an expression that consists entirely of a subexpression of the preceding type.

The following is an example of a query with a scope condition in the WHERE clause.

The following range queries apply to Btree indexes and hash indexes:

SELECT * from t1 WHERE key_col = 1 OR key_col in (15,18,20);the following range queries apply to Btree indexes
SELECT * from t1 WHERE key_col > 1 and Key_col < 10;
SELECT * from T1 WHERE key_col like ' ab% ' OR key_col between ' bar ' and ' foo ';

How MySQL uses the index

Indexes are used to quickly find rows that have a specific value in a column. Without an index, MySQL must start with the 1th record and then read the entire table until the relevant rows are found. The larger the table, the more time it takes. If there is an index to the column queried in the table, MySQL can quickly reach a location to find the middle of the data file, and there is no need to look at all the data. If a table has 1000 rows, this is at least 100 times times faster than sequential reads. Note If you need to access most of the rows, sequential reads are much faster because at this point we avoid disk searches.

Most MySQL indexes (PRIMARY KEY, UNIQUE, index, and fulltext) are stored in the B-tree. Only the index of the spatial column type uses the R-tree, and the memory table also supports the hash index.

Ii. methods of Use

First create a table:

CREATE TABLE    t_student    (        stu_id int not null,        Stu_name CHAR (TEN) not NULL,        Stu_class int. not null,< C12/>stu_sex CHAR (2) NOT NULL,        stu_age INT not NULL,        PRIMARY KEY (stu_id)    )    Engine=innodb DEFAULT Charset=utf8;

1.1 General IndexCreate an index
This is the most basic index and it has no limitations. It is created in the following ways:

CREATE INDEX index_id  

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.
Modify Table Structure

ALTER TABLE t_student ADD INDEX  index_name (Stu_name (4));

Specify directly when creating a table

CREATE TABLE    t_student1    (        stu_id int not null,        Stu_name CHAR (TEN) not NULL,        Stu_class int. not null,< C5/>stu_sex CHAR (2) NOT NULL,        stu_age INT not NULL,        PRIMARY KEY (stu_id),        INDEX index_name (Stu_name (5))    )    Engine=innodb DEFAULT Charset=utf8;

syntax for dropping an index

DROP index index_id on  t_student;drop index index_name on  t_student;

Results

1.2. Unique indexThe primary key is a unique index, the primary key is specified when the table is built, generally with the auto_increment column, the keyword is primary key. It is similar to the previous normal index, except 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. It is created in the following ways:
Create an index

Modify Table Structure

specify directly when creating a table

1.3, Multi-column index

Create an index

CREATE  

CREATE INDEX Index_age_aex on t_student (stu_age,stu_sex);

Modify Table Structure

ALTER mytable ADD  

specify directly when creating a table

An advantage of a multicolumn index, which is manifested by the concept of a leftmost prefixing called the leftmost prefix. Continuing to consider the previous example, we now have a multi-column index on the FirstName, LastName, and age columns, which we call the index fname_lname_age. When the search condition is a combination of the following columns, MySQL uses the fname_lname_age index:
Firstname,lastname,age
Firstname,lastname
FirstName
On the other hand, it is equivalent to the index we created (Firstname,lastname,age), (Firstname,lastname), and (FirstName) on these column combinations. The following queries all have the ability to use this Fname_lname_age index:
Select Peopleid from people Where firstname= ' Mike ' and lastname= ' Sullivan ' and age= ' 17 ';
Select Peopleid from people Where firstname= ' Mike ' and lastname= ' Sullivan ';
Select Peopleid from people Where firstname= ' Mike ';
The following queries are not able to use this Fname_lname_age index:
Select Peopleid from people Where lastname= ' Sullivan ';
Select Peopleid from people Where age= ' 17 ';
Select Peopleid from people Where lastname= ' Sullivan ' and age= ' 17 ';

1.4. Full-Text Indexing

Full-text indexing (also known as full-text search) is a key technology used by search engines at present. It can use "word segmentation technology" and other algorithms to intelligently analyze the text of the key words in the frequency and importance, and then follow certain algorithm rules to intelligently filter out the search results we want. Here we don't go into the bottom-up principle of implementation, and now let's look at how to create and use full-text indexing in MySQL.
in MySQL, creating a full-text index is relatively straightforward. For example, we have an article table (article) with three fields for the primary key ID (ID), the title of the article, and the contents of the article (content). Now we want to be able to create full-text indexes on title and content two columns, and create SQL statements for article tables and full-text indexes as follows:
--Create a article table

CREATE TABLE article (ID INT UNSIGNED auto_increment not NULL PRIMARY KEY,  title VARCHAR (+), content TEXT, fulltext (title, content)) Engine=myisam DEFAULT Charset=utf8;

Look at the index

The above is a SQL example that creates a full-text index while creating a table. Also, if we want to create a full-text index of the specified field for a table that already exists, also take the article table as an example, we can create it using the following SQL statement:
--Create a full-text index for the title and content fields of an existing article table
--The index name is fulltext_article

ALTER TABLE article ADD fulltext INDEX fulltext_article (title, content)

After you create a full-text index in MySQL, you should now know how to use it. We must use unique syntax to query with full-text indexes. For example, we want to retrieve the specified query string in full text in the title and content column of the article table, and you can write the SQL statement as follows:

SELECT * from article WHERE MATCH (title, content) against (' query string ');

Precautions

The search must be on an indexed column of type Fulltext, and the column specified in match must be specified in fulltext
Can only be applied in tables with table engine MyISAM type (MySQL 5.6 can also be used in the InnoDB table engine)
You can only create a full-text index above columns of char, varchar, text type
Like a normal index, you can specify it when you define a table, or you can add or modify a table after you create it
For a large order of record inserts, creating an index after inserting data into a table that has no index is much faster than inserting it into an indexed data table
The search string must be a constant string and cannot be the column name of the table
There is no match (limited only in natural search) when the search record has more than 50% selectivity

1.5. Verify that use is index

Some of the 1.3. Here you can connect to the above multi-column index, where I have added some data in the following

You can use the statement EXPLAIN SELECT * from t_student WHERE stu_age = 12 To verify that the index is used

The following instructions use the index

If you do not use the index, the result should be as follows:

The above just verifies whether the index is used, and then looks at the results using indexes and unused indexes:

The result of using the index: (note that the CREATE INDEX INDEX_AGE_AEX on t_student (stu_age,stu_sex) is added here;)

The results are sorted by sex and age

To delete the index, execute the same statement:

Results are sorted directly by ID

1.6. Use the ALTER command to add and remove indexesThere are four ways to add an index to a data table:

ALTER TABLE tbl_name Add PRIMARY key (column_list): The statement adds a primary key, which means that the index value must be unique and cannot be null. ALTER TABLE tbl_name ADD unique index_name (column_list): This statement creates an indexed value that must be unique (except for NULL, NULL may occur more than once). ALTER TABLE tbl_name Add index index_name (column_list): Add a normal index and the index value can occur more than once. ALTER TABLE tbl_name ADD fulltext index_name (column_list): The statement specifies that the index is fulltext for full-text indexing.

The following example adds an index to the table.

ALTER TABLE testalter_tbl ADD INDEX (c);

You can also use the drop clause in the ALTER command to delete an index. Try the following instance to delete the index:

ALTER TABLE testalter_tbl DROP INDEX (c);

To Add and remove primary keys by using the ALTER command
The primary key can only work on one column, and when you add a primary key index, you need to make sure that the primary key is not empty by default (not NULL). Examples are as follows:

ALTER TABLE testalter_tbl MODIFY i INT not NULL; ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

You can also delete a primary key by using the ALTER command:

ALTER TABLE testalter_tbl DROP PRIMARY KEY;

You only need to specify primary KEY when deleting the specified, but you must know the index name when you delete the index.
Display index Information
You can use the SHOW Index command to list related index information in a table. You can format the output information by adding \g.
Try the following examples:

SHOW INDEX from Table_name\g

third, the shortcomings of the index

The benefits of using indexes are described above, but excessive use of indexes will result in abuse. So the index has its drawbacks as well:

1. 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 updating a table, MySQL not only saves the data, but also saves the index file.

2. index files that create indexes that consume 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 up quickly.

Indexing is just one factor in efficiency, and if your MySQL has a large data size table, you need to spend time studying to build the best indexes, or refine the query statements.

Iv. Considerations for using Indexes

There are some tips and considerations when working with indexes:

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 null values. 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

The MySQL 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

It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.

5. Do not perform calculations on columns

SELECT * from the users where year (adddate) <2007;

The operation will be performed on each line, which will cause the index to fail with a full table scan, so we can change to:

SELECT * from users where adddate< ' 2007-01-01 ';

6. Do not use not and <> operations

Copyright NOTICE: This article for Bo Master Lin Bingwen Evankaka original article, without Bo Master permission not reproduced.

MySQL index principle and usage Daquan