MySQL index simple introduction and optimization

How the Index Works

An index is a pointer to a sort result that sets the content of the data. Quickly navigate to indexed data, like a catalog of books. Its function is a kind of application on the table for users to quickly read

Type of index

The index defaults to the following types of

index_type:btree #默认索引是BTREE MySQL currently has the following types of indexes: Fulltext,hash,btree,rtree

Build an index

Indexing can generally be established as an index in a column, or a composite index (multiple columns indexed together, prioritized) index establishment to ensure that no useless index is created by some SQL statements to see if the indexed index is used. The index is not as much as possible.

Design principles for Indexes

1; The index column is typically a column in a wehere clause or a column in a join clause

For example

mysql> Select *from Student where    sno=6; #以where后面的子句Son作为索引

2, as far as possible do not index small base, white is a column of the unique value of a lot, the number of repetition is very few, such as gender column only men and women, then its base is only 2 like the Age column is best not to do index, and its base is less the following is how I check a column (Sage) The result of how much

Mysql> Select COUNT (Distinct Sage) from student;

+----------------------+

| COUNT (distinct Sage) |

+----------------------+

| 6 |

+----------------------+

1 row in Set

3; Use a short index whenever possible: if you specify the minimum length for the character column index. #比如一个索引 "I am XX" "XX" is the only use of this

The creation field value of an index must be kept short and unique

4; Compound index prefix attributes, the order of the indexes is important #简单就是条件越苛刻 the less result key (a) key (A, b) key (a,b,c)

Formally indexed

1. Re-index in established tables

mysql> use Oldboy;

Database changed

mysql> desc Student;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| Sno | Int (10) | NO | PRI |       NULL | |

| Sname | varchar (16) |     NO | |       NULL | |

| Ssex | char (2) |     NO | |       NULL | |

| Sage | tinyint (2) |     NO | |       0 | |

| sdept | varchar (16) |     YES | |       NULL | |

+-------+-------------+------+-----+---------+-------+

5 rows in Set

#可以发现这个表的结构中没有索引

#创建索引

Mysql> CREATE index Ind_sname on student (Sname);

Query OK, 0 rows affected

records:0 duplicates:0 warnings:0

#Ind_sname index name student (Sname) student table under Sname column index

Then look at the table student structure you can find that the key is built with a Mul value indicating that the index has been established.

mysql> desc Student;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| Sno | Int (10) | NO | PRI |       NULL | |

| Sname | varchar (16) | NO | MUL |       NULL | |

| Ssex | char (2) |     NO | |       NULL | |

| Sage | tinyint (2) |     NO | |       0 | |

| sdept | varchar (16) |     YES | |       NULL | |

+-------+-------------+------+-----+---------+-------+

5 rows in Set

Another way to index a table after it is built

Mysql> ALTER TABLE student Add index ind_sage (Sage);

Query OK, 0 rows affected

records:0 duplicates:0 warnings:0

2. Indexing at the same time as the table is built

Mysql> CREATE TABLE Student2 (

Sno Int (Ten) not NULL COMMENT ' study number ',

Sname varchar (+) not NULL COMMENT ' name ',

Ssex char (2) not NULL COMMENT ' sex ',

Sage tinyint (2) Not NULL default ' 0 ' COMMENT ' student age ',

Sdept varchar (+) default NULL COMMENT ' student Department ',

PRIMARY KEY (Sno),

KEY ' Ind_sname ' (' Sname ')

) Engine=innodb auto_increment=1 DEFAULT CHARSET=GBK;

Query OK, 0 rows affected

#查看表结构

mysql> desc Student2;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| Sno | Int (10) | NO | PRI |       NULL | |

| Sname | varchar (16) | NO | MUL |       NULL | |

| Ssex | char (2) |     NO | |       NULL | |

| Sage | tinyint (2) |     NO | |       0 | |

| sdept | varchar (16) |     YES | |       NULL | |

+-------+-------------+------+-----+---------+-------+

5 rows in Set

3. And rebuild the index, I don't know.

View the index, because the index is above the table, and the index looks at the index of a table as follows

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/71/wKioL1V9JumyE4upAAIAFFZBiuE552.jpg "title=" 1.png " alt= "Wkiol1v9jumye4upaaiaffzbiue552.jpg"/>

Delete Index

Mysql> DROP Index ind_sname on student;

Query OK, 0 rows affected

records:0 duplicates:0 warnings:0

#Ind_sname Index name term sentence mysql> Show index from student; You can see that the Ind_sname index is no longer in the column sname the key value is not on the Mul

mysql> desc Student;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| Sno | Int (10) | NO | PRI |       NULL | |

| Sname | varchar (16) |     NO | |       NULL | |

| Ssex | char (2) |     NO | |       NULL | |

| Sage | tinyint (2) | NO | MUL |       0 | |

| sdept | varchar (16) |     YES | |       NULL | |

+-------+-------------+------+-----+---------+-------+

5 rows in Set

To create a composite index

Mysql> CREATE index sage_sdept on student (Sage,sdept (10));

Query OK, 0 rows affected

records:0 duplicates:0 warnings:0

# sage_sdept Compound index name Student (sage,sdept (10)) indexes the first 10 characters of a full index sdept column in a Sage column in a student table

View the Build Table statement Composite index has come out.

CREATE TABLE ' Student ' (

' Sno ' int (ten) not NULL COMMENT ' study number ',

' Sname ' varchar (+) not NULL COMMENT ' name ',

' Ssex ' char (2) not NULL COMMENT ' sex ',

' Sage ' tinyint (2) not NULL DEFAULT ' 0 ' COMMENT ' student age ',

' sdept ' varchar (+) DEFAULT NULL COMMENT ' student Department ',

PRIMARY KEY (' Sno '),

KEY ' sage_sdept ' (' Sage ', ' sdept ')

) Engine=innodb DEFAULT CHARSET=GBK |

Checks whether the query statement goes through the index phrase explain select * from student where sno=6; view clause where sno=6

Mysql> Explain select * from student where sno=6;

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

| 1 | Simple | Student | Const | PRIMARY | PRIMARY | 4 |    Const | 1 | NULL |

+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

1 row in Set

#Simple means high efficiency,key is not empty to walk index

Mysql> Explain select * from student where sdept= ' noon professional ';

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

| 1 | Simple | Student | All | NULL | NULL | NULL |    NULL | 7 | Using where |

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

1 row in Set

#表示没走索引

This article is from "think one or two" blog, please be sure to keep this source http://250919938.blog.51cto.com/962010/1661763

MySQL index simple introduction and optimization