MySQL index principle and query optimization detailed explanation

Index classification 1. Ordinary index index: accelerate search 2. Unique index primary key index: primary key: accelerate search + constraint (not empty and unique)
    Unique index: unique: speed up search + constraint (unique)
3. Combined index -primary key(id,name): Combined primary key index -unique(id,name): Combined unique index -index(id,name): Combined ordinary index 4. Full-text index fulltext: It is most effective when used to search for a very long article.
5. Spatial index: just understand it, almost never used

1 For example, you are making a membership card system for a shopping mall.
 2 
 3 This system has a member table 4 with the following fields:
 5 Member Number INT
 6 Member name VARCHAR(10)
 7 Member ID number VARCHAR(18)
 8 Member phone number VARCHAR(10)
 9 Member's address VARCHAR(50)
10 Member Remarks TEXT
11 
12 Then this membership number is used as the primary key, using PRIMARY
13 If you want to create an index for member names, then it is a normal INDEX
14 If you want to create an index for the member ID number, you can choose UNIQUE (unique, no duplication allowed)
15 
16 #In addition, there is a full-text index, namely FULLTEXT
17 Member notes information. If you need to create an index, you can choose full-text search.
18 works best when searching very long articles.
19 is used for shorter texts. If it is just one or two lines, a normal INDEX will also work.
20 But in fact, for full-text search, we will not use the index that comes with MySQL, but will choose third-party software such as Sphinx, which is specifically for full-text search.
twenty one 
22 #Other indexes such as spatial index SPATIAL, just understand them, there are almost no application scenarios where each index is not used

#When creating the above index, we can specify the index type for it. There are two types of hash type indexes: fast single query and slow range query. Btree type index: B+ tree, the more layers, the exponential growth of data volume (we use it because InnoDB supports it by default)
#Different storage engines support different index types. InnoDB supports transactions, row-level locking, B-tree, Full-text and other indexes, but does not support Hash indexes.
MyISAM does not support transactions, but supports table-level locking, B-tree, Full-text and other indexes, but does not support Hash indexes;
Memory does not support transactions, but supports table-level locking, B-tree, Hash and other indexes, but does not support Full-text indexes;
NDB supports transactions, row-level locking, and hash indexes, but does not support B-tree, Full-text, and other indexes.
Archive does not support transactions, but supports table-level locking. It does not support B-tree, Hash, Full-text, and other indexes.

1 #Method 1: When creating a table 2 CREATE TABLE table name (
 3 Field Name 1 Data Type [Integrity Constraints…],
 4 Field Name 2 Data Type [Integrity Constraints…],
 5 [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
 6 [index name] (field name [(length)] [ASC | DESC]) 
 7 );
 8 
 9 
10 #Method 2: CREATE creates an index on an existing table 11 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX index name 12 ON table name (field name [(length)] [ASC | DESC]);
13 
14 
15 #Method 3: ALTER TABLE Create an index on an existing table 16 ALTER TABLE table name ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
17 index name (field name [(length)] [ASC | DESC]);
18                              
19 #Delete index: DROP INDEX index name ON table name;
Syntax for creating/deleting an index

Make good use of the help document help create
help create index
==================
1. Create an index - create it when creating the table (a few points to note)
    create table s1(
    id int, #You can add primary key here
    #id int index #You cannot add an index like this, because index is just an index, not a constraint.
    #You cannot add an index name char(20) when defining a field, like a primary key or unique constraint.
    age int,
    email varchar(30)
    #primary key(id) #You can also add index(id) #You can add it like this);
    -After creating the table, create index name on s1(name); #Add a common indexcreate unique age on s1(age);Add a unique indexalter table s1 add primary key(id); #Add a housing and construction index, that is, add a primary key constraint to the id fieldcreate index name on s1(id,name); #Add a common joint index2. Delete the indexdrop index id on s1;
    drop index name on s1; #Delete a common indexdrop index age on s1; #Delete a unique index, just like a common index, you don't need to add unique in front of index to delete it, you can delete it directlyalter table s1 drop primary key; #Delete the primary key (because it was added according to alter, so we also use alter to delete it)

#1. Prepare table create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. Create a stored procedure to insert records in batches delimiter $$ #Declare the end symbol of the stored procedure to be $$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,concat('egon',i),'male',concat('egon',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$end delimiter; #Re-declare the semicolon as the end symbol#3. View the stored procedure show create procedure auto_insert1\G 
#4. Call the stored procedure call auto_insert1();

#No index: Scan from beginning to end, so the query speed is very slow mysql> select * from s1 where id=333;
+------+---------+--------+----------------+
| id | name | gender | email |
+------+---------+--------+----------------+
| 333 | egon333 | male | [email protected] |
| 333 | egon333 | f | alex333@oldboy |
| 333 | egon333 | f | alex333@oldboy |
+------+---------+--------+----------------+
rows in set (0.32 sec)
mysql> select * from s1 where email='egon333@oldboy';
....
... rows in set (0.36 sec)

#1. Be sure to create an index for the search condition field, for example, select * from t1 where age > 5; then you need to add an index for age. #2. If there is already a large amount of data in the table, creating an index will be very slow and take up hard disk space. Insertion, deletion and update are all very slow. Only query is fast. For example, create index idx on s1(id); will scan all the data in the table, and then use id as the data item to create an index structure and store it in the table on the hard disk.
After the creation, the query will be very fast #3. It should be noted that the index of the innodb table will be stored in the s1.ibd file, while the index of the myisam table will have a separate index file table1.MYI

#Analysis select * from s1 where id=123;
The SQL hits the index but does not cover it.
Use id=123 to locate the location of the id in the hard disk, or in the data table, in the index data structure.
However, the field we selected is *, and we need other fields besides id, which means that it is not enough to get the id through the index structure.
We also need to use the id to find the other field values ​​of the row where the id is located, which takes time. Obviously, if we only select the id,
This trouble is eliminated, as follows: select id from s1 where id=123;
This is a covering index. The index is hit, and the address of the id on the hard disk is directly obtained from the index data structure. The speed is very fast.

#Index merging: Merge multiple single-column indexes and use #Analysis:
We can use index merging to solve everything that combined indexes can do, such as create index ne on s1(name,email);#Combined index We can create indexes for name and email separately. Combined indexes can hit:
select * from s1 where name='egon';
select * from s1 where name='egon' and email='adf';
Index merge can hit:
select * from s1 where name='egon';
select * from s1 where email='adf';
select * from s1 where name='egon' and email='adf';
At first glance, it seems that index merging is better: it can hit more cases, but in fact it depends on the case. If name='egon' and email='adf',
Then the efficiency of combined index is higher than that of index merge. If it is a single condition query, it is more reasonable to use index merge.

#1. The leftmost prefix matching principle is a very important principle.
create index ix_name_email on s1(name,email,)
- Leftmost prefix match: must match from left to right select * from s1 where name='egon'; #ok select * from s1 where name='egon' and email='asdf'; #ok select * from s1 where email='[email protected]'; #No MySQL will keep matching to the right until it encounters a range query (>, <, between, like) and stops matching.
For example, if a = 1 and b = 2 and c > 3 and d = 4, then if we create an index in the order (a, b, c, d),
There is no need for an index for d. If you create an index of (a,b,d,c), it can all be used. The order of a,b,d can be adjusted arbitrarily.
#2.= and in can be in any order, for example, a = 1 and b = 2 and c = 3. You can create an (a,b,c) index in any order. The MySQL query optimizer will help you optimize it into a form that the index can recognize. #3. Try to choose a column with high discrimination as the index. The formula for discrimination is count(distinct col)/count(*).
Indicates the proportion of fields that are not repeated. The larger the proportion, the fewer records we scan. The unique key has a discrimination of 1, while some states,
The gender field may have a discrimination value of 0 in the face of big data, so someone may ask, is there any empirical value for this ratio? Different usage scenarios,
This value is also difficult to determine. Generally, we require the fields that need to be joined to be above 0.1, that is, an average of 10 records are scanned for 1 record. #4. Index columns cannot be used in calculations. Keep the columns "clean", such as from_unixtime(create_time) = '2014-05-29'
The index cannot be used. The reason is very simple. The B+ tree stores the field values ​​in the data table.
However, when searching, the function needs to be applied to all elements for comparison, which is obviously too costly.
So the statement should be written as create_time = unix_timestamp('2014-05-29');

mysql> select * from s1 where id>3 and name='egon' and email='[email protected]' and gender='male';
Empty set (0.39 sec)
mysql> create index idx on s1(id,name,email,gender); #Leftmost prefix not followed Query OK, 0 rows affected (15.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from s1 where id>3 and name='egon' and email='[email protected]' and gender='male';
Empty set (0.43 sec)

mysql> drop index idx on s1;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index idx on s1(name,email,gender,id); #Follow the leftmost prefix Query OK, 0 rows affected (15.97 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from s1 where id>3 and name='egon' and email='[email protected]' and gender='male';
Empty set (0.03 sec)

1 6. Leftmost prefix matches 2 index(id,age,email,name)
 3 #id must appear in the condition (as long as id appears, the speed will be improved)
 4 id
 5 id age
 6 id email
 7 id name
 8 
 9 email #No, if this is the beginning, the speed will not be improved. 10 mysql> select count(*) from s1 where id=3000;
11 +---------+
12 | count(*) |
13 +---------+
14 | 1 |
15 +----------+
16 1 row in set (0.11 sec)
17 
18 mysql> create index xxx on s1(id,name,age,email);
19 Query OK, 0 rows affected (6.44 sec)
20 Records: 0 Duplicates: 0 Warnings: 0
twenty one 
22 mysql> select count(*) from s1 where id=3000;
23 +---------+
24 | count(*) |
25 +---------+
26 | 1 |
27 +----------+
28 1 row in set (0.00 sec)
29 
30 mysql> select count(*) from s1 where name='egon';
31 +---------+
32 | count(*) |
33 +---------+
34 | 299999 |
35 +---------+
36 1 row in set (0.16 sec)
37 
38 mysql> select count(*) from s1 where email='[email protected]';
39 +---------+
40 | count(*) |
41 +---------+
42 | 1 |
43 +---------+
44 1 row in set (0.15 sec)
45 
46 mysql> select count(*) from s1 where id=1000 and email='[email protected]';
47 +----------+
48 | count(*) |
49 +---------+
50 | 0 |
51 +----------+
52 1 row in set (0.00 sec)
53 
54 mysql> select count(*) from s1 where email='[email protected]' and id=3000;
55 +---------+
56 | count(*) |
57 +---------+
58 | 0 |
59 +----------+
60 1 row in set (0.00 sec)
Create a joint index, leftmost match

- like '%xx'
    select * from tb1 where email like '%cn';
    
- Use function select * from tb1 where reverse(email) = 'wupeiqi';
    
- or
    select * from tb1 where nid = 1 or name = '[email protected]';
    
    Special: It will be invalid only when there is a column that is not indexed in the or condition. The following will use the index select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or name = '[email protected]' and email = 'alex'
            
- Type inconsistency If the column is a string type, the input condition must be enclosed in quotation marks, otherwise...
    select * from tb1 where email = 999;
Normal index does not mean that the index will not be used - !=
    select * from tb1 where email != 'alex'
    Special: If it is a primary key, the index will still be used select * from tb1 where nid != 123
->
    select * from tb1 where email > 'alex'
    
    Special: If the primary key or index is an integer type, the index will still be used select * from tb1 where nid > 123
        select * from tb1 where num > 123
        
#If the sorting condition is an index, the select field must also be an index field, otherwise it will not be hit - order by
    select name from s1 order by email desc;
    When sorting by index, if the selected query field is not an index, the index will not be used. select email from s1 order by email desc;
    Special: If the primary key is sorted, the index is still used:
        select * from tb1 order by nid desc;
- The leftmost prefix of the combined index If the combined index is: (name, email)
    name and email -- use index name -- use index email -- do not use index - count(1) or count(column) instead of count(*) makes no difference in mysql - create index xxxx on tb(title(19)) #text type, length must be specified

- Avoid using select *
- count(1) or count(column) instead of count(*)
- When creating a table, try to use char instead of varchar
- The order of the fields in the table is fixed-length fields first - Composite indexes instead of multiple single-column indexes (when multiple conditions are often used for query)
- Use short indexes whenever possible - Use JOINs instead of sub-queries
- When joining tables, make sure the condition type is consistent - Index hash values ​​(with few duplicates) are not suitable for indexing, for example: gender is not suitable

0. Run first to see if it is really slow, and set SQL_NO_CACHE
1. Where condition single table query, lock the minimum return record table. This sentence means to apply the where clause of the query statement to the table with the smallest number of records returned, and start the query. Query each field of the table separately to see which field has the highest discrimination. 2. Explain to check whether the execution plan is consistent with the expectation in 1 (start the query from the table with fewer locked records).
3. Use the SQL statement of the form of order by limit to prioritize the sorted table. 4. Understand the usage scenarios of the business side. 5. Refer to the major principles of index creation when adding indexes. 6. Observe the results. If they do not meet expectations, continue to analyze from 0.