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;
?写道
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)
?上述例子说明两个问题:
?
- 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)
??从上述看出，单索引还是会优先选择的
?
总结：
- 主键是最优选择单索引会优先选择