MySql index improves query speed common methods code examples

Use indexes to speed up queries

1. Introduction

In web development, there are three parts: business templates, business logic (including cache, connection pool) and database. The database is responsible for executing SQL queries and returning query results, which is the most important performance bottleneck affecting website speed. This article mainly focuses on the MySQL database. In Taobao's IOE (I stands for the abbreviation of IBM, that is, removing IBM's storage devices and minicomputers; O stands for the abbreviation of Oracle, removing Oracle database, using MySQL and Hadoop instead; E stands for EMC2, removing EMC2's equipment, using PC server instead of EMC2), MySQL clusters are used extensively! An important step in optimizing data is to create indexes. For slow queries in MySQL, you can use indexes to increase query speed. Indexes are used to quickly find rows that have a specific value in a column. Without an index, MySQL will scan the entire table, starting with the first record, and then read the entire table until it finds the relevant rows.

2.Mysql index type and creation

Index related knowledge:

PRI primary key constraint;

UNI unique constraint;

MUL can be repeated.


View the indexmysql> show index from tblname;
mysql> show keys from tblname;
Table
The name of the table.
Non_unique
0 if the index cannot include repeated words. 1 if it can.
Key_name
The name of the index.
Seq_in_index
The column sequence number in the index, starting from 1.
Column_name
The column name.
· Collation
How columns are stored in the index. In MySQL, this has the values ​​'A' (ascending) or NULL (no sorting).
Cardinality
An estimate of the number of unique values ​​in the index. It can be updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so even for small tables the value is not necessarily exact. The greater the cardinality, the greater the chance that MySQL will use the index when doing a join.
Sub_part
If the column is only partially indexed, the number of characters indexed. NULL if the entire column is indexed.
· Packed
Indicates how keywords are compressed. If not compressed, this is NULL.
· Null
Contains YES if the column contains NULL. If not, the column contains NO.
Index_type
The index method used (BTREE, FULLTEXT, HASH, RTREE).
· Comment

1). Primary key index

It is a special unique index that does not allow nulls. Generally, the primary key index is created at the same time when the table is created:

CREATE TABLE user(
id int unsigned not null auto_increment,
name varchar(50) not null,
email varchar(40) not null,
primary key (id)
);

2). Ordinary index

This is the most basic index, without any restrictions:

create index idx_email on user(
email(20)
);create index idx_name on user(name(20));

MySQL supports index prefixes. Generally, names do not exceed 20 characters, so the length of the index is limited to 20 to save index file size.

3). Unique index

It is similar to the previous ordinary index, except that the values ​​of the index column must be unique, but null values ​​are allowed. If it is a composite index, the combination of column values ​​must be unique.

CREATE UNIQUE INDEX idx_email ON user(
email
);

4). Combined index

create table sb_man(
id int PRIMARY key auto_increment,
new_name char(30) not null,
old_name char(30) not null,
index name(new_name,old_name)
);# The name index is an index on new_name and old_name. Query method: select * from sb_man where new_name='yu'; select * from sb_man where new_name='yu' and old_name='yu1'; Tip: >>>>>> The composite index is created by the leftmost prefix, so you cannot use the following sql select * from sb_man where old_name='yu1'; <<<< Error 

3. When to use indexes

1. Index reference

In addition to the ordered search mentioned above, the database uses a variety of fast positioning technologies on index columns to greatly improve query efficiency. Especially when the amount of data is very large and the query involves multiple tables, using indexes can often speed up the query by thousands of times.

For example, there are two unindexed tables t1 and t2, each containing only columns c1 and c2. Each table contains 1000 rows of data, with a value of 111. Then set up three tables with different values.

(Here I use pymysql to execute while creating the data)

The query to find rows with equal corresponding values ​​is as follows.

To process this query without an index, all combinations of the three tables must be searched to obtain those rows that match the WHERE clause.

select c1,c2 from t1,t2 where c1!=c2

result Query process >>

2. Create an index

You can create an index when executing the CREATE TABLE statement, or you can use CREATE INDEX or ALTER TABLE separately to add an index to a table.

1. ALTER TABLE

ALTER TABLE is used to create a normal index, a UNIQUE index, or a PRIMARY KEY index.

Delete index: alter table tab_name drop {index|key} index_name;
alter table t1 drop index idx_c1;
Add an index: alter table t1 add index idx_c1(c1);
alter table t2 add index idx_c2(c2);

Query results Although it feels useless. . . But that's the thing about index queries. . .

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.