Three principles for adding mysql indexes _ MySQL

Three principles for adding mysql indexes I. importance of indexes

The index is used to quickly find rows with a specific value in a column. If no index is used, MySQL must start with 1st records and read the entire table until related rows are found. The larger the table, the more time it takes. If the column to be queried in the table has an index, MySQL can quickly find the data file in the middle of a location, and there is no need to read all the data. Note that if you want to access most rows, sequential reading is much faster, because disk search is avoided.

If you use the Xinhua dictionary to find the Chinese character "Zhang" without using a directory, it may take two hours to find the last page from the first page of the Xinhua Dictionary. The thicker the dictionary, the more time you spend. Now you can use the directory to search for the Chinese character "Zhang". The first letter of the Chinese character is z. The Chinese character starting with z starts from more than 900 pages. with this clue, it may take one minute to search for a Chinese character, this shows the importance of indexes. But is the index created more and better? of course not. if the directory of a book is divided into several levels, I think you will also be dizzy.

2. preparations

 
 

  
  
// Prepare two test tables
  
  
Mysql> create table 'test _ t '(
  
  
-> 'Id' int (11) not null auto_increment,
  
  
-> 'Num' int (11) not null default '0 ',
  
  
-> 'D _ num' varchar (30) 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 (11) not null auto_increment,
  
  
-> 'Num' int (11) 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 to facilitate data insertion
  
  
Mysql> delimiter |
  
  
Mysql> create procedure I _test (pa int (11), tab varchar (30 ))
  
  
-> Begin
  
  
-> Declare max_num int (11) default 100000;
  
  
-> Declare I int default 0;
  
  
-> Declare rand_num int;
  
  
-> Declare double_num char;
  
  
->
  
  
-> If tab! = 'Test _ test' then
  
  
-> Select count (id) into max_num from test_t;
  
  
-> While I <pa do
  
  
-& Gt; if max_num & lt; 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 max_num from test_test;
  
  
-> While I <pa do
  
  
-& Gt; if max_num & lt; 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 whether the profiling is enabled and disabled by default.
  
  
+ --------------------------- + ------- +
  
  
| 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 enabling, it is used to compare the execution time after the index is added.
  
  
Query OK, 0 rows affected (0.00 sec)
 
 

III. instances

1. if there is too little data in a single table, indexing will affect the speed.

 
 

  
  
Mysql> call I _test (10, 'Test _ t'); // Insert 10 conditions to 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, 10 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 (10, 'Test _ t') | // Insert 10 data records
  
  
| 1 | 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 an index
  
  
| 5 | 0.00127525 | select num from test_t where num! = 0 | // after the index is used, it is about 0.2 times that no index is used.
  
  
| 6 | 0.00024375 | explain select num from test_t where num! = 0 |
  
  
+ ---------- + ------------ + ----------------------------------------------- +
  
  
6 rows in set (0.00 sec)
 
 

Explanation:

Id: the SQL execution sequence.

Select_type: SIMPLE, PRIMARY, UNION, dependent union, union result, SUBQUERY, dependent subquery, and DERIVED different query statements have different select_types.

Table: The name of the table to be searched.

Type: indicates whether the index type is used or whether the index is used. efficiency from high to low const, eq_reg, ref, range, index, and ALL. In fact, the root of your SQL statement has a direct relationship. for example, you can use the primary key to use the primary key, add the index to the condition after the where clause.

Possible_keys: possible indexes

Key: Use Index

Key_len: used index length

Ref: which column or constant is used together with the key to select rows from the table. this is generally used in multi-table joint queries.

Rows: number of lines found

Extra: additional instructions

I wrote a blog post about mysql distinct and group by some time ago. some friends left a message saying that the test results are different from the test results I did at the time. I explained it by analogy, today, we have time to express the indexing principles more intuitively in the context of examples.

2. when filtering conditions such as 'order by' and 'group by' after 'where', it is best to add an index to the following fields. Select indexes such as primary key, UNIQUE, and INDEX based on the actual situation, but the more indexes, the better.

3. join queries, subqueries, and other multi-table operations require indexed joined Fields

 
 

  
  
Mysql> call I _test (10, 'Test _ test'); // Insert 10 data records to 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:
  
  
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 database name, and 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 data size is very large, it is best not to use joint queries, even if you have performed an index.

The above is just a personal summary.