MySql干货分享之索引

Wesley13
• 阅读 527

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。

今天我们就来聊聊MySQL的索引。尽管MySQL有许多优点,但是在海量数据的情况下,性能方面的表现还是会让人捉急,这时候就轮到MySQL的索引出场了。我会以抛出问题然后解决问题的方式来进行本次分享。比如:什么是索引?索引可以做什么?为什么使用索引可以提高效率?MySQL支持哪些索引类型?什么情况下应不建或少建索引?什么是联合索引?为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引?

一、什么是索引?

官方解释:索引(Index)是帮助MySQL高效获取数据的数据结构。

通俗理解:索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。

二、索引可以做什么?

首先,索引不是万能的,索引可以加快数据检索操作,但会使数据修改操作变慢。每次修改数据记录,索引就必须刷新一次。为了在某种程度上弥补这一缺陷,许多 SQL 命令都有一个 DELAY_KEY_WRITE 项。这个选项的作用是暂时制止 MySQL 在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新,对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合,DELAY_KEY_WRITE 选项的作用将非常明显。

三、为什么使用数据索引能提高效率?

  • 数据索引的存储是有序的
  • 在有序的情况下,通过索引查询一个数据是无需遍历索引记录的
  • 极端情况下,数据索引的查询效率为二分法查询效率,趋近于 log2(N)

四、MySQL支持哪些索引类型?

我们这里说的索引类型并不是指“主键索引”、“外键索引”这些,而是索引底层的数据结构。MySQL的索引数据结构支持以下两种:

  • B-Tree 索引。B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的,如下图: MySql干货分享之索引
  • Hash 索引。哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可,是无序的,如下图所示: MySql干货分享之索引
    • 哈希索引的优势:等值查询,哈希索引具有绝对优势(前提是:没有大量重复键值,如果大量重复键值时,哈希索引的效率很低,因为存在所谓的哈希碰撞问题。)
    • 哈希索引不适用的情况:
      • 不支持范围查询
      • 不支持索引完成排序
      • 不支持联合索引的最左前缀匹配规则

五、什么情况下应不建或少建索引?

我们都知道什么时候应该使用索引,那么,什么时候不应该使用索引呢?我们上面说到,索引并不是万能的,所以,索引肯定也有不适用的场景。以下几个场景的时候,我们应该尽量不建或者说少建索引:

  • 表记录太少。
  • 经常插入、删除、修改的表。
  • 数据重复且分布平均的表字段,假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。
  • 经常和主字段一块查询但主字段索引值比较多的表字段。

六、什么是联合索引?

  • 联合索引是两个或更多个列上的索引。 对于联合索引,MySQL支持从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c),可以支持a 、 a,b 、 a,b,c 这3种组合进行查找,但不支持 b,c进行查找,当最左侧字段是常量引用时,索引就十分有效。
  • 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引。
  • 复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不知道姓,电话簿将没有用处。

七、为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引?

  • B+的磁盘读写代价更低。B+的内部结点并没有指向关键字具体信息的指针,因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
  • B+-tree的查询效率更加稳定。由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

八、结语

看到这里,你应该已经把MySQL的索引知识都过了个大概,希望本文能帮到你,happy hacking。​接下来听首听首歌放松一下吧。 真的爱你 BEYOND - BEYOND

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
2年前
mySQL (关系型数据库管理系统)
MySQL是一个关系型数据库管理系统,由瑞典MySQLAB公司开发,目前属于 Oracle 旗下产品。MySQL是最流行的关系型数据库管理系统之一,在WEB应用方面,MySQL是最好的RDBMS(RelationalDatabaseManagementSystem,关系数据库管理系统)应用软件。MySQL是一种关系数据库管理系统,关
Easter79 Easter79
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
mysql零基础入门视频教程免费分享!
MySQL是一个关系型数据库管理系统,由瑞典MySQLAB公司开发,目前属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一,在WEB应用方面,MySQL是最好的RDBMS(RelationalDatabaseManagementSystem,关系数据库管理系统)应用软件。目前mysql已经成为最为流行的开源关
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Stella981 Stella981
2年前
ELK学习笔记之ElasticSearch的索引详解
0x00ElasticSearch的索引和MySQL的索引方式对比Elasticsearch是通过Lucene的倒排索引技术实现比关系型数据库更快的过滤。特别是它对多条件的过滤支持非常好,比如年龄在18和30之间,性别为女性这样的组合查询。倒排索引很多地方都有介绍,但是其比关系型
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
5个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这