索引的优缺点
索引可以大大提升查询的速度
牺牲了增删改的速度,因为在增删改的时候,为了保证索引的有序性,需要动态维护索引
空间
创建索引需要占用磁盘空间
索引的类型
主键索引: 数据列不允许重复,不允许为NULL,一个表只能有一个主键。
关键字 primary key 创表时,对字段名添加主键约束,相当于同时创建了主键索引
唯一索引: 数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引。
-
可以通过
ALTER TABLE table_name ADD UNIQUE (column);
创建唯一索引 -
可以通过
ALTER TABLE table_name ADD UNIQUE (column1,column2);
创建唯一组合索引 -
关键字 unique 创表时,对字段名添加唯一约束,相当于同时创建了唯一索引
普通索引: 基本的索引类型,没有唯一性的限制,允许为NULL值。
-
可以通过
ALTER TABLE table_name ADD INDEX index_name (column);
创建普通索引 -
可以通过
ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);
创建组合索引 -
关键字(Index)
全文索引: 是目前搜索引擎使用的一种关键技术。
-
可以通过
ALTER TABLE table_name ADD FULLTEXT (column);
创建全文索引 -
关键字 fulltext InnDB 不支持 MyISAM支持
-
聚簇索引 = 主键索引 = 一级索引 非聚簇索引 = (普通索引) = 二级索引0
创建和删除索引的语法
(聚簇索引=主键索引是表自带的,我们自己创建的都是非聚簇索引)
(如果表没有索引,那么会选择隐藏列rowid来创建聚簇索引)
创建
1.在create表的创建
2.在create建表成功以后,使用alter语句添加索引
ALTER TABLE 表名 ADD INDEX 索引名(字段名);
3.在create建表成功以后,使用create index语句添加索引
CREATE INDEX 索引名 ON 表名(字段名)
删除
1.使用alter语句删除索引
ALTER TABLE 表名 DROP KEY 索引名
2.使用drop语句删除索引
DROP INDEX 索引名 ON 表名
索引的数据结构
MySQL的索引支持两种数据结构(默认用B+树)
1.B+TREE B+数据结构的索引
2.HASH 哈希结构的索引(不支持范围查询)
数据结构的演进
二叉树
问题:无法自动平衡
平衡二叉树 -AVL 树
左旋算法:当右边的叶子节点的深度减去左边的叶子节点的深度>1的时候触发左旋
右旋算法:当左边的叶子节点的深度减去右边的叶子节点的深度>1的时候触发右旋
目的:维持树的平衡
多路平衡二叉树 - B树
树的度数(Degree):每个节点中可以存储的元素的个数
单路树:度数=1
多路树:度数=n,n>1
同等规模的数据,单路树是高瘦树,多路树是矮胖树
多路树比单路树的优势在于更少次数的磁盘IO就可以找到数据
MySQL是怎么设计索引的?
树的每一个节点是一个磁盘块,MySQL将表的ibd文件拆分为很多个磁盘块,每个磁盘块的大小统一是16KB,每次磁盘IO都是读取一个磁盘块的数据,一个磁盘块的数据也称为一页数据
B树升级为B+树