MySQL索引

type
status
date
slug
summary
tags
category
icon
password

索引

索引的概念

索引是帮助MySQL高效获取数据(作用)的一种数据结构(本质)
是一种树结构。

索引的优缺点

优点

  1. 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。(没有索引，查询数据时，会进行全表查询，对数据表的每一条数据进行对比)。
  1. 可以通过索引列堆数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点

  1. 索引本身也算是一张表，该表中保存主键与索引字段，索引列也是要占用磁盘空间的。
  1. 索引虽然能提高查询效率，但是会降低更新表的速度(删，改，增)。因为数据变化后，需要重新更新索引的信息。

索引结构

索引是在MySQL的存储引擎层中实现的，而不是服务器层实现的。所以每种存储引擎的索引结构都不一定是完全相同的。
Btree：最常见的索引类型，mysql默认的存储引擎InnoDB中的索引就是采用这种结构。
Hash索引：MySQL中Memory存储引擎默认支持的索引类型。
R-tree索引(空间索引)：空间索引是MyISAN引擎的一个特殊索引类型。使用较少。
Full-text（全文索引）：全文索也是MyISAM的一个特殊索引类型，主要用于全文索引。
notion image

BTREE结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：。
  • 树中每个节点最多包含m个子节点；
  • 所有的叶子节点都在同一层；
  • 每个非叶子节点由n个key与n+1个指针组成。(也就是key总是比指针个数少一个)
  • 除根节点与叶子节点外，每个节点至少有【ceil（m/2】个孩子。(ceil是向上取整)
  • 若根节点不是叶子节点，则至少有两个孩子。(子节点)
notion image
notion image

B+TREE结构(mysql的B+tree)

B+Tree为BTree的变种，B+Tree与BTree的区别为：
  1. n叉B+Tree最多含有n个key,而BTree最多含有n-1个key
  1. B+Tree的叶子节点保存了所有的key信息，依key大小顺序排列。
  1. 所以有非叶子节点有可以看做key的索引部分。
  1. 数据都是存放在叶子节点中。
💡
而MySQL的B+Tree对原本的B+Tree进行了优化，相邻叶子节点之间具有指针，形成了一个链式环结构。这样主要是为了提高范围搜索。
notion image

区别

BTree和B+Tree之间的区别
  • 非叶子节点只存储键值信息。
  • 所有叶子节点之间都有一个连接指针。提高区间访问性能。
  • 数据记录都存放在叶子节点中
从BTree结构图中可以看到每个节点中不仅包含数据的key值，还有value值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致BTree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

索引分类

  • 普通索引： 最基本的索引，它没有任何限制。
  • 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
  • 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。
  • 组合索引：顾名思义，就是将单列索引进行组合。
  • 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
  • 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。

索引语法

MYSQL

索引设计原则

  1. 对查询频次较高，且数据量比较大的表建立索引。
  1. 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。(也就是将经常作为条件的列创建索引)
  1. 使用唯一索引，区分度越高，使用索引的效率越高。（也就是对具有唯一属性的列创建索引）
  1. 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。(总的来说就是，在一张数据表中索引不要创建太多了)
  1. 创建组合索引时，要注意最左匹配原则。
最左前缀匹配原则   MYSQL

存储引擎

MySQL数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平以及广泛的不同的功能和能力，在MySQL中 , 将这些不同的技术及配套的功能称为存储引擎。（关系型数据库是以表的形式存储数据，所以存储引擎的类型也称表类型）
MySQL5.7支持的引擎包括：InnoDB(默认)、MyISAM(5.0之前默认)、MEMORY、Archive、Federate、CSV、BLACKHOLE等。
(概念随便看一看就行，主要记主要的引擎特点和区别)

MySQL的两个主要的引擎

特性
MyISAM
InnoDB
MEMORY
存储限制
有(平台对文件系统大小的限制)
64TB
有(平台的内存限制)
事务安全
不支持
支持
不支持
锁机制
表锁
表锁/行锁(适合高并发)
表锁
B+Tree索引
支持
支持
支持
哈希索引
不支持
不支持
支持
全文索引
支持
支持
不支持
集群索引
不支持
支持
不支持
数据索引
不支持
支持
支持
数据缓存
不支持
支持
N/A
索引缓存
支持
支持
N/A
数据可压缩
支持
不支持
不支持
空间使用
低
高
N/A
内存使用
低
高
中等
批量插入速度
高
低
高
外键
不支持
支持
不支持

引擎的选择

MyISAM：对于读多写少(查询多，增删改少)采用MyISAM引擎，并且对事务的完整性、并发性要求不是很高。
InnoDB：对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作，查询少。

SQL语句的优化

定位低效率SQL

查看sql语句执行的频率
show [session] [global] status
参数：session,查看当前连接的统计结果。
global,查看数据库启动至今的统计结果。
show global status like 'Com_______';
只查看对引擎为innodb的表的sql执行频率
show global status like 'Innodb_rows_%';
查看sql执行的详细信息（比如，执行时间，执行sql的用户，sql类型等）
show processlist

查看SQL语句执行计划

在执行SQL语句前加关键字EXPLAIN。
查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
常见的几种类型具体含义如下：
  • system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
  • const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
  • eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
  • ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
  • index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
  • range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
  • index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
  • ALL：全表扫描

索引失效

  1. 有组合索引时，不遵循最左前缀匹配原则，会导致索引失效。
  1. 使用范围查询(比如：> , < ,between关键字)，则范围查询后的查询条件会索引失效。
  1. 对索引列进行运算操作(比如：+，-，*，substring截取等)，索引会失效。
  1. 字符串类型不加单引号('')，会导致索引失效。
  1. 尽量避免使用select * ，虽然select * 不会导致索引失效，但会进行回表查询。(有点难解释)
      2. select后面的字段如果都是创建了索引的列(即覆盖索引)，那么查询时，由于索引中也存有索引列的数据，直接返回查询结果；但是select后面的字段有不是索引的列，查询索引时，索引没有该列的数据，所以就需要到数据表中查询该列数据（要时间），最后返回（注意，主键索引(聚集索引)是特殊的，主键索引的数据就包含了整行数据，不能简单理解为select id from 表 where id = xxx才不会回表）。
  1. 用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
  1. 以%开头的like模糊查询,会导致索引失效。如果必须使用%开头的模糊查询，可以采用覆盖索引的方式，即select后的字段必须都是创建了索引的列。
  1. is null或is not null都会有可能导致索引失效。建议不对含Null字段添加索引（因为Null值在mysql是不确定的值）
  1. in走索引，而not in 会导致索引失效。IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同)
  1. 尽量使用复合索引，而少使用单列索引（节省空间）。
      2. 根据最左前缀匹配原则，如对三个列(a,b,c)创建复合索引，实际上是创建了三个索引（a，ab，abc）。进行多条件查询时，能匹配的索引更多。
      但是对三个列分别创建索引(a，b，c)，在进行多条件查询时，数据库只会选择一个最优的索引。
💡
注意1，对于回表查询的情况，主键索引（聚集索引）是特殊的。只要是通过主键查询，即使是select * from t where id = x;也不会回表，这是因为主键索引的数据域包含了整行数据。
💡
注意2，对回表查询的定义。能通过非聚集索引（辅助索引）拿到所有列的值，而不是后续再根据主键id去主键索引中获取其它列的值，就不算回表查询。
https://chat.openai.com/share/a4d660ee-d5a6-4a23-a434-243125f2526e
 
  • GitTalk

© aya 2024-2025