Quick Start to MySQL index super tutorial, quick start to mysql Index

Source: Internet
Author: User
Tags mysql index

Quick Start to MySQL index super tutorial, quick start to mysql Index

The so-called index is to sort specific algorithms for specific mysql fields, such as Binary Tree algorithms and hash algorithms. The hash algorithm creates feature values and then quickly searches for them based on the feature values. The most widely used field is the binary tree algorithm BTREE, which is used by mysql by default, for example, you can scan 20 rows to get the result of scanning 2 ^ 20 rows before using B-tree. The specific implementation method will be discussed in a topic of algorithms in this blog;

Explain Optimize Query Detection

EXPLAIN helps developers analyze SQL problems. It shows how mysql uses indexes to process select statements and connect tables. It can help select better indexes and write more optimized query statements.

You can add the Explain statement before the select statement:

Explain select * from blog where false;

Mysql analyzes each SQL statement before executing a query, determines whether to use an index or full table scan. If you send a select * from blog where falseMysql statement, the query operation is not performed, after the analysis by the SQL analyzer, MySQL knows that no statements will comply with the operation;

Example

mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2"; 

-- Result:

id: 1 


Select_type: SIMPLE -- Query type (SIMPLE query, joint query, subquery)
 

  • Table: user -- displays the data of this row about which table
  •  
  • Type: range -- interval index (Data smaller than the 1990/2/2 range). This is an important column that shows the type of connection. The connection type from the best to the worst is system> const> eq_ref> ref> fulltext> ref_or_null> index_merge> unique_subquery> index_subquery> range> index> ALL. const indicates a hit, ALL indicates that the results are determined only when the entire table is scanned. In general, make sure that the query reaches the range level at least, and it is best to reach the ref level.
  •  
  • Possible_keys: birthday -- specifies which index MySQL can use to find rows in the table. If it is null, there is no relevant index. To improve performance, you can check the WHERE clause to see if some fields are referenced or if the fields are not suitable for indexing.
  •  
  • Key: birthday -- actually used index. If it is NULL, no index is used. If primary is used, the primary key is used.
  •  
  • Key_len: 4 -- the longest index width. If the key is NULL, the length is NULL. The shorter the length, the better.
  •  
  • Ref: const -- display the field or constant used with the key.
  •  
  • Rows: 1 -- this number indicates the amount of data that mysql needs to traverse before it can be found. It is inaccurate on innodb.
  •  
  • Extra: Using where; Using index -- execution status description. here we can see bad examples: Using temporary and Using

Select_type

  • Simple select (do not use union or subquery)
  • Primary exclusive select
  • The second or subsequent select statement in union
  • The second or subsequent select statement in the dependent union is dependent on the external query.
  • Union result.
  • The first select in subquery
  • The first select in the dependent subquery depends on the external query.
  • Select (subquery of the from clause) of the derived export table)

Extra and type

  • Distinct: Once MYSQL finds the row that matches the row, it does not search again.
  • Not exists: MYSQL optimizes left join. Once it finds a row that matches the left join standard, it no longer searches.
  • Range checked for each Record (index map: #): no ideal index is found. Therefore, for each row combination in the preceding table, MYSQL checks which index is used, use it to return rows from the table. This is one of the slowest connections using indexes.
  • Using filesort: When you see this, the query needs to be optimized. MYSQL requires additional steps to find out how to sort the returned rows. It sorts all rows according to the connection type and the row pointer that stores the sort key value and all rows matching the condition.
  • Using index: the column data is returned from a table that only uses the information in the index but does not read the actual action. This occurs when all the request columns in the table are in the same index.
  • When Using temporary sees this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results. This usually happens when order by is applied to different column sets, rather than group.
  • Where used uses the WHERE clause to limit which rows match the next table or return the rows to the user. If you do not want to return ALL rows in the table and the connection type is ALL or index, this will happen, or if there is a problem with the query interpretation of different connection types (sort by efficiency order)
  • The system table has only one row: system table. This is a special case of the const connection type.
  • Const: the maximum value of a record in the table can match this query (the index can be a primary key or a unique index ). Because there is only one row, this value is actually a constant, because MYSQL first reads this value and treats it as a constant.
  • Eq_ref: During the connection, MYSQL reads a record from the table from the Union of each record in the previous table during query, it is used when you query all data that uses the index as the primary key or unique key.
  • Ref: This connection type only occurs when the query uses keys that are not the only or primary key, or some of these types (for example, using the leftmost prefix. For each row union in the previous table, all records are read from the table. This type depends heavily on the number of records matched by the index. The fewer the records, the better. +
  • Range: This connection type uses an index to return rows in a range, such as> or <what happens when something is searched +
  • Index: This connection type performs a full scan of each record in the preceding table (better than ALL, because the index is generally smaller than the table data) +
  • ALL: this connection type performs a full scan for each of the preceding records. This is generally poor and should be avoided as much as possible.
  • Type:
  • If it is Only index, this means that information is Only retrieved from the index tree, which is faster than scanning the entire table.
  • If it is where used, the where restriction is applied.
  • If it is impossible where, it indicates that the where is not needed. Generally, nothing is found.
  • If this information shows Using filesort or Using temporary, it will be very difficult, and the WHERE and order by indexes are often unable to take into account. If the index is determined by where, then in order, this will inevitably lead to Using filesort. It depends on whether filtering and sorting are cost-effective, or sorting and filtering are cost-effective.

Index

Index type

(1) UNIQUE index of UNIQUE

The same value cannot be displayed, but the value can be NULL.

(2) INDEX common INDEX

The same index content is allowed.

(3) primary key index

The same value cannot appear and cannot be NULL. A table can have only one primary_key index.

(4) fulltext index full-text index

The above three indexes are used for column values. However, full-text indexes can be used for a word in a value, such as a word in an article. However, they are useless, this is because only myisam and English are supported, and the efficiency is not flattering. However, you can use third-party applications such as coreseek and xunsearch to meet this requirement.

MySQL supports many data types. Selecting the appropriate data type to store data has a great impact on performance. Generally, the following guiding principles can be observed:

(1) smaller data types are generally better: smaller data types usually require less space in the disk, memory, and CPU cache for faster processing.
(2) A simple data type is better: the processing overhead of integer data is smaller than that of characters, because the strings are more complex. In MySQL, the built-in Date and Time data types should be used instead of strings to store the time, and the IP addresses of integer data types should be used to store the time.
(3) Avoid NULL as much as possible: the column should be specified as not null unless you want to store NULL. In MySQL, it is difficult to query and optimize columns with null values, because they make the index and index statistics and comparison operations more complex. You should replace null values with 0, a special value, or an empty string.
For any DBMS, indexing is the most important factor for optimization. For a small amount of data, the impact of no suitable index is not very big, but as the data volume increases, the performance will drop sharply.
If multiple columns are indexed (composite indexes), the column order is very important. MySQL can only search for the leftmost prefix of the index effectively. For example:
Assume that the combined index it1c1c2 (c1, c2) exists. The query statement select * from t1 where c1 = 1 and c2 = 2 can use this index. The query statement select * from t1 where c1 = 1 can also use this index. However, the query statement select * from t1 where c2 = 2 cannot use this index, because there is no Bootstrap column for the combined index, that is, to use column c2 for search, c1 must be equal to a value.

Index is the key to quick search. The establishment of MySQL indexes is very important for the efficient operation of MySQL.

Index fields in database tables can greatly improve the query speed. Suppose we have created a mytable table:

CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL);

We randomly inserted 10000 records, one of which is 5555 and admin.

When querying the record SELECT * FROM mytable WHERE username = 'admin'; of username = "admin", MySQL does not need to perform any scans if an index has been created on username, this record can be found accurately. On the contrary, MySQL scans all records to query 10000 records.

Index creation

ALTER TABLE

Applies to adding a table after it is created.

Alter table name ADD index type (unique, primary key, fulltext, index) [index name] (field name)

Alter table 'table _ name' ADD index'index _ name' ('column _ list') -- INDEX name; optional; if not, the current index name is the field name; alter table 'table _ name' add unique ('column _ list ') alter table 'table _ name' add primary key ('column _ list') ALTER table 'table _ name' add fulltext key ('column _ list') CREATE INDEX

Create index adds a common INDEX or unique index to a table.

-- For example, only these two indexes can be added;

CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list) 

You can also add

Create table 'test1' ('id' smallint (5) UNSIGNED AUTO_INCREMENT not null, -- Note: The primary key index is created below, and 'username' varchar (64) is NOT created here) not null comment 'username', 'nickname' varchar (50) not null comment' nickname/name', 'intro' text, primary key ('id '), unique key 'unique1 '('username'), -- index name, optional or not. The KEY 'index1' ('nickname') is the same as the column name '), fulltext key 'intro' ('intro') ENGINE = MyISAM AUTO_INCREMENT = 4 default charset = utf8 COMMENT = 'background user table ';

Index Deletion

Drop index 'index _ name' ON 'talbe _ name' alter table 'table _ name' DROP index 'index _ name' -- both statements are equivalent, delete the index index_name in table_name; alter table 'table _ name' drop primary key -- delete the primary key index. Note that the primary key index can only be deleted in this way.

View Indexes

show index from tablename \G;

Index Changes

Modify the wool and delete and recreate a wool --

Tips for creating Indexes

1. Create an index for a column with a high dimension

The number of non-repeated values in the data column. The higher the number, the higher the dimension.

If there are eight rows of data in the data table: a, B, c, d, a, B, c, d. the dimension of the table is 4.

To create an index for a column with high dimensions, such as gender and age, the age dimension is higher than the gender.

Columns such as gender are not suitable for creating indexes because the dimension is too low.

2. Use indexes for columns in where, on, group by, and order

3. Use indexes for small data columns, which will make the index file smaller and more index keys can be loaded in the memory.

4. Use prefix indexes for long strings

5. Do not create too many indexes. In addition to adding extra disk space, the speed of DML operations is greatly affected, because each time it is added, it is necessary to create a new index.

6. Using composite indexes can reduce the file index size, which is faster than multiple single-column indexes.

Combined index and prefix Index

Note that these two terms are used to create an index, not the index type;

Composite Index

What is the difference between a single-column index and a combined index in MySQL?

To visually compare the two, create a table first:

CREATE TABLE `myIndex` (  `i_testID` INT NOT NULL AUTO_INCREMENT,  `vc_Name` VARCHAR(50) NOT NULL,  `vc_City` VARCHAR(50) NOT NULL,  `i_Age` INT NOT NULL,  `i_SchoolID` INT NOT NULL,  PRIMARY KEY (`i_testID`) ); 

Assume that there are 1000 data records in the table, five vc_Name = "erquan" records are distributed in the Top 7 and bottom 8 of the 10000 records, except for the city, age, the school combinations are different. Look at this T-SQL:

SELECT 'I _ testid' FROM 'myindex' WHERE 'vc _ name' = 'erquand' AND 'vc _ City' = 'zhengzhou' AND 'I _ age' = 25; -- associated search;
First, create a single MySQL index:

An index is created on the vc_Name column. When the T-SQL was executed, MYSQL quickly locked the target to five records in vc_Name = erquan and pulled them out to the intermediate result set. In this result set, records with vc_City not equal to "Zhengzhou" are excluded, records with I _Age not equal to 25 are excluded, and a unique matching record is filtered out. Although an index is created on vc_Name, MYSQL does not need to scan the entire table during query, which improves the efficiency, but there is still some distance from our requirements. Similarly, the efficiency of single-column MySQL indexes created in vc_City and I _Age is similar.

To further extract MySQL efficiency, you must consider establishing a composite index. Vc_Name, vc_City, and I _Age are built into an index:

Alter table 'myindex' ADD index'name _ city_age '(vc_Name (10), vc_City, I _Age );

When creating a table, the length of vc_Name is 50. Why is 10 used here? This is the prefix index mentioned below, because the name length generally does not exceed 10, which will accelerate the index query speed and reduce the size of the index file, increases the INSERT update speed.

When you run the T-SQL, MySQL finds a unique record without scanning any record !!

If you create a single-column index on vc_Name, vc_City, and I _Age respectively, so that the table has three single-column indexes, will the query efficiency be the same as the preceding combined index? The answer is a big difference, far lower than our combined index. Although there are three indexes at this time, MySQL can only use one of them, which seems to be the most efficient single-column index, and the other two cannot be used, that is to say, it is still a full table scan process.

The establishment of such a composite index is actually equivalent to the establishment

  • Vc_Name, vc_City, I _Age
  • Vc_Name, vc_City
  • Vc_Name

These three composite indexes! Why are there no composite indexes such as vc_City and I _Age? This is because mysql Composite Index "leftmost prefix" results. A simple understanding is to combine only from the leftmost. Not as long as the query contains these three columns will use the composite index, the following T-SQL will use:

SELECT * FROM myIndex WHREE vc_Name = "erquan" AND vc_City = "Zhengzhou" SELECT * FROM myIndex WHREE vc_Name = "erquan"

The following are not used:

SELECT * FROM myIndex WHREE I _Age = 20 AND vc_City = "Zhengzhou" SELECT * FROM myIndex WHREE vc_City = "Zhengzhou"

That is, name_city_age (vc_Name (10), vc_City, I _Age) is indexed from left to right. If there is no left index, Mysql does not perform index query.

Prefix Index

If the index column length is too long, this type of column index will generate a large index file, which is not easy to operate. You can use the prefix index method to index the prefix at a suitable point, it can be controlled at a value of 0.31 gold (more than this value can be created)

Select count (DISTINCT (LEFT ('title', 10)/COUNT (*) FROM Arctic; -- a prefix index can be created if the value is greater than 0.31, distinct repeats alter table 'user' add index 'uname' (title (10); -- adds the prefix index SQL, and builds the INDEX of the person's name on 10, this reduces the size of the index file and speeds up index query.

What types of SQL statements do not go indexed?

Try to avoid these SQL statements without Indexing

SELECT 'sname' FROM 'std' WHERE 'age' + 10 = 30; -- no index is used, because all index columns are involved in the calculation of SELECT 'sname' FROM 'stu' where left ('date', 4) <1990; -- indexes are not used because function operations are used, the principle is the same as that above: SELECT * FROM 'houdunwang 'WHERE 'uname' LIKE' backing % '-- SELECT * FROM 'houdunwang' WHERE 'uname' LIKE "% backing %" -- not going index -- Regular Expressions Do not use indexes, this should be well understood, so it is difficult to see the reason for the regexp keyword in SQL-strings and numbers do not use indexes; create table 'A' ('A' char (10); explain select * FROM 'A' WHERE' A' = "1" -- Take the index explain select * FROM 'A' WHERE 'a' = 1 -- Do not go to the index select * from dept where dname = 'xxx' or loc = 'xx 'or deptno = 45 -- if the condition contains or, it is not used even if there is a condition with an index. In other words, all the fields required must be indexed. We recommend that you avoid using the or keyword whenever possible. If mysql estimates that full table scan is faster than indexing, no index is used.

Index efficiency during multi-Table Association

SELECT 'sname' FROM 'stu' where left ('date', 4) <1990; -- indexes are not used because function operations are used, the principle is the same as that above: SELECT * FROM 'houdunwang 'WHERE 'uname' LIKE' backing % '-- SELECT * FROM 'houdunwang' WHERE 'uname' LIKE "% backing %" -- not going index

It can be seen that the type of all tables is all, indicating the full table index; that is, 6, 6, 6, 6, and 216 queries are traversed in total;

Except for the first table index (required, to be associated with other tables), the rest is range (obtained from the index range), that is, 6 + 1 + 1 + 1 + 1, you can traverse and query nine times in total;

Therefore, we recommend that you join as few tables as possible during multi-table join, because accidentally it is a terrorist scan of the Cartesian product. In addition, we recommend that you use left join as much as possible, more with less Association. if join is used, the first table must be fully scanned. The number of scans can be reduced with less Association.

Disadvantages of Indexing

Do not blindly create indexes. Only create indexes for columns with frequent query operations. Creating indexes will make the query operations faster, but will reduce the speed of adding, deleting, and updating operations, these operations will re-sort or update the index files;

However, in Internet applications, the query statements are much larger than DML statements, and can even account for 80% ~ 90%, so do not worry too much, but when importing big data, you can first Delete the index, then insert data in batches, and then add the index;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.