初识mysql索引

单线程_01
124 阅读9分钟

初识mysql索引

索引是什么

索引介绍

索引是帮助MySQL高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

使用索引的优势：

  • 提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势：

  • 索引列也是要占用空间的
  • 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低

下图是将age作为索引，使用二叉树作为索引结构的图例，可以看出要找出age=45只需要经过三次查询，相比较不加索引的全盘扫描快了3倍。虽然使用二叉树能极大提升数据查找效率，但是所有的数据库并没有使用其作为索引结构，下面看看mysql中的索引结构。

01.png

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

  • B+Tree索引：最常见的索引类型，大部分引擎都支持 B+ 树索引
  • Hash索引：底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
  • R-tree(空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
  • Full-text(全文索引)：是一种通过建立倒排索引,快速匹配文档的方式，类似于Lucene,Solr,ES

不同的存储引擎对于索引结构的支持情况。

索引InnoDBMyISAMMemory
B+Tree索引YYY
Hash索引NNY
R-tree(空间索引）NYN
Full-text(全文索引)Y(5.6+)YN

B+Tree索引

B+ Tree作为mysql默认的索引结构，其到底有哪些优势能作为众多索引结构中的第一顺位人。

  • B+树能显著减少IO次数，提高效率
  • B+树的查询效率更加稳定，因为数据放在叶子节点
  • B+树能提高范围查询的效率，因为叶子节点指向下一个叶子节点

查找一个节点所做的IO次数很大程度上取决于这个节点所处的树的高度，那么B+Tree是如何降低树的高度呢

俗话说没有对比就没有伤害，我们先看下二叉树是如何做：

  1. 非叶子节点最多拥有两个子节点
  2. 非叶子节值大于左边子节点、小于右边子节点
  3. 没有值相等重复的节点;

按照上述规则我们可以构造出如下二叉树，树的高度是5（第一层是1）

02.png

平衡二叉树除了满足以上三点外，还需要满足树的左右两边的层级数相差不会大于1，加上这条规则后的树是这样的：

03.png

此时树的高度为3，为了满足新增的这点，平衡二叉树有进行左旋或者右旋的操作，动画如下链接，请依次insert 2，3，5，6，7，8：

平衡二叉树演示

B+树是通过二叉查找树，再由平衡二叉树，B树（又名B-树）演化而来的，B+树中的B不是代表二叉（binary），而是代表平衡（balance），因为B+树同样满足以上4点。

除了降低IO的操作次数，在频繁IO读取方面，数据库系统的设计者巧妙利用磁盘预读原理（预读的长度一般为页的整倍数），将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

B+树的查询效率更加稳定，因为数据放在叶子节点，这点通过B- Tree 可以很直白的看出区别

随机insert 5/20/30/34/38/39/40/60/64/65，得到的B+树如下图所示：

04.png

随机insert同组数据，得到的B-树如下图所示：

05.png

B-Tree演示

B+Tree演示

Hash索引

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决

06.png

特点：

  • Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）
  • 无法利用索引完成排序操作
  • 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的

索引的分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

分类说明特点关键字
主键索引针对于表中主键创建的索引自动创建, 只能有一个primary
唯一索引避免同一个表中某数据列中的值重复可以有多个unique
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键词，而不是比较索引中的值可以有多个fulltext

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种

分类说明特点
聚集索引(Clustered Index)将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据必须有,而且只有一个
二级索引(Secondary Index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个

聚集索引选取规则:

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

下图是主键ID作为聚集索引，name作为二级索引

聚集索引的叶子节点下挂的是这一行的数据 。二级索引的叶子节点下挂的是name对应的主键值

07.png

下图是执行 select * from user where name="Arm" 的查找的执行示意图

ID是主键索引保存该行和索引的数据，name是二级索引只有该字段和对应的主键索引，因为查的是所有虽然会走name的二级索引但是只有name的数据，因此需要拿到主键索引进行回表查询

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询

08.png

索引使用

语法

# 创建索引
create [unique|fulltext] index index_name on table_name(index_col_name)
# 查看索引
show index from table_name
# 删除索引
drop index index_name on table_name

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

案例说明

# 在tb_user中创建name age profession 的联合索引
create index idx_name_age_profession on tb_user(name,age,profession)
# 通过 name 查询：走索引
explain select * from tb_user where name="大乔" 
# 通过 name age查询：走索引
explain select * from tb_user where age=22 and name="大乔"
# 通过 name age profession 查询：走索引
explain select * from tb_user where age=24 and name="花木兰" and profession="软件工程"
# 通过 age 查询：不走索引
explain select * from tb_user where age=22 
# 通过 age profession 查询：不走索引
explain select * from tb_user where age=22 and profession="软件工程"

image.png

注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

image.png

尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

索引失效的情况

索引列进行运算

17.png

字符串不加引号

# 创建 phone 的索引
create index inx_phone on tb_user(phone)

19.png 18.png

如果字符串不加单引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效。

模糊查询

尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

image.png

or连接条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

image.png

sql提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示，自己来指定使用哪个索引来达到优化操作的目的。

  • use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）
  • ignore index ： 忽略指定的索引
  • force index ： 强制使用索引

和name相关存在两个索引：

索引名字段索引类型索引方式
idx_name_age_professionname, age, professionNORMALBTREE
idx_namenameNORMALBTREE

image.png

覆盖索引

尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

29.png

30.png

在tb_user表中有一个联合索引 idx_name_age_profession，该索引关联了三个字段name、age、profession，而这个索引也是一个二级索引，叶子节点下面挂的是这一行的主键id。

当我们查询返回的数据在 id、name、age、profession 之中，则直接走二级索引直接返回数据了。

如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。 一直使用select * 查询返回所有字段值，很容易就会造成回表查询（除非是根据主键查询，此时只会扫描聚集索引）

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

create index idx_name on table_name(column(n))

索引长度 n 的值需要根据索引的选择性来决定，选择性是指不重复的索引值（基数）和数据表的记录总数的比值,计算方式如下

select count(distinct substring(字段,1,n)) / count(*) from 表名

索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

索引设计原则

  • 针对于数据量较大，且查询比较频繁的表建立索引
  • 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
  • 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
  • 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
  • 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
  • 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
  • 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

参考：MySQL索引原理及BTree（B-/+Tree）结构详解

avatar
文章
阅读
粉丝