mysql建立联合索引_mysql之联合索引

mysql之联合索引测试：

前期准备：

建立联合索引？

CREATE TABLE `test` (

`id` bigint(16) NOT NULL AUTO_INCREMENT,

`aaa` varchar(16) NOT NULL,

`bbb` varchar(16) NOT NULL,

`ccc` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

如果表已经建好了，那么修改表：

create index `sindex` on `test` (`aaa`,`bbb`,`ccc`);

这种方式报错

alert table test add INDEX `sindex` (`aaa`,`bbb`,`ccc`)

修改表的存储引擎：

ALTER TABLE tablename ENGINE = MyISAM;

ALTER TABLE tablename ENGINE = INNODB;

Explain:

其中最重要的字段为：id、type、key、rows、Extra

id：

1、id相同：执行顺序由上至下

2、id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3、id相同又不同(两种情况同时存在)：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

type：

访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

key：

查询中如果使用了覆盖索引，则该索引仅出现在key列表中

rows：

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra：

1、Using filesort ：

mysql对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”

2、Using temporary：

使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by

3、Using index：

表示相应的select操作中使用了覆盖索引(Covering Index)，避免了访问表的数据行，效率高

如果同时出现Using where，表明索引被用来执行索引键值的查找(参考上图)

如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作

覆盖索引(Covering Index)：也叫索引覆盖。就是select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。

Explain:

下方的控制台主要关注两个栏，type和extra

当extra出现道Using filesort和Using temproary这两个时，表示无法使用索引版，必须尽快做优化。

当type出现all时，表示走的是全表扫描没有走索引，效率低下，这时需要对sql进行调优。

当type出现ref或者index时，表示走的是索引，index是标准不重复的索引，ref表示虽然使用了索引，但是索引列中有重复的值，但是就算有权重复值，也只是在重复值的

范围内小范围扫描，不造成重大的性能影响。

测试语句是否使用了索引：

网上说联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。但是

我蒙蔽了，在我实际的测试中，aaa bbb ccc 这三个条件不管删除那个，怎么组合where条件查询 type:index extral:Using where; Using index 难道说都用到索引了嘛？

sql1:explain select * from test where aaa=1 and bbb=1;

用到了索引

sql2:explain select * from test where bbb=1 and aaa=1;

联合索引设置

aaa bbb

bbb aaa

bbb