The selection of MySQL database Indexes

Database indexIt is like the directory part of a book, which is convenient and convenient for you to search for data in the database. It brings a lot of convenience to the database administrator's work. InMySQL databaseIn, the use of indexes is always correct.

Example of a simple table:

Create table 'r2 '(

Id'int (11) default null,

ID1 'int (11) default null,

CNAME 'varchar (32) default null,

KEY 'id1' ('id1 ')

) ENGINE = MyISAM default charset = latin1

Select count (*) FROM r2;

250001 (V1)

Select count (*) FROM r2 WHERE ID1 = 1;

83036 (V2)

(Execution time = 110 MS)

(ID1 = 1) conditional query indexes are optional V2/V1 = 0.3321 or 33.21%.

In general (for example, the book "SQL Tuning"), if you choose more than 20%, full table scanning is better than using indexes.

I know that Oracle always chooses full table scan when it chooses more than 25%.

What about MySQL:

Mysql> explain select count (SUBNAME) FROM r2 WHERE ID1 = 1;

+ ---- + ------------- + ------- + ------ + --------------- + -----

| Id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |

+ ---- + ------------- + ------- + ------ + --------------- + -----

| 1 | SIMPLE | t2 | ref | ID1 | ID1 | 5 | const | 81371 | using where |

+ ---- + ------------- + ------- + ------ + --------------- + -----

This means that MySQL will use indexes to complete this query.

Let's compare the execution time of index query and full table scan:

Select count (SUBNAME) FROM t2 WHERE ID1 = 1-410 MS

Select count (SUBNAME) FROM t2 ignore index (ID1) WHERE ID1 = 1-200 MS

As you can see, full table scan is twice faster.

Refer to the more special example: selectivity ~ 95%:

SELECT cnt2/cnt1 FROM (SELECT count (*) cnt1 FROM r2) d1, (SELECT count (*) cnt2 FROM r2 WHERE ID1 = 1) d2;

0.9492 = 94.92%;

MySQL will use indexes to complete the query.

Execution time:

Select count (SUBNAME) FROM t2 WHERE ID1 = 1-1200 MS

Select count (SUBNAME) FROM t2 ignore index (ID1) WHERE ID1 = 1-260 MS

This full table scan is 4.6 times faster.

Why does MySQL select index access query?

MySQL does not calculate the index selectivity, but only predicts the number of logical IO operations. In addition, the number of logical IO operations in the middle of our example is less than the full table scan.

Finally, we conclude that indexes should be used with caution because they cannot help all queries. Therefore, when using database indexes, you must make a decision based on the actual situation.