MySQL (III), mysql

MySQL (III), mysql
MYSQL (III)

The previous chapter describes database views, stored procedures, and other operations. This chapter focuses on indexes and index considerations. If you want to read the previous article, the url is as follows:

• Full MYSQL entry (Part 1)
• Full MYSQL entry (Part 2)

Index Overview

An index is a structure that sorts the values of one or more columns in a database table (for example, the name column of the employee table. If you want to search for him or her by the name of a specific employee, the index will help you get information faster than searching all rows in the table.

For example, select * from table1 where id = 10000. If no index exists, you must traverse the entire table until the row with ID equal to 10000 is found. If an index exists, it must be an index created on the column with ID ), you can search in the index. Because the index is optimized by a certain algorithm, the number of queries is smaller. It can be seen that the index insertion speed is much faster than that without an index.

Common indexes in MySQL include:

• Common Index
• Unique Index
• Primary Key Index
• Composite Index

Apply the index below

Index operations

I. general index)

In general, there is only one function, which is to speed up the search. The operations are as follows:

1. Create a table first

create table tab1(    nid int not null auto_increment primary key,    name varchar(32) not null,    email varchar(64) not null,    extra text,    index ix_name (name))

2. Create an index

Create index name on table name (column name)

3. delete an index

Drop index name on table name;

4. View Indexes

Show index from table name;

5. Note (length must be specified if the index is of the BLOB or TEXT type .)

create index index_name on tab1(extra(32));

Ii. unique Index)

The biggest difference between a unique index and a normal index is that a unique constraint is added to the index column. The data column for adding a unique index can be empty, but it must be unique if there is a data value.

1. Create a table + unique index

Create table tab2 (nid int not null auto_increment primary key, name varchar (32) not null, email varchar (64) not null, extra text, unique ix_name (name) -- The focus is here)

2. Create an index

Create unique index name on table name (column name)

3. delete an index

Drop unique index name on table name

Iii. Primary Key Index

Defining a primary key for a table in the database relationship diagram will automatically create 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 quick access to data. Data cannot be blank

1. Create a table + primary key index

create table in1(    nid int not null auto_increment,    name varchar(32) not null,    email varchar(64) 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 the primary key

Alter table Name drop primary key; alter table name modify column name int, drop primary key;

Iv. Composite Index

Composite index refers to composite query, which combines two or more columns into one index for query.

The application scenario is: frequently use n columns for queries at the same time, for example, where name = 'zhang yanlin' and email = 666.

1. Create a table

create table in3(    nid int not null auto_increment primary key,    name varchar(32) not null,    email varchar(64) not null,    extra text)

2. Create a composite index

create index ix_name_email on in3(name,email);

After a composite index is created, some queries use indexes, and some do not:

• Name and email -- use index
• Name -- use index
• Email -- no index is used

Index considerations

1. correct indexing

After adding an index to a database table, you can quickly query the database. However, you must use the index correctly. If you use the index incorrectly, it will not work even if you create an index.

In the following cases, indexes are not used:

1. like '% xx' select * from tb1 where name like' % cn'; 2. Use the function select * from tb1 where reverse (name) = 'zhang yanlin '; 3. or select * from tb1 where nid = 1 or email = 'zhangyanlin @ live.com '; specifically, columns with no index in the or condition are invalid, select * from tb1 where nid = 1 or name = 'hangyanlin '; select * from tb1 where nid = 1 or email = 'hangyanlin @ live.com 'and name = 'aylin' 4. Inconsistent types if the column is of the string type, the input condition must be enclosed in quotation marks, otherwise... select * from tb1 Where name = 999; 5 ,! = Select * from tb1 where name! = 'Aylin' Special: if it is a primary key, it will still take the index select * from tb1 where nid! = 1236,> select * from tb1 where name> 'Alex 'Special: if the primary key or index is of the integer type, select * from tb1 where nid> 123 select * from tb1 where num> 1237, order by select email from tb1 order by name desc; when sorting by index, if the selected ing is not an index, it does not take the index Special: if the primary key is sorted, it will still take the index: select * from tb1 order by nid desc; 8. The leftmost prefix of the composite index. If the composite index is: (name, email) name and email -- use index name -- use index email -- do not use index

2. Other considerations

• Avoid usingselect *
• count(1) orcount(Column) Substitutioncount(*)
• When creating a tablechar Replacevarchar
• Priority of fields with Fixed sequence length in a table
• Composite indexes replace Multiple Single-Column indexes (when multiple condition queries are frequently used)
• Use short indexes whenever possible
• Use connection (JOIN) Instead of subqueries (Sub-Queries)
• Note that the condition types must be consistent during table connection.
• Index hash values (with few duplicates) are not suitable for indexing. For example, gender is not suitable.

3. Execution Plan

Explain + query SQL-used to display SQL Execution information parameters. You can optimize SQL statements based on the 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)

Details

4. limit Paging

The paging function is a noteworthy issue because we will always use it

10 entries per page: Current 118 120,125 descending order: large small 980 970 7 6 6 5 54 43 3221 19 98 next page: select * from tb1 where nid <(select nid from tb1 where nid <minimum value of the current page order by nid desc limit per page * [page number-current page ]) A order by. 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 40) A order by. nid asc limit 1) order by nid desc limit 10; Previous Page: select * from tb1 where nid <(select nid from tb1 where nid> maximum value of the current page order by nid asc limit per page * [current page-page number ]) A order by. 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 20) A order by. nid desc limit 1) order by nid desc limit 10;

This is the end of mysql's explanation. I like to give a thumbs up.

-This article Reprinted from: http://www.cnblogs.com/aylin/p/5777289.html