mysql之联合索引

浅浅的花香味﹌ 2023-05-22 10:22 2阅读 0赞

## ***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;

![v2-6a105efdeda8d67781f53a7f99e6311c\_b.jpg][v2-6a105efdeda8d67781f53a7f99e6311c_b.jpg] 联合索引设置

![v2-d5e241c1095052ed2883e39043b3ad95\_b.jpg][v2-d5e241c1095052ed2883e39043b3ad95_b.jpg] aaa bbb

![v2-2c467f5e018f746b1434fce3ee918aae\_b.jpg][v2-2c467f5e018f746b1434fce3ee918aae_b.jpg] bbb aaa

![v2-bea046c665c5891ccb6ee3064cf43838\_b.jpg][v2-bea046c665c5891ccb6ee3064cf43838_b.jpg] bbb

[v2-6a105efdeda8d67781f53a7f99e6311c_b.jpg]: https://ss.csdn.net/p?https://pic1.zhimg.com/v2-6a105efdeda8d67781f53a7f99e6311c_b.jpg
[v2-d5e241c1095052ed2883e39043b3ad95_b.jpg]: https://ss.csdn.net/p?https://pic2.zhimg.com/v2-d5e241c1095052ed2883e39043b3ad95_b.jpg
[v2-2c467f5e018f746b1434fce3ee918aae_b.jpg]: https://ss.csdn.net/p?https://pic3.zhimg.com/v2-2c467f5e018f746b1434fce3ee918aae_b.jpg
[v2-bea046c665c5891ccb6ee3064cf43838_b.jpg]: https://ss.csdn.net/p?https://pic1.zhimg.com/v2-bea046c665c5891ccb6ee3064cf43838_b.jpg