MYSQL INNODB 目录相关

2012-08-19

MYSQL INNODB 索引相关陆陆续续看了一些关于mysql innodb 引擎的文档，但是还是不能回答我自己如何根据查询

MYSQL INNODB 索引相关

陆陆续续看了一些关于mysql innodb 引擎的文档，但是还是不能回答我自己如何根据查询语言建立索引的问题，所以必须做一些小实验才能解开上述疑惑。

首先，总结下一些文档的内容:

innodb用的是b+ tree，PK索引树的叶节点是数据文件也就是record，而辅助索引（也就是非PK字段的索引的叶节点则是指向PK索引树的指针），从而达到加快查询的目的。索引并非一张表必然建立的，如果数据量较小的话，建议还是不采取建立索引，等查询速度变慢，再建立索引。

本文章的主要目的在于：

mysql innodb如何使用索引，即一条sql语句如何使用索引。

测试环境

OS：Linux debian 2.6.32-5-amd64 #1 SMP Wed May 18 23:13:22 UTC 2011 x86_64 GNU/Linux 虚拟机mysqladmin ?Ver 8.42 Distrib 5.1.49, for debian-linux-gnu on x86_64建立基础表结构

建立基础表

create table test ( col_pk int  primary key,col_index_1 int,col_index_compound_1 int,col_index_coumpount_2 int)engine=innodb,character set=gbk;

查看主键索引

 show indexes from test;

?写道

从上面可以看出innoDB选择的常量表进行查询，因为表中没有数据所以查询为不可能执行

然后我们插入数据：

insert into test values(1,1,1,1);insert into test values(2,1,1,1);--再次查询 explain select * from test where col_pk = 1;+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+|  1 | SIMPLE      | test  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+1 row in set (0.00 sec)

插入数据后我们可以看出，这个查询会使用索引col_pk

接着我们在test建立索引col_index_1和（col_index_compound_1和col_index_compound_2）的索引

create index index_col_index_1 on test(col_index_1);create index index_col_index_compound_12 on test(col_index_compound_1,col_index_coumpount_2);mysql> show indexes from test;+-------+------------+-----------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name                    | Seq_in_index | Column_name           | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+-------+------------+-----------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+| test  |          0 | PRIMARY                     |            1 | col_pk                | A         |           2 |     NULL | NULL   |      | BTREE      |         || test  |          1 | index_col_index_1           |            1 | col_index_1           | A         |           2 |     NULL | NULL   | YES  | BTREE      |         || test  |          1 | index_col_index_compound_12 |            1 | col_index_compound_1  | A         |           2 |     NULL | NULL   | YES  | BTREE      |         || test  |          1 | index_col_index_compound_12 |            2 | col_index_coumpount_2 | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |+-------+------------+-----------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+4 rows in set (0.00 sec)

可以从上面看出，我们已经拥有了3个索引，然后进行我们的查询

mysql> explain select * from test where col_pk = 1;+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+|  1 | SIMPLE      | test  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+1 row in set (0.00 sec)mysql> explain select * from test where col_pk = 1 and col_index_1=1;+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+| id | select_type | table | type  | possible_keys             | key     | key_len | ref   | rows | Extra |+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+|  1 | SIMPLE      | test  | const | PRIMARY,index_col_index_1 | PRIMARY | 4       | const |    1 |       |+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+1 row in set (0.00 sec)

?从上面两个对比中发现只有有pk作为select的对象则，一定会使用pk索引

mysql> explain select * from test where col_index_1=1 order by col_pk;+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra       |+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+|  1 | SIMPLE      | test  | ref  | index_col_index_1 | index_col_index_1 | 5       | const |    1 | Using where |+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from test order by col_pk;+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+|  1 | SIMPLE      | test  | index | NULL          | PRIMARY | 4       | NULL |    2 |       |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+1 row in set (0.00 sec)

?上述例子则说明where 的过滤条件会优于ordery by

mysql> explain select * from test where col_pk = 1 or col_index_1=1;+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys             | key  | key_len | ref  | rows | Extra       |+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+|  1 | SIMPLE      | test  | ALL  | PRIMARY,index_col_index_1 | NULL | NULL    | NULL |    2 | Using where |+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from test where col_index_1=1 or col_pk =1;+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys             | key  | key_len | ref  | rows | Extra       |+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+|  1 | SIMPLE      | test  | ALL  | PRIMARY,index_col_index_1 | NULL | NULL    | NULL |    2 | Using where |+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+1 row in set (0.00 sec)

?上述例子说明两个问题:

where条件的前后顺序不影响索引的选择or关键字会导致扫描所有记录

mysql> explain select * from test where col_index_compound_1=1 and col_pk =1;+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+| id | select_type | table | type  | possible_keys                       | key     | key_len | ref   | rows | Extra |+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+|  1 | SIMPLE      | test  | const | PRIMARY,index_col_index_compound_12 | PRIMARY | 4       | const |    1 |       |+----+-------------+-------+-------+-------------------------------------+---------+---------+-------+------+-------+1 row in set (0.00 sec)mysql> explain select * from test where col_index_1=1 and col_pk =1;+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+| id | select_type | table | type  | possible_keys             | key     | key_len | ref   | rows | Extra |+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+|  1 | SIMPLE      | test  | const | PRIMARY,index_col_index_1 | PRIMARY | 4       | const |    1 |       |+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+1 row in set (0.00 sec)

?以上例子说明，and的情况下，主键优先

mysql> explain select * from test where col_index_compound_1=2 and col_index_1 =1;+----+-------------+-------+------+-----------------------------------------------+-------------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys                                 | key               | key_len | ref   | rows | Extra       |+----+-------------+-------+------+-----------------------------------------------+-------------------+---------+-------+------+-------------+|  1 | SIMPLE      | test  | ref  | index_col_index_1,index_col_index_compound_12 | index_col_index_1 | 5       | const |    1 | Using where |+----+-------------+-------+------+-----------------------------------------------+-------------------+---------+-------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from test where col_index_compound_1=2 and col_index_1 =1 and col_index_coumpount_2=1;+----+-------------+-------+------+-----------------------------------------------+-------------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys                                 | key               | key_len | ref   | rows | Extra       |+----+-------------+-------+------+-----------------------------------------------+-------------------+---------+-------+------+-------------+|  1 | SIMPLE      | test  | ref  | index_col_index_1,index_col_index_compound_12 | index_col_index_1 | 5       | const |    1 | Using where |+----+-------------+-------+------+-----------------------------------------------+-------------------+---------+-------+------+-------------+1 row in set (0.00 sec)

?上述例子说明，当联合索引和单索引没有交集的时候，单个索引会优先考虑
建立新索引 index_col_index_1_and_compound_1?create index index_col_index_1_and_compound_1 on test(col_index_1,col_index_compound_1);

mysql> explain select * from test where col_index_1=1 and col_index_compound_1=1;+----+-------------+-------+------+--------------------------------------------+-------------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys                                                                  | key               | key_len | ref   | rows | Extra       |+----+-------------+-------+------+--------------------------------------------+-------------------+---------+-------+------+-------------+|  1 | SIMPLE      | test  | ref  | index_col_index_1,index_col_index_compound_12,index_col_index_1_and_compound_1 | index_col_index_1 | 5       | const |    1 | Using where |+----+-------------+-------+------+--------------------------------------------+-------------------+---------+-------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from test where col_index_compound_1=2 and col_index_1 =1;+----+-------------+-------+------+--------------------------------------------+-------------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys                                                                  | key               | key_len | ref   | rows | Extra       |+----+-------------+-------+------+--------------------------------------------+-------------------+---------+-------+------+-------------+|  1 | SIMPLE      | test  | ref  | index_col_index_1,index_col_index_compound_12,index_col_index_1_and_compound_1 | index_col_index_1 | 5       | const |    1 | Using where |+----+-------------+-------+------+--------------------------------------------+-------------------+---------+-------+------+-------------+1 row in set (0.00 sec)

??从上述看出，单索引还是会优先选择的

总结：

主键是最优选择单索引会优先选择

热点排行

其他数据库

MYSQL INNODB 目录相关