IT数码 购物 网址 头条 软件 日历 阅读 图书馆
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
图片批量下载器
↓批量下载图片,美女图库↓
图片自动播放器
↓图片自动播放器↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁
 
   -> 大数据 -> Mysql高级:索引(一) -> 正文阅读

[大数据]Mysql高级:索引(一)

目录

索引是什么?

MySQL索引的结构

索引的优势

索引的劣势

MySQL索引的分类

主键索引

二级索引(辅助索引)

索引的基本语法

BTree的检索原理

创建索引的注意事项

选择合适的字段创建索引

被频繁更新的字段应该慎重建立索引

尽可能的考虑建立联合索引而不是单列索引

避免冗余索引

考虑在字符串类型的字段上使用前缀索引代替普通索引。

哪些情况不需要建索引

使用索引的一些建议


参考:

JAVAGUIDE-MySQL索引总结

索引是什么?

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。

索引的本质是数据结构,可以简单理解为排好序的快速查找数据结构。

索引的两大功能:排序和查找,索引会影响WHERE条件后面的查找,和ORDER BY后面的排序。

在数据之外,数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

(例如BTree)

上图展示了一种可能的索引方式。左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在O(log2n)O(log2n)的复杂度内获取到相应数据。(参考:MySQL索引原理及BTree(B-/+Tree)结构详解_一念永恒-CSDN博客_btree

一般来说索引本身也很大,不可能全部存储在内存中,因此所有往往以索引文件的形式存储在磁盘上。

我们平常说的索引,如果没有特别的指明,都是指B树(多路搜索树,不一定是二叉树的)结构组织的索引。其中聚集索引,次要索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引。

MySQL索引的结构

  • BTree索引(这篇博客主要用于学习记录BTree索引)
  • Hash索引
  • full-text索引
  • R-Tree索引

索引的优势

类似于大学图书馆建书目所有,提高数据的检索的效率,降低数据库的IO成本。

通过索引对数据进行排序,降低数据排序成本,降低了CPU的消耗。

索引的劣势

实际上索引也是一张表,该表保存了主键与索引字段,并指向了实体表的记录,所以索引列也是要占用空间的。虽然索引大大提高了查询速度,同时却会降低更新表的速度。如对表进行INSERT,UPDATE和DELETE,都会因为调整因为更新带来的键值变化后的索引信息。

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

MySQL索引的分类

主键索引

数据表的主键列使用的就是主键索引。

二级索引(辅助索引)

  • 唯一索引

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

  • 普通索引(Index

普通索引的唯一作用就是为了快速查询数据,一张表允许创建多个普通索引,并允许数据重复和 NULL。

  • 单值索引
    • 即一个索引只包含单个列,一个表可以有多个单列索引。
  • 复合索引(联合索引、多列索引)
    • 即一个索引包含多个列,遇到多条件查询时,不可避免会使用多列索引,联合索引又叫复合索引。

针对于单值索引来讲,我们都是建立复合索引,因为毕竟不可能规定用户去查一个指定的字段。不过像银行系统,频繁会用到身份证,银行卡号来做查询,不过这就是复合索引了。

一般来说,一张表建立的索引不超过五个,不过这也仅

仅是一个中规中矩的建议。

  • ?前缀索引(Prefix)

前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引,相比普通索引建立的数据更小, 因为只取前几个字符。

  • 全文索引(Full Text)?

全文索引主要是为了检索大文本数据中的关键字的信息,是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引,5.6 之后 InnoDB 也支持了全文索引。

索引的基本语法

添加 PRIMARY KEY(主键索引)

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

?添加 UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

?添加 INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

?添加 FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

?添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

BTree的检索原理

?【初始化介绍】

一颗B+树,浅蓝色的块我们称之为一个磁盘块。可以看到每个磁盘块包含了几个数据项(深蓝色所示)和指针(黄色所示)。

假设磁盘块1包含数据项17和35,包含指针P1,P2,P3。P1表示了小于17的磁盘块,P2表示17-35之间的磁盘块,P3表示大于35的磁盘块。

真实的数据存在于叶子节点,即3、5、9、10、13、15、28、29、36、60、75、79、90、99。

非叶子节点不存储真实的数据,只存储指引搜索方向的数据项。比如17,35并不真实存在于数据表之中。

【查找过程】
如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找法确定29在17-35之间,锁定磁盘块1的P2之中,内存时间因为非常短(相比于磁盘的IO)可以忽略不计,通过磁盘块1的P2指针指向了磁盘块3,26-30的范围中,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找到29,结束查询,总计三次IO。

真实的情况是,3层的B+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次IO,消耗的成本非常非常高。

创建索引的注意事项

选择合适的字段创建索引

  • 不为NULL的字段
  • 频繁作为条件查询的字段
  • 被经常频繁用于连接的字段
  • 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度(也就是说,建立一个索引,不仅仅要考虑到查的快,也要考虑是不是和排序字段撞车。)
  • 查询中统计或者分组字段

被频繁更新的字段应该慎重建立索引

虽然索引能带来查询上的效率,但维护索引的成本也是不小的。如果一个字段不被经常查询,反而被经常修改,那么就更不应该在这种字段上建立索引。

尽可能的考虑建立联合索引而不是单列索引

因为索引需要占用磁盘空间的,可以简单理解为每个索引都对应的一颗B+树,如果一个表的字段过多,索引过多,那么当这个表的数据达到一个体量后,索引占用的空间也是很多的,且修改索引时,耗费的时间也较多。如果是联合索引,多个字段在一个索引上,那么将会节约很大的磁盘空间。且修改的数据操作效率也会提升。

避免冗余索引

冗余索引指的是索引的功能相同,能够命中索引(a, b)就肯定能命中索引(a) ,那么索引(a)就是冗余索引。如(name,city )和(name )这两个索引就是冗余索引,能够命中前者的查询肯定是能够命中后者的 在大多数情况下,都应该尽量扩展已有的索引而不是创建新索引。

考虑在字符串类型的字段上使用前缀索引代替普通索引。

前缀索引仅限于字符串类型,较普通索引会占用更小的空间,所以可以考虑使用前缀索引带替普通索引。

哪些情况不需要建索引

  • 表记录太少
  • 经常增删查的表
  • 数据重复且分布均匀的表字段,因此应该只为最经常查询和最经常排序的数据建立索引。如果某个数据列包含多个重复内容,为它建立索引就没有太大的实际效果。

使用索引的一些建议

  • 对于中到大型表索引都是非常有效的,但是特大型表的话维护开销会很大,不适合建索引
  • 避免 where 子句中对字段施加函数,这会造成无法命中索引。
  • 在使用 InnoDB 时使用与业务无关的自增主键作为主键,即使用逻辑主键,而不要使用业务主键。
  • 删除长期未使用的索引,不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用
  • 在使用 limit offset 查询缓慢时,可以借助索引来提高性能

????????

?

  大数据 最新文章
实现Kafka至少消费一次
亚马逊云科技:还在苦于ETL?Zero ETL的时代
初探MapReduce
【SpringBoot框架篇】32.基于注解+redis实现
Elasticsearch:如何减少 Elasticsearch 集
Go redis操作
Redis面试题
专题五 Redis高并发场景
基于GBase8s和Calcite的多数据源查询
Redis——底层数据结构原理
上一篇文章      下一篇文章      查看所有文章
加:2021-09-14 13:24:52  更:2021-09-14 13:25:17 
 
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁

360图书馆 购物 三丰科技 阅读网 日历 万年历 2024年11日历 -2024/11/23 20:26:44-

图片自动播放器
↓图片自动播放器↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  IT数码