A brief understanding of the three principles of adding MySQL indexes

//Prepare two test tablesmysql> 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 insertionmysql> 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 
-> 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 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 whether profiling is enabled. It is 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 turning it on, it is to compare the execution time after adding the index. Query OK, 0 rows affected (0.00 sec)

mysql> call i_test(10,'test_t'); //Insert 10 conditions 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, 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 ten records | 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 | //After using the index, it is almost 0.2 times that of not using the index| 6 | 0.00024375 | explain select num from test_t where num!=0 | 
+----------+------------+---------------------------------------------+ 
6 rows in set (0.00 sec)

mysql> call i_test(10,'test_test'); //Insert 10 records 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 database name, a.num is a field of test_t rows: 1080 
Extra: Using index 
2 rows in set (0.01 sec) 
ERROR: 
No query specified