Shulou(Shulou.com)06/01 Report--

First, the importance of the index

The index is used to quickly find rows that have a specific value in a column. Without using an index, MySQL must start with the first record and then read the entire table until it finds the relevant row. The bigger the watch, the more time it takes. If the query column in the table has an index, MySQL can quickly get to a location to find the middle of the data file, and there is no need to look at all the data. Note that sequential reads are much faster if you need to access most of the rows, because we avoid disk search at this time.

If you use Xinhua Dictionary to look up the Chinese character "Zhang" instead of using catalogs, you may have to find the last page from the first page of Xinhua Dictionary, which may take two hours. The thicker the dictionary, the more time you spend. Now you use the catalog to find the Chinese character "Zhang". The first letter of Zhang is a Chinese character that begins with zrecovery. starting from more than 900 pages, with this clue, it may only take you one minute to find a Chinese character, which shows the importance of the index. But is it better to build as many indexes as possible? of course not. If the catalogue of a book is divided into several levels, I think you will be dizzy, too.

Second, preparatory work

/ / prepare two test tables: mysql > CREATE TABLE `test_ t` (- > `id` int (11) NOT NULL auto_increment,-> `num` int (11) NOT NULL default '0mm,-> `d_ num` varchar (30) NOT NULL default' 0mm,-> PRIMARY KEY (`id`)->) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 Query OK, 0 rows affected (0.05sec) mysql > CREATE TABLE `test_ test` (- > `id` int (11) NOT NULL auto_increment,-> `num` int (11) NOT NULL default '0mm,-> PRIMARY KEY (`id`)->) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 Query OK, 0 rows affected (0.05sec) / / create a stored procedure for data insertion convenience mysql > delimiter | mysql > create procedure i_test (pa int (11), tab varchar (30))-> begin-> declare max_num int (11) default 1000000;-> 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

If max_num

Select cast (rand () * 100as 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

If max_num

Select cast (rand () * 100as 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 profiling executed is started. The default is +-- +-- + | 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 enabled, it is used to compare the execution time of indexed Query OK, 0 rows affected (0.00 sec)

Third, examples

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

Mysql > call i_test; / / insert 10 conditional Query OK into the test_t table, 1 row affected (0.02 sec) mysql > select num from test_t where numbing zero; mysql > explain select num from test_t where numbage zero\ 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 numbing 0; mysql > explain select num from test_t where numbing 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 pieces of data | 2 | 0.00026350 | select num from test_t where num numbers 0 | | 3 | 0.00022250 | explain select num from test_t where numm numbers 0 | | 4 | 0.18385400 | create index num_2 on test_t (num) | / / create an index | 5 | 0.00127525 | select num from test_t where numb numbers 0 | / / after using the index Almost 0.2x that of unused indexes | 6 | 0.00024375 | explain select num from test_t where numb indexes 0 | +-+ 6 rows in set (0.00 sec)

Explanation:

Id: query statements that indicate the order in which sql executes select_type:SIMPLE,PRIMARY,UNION,DEPENDENT UNION,UNION RESULT,SUBQUERY,DEPENDENT SUBQUERY,DERIVED will have different select_typetable: table name type: indicates the use of index types, or whether indexes are used or not. Efficiency is from high to low const, eq_reg, ref, range, index and ALL. In fact, this root is directly related to the way you write sql. For example, if you can use the primary key, use the primary key, and add the index to the condition after where. If it is unique plus a unique index such as possible_keys: possible index key: use index key_len: use index length ref: which column or constant to use with key to select rows from a table, usually in a multi-table federated query. Rows: number of rows found Extra: additional instructions

Some time ago, I wrote a blog post on which mysql distinct or group by is better, in which a friend left a message saying that the test results were different from the test results I did at that time. At that time, I explained, for example, how the index works more intuitively when I have time today.

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

3. Related fields should be indexed in multi-table operations such as federated query and subquery.

Mysql > call i_test; / / insert 10 pieces of data Query OK into test_test table, 1 row affected (0.02 sec) mysql > explain select a.num as num1,b.num as num2 from test_t as a left join test_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 (1080 sec) ERROR: No query specified

When the amount of data is very large, it is best not to use federated queries, even if you do an index.

The above is just a personal summary, throwing a brick to attract jade.

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.