Get a brief understanding of the three principles for adding mysql indexes

1. Importance of Index

Indexes are used to quickly find rows with a specific value of 1 in a column. Without using indexes, MySQL must start with record 1 and read the entire table until it finds the relevant row. The larger the table, the more time it takes. If the column queried in the table has 1 index, MySQL can quickly reach 1 position to search in the middle of the data file, without looking at all the data. Note that if you need to access most rows, sequential reading is much faster, because we avoid disk search at this time.

If you use Xinhua Dictionary to find the Chinese character "Zhang" instead of using the catalogue, you may have to find the last page from the first page of Xinhua Dictionary, which may take 2 hours. The thicker the dictionary, the more time you spend. Now you use the directory to find the Chinese character "Zhang". The initials of Zhang are z, and the Chinese characters beginning with z start from more than 900 pages. With this clue, it may take you only one minute to find a Chinese character, which shows the importance of index. But is it better to build more indexes? Of course not. If the catalogue of a book is divided into several levels, I think you will be dizzy.

2. Preparations

// Prepare 2 Test sheet  
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 1 Stored procedures, convenient for inserting data  
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 
-> 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%"; // View 1 Under, record the executed profiling Is it started? It is not started 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; // When turned on, it is to compare the execution time after adding index  
Query OK, 0 rows affected (0.00 sec) 

3. Examples

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

mysql> call i_test(10,'test_t'); // Toward test_t Table insertion 10 Condition  
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 Bar 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 an index  
| 5 | 0.00127525 | select num from test_t where num!=0 | // After using the index, almost no index is used 0.2 Times  
| 6 | 0.00024375 | explain select num from test_t where num!=0 | 
+----------+------------+---------------------------------------------+ 
6 rows in set (0.00 sec) 

Explanation:

id: Indicates the order in which sql is executed 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 the table to look up type: Indicates the use of index type, or the use of index. Efficiency from high to low const, eq_reg, ref, range, index and ALL, in fact, this root of your sql writing directly related, for example: can use the primary key, where after the conditions plus index, if only 1 plus only 1 index, etc possible_keys: Possible indexes key: Using indexes key_len: Use the length of the index ref: Which column or constant to use to select rows from a table from key1, as is common in multi-table union queries. rows: Number of rows found Extra: Additional instructions

Some time ago, I wrote a blog post mysql distinct or group by, which is better. There was a friend's message saying that the test results were different from the test results I did at that time. At that time, I explained it analogically. Today, I have time to express the working principle of the index more intuitively with examples.

2. When filtering the conditions after where, order by, group by, etc., it is better to add indexes to the following fields. According to the actual situation, choose PRIMARY KEY, UNIQUE, INDEX and other indexes, but the more the better, the moderate.

3. The related fields should be indexed during multi-table operations such as joint query and subquery

mysql> call i_test(10,'test_test'); // Toward test_test Table insertion 10 Bar data  
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 Yes test_t Adj. 1 Fields  
rows: 1080 
Extra: Using index 
2 rows in set (0.01 sec) 
ERROR: 
No query specified 

When the amount of data is extremely large, it is best not to use union query, even if you do index.

The above is just a personal summary.