Tutorial _ MySQL

This article mainly introduces the MySQL joint index learning tutorial, which talks about the optimization of the Union index on sorting and other knowledge points. if you need a friend, you can refer to the joint index, also known as composite index. For Composite Index: Mysql uses the fields in the index from left to right. one query can only use one part of the index, but only the leftmost part. For example, the index is key index (a, B, c ). supports a | a, B | a, B, and c combinations for search, but does not support search by B and c. when the leftmost field is a constant reference, the index is very effective.

The index of two or more columns is called a composite index.
You can narrow the search range by using the additional columns in the index, but using an index with two columns is different from using two separate indexes. The structure of the composite index is similar to that of the phone book. the name of a person consists of a surname and a name. The phone book is first sorted by the last name, and then sorted by the name of the person with the same surname. If you know the last name, the phone book will be very useful; if you know the last name and the first name, the phone book will be more useful, but if you only know the first name but not the last name, the phone book will be useless.
Therefore, when creating a composite index, you should carefully consider the column sequence. Composite indexes are useful when you search all the columns in an index or only the first few columns. Composite indexes are useless when you only search for any of the following columns.
For example, create a compound index of name, age, and gender.

create table test(a int,b int,c int,KEY a(a,b,c));

Composite index creation principles:

If you are likely to search for only one column multiple times, this column should be the first column in the composite index. If you are likely to perform a separate search for two columns in one or two column indexes, you should create another index that only contains the second column.
As shown in, if you need to query age and gender, you should create a new compound index containing age and gender.
Primary keys that contain multiple columns will always automatically create indexes in the form of Composite Indexes. the column sequence is the sequence in which they appear in the table definition, rather than the sequence specified in the primary key definition. In consideration of the future search performed by the primary key, determine which column should be at the top.
Note that creating a composite index should contain a few columns, which are often used in select queries. Adding too many columns to a composite index does not bring too many benefits. In addition, a considerable amount of memory is used to store the values of composite index columns, resulting in memory overflow and performance degradation.

Composite index sorting optimization:

Composite indexes are optimized only for order by statements with the same or opposite sorting as the indexes.
When creating a composite index, each column defines the ascending or descending order. For example, define a composite index:

CREATE INDEX idx_example  ON table1 (col1 ASC, col2 DESC, col3 ASC) 

Three columns are respectively: col1 in ascending order, col2 in descending order, and col3 in ascending order. Now, if we execute two queries
1:

Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC

Same as index order
2:

Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC 

Opposite to index order
You can choose not to optimize composite indexes for query 1 and 2.
If the query is:

Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC

The sorting result is completely different from the index, and the query will not be optimized by the composite index.

The function of the query optimizer in the where query:

If a multi-column index exists on Col1 and Col2, the following statement is used: select * from table where col1 = val1 AND col2 = val2 the query optimizer will try to determine which index will find fewer rows. Then use the obtained index to take the value.
1. if a multi-column index exists, any leftmost index prefix can be used by the optimizer. Therefore, the order of the combined index is different, which affects the selection of the index. try to put less values in front.
For example, the index of one or more columns is (col1, col2, col3)
In this case, the search for indexes in columns (col1), (col1 col2), and (col1 col2 col3) will apply.

SELECT * FROM tb WHERE col1 = val1 SELECT * FROM tb WHERE col1 = val1 and col2 = val2 SELECT * FROM tb WHERE col1 = val1 and col2 = val2 AND col3 = val3 

2. if the column does not constitute the leftmost prefix of the index, the created index does not work.
For example:

SELECT * FROM tb WHERE col3 = val3 SELECT * FROM tb WHERE col2 = val2 SELECT * FROM tb WHERE col2 = val2 and col3=val3 

3. if the query condition of a Like statement does not start with a wildcard, the index is used.
For example, % car or % car % does not use an index.
Che % uses the index.
Disadvantages of indexing:
1. occupied disk space.
2. added the insert and delete operations. The more indexes a table has, the slower the insert and delete operations. For example, you cannot create too many indexes for a system that requires quick entry.

Below are some common index restrictions

1. use non-equals operators (<> ,! =)
In this case, even if the dept_id column has an index, the query statement still performs a full table scan.
Select * from dept where staff_num <> 1000;
But such a query is indeed needed during development. Is there no way to solve the problem?
Yes!
By replacing the non-equal sign with the or syntax for the query, you can use the index to avoid full table scanning: the above statement is changed to the following, you can use the index.

select * from dept shere staff_num < 1000 or dept_id > 1000; 

2. use is null or is not null
Using is null or is nuo null also limits the use of indexes, because the database does not define a null value. If the indexed column contains many null values, this index will not be used (unless the index is a bitmap index, which will be explained in detail in future blog articles ). Using null in SQL statements can cause a lot of trouble.
The solution is to define the columns to be indexed as non-null (not null) during table creation)

3. use functions
If function-based indexes are not used, the optimizer will ignore these indexes when using functions for columns with indexes in the where clause. The following query will not use an index:

select * from staff where trunc(birthdate) = '01-MAY-82'; 

However, if you apply a function to a condition, the index takes effect. if you change the preceding statement to the following statement, you can search through the index.

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999); 

4. unmatched data types
Unmatched data types are also one of the performance issues that are difficult to find.
In the following example, dept_id is a varchar2 field with an index, but the following statement performs a full table scan.

select * from dept where dept_id = 900198; 

This is because oracle will automatically convert the where clause to to_number (dept_id) = 900198, which is what 3 says, thus limiting the use of indexes.
You can use indexes by changing the SQL statement to the following format:

select * from dept where dept_id = '900198'; 

Well, note the following:

For example, if you have an article table, you need to display the list in reverse chronological order under a certain category:

 SELECT * FROM articles WHERE category_id = ... ORDER BY created DESC LIMIT ...

This kind of query is very common, basically no matter what application can find the explain to put similar SQL, academic readers see the above SQL, may say SELECT * is not good, to query only the required fields, simply change the SQL statement to the following format:

SELECT id FROM articles WHERE category_id = ... ORDER BY created DESC LIMIT ...

Let's assume that the id here is the primary key. As for the specific content of the article, it can be saved to a key-value type cache such as memcached. as a result, readers of the academic school should be able to pick out nothing wrong. next we will consider how to build an index based on this SQL statement:

Regardless of special circumstances such as data distribution, any qualified WEB developer knows that such an SQL statement should create a "category_id, created" compound index, but this is the best answer? No. now it's time to look back at the title: the database engine type should be taken into account for index creation in MySQL!

If our database engine is InnoDB, "category_id, created" composite index is the best answer. Let's take a look at the index structure of InnoDB. in InnoDB, the index structure has a special place: non-primary key indexes will save the value of the corresponding primary key on the leaf node of its BTree, the most direct advantage of this is Covering Index, which can be obtained directly in the Index without getting the id value in the data file.

If our database engine is MyISAM, creating a "category_id, created" compound index is not the best answer. Because in the Index structure of MyISAM, the non-primary key Index does not save the value of the corresponding primary key. to use the Covering Index, you should create a composite Index "category_id, created, id.

I should understand what I mean when I finish the failover. I hope that you will be able to think more comprehensively when considering indexes in the future. there are still many similar problems in practical application, for example, most people do not use Cardinality (show index from... can see this parameter). Cardinality indicates the number of unique values. generally, if the proportion of unique values in the total number of rows is less than 20%, cardinality is considered to be too small. in this case, the index will not play a major role in select except for the slow insert, update, and delete speeds; another detail is that the character set is not taken into account when indexing. for example, for the username field, if only English or underline characters are allowed, do not use the gbk or UTF-8 character sets, you should use a simple character set such as latin1 or ascii. The index file will be much smaller and the speed will naturally be much faster. I will not talk about these details.