索引匹配方式

下面举例皆在索引 idx(name,age,pos)建立前提下

全值匹配

全值匹配指的是和索引中的所有列进行匹配

匹配最左前缀

只匹配前面的几列

匹配列前缀

可以匹配某一列的值的开头部分

比如:select * from staffs where name like ‘J%’;
这个语句可以利用到用name建立的索引进行查找。但是如果是 select * from staffs where name like ‘%J%’;就无法用到。

匹配范围值

可以查找某一个范围的数据

比如:explain select * from staffs where name > ‘Mary’;

精确匹配某一列并范围匹配另外一列

可以查询第一列的全部和第二列的部分

比如:explain select * from staffs where name = ‘July’ and age > 25;

只访问索引的查询

查询的时候只需要访问索引,不需要访问数据行,本质上就是覆盖索引

哈希索引

哈希索引是memory存储引擎使用的索引,memory的数据文件存储在内存中,这是它效率很高的原因,但正因如此memory不支持持久化,数据易丢失。

特点:

  • 哈希索引基于哈希表的实现,只有精确匹配索引所有列的查询才有效(不只是范围查询)
  • 在mysql中,只有memory的存储引擎显式支持哈希索引
  • 哈希索引自身只需存储对应的hash值,所以索引的结构十分紧凑,这让哈希索引查找的速度非常快

限制/缺点:

  • 哈希索引只包含哈希值和行指针,而不存储字段值,索引不能使用索引中的值来避免读取行
  • 哈希索引数据并不是按照索引值顺序存储的,所以无法进行排序
  • 哈希索引不支持部分列匹配查找,哈希索引是使用索引列的全部内容来计算哈希值
  • 哈希索引支持等值比较查询,但不支持任何范围查询
  • 访问哈希索引的数据非常快,除非有很多哈希冲突,当出现哈希冲突的时候,存储引擎必须遍历链表中的所有行指针,逐行进行比较,直到找到所有符合条件的行
  • 哈希冲突比较多的话,维护的代价也会很高

组合索引

当包含多个列作为索引,需要注意的是正确的顺序依赖于该索引的查询,同时需要考虑如何更好的满足排序和分组的需要

案例:

image-20220330210521713
注意倒数第二条语句,范围查找,后方的列不会再使用索引

聚簇索引与非聚簇索引

聚簇索引

不是单独的索引类型,而是一种数据存储方式,指的是数据行跟相邻的键值紧凑的存储在一起

优点:

  • 可以把相关数据保存在一起
  • 数据访问更快,因为索引和数据保存在同一个树中
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点:

  • 聚簇数据最大限度地提高了IO密集型应用的性能,如果数据全部在内存,那么聚簇索引就没有什么优势
  • 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式(因此建议使用自增主键,每次插入新纪录都是追加,不涉及挪动其他记录,因此效率最高(性能),非主键索引占用的空间也最小(存储空间)。)
  • 更新聚簇索引列的代价很高,因为会强制将每个被更新的行移动到新的位置
  • 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题
  • 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候

在MySQL中,B+树为了维护索引的有序性,在新插入值时会做必要的维护,常常需要逻辑上挪动后面的数据以腾出位置。

在挪动过程中,会出现页分裂与页合并。

页分裂:申请新的数据页,挪动部分数据从旧数据页到新数据页。

页合并:相邻两个页由于删除了数据,利用率很低之后,会将两个数据页合并。

非聚簇索引

数据文件跟索引文件分开存放

覆盖索引

  • 如果一个索引包含所有需要查询的字段的值,我们称之为覆盖索引
  • 不是所有类型的索引都可以称为覆盖索引,覆盖索引必须要存储索引列的值
  • 不同的存储实现覆盖索引的方式不同,不是所有的引擎都支持覆盖索引,memory不支持覆盖索引

优点:

  1. 索引条目通常远小于数据行大小,如果只需要读取索引,那么mysql就会极大的减少数据访问量
  2. 因为索引是按照列值顺序存储的,所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多(符合磁盘预读特性)
  3. 一些存储引擎如MYISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用,这可能会导致严重的性能问题
  4. 由于INNODB的聚簇索引,覆盖索引对INNODB表优势很大

在执行计划中可以看到语句是否用到了覆盖索引:在explain的extra列可以看到using index的信息,此时就使用了覆盖索引,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> explain select store_id,film_id from inventory\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: inventory
partitions: NULL
type: index
possible_keys: NULL
key: idx_store_id_film_id
key_len: 3
ref: NULL
rows: 4581
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.01 sec)

在大多数存储引擎中,覆盖索引只能覆盖那些只访问索引中部分列的查询。不过,可以进一步的进行优化,可以使用innodb的二级索引来覆盖查询。

例如:actor使用innodb存储引擎,并在last_name字段又二级索引,虽然该索引的列不包括主键actor_id,但也能够用于对actor_id做覆盖查询

索引优化细节

  1. 当使用索引列进行查询的时候尽量不要使用表达式,把计算放到业务层而不是数据库层

  2. 尽量使用主键查询,而不是其他索引,因为主键查询不会触发回表查询

  3. 当用长字段建立索引时,可使用前缀索引

    有时候需要索引很长的字符串,这会让索引变的大且慢,通常情况下可以使用某个列开始的部分字符串,这样大大的节约索引空间,从而提高索引效率,但这会降低索引的选择性,索引的选择性是指不重复的索引值和数据表记录总数的比值。索引的选择性越高则查询效率越高,因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

    比如:app,apple,apples,取前三个字符串的话,就都是app了,这种情况下索引的选择性就偏低

    一般情况下某个列前缀的选择性也是足够高的,足以满足查询的性能,但是对应BLOB,TEXT,VARCHAR类型的列,必须要使用前缀索引,因为mysql不允许索引这些列的完整长度,使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性,通过又不能太长。

  1. 使用索引扫描来排序

    mysql有两种方式可以生成有序的结果:通过排序操作或者按索引顺序扫描,如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序

    扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列,那么就不得不每扫描一条索引记录就得回表查询一次对应的行,这基本都是随机IO,因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢。

    但按索引查询有诸多限制:

    • 只有当索引的列顺序和order by子句的顺序完全一致,并且所有列的排序方式都一样时,mysql才能够使用索引来对结果进行排序
    • 如果查询需要关联多张表,则只有当orderby子句引用的字段全部为第一张表时,才能使用索引做排序。
    • order by子句和查找型查询的限制是一样的,where之后的条件的列如果满足索引的最左匹配的要求的话,会用到索引排序,否则,mysql都需要执行顺序操作,而无法利用索引排序。同样如果where之后的有范围条件的话,无法用索引排序
  2. union all,in,or都能够使用索引,但是推荐使用in(其实差别不大)

  3. 范围列可以用到索引
    范围列可以用到索引,但是范围列后面的列无法用到索引,索引最多用于一个范围列

  4. 强制类型转换会全表扫描
    比如有索引:idx_1(phone),phone为varchar类型
    explain select * from user where phone=13800001234;不会报错,但也不会用到索引
    explain select * from user where phone=’13800001234’;就可以用到索引

  5. 更新十分频繁,数据区分度不高的字段上不宜建立索引

    • 更新会变更B+树,更新频繁的字段建议索引会大大降低数据库性能

    • 类似于性别这类区分不大的属性(即离散度低的数据),建立索引是没有意义的,不能有效的过滤数据

    • 一般区分度在80%以上的时候就可以建立索引,区分度(或者说离散度)可以使用 count(distinct(列名))/count(*) 来计算

      究竟用什么字段来创建索引,必须要考虑实际情况:
      比如,数据表不需要再改动也不需要再插入新数据时,当然就可以使用有序且易于比较大小的字段,查询效率会更高,但是这可能在一定程度上牺牲了空间,算是以空间换时间的方法。那么反过来,为了节省空间,我们也可以用较短或者较简单的字段去创建索引。
      但是如果建立索引之后数据会频繁插入呢?插入数据时,B+树为了维护索引的有序性,在新插入值时会做必要的维护,常常需要逻辑上挪动后面的数据以腾出位置。这时候会导致页分裂与页合并,插入效率和空间利用率都不高,这个时候还是用自增主键更好,因为顺序插入不会涉及到页分裂与页合并问题。

  1. 创建索引的列,不允许为null,为null时可能会得到不符合预期的结果(根据实际情况判断)

  2. 当需要进行表连接的时候,最好不要超过三张表,因为需要join的字段,数据类型必须一致,不然在多表查询时会效率变慢。

    MySQL join算法(以下部分引用自数据库基础(七)Mysql Join算法原理)

    1. Simple Nested-Loop Join(简单的嵌套循环连接)

    简单来说嵌套循环连接算法就是一个双层for 循环 ,通过循环外层表的行数据,逐个与内层表的所有行数据进行比较来获取结果,如图:

    img

    1. Index Nested-Loop Join(索引嵌套循环连接)

    Index Nested-Loop Join其优化的思路 主要是为了减少内层表数据的匹配次数, 简单来说Index Nested-Loop Join 就是通过外层表匹配条件 直接与内层表索引进行匹配,避免和内层表的每条记录去进行比较, 这样极大的减少了对内层表的匹配次数,从原来的匹配次数=外层表行数 * 内层表行数,变成了 外层表的行数 * 内层表索引的高度,极大的提升了 join的性能。

    img注意:使用Index Nested-Loop Join 算法的前提是匹配的字段必须建立了索引。

    1. Block Nested-Loop Join(缓存块嵌套循环连接)

      缓存块嵌套循环连接算法意在通过一次性缓存外层表的多条数据,以此来减少内层表的扫表次数,从而达到提升性能的目的。如果无法使用Index Nested-Loop Join的时候,数据库是默认使用的是Block Nested-Loop Join算法的。

      不再是逐条获取驱动表的数据,而是一块一块的获取,引入了join buffer缓冲区,将驱动表join相关的部分数据列(大小是join buffer的限制)缓存到join buffer中,然后全表扫描被驱动表,被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作),将简单嵌套循环中的多次比较合并成一次,降低了非驱动表的访问频率。

      当level 表的 user_id 不为索引的时候,默认会使用Block Nested-Loop Join算法,匹配的过程类似下图:

      img

      驱动表能不能一次加载完,要看join buffer能不能存储所有的数据,默认情况下join_buffer_size=256k,查询的时候Join Buffer 会缓存所有参与查询的列而不是只有join的列,在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段,可以让join buffer中可以存放更多的列。

    Index Nested-Loop Join 是通过索引的机制减少内层表的循环匹配次数达到优化效果,而Block Nested-Loop Join 是通过一次缓存多条数据批量匹配的方式来减少外层表的IO次数,同时也减少了内层表的扫表次数,通过 理解join 的算法原理我们可以得出以下表连接查询的优化思路。

    注意事项:

    • 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)
    • 为匹配的条件增加索引(减少内层表的循环匹配次数)
    • 增大join buffer size的大小(一次缓存的数据越多,那么内层包的扫表次数就越少)
    • 减少不必要的字段查询(字段越少,join buffer 所缓存的数据就越多)
  3. 能使用limit的时候尽量使用limit

索引本质上功能是限制输出,在sql调优的步骤中,其实最核心的是减少数据的IO量,因此使用limit能保证返回的数据量最少,查询效率也就因此得到提升。

  1. 单表索引建议控制在5个以内

主要原因是索引占用空间很大。

  1. 单索引字段数不允许超过5个(组合索引)

主要原因是是列太多导致占用太多的内存空间,会导致树的深度加深,降低效率。

  1. 创建索引的时候应该避免以下错误概念:

    • 索引越多越好。
    • 过早优化,在不了解系统的情况下进行优化。

索引选择性策略

在InnoDB存储引擎中,每个表中可以添加多个索引,若一个SQL中涉及多个索引,只会选择最优的一个

索引的选择性的策略,选择性为某一索引列不重复的值的比例;选择性大小的公式:S = C / #T
S(Selectivity)为选择性,C(Cardinality)为基数,即不重复的索引值,#T为记录总数。

巨人的肩膀

MySQL 谈谈Memory存储引擎

MySQL核心知识学习之路 页分裂与页合并

MySQL索引基数

数据库基础(七)Mysql Join算法原理

MySQL进阶系列:join连接的原理-3种算法