Mysql increase the index of the three principles

First, the importance of the index

Indexes are used to quickly find lines that have a specific value in a column. Instead of using an index, MySQL must start with the first record and then read the entire table until it finds the relevant row. The larger the table, the more time it takes. If the table in the query column index, MySQL can quickly reach a location to search the middle of the data file, there is no need to see all the data. Note that if you need to access most of the rows, sequential reads are much faster since we avoid disk searches.

If you use Xinhua Dictionary to find "Zhang" the Chinese characters, do not use the directory, you may want to find the last page from Xinhua Dictionary page, it may take two hours. The thicker the dictionary, the more time you spend. Now you use the directory to find the Chinese character "Zhang". The first letter of the Zhang is z. The Chinese character beginning with z starts with more than 900 pages. With this clue, you can find a Chinese character in just one minute. This shows the importance of the index Sex. However, the index is not built as possible, of course not, of course not, if a book directory is divided into several levels, I think you will dizzy.

Second, the preparatory work

// 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 the insertion of data mysql> delimiter mysql> create procedure i_test (pa int (11), tab varchar (30) -> declare double_num char; -> -> if tab! = 'test_test' then -> select count () select random (rand () * 100 as unsigned) into rand_num; -> select concat (rand_num, rand_num) into double_num; -> while i <pa do -> if max_num <100000 then -> select cast > 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%"; , Record the implementation of the profiling is not Started, 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; / / open, in order to compare the index after the execution time Query OK, 0 rows affected (0.00 sec)

Third, examples

1, too little single table data, the index will affect the speed

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! = 0G; *************************** 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 Query OK, 10 rows affected (0.19 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> select num from test_t where num! = 0; mysql> create index num_2 on test_t (num) explain select num from test_t where num! = 0G; *************************** 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 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 | // use the index, almost no use of the index 0.2 times | 6 | 0.00024375 | explain select num from test_t where num! = 0 | + --------- - + ------------ + ----------------------------------- --------- - + 6 rows in set (0.00 sec)

Explanation:

id: that the order of implementation of sql

select_type: SIMPLE, PRIMARY, UNION, DEPENDENT UNION, UNION RESULT, SUBQUERY, DEPENDENT SUBQUERY, DERIVED different query will have different select_type

table: Find the name of the table

type: that the use of index types, or whether the use of index efficiency from high to low const, eq_reg, ref, range, index and ALL, in fact, the root of your sql direct relationship, for example: where the conditions behind the index, if it is the only plus unique index

possible_keys: Possible indexes

key: use index

key_len: use the length of the index

ref: which column or constant to use together with the key to select the row from the table, usually in multi-table joint query.

Rows: Find out the number of rows

Extra: additional instructions

Some time ago wrote a post mysql distinct and group by who better, there are friends leave a message, said the test results I did when the test results are not the same, when I played like an explanation, there is time today, with examples of the situation , A more intuitive expression of the working principle of the index.

2, where after the conditions, order by, group by, etc. This filter, the back of the field with the best index. According to the actual situation, select PRIMARY KEY, UNIQUE, INDEX index, but not the more the better, to be moderate.

3, joint queries, subqueries and other multi-table operations related fields to be indexed

mysql> call i_test (10, 'test_test'); // insert 10 data into 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.numG; *************************** 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 test_t One field rows: 1080 Extra: Using index 2 rows in set (0.01 sec) ERROR: No query specified

When the amount of data is particularly large, it is best not to use the union query, even if you indexed.

The above is only a personal summary of what to start.