MYSQL INNODB 索引相关

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

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

 

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

 

本文章的主要目的在于：

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

 

测试环境

 

1. OS：Linux debian 2.6.32-5-amd64 #1 SMP Wed May 18 23:13:22 UTC 2011 x86_64 GNU/Linux 虚拟机
2. 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;

 写道

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 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 | 0 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

 

我们如下查询：

 

explain select * from test where col_pk = 1;

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

 

 

从上面可以看出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)

 上述例子说明两个问题:

 

1. where条件的前后顺序不影响索引的选择
2. 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)

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

 

总结：

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