MySQL索引详解(聚集索引与非聚集索引以及数据结构)

来源(CSDN博客)

From: https://blog.csdn.net/lmiao1992/article/details/101429126

MySQL索引详解(聚集索引与非聚集索引以及数据结构)

leo_gothic 2019-09-30 11:48:17 503 收藏 1

分类专栏: 数据库

版权

一、索引的简介

索引是存储引擎快速找到记录的一种数据结构,是数据库中专门用于帮助用户快速查询数据的一种数据结构,可以帮助用户快速寻找到需要的数据行,是数据库性能优化中最重要的工具。

使用索引的主要目的是为了优化查询速度,它们包含着对数据表里所有记录的引用指针。简单的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。

二、索引的分类

索引是在存储引擎中实现的,也就是说不同的存储引擎会使用不同的索引,像:MyISAM 和 InnoDB 这两种存储引擎使用的索引结构是 B + tree ;Memory 存储引擎支持 Btree和hash 索引结构。

1. 单列索引

· 普通索引:MySQL中最基本的索引类型,没有什么限制,允许定义索引的列中插入重复值和空值,纯粹就是为了查询数据更快一些;

· 唯一索引:索引列中的值必须是唯一的,但是允许空值;

· 主键索引:一种特殊的唯一索引,不允许有空值。

2. 组合索引

在表中的多个字段组合上创建的索引,遵循最左前缀原则。

最左前缀原则:使用组合索引时在查询条件中使用了这些字段的左边字段时,索引才会被使用。

回表:如果 select 所需获得列中有大量的非索引列,索引就需要到表中找到相应的列的信息。

3. 全文索引

全文索引只能在MyISAM存储中才能使用,而且只能在char、varchar、text 类型字段上才能使用。

三、索引的使用

创建一个表用来做测试使用:

CREATE TABLE `user`  (
	`id` int(10) NOT NULL COMMENT '主键',
	`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
	`age` tinyint(3) NULL DEFAULT NULL COMMENT '年龄',
	`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址',
	PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


1.  普通索引

CREATE INDEX indexName ON  tableName(columnName(columnLength))

例如: create index index_name on `user`(name(10))

列的长度可以不写,但是创建索引时如果是blob 和 text 类型,必须指定length。

2. 唯一索引

CREATE UNIQUE INDEX indexName ON tableName(columnName(columnLength))

3.  全文索引

CREATE FULLTEXT INDEX indexName ON tableName(columnName(columnLength))

4.  联合索引

ALTER TABLE tableName add INDEX indexName (column1,column2,column3)

例如:ALTER TABLE `user`  add INDEX indexName (name,age,address)

5. 删除索引

DROP INDEX indexName ON  tableName;

例如:DROP INDEX index_name ON `user`

四、索引的存储结构

1. B Tree 和 B + Tree 的特点以及区别

· 树的高度一般都是2-4层,树的高度会直接影响到 IO 读写的效率;

· 如果是三层树结构,支撑的数据量可以达到 20G ,如果是4层树结构,支撑的数据可以达到几十 T;

· B Tree 和 B+ Tree最大的区别就是非叶子接覅是否存储数据的问题:B Tree 是非叶子节点和叶子节点都会存储数据, B + Tree 只有叶子节点会存储数据,而且存储数据都是在一行上面,并且这些数据都是有指针指向的,也就是说是有顺序的。

2. 非聚集索引(MyISAM)

· 叶子节点只会存储数据行的指针,简单来说数据和索引不在一起,就是非聚集索引;

· 主键索引和辅助索引都会存储指针的值。



简单来讲:就是主键索引与辅助索引都是存储的是数据的指针,都是拿着指针去数据文件中找数据。

3. 聚集索引(InnoDB)

· 主键索引(聚集索引)的叶子节点会存储数据行,也就是说数据和索引是在一起,这就是聚集索引。

· 辅助索引只会存储主键值;

· 如果没有主键,就会使用唯一索引建立聚集索引;如果没有唯一索引,MySQL会按照一定的规则创建聚集索引。



简单来讲:就是主键索引存储的是数据,在找到主键的时候就直接找到了数据,比较方便快捷;使用辅助索引的话存储的是主键的值,拿到主键的值,再去主键索引中找一下数据行。

————————————————

版权声明:本文为CSDN博主「leo_gothic」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。

原文链接:https://blog.csdn.net/lmiao1992/java/article/details/101429126

Link: http://www.asm32.net/article_details.aspx?id=7369


浏览次数 0 发布时间 2020-07-27 12:00:05 从属分类 MySQL 【评论】【 】【打印】【关闭
 
| www.asm32.net | 2006版 | 资料中心 | linux | asm/asm32 | C/C++ | VC++ | java | Python | 书签 | ASP.Net书签 | 京ICP备09029108号-1