Principles and usage of MySql Indexes

Principles and usage of MySql Indexes

Original works of Lin bingwen Evankaka. Reprinted please indicate the source http://blog.csdn.net/evankaka

I. Index Introduction

An index is a structure that sorts the values of one or more columns in a database table. In relational databases, an index is a table-related database structure that enables faster execution of SQL statements corresponding to tables. The index function is equivalent to the book directory. You can quickly find the desired content based on the page number in the directory. When a table contains a large number of records, to query the table, the first method of searching information is full table search. All records are retrieved one by one and compared with the query conditions, then, return records that meet the conditions. This will consume a lot of database system time and cause a lot of disk I/O operations. The second is to create an index in the table, then, find the index value that meets the query conditions in the index, and quickly find the corresponding records in the table by the ROWID (equivalent to the page number) saved in the index.
An index is a separate, physical database structure. It is a set of one or more column values in a table and a logical pointer list pointing to the data page that physically identifies these values in the table. The Index provides pointers to the data values stored in the specified column of the table, and then sorts these pointers according to the sort order you specify. The database uses an index in a similar way as you use an index in a book: it searches for an index to find a specific value, and then returns the pointer to the row containing the value. In the database graph, you can create, edit, or delete each index type on the index/Key Attribute page of the selected table. When you save the table to which the index is attached or the relational graph of the table is saved, the index is saved in the database.

Mysql index Overview

All MySQL column types can be indexed. Using indexes for related columns is the best way to improve the performance of SELECT operations. Define the maximum number of indexes and the maximum index length for each table based on the storage engine. All storage engines Support at least 16 indexes for each table, with a total index length of at least 256 bytes. Most storage engines have higher limits.

In MySQL 5.1, for MyISAM and InnoDB tables, the prefix length can reach 1000 bytes. Note that the prefix limit should be measured in bytes, while the prefix length in the create table statement is interpreted as the number of characters. You must consider specifying the prefix length for a column that uses a multi-byte character set.

You can also create FULLTEXT indexes. This index can be used for full-text search. Only the MyISAM storage engine supports FULLTEXT indexes and only CHAR, VARCHAR, and TEXT columns. The index always applies to the entire column and does not support partial (prefix) indexes. You can also create an index for the spatial column type. Only the MyISAM storage engine supports the space type. Spatial indexes use the R-tree. By default, the MEMORY (HEAP) storage engine uses hash indexes, but also supports B-tree indexes.

B-tree index and hash Index

For B-tree and HASH indexes, when the =, <=>, IN, IS NULL, or IS NOT NULL operator IS used, the comparison between key elements and constant values corresponds to a range condition. Hash indexes also have some other features: they are used only for Equality comparison using the = or <=> operator (but very quickly ). The optimizer cannot use hash indexes to accelerate the order by operation. (This type of index cannot be used to search for the next entry in order ). MySQL cannot determine the number of 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 hash-index MEMORY table, some queries will be affected. You can only use the entire keyword to search for a row. (Use B-tree indexes. You can use the leftmost prefix of any keyword to find the row ).

For B-tree indexes, when >,<=, <=, BETWEEN, and ,! = Or <>, or LIKE 'pattern' (where 'pattern' does not start with a wildcard) operator, the comparison between the key element and the constant value corresponds to a range condition. "Constant Value" refers to the expression that queries the constants in a string, the const in the same join, the columns in the system table, the results of non-correlated subqueries, and completely composed of the preceding types of subexpressions.

The following are examples of queries with range conditions in the WHERE clause.

The following range of queries apply to the btree index and hash index:

SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (, 20); the following range of queries apply to the btree Index
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 to use indexes in Mysql

The index is used to quickly find rows with a specific value in a column. If no index is used, MySQL must start with 1st records and read the entire table until related rows are found. The larger the table, the more time it takes. If the column to be queried in the table has an index, MySQL can quickly find the data file in the middle of a location, and there is no need to read all the data. If a table has 1000 rows, this is at least 100 times faster than sequential reading. Note that if you want to access most rows, sequential reading is much faster, because disk search is avoided.

Most MySQL indexes (primary key, UNIQUE, INDEX, and FULLTEXT) are stored in Tree B. Only the R-tree is used for spatial column indexes, and the MEMORY table also supports hash indexes.

Ii. Usage

First, create a table:

CREATE TABLE    t_student    (        STU_ID INT NOT NULL,        STU_NAME CHAR(10) NOT NULL,        STU_CLASS INT NOT NULL,        STU_SEX CHAR(2) NOT NULL,        STU_AGE INT NOT NULL,        PRIMARY KEY (STU_ID)    )    ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.1 create an index for a Common Index
This is the most basic index with no restrictions. It can be created in the following ways:

CREATE INDEX index_id  ON t_student(STU_ID); 

For CHAR and VARCHAR types, the length can be smaller than the actual length of the field. For BLOB and TEXT types, the length must be specified.
Modify Table Structure

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

Specify

CREATE TABLE    t_student1    (        STU_ID INT NOT NULL,        STU_NAME CHAR(10) NOT NULL,        STU_CLASS INT NOT NULL,        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 deleting an index

DROP INDEX index_id  ON t_student;DROP INDEX index_name  ON t_student;

Result

1.2 The primary key of a unique index is a unique index. The primary key must be specified during table creation. Generally, the auto_increment column is used, and the key word is the primary key. It is similar to the previous normal index. The difference is that the value of the index column must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways:
Create an index

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

Modify Table Structure

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) 

Specify

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,UNIQUE [indexName] (username(length))); 

1. 3. Multi-column Indexes

Create an index

CREATE  INDEX indexName ON mytable(username1(length),username2(length)) 

Create index index_age_aex ON t_student (STU_AGE, STU_SEX );

Modify Table Structure

ALTER mytable ADD  [indexName] ON (username1(length),username2(length)) 

Specify

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,INDEX [indexName] (username1(length),username2(length))); 

One advantage of Multi-column indexing is that it is embodied by the concept of Leftmost Prefixing. Continue to consider the previous example. Now we have a multiple-column index on the firstname, lastname, and age columns. We call this 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 created on the combination of columns (firstname, lastname, age), (firstname, lastname), and (firstname. All of the following queries can 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 cannot use the 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 index

Full-text index (also called full-text search) is a key technology currently used by search engines. It uses multiple algorithms such as word segmentation to intelligently analyze the frequency and importance of keyword words in text, and then intelligently filters out the desired search results based on certain algorithm rules. Here, we will not go into the underlying implementation principle. Now let's look at how to create and use full-text indexes in MySQL.
In MySQL, creating full-text indexes is relatively simple. For example, we have an article table (article) with three fields: primary key ID (id), article title (title), and article content (content. Now we want to create full-text indexes on the title and content columns. The SQL statement for creating the article table and full-text indexes is as follows:
-- Create an article table

CREATE TABLE article ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,  title VARCHAR(200), content TEXT, FULLTEXT (title, content) )ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

Look at the index

The preceding SQL example creates a full-text index when creating a table. In addition, if you want to create a full-text index for a specified field in an existing table, take the article table as an example. You can use the following SQL statement to create a full-text index:
-- Create a full-text index for the title and content fields of the existing article table
-- The index name is fulltext_article.

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

After creating a full-text index in MySQL, you should know how to use it now. We must use the special syntax to use full-text indexes for queries. For example, to retrieve the specified query string in the title and content columns of the article table, you can write an SQL statement as follows:

SELECT * FROM article where match (title, content) AGAINST ('query string ');

Notes

The search must be performed on fulltext-type index columns, and the columns specified in match must be specified in fulltext.
It can only be applied to tables whose table engine is MyIsam (MySQL 5.6 and later can also be used in Innodb table engine)
Only full-text indexes can be created on char, varchar, and text columns.
Like a normal index, you can specify a table when defining a table, or add or modify a table after it is created.
For insert of a large number of records, it is much faster to insert data into a table without indexes to create an index than to insert data into a data table with indexes.
The search string must be a constant string and cannot be the column name of the table.
When the selectivity of search records exceeds 50%, no matching is considered (only restricted in natural search)

1.5. verify whether the index is used

Some operations are followed by 1. 3. You can connect to multiple index columns. Here, I have added some data, as shown below:

You can use the explain select * FROM t_student WHERE STU_AGE = 12; statement to verify whether an index is used.

The following describes the indexes used.

If the index is not used, the result is as follows:

The above is only to verify whether an index is used. Next, let's look at the results of using or not using an index:

The INDEX result is used: (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.

If the index is deleted, run the same statement:

The results are sorted by ID directly.

1.6 you can use the ALTER command to add or delete an index in four ways:

Alter table tbl_name add primary key (column_list): This 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): the index value created by this statement must be UNIQUE (except NULL, NULL may occur multiple times ). Alter table tbl_name add index index_name (column_list): adds a common INDEX. The INDEX value can appear multiple times. Alter table tbl_name add fulltext index_name (column_list): This statement specifies that the index is FULLTEXT for full-text indexing.

The following example adds an index to a table.

 ALTER TABLE testalter_tbl ADD INDEX (c);

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

ALTER TABLE testalter_tbl DROP INDEX (c);

Use the ALTER command to add or delete a primary key
A primary key can only act on one column. When adding a primary key index, make sure that the primary key is not null by default ). Example:

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

You can also use the ALTER command to delete the primary key:

 ALTER TABLE testalter_tbl DROP PRIMARY KEY;

When deleting an index, you only need to specify the primary key. However, you must know the index name When deleting the index.
Show index information
You can use the show index Command to list the INDEX information in the table. You can add \ G to format the output information.
Try the following instances:

SHOW INDEX FROM table_name\G

Iii. Index Deficiency

The advantages of using indexes are described above, but excessive use of indexes will cause abuse. Therefore, the index also has its disadvantages:

1. Although the index greatly improves the query speed, it also reduces the speed of updating the table, such as performing INSERT, UPDATE, and DELETE operations on the table. When updating a table, MySQL not only needs to save data, but also stores the index file.

2. index files that occupy disk space when an index is created. This problem is not serious in general, but if you create multiple composite indexes on a large table, the index file will expand very quickly.

Indexes are only a factor to improve efficiency. If your MySQL database has a large data volume of tables, you need to spend time researching and creating the best indexes or optimizing query statements.

Iv. Notes on using Indexes

Tips and notes for using indexes:

1. The index does not contain columns with NULL values.

As long as a column contains a NULL value, it will not be included in the index. If a column in the composite index contains a NULL value, this column is invalid for this composite index. Therefore, do not set the default value of a field to NULL during database design.

2. Use short Indexes

Index a string or column. If possible, specify a prefix length. For example, if a CHAR (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.

3. Index column sorting

MySQL queries only use one index. Therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. Try not to include the sorting of multiple columns. It is best to create a composite index for these columns if necessary.

4. like statement operation

Generally, like operations are not encouraged. If they are not usable, how to use them is also a problem. Like "% aaa %" does not use indexes, but like "aaa %" can use indexes.

5. Do not perform operations on columns

Select * from users where YEAR (adddate) <2007;

The operation will be performed on each row, which will cause index failure and scan the entire table, so we can change it:

Select * from users where adddate <'2014-01-01 ';

6. Do NOT use the not in and <> operations

Copyright Disclaimer: This article is the original article by the blogger Lin bingwen Evankaka, which cannot be reproduced without the permission of the blogger.