精华内容
下载资源
问答
  • 一文搞懂MySQL索引所有知识点(建议收藏)

    万次阅读 多人点赞 2020-10-24 12:19:05
    Mysql索引 索引介绍 索引是什么 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。 一般来说索引本身也很大,不可能全部存储在内存中,因此...

    Mysql索引

    索引介绍

    索引是什么

    • 官方介绍索引是帮助MySQL高效获取数据数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度

    • 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往是存储在磁盘上的文件中的(可能存储在单独的索引文件中,也可能和数据一起存储在数据文件中)。

    • 我们通常所说的索引,包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等,没有特别说明,默认都是使用B+树结构组织(多路搜索树,并不一定是二叉的)的索引。

    索引的优势和劣势

    优势:

    • 可以提高数据检索的效率,降低数据库的IO成本,类似于书的目录。

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

      • 被索引的列会自动进行排序,包括【单列索引】和【组合索引】,只是组合索引的排序要复杂一些。
      • 如果按照索引列的顺序进行排序,对应order by语句来说,效率就会提高很多。

    劣势:

    • 索引会占据磁盘空间

    • 索引虽然会提高查询效率,但是会降低更新表的效率。比如每次对表进行增删改操作,MySQL不仅要保存数据,还有保存或者更新对应的索引文件。

    索引类型

    主键索引

    索引列中的值必须是唯一的,不允许有空值。

    普通索引

    MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值。

    唯一索引

    索引列中的值必须是唯一的,但是允许为空值。

    全文索引

    只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

    空间索引

    MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

    前缀索引

    在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时,可以指定索引列的长度,但是数值类型不能指定。

    其他(按照索引列数量分类)

    1. 单列索引

    2. 组合索引

      组合索引的使用,需要遵循最左前缀匹配原则(最左匹配原则)。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

    索引的数据结构

    Hash表

    Hash表,在Java中的HashMap,TreeMap就是Hash表结构,以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列,Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高,时间复杂度为O(1);但是不支持范围快速查找,范围查找时还是只能通过扫描全表方式。

    显然这种并不适合作为经常需要查找和范围查找的数据库索引使用。

    二叉查找树

    二叉树,我想大家都会在心里有个图。

    在这里插入图片描述

    二叉树特点:每个节点最多有2个分叉,左子树和右子树数据顺序左小右大。

    这个特点就是为了保证每次查找都可以这折半而减少IO次数,但是二叉树就很考验第一个根节点的取值,因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”,这就很难受很不稳定。

    在这里插入图片描述

    显然这种情况不稳定的我们再选择设计上必然会避免这种情况的

    平衡二叉树

    平衡二叉树是采用二分法思维,平衡二叉查找树除了具备二叉树的特点,最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡,不会出现左子树很高、右子树很矮的情况。

    使用平衡二叉查找树查询的性能接近于二分查找法,时间复杂度是 O(log2n)。查询id=6,只需要两次IO。

    在这里插入图片描述

    就这个特点来看,可能各位会觉得这就很好,可以达到二叉树的理想的情况了。然而依然存在一些问题:

    1. 时间复杂度和树高相关。树有多高就需要检索多少次,每个节点的读取,都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。磁盘每次寻道时间为10ms,在表数据量大时,查询性能就会很差。(1百万的数据量,log2n约等于20次磁盘IO,时间20*10=0.2s)

    2. 平衡二叉树不支持范围查询快速查找,范围查询时需要从根节点多次遍历,查询效率不高。

    B树:改造二叉树

    MySQL的数据是存储在磁盘文件中的,查询处理数据时,需要先把磁盘中的数据加载到内存中,磁盘IO 操作非常耗时,所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO,如果想要减少磁盘IO操作,就需要尽量降低树的高度。那如何降低树的高度呢?

    假如key为bigint=8字节,每个节点有两个指针,每个指针为4个字节,一个节点占用的空间16个字节(8+4*2=16)。

    因为在MySQL的InnoDB存储引擎一次IO会读取的一页(默认一页16K)的数据量,而二叉树一次IO有效数据量只有16字节,空间利用率极低。为了最大化利用一次IO空间,一个简单的想法是在每个节点存储多个元素,在每个节点尽可能多的存储数据。每个节点可以存储1000个索引(16k/16=1000),这样就将二叉树改造成了多叉树,通过增加树的叉树,将树从高瘦变为矮胖。构建1百万条数据,树的高度只需要2层就可以(1000*1000=1百万),也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了,查询数据的效率也就提高了。

    这种数据结构我们称为B树,B树是一种多叉平衡查找树,如下图主要特点:

    1. B树的节点中存储着多个元素,每个内节点有多个分叉。

    2. 节点中的元素包含键值和数据,节点中的键值从大到小排列。也就是说,在所有的节点都储存数据。

    3. 父节点当中的元素不会出现在子节点中。

    4. 所有的叶子结点都位于同一层,叶节点具有相同的深度,叶节点之间没有指针连接。

    在这里插入图片描述

    举个例子,在b树中查询数据的情况:

    假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块5。

    第一次磁盘IO:将磁盘块1加载到内存中,在内存中从头遍历比较,10<15,走左路,到磁盘寻址磁盘块2。

    第二次磁盘IO:将磁盘块2加载到内存中,在内存中从头遍历比较,7<10,到磁盘中寻址定位到磁盘块5。

    第三次磁盘IO:将磁盘块5加载到内存中,在内存中从头遍历比较,10=10,找到10,取出data,如果data存储的行记录,取出data,查询结束。如果存储的是磁盘地址,还需要根据磁盘地址到磁盘中取出数据,查询终止。

    相比二叉平衡查找树,在整个查找过程中,虽然数据的比较次数并没有明显减少,但是磁盘IO次数会大大减少。同时,由于我们的比较是在内存中进行的,比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景,所以使用B树构建索引可以很好的提升查询的效率。

    过程如图:

    在这里插入图片描述

    看到这里一定觉得B树就很理想了,但是前辈们会告诉你依然存在可以优化的地方:

    1. B树不支持范围查询的快速查找,你想想这么一个情况如果我们想要查找10和35之间的数据,查找到15之后,需要回到根节点重新遍历查找,需要从根节点进行多次遍历,查询效率有待提高。

    2. 如果data存储的是行记录,行的大小随着列数的增多,所占空间会变大。这时,一个页中可存储的数据量就会变少,树相应就会变高,磁盘IO次数就会变大。

    B+树:改造B树

    B+树,作为B树的升级版,在B树基础上,MySQL在B树的基础上继续改造,使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

    • B树:非叶子节点和叶子节点都会存储数据。
    • B+树:只有叶子节点才会存储数据,非叶子节点至存储键值。叶子节点之间使用双向指针连接,最底层的叶子节点形成了一个双向有序链表。

    在这里插入图片描述

    B+树的最底层叶子节点包含了所有的索引项。从图上可以看到,B+树在查找数据的时候,由于数据都存放在最底层的叶子节点上,所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系,但是从另一方面来说,由于数据都被放到了叶子节点,所以放索引的磁盘块锁存放的索引数量是会跟这增加的,所以相对于B树来说,B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况,在索引中数据满足了当前查询语句所需要的全部数据,此时只需要找到索引即可立刻返回,不需要检索到最底层的叶子节点。

    举个例子:

    • 等值查询:

    假如我们查询值等于9的数据。查询路径磁盘块1->磁盘块2->磁盘块6。

    1. 第一次磁盘IO:将磁盘块1加载到内存中,在内存中从头遍历比较,9<15,走左路,到磁盘寻址磁盘块2。

    2. 第二次磁盘IO:将磁盘块2加载到内存中,在内存中从头遍历比较,7<9<12,到磁盘中寻址定位到磁盘块6。

    3. 第三次磁盘IO:将磁盘块6加载到内存中,在内存中从头遍历比较,在第三个索引中找到9,取出data,如果data存储的行记录,取出data,查询结束。如果存储的是磁盘地址,还需要根据磁盘地址到磁盘中取出数据,查询终止。(这里需要区分的是在InnoDB中Data存储的为行数据,而MyIsam中存储的是磁盘地址。)

    过程如图:

    在这里插入图片描述

    • 范围查询:

    假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

    1. 首先查找值等于9的数据,将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样,发生了三次磁盘IO。

    2. 查找到15之后,底层的叶子节点是一个有序列表,我们从磁盘块6,键值9开始向后遍历筛选所有符合筛选条件的数据。

    3. 第四次磁盘IO:根据磁盘6后继指针到磁盘中寻址定位到磁盘块7,将磁盘7加载到内存中,在内存中从头遍历比较,9<25<26,9<26<=26,将data缓存到结果集。

    4. 主键具备唯一性(后面不会有<=26的数据),不需再向后查找,查询终止。将结果集返回给用户。

    在这里插入图片描述

    可以看到B+树可以保证等值和范围查询的快速查找,MySQL的索引就采用了B+树的数据结构。

    Mysql的索引实现

    介绍完了索引数据结构,那肯定是要带入到Mysql里面看看真实的使用场景的,所以这里分析Mysql的两种存储引擎的索引实现:MyISAM索引InnoDB索引

    MyIsam索引

    以一个简单的user表为例。user表存在两个索引,id列为主键索引,age列为普通索引

    CREATE TABLE `user`
    (
      `id`       int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(20) DEFAULT NULL,
      `age`      int(11)     DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      KEY `idx_age` (`age`) USING BTREE
    ) ENGINE = MyISAM
      AUTO_INCREMENT = 1
      DEFAULT CHARSET = utf8;
    

    在这里插入图片描述

    MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时,叶子节点中存储的键值为索引列的值,数据为索引所在行的磁盘地址。

    主键索引

    在这里插入图片描述

    表user的索引存储在索引文件user.MYI中,数据文件存储在数据文件 user.MYD中。

    简单分析下查询时的磁盘IO情况:

    根据主键等值查询数据:

    select * from user where id = 28;
    
    1. 先在主键树中从根节点开始检索,将根节点加载到内存,比较28<75,走左路。(1次磁盘IO)
    2. 将左子树节点加载到内存中,比较16<28<47,向下检索。(1次磁盘IO)
    3. 检索到叶节点,将节点加载到内存中遍历,比较16<28,18<28,28=28。查找到值等于30的索引项。(1次磁盘IO)
    4. 从索引项中获取磁盘地址,然后到数据文件user.MYD中获取对应整行记录。(1次磁盘IO)
    5. 将记录返给客户端。

    磁盘IO次数:3次索引检索+记录数据检索。

    在这里插入图片描述

    根据主键范围查询数据:

    select * from user where id between 28 and 47;
    
    1. 先在主键树中从根节点开始检索,将根节点加载到内存,比较28<75,走左路。(1次磁盘IO)

    2. 将左子树节点加载到内存中,比较16<28<47,向下检索。(1次磁盘IO)

    3. 检索到叶节点,将节点加载到内存中遍历比较16<28,18<28,28=28<47。查找到值等于28的索引项。

      根据磁盘地址从数据文件中获取行记录缓存到结果集中。(1次磁盘IO)

      我们的查询语句时范围查找,需要向后遍历底层叶子链表,直至到达最后一个不满足筛选条件。

    4. 向后遍历底层叶子链表,将下一个节点加载到内存中,遍历比较,28<47=47,根据磁盘地址从数据文件中获取行记录缓存到结果集中。(1次磁盘IO)

    5. 最后得到两条符合筛选条件,将查询结果集返给客户端。

    磁盘IO次数:4次索引检索+记录数据检索。

    在这里插入图片描述

    **备注:**以上分析仅供参考,MyISAM在查询时,会将索引节点缓存在MySQL缓存中,而数据缓存依赖于操作系统自身的缓存,所以并不是每次都是走的磁盘,这里只是为了分析索引的使用过程。

    辅助索引

    在 MyISAM 中,辅助索引和主键索引的结构是一样的,没有任何区别,叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的,而辅助索引的键值可以重复。

    查询数据时,由于辅助索引的键值不唯一,可能存在多个拥有相同的记录,所以即使是等值查询,也需要按照范围查询的方式在辅助索引树中检索数据。

    InnoDB索引

    主键索引(聚簇索引)

    每个InnoDB表都有一个聚簇索引 ,聚簇索引使用B+树构建,叶子节点存储的数据是整行记录。一般情况下,聚簇索引等同于主键索引,当一个表没有创建主键索引时,InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下:

    1. 在表上定义主键PRIMARY KEY,InnoDB将主键索引用作聚簇索引。
    2. 如果表没有定义主键,InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
    3. 如果以上两个都没有,InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

    除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB,辅助索引中的叶子节点存储的数据是该行的主键值都。 在检索时,InnoDB使用此主键值在聚簇索引中搜索行记录。

    这里以user_innodb为例,user_innodb的id列为主键,age列为普通索引。

    CREATE TABLE `user_innodb`
    (
      `id`       int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(20) DEFAULT NULL,
      `age`      int(11)     DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      KEY `idx_age` (`age`) USING BTREE
    ) ENGINE = InnoDB;
    

    在这里插入图片描述

    InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中。InnoDB的数据组织方式,是聚簇索引。

    主键索引的叶子节点会存储数据行,辅助索引只会存储主键值。

    在这里插入图片描述

    等值查询数据:

    select * from user_innodb where id = 28;
    
    1. 先在主键树中从根节点开始检索,将根节点加载到内存,比较28<75,走左路。(1次磁盘IO)

    2. 将左子树节点加载到内存中,比较16<28<47,向下检索。(1次磁盘IO)

    3. 检索到叶节点,将节点加载到内存中遍历,比较16<28,18<28,28=28。查找到值等于28的索引项,直接可以获取整行数据。将改记录返回给客户端。(1次磁盘IO)

      磁盘IO数量:3次。

    在这里插入图片描述

    辅助索引

    除聚簇索引之外的所有索引都称为辅助索引,InnoDB的辅助索引只会存储主键值而非磁盘地址。

    以表user_innodb的age列为例,age索引的索引结果如下图。

    在这里插入图片描述

    底层叶子节点的按照(age,id)的顺序排序,先按照age列从小到大排序,age列相同时按照id列从小到大排序。

    使用辅助索引需要检索两遍索引:首先检索辅助索引获得主键,然后使用主键到主索引中检索获得记录。

    画图分析等值查询的情况:

    select * from t_user_innodb where age=19;
    

    在这里插入图片描述

    根据在辅助索引树中获取的主键id,到主键索引树检索数据的过程称为回表查询。

    磁盘IO数:辅助索引3次+获取记录回表3次

    组合索引

    还是以自己创建的一个表为例:表 abc_innodb,id为主键索引,创建了一个联合索引idx_abc(a,b,c)。

    CREATE TABLE `abc_innodb`
    (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `a`  int(11)     DEFAULT NULL,
      `b`  int(11)     DEFAULT NULL,
      `c`  varchar(10) DEFAULT NULL,
      `d`  varchar(10) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      KEY `idx_abc` (`a`, `b`, `c`)
    ) ENGINE = InnoDB;
    

    select * from abc_innodb order by a, b, c, id;

    在这里插入图片描述

    组合索引的数据结构:

    在这里插入图片描述

    组合索引的查询过程:

    select * from abc_innodb where a = 13 and b = 16 and c = 4;
    

    在这里插入图片描述

    最左匹配原则:

    最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

    在组合索引树中,最底层的叶子节点按照第一列a列从左到右递增排列,但是b列和c列是无序的,b列只有在a列值相等的情况下小范围内递增有序,而c列只能在a,b两列相等的情况下小范围内递增有序。

    就像上面的查询,B+树会先比较a列来确定下一步应该搜索的方向,往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列,B+树就不知道第一步应该从哪个节点查起。

    可以说创建的idx_abc(a,b,c)索引,相当于创建了(a)、(a,b)(a,b,c)三个索引。、

    组合索引的最左前缀匹配原则:使用组合索引查询时,mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

    覆盖索引

    覆盖索引并不是说是索引结构,覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候,我们只可以拿到主键值,相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况,在上面abc_innodb表中的组合索引查询时,如果我只需要abc字段的,那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了,而不需要回表。这种情况就是覆盖索引

    可以看一下执行计划:

    覆盖索引的情况:

    在这里插入图片描述

    未使用到覆盖索引:

    在这里插入图片描述

    总结

    看到这里,你是不是对于自己的sql语句里面的索引的有了更多优化想法呢。比如:

    避免回表

    在InnoDB的存储引擎中,使用辅助索引查询的时候,因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引,索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能。那如何避免呢?

    使用索引覆盖,举个例子:现有User表(id(PK),name(key),sex,address,hobby…)

    如果在一个场景下,select id,name,sex from user where name ='zhangsan';这个语句在业务上频繁使用到,而user表的其他字段使用频率远低于它,在这种情况下,如果我们在建立 name 字段的索引的时候,不是使用单一索引,而是使用联合索引(name,sex)这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。

    这里就是一个典型的使用覆盖索引的优化策略减少回表的情况。

    联合索引的使用

    联合索引,在建立索引的时候,尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间,还可以更容易的使用到索引覆盖。试想一下,索引的字段越多,是不是更容易满足查询需要返回的数据呢。比如联合索引(a_b_c),是不是等于有了索引:a,a_b,a_b_c三个索引,这样是不是节省了空间,当然节省的空间并不是三倍于(a,a_b,a_b_c)三个索引,因为索引树的数据没变,但是索引data字段的数据确实真实的节省了。

    联合索引的创建原则,在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面,频繁使用代表索引利用率高,区分度高代表筛选粒度大,这些都是在索引创建的需要考虑到的优化场景,也可以在常需要作为查询返回的字段上增加到联合索引中,如果在联合索引上增加一个字段而使用到了覆盖索引,那我建议这种情况下使用联合索引。

    联合索引的使用

    1. 考虑当前是否已经存在多个可以合并的单列索引,如果有,那么将当前多个单列索引创建为一个联合索引。
    2. 当前索引存在频繁使用作为返回字段的列,这个时候就可以考虑当前列是否可以加入到当前已经存在索引上,使其查询语句可以使用到覆盖索引。
    展开全文
  • 一文搞懂mysql索引

    2020-08-27 23:12:47
    mysql索引索引是啥索引类型数据库数据存储MyISAMInnoDB聚集索引和非聚集索引聚集索引非聚集索引为何应该使用自增整型主键InnoDB非聚集索引回表查询索引覆盖联合索引最左匹配原则 索引是啥 索引是一种排好序的数据...

    索引是啥

    索引是一种排好序的数据结构,mysql使用这种结构能够高效获取数据

    索引类型

    1. 二叉树
      在一些情况下回退化为链表。
      在这里插入图片描述

    2. hash
      只能实现= 和in 查找,无法范围查找,可能有哈希冲突。查找复杂度低。
      在这里插入图片描述

    3. 红黑树
      树的高度在数据量大的情况下太高,效率较低。

    4. B树
      mysql使用B+树,非叶子节点不存储数据,只存储索引,叶子节点存储索引和数据,叶子节点之间有指针,方便范围查找。
      下面是B+树示意图
      在这里插入图片描述

    下面是B树的示意图
    在这里插入图片描述

    没有使用B树的原因:B树的非叶子节点也存储数据,导致存储的索引树的高度比较高,查找效率差。而且因为数据可能不是那么整齐,io的预读利用不上,io性能也较差。

    数据库数据存储

    在mysql安装路径下有data目录,这个目录下有以数据表为名称的目录。

    MyISAM

    在这里插入图片描述

    三种文件:
    ***.frm 数据表结构
    ***.MYI 数据表索引
    ***.MYD 数据表数据

    InnoDB

    ***.frm 数据表结构
    ***.ibd 数据表索引和数据,本身就是一个按照B+tree组织的数据结构文件。

    聚集索引和非聚集索引

    聚集索引

    索引叶子节点存储了数据。
    聚集索引

    非聚集索引

    索引叶子节点只存储数据存储指针。索引和数据分开存储。
    在这里插入图片描述

    为何应该使用自增整型主键

    1. 如果没有合适的主键,数据库会自己建立一个隐形自增列作为主键索引。
    2. 非整型主键尤其是字符串主键,存储比较耗空间,比较比较耗性能。
    3. 自增主键在添加到索引树的时候能减少树的平衡带来的性能损耗。

    InnoDB非聚集索引

    回表查询

    非聚集索引里面只有主键索引,需要再去主键索引再查一遍,拿到数据。

    索引覆盖

    如果查询的数据就在非聚集索引的键值里面,在非主键索引查一遍就拿到数据了,就不需要再去回表。

    联合索引

    多个字段组合成为一个索引键。
    在这里插入图片描述

    最左匹配原则

    使用联合索引,一定要按照建立索引的顺序,从最左边开始匹配。
    如果第一个索引子项没有,直接从后面的索引子项匹配,就不会走索引,因为在后面的索引项看来,索引不是排好序的,必须要全表扫描。

    展开全文
  • 一文搞懂MySQL索引(清晰明了)

    万次阅读 多人点赞 2021-02-02 17:30:43
    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 MySQL中常用的索引结构(索引底层的数据结构)有:B-TREE ,B+TREE ,HASH 等。 MySQL 的索引有两种分类方式:逻辑分类和物理...

    索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

    📌简单类比一下,数据库如同书籍,索引如同书籍目录,假如我们需要从书籍查找与 xx 相关的内容,我们可以直接从目录中查找,定位到 xx 内容所在页面,如果目录中没有 xx 相关字符或者没有设置目录(索引),那只能逐字逐页阅读文本查找,效率可想而知。

    ❓如果你不仅仅想了解索引,可看我上篇文章《MySQL体系构架、存储引擎和索引结构》

    1️⃣索引的优缺点

    索引可以大大提高MySQL的检索速度,为什么不对表中的每一个列创建一个索引呢?

    优点

    • 索引大大减小了服务器需要扫描的数据量,从而大大加快数据的检索速度,这也是创建索引的最主要的原因。
    • 索引可以帮助服务器避免排序和创建临时表
    • 索引可以将随机IO变成顺序IO
    • 索引对于InnoDB(对索引支持行级锁)非常重要,因为它可以让查询锁更少的元组,提高了表访问并发性
    • 关于InnoDB、索引和锁:InnoDB在二级索引上使用共享锁(读锁),但访问主键索引需要排他锁(写锁)
    • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
    • 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
    • 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
    • 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

    缺点

    • 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
    • 索引需要占物理空间,除了数据表占用数据空间之外,每一个索引还要占用一定的物理空间,如果需要建立聚簇索引,那么需要占用的空间会更大
    • 对表中的数据进行增、删、改的时候,索引也要动态的维护,这就降低了整数的维护速度
    • 如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
    • 对于非常小的表,大部分情况下简单的全表扫描更高效;

    2️⃣创建索引准则

    索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。

    应该创建索引的列

    • 经常需要搜索的列上,可以加快搜索的速度
    • 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构
    • 经常用在连接(JOIN)的列上,这些列主要是一外键,可以加快连接的速度
    • 经常需要根据范围(<,<=,=,>,>=,BETWEEN,IN)进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的
    • 经常需要排序(order by)的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
    • 经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

    不该创建索引的列

    • 对于那些在查询中很少使用或者参考的列不应该创建索引。
      若列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
    • 对于那些只有很少数据值或者重复值多的列也不应该增加索引。
      这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
    • 对于那些定义为text, image和bit数据类型的列不应该增加索引。
      这些列的数据量要么相当大,要么取值很少。
    • 当该列修改性能要求远远高于检索性能时,不应该创建索引。(修改性能和检索性能是互相矛盾的)

    3️⃣索引结构

    MySQL中常用的索引结构(索引底层的数据结构)有:B-TREE ,B+TREE ,HASH 等。这部分强烈建议看我上篇《MySQL体系构架、存储引擎和索引结构》,有更细致的讲解,以下我也会简要说明。

    B-TREE

    B-树就是B树,多路搜索树,树高一层意味着多一次的磁盘I/O,下图是3阶B树
    在这里插入图片描述
    B树的特征:

    • 关键字集合分布在整颗树中;
    • 任何一个关键字出现且只出现在一个结点中;
    • 搜索有可能在非叶子结点结束;
    • 其搜索性能等价于在关键字全集内做一次二分查找;
    • 自动层次控制;

    B+TREE

    B+树是B-树的变体,也是一种多路搜索树
    在这里插入图片描述
    B+树的特征:

    • 所有关键字都出现在叶子结点的链表中(稠密索引),且链表中的关键字恰好是有序的;
    • 不可能在非叶子结点命中;
    • 非叶子结点相当于是叶子结点的索引(稀疏索引),叶子结点相当于是存储(关键字)数据的数据层;
    • 每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历。
    • 更适合文件索引系统;

    HASH

    哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。
    在这里插入图片描述
    Hash索引仅仅能满足"=",“IN"和”<=>"查询,不能使用范围查询。也不支持任何范围查询,例如WHERE price > 100
      
    由于Hash索引比较的是进行Hash运算之后的Hash值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的Hash算法处理之后的Hash值的大小关系,并不能保证和Hash运算前完全一样。

    补充:索引存储在文件系统中

    索引是占据物理空间的,在不同的存储引擎中,索引存在的文件也不同。存储引擎是基于表的,以下分别使用MyISAM和InnoDB存储引擎建立两张表。
    存储引擎是基于表的,以下建立两张别使用MyISAM和InnoDB引擎的表,看看其在文件系统中对应的文件存储格式。
    存储引擎为MyISAM:

    • *.frm:与表相关的元数据信息都存放在frm文件,包括表结构的定义信息等
    • *.MYD:MyISAM DATA,用于存储MyISAM表的数据
    • *.MYI:MyISAM INDEX,用于存储MyISAM表的索引相关信息

    存储引擎为InnoDB:

    • *.frm:与表相关的元数据信息都存放在frm文件,包括表结构的定义信息等
    • *.ibd:InnoDB DATA,表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引,叶子节点存储索引和索引对应的数据

    4️⃣索引分类

    MySQL 的索引有两种分类方式:逻辑分类和物理分类。

    逻辑分类

    有多种逻辑划分的方式,比如按功能划分,按组成索引的列数划分等

    按功能划分

    • 主键索引:一张表只能有一个主键索引,不允许重复、不允许为 NULL;
     ALTER TABLE TableName ADD PRIMARY KEY(column_list); 
    
    • 唯一索引:数据列不允许重复,允许为 NULL 值,一张表可有多个唯一索引,索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
    CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
    # 或者
    ALTER TABLE TableName ADD UNIQUE (column_list); 
    
    • 普通索引:一张表可以创建多个普通索引,一个普通索引可以包含多个字段,允许数据重复,允许 NULL 值插入;
    CREATE INDEX IndexName ON `TableName`(`字段名`(length));
    # 或者
    ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length));
    
    • 全文索引:它查找的是文本中的关键词,主要用于全文检索。(篇幅较长,下文有独立主题说明)

    按列数划分

    • 单例索引:一个索引只包含一个列,一个表可以有多个单例索引。
    • 组合索引:一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则,即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

    物理分类

    分为聚簇索引和非聚簇索引(有时也称辅助索引或二级索引)

    聚簇索引和非聚簇索引

    聚簇是为了提高某个属性(或属性组)的查询速度,把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。

    聚簇索引(clustered index)不是单独的一种索引类型,而是一种数据存储方式。这种存储方式是依靠B+树来实现的,根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时,方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块,找到索引也就找到了数据。

    非聚簇索引:数据和索引是分开的,B+树叶子节点存放的不是数据表的行记录。

    虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引,但是只有InnoDB的主键索引才是聚簇索引,InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

    拓展:聚簇索引优缺点

    优点:

    • 数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快
    • 聚簇索引对于主键的排序查找和范围查找速度非常快

    缺点:

    • 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键(主键列不要选没有意义的自增列,选经常查询的条件列才好,不然无法体现其主键索引性能)
    • .更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。
    • 二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

    【补充】Mysql中key 、primary key 、unique key 与index区别

    key 与 index 含义

    key具有两层含义:1.约束(约束和规范数据库的结构完整性)2.索引

    index:索引

    key 种类

    key:等价普通索引 key 键名 (列)

    primary key:

    • 约束作用(constraint),主键约束(unique,not null,一表一主键,唯一标识记录),规范存储主键和强调唯一性
    • 为这个key建立主键索引

    unique key:

    • 约束作用(constraint),unique约束(保证列或列集合提供了唯一性)
    • 为这个key建立一个唯一索引;

    foreign key:

    • 约束作用(constraint),外键约束,规范数据的引用完整性
    • 为这个key建立一个普通索引;

    实战分析

    建立个user表,看看表的各个字段,下面我们逐一分析

    mysql> create table user(
        -> id int auto_increment,
        -> username varchar(100) not null,
        -> user_id int(8) primary key,
        -> depart_no int not null,
        -> corp varchar(100),
        -> phone char(11),
        -> key auto_id (id),
        -> unique key phone (phone),
        -> index username_depart_corp (username,depart_no,corp),
        -> constraint fk_user_depart foreign key(depart_no) references depart(id);
        -> )engine=innodb charset=utf8;
    

    auto_increment修饰的字段需要是一个候选键,需要用key指定,否则报错。我们看下表的结构:
    在这里插入图片描述

    查看表的索引
    在这里插入图片描述

    可见key也会生成索引

    key 值类型

    • PRI 主键约束
    • UNI 唯一约束
    • MUL 可以重复

    在这里插入图片描述
    如果一个Key有多个约束,将显示约束优先级最高的, PRI>UNI>MUL

    5️⃣InnoDB和MyISAM索引实现

    InnoDB索引实现

    InnoDB使用B+TREE存储数据,除了主键索引为聚簇索引,其它索引均为非聚簇索引。

    一个表中只能存在一个聚簇索引(主键索引),但可以存在多个非聚簇索引。

    InnoDB表的索引和数据是存储在一起的,.idb表数据和索引的文件
    在这里插入图片描述

    聚簇索引(主键索引)

    B+树 叶子节点包含数据表中行记录就是聚簇索引(索引和数据是存放在一块的)

    InnoDB主键索引的示意图

    在这里插入图片描述
    可以看到叶子节点包含了完整的数据记录,这就是聚簇索引。因为InnoDB的数据文件(.idb)按主键聚集,所以InnoDB必须有主键(MyISAM可以没有),如果没有显示指定主键,则选取首个为唯一且非空的列作为主键索引,如果还没具备,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。


    主键索引结构分析:

    • B+树单个叶子节点内的行数据按主键顺序排列,物理空间是连续的(聚簇索引的数据的物理存放顺序与索引顺序是一致的);
    • 叶子节点之间是通过指针连接,相邻叶子节点的数据在逻辑上也是连续的(根据主键值排序),实际存储时的数据页(叶子节点)可能相距甚远。

    非聚簇索引(辅助索引或二级索引)

    在聚簇索引之外创建的索引(不是根据主键创建的)称之为辅助索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行数据记录,而是主键值。首先通过辅助索引找到主键值,然后到主键索引树中通过主键值找到数据行。

    InnoDB辅助索引的示意图

    在这里插入图片描述

    拓展:InnoDB索引优化

    • InnoDB中主键不宜定义太大,因为辅助索引也会包含主键列,如果主键定义的比较大,其他索引也将很大。如果想在表上定义 、很多索引,则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

    • InnoDB中尽量不使用非单调字段作主键(不使用多列),因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

    MyISAM索引实现

    MyISAM也使用B+Tree作为索引结构,但具体实现方式却与InnoDB截然不同。MyISAM使用的都是非聚簇索引。

    MyISAM表的索引和数据是分开存储的,.MYD表数据文件 .MYI表索引文件
    在这里插入图片描述

    MyISAM主键索引

    MyISAM主键索引的原理图

    在这里插入图片描述

    可以看到叶子节点的存放的是数据记录的地址。也就是说索引和行数据记录是没有保存在一起的,所以MyISAM的主键索引是非聚簇索引。

    MyISAM辅助索引

    在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。 MyISAM辅助索引也是非聚簇索引。

    InnoDB和MyISAM的索引检索过程

    对于InnoDB和MyISAM而言,主键索引是根据主关键字来构建的B+树存储结构,辅助索引则是根据辅助键来构造的B+树存储结构,彼此的索引树都是相互独立的。

    InnoDB辅助索引的访问需要两次索引查找,第一次从辅助索引树找到主键值,第二次根据主键值到主键索引树中找到对应的行数据。

    MyISM使用的是非聚簇索引,表数据存储在独立的地方,这两棵(主键和辅助键)B+树的叶子节点都使用一个地址指向真正的表数据。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。

    假想一个表如下图存储了4行数据。其中Id作为主索引,Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。
    在这里插入图片描述

    聚簇索引和非聚簇索引的区别

    • 聚簇索引的叶子节点存放的是数据行(主键值也是行内数据),支持覆盖索引;而二级索引的叶子节点存放的是主键值或指向数据行的指针。
    • 由于叶子节点(数据页)只能按照一棵B+树排序,故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织,所以一张表可以有多个辅助索引。

    6️⃣操作索引

    创建索引

    索引名称 index_name 是可以省略的,省略后,索引的名称和索引列名相同。

    -- 创建普通索引 
    CREATE INDEX index_name ON table_name(col_name);
    
    -- 创建唯一索引
    CREATE UNIQUE INDEX index_name ON table_name(col_name);
    
    -- 创建普通组合索引
    CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
    
    -- 创建唯一组合索引
    CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
    

    修改表结构创建索引

    ALTER TABLE table_name ADD INDEX index_name(col_name);
    

    创建表时直接指定索引

    CREATE TABLE table_name (
        ID INT NOT NULL,
        col_name VARCHAR (16) NOT NULL,
        INDEX index_name (col_name)
    );
    

    删除索引

    -- 直接删除索引
    DROP INDEX index_name ON table_name;
    
    -- 修改表结构删除索引
    ALTER TABLE table_name DROP INDEX index_name;
    

    其它相关命令

    -- 查看表结构
    desc table_name;
    
    -- 查看生成表的SQL
    show create table table_name;
    
    -- 查看索引信息(包括索引结构等)
    show index from  table_name;
    
    -- 查看SQL执行时间(精确到小数点后8位)
    set profiling = 1;
    SQL...
    show profiles;
    

    在这里插入图片描述

    7️⃣索引实战

    索引实战学习的基础,首先应该学会分析SQL的执行,使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,下面我们学习下EXPLAIN。

    explain

    使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理SQL语句。不展开讲解,大家可自行百度这块知识点。

    使用格式:EXPLAIN SQL...;

    Look一下EXPLAIN 查询结果包含的字段(v5.7)

    mysql> explain select * from student;
    +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
    | id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
    +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
    +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
    
    • id:选择标识符
    • select_type:表示查询的类型。
    • table:输出结果集的表
    • partitions:匹配的分区
    • type:表示表的连接类型
    • possible_keys:表示查询时,可能使用的索引
    • key:表示实际使用的索引
    • key_len:索引字段的长度
    • ref:列与索引的比较
    • rows:扫描出的行数(估算的行数)
    • filtered:按表条件过滤的行百分比
    • Extra:执行情况的描述和说明

    Extra 探究

    本打算展开讲一下Extra的常见的几个值:Using index,Using index condition,Using where,其中Using index 表示使用了覆盖索引,其它的都不好总结。翻阅网上众多博文,目前暂未看到完全总结到位的,且只是简单的查询条件下也是如此。我本打算好好总结一番,发现半天过去了,坑貌似越来越大,先打住,因为我目前没太多时间研究…

    我这有个简单的表结构,有兴趣的同学可以多尝试总结(注意 玩总结的,不能只考虑简单查询的情况)

    create table student(
     id int auto_increment primary key,
     name varchar(255) not null,
     c_id int,
     phone char(11),
     guardian varchar(50) not null,
     qq varchar(20) not null,
     index stu_class_phone (name,c_id,phone),
     index qq (qq)
    )engine=innodb charset=utf8;
    

    这里有我的一个比对关键项表,或许对有心探索的同学有点帮助,估计看了也有点懵,建议先尝试后再回头看我这个表。
    在这里插入图片描述

    key_len讲解

    我也稍微讲解一下网文中鲜有提及key_len字节长度计算规则,
    在这里插入图片描述

    mysql> explain select * from student where name='Joe';
    +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
    | id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
    +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | student | NULL       | ref  | stu_class_phone | stu_class_phone | 767     | const |    1 |   100.00 | NULL  |
    +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> explain select * from student where name='Joe' and c_id=2;
    +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
    | id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra |
    +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
    |  1 | SIMPLE      | student | NULL       | ref  | stu_class_phone | stu_class_phone | 772     | const,const |    1 |   100.00 | NULL  |
    +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> explain select * from student where name='Joe' and c_id=2 and phone='13500000000';
    +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+
    | id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref               | rows | filtered | Extra |
    +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | student | NULL       | ref  | stu_class_phone | stu_class_phone | 806     | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    • 如果表结构未限制某列为非空,那么MySQL将会使用一个字节来标识该列对应的值是否为NULL;限定非空,不止not null,还有primary key等隐含非空约束。

    • 字符串类型括号内的数字并不是字节数,而是字符长度,一个字符占几个字节与建表选用的字符集有关,如果表使用的是utf8字符集,那么一个字符占3个字节;注意,对于可变长字符串类(varchar)型的实际占用字节数,除了需要考虑设置了非空与否的那个字节,还要使用2个字节来记录字符串的长度。定长字符串类型(char)则不用额外字节记录长度

    • 整数类型括号内的数字无论是什么,都不影响它实际的字节数,int就是4个字节。int(xx),xx只是填充占位符,一般配合zerofill使用,只是一种标识,没有多大用处。

    观察三次Explain 的查询结果,留意key_len与where搜索键的微妙关系,如果type列的值是ref时,ref列的值标识索引参考列的形参。

    首先,我们看到key列为stu_class_phone ,说明该查询使用了stu_class_phone索引,这是一个组合索引(name,c_id,phone)。看下这三个字段的结构声明与实际字节计算:

    • name varchar(255) not null, (占767字节)
      • ①255字长(utf8字符集,一个字长3字节 )255*3=765 √
      • ②是否非空 已限定非空(not null) 那就不额外占1字节
      • ③字符串长度 str_len占2字节√
    • c_id int,(占5字节)
      • ①是否非空 未限定非空 那将额外占1字节 √
      • ②int 占4字节√
    • phone char(11),(占36字节)
      • ①11字长(utf8字符集,一个字长3字节 )11*3=33√

    int(xx) xx无论是多少 int永远4字节 xx只是填充占位符(一种标识 一般配合zerofill使用的)

    组合索引满足最左前缀原则就会生效,我们看到三次Explain 的查询中stu_class_phone索引都生效了,第一次采用name构建索引树,第二次采用name+c_id构建索引树,第三次采用name+c_id+phone构建索引树。第一次:key_len就是name的存储字节数,767;第二次:key_len就是name+c_id的存储字节数,767+5=772;第三次:255 * 3 + 2 + 5 + 11 * 3 + 1 = 806

    我们再看一条执行计划:

    mysql> explain select * from student where name='Joe' and phone ='13500000000';
    +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
    | id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra                 |
    +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
    |  1 | SIMPLE      | student | NULL       | ref  | stu_class_phone | stu_class_phone | 767     | const |    1 |    50.00 | Using index condition |
    +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
    

    为什么不是255 * 3 + 11 * 3 + 1 +2=801;却是767?我们看下ref为const,说明只有一个索引键生效,明显就是name,因为 不符合最左前缀原则,phone列被忽视了;也可能是mysql做了优化,发现通过name和phone构建的索引树对查询列 (* 表示全部列)并没有加快了查询速率,自行优化,减少键长。

    拓展:优秀的索引是什么样的?

    • 键长 短
    • 精度 高

    比如,在保证查询精度的情况下,两个索引的key_len分别为10字节和100字节,数据行的量也一样(大数据量效果更佳),100字节索引检索的时间会比10字节的要多;再者,一个磁盘页能存储的10字节的索引记录的量是100字节的10倍。

    最左前缀原则

    在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先(查询条件精确匹配索引的左边连续一列或几列,则构建对应列的组合索引树),在检索数据时也从联合索引的最左边开始匹配。

    为了方便讲解,我写了点个人理解的概念声明,如下图:
    在这里插入图片描述

    mysql> create table t(
        -> a int not null,
        -> b char(10) not null,
        -> c int not null,
        -> d varchar(20) not null,
        -> index abc(a,b,c)
        -> )engine=innodb charset=utf8;
        
    mysql> insert into t values(1,'hello',1,'world');
    mysql> insert into t values(2,'hello',2,'mysql');
    

    以下均为筛选条件不包含主键索引情况下:(主键索引优先级最高)

    • 只要筛选条件中含有组合索引最左边的列但不含有主键搜索键的时候,至少会构建包含组合索引最左列的索引树。(如:index(a))
      在这里插入图片描述

    • 查询列都是组合索引列且筛选条件全是组合索引列时,会构建满列组合索引树(index(a,b,c) )【覆盖索引】
      在这里插入图片描述

    • 筛选条件包含普通搜索键但没包含组合索引列最左键,不会构建组合索引树
      在这里插入图片描述

    • 如果筛选条件全是组合索引最左连续列作为搜索键,将构建连续列组合索引树。(比如:index(a,b)却不能index(a,c))
      在这里插入图片描述

    • MySQL查询优化器会优化and连接,将组合索引列规则排号。(比如:b and a 等同于 a and b)
      在这里插入图片描述

    前缀索引

    有时候需要索引很长的字符列,这会让索引变得大且慢。通常可以以某列开始的部分字符作为索引,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指不重复的索引值和数据表的记录总数的比值,索引的选择性越高则查询效率越高。

    以下是一个百万级数据表的简化呈现
    在这里插入图片描述
    图一 area 字段没有设置为索引,图二 area 字段设置为前4字符作为索引,图三 area 字段设置前5字符作为索引,当数据是百万当量时候,毫无疑问,图三的索引速度将大大优越于前两个图场景。

    CREATE TABLE `x_test` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `x_name` varchar(255) NOT NULL,
      PRIMARY KEY (`id`),
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8
    

    通过存储过程插入10万数据(不建议使用此方法,太慢了)

    DROP PROCEDURE IF EXISTS proc_initData;
    DELIMITER $
    CREATE PROCEDURE proc_initData()
    BEGIN
        DECLARE i INT DEFAULT 1;
        WHILE i<=100000 DO
            INSERT INTO x_test VALUES(null,RAND()*100000);
            SET i = i+1;
        END WHILE;
    END $
    CALL proc_initData();
    

    在这里插入图片描述

    不使用索引查询某条记录
    在这里插入图片描述

    使用索引查询某条记录

    alter table x_test add index(x_name(4));
    

    在这里插入图片描述

    前缀字符并非越多越好,需要在索引的选择性和索引IO读取量中做出衡量。

    覆盖索引与回表

    上文我们介绍过索引可以划分为聚簇索引和辅助索引。在InnoDB中的主键索引就是聚簇索引,主键索引的查询效率也是非常高的,除此之外,还有非聚簇索引,其查询效率稍逊。覆盖索引其形式就是,搜索的索引键中的字段恰好是查询的字段(或是组合索引键中的其它字段)。覆盖索引的查询效率极高,原因在与其不用做回表查询。

    student表中存在组合索引 stu_class_phone(name,c_id,phone),student表结构如下:

    mysql> desc student;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | id       | int(11)      | NO   | PRI | NULL    | auto_increment |
    | name     | varchar(255) | NO   | MUL | NULL    |                |
    | c_id     | int(11)      | YES  |     | NULL    |                |
    | phone    | char(11)     | YES  |     | NULL    |                |
    | guardian | varchar(50)  | NO   |     | NULL    |                |
    +----------+--------------+------+-----+---------+----------------+
    

    最直观的呈现:(通过explain执行分析SQL可观测到Extra字段值包含Using index)
    在这里插入图片描述
    当然对于组合索引你还可以查询组合索引键中的其他字段:
    在这里插入图片描述
    但是不能包含杂质搜索键(不属于所搜索索引中的列)
    在这里插入图片描述

    典型使用场景: 全表count查询,根据某关键字建立索引,直接count(关键字)即可,如果是count() 则需要回表搜索。(此项做保留,近期发现count() 也是使用了using index,有可能是新版本mysql内部做了优化处理)

    alter table student add key(name);
    
    mysql> explain select count(name) from student;
    +----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | student | NULL       | index | NULL          | name | 767     | NULL |    1 |   100.00 | Using index |
    +----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
    
    

    回表

    查询的列数据作为索引树的键值,直接在索引树中得到反馈(存在于索引节点),不用遍历如InnoDB中的叶子节点(存放数据表各行数据)就可得到查询的数据(不用回表)。

    下面以InnoDB表中的辅助索引作图示说明:
    在这里插入图片描述

    全文索引 FULLTEXT

    通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询,但是,如果希望通过关键字的匹配来进行查询过滤,那么就需要基于相似度的查询,而不是原来的精确数值比较。全文索引,就是为这种场景设计的,通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题。

    FULLTEXT VS LIKE+%

    使用LIKE+%确实可以实现模糊匹配,适用于文本比较少的时候。对于大量的文本检索,LIKE+%与全文索引的检索速度相比就不是一个数量级的。

    例如: 有title字段,需要查询所有包含 "政府"的记录.,使用 like "%政府%“方式查询,查询速度慢(全表查询)。且当查询包含"政府” OR "中国"的字段时,使用like就难以简单满足,而全文索引就可以实现这个功能。

    FULLTEXT的支持情况

    • MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;
    • MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
    • 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引

    InnoDB内部并不支持中文、日文等,因为这些语言没有分隔符。可以使用插件辅助实现中文、日文等的全文索引。

    创建全文索引

    //建表的时候
    FULLTEXT KEY keyname(colume1,colume2)  // 创建联合全文索引列
    
    //在已存在的表上创建
    create fulltext index keyname on xxtable(colume1,colume2);
    
    alter table xxtable add fulltext index keyname (colume1,colume2);
    

    使用全文索引

    全文索引有独特的语法格式,需要配合match 和 against 关键字使用

    • match()函数中指定的列必须是设置为全文索引的列
    • against()函数标识需要模糊查找的关键字
     create table fulltext_test(
         id int auto_increment primary key,
         words varchar(2000) not null,a
         artical text not null,
         fulltext index words_artical(words,artical)
    )engine=innodb default charset=utf8;
    
    insert into fulltext_test values(null,'a','a');
    insert into fulltext_test values(null,'aa','aa');
    insert into fulltext_test values(null,'aaa','aaa');
    insert into fulltext_test values(null,'aaaa','aaaa');
    

    好,我们使用全文搜索查看一下

    select * from fulltext_test where match(words,artical) against('a');
    select * from fulltext_test where match(words,artical) against('aa');
    select * from fulltext_test where match(words,artical) against('aaa');
    select * from fulltext_test where match(words,artical) against('aaaa');
    

    发现只有aaa和aaaa才能查到记录,为什么会这样呢?
    在这里插入图片描述

    全文索引关键词长度阈值

    这其实跟全文搜索的关键词的长度阈值有关,可以通过show variables like '%ft%';查看。可见InnoDB的全文索引的关键词 最小索引长度 为3。上文使用的是InnoDB引擎建表,同时也解释为什么只有3a以上才有搜索结果。
    在这里插入图片描述
    设置关键词长度阈值,可以有效的避免过短的关键词,得到的结果过多也没有意义。

    也可以手动配置关键词长度阈值,修改MySQL配置文件,在[mysqld]的下面追加以下内容,设置关键词最小长度为5。

    [mysqld]
    innodb_ft_min_token_size = 5
    ft_min_word_len = 5
    

    然后重启MySQL服务器,还要修复索引,不然参数不会生效

    repair table 表名 quick;
    

    为什么上文搜索关键字为aaa的时候,有且仅有一条aaa的记录,为什么没有aaaa的记录呢?

    全文索引模式

    自然语言的全文索引 IN NATURAL LANGUAGE MODE

    默认情况下,或者使用 IN NATURAL LANGUAGE MODE 修饰符时,match() 函数对文本集合执行自然语言搜索,上面的例子都是自然语言的全文索引。

    自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里,相关度是基于匹配的关键词的个数,以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语,匹配时的相关度就越高。

    MySQL在全文查询中会对每个合适的词都会先计算它们的权重,如果一个词出现在多个记录中,那它只有较低的权重;相反,如果词是较少出现在这个集的文档中,它将得到一个较高的权重。

    MySQL默认的阀值是50%。如果一个词语的在超过 50% 的记录中都出现了,那么自然语言的搜索将不会搜索这类词语。

    上文关键词长度阈值是3,所以相当于只有两条记录:aaa 和 aaaa
    aaa 权重 2/2=100% 自然语言的搜索将不会搜索这类词语aaa了 而是进行精确查找 aaaa不会出现在aaa的结果集中。

    这个机制也比较好理解,比如一个数据表存储的是一篇篇的文章,文章中的常见词、语气词等等,出现的肯定比较多,搜索这些词语就没什么意义了,需要搜索的是那些文章中有特殊意义的词,这样才能把文章区分开。

    select * from fulltext_test where match(words,artical) against('aaa');
    等价
    select * from fulltext_test where match(words,artical) against('aaa'  IN NATURAL LANGUAGE MODE);
    

    布尔全文索引 IN BOOLEAN MODE

    在布尔搜索中,我们可以在查询中自定义某个被搜索的词语的相关性,这个模式和lucene中的BooleanQuery很像,可以通过一些操作符,来指定搜索词在结果中的包含情况。

    建立如下表:

    CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT (title,body)
    ) ENGINE=InnoDB
    
    • + (AND)全文索引列必须包含该词且全文索引列(之一)有且仅有该词

    • - (NOT)表示必须不包含,默认为误操作符。如果只有一个关键词且使用了- ,会将这个当成错误操作,相当于没有查询关键词;如果有多个关键词,关键词集合中不全是负能符(~ -),那么-则强调不能出现。

    -- 查找title,body列中有且仅有apple(是apple不是applexx 也不是 xxapple)但是不含有banana的记录
    SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+apple -banana' IN BOOLEAN MODE);
    
    • > 提高该词的相关性,查询的结果靠前
    • < 降低该词的相关性,查询的结果靠后
    -- 返回同时包含apple(是apple不是applexx 也不是 xxapple)和banana或者同时包含apple和orange的记录。但是同时包含apple和banana的记录的权重高于同时包含apple和orange的记录。
    SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE);   
    
    • ~ 异或,如果包含则降低关键词整体的相关性
    -- 返回的记录必须包含apple(且不能是applexx 或 xxapple),但是如果同时也包含banana会降低权重(只出现apple的记录会出现在结果集前面)。但是它没有 +apple -banana 严格,因为后者如果包含banana压根就不返回。
    SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+apple ~banana' IN BOOLEAN MODE);
    
    • * 通配符,表示关键词后面可以跟任意字符
    -- 返回的记录可以为applexx
    SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('apple*' IN BOOLEAN MODE);
    
    • 空格 表示OR
    -- 查找title,body列中包含apple(是apple不是applexx 也不是 xxapple)或者banana的记录,至少包含一个
    SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('apple banana' IN BOOLEAN MODE);  
    
    • "" 双引号,效果类似like '%some words%'
    -- 模糊匹配 “apple banana goog”会被匹配到,而“apple good banana”就不会被匹配
    SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"apple banana"' IN BOOLEAN MODE);  
    

    InnoDB中FULLTEXT中文支持

    InnoDB内部并不支持中文、日文等,因为这些语言没有分隔符。可以使用插件辅助实现中文、日文等的全文索引。

    MySQL内置ngram插件便可解决该问题。

     FULLTEXT (title, body) WITH PARSER ngram
    ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;
    CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;
    

    索引失效

    数据库表中添加索引后确实会让查询速度起飞,但前提必须是正确的使用索引来查询,如果以错误的方式使用,则即使建立索引也会不奏效。即使建立索引,索引也不会生效:

     create table tb1(
         nid int auto_increment primary key,
         name varchar(100) not null,
         email varchar(100) not null,
         num int,
         no_index char(10),
         index(name),
         index(email),
         index(num)
    )engine=innodb;
    

    以下说明都 排除覆盖索引 情况下:

    > < 范围查询

    mysql 会一直向右匹配直到遇到索引搜索键使用>、<就停止匹配。一旦权重最高的索引搜索键使用>、<范围查询,那么其它>、<搜索键都无法用作索引。即索引最多使用一个>、<的范围列,因此如果查询条件中有两个>、<范围列则无法全用到索引。

    like %xx

    如搜索键值以通配符%开头(如:like '%abc'),则索引失效,直接全表扫描;若只是以%结尾,则不影响索引构建。

    mysql> explain select * from tb1 where name like '%oe';
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | tb1   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    

    对索引列进行运算

    如果查询条件中含有函数或表达式,将导致索引失效而进行全表扫描。 select * from user where YEAR(birthday) < 1990

    mysql> explain select * from tb1 where length(name)>2;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | tb1   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    

    or 条件索引问题

    or 的条件列除了同时是主键的时候,索引才会生效。其他情况下的,无论条件列是什么,索引都失效。

    mysql> explain select * from tb1 where nid=1 or nid=2;
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | tb1   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> explain select * from tb1 where name='Joe' or name='Tom';
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | tb1   | NULL       | ALL  | name          | NULL | NULL    | NULL |    3 |    66.67 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    

    数据类型不一致(隐式类型转换导致的索引失效)

    如果列是字符串类型,传入条件是必须用引号引起来,不然报错或索引失效。

    mysql> explain select * from tb1 where name=12;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | tb1   | NULL       | ALL  | name          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    

    != 问题

    普通索引使用 !=索引失效,主键索引没影响。
    where语句中索引列使用了负向查询,可能会导致索引失效。
    负向查询包括:NOT、!=、<>、NOT IN、NOT LIKE等。

    mysql> explain select * from tb1 where name!='Joe';
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | tb1   | NULL       | ALL  | name          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    

    联合索引违背最左匹配原则

    联合索引中,where中索引列违背最左匹配原则,一定会导致索引失效(上文有说)

    order by问题

    order by 对主键索引排序会用到索引,其他的索引失效

    mysql> explain select * from tb1 order by name;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
    |  1 | SIMPLE      | tb1   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using filesort |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> explain select * from tb1 order by nid;
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
    | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | tb1   | NULL       | index | NULL          | PRIMARY | 4       | NULL |    3 |   100.00 | NULL  |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
    

    【主键妙用】LIMIT分页

    若需求是每页显示10条数据,如何建立分页?

    我们可以先使用LIMIT尝试:

    --第一页
    SELECT * FROM table_name LIMIT 0,10;
    --第二页
    SELECT * FROM table_name LIMIT 10,10;
    --第三页
    SELECT * FROM table_name LIMIT 20,10;
    

    但是这样做有如下弊端:
    每一条select语句都会从1遍历至当前位置,若跳转到第100页,则会遍历1000条记录

    改善:
    若已知每页的max_id和min_id,则可以通过主键索引来快速定位:

    --下一页
    SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id > max_id LIMIT 10);
    --上一页
    SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id < min_id ORDER BY id DESC LIMIT 10);
    --当前页之后的某一页
    SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id < min_id ORDER BY id desc LIMIT (页数差*10)) AS N ORDER BY N.id ASC LIMIT 10) AS P ORDER BY P.id ASC);
    --当前页之前的某一页
    SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id > max_id LIMIT (页数差*10)) AS N ORDER BY N.id DESC LIMIT 10) AS P) ORDER BY id ASC;
    

    可能版本会不支持:This version of MySQL doesn't yet support 'LIMIT 只要加多一个子查询即可

    SELECT * FROM x_test WHERE id in (SELECT id FROM ( SELECT id FROM x_test WHERE id>max_id LIMIT 10) t);
    

    =============================================================================================

    至此!如有不足,还望各位读者指出。如果还不错,一键三连。

    =============================================================================================

    参考文档:
    MySQL索引总结
    你来说一下 Mysql 索引有几种类型呢?分别是什么?
    聚簇索引和非聚簇索引(通俗易懂 言简意赅)
    MySQL 之全文索引

    展开全文
  • 一文搞懂Mysql索引

    2020-09-09 17:48:40
    Mysql索引 B+树是什么? 平衡二叉树: B-树: B+树: 我们先来分析下这几种树结构的特点,为什么Mysql最终选择了B+树来实现索引 1.平衡二叉树的查询复杂度为logn(底数为2),通过自旋来保证树的平衡性,由于数据库...

    Mysql索引

    B+树是什么?

    平衡二叉树:
    在这里插入图片描述

    B-树:

    在这里插入图片描述

    B+树:

    在这里插入图片描述

    我们先来分析下这几种树结构的特点,为什么Mysql最终选择了B+树来实现索引

    1.平衡二叉树的查询复杂度为logn(底数为2),通过自旋来保证树的平衡性,由于数据库的数据量非常大,所以很难做到把数据库的全量数据通过二叉树保存到内存中(内存空间不足,成本太高,数据易丢),可以采用数据局部保存在内存中,这会导致无法进行自旋操作。数据量很大时,二叉树的高度会很大,从而导致查询时进行的磁盘IO次数很多。

    2.B-树的实现采用多叉树的方式,可以很好的降低树的高度,每个节点通过范围区间来划分子节点,B-的每个节点都会保存数据库行记录,并且叶子节点没有通过链表来连接,所以它的范围查询会比较麻烦,需要通过节点的回溯操作(这两个特性是跟B+树的最大区别)

    3.B+树的实现也是采用多叉树,它的非叶子节点只保存主键索引值,叶子节点保存主键索引值和行记录,叶子节点通过链表连接,从而实现快速的范围查询。非叶子节点不保存行记录是为了进行空间压缩,使得每个非叶子节点的内存占用更小,从而使得每一页(每次从磁盘中读取一页数据,innodb每一页大小为16k)能涵盖尽可能多的数据,从而减少磁盘IO次数。

    B+树代码实现

    
    /**
     * 这是B+树非叶子节点的定义。
     *
     * 假设keywords=[3, 5, 8, 10]
     * 4个键值将数据分为5个区间:(-INF,3), [3,5), [5,8), [8,10), [10,INF)
     * 5个区间分别对应:children[0]...children[4]
     *
     * m值是事先计算得到的,计算的依据是让所有信息的大小正好等于页的大小:
     * PAGE_SIZE = (m-1)*4[keywordss大小]+m*8[children大小]
     */
    public class BPlusTreeNode {
      public static int m = 5; // 5叉树
      public int[] keywords = new int[m-1]; // 键值,用来划分数据区间
      public BPlusTreeNode[] children = new BPlusTreeNode[m];//保存子节点指针
    }
    
    /**
     * 这是B+树中叶子节点的定义。
     *
     * B+树中的叶子节点跟内部节点是不一样的,
     * 叶子节点存储的是值,而非区间。
     * 这个定义里,每个叶子节点存储3个数据行的键值及地址信息。
     *
     * k值是事先计算得到的,计算的依据是让所有信息的大小正好等于页的大小:
     * PAGE_SIZE = k*4[keyw..大小]+k*8[dataAd..大小]+8[prev大小]+8[next大小]
     */
    public class BPlusTreeLeafNode {
      public static int k = 3;
      public int[] keywords = new int[k]; // 数据的键值
      public long[] dataAddress = new long[k]; // 数据地址
    
      public BPlusTreeLeafNode prev; // 这个结点在链表中的前驱结点
      public BPlusTreeLeafNode next; // 这个结点在链表中的后继结点
    }
    

    上述程序中,我们定义了一个m叉树,m的值越大树的高度越小,那是不是m的值设置越大就越好呢?我们知道,数据库是按页读取数据,当读取一个行记录时会把该行记录所在的一页都读取出来,设置合理的m值,尽量使一个节点的大小等于一页的大小,使得查询节点数据时只需进行一次磁盘IO,减少磁盘的IO次数是数据库查询性能优化的核心。

    节点分裂是什么?

    通过预先设置合理的m值,使得节点的大小等于一页的大小。经过插入操作后,可能会使得某些节点的大小超出一页的大小,这会导致一个节点的查询操作需要进过多次磁盘IO,这会导致B+的查询性能渐渐变差,节点分裂就是为了解决这个问题。

    实现思路:将超过大小的节点分裂成两个节点,这时可能导致父节点的子节点数目超过限定值,从而导致父节点也需要进行分裂操作,可能会产生级联分裂的现象,一直影响到根节点。

    在这里插入图片描述

    注:这也是为什么推荐Innodb的主键设置为递增主键,避免插入操作频繁触发节点分裂,递增主键插入时,节点数据大于一页,会创建一个新的节点而不是插入旧节点并且分裂。

    节点合并是什么?

    我们在删除某个数据的时候,也要对应地更新索引节点。这个处理思路有点类似跳表中删除数据的处理思路。频繁的数据删除,就会导致某些节点中,子节点的个数变得非常少,长此以往,如果每个节点的子节点都比较少,势必会影响索引的效率。我们可以设置一个阈值。在 B+ 树中,这个阈值等于 m/2。如果某个节点的子节点个数小于 m/2,我们就将它跟相邻的兄弟节点合并。不过,合并之后节点的子节点个数有可能会超过 m。针对这种情况,我们可以借助插入数据时候的处理方法,再分裂节点。

    在这里插入图片描述

    总结

    • 数据库索引用于加速查询
    • 虽然哈希索引是O(1),树索引是O(log(n)),但SQL有很多“有序”需求,故数据库使用树型索引
    • InnoDB不支持哈希索引
    • 数据预读的思路是:磁盘读写并不是按需读取,而是按页预读,一次会读一页的数据,每次加载更多的数据,以便未来减少磁盘IO
    • 局部性原理:软件设计要尽量遵循“数据读取集中”与“使用到一个数据,大概率会使用其附近的数据”,这样磁盘预读能充分提高磁盘IO
    • 数据库的索引最常用B+树:

    (1)很适合磁盘存储,能够充分利用局部性原理,磁盘预读;

    (2)很低的树高度,能够存储大量数据;

    (3)索引本身占用的内存很小;

    (4)能够很好的支持单点查询,范围查询,有序性查询;

    数据库索引

    聚簇索引与普通索引的区别?

    一,MyISAM的索引

    MyISAM的索引与行记录是分开存储的,叫做非聚簇索引。

    主键索引与普通索引没有本质的差异:

    (1)有连续聚集的区域单独存储行记录

    (2)主键索引的叶子节点存储主键,并且存储相对应的行记录指针

    (3)普通索引的叶子节点存储索引列,与之对应的行记录指针

    主键索引与普通索引是两棵独立的索引B+树,通过索引列查找时,先定位到B+树的叶子节点,再通过指针定位到行记录。

    在这里插入图片描述

    二、Innodb的索引

    Innodb的主键索引与行记录是存储在一起的,因此称为聚簇索引

    • 没有单独区域存储行记录
    • 主键索引的叶子节点,存储主键,与对应行记录(而不是指针)

    因为这个特性,InnoDB的表必须要有聚集索引:

    (1)如果表定义了PK,则PK就是聚集索引;

    (2)如果表没有定义PK,则第一个非空unique列是聚集索引;

    (3)否则,InnoDB会创建一个隐藏的row-id作为聚集索引;

    聚集索引,也只能够有一个,因为数据行在物理磁盘上只能有一份聚集存储。

    InnoDB的普通索引可以有多个,它与聚集索引是不同的:

    • 普通索引的叶子节点,存储主键(也不是指针)

    对于InnoDB表,这里的启示是:

    (1)不建议使用较长的列做主键,例如char(64),因为所有的普通索引都会存储主键,会导致普通索引过于庞大;

    (2)建议使用趋势递增的key做主键,由于数据行与索引一体,这样不至于插入记录时,有大量索引分裂,行记录移动;

    在这里插入图片描述

    总结

    (1)MyISAM的索引与数据分开存储。

    (2)MyISAM的叶子节点保存行记录指针,主键索引与普通索引的区别不大。

    (3)Innodb的聚簇索引叶子节点存储行记录和主键,普通索引存储索引列和主键值。

    (4)Innodb有且只能有一个聚簇索引,普通索引可以有多个。

    (5)Innodb适合使用趋势递增整数作为PK,而不适合使用较长的列作为PK。

    覆盖索引、前缀索引、索引下推是什么?

    覆盖索引

    通过前文分析可知,通过普通索引进行查询时,先在普通索引查找到对应的主键,再通过主键在聚簇索引上找到对应的行记录,每次查询都会有个回表操作。我们来看下怎么通过覆盖索引来优化这个问题。

    我们创建一张用户信息表

    CREATE TABLE `tuser` (
      `id` int(11) NOT NULL,
      `id_card` varchar(32) DEFAULT NULL,
      `name` varchar(32) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `ismale` tinyint(1) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `id_card` (`id_card`),
      KEY `name_age` (`name`,`age`)
    ) ENGINE=InnoDB
    

    查询需求:

    根据身份证号(id_card)来查询用户名称

    方案一:id_card建立普通索引,查询时先通过普通索引查找到对应的主键,再通过主键在聚集索引中查找到对应的行记录。(会有一次回表操作)

    方案二:id_card与name建立联合索引,通过id_card直接在普通索引中查找到对应的name,直接返回。(没有回表操作)

    方案二就是通过联合索引来实现了覆盖索引,避免了每次查询都要查找聚簇索引。

    最左前缀原则

    在建立联合索引时,我们应该如何来安排索引内的字段顺序?

    我们用(name,age)这个联合索引来分析

    在这里插入图片描述

    查询需求1:根据姓名来查询用户信息,查询条件where name ='张三’或者where name like '张%'都能通过上述联合索引快速查找到记录。

    查询需求2:通过age来查询用户信息,查询条件where age=30,这时索引失效,会进行全表扫描。

    通过索引来满足上述两个查询需求的方案有:

    1. 创建一个(name,age)联合索引和age的普通索引。
    2. 创建一个(age,name)联合索引和name的普通索引。

    这时我们要考虑的原则是空间。name字段要比age字段所占的空间更大,所以方案1会是一个更好的方案。

    总结:

    1.第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

    2.优先选择占用空间更小的方案来创建索引。

    索引下推

    我们还是以用户表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是10岁的所有男孩”。那么,SQL语句是这么写的:

    mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
    

    借助前缀索引规则,通过“张”定位到第一条前缀为“张”的记录,然后判断其他条件是否满足。

    在MySQL 5.6之前,只能从ID3开始一个个回表。到主键索引上找出数据行,再对比字段值。

    而MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

    无索引下推执行流程:

    在这里插入图片描述

    索引下推执行流程:

    在这里插入图片描述

    如何给字符串字段添加索引?

    上文分析索引结构时我们总结出:建议使用趋势递增的整数字段来创建索引,但是现实业务中我们可能需要创建字符串索引。

    我们拿用户中心来分析:

    mysql> create table user(
    ID bigint unsigned primary key,
    email varchar(64), 
    idno varchar(128),
    ... 
    )engine=innodb; 
    

    通过邮箱登录时,需要频繁地通过邮箱来查询用户信息:select * from user where email = ‘xxx@qq.com’,我们要如何来优化这个查询需求呢?我们通常的做法是给email创建唯一索引,这是一个很好的方案吗?

    方案一:为email添加普通索引

    alter table user add index index1(email);
    

    优点:

    1.无需对字符串值进行额外的处理。

    缺点:

    1.字段值较长时,索引结构会占用较多的空间。

    方案二:为email添加前缀索引

    alter table User add index index2(email(6));
    

    优点:

    1.存储在索引结构中的数据是字符串的局部子串,节约了索引所占空间大小。

    缺点:

    1.需要对字符串的值进行截取操作。

    2.前缀索引会使覆盖索引失效。

    注:这种方案的核心思想是当字符串的子串具有足够的唯一性时,可以使用子串来创建索引。

    方案三:将字符串字段转换成整数字段,在整数字段上创建索引

    alter table t add id_card_crc int unsigned, add index(id_card_crc);
    

    通过hash算法(crc32)将字符串转成int值,基于hash值来创建普通索引。

    优点:

    1.通过整数值来创建索引,可以很好的减少索引所占空间大小,并且每个节点(一页16k)所能容纳的数据更多从而减少磁盘IO。

    缺点:

    1.需要通过一定的hash算法来把字符串转换成整数值,这个计算操作会耗费一定的cpu资源。

    注:要保证计算出来的整数值具有很好的辨识度。

    方案三:将字符串字段转换成整数字段,在整数字段上创建索引

    alter table t add id_card_crc int unsigned, add index(id_card_crc);
    

    通过hash算法(crc32)将字符串转成int值,基于hash值来创建普通索引。

    优点:

    1.通过整数值来创建索引,可以很好的减少索引所占空间大小,并且每个节点(一页16k)所能容纳的数据更多从而减少磁盘IO。

    缺点:

    1.需要通过一定的hash算法来把字符串转换成整数值,这个计算操作会耗费一定的cpu资源。

    注:要保证计算出来的整数值具有很好的辨识度。

    参考资料:

    [1] MySQL技术内幕 InnoDB存储引擎 第2版

    [2]数据结构与算法之美

    [3]MySQL实战45讲

    [4]架构师之路

    展开全文
  • 一文搞懂MySQL索引所有知识点 Mysql索引 索引介绍 索引是什么 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。 一般来说索引本身也很大...
  • 1、创建索引的几种方式; 2、mysql索引知识; 3、mysql索引优化;
  • 而且索引相关的知识也是面试频率非常高的点之一,因此,本文将从基础理论出发,介绍MySQL按照逻辑角度的索引分类和实现。通过索引数据结构的实现原理阐述不同结构对建立索引带来的优劣势,希望通过这篇文章能给大家...
  • 个人开发博客网站,欢迎访问:rayoluo.top B+树特点: 树中的节点并不存储数据本身,而是...1.给一亿个数据构建二叉查找树索引索引中会包含大约1亿个节点,每个节点假设占用16字节,那就需要大约1GB的内存空间,.
  • 索引是帮助MySQL高效获取数据的排好序的数据结构(本质是一种优化查询的数据结构) 二.为什么要使用索引索引的出现就是为了提高查询效率,就一本新华词典,我们通过目录快速锁定要查询的“字”在那一页。其实说白...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,599
精华内容 1,439
关键字:

一文搞懂mysql索引

mysql 订阅