MySQL高级 - 索引

67 阅读5分钟

「这是我参与2022首次更文挑战的第2天,活动详情查看:2022首次更文挑战

什么是索引

索引就是排好序的,帮助我们进行快速查找的数据结构。

简单来讲,索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引,能够显著地提高数据查询的效率,从而提升服务器的性能。

专业一点来说呢,索引是一个排好序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据库十分庞大的时候,索引可以大大加快查询的速度,这是因为使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

一般来说索引本身也比较大,不可能全部保存在内存中,因此索引通常是以索引文件的形式存储在磁盘上

索引的种类

  • 普通索引

    • 这是最基本的索引类型,基于普通字段建立的索引,没有任何限制。
    CREATE INDEX <索引的名字> ON tablename (字段名);
    ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
    CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );
    
  • 唯一索引

    • 与"普通索引"类似,不同的就是:索引字段的值必须唯一,但允许有空值。
    CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
    ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
    CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;
    
  • 主键索引

    • 它是一种特殊的唯一索引,不允许有空值。在创建或修改表时追加主键约束即可,每个表只能有一个主键。
    CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
    ALTER TABLE tablename ADD PRIMARY KEY (字段名);
    
  • 复合索引

    • 用户可以在多个列上建立索引,这种索引叫做组复合索引(组合索引)。复合索引可以代替多个单一索引,相比多个单一索引复合索引所需的开销更小。
    CREATE INDEX <索引的名字> ON tablename (字段名1,字段名2...);
    ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1,字段名2...);
    CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1,字段名2...) );
    
    • 复合索引使用注意事项:
      1. 何时使用复合索引,要根据where条件建索引,注意不要过多使用索引,过多使用会对更新操作效率有很大影响。
      2. 如果表已经建立了(col1,col2),就没有必要再单独建立(col1);如果现在有(col1)索引,如果查询需要col1和col2条件,可以建立(col1,col2)复合索引,对于查询有一定提高。
  • 全文索引

    • 查询操作在数据量比较少时,可以使用like模糊查询,但是对于大量的文本数据检索,效率很低。如果使用全文索引,查询速度会比like快很多倍。在MySQL5.6以前的版本,只有MyISAM存储引擎支持全文索引,从MySQL5.6开始MyISAM和InnoDB存储引擎均支持。
    CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
    
    ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
    
    CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;
    
    • 全文索引方式有自然语言检索IN NATURAL LANGUAGE MODE和布尔检索IN BOOLEAN MODE两种
  • 和常用的like模糊查询不同,全文索引有自己的语法格式,使用match和against关键字,比如

    SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aabb');
    
    -- * 表示通配符,只能在词的后面
    SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aa*' IN BOOLEAN MODE);
    
    • 全文索引使用注意事项:
      • 全文索引必须在字符串、文本字段上建立。
      • 全文索引字段值必须在最小字符和最大字符之间的才会有效。(innodb:3-84;myisam:4-84)

索引的优势与劣势

  • 优点

    • 提高数据检索的效率,降低数据库的IO成本
    • 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
  • 缺点

    • 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
    • 索引需要占物理空间,除了数据表占用数据空间之外,每一个索引还要占用一定的物理空间
    • 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度
  • 创建索引的原则

    • 在经常需要搜索的列上创建索引,可以加快搜索的速度;
    • 在作为主键的列上创建索引,强制该列的唯一性和组织表中数据的排列结构;
    • 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;
    • 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
    • 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
    • 在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。