mysql索引(五)联合索引

1,281 阅读3分钟

这是我参与8月更文挑战的第21天,活动详情查看:8月更文挑战

Mysql索引大概有五种类型:

  • 普通索引(INDEX):最基本的索引,没有任何限制

  • 唯一索引(UNIQUE):与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。

  • 主键索引(PRIMARY):它 是一种特殊的唯一索引,不允许有空值。

  • 全文索引(FULLTEXT ):可用于 MyISAM 表,mysql5.6之后也可用于innodb表, 用于在一篇文章中,检索文本信息的, 针对较大的数据,生成全文索引很耗时和空间。

  • 联合(组合)索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。

这里我们先来看联合索引(组合索引)。

比较简单的是单列索引(b+tree)。这个就不做解释。

遇到多条件查询时,不可避免会使用到多列索引。

我们使用一个例子来理解联合索引的使用方法:

我们来创建一个表,里边有五个字段c1,c2,c3,c4,c5。这个数据表有一个组合索引(c1,c2,c3,c4)

创建数据表:

MariaDB [test]> CREATE TABLE t(
    -> c1 CHAR(1) not null,
    -> c2 CHAR(1) not null,
    -> c3 CHAR(1) not null,
    -> c4 CHAR(1) not null,
    -> c5 CHAR(1) not null
    -> )ENGINE myisam CHARSET UTF8;
Query OK, 0 rows affected (0.09 sec)

添加联合索引:

MariaDB [test]> alter table t add index c1234(c1,c2,c3,c4);
Query OK, 0 rows affected (0.00 sec)              
Records: 0  Duplicates: 0  Warnings: 0

添加几条数据:

MariaDB [test]> insert into t VALUES('1','1','1','1','1'),('2','2','2','2','2'),('3','3','3','3','3'),('4','4','4','4','4'),('5','5','5','5','5');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

接下来我们使用MySql Explain开始分析我们各种情况的查询语句是否用到了联合索引。且用到了联合索引中的那几个元素。

1:效率最高,同时走四个索引

(1):按顺序写

explain select * from t where c1 = '1' and c2 = '1' and c3 = '1' and c4 = '1';

1.png

(2):不按顺序写,经过mysql的优化,也是走全部索引的

explain select * from t where  c3 = '1' and c4 = '1' and c1 = '1' and c2 = '1';

2.png

2:最左前缀原则

(1):不走索引

explain select * from t where c2 = '1' and c3 = '1' and c4 = '1';

3.png

因为组合索引遵循最左前缀原则,而,我们的组合索引第一个字段是c1,如果我们的where查询条件中没有c1这个筛选条件,那么mysql默认认为我们不希望通过索引查询。

(2):覆盖部分索引

4.png

我们可以对比上边两次查询的结果,

explain select * from t where c1 = '1' and c4 = '1';

只走了C1索引,因为组合索引遵循最左前缀原则。

explain select * from t where c1 = '1' and c2 = '1'; 这条查询语句走了C1和C2两个索引,同样,这个也是最左前缀原则的结果。

通过上边的对比,我们在使用联合索引的时候需要注意索引的使用顺序问题。

3:当查询条件中有范围查询及模糊查询的情况

(1):第一个字段使用模糊查询

explain select * from t where c1 like '3';

5.png

(2):第一个字段使用模糊查询并且其后边还有查询条件的时候

explain select * from t where c1 like '3' and c2 = '1' and c3 = '1' and c4 = '1';

6.png

从上边的查询结果我们可以看出,第一个字段使用模糊查询对之后的查询条件使用索引是没有影响的。

(3):使用between关键字范围查询

explain select * from t where c1 between '1' and '3' and c2 = '1' and c3 = '1' and c4 = '1';

7.png

全索引匹配。

(4):使用“>”“<”进行范围查询

explain select * from t where c1 > '3' and c2 = '1' and c3 = '1' and c4 = '1';

8.png

使用 > < 的时候,会对索引产生影响,通过上边的查询结果我们可以发现当第一个字段使用范围查询之后,后边的条件便不会再走索引了。

explain select * from t where c1 = '1' and c2 > '1' and c3 = '1' and c4 = '1';

9.png

这次就是走两个索引C1和C2。

以上大概就是联合索引的基本使用。

有好的建议,请在下方输入你的评论。

欢迎访问个人博客 guanchao.site

欢迎访问小程序: