每日MySQL之011：MySQL和DB2中的Multiple-Column Index（复合索引）

MySQL支持复合索引，也就是创建在多个列上的索引，一个索引最多支持16列。复合索引针对的查询包括：查询条件匹配索引所有列、查询条件只匹配第一列、查询条件只匹配前两列、查询条件只匹配前三列，以此类推（MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. ）。

如果一个表有multiple-column索引，那么只有索引的最左前缀(leftmost prefix )才能被优化器使用。例如，有一个3列的索引在(col1, col2, col3)，只有(col1), (col1, col2), 和 (col1, col2, col3) 才能走索引。

假如有以下表和索引的定义，索引testidx建立在col2列和col3列上：
db2 "create table test(col1 int, col2 int, col3 int)"
db2 "create index testidx on test(col2, col3)"

那么以下SQL会用到索引testidx：
SELECT * FROM test WHERE col2=1111"
SELECT * FROM test WHERE col2=1111 AND col3=2111"
SELECT * FROM test WHERE col2=1111 AND (col3=2111 OR col3=2112)"
SELECT * FROM test WHERE col2=1111 AND col3 >=2111 AND col3 < 2114"

以下SQL不会用到索引testidx：
"SELECT * FROM test WHERE col3=2111" 
"SELECT * FROM test WHERE col2=1111 OR col3=2111"

验证，由于DB2和MySQL这一块是一样的，所以，用了DB2做测试，测试脚本如下：

#!/bin/bashdb2 "connect to sample"db2 "select tabname from syscat.tables where tabname='TEST'"if [ $? -eq 0 ] ; thenecho "table alreay exist, will delete now"db2 "drop table test"fidb2 "create table test(col1 int, col2 int, col3 int)"db2 "create index testidx on test(col2, col3)"for (( i = 0; i < 1000; i++ ))dodb2 "insert into test values($i, $i + 1000, $i + 2000)" > /dev/nulldonedb2 runstats on table $USER.test and indexes all;db2expln -d sample -g -statement "SELECT * FROM test WHERE col2=1111" -output db2expln1.outdb2expln -d sample -g -statement "SELECT * FROM test WHERE col2=1111 AND col3=2111" -output db2expln2.outdb2expln -d sample -g -statement "SELECT * FROM test WHERE col2=1111 AND (col3=2111 OR col3=2112)" -output db2expln3.outdb2expln -d sample -g -statement "SELECT * FROM test WHERE col2=1111 AND col3 >=2111 AND col3 < 2114" -output db2expln4.outdb2expln -d sample -g -statement "SELECT * FROM test WHERE col3=2111" -output db2expln5.outdb2expln -d sample -g -statement "SELECT * FROM test WHERE col2=1111 OR col3=2111" -output db2expln6.outdb2 drop table test;db2 connect reset;

完成之后，查看执行计划，发现db2expln1.out~db2expln4.out的执行计划如下，用到了索引扫描：

而db2expln5.out和db2expln6.out没有用到索引扫描：