Mysql之索引｜青训营笔记

奶油要分开放
79 阅读8分钟

Mysql之索引

这是我参与「第五届青训营 」笔记创作活动的第4天

概述 

本节课程主要分为两个方面：

  • 索引初识
  • 索引应用

索引初识

一、MySQL索引机制概述

   对于MySQL索引机制的作用，经过上述「新华字典」的案例后可得知：索引就是用来帮助表快速检索目标数据的。此时先来简单回顾一下MySQL中索引是如何使用的呢？首先需要创建索引，MySQL可以通过CREATE、ALTER、DDL三种方式创建一个索引。

1.1、MySQL索引的创建方式

  • ①使用CREATE语句创建
CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);

这种创建方式可以给一张已存在的表结构添加索引，其中需要指定几个值：

  • indexName：当前创建的索引，创建成功后叫啥名字。
  • tableName：要在哪张表上创建一个索引，这里指定表名。
  • columnName：要为表中的哪个字段创建索引，这里指定字段名。
  • length：如果字段存储的值过长，选用值的前多少个字符创建索引。
  • ASC|DESC：指定索引的排序方式，ASC是升序，DESC是降序，默认ASC。

当然，上述语句中的INDEX也可更改为KEY，作用都是创建一个普通索引，而对于其他的索引类型，这点在后续的索引分类中再聊。

  • ②使用ALTER语句创建
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);
复制代码

这里的参数都相同，所以不再重复赘述。

  • ③建表时DDL语句中创建
CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  INDEX [indexName] (columnName(length))  
);

1.2、查询、删除、指定索引

但不管通过哪种方式建立索引，本质上创建的索引都是相同的，当索引创建完成后，可通过SHOW INDEX FROM tableName;这条命令查询一个表中拥有的索引，如下：

CREATE TABLE `zz_user`  (
  `user_id` int(8) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NULL DEFAULT "",
  `user_sex` varchar(255) NULL DEFAULT "",
  `user_phone` varchar(255) NULL DEFAULT "",
	PRIMARY KEY (`user_id`) USING BTREE
)
    ENGINE = InnoDB 
    CHARACTER SET = utf8 
    COLLATE = utf8_general_ci 
    ROW_FORMAT = Compact;

二、MySQL的索引分类

   在前面我为什么用多样化去形容数据库索引呢？因为确实如此，先列一些大家都听说过的索引称呼：聚簇索引、非聚簇索引、唯一索引、主键索引、联合索引、全文索引、单列索引、多列索引、复合索引、普通索引、二级索引、辅助索引、次级索引、有序索引、B+Tree索引、R-Tree索引、T-Tree索引、Hash索引、空间索引、前缀索引......

是不是看的眼花缭乱，这些都是MySQL中索引的一些称呼，一通看下来，估计大家看“索引”两个字都有点不认识了^_^

但实际上MySQL中真的有这么多索引类型吗？其实并没有，上述列出的索引称呼中，有几个称呼对应的索引是同一个，有一部分只是逻辑上的索引，那索引究竟该如何分类呢？其实从不同的层面上来说，可以将索引划分为不同的类型，接下来重点聊一聊。

2.1、数据结构层次

   前面聊索引本质的时候提到过，索引建立后也会在磁盘生成索引文件，那每个具体的索引节点该如何在本地文件中存放呢？这点是由索引的数据结构来决定的。比如索引的底层结构是数组，那所有的索引节点都会以Node1→Node2→Node3→Node4....这样的形式，存储在磁盘同一块物理空间中，不过MySQL的索引不支持数组结构，或者说数组结构不适合作为索引结构，MySQL索引支持的数据结构如下：

  • B+Tree类型：MySQL中最常用的索引结构，大部分引擎支持，有序。
  • Hash类型：大部分存储引擎都支持，字段值不重复的情况下查询最快，无序。
  • R-Tree类型：MyISAM引擎支持，也就是空间索引的默认结构类型。
  • T-Tree类型：NDB-Cluster引擎支持，主要用于MySQL-Cluster服务中。

在上述的几种索引结构中，B+树和哈希索引是最常见的索引结构，几乎大部分存储引擎都实现了，对于后续两种索引结构在某些情况下也较为常见，但除开列出的几种索引结构外，MySQL索引支持的数据结构还有R+、R*、QR、SS、X树等结构。

2.2、字段数量层次

   前面从索引的数据结构层次出发，可以将索引分为不同结构的类型，而从表字段的层次来看，索引又可以分为单列索引和多列索引，这两个称呼也比较好理解，单列索引是指索引是基于一个字段建立的，多列索引则是指由多个字段组合建立的索引。

单列索引也会分为很多类型，比如：

  • 唯一索引：指索引中的索引节点值不允许重复，一般配合唯一约束使用。
  • 主键索引：主键索引是一种特殊的唯一索引，和普通唯一索引的区别在于不允许有空值。
  • 普通索引：通过KEY、INDEX关键字创建的索引就是这个类型，没啥限制，单纯的可以让查询快一点。
  • .....还有很多很多，只要是基于单个字段建立的索引都可以被称为单列索引。

2.3、功能逻辑层次

   相信大家在面试时，如果问到了MySQL索引机制，相信一定会问如下这道面试题：

请回答一下你知道的MySQL索引类型。

这题的答案该怎么回答呢？其实主要就是指MySQL索引从逻辑上可以分为那些类型，以功能逻辑划分索引类型，这也是最常见的划分方式，从这个维度来看主要可划分为五种：

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

对于普通索引、唯一索引、主键索引都介绍过了，就不再过多阐述，但稍微提一嘴，在主键字段上建立的索引被称为主键索引，非主键字段上建立的索引一般被称为辅助索引或、二级索引或次级索引，接着重点聊一下全文索引和空间索引。

全文索引和空间索引都是MySQL5.7版本后开始支持的索引类型，不过这两种索引都只有MyISAM引擎支持，其他引擎要么我没用过，要么就由于自身实现的原因不支持，例如InnoDB。对于全文索引而言，其实在MySQL5.6版本中就有了，但当时并不支持汉字检索，到了5.7.6版本的时候才内嵌ngram全文解析器，才支持亚洲语种的分词，同时InnoDB引擎也开始支持全文索引，在5.7版本之前，只有MyISAM引擎支持。

2.4、存储方式层次

   上面聊完了三种不同层次的索引划分后，接着从存储方式的层面再聊聊，从存储方式来看，MySQL的索引主要可分为两大类：

  • 聚簇索引：也被称为聚集索引、簇类索引
  • 非聚簇索引：也叫非聚集索引、非簇类索引、二级索引、辅助索引、次级索引

索引应用

一、MySQL各索引的优劣分析

   首先来聊聊索引机制带来的利害关系，有句古话曾说过： “凡事有利必有弊” ，而MySQL的索引机制也不例外，引入索引机制后，能够给数据库带来的优势很明显：

  • ①整个数据库中，数据表的查询速度直线提升，数据量越大时效果越明显。
  • ②通过创建唯一索引，可以确保数据表中的数据唯一性，无需额外建立唯一约束。
  • ③在使用分组和排序时，同样可以显著减少SQL查询的分组和排序的时间。
  • ④连表查询时，基于主外键字段上建立索引，可以带来十分明显的性能提升。
  • ⑤索引默认是B+Tree有序结构，基于索引字段做范围查询时，效率会明显提高。
  • ⑥从MySQL整体架构而言，减少了查询SQL的执行时间，提高了数据库整体吞吐量。

看着上面一条又一条的好处，似乎感觉索引好处很大啊，对于这点确实毋庸置疑，但只有好处吗？No，同时也会带来一系列弊端，如：

  • ①建立索引会生成本地磁盘文件，需要额外的空间存储索引数据，磁盘占用率会变高。

  • ②写入数据时，需要额外维护索引结构，增、删、改数据时，都需要额外操作索引。

  • ③写入数据时维护索引需要额外的时间开销，执行写SQL时效率会降低，性能会下降。

参考资料

avatar
文章
阅读
粉丝