QuickStart Tutorial for Fast Learning Mysql index _mysql

The so-called index is a specific MySQL field for some specific algorithm sorting, such as the two-fork tree algorithm and hashing algorithm, the hash algorithm is established by establishing the eigenvalues, and then based on the characteristics of a quick lookup. and use the most, and is the MySQL default is the binary tree algorithm btree, through the btree algorithm to establish the index of the field, such as scanning 20 rows can get no use Btree scan before the results of the 2^20, the specific implementation of the following blog will be an algorithm topic will have a specific analysis of the discussion ;

Explain optimization query detection

Explain can help developers analyze SQL problems, explain shows how MySQL uses indexes to process SELECT statements and join tables, helping to choose better indexes and writing more optimized query statements.

Using the method, add explain to the SELECT statement before you can:

Explain select * from blog where false;

MySQL analyzes each SQL emitted before executing a query, deciding whether to use an index or full table scan if you send a select * from blog where Falsemysql does not perform a query operation, Because after the analysis of SQL Analyzer, MySQL has made it clear that no statement conforms to the operation;

Example

mysql> EXPLAIN Select ' Birday ' from ' user ' WHERE ' birthday ' < ' 1990/2/2 '; 

--The result:

Id:1 

 
Select_type:simple--Query type (simple query, federated query, subquery)  
 

• Table:user--Shows the data in this row about which table
• Type:range--Interval index (data in less than 1990/2/2 interval), which is an important column that shows what type of connection is used. From best to worst connection type for system > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery &G T Index_subquery > Range > Index > All,const on behalf of a hit, all representatives scanned the entire table to determine the results. In general, you have to ensure that the query reaches at least range level, preferably to 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 check the WHERE clause to see if some of the fields are referenced, or if the field is not an appropriate index.
• Key:birthday-the index that is actually used. If NULL, the index is not used. If primary, the primary key is used.
• Key_len:4-the longest index width. If the key is null, the length is null. Without loss of accuracy, the shorter the length the better.
• Ref:const--Shows which field or constant is used with the key.
• Rows:1--This number indicates how much data MySQL is going to traverse to find, not accurate on InnoDB.
• Extra:using where; Using index--execution status description, here's a bad example of using temporary and using

Select_type

• Simple select (Do not use union or subqueries)
• Primary most out of the Select
• The second or subsequent SELECT statement in the Union union
• The second or subsequent SELECT statement in the Dependent Union union, depending on the query outside
• Results of Union result union.
• Subquery The first select in a subquery
• Dependent the first select in the subquery subquery, depending on the query outside
• Derived a SELECT (subquery for the FROM clause) of the export table

Extra and type detailed description

• Distinct: Once MySQL finds a row that matches the row, it stops searching.
• The not Exists:mysql optimizes the left join, and once it finds a row that matches the left join criterion, it stops searching
• Range checked for every record (index map:#): No ideal index is found, so for each row from the previous table, MySQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections using the index
• Using Filesort: When you see this, the query needs to be optimized. MySQL needs to take extra steps to find out how to sort the rows 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 only uses the information in the index without reading the actual action, which occurs when the entire Request column for the table is 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 typically occurs when a different set of columns is on an order by, not a group by
• Where used uses a WHERE clause to restrict which rows 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. 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 line, this value is actually constant, because MySQL first reads the value and treats it as a constant.
• Eq_ref: In the 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 all of the index primary key or 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 of the previous table, all records are read from the table. This type relies heavily on the number of records that match against the index-the less the better +
• Range: This connection type uses the index to return rows in a range, such as what happens when you use > or < to find something
• Index: This connection type makes a full scan of each record in the previous table (better than all because the index is generally less than the table data) +
• All: This connection type is completely scanned for each of the previous records, which is generally bad and should be avoided
• Where type:
• If it is only index, this means that the information is retrieved using only the information in the index tree, which is faster than scanning the entire table.
• If it is a where used, the where constraint is used.
• If it's a impossible where it means no where, it's usually nothing.
• If this information displays using Filesort or using temporary, it will be difficult to find the index of where and order by, and if the index is determined according to the where, then it is bound to cause a using Filesort, this depends on the first filter and then sort the cost, or the first sorting and filtering cost-effective.

Index

Types of indexes

(1) Unique unique index

You cannot have the same value, you can have a null value

(2) Index general indexes

Allow the same indexed content to appear

(3) primary key index for PRIMARY key

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

(4) Fulltext index full-text indexing

All three of these indexes work on the value of a column, but Full-text indexing can be used for a word in a value, such as a word in an article, but there is no egg in it, because only MyISAM and English support, and efficiency is not flattering, But it can be done with third-party applications like Coreseek and Xunsearch.

MySQL supports many data types, and choosing the right data type to store data has a significant impact on performance. Generally, you can follow some of the following guidelines:

(1) Smaller data types are generally better: smaller data types typically require less space in disk, memory, and CPU caching, and are faster to handle.
(2) A simple data type is better: integer data has less processing overhead than characters because the strings are more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.
(3) Try to avoid null: you should specify NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they make indexing, indexing, and comparison operations more complex. You should use 0, a special value, or an empty string instead of a null value.
For any DBMS, indexing is the most important factor for optimization. For a small amount of data, no proper index impact is not very large, but when the volume of data increases, performance will drop dramatically.
If you index multiple columns (combined indexes), the order of the columns is very important, and MySQL can only find the leftmost prefix of the index effectively. For example:
The query statement select * from T1 where c1=1 and c2=2 can use this index, assuming there is a composite index IT1C1C2 (C1,C2). The query statement select * FROM T1 where c1=1 can also use the index. However, the query statement select * FROM T1 where c2=2 is not able to use the index, because there is no boot column that combines the index, that is, if you want to use the C2 column for a lookup, you must appear c1 equal to a value.

Indexing is the key to fast searching. The establishment of MySQL index is important for MySQL to run efficiently.

In a database table, indexing a field can greatly improve the query speed. If we create a mytable table:

CREATE TABLE mytable (

ID INT not NULL, 

username VARCHAR is not null

);

We randomly inserted 10,000 records into it, one of which was: 5555, admin.

In the lookup username= "admin" record SELECT * from mytable WHERE username= ' admin ', if the index has been established on the username, MySQL does not need any scan, that is, the record can be found accurately. Instead, MySQL scans all the records to find 10,000 records.

Creation of indexes

ALTER TABLE

Apply to add after table 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, but not, 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 ') 
alte R TABLE ' table_name ' ADD fulltext KEY (' column_list ') 
CREATE INDEX

Create index to add a normal index or a 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) 

In addition, you can add

Create TABLE ' test1 ' ( 
 ' id ' smallint (5) UNSIGNED auto_increment not NULL--note that the primary key index is created below without creating 
 ' username ' var char () NOT NULL COMMENT ' username ', 
 ' nickname ' varchar not null COMMENT ' nickname/Name ', 
 ' intro ' text, 
 PRIMARY KEY (' Id '), the 
 UNIQUE key ' unique1 ' (' username '),--index name, but not, not just the same as the column name 
 key ' Index1 ' (' nickname '), 
 fulltext key ' Intro ' (' intro ') 
engine=myisam auto_increment=4 DEFAULT charset=utf8 ' backend user table '; 

Deletion of indexes

Drop index ' index_name ' on ' talbe_name ' 
ALTER TABLE ' table_name ' DROP index ' index_name ' 
--both are equivalent, is to delete the index index_name in the table_name; 
 
ALTER TABLE ' table_name ' drop PRIMARY key--Deletes primary key index, note that primary key index can only be deleted in this way 

View of the Index

Show index from TableName \g;

Changes to Indexes

Change a yarn, delete the reconstruction of a Can-

Tips for creating indexes

1. High-dimension column creation index

The number of distinct values in the data column, the higher the number, the higher the dimension

If there are 8 rows of data in the datasheet A, B, c,d,a,b,c,d This table has a dimension of 4

To create indexes for dimension-high columns, such as gender and age, the dimensions of the age are higher than the gender

Sex Such columns are not suitable for indexing because the dimension is too low

2. Use indexes for 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 a prefix index for longer strings

5. Do not create too many indexes, in addition to additional disk space, the speed of the DML operation has a significant impact, because every time they are added to the new index

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

Combined index and prefix index

Note that these two names are a kind of salutation to the indexing technique, not the type of the index;

Composite Index

What is the difference between MySQL Single-column index and composite index?

To visually contrast the two, build a table first:

CREATE TABLE ' Myindex ' ( 
 ' i_testid ' INT not null auto_increment, 
 ' vc_name ' VARCHAR (%) not NULL, 
 ' vc_city ' VA Rchar is not null, 
 ' i_age ' int is not NULL, 
 ' i_schoolid ' int is not NULL, 
 PRIMARY KEY (' I_testid ') 
); 

If there are 1000 data in the table, there are 5 vc_name= "Erquan" records in the 10,000 records 7 8, but the City,age,school combinations are different. Look at this T-sql:

SELECT ' I_testid ' from ' myindex ' WHERE ' vc_name ' = ' Erquan ' and ' vc_city ' = ' zhengzhou ' and ' i_age ' = 25; --related search;
First consider building a MySQL single-column index:

An index was established on the Vc_name column. When you execute T-SQL, MYSQL quickly locks the target on the 5 records of Vc_name=erquan, taking it out and putting it in an intermediate 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 records, finally filtered out the only eligible records. Although the index is established on the Vc_name, the query MySQL does not need to scan the entire table, the efficiency is improved, but there is a certain distance from our request. Similarly, the efficiency of the MySQL Single-column index established separately in vc_city and I_age is similar.

To further squeeze MySQL's efficiency, consider establishing 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), vc_city,i_age);

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

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

If a Single-column index is established on the Vc_name,vc_city,i_age, the table has 3 Single-column indexes, and the query is as efficient as the combined index above? The answer is very different, far below our combined index. Although there are three indexes at this time, MySQL can only use the one that it considers to be the most efficient Single-column index, the other two are not available, that is, the process of a full table scan.

The establishment of such a composite index, in fact, is equivalent to the establishment of a separate

• Vc_name,vc_city,i_age
• Vc_name,vc_city
• Vc_name

Such a combination of three indexes! Why isn't there such a combination index as vc_city,i_age? This is because the MySQL composite index "leftmost prefix" results. The simple understanding is to just start from the left. Instead of using the combined index for queries that contain all three columns, the following T-SQL uses:

SELECT * from Myindex whree vc_name= "Erquan" and vc_city= "Zhengzhou" select * from Myindex whree vc_name= "Erquan"

The following few 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, vc_city,i_age) is indexed from left to right, and if there is no left-front index MySQL does not perform the 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 method index prefix index should be controlled at a suitable point, control at 0.31 gold value can be (greater than this value to create)

SELECT COUNT (DISTINCT (' title '))/count (*) from Arctic; --this value is greater than 0.31 to create a prefix index, distinct to repeat ALTER TABLE ' user ' ADD INDEX ' uname ' (title (10)); --Add prefix index SQL, set the index of people name to 10, which can reduce index file size, speed index query

What kind of SQL does not go to the index

Try to avoid these not-going-indexed SQL

Select ' sname ' Stu ' where ' age ' +10=30;--will not use the index because all indexed columns are involved in the calculation of 
 
SELECT ' sname ' from ' Stu ' where left (' Date ', 4) < 1990; --The index is not used because the function operation is the same as the previous 
 
select * from ' Houdunwang ' WHERE ' uname ' like ' backing% '-Walk index 
 
SELECT * from ' Houdunwang ' W Here's ' uname ' like '% backing% '--no indexing- 
 
-Regular expressions don't use indexes, which should be well understood, so why is it hard to see the reason for the REGEXP keyword in sql- 
 
-strings and numbers are not indexed; 
CREATE TABLE ' A ' (' A ' char ()); 
EXPLAIN select * from ' a ' where ' a ' = ' 1 '--Walk index 
EXPLAIN select * from ' a ' where ' a ' = 1--Do not go index 
 
SELECT * FROM dept WH ere dname= ' xxx ' or loc= ' xx ' or deptno=45--if there is an or in the condition, it will not be used even if it has a conditional index. In other words, all the fields required to be used must be indexed, and we recommend that you avoid using the OR keyword as much as possible 
 
--if MySQL estimates using full table scans is faster than indexing, do not use the index 

Indexing efficiency in multiple table associations

SELECT ' sname ' from ' Stu ' WHERE Left (' Date ', 4) <1990;  --The index is not used because the principle is the same as the above
select * from ' Houdunwang ' WHERE ' uname ' like ' backing% '-Walk index
SELECT * from ' Houdunwang ' WHERE ' uname ' like '% backing% '--do not go index

As can be seen from the above figure, all tables are of type all, representing the whole table index, that is 6 6 6, the total traversal query is 216 times;

In addition to the first representation of the full table index (must be associated with this other table), the rest of the range (indexed range), that is, 6+1+1+1, the total traversal query 9 times;

So we recommend that you join a few tables as little as possible, because carelessness is a terrorist scan of the Cartesian product, and we also recommend that you use a LEFT join as much as possible. Because the first table is a must-have-scan with a join, With fewer associations, you can reduce the number of scans.

Disadvantages of indexing

Do not blindly create indexes, only for columns that frequently query operations, creating indexes makes query operations faster, but reduces the speed of adding, deleting, and updating operations, because the index files are reordered or updated as they are performed;

However, in the Internet application, the query statement is far greater than the statements of DML, can even account for 80%~90%, so do not care too much, just in large data import, you can delete the index, and then bulk insert data, and finally add index;