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 (    nid int not NULL Auto_increment primary key,    name varchar (+) NOT NULL,    email varchar (+) n OT 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

Show 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 (32));

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 (    nid int not NULL Auto_increment primary key,    name varchar (+) NOT NULL,    email varchar (+) n OT 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 (    nid int not null auto_increment,    name varchar (+) NOT NULL,    email varchar (+) not 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  column name 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 (    nid int not NULL Auto_increment primary key,    name varchar (+) NOT NULL,    email varchar (+) No T 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 TB1 where name like '%CN '; 2, use function select * from TB1 where reverse (name) = ' Zhang Yan Forest '; 3, or SE    Lect * from tb1 where nid = 1 or email= ' [email protected] ';            Special: When the OR condition has an unindexed columns invalidation, the following will go through the index select * from tb1 where nid = 1 or name = ' Zhangyanlin '; SELECT * from tb1 where nid = 1 or email = ' [email protected] ' and name = ' Aylin ' 4, type inconsistent if the column is a string type, the incoming condition must be quoted, not Then ... select * from tb1 where name = 999;5,! = select * from TB1 where name! = ' Aylin ' Special: If it is a primary key, the index SE will still be gone Lect * from tb1 where nid! = 1236, > select * from tb1 where name > ' Alex ' Special: If the primary key or index is an integer type, then the index SE is still gone  Lect * from tb1 where nid > 123 select * from TB1 where num > 1237, ORDER by select e-mail from Tb1 order by    Name Desc; When sorting by index, the selected mappings are not indexed if they are not indexes: if the primary key is sorted, then the index: SELECT * from Tb1 ORDER by nid desc; 8. Combination index leftmost prefix if the combined index is: (name,email) name and email--Use index name--use index EMail--Do not use the 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

Mysql> Explain select * from tb2;+----+-------------+-------+------+---------------+------+---------+------+---- --+-------+| ID | Select_type | Table | Type | Possible_keys | Key  | key_len | ref  | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+|  1 | Simple      | TB2   | All  | NULL          | NULL | NULL    | NULL |    2 | NULL  |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in Set ( 0.00 sec)

Detailed Introduction

4. Limit Paging

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

Per page 10 article: Current 118 120, 125 reverse: Size 980 970 7 6 6 5 54 43 3221 19 98 Next: Select  * FROM TB1 where Nid < (select Nid from Tb1 where Nid < current page minimum ORDER by    Nid desc limit per page data * "page-current page") A ORDER BY A.nid ASC limit 1) Order by nid desc limit 10;  SELECT * from TB1 where Nid < (select Nid from Tb1 where nid < 970         ORDER BY nid desc limit of A ORDER by a.nid ASC limit 1) Order by nid desc limit 10; previous: Select  * FROM TB1 where Nid < (select Nid from Tb1 where nid > Current page Maximum order by    Nid ASC limit per page data * "Current page-page number") A ORDER BY A.nid ASC limit 1) Order by nid desc limit 10; SELECT * from TB1 where Nid < (select Nid from Tb1 where nid > 980 ORDER by nid ASC limit) A ORDER BY A.nid DescLimit 1) Order by nid desc limit 10; 

MYSQL (iii)