MySql索引和索引创建策略

目录
  • 1、B+树索引
    • 1.1、聚集索引/聚簇索引
    • 1.2、辅助索引/二级索引
    • 1.3、联合索引/复合索引
      • 1.3.1、什么是复合索引
      • 1.3.2、最左原则
      • 1.3.3、联合索引的查询优化
  • 2、哈希索引
    • 2.1、查看哈希索引的命中率等信息
  • 3、索引的创建策略
    • 3.1、 单列索引的策略
      • 3.1.1、列的类型占用的空间越小,越适合作为索引
      • 3.1.2、根据列的值的离散性
      • 3.1.3、前缀索引
      • 3.1.2、只为搜索、排序和分组的列建索引
    • 3.2、 多列索引的策略
      • 3.2.1、离散性最高的列放前面
      • 3.2.2、三星索引

1、B+树索引

顾名思义,结构是B+树的索引就是B+树索引,一般情况下,InnoDb引擎中创建的常规索引都是B+的结构。

B+树索引就是以下这几种。

1.1、聚集索引/聚簇索引

定义主键时,主键上自动追加的索引就是聚集索引,也称聚簇索引。

Mysql用组建构建一颗B+树,如下图所示,每个叶子节点对应一个主键,以及和这个主键对应的其它数据。

如果我们创建表时没有定义主键,Mysql也会自动创建一个主键和对应的索引,主键名是rowId

1.2、辅助索引/二级索引

如果我们对非主键的列column创建索引,那这个索引就称为辅助索引/二级索引。同样的,Mysql会为这个索引创建一个B+树,树的叶子节点除了包含这个列column的值以外,就只包含这个列所在行的主键值,这样通过列的索引就可以查到叶子节点,然后叶子节点中的主键信息再从主键的索引中搜索,最终得到一整行的数据。

通过二级索引找到主键,再从主键得到一整行数据的行为叫做回表。

1.3、联合索引/复合索引

1.3.1、什么是复合索引

聚合索引可以说是二级索引的一种特殊情况。一般二级索引都是只对一个非主键的列添加索引,而聚合索引则是一次性对多个列同时添加索引。

一般的二级索引用这样的语句创建:

CREATE INDEX  order_name_index on t_order(order_name);

复合索引则是这样创建:

CREATE INDEX  order_name_and_order_type_index on t_order(order_name, order_type);

对于复合索引,Mysql会也会创建一个B+树,但因为是多个列的索引,所以B+树的排序规则比较特殊,是遵循最左原则。下面会讲到什么是最左原则。

之后叶子节点包含的信息有多个,一个是作为索引的各个列的值,另一个就是主键的值。

1.3.2、最左原则

所谓的最左原则是,B+树的排序规则是根据索引定义时,定义的语句中的列名从左到右进行排序。

比如定义语句如下:

CREATE INDEX  joint_index on t_order(order_name, order_type, submit_time);

那排序规则是先排order_name,如果order_name相同,再排order_type,最后排submit_time

那当我们查询时,根据定义时列的顺序从左至右,where子句或者order by等子句应该尽量先从order_name开始,然后以此类推。

比如说,我们已经定义了上面的三个列组成的复合索引,那查询或者排序的时候尽量先order_name,再order_type,最后submit_time

select * from t_order where order_name = 'order1'
and order_type = 1
and submit_time = str_to_date('2022-08-02 00:52:26', '%Y-%m-%d %T')

原因很简单,因为联合索引的排序规则是先排order_name,如果order_name相同,再排order_type,最后排submit_time。所以只有查询排序时也遵循这个规则,我们才能用上索引。

如果我们不完全遵守最左原则,比如查询排序只排两个列,忽略中间那个order by order_name, submit_time。那这个时候Mysql会有智能化的处理,他会自己判断是用索引快还是不用索引快。

1.3.3、联合索引的查询优化

尽量使用到组成联合索引的列,并且保证顺序。可以通过查询索引查看列的顺序。查看sql_in_index

show index from t_order;

查询返回的字段尽量就只返回组成联合索引的列和主键,不要返回其它的列,以免造成回表。
这应该容易理解,因为联合索引的B+树的叶子节点就只包含主键和组成联合索引的列的值,如果返回的字段就这几列,那在一个B+树种查询就完事了。如果还要返回其它的列的话,就又要去主键的索引中查找,有回表操作。

2、哈希索引

一般数据库都会用B+树索引查询数据,但是当数据库使用一段时间后,InnoDB 会记录一些使用频率较高的热数据,然后为这些热数据建立哈希结构的索引,这就是哈希索引的应用场景。

这个索引在Mysql 5.7开始默认开启。

2.1、查看哈希索引的命中率等信息

使用语句:

show engine innodb status;

其中的status有很多信息,其中就包括哈希索引的情况。我们把信息复制到编辑器中查看。其中的这一段就是哈希索引的信息。

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 5 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
-- 哈希索引的命中率,可根据这个来决定是否使用哈希索引
0.00 hash searches/s, 0.00 non-hash searches/s
---

3、索引的创建策略

3.1、 单列索引的策略

3.1.1、列的类型占用的空间越小,越适合作为索引

因为B+树也是占用空间的,所以在固定空间中,如果列的类型占用的空间越小,那我们一次就能读取更多的B+树节点,这样自然就加快了效率。

3.1.2、根据列的值的离散性

离散性是指数据的值重复的程度高不高,假如有N条数据的话,那离散性就可以用数值表示,范围是1/N 到 1。

比如说某个列在数据库中有下面几条数据(1, 2, 3, 4, 5, 5, 3),其中5和3都有重复,去重后应该是(1, 2, 3, 4, 5)。我们将去重后的条数除以总条数就得到离散性。这里是5/7。那么这个数值越小,代表这个列的重复数据越多;值越大代表重复数据越少。

如果一个列的数据的重复性越低,那么这个列就越适合加索引。

因为索引是需要起到筛选的作用。比如我们有个where条件是where id = 1,如果数据重复性较高,那可能根据索引会返回100条数据,然后我们在根据其他where条件在100条数据中再筛选。

如果数据重复性较低,那可能就只返回1条数据,那之后的运算量明显小得多。

所以一个列的数据离散性越高,那这个列越适合添加索引。

我们可以用下面的语句得到某个列的离散性程度。

select count(distinct id)/count(*) form t_table;

3.1.3、前缀索引

前缀索引和后缀索引:

有些列的值比较长,比如一些备注日志信息也会记录在数据库当中,这类信息的长度往往比较长,如果我们需要对这类列加索引,那索引并不是索引字符串的全部长度。这时候我们就可以建立前缀索引,即对字符串的前面几位建立索引。

所以前缀索引就是建立范围更小索引,选择一个好前缀位数就能有一个更好的查询效率。

不过有一些缺点,就是这类索引无法应用到order bygroup语句上。

Mysql没有后缀索引,如果非要实现后缀索引,那在数据存储时我们应该将数据反转,这样就能用前缀索引达到后缀索引的效果。后缀索引的一个经典应用就是邮箱,快速查询某种类型的邮箱。

选择前缀索引的位数:

这里的逻辑和列的离散性类似,我们需要看看字符串的前面几位的子字符串的离散性如何。比如对于下面的表,内容是电影票的相关信息,我们需要对order_note建立前缀索引。

来比较一下各个位的子字符串的离散性。

SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12,
COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14,
COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;

![在这里插入图片描述](https://img-blog.csdnimg.cn/33a12fadd99944098e91f883d6bfaa2f.png #pic_center =x80)
可以看出,前面几位的子字符串的离散程度较低,后面sel13开始就比较高,那我们可以根据实际情况,建立13~15位的前缀索引。

建立前缀索引SQL语句:

alter table order_exp add key(order_note(13));

3.1.2、只为搜索、排序和分组的列建索引

这个理由很简单,不解释了。

3.2、 多列索引的策略

3.2.1、离散性最高的列放前面

原因很简单,查询时根据定义复合索引时的列的顺序来查询的,离散性高的列放在前面的话,就能更早的将更多的数据排除在外。

3.2.2、三星索引

三星索引是一种策略。有三种条件,满足一条则索引获得一颗星,三颗星则是很好的索引。

三条策略分别是

索引将相关记录放在一起。

意思是查询需要的数据在索引树的叶子节点中连续或者足够靠近。举个例子,下面是某个索引的B+树。如果查询需要的数据只覆盖叶子节点的前两个片,即0000 ~ a。这很明显,后面的片我们就没必要再去查询了,这无疑增加了效率。如果查询需要的数据每个片都分散一点,那么查询的次数就增加了很多。

所以查询需要的数据在叶子节点上越连续,越窄就越好。

索引中的数据顺序与查找中的数据排序一致。

这容易理解,讲解联合索引中说过,B+树的排序顺序和索引中的数据一样,所以查询时的where的数据顺序越贴近索引中的顺序,就越能更好地利用B+树。

索引的列包含查询中的所有列。

这个可以避免回文操作,不多解释。

三星索引的权重:

一般来说第三个策略权重占到50%,之后是第一个策略27%, 第二个策略23%。

三星索引实例:

CREATE TABLE customer (
	cno INT,
	lname VARCHAR (10),
	fname VARCHAR (10),
	sex INT,
	weight INT,
	city VARCHAR (10)
);

CREATE INDEX idx_cust ON customer (city, lname, fname, cno);

我们创建以上的索引,那么对于下面的查询语句,这个索引就是三星索引。

select cno,fname from customer where lname='xx' and city ='yy' order by fname;

首先,查询条件中有lname=’xx’ and city =’yy’,这条件让我们这需要在lname=’xx’ and city =’yy’的那一片B+树的叶子节点中查询,让我们的查询变窄了很多,并且这部分的数据是连续的,因为B+树是先根据city排序,再根据lname查询。

另外,因为已经锁定lname=’xx’ and city =’yy’,所以这部分的数据是根据fname和cno排序。查询语句正好是根据`fname```排序,所以第二点也满足。

最后是查询的结果都包含正在索引中,不会有回文,第三点也满足,所以这个索引是三星索引。

到此这篇关于MySql索引和索引创建策略的文章就介绍到这了,更多相关MySql索引创建内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 一文弄懂MySQL索引创建原则

    目录 一.适合创建索引 1.字段的数值有唯一性限制 2.频繁作为Where查询条件的字段 3.经常Groupby和Orderby的列 4.Update.Delete的where条件列 5.Distinct字段需要创建索引 6.多表Join连接操作时,创建索引注意事项 7.使用列的类型小的创建索引 8.使用字符串前缀创建索引 9.区分度高的列适合作为索引 10.使用最频繁的列放到联合索引的左侧 11.在多个字段都要创建索引的情况下,联合索引由于单值索引 二.不适合创建索引 1.在where中使用不

  • 图文详解MySQL中两表关联的连接表如何创建索引

    本文介绍了MySQL中两表关联的连接表是如何创建索引的相关内容,分享出来供大家参考学习,下面来看看详细的介绍: 问题介绍 创建数据库的索引,可以选择单列索引,也可以选择创建组合索引. 遇到如下这种情况,用户表(user)与部门表(dept)通过部门用户关联表(deptuser)连接起来,如下图所示: 表间关系 问题就是,在这个关联表中该如何建立索引呢? 针对该表,有如下四种选择: 针对于user_uuid建立单列索引idx_user 针对于user_dept建立单列索引idx_dept 建立组合

  • MySQL使用命令创建、删除、查询索引的介绍

    MySQL数据库表可以创建.查看.重建和删除索引,索引可以提供查询速度.索引根据分类,分为普通索引和唯一索引:有新建索引.修改索引和删除.但是索引不是到处都可以创建,需要根据具体的条件.下面利用实例说明索引创建到销毁的过程,操作如下: 熟悉使用MySQL命令可以方便灵活地执行各种数据库操作:本文主要是对如何使用命令操作MySQL索引,包括创建索引.重建索引.查询索引.删除索引的操作.以下所列示例中的 `table_name` 表示数据表名,`index_name` 表示索引名,column li

  • MySQL创建索引需要了解的

    前言: 在 MySQL 中,基本上每个表都会有索引,有时候也需要根据不同的业务场景添加不同的索引.索引的建立对于数据库高效运行是很重要的,本篇文章将介绍下创建索引相关知识及注意事项. 1.创建索引方法 创建索引可以在建表时指定,也可以建表后使用 alter table 或 create index 语句创建索引.下面展示下几种常见的创建索引场景. # 建表时指定索引 CREATE TABLE `t_index` (   `increment_id` int(11) NOT NULL AUTO_I

  • MySQL字符串索引更合理的创建规则讨论

    前言 针对使用MySQL的索引,我们之前介绍过索引的最左前缀规则,索引覆盖,唯一索引和普通索引的使用以及优化器选择索引等概念,今天我们讨论下如何更合理的给字符串创建索引. 如何更好的创建字符串索引 我们知道,MySQL中,数据和索引都是在一颗 B+树 上,我们建立索引的时候,这棵树所占用的空间越小,检索速度就会越快,而varchar格式的字符串有些会很长,那么在效率为上的今天,我们如何更加合理的建立字符串的索引呢? 假如说我们一张表中存在 email 字段,现在要给 email 字段创建索引,e

  • MySQL创建高性能索引的全步骤

    一.索引基础 1. 索引的类型 1.1 B-Tree 索引 大多数MySQL存储引擎默认使用的是B+树的索引,不同的存储引擎用不同的方式使用B+树索引,MyISAM使用前缀压缩技术使得索引更小,但是InnoDB则按照元数据格式进行存储:MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行. B树 和 B+ 树 B树: B+树: 区别: B树的关键字和记录是放在一起的,叶子节点可以看作外部节点,不包含任何信息:B+树的非叶子节点中只有关键字和指向下一个节点的索引

  • 怎样正确创建MySQL索引的方法详解

    索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本.MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,所以大数据量建立索引是非常有必要的.MySQL提供了Explain,用于显示SQL执行的详细信息,可以进行索引的优化. 什么是索引? MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构.我们可以简单理解为:快速查找排好序的一种数据结构.Mysql索引主要有两种结构:B+Tree索引和Hash索引.我们平

  • MySQL 索引的优缺点以及创建索引的准则

    一.为什么要创建索引呢(优点) 这是因为,创建索引可以大大提高系统的性能. 第一, 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性. 第二, 可以大大加快数据的检索速度,这也是创建索引的最主要的原因. 第三, 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义. 第四, 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间. 第五, 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能. 二.建立方向索引的不利因素(缺点) 也许会有人

  • MySql索引和索引创建策略

    目录 1.B+树索引 1.1.聚集索引/聚簇索引 1.2.辅助索引/二级索引 1.3.联合索引/复合索引 1.3.1.什么是复合索引 1.3.2.最左原则 1.3.3.联合索引的查询优化 2.哈希索引 2.1.查看哈希索引的命中率等信息 3.索引的创建策略 3.1. 单列索引的策略 3.1.1.列的类型占用的空间越小,越适合作为索引 3.1.2.根据列的值的离散性 3.1.3.前缀索引 3.1.2.只为搜索.排序和分组的列建索引 3.2. 多列索引的策略 3.2.1.离散性最高的列放前面 3.2

  • MySQL数据库索引原理及优化策略

    目录 1 索引 索引概念 索引作用 索引的使用场景 2 索引分类 B树索引和B+树索引区别 3 索引操作 创建主键索引 唯一索引的创建 普通索引的创建 查询索引 删除索引 索引创建原则 1 索引 索引概念 索引是一种特殊的文件,包含着对数据表里所有记录的引用指针.可以对表中的一列或多列创建索引,并指定索引的类型,各类索引有各自的数据结构实现. 索引作用 数据库中的表.数据.索引之间的关系,类似于书架上的图书.书籍内容和书籍目录的关系,索引所起的作用类似书籍目录,可用于快速定位.检索数据.索引可以

  • 详解mysql索引总结----mysql索引类型以及创建

    关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车.对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢.还是以WordPress来说,其多个数据表都会对经常被查询的字段添加索引,比如wp_comments表中针对5个字段设计了BTREE索引. 一个简单的对比测试 以我去年测试的数据作为一个简单示例,20多条数据源随机生成200万条

  • MySQL为JSON字段创建索引方式(Multi-Valued Indexes 多值索引)

    目录 多值索引简介 创建多值索引 JSON对象字段索引 JSON数组对象索引 在组合索引中创建多值索引 多值索引的局限 应用场景 多值索引简介 从MySQL 8.0.17 开始, InnoDB支持创建多值索引(Multi-Valued Indexes),该索引是在JSON存储值数组的列上定义的二级索引,对于单个数据记录可以有多个索引记录.此类索引特定的语法定义: CAST(expression AS type ARRAY),例如CAST(data->'$.zipcode' AS UNSIGNED

  • MySQL Order By索引优化方法

    尽管 ORDER BY 不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的 ORDER BY 字段在 WHERE 子句中都被包括了. 使用索引的MySQL Order By 下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分: 复制代码 代码如下: SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; SELECT * FROM t1 WHERE key_part1=constant ORD

  • Mysql中的索引精讲

    前言 开门见山,直接上图,下面的思维导图即是现在要讲的内容,可以先有个印象- 常见索引类型(实现层面) 索引种类(应用层面) 聚簇索引与非聚簇索引 覆盖索引 最佳索引使用策略 1.常见索引类型(实现层面) 首先不谈Mysql怎么实现索引的,先马后炮一下,如果让我们来设计数据库的索引,该怎么设计? 我们首先思考一下索引到底想达到什么效果?其实就是想能够实现快速查找数据的策略,所以索引的实现本质上就是一个查找算法. 但是跟普通的查找有所不同,因为我们的数据有一下特征: 1.存储的数据是非常非常多的

  • MySQL查询冗余索引和未使用过的索引操作

    MySQL5.7及以上版本提供直接查询冗余索引.重复索引和未使用过索引的视图,直接查询即可. 查询冗余索引.重复索引 select * sys.from schema_redundant_indexes; 查询未使用过的索引 select * from sys.schema_unused_indexes; 如果想在5.6和5.5版本使用,将视图转换成SQL语句查询即可 查询冗余索引.重复索引 select a.`table_schema`,a.`table_name`,a.`index_name

  • MySQL数据库之索引详解

    目录 一.MySQL索引简介 二.MySQL五种类型索引详解 (一)普通索引 (二)唯一性索引 (三)主键索引 (四)复合索引 (五)全文索引 三.MySQL索引使用原则 总结 今天继续给大家介绍MySQL相关知识,本文主要内容是MySQL索引相关内容. 一.MySQL索引简介 索引是MySQL数据库为了加快数据查询的速度,给表中的某一个或者是某几个列添加的一种"目录".MySQL的索引是一个特殊的文件,但是InnoDB类型引擎(关于MySQL的引擎我们会在今后的文章中进行讲解)的表的

  • Mysql数据库之索引优化

    MySQL凭借着出色的性能.低廉的成本.丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库.虽然性能出色,但所谓"好马配好鞍",如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如"精通MySQL"."SQL语句优化"."了解数据库原理"等要求.我们知道一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,

  • 通过实例认识MySQL中前缀索引的用法

    今天在测试环境中加一个索引时候发现一警告 root@test 07:57:52>alter table article drop index ind_article_url; Query OK, 144384 rows affected (16.29 sec) Records: 144384 Duplicates: 0 Warnings: 0 root@test 07:58:40>alter table article add index ind_article_url(url); Query

随机推荐