MySQL基础（七）：索引简介

最新推荐文章于 2023-06-11 21:48:39 发布

凤求凰的博客

最新推荐文章于 2023-06-11 21:48:39 发布

阅读量383

点赞数

分类专栏： # 数据库(MySQL) 文章标签：索引

本文链接：https://blog.csdn.net/weixin_44571270/article/details/106972583

版权

数据库(MySQL) 专栏收录该内容

16 篇文章 3 订阅

订阅专栏

下面是小凰凰的简介，看下吧！
💗人生态度：珍惜时间，渴望学习，热爱音乐，把握命运，享受生活
💗学习技能：网络 -> 云计算运维 -> python全栈( 当前正在学习中)
💗您的点赞、收藏、关注是对博主创作的最大鼓励，在此谢过！
有相关技能问题可以写在下方评论区，我们一起学习，一起进步。
后期会不断更新python全栈学习笔记，秉着质量博文为原则，写好每一篇博文。

一、索引是什么？优点、缺点

1、索引的官方定义

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

你可以简单理解为“排好序的快速查找数据结构”。

2、索引的优点与缺点

# 优点
1. 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

# 缺点
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。 
# 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

二、mysql索引结构

阅读之前建议看看磁盘块索引详解：https://blog.csdn.net/weixin_44571270/article/details/105899656

1、BTree索引

原理图
在这里插入图片描述

注意：由于myisam是非聚集索引，因此data其实存的是数据的物理地址。
每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null，只有树枝节点、根结点才有指针。

2、B+Tree索引

只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针，这样一棵树成了数据库系统实现索引的首选数据结构。
在这里插入图片描述

注意：由于innodb是聚集索引，因此data其实存的就直接是数据，因此查询更高效。

最主要的原因是：这棵树矮胖，呵呵。一般来说，索引很大，往往以索引文件的形式存储的磁盘上，索引查找时产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的时间复杂度。树高度越小，I/O次数越少。

# 为什么B+树成为数据库系统实现索引的首选数据结构
1. 那为什么是B+树而不是B树呢，因为它根节点与树枝节点不存储data

2. 这样一个节点就可以存储更多的key。这样整个树相比B树会更加矮胖，I/O次数更少！

3. MyISAM存储引擎采用b-树实现索引，innodb采用b+树实现索引，因此这也是innodb用的更加多的原因之一

3、B+树与B-树的区别

# B+树与B-树的区别
1. 从存储数据来看
b-树，叶子节点跟非叶子节点都储存数据
b+树，只有在叶子节点储存数据
2. 从结构上来看
B树：平衡多叉搜索树； 
B+树：有序双向链表（作用是优化范围查询）+平衡多叉搜索树；

# B+树的特点：
1、B+树的层级更少：相较于B树B+每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快；

2、B+树查询速度更稳定：B+所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定;

3、B+树天然具备排序功能：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。
4、B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

# B树相对于B+树的优点
如果经常访问的数据离根节点很近，而B树的非叶子节点本身存有关键字其数据的地址，所以这种数据检索的时候会要比B+树快。

4、聚簇索引与非聚簇索引

也叫聚集索引与非聚集索引

MyISAM

data存的是数据地址。索引是索引，数据是数据。索引放在XX.MYI文件中，数据放在XX.MYD文件中，所以也叫非聚集索引。

InnoDB

data存的是数据本身。索引也是数据。数据和索引存在一个XX.IDB文件中，所以也叫聚集索引。

MyISAM 与InnoDB建表文件对比：https://blog.csdn.net/weixin_44571270/article/details/106710860

1. 聚簇索引的好处： 
	•	按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

2. 聚簇索引的限制： 
	•	对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。 
	•	由于数据物理存储排序方式只能有一种 ，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键 。
	•	为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。（参考聚簇索引的好处。） 

这里说明了主键索引为何采用自增的方式：1、业务需求，有序。2、能使用到聚簇索引

5、full-text全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

CREATE TABLE `article` ( 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `title` varchar(200) DEFAULT NULL, 
  `content` text, 
  PRIMARY KEY (`id`), 
  FULLTEXT KEY `title` (`title`,`content`) # 全文索引
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

限制： 
mysql5.6.4以前只有Myisam支持，5.6.4版本以后innodb才支持，但是官方版本不支持中文分词，需要第三方分词插件。 
5.7以后官方支持中文分词。 
  
随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被 solr,elasticSearch等专门的搜索引擎所替代。

6、Hash索引

Hash索引只有Memory, NDB两种引擎支持，Memory引擎默认支持Hash索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储。 
NoSql采用此种索引结构。

7、R-Tree索引

R-Tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。 

相对于b-tree，r-tree的优势在于范围查找。

三、mysql索引分类

1、主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引

// 随表一起建索引： 
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200), 
  PRIMARY KEY(id)  
); 
unsigned (无符号的) 
使用  AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。 


CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200), 
  PRIMARY KEY(id)  
); 


// 单独建主键索引： 
ALTER TABLE customer  
 add PRIMARY KEY customer(customer_no);   
  
// 删除主键索引： 
ALTER TABLE customer  
 drop PRIMARY KEY ;   
  
// 修改主键索引： 
必须先删除掉(drop)原索引，再新建(add)索引

2、单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

索引建立成哪种索引类型？
根据数据引擎类型自动选择的索引类型
除开 innodb 引擎主键默认为聚簇索引 外。 innodb 的索引都采用的 B+TREE
myisam 则都采用的 B-TREE索引

// 随表一起建索引： 
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200), 
  PRIMARY KEY(id), 
  KEY (customer_name)   
); 
 注意：随表一起建立的索引 索引名同 列名(customer_name) 
// 单独建单值索引： 
CREATE  INDEX idx_customer_name ON customer(customer_name);  
 
// 删除索引： 
DROP INDEX idx_customer_name ;

3、唯一索引

索引列的值必须唯一，但允许有空值

// 随表一起建索引： 
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200), 
  PRIMARY KEY(id), 
  KEY (customer_name), 
  UNIQUE (customer_no) 
); 
注意：建立唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。   


// 单独建唯一索引： 
CREATE UNIQUE   INDEX id x_customer_no ON customer(customer_no);  
 
// 删除索引： 
DROP INDEX idx_customer_no on customer ;

4、复合索引

即一个索引包含多个列

在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)
当表的行数远大于索引列的数目时可以使用复合索引

// 随表一起建索引： 
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200), 
  PRIMARY KEY(id), 
  KEY (customer_name), 
  UNIQUE (customer_name), 
  KEY (customer_no,customer_name) 
); 
  
// 单独建索引： 
CREATE    INDEX idx_no_name ON customer(customer_no,customer_name);  
 
// 删除 索引： 
DROP INDEX idx_no_name  on customer ;

5、基本语法

  // 创建
    ALTER mytable ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length)) 
  // 删除
    DROP INDEX [indexName] ON mytable; 
  // 查看
    SHOW INDEX FROM table_name\G
  
// 使用ALTER命令有四种方式来添加数据表的索引： 
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。 


ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。 


ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。 


ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

6、辅助索引

辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引（主键索引）的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引（单值索引、唯一索引、复合索引）

上面我们讲b+树索引实现，是采用的聚集索引（id）来讲的，因此叶子结点的data存的是本条记录的数据，辅助索引的b+树实现的特点是：辅助索引的叶子结点存放的是辅助索引建立的字段所在的那张表的主键的聚集索引数据块的地址。

查找流程：当通过辅助索引查找时，先通过辅助索引，获取聚集索引，再通过聚集索引查找到相应的数据。

辅助索引又分为覆盖索引和非覆盖索引：

覆盖索引
下面语句叫覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

select name from user where name='jason';

非覆盖索引
下面语句叫非覆盖索引,虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用聚集索引去查找！

select age from user where name='jason';

四、创建索引的原则

1、哪些情况需要创建索引

1. 主键自动建立唯一索引

2. 频繁作为查询条件的字段应该创建索引(where 后面的语句)

3. 查询中与其它表关联的字段，外键关系建立索引
A表关联B表：A join B,on 后面的连接条件 既 A 表查询 B 表的条件。所以 B 表被关联的字段建立索引能大大提高查询效率 
因为在 join 中，join 左边的表会用每一个字段去遍历 B 表的所有的关联数据，相当于一个查询操作

4. 单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)

5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
group by 和 order by 后面的字段有索引大大提高效率

6. 查询中统计或者分组字段

2、哪些情况不要创建索引

1. 表记录太少
2. 经常增删改的表
	Why:提高了查询速度，同时却会降低更新表的速度
	如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
3. Where条件里用不到的字段不创建索引
4. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

凤求凰的博客

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL基础（七）：索引简介

文章目录一、索引是什么？优点、缺点1、索引的官方定义2、索引的优点与缺点二、mysql索引结构1、BTree索引2、B+Tree索引3、B+树与B-树的区别4、聚簇索引与非聚簇索引5、full-text全文索引6、Hash索引7、R-Tree索引三、mysql索引分类1、主键索引2、单值索引3、唯一索引4、复合索引5、基本语法6、辅助索引四、创建索引的原则1、哪些情况需要创建索引2、哪些情况不要创建索引一、索引是什么？优点、缺点1、索引的官方定义MySQL官方对索引的定义为：索引（Index）是帮助M
复制链接

扫一扫