vlambda博客
学习文章列表

MySQL(十):索引基础知识回顾

尺有所短,寸有所长;不忘初心,方得始终。

1、索引简介

1.1 什么是索引

索引是对数据库表中一列或多列的值进行排序的一种结构,可以大大提高MySQL的检索速度。索引在MySQL中也叫做key,当表中的数据量越来越大时,索引对于查询性能的影响非常大。

那索引具体是什么呢,找几个生活中实例比较一下就清晰了:

  • 新华字典:索引就相当于字典的音序表,我们可以通过音序表,快速在几百页中定位到我们要查找的字。
  • 书店书架:索引就相当于书店里面的书架上的标签,可以通过标签,快速从成千上万本书中找到我们需要的书籍。

由此可知,其实索引就是一个目录,即数据库表的一个数据目录,帮助快速定位数据在磁盘中的位置,以此达到提高查询性能的目的。

1.2 索引的优缺点

  • 优点

    • 索引减小了需要扫描的数据量,从而大大加快数据的检索速度(创建索引的最主要的原因)
    • 可以加速表与表的连接
    • 可以显著的减少查询中分组和排序的时间
    • 索引可以帮助服务器避免排序和创建临时表
    • 索引可以将随机IO变成顺序IO
MySQL(十):索引基础知识回顾

索引既然有这么多优点,那为什么不对表中每个列都建一个索引呢,这样不是更加能提升性能吗,实际上这是不可取的,索引虽然有诸多优点,但是也有很多缺点

  • 缺点

    • 对表中的数据进行增、删、改的时候,索引也要动态的维护,降低了数据的写入速度
    • 随着数据量的增加,创建索引和维护索引要耗费时间也会越来越长,影响性能
    • 索引的存储需要占物理空间,每一个索引都要占用一定的物理空间

2、创建索引准则

基于以上索引的介绍,我们知道索引优缺点都很明显,我们不能在表数据中所有的列都添加索引,需要根据具体场景选择创建索引的列与类型。那么具体应该在那些列中添加索引,那些列中不能添加索引呢?

  • 能创建索引的列

    • 主键索引,在MySQL中,主键列会默认的当成唯一性索引
    • 在业务场景中被【 当成条件查询的列】创建索引,可以提高查询效率
    • 外键索引,比如需要【 用于JOIN的列】创建索引,可以提高连接的速度
    • 由于索引是已经排序的,所以在经常【 用于范围查询的列】和需要【 排序的列】创建索引,可以避免排序,提高查询效率
  • 不能创建索引的列

    以上几种情况的列,一般不建议创建索引,非但不能提高查询速度,反而增加索引后提高了数据的维护时间成本和空间成本。

    • 经常用于计算的列
    • 数据值很少或者大量重复的列
    • 大字段的列
    • 经常修改的列
    • 很少使用的字段

3、MySQL索引的创建与分类

3.1MySQL索引类型

MySQL索引的类型其实只有五种,但是我们经常会听到很多种不同的索引,那其实是在不同维度划分的类型:

  • 存储结构维度划分

    B Tree索引、Hash索引、B + Tree索引

  • 应用层次维度划分

    普通索引、唯一索引、主键索引、全文索引,空间索引

    空间索引基本不使用,这里不做介绍

  • 索引键值类型维度划分

    主键索引、辅助索引(二级索引)

  • 数据存储和索引键值逻辑关系维度划分

    聚集索引(聚簇索引)、非聚集索引(非聚簇索引)

  • 索引组成维度划分

    组合索引(复合索引)、单一索引

本文主要以应用层次维度来说明索引的分类,其他维度在后续文章中描述。

3.2MySQL索引的创建与删除

  • 索引的创建

    索引的创建方式有三种:建表时创建索引,已存在的表上直接创建索引,已存在的表上新增列并创建索引

    • 建表时创建索引

      CREATE TABLE 表名 (
      字段名1 数据类型 [完整性约束条件…],
      字段名2 数据类型 [完整性约束条件…],
      [NORMAL | UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
      [索引名] (字段名[(长度)] [ASC | DESC])
      );
    • 已存在的表上直接创建索引

      CREATE  [NORMAL | UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名  ON 表名 (字段名[(长度)]  [ASC | DESC]) ;
    • 已存在的表上新增列并创建索引(修改表结构)

      ALTER TABLE 表名 ADD  [NORMAL | UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)]  [ASC | DESC]) ;
  • 名词解释

    • NORMAL | UNIQUE | FULLTEXT | SPATIAL

      可选参数,Normal 普通索引,Unique 唯一索引,Full Text 全文索引,SPATIAL 空间索引

    • INDEX | KEY

      同义词,作用相同,用来指定创建索引

    • ASC | DESC

      指定升序或降序的索引值存储

  • 索引的删除

    DROP INDEX 索引名 ON 表名字;
  • 查看表结构

    desc table_name;
  • 查看生成表的SQL

    show create table table_name;
  • 查看索引结构信息

    show index from  table_name;
  • 查看SQL执行时间

    set profiling = 1;
    select * from user where id=1;
    show profiles;

3.3 普通索引

最基本的索引类型,基于普通字段建立的索引,没有任何限制。

一张表可以创建多个普通索引,一个普通索引可以包含多个字段【组合索引】,允许数据重复,允许 NULL 值插入

  • 建表时创建索引

    CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `name` char(255) CHARACTER NOT NULL ,
    `idcard` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (name(length))
    )
  • 已存在的表上直接创建索引

    CREATE INDEX index_name ON user (name(length))
  • 已存在的表上新增列并创建索引(修改表结构)

    ALTER TABLE user ADD INDEX index_name ON (name(length))

通过以上三种方式为User表的name字段创建普通索引时,可以看到,并没有使用NORMAL关键字,这是因为在创建普通索引时,NORMAL关键字是可以省略的,直接使用Index即可。

3.4 唯一索引

普通索引基本相同类似,区别在于:唯一索引字段的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。在创建或修改表时追加唯一约束,就会自动创建对应的唯一索引。

  • 建表时创建索引

    CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `name` char(255) CHARACTER NOT NULL ,
    `idcard` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (name(length))
    )
  • 已存在的表上直接创建索引

    CREATE UNIQUE INDEX index_name ON user (idcard(length))
  • 已存在的表上新增列并创建索引(修改表结构)

    ALTER TABLE user ADD UNIQUE index_name ON (idcard(length))

通过以上三种方式为User表的idcard(身份证号码)字段创建唯一索引时,使用UNIQUE关键字。

3.5 主键索引

是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引,通过PRIMARY KEY关键字指定

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` char(255) CHARACTER NOT NULL ,
`idcard` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)

3.6 组合索引

一个组合索引包含两个或两个以上的列。遵循 mysql 组合索引的【最左前缀原则】,即使用 where 时条件要按照索引建立时字段的排列方式放置索引才会生效。

CREATE INDEX index_name ON user (name,idcard);

3.7 全文索引

主要用来查找文本中的关键字,而不是直接与索引中的值相比较。

fulltext索引更像是一个搜索引擎,一般配合match against操作使用,而不是简单的where语句的like参数匹配。目前只有char、varchar,text 列上可以创建全文索引。

  • 建表时创建索引

    CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `name` char(255) CHARACTER NOT NULL ,
    `idcard` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
    )
  • 已存在的表上直接创建索引

    CREATE FULLTEXT INDEX index_name ON user(content)
  • 已存在的表上新增列并创建索引(修改表结构)

    ALTER TABLE user ADD FULLTEXT index_name ON (content)

通过以上三种方式为user表的content字段创建全文索引时,使用FULLTEXT关键字。

在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。