【Mysql】索引简介

本文口味:番茄炒蛋,预计阅读:10分钟。

博客又停更了两个月,在这期间,对人生和世界多了许多思考。在人生的不同阶段,会对生活和世界有着不一样的认知,而认知的改变也会直接反应在行为模式之中。

对于生活的思考心得也会在之后的时间里,慢慢分享给大家,一方面是对自己心路历程的记录和总结,另一方面也希望能给遇到同样问题或疑惑的朋友以帮助。目前生活已经慢慢调整到我想要的样子,博客写作也该继续起航了。

一、说明

Mysql是最常用的关系型数据库,而索引则是Mysql调优中最关心的部分,设计一个好的索引并写出合适的sql,就能将查询速度大大提升。从本篇开始,将会对Mysql中的索引进行深入浅出的介绍,从索引的简介、类别、使用姿势到索引的原理,最后到索引实战。希望通过本系列的文章,能让你对mysql中的索引有一个更深入的认识。

以下是本文大纲:

二、什么是索引

索引是存储引擎用于快速查找记录的一种数据结构。

emm,用人话说,如果把Mysql比作一本书的话,索引就是书的目录,根据目录便能很快找到需要的信息所在的页面,如果没有目录的话,想要查找想要的信息就只能一页一页翻了。
比如下面这样一条简单的sql:

SELECT id,name,course,grade FROM t_grade WHERE name = 'Frank';

如果没有添加索引的话,只能从最小记录开始依次遍历mysql中的记录,然后对比每条记录是否符合搜索条件。如果表中的数据量不大(十万级别以下),耗时其实也还好,毕竟目前来说,CPU效率已经很高了。但这样其实是对CPU的一种浪费,就好比开着跑车在泥泞的乡村小路上驾驶,完全无法发挥它应有的性能。而索引便是这样一条康庄大道,有了索引,才能充分发挥mysql引擎的性能,让你的sql跑车风驰电掣。

三、索引的优缺点

对于大部分事物而言,通常存在其对立面的,有好的一面,就会有坏的一面,就像质量好的东西通常价格高,便宜的东西通常质量差,索引也是如此。

使用索引的优点显而易见:

  1. 可以大大加快数据检索效率。
  2. 可以加速表与表之间的连接。
  3. 可以通过唯一索引的创建,保证数据的唯一性。
  4. 可以显著减少分组与排序的时间。

总而言之,用一个字来总结,就是快。

使用索引的缺点也是需要考虑的:

  1. 索引的创建和维护需要时间成本。表中的数据量越大,插入或删除数据时,调整索引所需要的时间就越长。
  2. 索引需要单独存储,占用磁盘空间,如果设置了大量的索引,占用的空间甚至比记录本身更大。
  3. 在对数据进行增、删、改时,需要同时更新索引中的数据,因此会影响增删改的速度。

所以使用索引并不是百利而无一害,使用不当甚至可能造成删库跑路的惨剧【手动滑稽】。但当你了解它的原理,掌握了索引的真谛,它就会成为你的神兵利器,让你在mysql开发中所向披靡。

四、索引的分类以及创建姿势

索引可分为普通索引、唯一索引、主键索引、组合索引、全文索引。看起来好像很多很复杂,但其实并非如此,且听我慢慢道来。

普通索引,名字中就透露出它普通的气质,也就是最常见的索引。

如何创建一个普通索引呢?其实很简单,如果是在DDL中创建索引,可以这样使用:

CREATE TABLE `t_grade` (
    id BIGINT(20) COMMENT '主键id',
    name VARCHAR(30) COMMENT '姓名',
    course INT COMMENT '课程,1-语文,2-数学,3-英语,4-物理',
    grade DECIMAL(5,2) COMMENT '成绩',
    KEY idx_name(`name`)
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

这样就为”name”列创建了一个名为”idx_name”的普通索引。通用的创建方式为:

KEY 索引名 (`列名`)

如果是为一张已经创建好的表添加一个普通索引,那么可以这样:

ALTER TABLE `t_grade` ADD KEY idx_name(`name`);

你可能会说,“不是用index关键字来创建索引的吗”,别急别急,其实它们的效果是一样的。

主键索引,一看就是很关键的角色,没错,每张表都会有且只有一个主键索引,即使没有显式的创建主键索引的话,也会自动创建一个隐藏的主键索引。

这么重要的索引,用的关键字肯定也得不一样才行,创建主键索引的关键字是PRIMARY KEY,在DDL中添加主键索引的姿势为:

CREATE TABLE `t_grade` (
    id BIGINT(20) COMMENT '主键id',
    name VARCHAR(30) COMMENT '姓名',
    course INT COMMENT '课程,1-语文,2-数学,3-英语,4-物理',
    grade DECIMAL(5,2) COMMENT '成绩',
    PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

因为主键只能有一个,所以不需要添加主键名。通用的添加方式为:

PRIMARY KEY (`列名`)

如果是为已创建好的表添加主键索引,那么可以这样:

ALTER TABLE `t_grade` ADD PRIMARY KEY (`id`);

唯一索引,顾名思义,就是“唯一”的索引,被添加到索引中的列的值必须是唯一的,如果向数据表中插入一条已存在的唯一索引字段记录,就会报错。

定义唯一索引的关键字为 UNIQUE KEY。在DDL中添加唯一索引的姿势为:

CREATE TABLE `t_grade` (
    id BIGINT(20) COMMENT '主键id',
    name VARCHAR(30) COMMENT '姓名',
    course INT COMMENT '课程,1-语文,2-数学,3-英语,4-物理',
    grade DECIMAL(5,2) COMMENT '成绩',
    UNIQUE KEY uk_name (`name`)
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

唯一索引的通用添加方式为:

UNIQUE KEY 索引名 (`列名`)

为已创建好的表添加唯一索引:

ALTER TABLE `t_grade` ADD UNIQUE KEY uk_name (`name`);

组合索引,又叫联合索引,便是将两个或者多个字段组合在一起的索引,好像跟没说一样= =
看一个栗子就知道了。

CREATE TABLE `t_grade` (
    id BIGINT(20) COMMENT '主键id',
    name VARCHAR(30) COMMENT '姓名',
    course INT COMMENT '课程,1-语文,2-数学,3-英语,4-物理',
    grade DECIMAL(5,2) COMMENT '成绩',
    KEY idx_name_corse (`name`,`course`)
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

同样是使用key关键字,在索引名后添加多个字段名即可。这里有一点需要注意的是,字段排列是有顺序的。举例说明,下面这两个索引是不一样的:

ALTER TABLE `t_grade` ADD KEY idx_name_course (`name`,`course`);
ALTER TABLE `t_grade` ADD KEY idx_name_course (`course`,`name`);

索引的匹配遵循“左缀匹配原则”,举个栗子说明,如果创建的组合索引是

ALTER TABLE `t_grade` ADD KEY idx_name_course (`name`,`course`);

那么下面语句将能命中这个组合索引。

SELECT * FROM `t_grade` WHERE name = 'Frank';  

而下面这个语句将无法命中索引:

SELECT * FROM `t_grade` WHERE course = 1;

因为在组合索引中,索引中的记录是先按照前一个字段排序,然后再根据后一个字段排序的,所以如果直接使用组合索引中的第二个字段查询时,查询索引对索引记录进行遍历,遍历完成之后还需要回溯到聚簇索引中获取完整记录,这样反而更耗时间,所以sql优化器会选择直接对记录进行遍历。

如果你还不清楚索引的结构以及聚簇索引是什么,不要着急,后面的文章里会有详细的介绍。

联合唯一索引,便是将多个字段组合起来形成一个唯一键,举个栗子:

先删除所有索引,然后添加两条记录:

INSERT INTO `t_grade` (`id`, `name`, `course`, `grade`) VALUES(1, 'Frank', 1, 100);
INSERT INTO `t_grade` (`id`, `name`, `course`, `grade`) VALUES(2, 'Frank', 1, 95);

这样就能插入两条记录了。

然后删掉这两条记录,创建一个联合唯一索引:

ALTER TABLE `t_grade` ADD UNIQUE KEY idx_name_course (`name`,`course`);

然后再来执行一下上面的sql:

这时候,就会得到一个错误提示,因为将字段namecourse创建了联合唯一索引,所以这两个字段的组合值必须是唯一的,如果要插入的记录的这两个字段组合值已经存在,那么就会抛出异常。

最后一个是比较复杂的索引:全文索引,由于其复杂性,这里只简单的介绍它的创建姿势。

CREATE TABLE `t_article`(
        id BIGINT COMMENT '文章id',
        title VARCHAR(200) COMMENT '文章标题',
        content TEXT COMMENT '文章内容',
        FULLTEXT (title, content)
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

或者给现有表添加全文索引:

ALTER TABLE `t_article` ADD FULLTEXT KEY fidx_title_content (title,content) WITH PARSER ngram;

想要使用全文索引查询,则需要使用MATCH关键字。

SELECT * FROM `t_article` WHERE MATCH(title, content) AGAINST('查询字符串');

当然,如果想要使用全文索引,需要确认mysql的版本号在5.7以上,否则无法在innodb引擎上使用全文索引的中文检索插件ngram。

五、索引使用前后对比

为了更直观的看出索引的优缺点,我们可以来对数据表添加索引前后执行相同sql的耗时来看出对比,这里仅进行简单的比较,没有使用性能测试。

先来创建一个数据表:

CREATE TABLE `t_grade` (
    id BIGINT(20) COMMENT '主键id',
    name VARCHAR(30) COMMENT '姓名',
    course INT COMMENT '课程,0-化学,1-语文,2-数学,3-英语,4-物理',
    grade DECIMAL(5,2) COMMENT '成绩'
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

然后插入一百万条数据:

public void batchInsert(){
    long timeMillis = System.currentTimeMillis();
    System.out.println("开始插入数据");
    for (int i = 1; i < 1000000; i++) {
        GradeDO gradeDO = new GradeDO((long) i, randomName(), random.nextInt(5), BigDecimal.valueOf(random.nextDouble() * 100));
        gradeMapper.insert(gradeDO);
    }
    System.out.println("插入一百万条记录耗时:" + ( System.currentTimeMillis() - timeMillis) / 1000.0 );
}

输出如下:

开始插入数据
插入一百万条记录耗时:1507.102

现在是没有索引的状态,开始进行插入测试:

public void batchInsert(){
    long timeMillis = System.currentTimeMillis();
    System.out.println("开始插入数据");
    for (int i = 1000000; i < 1010000; i++) {
        GradeDO gradeDO = new GradeDO((long) i, randomName(), random.nextInt(5), BigDecimal.valueOf(random.nextDouble() * 100));
        gradeMapper.insert(gradeDO);
    }
    System.out.println("插入一万条记录耗时:" + ( System.currentTimeMillis() - timeMillis) / 1000.0 );
}

输出如下:

开始插入数据
插入一万条记录耗时:15.681

然后进行查询测试。

@Test
void testQuery() {
    long timeMillis = System.currentTimeMillis();
    System.out.println("开始查询");
    for (int i = 0; i < 100; i++) {
        Integer id = random.nextInt(1000000);
        GradeDO gradeDO = gradeMapper.selectById(id);
    }
    System.out.println("一百次查询耗时:" + ( System.currentTimeMillis() - timeMillis) / 1000.0 );
}

输出如下:

开始查询
一百次查询耗时:51.658

接下来,为id列创建一个主键,并为name字段创建一个普通索引。
再插入一万条记录:

开始插入数据
插入一万条记录耗时:17.465

然后进行查询测试。

开始查询
一百次查询耗时:0.191

可以看出,在有单个索引的情况下,创建记录耗时略长于无索引的情况,当字段数量和索引数量增加时,这种差距将会增大。查询效率可以清晰的看出,这里添加了索引之后,大大的缩减了查询的耗时,当然,这里主要是聚簇索引的功劳。

六、总结

索引是mysql中十分重要的一个特性,使用好它就能让你的sql如虎添翼。简单来说,索引一方面可以大大提升查询性能,另一方面也会占用时间和空间成本,因此索引的选择也是一门学问。索引有很多种类型,不同类型的索引有着不同的特性,因此只有了解了它们各自的特性才能正确使用它们。
关于索引的简介就先介绍到这里了,后面会对索引的原理进行进一步深入的介绍,让你不仅知道怎么使用索引,而且还能知道为什么要这样使用索引。