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

前言

现在的项目对于数据库操作基本上都是使用封装好的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综艺圈”，和我一起学习吧。

posted @ 2022-03-25 08:42  Code综艺圈  阅读(560)  评论(0)  编辑  收藏  举报