MYSQL (iii)

Introduction to Indexes

An index is a structure that sorts the values of one or more columns in a database table, such as the name (name) column of the employee table. If you want to find him or her by the name of a particular employee, the index helps you get information faster than searching all the rows in the table.

For example such a query: SELECT * FROM table1 where id=10000. If there is no index, you must traverse the entire table until the row with the ID equals 10000 is found, and after the index (which must be an index established on the ID column), you can find it in the index. Because the index is optimized by some algorithm, the number of lookups is much less. It can be seen that index socialize is much faster than no index.

Common indexes in MySQL are:

• Normal index
• Unique index
• Primary key Index
• Combined index

Let's apply the index below.

Index operations

I. GENERAL index

Common so there is only one function, is to speed up the search speed. Operation is as follows

1. Create a table first

CREATE TABLE Tab1 (    null auto_increment primary KEY,    name varchar (null, email varchar (  Null, extra text, index Ix_name (name))     

2. Create an index

Create index name on table name (column name)

3. Delete Index

Drop index name on table name;

4. View Index

from table name;

5. Precautions (If you are creating an index with a blob and TEXT type, you must specify length.) )

Create INDEX index_name on TAB1 (extra (+));

Second, unique index (unique)

Unique Indexes unique index and general index the biggest difference between the normal index and the index column is the addition of a single layer of constraint. A data column that adds a unique index can be empty, but must be unique as long as the data value exists.

1. CREATE TABLE + Unique index

CREATE TABLE TaB2 (    null auto_increment primary KEY,    name varchar (null, email varchar (  Null, extra text, unique Ix_name (name)-- focus here)      

2. Create an index

Create unique index index name on table name (column name)

3. Delete Index

Drop unique index index name on table name

Third, primary key index

Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows for fast access to the data. The data cannot be empty

1. CREATE TABLE + PRIMARY key index

CREATE TABLE in1 (    null auto_increment,    name varchar (NULL,email varchar (null, Extra text, primary key (NID), index Zhang (name))     

2. Create a PRIMARY key

ALTER TABLE name add primary key (column name);

3. Delete primary key

ALTER TABLE name drop primary key;alter table name  modify  int, drop primary key;

Iv. Combined Index

Combination of the index, that is, the meaning of the combination of query hehe, two or more columns combined into an index to query

Its application scenario is: Frequent use of n-columns to query, such as: where name = ' Zhang Yan forest ' and email = 666.

1. Create a table

CREATE TABLE in3 (    null auto_increment primary KEY,    name varchar (null, email varchar (  Null, extra text)     

2. Create a composite index

Create INDEX Ix_name_email on in3 (Name,email);

Once the composite index has been created, the query will use the index, and some will not:

• Name and email--Using the index
• Name--Using the index
• Email-Don't use index

Index considerations

1. Correct use of index

When you add an index to a database table, you can make the query database fast, but the premise must be to use the index to query correctly, and if you use it in an incorrect way, it will not work even if you build the index.

The following conditions are not used in the index:

1. Like‘%xx‘SELECT *From TB1where name like‘%cn‘;2, using functionsSELECT *From TB1where reverse (name) =‘Zhang Yan Forest‘;3, orSELECT *From TB1where Nid =1 or email=‘[Email protected]‘; Special: When the OR condition has an unindexed columns failure, the following indexSELECT *From TB1where Nid =1 or name =‘Zhangyanlin‘;SELECT *From TB1where Nid =1 or email =‘[Email protected]' AND name =‘Aylin‘4, type inconsistency if the column is a string type, the incoming condition must be enclosed in quotation marks, otherwise ...SELECT *From TB1WHERE name =999;5.! =SELECT *From TB1WHERE Name! =‘Aylin‘Special: If it is a primary key, the index will still goSELECT *From TB1where Nid! =1236. >SELECT *From TB1where name >‘Alex‘Special: If the primary key or index is an integer type, then the index is still goneSELECT *from tb1 where nid > 123 Span style= "COLOR: #0000ff" >select * from tb1 where num > 1237, order by select email from tb1 order BY name Desc; When sorting by index, the selected mapping is not indexed if it is not an index: if you sort the primary key, you still go index: select * from tb1 order BY nid desc; 8, combined index leftmost prefix if combined index is: (name,email) name and email-- use index name-- use index email--Do not use index     

2. Other matters needing attention

• 避免使用select*
• count(1)或count(列) 代替 count(*)
• 创建表时尽量时 char代替 varchar
• 表的字段顺序固定长度的字段优先
• 组合索引代替多个单列索引（经常使用多个条件查询时）
• 尽量使用短索引
• 使用连接（JOIN）来代替子查询(Sub-Queries)
• 连表时注意条件类型需一致
• 索引散列值（重复少）不适合建索引，例：性别不适合

3. Implementation plan

Explain + query SQL-used to display SQL execution information parameters that can be optimized for SQL based on reference information

From tb2;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| ID | Select_type | Table | Type | Possible_keys | Key  ref  | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+|  1 | Simple      | TB2   | All  | NULL          | NULL | NULL    | NULL |    2 | NULL |+----+-------------+-------+------+---------------+------+---------+------+------+-------+set (  0.00 sec)     

Detailed Introduction

4. Limit Paging

Paging is a matter of concern, as we will always use

Show 10 per page: current118120,125Reverse: Size980970766554433221st1998Next page:SELECT *FromTb1whereNid < (Select NidFrom (Select NidFrom TB1Where Nid < current page minimum value order by nid desc limit per page data * "page-current page") A ORDER by A.nid ASC limit1) Order BY nid desc limit10;SELECT *FromTb1whereNid < (Select NidFrom (Select NidFrom TB1Where Nid <970 ORDER BY nid desc limit() A ORDER by A.nid ASC limit1) Order BY nid desc limit10; Previous page:SELECT *FromTb1whereNid < (Select NidFrom (Select NidFrom TB1Where nid > Current page Max ORDER by nid ASC limit per page data * "Current page-page number") A ORDER by A.nid ASC limit1) Order by nid desc limit ten; SELECT * from tb1 where nid < (select Nid from  tb1 where n ID > 980 ORDER by nid ASC limit ) A ORDER BY A.nid desc limit 1) Order by nid desc limit 10; 

So this is the end of the MySQL explanation, like to give a little praise yo

MYSQL (c)