MySQL Index and index optimization

Speaking of improving database performance, the index is the most inexpensive thing. Do not add memory, do not change the program, do not have to tune SQL, as long as the correct ' create index ', the query speed may increase hundreds of thousands of times, this can be really tempting. But there is no free lunch, the increase in query speed is at the cost of inserting, updating, deleting, these write operations, increase the number of I/O. Types of MySQL Indexes

MySQL index contains: Ordinary index, primary key index, unique index, full-text index, in order to adapt to different queries, data structure, improve the query speed, choose the appropriate index method.

1. General Index

This is the most basic index and it has no limitations.

How to create a normal index:

In general, the creation of a normal index is to create a table first, and then create a normal index, the code is as follows

Create Table  intvarchar (+)) create index on CCC (Id,name);

To modify the table structure to create an index:

ALTER mytable ADD INDEX [IndexName] on (username (length))

The creation table is the specified index:

CREATE TABLE mytable (   INTnotNULL,    VARCHAR (  not NULL,    INDEX[indexname]  ( Username (length))   

To delete an index:

DROP INDEX [IndexName] on CCC;

Note: Length in the above instance code, if the Char,varchar type, can be less than the actual length of the field, and length must be specified if it is a blob and text type.

2. Primary KEY index

The primary key does not have a clear definition of the concept, which is one of the indexes and is a unique index, and must be defined as " PRIMARY KEY ", the primary key cannot be duplicated, and a table can have only one primary key.

Primary key fields, cannot be null, and cannot be duplicated; The primary key index is created at the same time as the table is constructed;

When a table is set to a column as the primary key, the column is the key index.

Specify primary key index when creating table, instance

Create Table  intprimarykeyvarchar (+ NOT NULL Defaul ");

Create an index after the build table is complete:

Alter Table Add Primary Key (column name);

3. Full-Text Indexing

Full-text indexing, mainly for the retrieval of files, text, such as articles, full-text indexing is useful for MyISAM.

To create a full-text index, instance:

CREATE TABLE articles (       INTnotNULLPRIMARYKEY,         VARCHAR(+),       TEXT,       Fulltext (title,body)     ) engine  =MyISAM charset UTF8;

How full-text indexes are used, example: match () against ()

Select *  from where match (title,body) against ('database

Note:

1. In MySQL, the fulltext index is only valid for the MyISAM storage engine;
2. MySQL self-provided fulltext for the English effective, the Chinese invalid->sphinx (coreseek) technology processing Chinese;
3. The use method is match (field name:) against (' keywords ');
4. A full-text index is called a stop word, because in a text, creating an index is an infinite number, so, for some common words and characters, it is not created, these words, called stop words.

The following full text stop word section shows, others can consult MySQL manual

4. Unique index

When a column of a table is specified as a unique constraint, this column is a unique index;

The unique field can be null and can have more than one null, but it cannot be duplicated if it is specific.

To declare a unique index when creating a table, instance:

Create Table  intprimarykeyvarchar(unique);

After creating the table, go to create a unique index, instance:

Create Table  intprimarykeyvarchar(+)); Create Unique index eee_index on   eee (name);

SQL operations for MySQL indexes

1. Querying the Index

index[from TableName; 

2. Deleting an index

Alter Table TableName Drop Index

3. Modifying the Index

Delete and re-create it first.

MySQL index's advantages and disadvantages: I. Disk occupancy;

For example, MyISAM stores 3 files on disk with the same filenames and table names, but with the following extensions:

• . frm (save table definition)
• MYD (MYData, storing data)
• MYI (myindex, storage index) consumes a certain amount of disk space, and the index file increases as the data is increased

II. Impact on the efficiency of DML (UPDATE delete insert) statements, changes in data, and the need to maintain index benefits at the same time:

The query is faster, but why is it getting faster?

Simple understanding: There is no index of the situation, to make a full table query, plus index after the use of Btree method for data retrieval, the amount of data to be retrieved greatly reduced, index retrieval speed is much faster.

You can refer to the implementation of MySQL index, to understand, refer to the article

Http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

Application of MySQL Index

Conditions suitable for indexing:

• Must be used frequently in the where bar
• The contents of this field are not unique in several values (sex)
• Field content does not change frequently.

Considerations for Using Indexes:

#实例说明: Create a column in the Dept table that conforms to the left of index # dname, and Loc is the right column Alter Table Add Index

Note that if we have a composite index in our table (the index is on more than one column), we notice at this point:

• For a multi-column index that is created, the index is generally used only if the query condition uses the leftmost column.

Select *  from where loc='aaa'\g  #就不会使用到索引

#explain can help us perform MySQL execution when we don't actually execute an SQL statement, so we use our parsing of SQL instructions.

Explain select * FROM dept where dname= ' aaa ' \g  #就会使用到索引

• If there is or in the condition, it will not be used even if there is a conditional index. In other words, all the fields required for use must be indexed, and we recommend that you try to avoid using the OR keyword;

• Select *  from where dname=or loc=or deptno=# The criteria fields that appear above must be indexed

• For queries that use like, the query is '%AAA ' will not be used to the index, using ' aaa% ';

• Select *  from where  like ' %aaa ' \g   % or _ such a character. If you must have a change in the previous value, consider using full- text index-Sphinx.

• If the column type is a string, be sure to use quotation marks to reference the data in the condition. Otherwise, the index is not used. (when added, the string must be "), that is, if the column is a string type, make sure to include it with ' ';
• If MySQL estimates that using a full table scan is faster than using an index, the index is not used.

Explain tools Introduction

Explain can help us perform MySQL execution when we don't actually execute a SQL statement, so we use our parsing SQL instructions.

Possible_key: The query can take advantage of the index, if no index can be used null Key:mysql from Possible_key to use the index Key_len: The index is selected to use the index long Degree rows: shows the number of rows that MySQL must check when it executes a query

Extra: Query detail information

No tables

Use from DUAL or without any FROM clause in a query statement

Using Filesort

When query contains an order by operation, and the index does not complete the sort,

Impossible WHERE noticed after reading const TABLES:MYSQL Query Optimizer by collecting statistics it is not possible to have results using temporary some operations must use temporary tables, common GR OUP by; ORDER byusing where you don't have to read all the information in the table, you can get the data you need only by indexing; How to view the usage of indexes

Show status like ' handler_read% ';

We can note:
Handler_read_key: The higher the value the better, the higher the number of times that the index is queried.

Handler_read_rnd_next: The higher the value, the less efficient the query.

MySQL Index and index optimization