SQL Study Notes (20) ---------- MySQL INDEX OPTIMIZATION full strategy, mysql INDEX OPTIMIZATION

SQL Study Notes (20) ---------- MySQL INDEX OPTIMIZATION full strategy, mysql INDEX OPTIMIZATION

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 in this row about which table has the type: range -- interval index (Data smaller than the 1990/2/2 range). This is an important column, shows the connection type. 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 -- indicates 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

Extra and type

Type:

Index

Index type

UNIQUE index

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

INDEX Common INDEX

The same index content is allowed.

Primary key index

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

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.

CURD of the Index

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, you can only add these two indexes. 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;-you can create a prefix index if the value is greater than 0.31, distinct repeats alter table 'user' add index 'uname' (title (10);-adds the prefix index SQL to create the INDEX of the person's name at 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;-The index is not used because function operations are used, and the principle is the same as above.
• SELECT * FROM 'houdunwang 'WHERE 'uname' LIKE' backing % '-go to index
• SELECT * FROM 'houdunwang 'WHERE 'uname' LIKE "% %"-No index left

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.

 

 

 

This article draws on the http://www.codeceo.com/article/mysql-index.html