How to use indexes to improve query speed _mysql

Using indexes to improve query speed
1. Foreword
In Web development, page templates, business logic (including caching, connection pooling) and database three parts, in which the database is responsible for executing SQL queries and returning query results, is the most important performance bottleneck affecting the speed of the site. This article is mainly for MySQL database, dual 11 of the electric business War, sparked a hot Taobao technology, and Taobao now go to IoE (I on behalf of IBM's abbreviation, that is, to IBM's storage equipment and minicomputer; O is an abbreviation for Oracle, or Oracle database, Adopt MySQL and Hadoop alternative solutions,; E is representative of EMC2, that is, to EMC2 equipment, with PC server instead of EMC2, a large number of MySQL cluster use! Let MySQL again become a dazzling star! The important step of optimizing the data is the establishment of the index, for the slow query in MySQL, we can improve the query speed by using the index. Indexes are used to quickly find rows that have a specific value in a column. Without indexing, MySQL will perform a full table scan, starting with the 1th record and then reading through the entire table until the relevant rows are found.

2.mysql index Type and creation
The commonly used index types are

(1) Primary key index
It is a special unique index and does not allow null values. The primary key index is typically created at the same time as the table is being built:

Copy Code code as follows:

CREATE TABLE User (
ID int unsigned NOT NULL auto_increment,
Name varchar NOT NULL,
Email varchar NOT NULL,
Primary KEY (ID)
);

(2) General index
This is the most basic index, and it has no limitations. How to create:

Copy Code code as follows:

Create index idx_name on user (
Name (20)
);

MySQL supports the prefix index, the general name will not exceed 20 characters, so we set up the index of the time limit of 20, so you can save the index file size

(3) Unique index
It is similar to the previous normal index, except that the value of the indexed column must be unique, but a null value is allowed. If it is a combined index, the combination of the column values must be unique. How to create:

Copy Code code as follows:

CREATE UNIQUE INDEX idx_email on user (
Email
);

(4) Full-text indexing
MySQL supports Full-text indexing and search capabilities.  The full-text indexed type in MySQL is an index of fulltext. The fulltext index can only be used for MyISAM tables;

Copy Code code as follows:

CREATE TABLE Articles (
ID INT UNSIGNED auto_increment not NULL PRIMARY KEY,
Title VARCHAR (200),
Body TEXT,
Fulltext (Title,body)
);

Mysql> SELECT * from articles WHERE MATCH (title,body) against (' database ');

Query Results:
+----+-------------------+------------------------------------------+
| ID | Title | Body |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. Yoursql | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+
2 rows in Set (0.00 sec)
The MATCH () function performs a natural language search within a database for a string. A database is 1 sets of 1 or 2 columns contained within Fulltext. The search string is given as a parameter to the against (). For each row in the table, MATCH () returns a correlation value, that is, a similarity metric between the search string and the line literal in the MATCH () table.
(5) composite Index

Copy Code code as follows:

CREATE TABLE Test (
ID INT not NULL,
Last_Name CHAR () not NULL,
First_Name CHAR () not NULL,
PRIMARY KEY (ID),
INDEX name (last_name,first_name)
);

The name index is an index to last_name and first_name. Indexes can be used to query for last_name, or for last_name and first_name to specify values within a known range. Therefore, the name index is used for the following query:
SELECT * FROM Test WHERE last_name= ' Widenius ';
SELECT * FROM Test WHERE last_name= ' Widenius ' and first_name= ' Michael ';
But it cannot be used for SELECT * from Test WHERE first_name= ' Michael '; this is because the MySQL combination index is the result of "leftmost prefix", the simple understanding is to only start from the left side of the combination.

3. Under what circumstances use the index
(1) For the search word Jianjian index, if in your table, a field you often use to do the search, then, please set up the index bar. In general, columns that appear in a where and join need to be indexed to improve query speed.
For example, the person whose name is "Li Wu" is retrieved from the FPS table (the Name field in the table),
The following explain is used to explain the difference between performing an index and not establishing an index:

A. Before indexing is established

Copy Code code as follows:

Explain select name from fps where name= "Li Wu";

[SQL] Select name from fps where name= "Li Wu";
Effects of data bars: 0
Time: 0.003ms
B. When indexing is established

Copy Code code as follows:

Create index idx_name on fps (
Name
);

Explain select name from fps where name= "Li Wu";

[SQL] Select name from fps where name= "Li Wu";

Effects of data bars: 0
Time: 0.001ms

(2) Let's take a look at the meaning of this explain analysis result.
Table : This is the name of the watch.
Type: The types of connection operations. Here is a description of the MySQL document about the type of ref connection:
"For each combination of rows from the previous table, all rows that have a matching index value are read from this table." If the join uses only the leftmost prefix of the key, or if the key is not
Unique or primary key (in other words, if the join cannot select a single row based on the keyword), use ref. If you use a key that matches only a few rows, the join
The type is good. "In this case, because the index is not a unique type, ref is the best connection type we can get." If explain shows that the connection type is "All" and you don't want to select most of the records from the table, then MySQL will be very inefficient because it scans the entire table. You can add more indexes to solve this problem. For more information, please refer to the MySQL manual description.
Possible_keys:
The name of the index that may be available. The index name here is the index nickname specified when the index was created, and the first column in the index is displayed by default if the index does not have a nickname
(In this case, it's "idx_name").
Key:
It shows the name of the index that MySQL actually uses. If it is empty (or null), MySQL does not use the index.
Key_len:
The length of the part used in the index, in bytes.
Ref:
It displays the name of the column (or the word "const"), and MySQL selects the rows based on those columns. In this case, MySQL selects rows based on three constants.
rows:
The number of records that MySQL believes it must scan before it finds the correct result. Obviously, the ideal figure here is 1. The number of records traversed before indexing in this example is 1041, and the index is 1
Extra:
There may be many different options, most of which will have a negative impact on the query. In this case, MySQL simply reminds us that it will restrict the search result set with the Using Where,using index clause.

4. Most commonly used storage engines:
(1) MyISAM storage Engine: Each myisam is stored on disk as three files. The file name is the same as the table name, and the extension is. frm (storage table definition),. MyD (storing data),. Myi (storage index). Data files and index files can be placed in different directories, with an average distribution of IO, to achieve faster speeds. There is no limit to storage size, and the maximum valid table size for the MySQL database is usually determined by the operating system's limitations on file size, and the
(2) InnoDB storage Engine: transaction security with Commit, rollback, and crash recovery capabilities. Compared with MyISAM, InnoDB writes less efficiently and consumes more disk space to preserve data and indexes.
(3) How to select the appropriate engine
the following are the environments that the common storage engine applies To:
Myisam: It is on the web, Storage engines that are most commonly used in data warehousing and other application environments; The
InnoDB: is used for transaction processing applications with additional features, including acid transaction characteristics.