欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql之联合索引

程序员文章站 2024-03-16 18:54:28
...

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;


mysql之联合索引
联合索引设置





mysql之联合索引
aaa bbb






mysql之联合索引
bbb aaa





mysql之联合索引
bbb