mysql索引

最新推荐文章于 2024-10-18 18:39:07 发布

MLQP

最新推荐文章于 2024-10-18 18:39:07 发布

阅读量97

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_45114590/article/details/117250080

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MYSQL索引

是帮助mysql 高效获取数据的数据结构(有序),在数据之外,数据库系统还维护者满足特定查找算法的数据结构,这些数据结构以某种方式引用数据,这样就可以在这些数据结构上实现高效查找算法,这种数据结构就是索引.

索引的优势

1、类似与数据目录的引用，提高数据检索的效率，降低IO次数
2、通过索引列对数据进行排序，降低数据排序的成本。

索引的劣势

1、索引也是一张表，该表保存了主键与索引的字段，并指向实体的数据记录，索引需要额外占用空间。
2、索引提高了检索速度，降低了更新表的速度，进行insert，update,delete操作时，不仅需要更新表的内容，还要更新该表的索引的信息。

索引的结构

1、btree 索引：最常见的索引
2、hash 哈希索引
3、r-tree 空间索引
4、full-text 全文索引

btree索引

叫多路平衡搜索树,一颗m叉的Btree特性如下:
树中每个节点最多包含m个孩子
树根节点与叶子节点外,每个节点至少有[ceil(m/2)]个孩子
若根节点不是叶子节点,则至少有两个孩子
所有的叶子节点都在同一层
每个非叶子节点由n个key与n+1个指针组成,其中[ceil(m/2)-1]<=n<=m-1

以5叉BTree为例: [ceil(m/2)]=3
key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4,当n>4时，中间节点分裂到父节点，两边节点分裂。

插入 C N G A H E K Q M F W L T Z D P R X Y S 为例。

在这里插入图片描述

B+TREE 结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：
1).n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
2).B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
3).所有的非叶子节点都可以看作是key的索引部分。
由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。
MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指
针，就形成了带有顺序指针的B+Tree，提高区间访问的性能

索引分类

1）单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
2）唯一索引：索引列的值必须唯一，但允许有空值
3）复合索引：即一个索引包含多个列

	添加索引的语法
	create index idx_first_name on employees(first_name);
	查看索引
	show index from table_name;
	删除索引
	drop index index_name on tabl_name;
	添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
	alter table tb_name add primary key(column_list);
	创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	alter table tb_name add unique index_name(column_list);
	添加普通索引， 索引值可以出现多次。
	alter table tb_name add index index_name(column_list)
	指定了索引为FULL-TEXT， 用于全文索引
	alter table tb_name add fulltext index_name(column_list)
	创建复合索引
	CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
	就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;

创建索引的时候尽量考虑这些原则，提升索引的效率，高效的使用索引
对查询频次较高，且数据量比较大的表建立索引。

1.索引字段的选择，应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
2.使用唯一索引，区分度越高，使用索引的效率越高。
3.索引可以有效的提升查询数据的效率，但索引越多，维护索引的代价越多，对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会有相当高的维护代价，降低DML操作的效率，增加相应操作的时间。另外索引过多的话，mysql需要从可用索引（possible_keys）中挑选，无疑提高了选择的代价。
4.使用短索引，索引也需要硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效
率。构成索引的字段总长度比较短，给定大小的存储块内可以存储更多的索引值，可以有效的提升MySQL访问索引的I/O效率。
5.利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。