3 principles for adding a MySQL index

One, the importance of indexing

Indexes are used to quickly find rows that have a specific value in a column. Without indexing, MySQL must start with the 1th record and read through the entire table until the relevant rows are found. The larger the table, the more time it takes. If the query column in the table has an index, MySQL can quickly reach a location to find the middle of the data file, there is no need to look at all the data. Note that if you need to access most rows, sequential reads are much faster because we avoid disk searches at this time.

If you use the Xinhua dictionary to find "Zhang" the Chinese character, do not use the table of contents, you may want to take the first page of the Xinhua dictionary to find the last page, it may cost two hours. The thicker the dictionary, the more time you spend. Now you use the table of contents to find "Zhang" the Chinese character, Zhang's first letter is z,z beginning of the Chinese characters from more than 900 pages, with this clue, you look for a Chinese character may only a minute, this shows the importance of the index. But the more the index is built, the better, of course not, if the catalogue of a book is divided into several levels, I think you will faint.

Ii. preparations for the work

 
 

  
  

  
Prepare two test sheets
  
  

  
mysql> CREATE TABLE ' test_t ' (
  
  

  
-> ' id ' int (one) not NULL auto_increment,
  
  

  
-> ' num ' int (one) not NULL default ' 0 ',
  
  

  
-> ' d_num ' varchar () not NULL default ' 0 ',
  
  

  
-> PRIMARY KEY (' id ')
  
  

  
->) Engine=myisam DEFAULT Charset=utf8 auto_increment=1;
  
  

  
Query OK, 0 rows affected (0.05 sec)
  
  

  

  
  

  
mysql> CREATE TABLE ' test_test ' (
  
  

  
-> ' id ' int (one) not NULL auto_increment,
  
  

  
-> ' num ' int (one) not NULL default ' 0 ',
  
  

  
-> PRIMARY KEY (' id ')
  
  

  
->) Engine=myisam DEFAULT Charset=utf8 auto_increment=1;
  
  

  
Query OK, 0 rows affected (0.05 sec)
  
  

  

  
  

  
Create a stored procedure that is convenient for inserting data
  
  

  
Mysql> delimiter
  
  

  
Mysql> CREATE PROCEDURE i_test (PA int (one), tab varchar (30))
  
  

  
-> begin
  
  

  
-> declare max_num int (one) default 100000;
  
  

  
-> declare i int default 0;
  
  

  
-> declare rand_num int;
  
  

  
-> declare Double_num char;
  
  

  
->
  
  

  
-> if tab!= ' Test_test ' then
  
  

  
-> select COUNT (ID) into the max_num from test_t;
  
  

  
-> while I < PA do
  
  

  
-> if Max_num < 100000 then
  
  

  
-> Select CAST (rand () *100 as unsigned) into rand_num;
  
  

  
-> Select Concat (rand_num,rand_num) into Double_num;
  
  

  
-> INSERT INTO test_t (num,d_num) values (rand_num,double_num);
  
  

  
-> End If;
  
  

  
-> Set i = i +1;
  
  

  
-> End While;
  
  

  
-> Else
  
  

  
-> select COUNT (ID) into the max_num from Test_test;
  
  

  
-> while I < PA do
  
  

  
-> if Max_num < 100000 then
  
  

  
-> Select CAST (rand () *100 as unsigned) into rand_num;
  
  

  
-> INSERT into test_test (num) values (rand_num);
  
  

  
-> End If;
  
  

  
-> Set i = i +1;
  
  

  
-> End While;
  
  

  
-> End If;
  
  

  
-> End
  
  

  
Query OK, 0 rows Affected (0.00 sec)
  
  

  

  
  

  
Mysql> delimiter;
  
  

  
Mysql> Show variables like "%pro%"; Check to see if the record execution of the profiling is not open, the default is not open
  
  

  
+---------------------------+-------+
  
  

  
Variable_name Value
  
  

  
+---------------------------+-------+
  
  

  
Profiling off
  
  

  
Profiling_history_size 15
  
  

  
Protocol_version 10
  
  

  
Slave_compressed_protocol off
  
  

  
+---------------------------+-------+
  
  

  
4 rows in Set (0.00 sec)
  
  

  

  
  

  
Mysql> set profiling=1; After opening, it is to compare the execution time after the index is added
  
  

  
Query OK, 0 rows Affected (0.00 sec)
 
 

Third, the example

1, single table data is too small, index will affect speed

 
 

  
  

  
Mysql> call I_test (' test_t '); Insert 10 pieces into the test_t table
  
  

  
Query OK, 1 row affected (0.02 sec)
  
  

  

  
  

  
Mysql> Select num from test_t where num!=0;
  
  

  
mysql> explain select num from test_t where num!=0\g;
  
  

  
1. Row ***************************
  
  

  
Id:1
  
  

  
Select_type:simple
  
  

  
table:test_t
  
  

  
Type:all
  
  

  
Possible_keys:null
  
  

  
Key:null
  
  

  
Key_len:null
  
  

  
Ref:null
  
  

  
Rows:10
  
  

  
Extra:using where
  
  

  
1 row in Set (0.00 sec)
  
  

  

  
  

  
ERROR:
  
  

  
No query specified
  
  

  

  
  

  
Mysql> CREATE index num_2 on test_t (num);
  
  

  
Query OK, rows affected (0.19 sec)
  
  

  
Records:10 duplicates:0 warnings:0
  
  

  

  
  

  
Mysql> Select num from test_t where num!=0;
  
  

  

  
  

  
mysql> explain select num from test_t where num!=0\g;
  
  

  
1. Row ***************************
  
  

  
Id:1
  
  

  
Select_type:simple
  
  

  
table:test_t
  
  

  
Type:index
  
  

  
Possible_keys:num_2
  
  

  
Key:num_2
  
  

  
Key_len:4
  
  

  
Ref:null
  
  

  
Rows:10
  
  

  
Extra:using where; Using Index
  
  

  
1 row in Set (0.00 sec)
  
  

  

  
  

  
ERROR:
  
  

  
No query specified
  
  

  

  
  

  
Mysql> Show Profiles;
  
  

  
+----------+------------+---------------------------------------------+
  
  

  
query_id Duration Query
  
  

  
+----------+------------+---------------------------------------------+
  
  

  
1 0.00286325 Call I_test (Ten, ' test_t ')//Insert 10 data
  
  

  
2 0.00026350 Select num from test_t where num!=0
  
  

  
3 0.00022250 explain select num from test_t where num!=0
  
  

  
4 0.18385400 CREATE index num_2 on test_t (num)//CREATE INDEX
  
  

  
5 0.00127525 Select num from test_t where num!=0///index is almost 0.2 times times less than the index used
  
  

  
6 0.00024375 explain select num from test_t where num!=0
  
  

  
+----------+------------+---------------------------------------------+
  
  

  
6 rows in Set (0.00 sec)
 
 

Explain:

ID: Indicates the order in which SQL executes

Select_type:simple,primary,union,dependent union,union result,subquery,dependent SUBQUERY, Derived different query statements will have different select_type

Table: Represents the name of a lookup

Type: The index type is used, or there is no index used. Efficiency from high to low const, EQ_REG, ref, range, index and all, in fact, this root of your SQL is directly related to the writing, for example: can use the primary key on the primary key, where the conditions after the index, If the only plus unique index and so on

Possible_keys: Possible indexes

Key: Using Indexes

Key_len: Using the length of the index

Ref: Use which column or constant to select rows from the table with the key, typically in a multiple-table federated query.

Rows: Number of rows found

Extra: Extra Notes

A while ago wrote a blog about MySQL distinct and group by who better, there are friends in the message, said the test results root I did the test results are different, at that time I explained that, today there is time, in the case of the situation, more intuitive expression of the working principle of the index.

After the 2,where condition, the order by, the group by and so on filter, the following fields preferably indexed. According to the actual situation, choose Primary Key, UNIQUE, index index, etc., but not the more the better, to moderate.

3, the Union query, subqueries and other table operations when the connected fields to be indexed

 
 

  
  

  
Mysql> call I_test (' test_test '); Insert 10 data into the Test_test table
  
  

  
Query OK, 1 row affected (0.02 sec)
  
  

  

  
  

  
Mysql> explain select A.num as num1,b.num as num2 from test_t as a-left join TES
  
  

  
T_test as B on a.num=b.num\g;
  
  

  
1. Row ***************************
  
  

  
Id:1
  
  

  
Select_type:simple
  
  

  
Table:a
  
  

  
Type:index
  
  

  
Possible_keys:null
  
  

  
Key:num_2
  
  

  
Key_len:4
  
  

  
Ref:null
  
  

  
Rows:10
  
  

  
Extra:using Index
  
  

  
2. Row ***************************
  
  

  
Id:1
  
  

  
Select_type:simple
  
  

  
Table:b
  
  

  
Type:ref
  
  

  
Possible_keys:num_1
  
  

  
Key:num_1
  
  

  
Key_len:4
  
  

  
Ref:bak_test.a.num//bak_test is the name of the database, A.num is a field of test_t
  
  

  
rows:1080
  
  

  
Extra:using Index
  
  

  
2 rows in Set (0.01 sec)
  
  

  

  
  

  
ERROR:
  
  

  
No query specified
 
 

When the volume of data is particularly large, it is best not to use federated queries, even if you have indexed them.

It's just a little bit of a personal summary.