mysql-Index Optimization

An index is a specific sort of algorithm for a particular MySQL field, such as a two-tree algorithm and a hashing algorithm. MySQL default is the two fork tree algorithm BTREE.

Explain optimized query detection

Explain can help developers analyze SQL problems, explain shows how MySQL uses indexes to process SELECT statements and join tables.

How to use: Add explain before the SELECT statement,

select * from blog where myname=‘alex‘;

Before executing a query, MySQL parses each SQL issued to determine whether to use an index or a full table scan. If you send a select * from blog where False,mysql is not performing a query operation, because after parsing the SQL parser, MySQL has been clearly aware that there will be no statements that conform to the operation.

query example,

Mysql> EXPLAINSELECT' Birday ' from' User 'WHERE' Birthday ' <"1990/2/2"; --Result: ID:1Select_type:simple--Query type (simple query, federated query, subquery) Table:user--Shows the data of this row is about which table Type:range--interval index (in less than1990/2/2Interval of data), which is an important column that shows what type of connection is used. The best to worst connection type is **system >Const> Eq_ref > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_subquery > Range & Gt Index > all**,ConstRepresents a hit, all represents a scan of the full table before the result is determined. In general, it is best to ensure that the query reaches at least the range level, preferably ref. Possible_keys:birthday--Indicates which index MySQL can use to find rows in the table. If it is empty, there is no index associated with it. To improve performance, you can pass the testWHEREclause to see if some fields are referenced, or check that the fields are not appropriate for the index.Key: Birthday--The index that is actually used. If NULL, the index is not used. If it is primary, it means that the primary key is used. Key_len:4-the longest index width. If the key is null, the length is null. With no loss of accuracy, the shorter the length, the better ref:Const--Shows which field or constant is associated withKeyare used together. Rows1This number indicates how much data MySQL will traverse to find and is not accurate on InnoDB. Extra:Using where;UsingIndex--Execution status description, the bad example that can be seen here isUsingTemporary andUsing

Select_type
Simple select (Do not use union or subquery)
Primary the outermost Select
The second or subsequent SELECT statement in a Union union
Dependent the second or subsequent SELECT statement in the Union Union, depending on the outside query
The result of Union result union.
Subquery The first select in a subquery
Dependent the first select in a subquery subquery, depending on the outside query
Derived the select of the exported table (subquery FROM clause)

Extra,type Detailed description

Distinct: Once MySQL finds a row that matches a row, it no longer searches for

Not Exists:mysql optimizes the left join, and once it finds a row that matches the left join standard, it no longer searches for

Range checked for each Record (index map:#): No ideal index was found, so for every combination of rows from the preceding table, MySQL examines which index to use and uses it to return rows from the table. This is one of the slowest connections to use the index

Using filesort: When you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria.
Using index: Column data is returned from a table that uses only the information in the index and does not read the actual action, which occurs when all the request columns of the table are part of the same index

Using Temporary when you see this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results, which usually occurs on an order by on a different set of columns, rather than on the group by

Where used uses a WHERE clause to restrict which rows will match the next table or return to the user. If you do not want to return all the rows in the table, and the connection type all or index, this occurs, or the query has a problem the interpretation of different connection types (sorted in order of efficiency)

The system table has only one row: the system table. This is a special case of the const connection type

Const: The maximum value of a record in a 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 reads the value first and treats it as a constant.

Eq_ref: In a connection, when MySQL queries, from the previous table, the union of each record reads a record from the table, which is used when the query uses the index as the primary key or the unique key.

Ref: This connection type occurs only if the query uses a key that is not a unique or primary key or is part of these types (for example, using the leftmost prefix). For each row union of the previous table, all records are read from the table. This type is heavily dependent on how many records are matched against the index-the less the better

Range: This connection type uses an index to return rows in a range, such as what happens when you use > or < to find something

Index: This connection type is fully scanned for each record in the previous table (better than all because the index is generally less than the table data)

All: This connection type is fully scanned for each previous record, which is generally bad and should be avoided as much as possible.

If it is only index, this means that information is retrieved only from the information in the index tree, which is faster than scanning the entire table.

If it is a where used, the where limit is used.

If it is impossible where means no where, it is generally not found out what.

If this information shows the using Filesort or using temporary, then the where and order by indexes are often out of balance, and if the index is determined by where, then the order by will inevitably cause the using Filesort, it depends on whether to filter and reorder the cost, or first sort and then filter the cost.

Type of index

Unique Unique index
Can not appear the same value, can have null value

Index Normal indexes
Allow the same indexed content to appear

PRIMARY Key PRIMARY Key index
The same value is not allowed and cannot be a null value, and a table can have only one Primary_key index

Fulltext Index full-text indexing
You can target a word in a value, such as a word in a story,
MySQL 5.6.4 starts to support InnoDB full-Text Search

Creation of indexes

ALTER TABLE
Applies when the table is created and then added

ALTER table name ADD index type (unique,primary key,fulltext,index) index name

 ALTER TABLE ' table_name ' ADD INDEX ' index_name ' (' column_list ' )--index name, can not be;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 to add a normal or unique index to a table

--例,只能添加这两种索引; CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list)

In addition, you can add a table when you build it

CREATE TABLE ' test1 '(' id ' smallint(5) UNSIGNED Auto_increment not NULL,--note that the primary key index is created below and there is no need to create a' username ' varchar( -) not NULLCOMMENT' User name ',' nickname ' varchar( -) not NULLCOMMENT' nickname/name ',' intro 'TextPRIMARY KEY(' id '),UNIQUE KEY ' unique1 '(' username '),---index name, can not, not just the same as the column nameKEY ' index1 '(' nickname '), FulltextKEY ' intro '(' intro ')) Engine=myisam auto_increment=4 DEFAULTCharset=utf8 comment=' Background user table ';

Deletion of indexes

DROP INDEX `index_name` ON `talbe_name`  ALTER TABLE `table_name` DROP INDEX `index_name` -- 这两句都是等价的,都是删除掉table_name中的索引index_name;ALTER TABLE `table_name` DROP PRIMARY KEY -- 删除主键索引,注意主键索引只能用这种方式删除

View of the Index

show index from tablename \G;

Changes to the index
Delete the rebuilt one can either

Tips for creating indexes

1. Creating indexes on columns with high dimensions
The number of distinct values in the data column, the higher the dimension, the greater the
If the data table has 8 rows of data A, B, c,d,a,b,c,d the dimension of this table is 4
To create indexes for columns with high dimensions, such as gender and age, the dimension of the age is higher than the gender
Columns such as gender are not suitable for creating indexes because the dimensions are too low

2. Use indexes on columns that appear in Where,on,group By,order by

3. Use indexes on smaller data columns, which makes the index file smaller and allows more index keys to be loaded in memory

4. Use the prefix index for longer strings

5. Do not create indexes too much, except for additional disk space, which has a significant impact on the speed of DML operations because they have to be re-indexed every time they are incremented

6. Using a composite index, you can reduce the file index size and use it faster than multiple single-column indexes

Combined Index
To visually compare the two, first build a table:

CREATE TABLE ' Myindex '(' I_testid ' INT  not NULLAuto_increment,' Vc_name ' VARCHAR( -) not NULL,' vc_city ' VARCHAR( -) not NULL,' I_age ' INT  not NULL,' I_schoolid ' INT  not NULL,PRIMARY KEY(' I_testid ')  );

Assuming that the table already has 1000 data, in these 10,000 records 7 8 in the field distribution of 5 vc_name= "Erquan" records, but the combination of City,age,school are different. Look at this T-sql:

`i_testID` FROM `myIndex` WHERE `vc_Name`=‘`vc_City`=‘`i_Age`=25; -- 关联搜索;

If you consider building a MySQL single-column index:
An index was established on the Vc_name column. When executing T-SQL, MYSQL quickly locks the target on the 5 records of Vc_name=erquan and takes it out to a middle result set. In this result set, the first rule out vc_city not equal to "Zhengzhou" record, and then exclude i_age not equal to 25 of the record, and finally filtered out the only qualified records. Although the index is built on the vc_name, MySQL does not have to scan the whole table when querying, but the efficiency is improved, but there is a certain distance from our request. Similarly, the MySQL single-column indexes established separately in vc_city and i_age are similar in efficiency.

To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build the Vc_name,vc_city,i_age into an index:

ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age);

When the table is built, the length of the vc_name is 50, why is it used in 10? This is the prefix index that is mentioned below, because in general, the length of the name does not exceed 10, which speeds up the index query, reduces the size of the index file, and increases the update speed of the INSERT.

When executing T-SQL, MySQL does not need to scan any records to find a unique record!!

If you set up a single-column index on Vc_name,vc_city,i_age, so that the table has 3 single-column indexes, is the query as efficient as the combined index above? The answer is much different, much lower than our combined index. Although there are three indexes at this point, MySQL can only use the one that it considers to be the most efficient single-column index, the other two are not used, that is, a full-table scan process.

The establishment of such a composite index is actually equivalent to establishing a separate
Vc_name,vc_city,i_age
Vc_name,vc_city
Vc_name
Such a three combination index! Why is there no such combination index as vc_city,i_age? This is because MySQL combines the results of the "leftmost prefix " of the index. The simple understanding is only from the left to the beginning of the combination. It is not just that the combined index is used for queries that contain these three columns, and several of the following T-SQL is used:

SELECTFROMANDSELECTFROM myIndex WHREE vc_Name=”erquan”

And the next few are not used:

SELECTFROM myIndex WHREE i_Age=20ANDSELECTFROM myIndex WHREE vc_City=”shanghai”

That is, Name_city_age (Vc_name (), vc_city,i_age) is indexed from left to right, and if there is no left front index MySQL does not perform an index query

Prefix index
If the index column length is too long, this column index will produce a large index file, not easy to operate, you can use the prefix index indexing prefix index should be controlled at a suitable point, control at 0.31 gold value (greater than this value can be created)

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic;

This value is greater than 0.31 to create a prefix index

ALTER TABLE `user` ADD INDEX `uname`(title(10));

Increase prefix index SQL to set the index of person names at 10, which reduces index file size and speeds up index queries

What kind of SQL does not go index

 SELECT ' sname ' from  ' stu ' WHERE ' age '+Ten=30 ;--The index is not used because all indexed columns participate in the calculation SELECT ' sname '  from ' Stu ' WHERE left (' Date ',4) <1990; --no index is used because the function operation is used, and the principle is the same as aboveselect  * FROM  Span class= "hljs-string" > ' Houdunwang '  where   Uname '  like   ' backing% ' -Go index select  * from   ' Houdunwang '  where   ' uname '  like  --do not go index--the regular expression does not use an index, which should be well understood, so why in sql  It is difficult to see the reason for the RegExp keyword--the string is not indexed compared to the number;   CREATE TABLE ' a ' (' a ' char(ten));EXPLAINSELECT* from ' A ' WHERE ' A '="1"--Walk Index EXPLAINSELECT* from ' A ' WHERE ' A '=1--Do not go indexSelect* fromDeptwhereDname=' xxx ' orLoc=' xx ' ordeptno= $-If the condition hasor, even if the conditional index is not used. In other words, all the fields that are required to be used must be indexed, and we recommend that you try to avoid usingorKeyword--if MySQL estimates that using a full table scan is faster than using an index, the index is not used

Disadvantages of indexing
Instead of blindly creating indexes, creating indexes on columns that are frequently queried, creating indexes can make query operations faster, but it can slow the increase, deletion, and update operations, because the index files are reordered or updated at the same time as these operations are performed;

When you import big data, you can delete the index, insert the data in bulk, and then add the index.

mysql-Index Optimization