面试中,MySQL 索引相关的问题基本都是一系列问题,都是先从索引的基本原理,再到索引的使用场景,比如:
今天就带大家,夯实 MySQL 索引的知识点。
当你想查阅书中某个知识的内容,你会选择一页一页的找呢?还是在书的目录去找呢?
傻瓜都知道时间是宝贵的,当然是选择在书的目录去找,找到后再翻到对应的页。书中的目录,就是充当索引的角色,方便我们快速查找书中的内容,所以索引是以空间换时间的设计思想。
那换到数据库中,索引的定义就是帮助存储引擎快速获取数据的一种数据结构,形象的说就是索引是数据的目录。
所谓的存储引擎,说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MySQL 存储引擎有 MyISAM 、InnoDB、Memory,其中 InnoDB 是在 MySQL 5.5 之后成为默认的存储引擎。
下图是 MySQL 的结构图,索引和数据就是位于存储引擎中:
你知道索引有哪些吗?大家肯定都能霹雳啪啦地说出聚簇索引、主键索引、二级索引、普通索引、唯一索引、hash索引、B+树索引等等。
然后再问你,你能将这些索引分一下类吗?可能大家就有点模糊了。其实,要对这些索引进行分类,要清楚这些索引的使用和实现方式,然后再针对有相同特点的索引归为一类。
我们可以按照四个角度来分类索引。
接下来,按照这些角度来说说各类索引的特点。
每一种存储引擎支持的索引类型不一定相同,我在表中总结了 MySQL 常见的存储引擎 InnoDB、MyISAM 和 Memory 分别支持的索引类型。
在创建表时,InnoDB 存储引擎会根据不同的场景选择不同的列作为索引:
其它索引都属于辅助索引(Secondary Index),也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。
为了让大家理解 B+Tree 索引的存储和查询的过程,接下来我通过一个简单例子,说明一下 B+Tree 索引在存储数据中的具体实现。
先创建一张商品表,id 为主键,如下:
商品表里,有这些行数据:
这些行数据,存储在 B+Tree 索引时是长什么样子的?
B+Tree 是一种多叉树,叶子节点才存放数据,非叶子节点只存放索引,而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中,因此在叶子节点中,包括了所有的索引值信息,并且每一个叶子节点都指向下一个叶子节点,形成一个链表。
比如,我们执行了下面这条查询语句,这条语句使用了主键索引查询 id 号为 5 的商品。查询过程是这样的,B+Tree 会自顶向下逐层进行查找:
数据库的索引和数据都是存储在硬盘的,我们可以把读取一个节点当作一次磁盘 I/O 操作。那么上面的整个查询过程一共经历了 3 个节点,也就是进行了 3 次 I/O 操作。
B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足,这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O,所以B+Tree 相比于 B 树和二叉树来说,最大的优势在于查询效率很高,因为即使在数据量很大的情况,查询一个数据的磁盘 I/O 依然维持在 3-4次。
我这里将前面的商品表中的 product_no (商品编码)字段设置为二级索引,那么二级索引的 B+Tree 如下图,其中非叶子的 key 值是 product_no(图中橙色部分),叶子节点存储的数据是主键值(图中绿色部分)。
如果我用 product_no 二级索引查询商品,如下查询语句:
会先检二级索引中的 B+Tree 的索引值(商品编码,product_no),找到对应的叶子节点,然后获取主键值,然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点,然后获取整行数据。这个过程叫「回表」,也就是说要查两个 B+Tree 才能查到数据。如下图:
不过,当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到,这时就不用再查主键索引查,比如下面这条查询语句:
这种在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」,也就是只需要查一个 B+Tree 就能找到数据。
前面已经讲了 B+Tree 的索引原理,现在就来回答一下 B+Tree 相比于 B 树、二叉树或 Hash 索引结构的优势在哪儿?
之前我也专门写过一篇文章,想详细了解的可以看这篇:「女朋友问我:为什么 MySQL 喜欢 B+ 树?我笑着画了 20 张图」,这里就简单做个比对。
B+Tree 只在叶子节点存储数据,而 B 树 的非叶子节点也要存储数据,所以 B+Tree 的单个节点的数据量更小,在相同的磁盘 I/O 次数下,就能查询更多的节点。
另外,B+Tree 叶子节点采用的是双链表连接,适合 MySQL 中常见的基于范围的顺序查找,而 B 树无法做到这一点。
对于有 N 个叶子节点的 B+Tree,其搜索复杂度为O(logdN),其中 d 表示节点允许的最大子节点个数为 d 个。
在实际的应用当中, d 值是大于100的,这样就保证了,即使数据达到千万级别时,B+Tree 的高度依然维持在 3~4 层左右,也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。
而二叉树的每个父节点的儿子节点个数只能是 2 个,意味着其搜索复杂度为 O(logN),这已经比 B+Tree 高出不少,因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。
Hash 在做等值查询的时候效率贼快,搜索复杂度为 O(1)。
但是 Hash 表不适合做范围查询,它更适合做等值的查询,这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。
从物理存储的角度来看,索引分为聚簇索引(主键索引)、二级索引(辅助索引)。
这两个区别在前面也提到了:
所以,在查询时使用了二级索引,如果查询的数据能在二级索引里查询的到,那么就不需要回表,这个过程就是覆盖索引。如果查询的数据不在二级索引里,就会先检索二级索引,找到对应的叶子节点,获取到主键值后,然后再检索主键索引,就能查询到数据了,这个过程就是回表。
从字段特性的角度来看,索引分为主键索引、唯一索引、普通索引、前缀索引。
主键索引就是建立在主键字段上的索引,通常在创建表的时候一起创建,一张表最多只有一个主键索引,索引列的值不允许有空值。
在创建表时,创建主键索引的方式如下:
唯一索引建立在 UNIQUE 字段上的索引,一张表可以有多个唯一索引,索引列的值必须唯一,但是允许有空值。
在创建表时,创建唯一索引的方式如下:
建表后,如果要创建唯一索引,可以使用这面这条命令:
普通索引就是建立在普通字段上的索引,既不要求字段为主键,也不要求字段为 UNIQUE。
在创建表时,创建普通索引的方式如下:
建表后,如果要创建普通索引,可以使用这面这条命令:
前缀索引是指对字符类型字段的前几个字符建立的索引,而不是在整个字段上建立的索引,前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
使用前缀索引的目的是为了减少索引占用的存储空间,提升查询效率。
在创建表时,创建前缀索引的方式如下:
建表后,如果要创建前缀索引,可以使用这面这条命令:
从字段个数的角度来看,索引分为单列索引、联合索引(复合索引)。
通过将多个字段组合成一个索引,该索引就被称为联合索引。比如将商品表中的 product_no 和 name 字段组合成联合索引 (product_no, name),创建联合索引的方式如下:
可以看到,联合索引的非叶子节点保持了两个字段的值作为 B+Tree 的 key 值。当在联合索引查询数据时,先按 product_no 字段比较,在 product_no 相同的情况下按 name 字段比较。
也就是说,联合索引查询的 B+Tree 是先按 product_no 进行排序,然后再 product_no 相同的情况再按 name 字段排序。因此,使用联合索引时,存在最左匹配原则,也就是按照最左优先的方式进行索引的匹配。
比如,如果创建了一个 (a, b, c) 联合索引,如果查询条件是以下这几种,就可以匹配上联合索引:
需要注意的是,因为有查询优化器,所以 a 字段在 where 子句的顺序并不重要。
但是,如果查询条件是以下这几种,因为不符合最左匹配原则,所以就无法匹配上联合索引,联合索引就会失效:
另外,建立联合索引时的字段顺序,对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高,实际开发工作中建立联合索引时,要把区分度大的字段排在前面,这样区分度大的字段越有可能被更多的 SQL 使用到。
区分度就是某个字段 column 不同值的个数「除以」表的总行数,计算公式如下:
比如,性别的区分度就很小,不适合建立索引或不适合排在联合索引列的靠前的位置,而 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。
这里出一个题目考查大家,针对针对下面这条 SQL,你怎么通过索引来提高查询效率呢?
有的同学会认为,单独给 status 建立一个索引就可以了。
但是更好的方式给 status 和 create_time 列建立一个联合索引,因为这样可以避免 MySQL 数据库发生文件排序。
所以,要利用索引的有序性,在 status 和 create_time 列建立联合索引,这样根据 status 筛选后的数据就是按照 create_time 排好序的,避免在文件排序,提高了查询效率。
索引最大的好处是提高查询速度,但是索引也是有缺点的,比如:
所以,索引不是万能钥匙,它也是根据场景来使用的。
这里说一下几种常见优化索引的方法:
前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引,那我们为什么需要使用前缀来建立索引呢?
使用前缀索引是为了减小索引字段大小,可以增加一个索引页中存储的索引值,有效提高索引的查询速度。在一些大字符串的字段作为索引时,使用前缀索引可以帮助我们减小索引项的大小。
不过,前缀索引有一定的局限性,例如:
覆盖索引是指 SQL 中 query 的所有字段,在索引 B+Tree 的叶子节点上都能找得到的那些索引,从二级索引中查询得到记录,而不需要通过聚簇索引查询获得,可以避免回表的操作。
假设我们只需要查询商品的名称、价格,有什么方式可以避免回表呢?
我们可以建立一个联合索引,即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据,查询将不会再次检索主键索引,从而避免回表。
所以,使用覆盖索引的好处就是,不需要查询出包含整行记录的所有信息,也就减少了大量的 I/O 操作。
我们在建表的时候,都会默认将主键索引设置为自增的,具体为什么要这样做呢?又什么好处?
InnoDB 创建主键索引默认为聚簇索引,数据被存放在了 B+Tree 的叶子节点上。也就是说,同一个叶子节点内的各个数据是按主键顺序存放的,因此,每当有一条新的数据插入时,数据库会根据主键将其插入到对应的叶子节点中。
如果我们使用自增主键,那么每次插入的新数据就会按顺序添加到当前索引节点的位置,不需要移动已有的数据,当页面写满,就会自动开辟一个新页面。因为不需要重新移动数据,因此这种插入数据的方法效率非常高。
如果我们使用非自增主键,由于每次插入主键的索引值都是随机的,因此每次插入新的数据时,就可能会插入到现有数据页中间的某个位置,这将不得不移动其它数据来满足新数据的插入,甚至需要从一个页面复制数据到另外一个页面,我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片,导致索引结构不紧凑,从而影响查询效率。
举个例子,假设某个数据页中的数据是1、3、5、9,且数据页满了,现在准备插入一个数据7,则需要把数据页分割为两个数据页:
出现页分裂时,需要将一个页的记录移动到另外一个页,性能会受到影响,同时页空间的利用率下降,造成存储空间的浪费。
而如果记录是顺序插入的,例如插入数据11,则只需开辟新的数据页,也就不会发生页分裂:
因此,在使用 InnoDB 存储引擎时,如果没有特别的业务需求,建议使用自增字段作为主键。
用上了索引并不意味着查询的时候会使用到索引,所以我们心里要清楚有哪些情况会导致索引失效,从而避免写出索引失效的查询语句,否则这样的查询效率是很低的。
我之前写过索引失效的文章,想详细了解的可以去看这篇文章:谁还没碰过索引失效呢?
这里简单说一下,发生索引失效的情况:
我上面说的是常见的索引失效场景,实际过程中,可能会出现其他的索引失效场景,这时我们就需要查看执行计划,通过执行计划显示的数据判断查询语句是否使用了索引。
如下图,就是一个没有使用索引,并且是一个全表扫描的查询语句。
对于执行计划,参数有:
type 字段就是描述了找到所需数据时使用的扫描方式是什么,常见扫描类型的执行效率从低到高的顺序为:
考虑到查询效率问题,全表扫描和全索引扫描要尽量避免。
这次主要介绍了索引的原理、分类和使用。我把重点总结在了下面这个表格
浅谈Mysql哪些字段适合建立索引
1、表的主键、外键必须有索引;
2、数据量超过300的表应该有索引;
3、经常与其他表进行连接的表,在连接字段上应该建立索引;
4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
5、索引应该建在选择性高的字段上;
6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
B 、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
D、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
8、频繁进行数据操作的表,不要建立太多的索引;
9、删除无用的索引,避免对执行计划造成负面影响;
以上是一些普遍的建立索引时的判断依据。
索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。
因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。 另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。
总的来说,小型表肯定不建索引,
或者数据库记录在亿条数据级以上,还是建议使用非关系型数据库。
还有些特殊字段的数据库,比如BLOB,CLOB字段肯定也不适合建索引。
其实这个问题更感觉偏向于做软件项目的一种经验。
首先,应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据,在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。
其次,在对建立索引的时候要对表进行加锁,因此应当注意操作在业务空闲的时候进行。
首当其冲的考虑因素便是磁盘I/O。物理上,应当尽量把索引与数据分散到不同的磁盘上(不考虑阵列的情况)。逻辑上,数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。
其次,我们知道,在建立索引的时候要对表进行全表的扫描工作,因此,应当考虑调大初始化参数db_file_multiblock_read_count的值。一般设置为32或更大。
再次,建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作,因此,应当调整排序区的大小。
最后,建立索引的时候,可以加上nologging选项。以减少在建立索引过程中产生的大量redo,从而提高执行的速度。
设计好MySql的索引可以让你的数据库飞起来,大大的提高数据库效率。设计MySql索引的时候有一下几点注意:
对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。
比如有一条语句是这样的:
如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效
率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,
特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。
3,索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%a%” 不会使用索引而like “aaa%”可以使用索引。
7,不要在列上进行运算
作为经常和 MySQL 打交道的数据分析师,必须来答!
关于 MySQL 的书籍还是不少的,我在这推荐一些我觉得不错的,从入门到进阶应有尽有!
对于 MySQL 来说,它的入门就是去学习 SQL 语法的过程。
这个时候就很简单,只需要买本书,然后看一下,最后就是找些练习题来训练,巩固自己的知识。
这个入门过程我建议就是一鼓作气,拿出一段连续的时间,进行高强度的学习,那大概 5 ~ 7 天就差不多。
当然前提就是“足够多的时间”且“学习效率在一般及以上”。
从入门到进阶,这个时间段才是需要花时间去精进的,这个阶段也比入门阶段更难,因为我们需要去了解它的底层实现原理。
下面我就分别来推荐一下,记得先帮我 点赞收藏,回头儿再看不迷路~
我在上面说过,MySQL 入门,主要还是对 SQL 语法的学习。
对于入门来说,我这里推荐两本书:《SQL 基础教程》、《SQL 必知必会》
这两本书的顺序呢,我建议是先《SQL 基础教程》
这本书介绍的节奏的更加平缓,并且用图示和关键字加粗更加生动地介绍知识,适合零基础的学生。
对于零基础的朋友来说 Mick 的《SQL 基础教程》更容易看懂学会,非常适合入门者学习。
看完《SQL 基础教程》以后,可以快速的翻阅 《SQL 必知必会》,其实这就是个查漏补缺和复习巩固的作用。
当然《SQL 必知必会》也可以不看,只是个可选项。
推荐这本书的原因呢,是《SQL 必知必会》有专门的练习平台供大家立马练习。只要有练习平台我都会吹爆!
前期都不需要在自己的电脑上安装 MySQL,只需要在这个学习的过程中,以及学完知识之后的时间里,就在一些在线的刷题网站上使劲练习,大量的练。
至于去哪练习,当然有很多的在线练习 SQL 的网站,我找了 6 个比较好的,推荐给大家,直接看下面的文章就好了!
在这个阶段的后期你可以尝试在电脑上安装 MySQL 了,当然软件的安装的过程中可能碰到很多玄学的事情。
这里推荐个视频给大家,知乎上直接可以看:
有些同学在学习之初就是喜欢看视频学,那其余的数据库介绍啊数据库操作啊等内容可以也顺便看看,差不多看到第 21 集就可以了:
MySQL 进阶阶段,我们要开始了解它底层实现原理。
这就和入门阶段不一样了,比较难且需要花的时间较多。
如果只是学完基础来看设计到底层实现的大部头,估计也会直接劝退了。
这里可以先看这本《MySQL 是怎样运行的》,用比较搞笑的方式对 MySQL 的底层运行原理进行了介绍,此外还带了很多的配图更加容易理解,从根儿上理解 MySQL。
前面的部分的“基础篇”可以快速略过,重点的就是后面的“开发篇和优化篇”。
尤其是后面的“优化篇”对我帮助很大,学习了一些常见的 MySQL 优化方式。
毕竟数据库性能优化是对我们来说很重要的一趴。
MySQL 中用的做多的就是 InnoDB 引擎,所以更进一步学习 InnoDB 存储引擎很有必要。
这本书反正就是 MySQL 中的经典书,解析了 InnoDB 的体系结构、实现原理、工作机制,里面呆了大量的实践内容。
MySQL 不老的经典大部头,这个大家就放在最后看好了。
这本书好的方面是,每一章都是相对独立的主题,大家可以根据目录选择性的阅读。
我记得当时我主要就是看了创建高性能的索引、查询性能优化这几个章节,不得不说:Nice!
说实话,现在网上的推荐这么多,有多少人是能跟着做下来的。
希望大家多多思考,能够静下心来好好去学,而不是收藏了就是会了。
祝大家早日学会 SQL,早日修成大佬!
相信下面的这些回答对你也会有帮助:
版权声明:文章内容来源于网络,版权归原作者所有,如有侵权请点击这里与我们联系,我们将及时删除。