提的最多的数据库“索引”,先来简单了解一下

前言

现在的项目对于数据库操作基本上都是使用封装好的ORM框架,这样开发效率相对来说有所提高。但由于框架的封装,会自动生成SQL语句,这让一些小伙伴对SQL产生了一种陌生感(基本不写SQL),导致排查业务执行缓慢问题时比较盲目;其实本质还是SQL,而对于SQL的优化,索引是否使用上是一个关键的点,所以这先来了解一下平时见过的那些索引分类,后续再来好好说说索引的使用。

正文

1. 索引概述

索引是辅助高效获取数据的数据结构,目的就是为了提高查询效率

索引本身也会存在磁盘上,从存储和表数据操作效率来说,一个表创建过多的索引也不是个好事。

2. 索引分类

2.1 按逻辑使用分
  • 主键索引:主键索引也是一种唯一索引,不能有空值,一个表只能有一个主键。

    创建索引

    创建表时创建

    CREATE TABLE tableName(  
    	ID INT NOT NULL,   
    	username VARCHAR(16) NOT NULL,  
    	PRIMARY KEY (ID)   
    );
    

    修改表的形式添加

    ALTER TABLE tableName ADD PRIMARY KEY indexName(columnName);
    
  • 唯一索引:索引列的值必须唯一,但允许有空值

    创建索引

    创建表时创建

    CREATE TABLE tableName(  
    	ID INT NOT NULL,   
    	username VARCHAR(16) NOT NULL,  
    	UNIQUE indexName (username)   
    );  
    

    修改表的形式添加

    ALTER table tableName ADD UNIQUE indexName(columnName)
    

    有表时直接创建

    CREATE UNIQUE INDEX indexName ON tableName(columnName) 
    
  • 普通索引:基本的索引类型,没有唯一性限制允许有空值,一个表可以有多个普通索引;

    创建索引

    创建表时创建

    CREATE TABLE tableName(  
    	ID INT NOT NULL,   
    	username VARCHAR(16) NOT NULL,  
    	INDEX indexName (username)  
    );  
    

    修改表的形式添加

    ALTER table tableName ADD INDEX indexName(columnName)
    

    有表时直接创建

    CREATE INDEX indexName ON tableName (column_name)
    
  • 复合索引:一个索引可包含多列,一个表可以有多个复合索引,目的就是针对组合条件查询的场景。

    创建索引的方式和普通索引基本一样,只是可以指定多列。

    ALTER TABLE tableName ADD INDEX indexName(column_name1,column_name2,column_name3);
    
  • 全文索引:FULLTEXT索引,可以在varchar、char、text类型上创建,用作关键词查询等场景,但一般在关系型数据库中使用的不多,都会使用类似于ES的搜索引擎。

    创建索引

    创建表时创建

    CREATE TABLE tableName(
        id INT(10) PRIMARY KEY,
        username VARCHAR(10) NOT NULL,
        user_desc TEXT,
        FULLTEXT(user_desc)
    )
    

    修改表的形式添加

    ALTER TABLE tableName ADD FULLTEXT INDEX indexName(column_name);
    

    有表时直接创建

    CREATE FULLTEXT INDEX indexName ON tableName (column_name)
    

    如果是中文,在创建全文索引时,需要指明解析插件WITH PARSER ngram,否则查询不出对应结果,如下:

    CREATE FULLTEXT INDEX indexName ON tableName (column_name) WITH PARSER ngram;
    

    创建之后就可以针对对应的字段进行关键词搜索了,如下:

    # 针对column_name,如果匹配到有‘工作’两字的数据都查出来
    SELECT * FROM tableName WHERE MATCH(column_name) AGAINST('工作');
    
2.2 按存储分

索引其实是一种数据结构,可以不同的形式进行存储,所以可以将其进行如下分类:

  • Hash索引:采用Hash的形式进行存储,针对于等值条件的查询,效率很高,但比较耗内存,而在实际应用场景中,范围条件查询的场景比较多,所以Hash索引使用的不多。
  • BTree索引和B+ Tree索引:BTree和B+ Tree都是为了提升IO读效率,目的是减少IO读的次数,从而可以大大提升数据查询效率,B+ Tree其实是对BTree的扩展,B+ Tree能存储更多的数据,对叶子节点数据的存储增加关联关系,提升数据遍历效率。所以在InnoDB创建的索引默认都是B+ Tree索引。
  • R-Tree索引:空间索引,R树就是一棵用来存储高维数据的平衡树,可以用作地理数据存储。比如查看附近的共享单车位置信息这种场景,但对于数据量大点的场景,效率不高,都会使用其他方案代替,比如Redis。

具体的存储细节,暂时就不在这展开,关于数据结构和算法系列的文章,之前也分享过一部分,后续还会持续更新,说到具体内容时,再来详细说说如何在对应数据结构中操作数据。

2.3 聚簇索引和非聚簇索引
  • 聚簇索引(又称聚类索引、簇集索引):索引的顺序和表数据存储的物理顺序一致,因为一个表的数据顺序只有一种,所以一个表中只有一个聚簇索引。

    聚簇索引存储的形式是索引与数据信息存在一起,找到聚簇索引其实就找到了数据。

  • 非聚簇索引(又称非聚类索引、非聚集索引):索引的顺序和存储表数据的顺序无关;

    非聚簇索引存储的形式是索引和数据分开存储,先是根据索引找到对应数据的物理地址,然后根据物理地址再去定位对应的数据信息。

总结

关于索引先聊这么多,虽然ORM帮我们省去了写SQL的时间,但控制ORM生成高效的SQL语句是我们必须要做的,所以小伙伴们赶紧卷起来吧~~~,后面的文章还会继续说说索引在实际场景中的应用、SQL如何才能匹配到索引、如何避免索引失效等,关注“Code综艺圈”,和我一起学习吧。

页面下部广告