MySQL中的索引

最新推荐文章于 2021-11-09 10:48:04 发布
最新推荐文章于 2021-11-09 10:48:04 发布
阅读量290 收藏
点赞数
分类专栏： MySQL
本文为博主原创文章，欢迎转载，不必注明，欢迎注明。
本文链接：https://blog.csdn.net/Zereao/article/details/104906002
版权

引子

在上一篇文章《MySQL InnoDB锁类型》中，我介绍了MySQL  InnoDB引擎实现的一些锁。接下来，我再跟着官方文档，总结一下MySQL中的索引。

索引概述

索引(index)是一种为表中的行提供快速查找功能的数据结构，通常通过形成表示 特定列或一组列 的所有值 的树形结构(B-tree)来实现。

InnoDB表总是有一个表示主键的聚簇索引，当然，还可以在一列或者多列上定义一个或多个二级索引。根据其结构，二级索引还可以细分为：部分索引(partial index)、列索引(column index)和复合索引(composite index)。

索引用于快速查找具有特定值的行。如果没有索引，MySQL必须从第一行开始，然后扫描整个表来找到相关的行。表越大，消耗就越多。如果表中有相关列的索引，MySQL可以快速确定要在数据文件中间查找的位置，而不必扫描所有的数据。

聚簇索引

每张 InnoDB表 都有一个特殊的索引，称为 聚簇索引(clustered index)，用于存储 行的数据。

对于聚簇索引，有以下要点需要了解：

  • 当我们在表上定义了主键，InnoDB会将其用作聚簇索引。
  • ※ 如果我们没有定义主键，MySQL会定位到第一个 所有键列都不为空的唯一索引，InnoDB会将其用作聚簇索引。
  • ※ 如果该表没有主键，也没有合适的唯一索引，InnoDB会在包含行ID值的合成列上内部生成一个名为 GEN_CLUST_INDEX 的隐藏聚簇索引。

对于上面我标 ※ 的两个小点，可以参看原文：

  • If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.
  • If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values.

此外，对于上面第三点，既没有定义主键，也没有合适的唯一索引时，InnoDB会自动创建一个隐藏索引。此时，表中的行根据 InnoDB 分配给行的ID来进行排序的。行ID 是一个 6字节 的字段，随着新行的插入而递增。实际上，按照行ID的排序就是按照 插入顺序排序的。

还需要注意的是，修改聚簇索引的列是一项昂贵的操作，需要请选择很少或从不更新的列来做主键列。

二级索引

除了聚簇索引外的其他所有索引，都称为 二级索引(secondary indexes)。

在InnoDB中，二级索引中的每个记录都包含该行的主键列，以及为二级索引指定的列。InnoDB使用这个主键值在聚簇索引中搜索行。

如果主键很长，那么二级索引会占用很多空间，所以最好使用较短的主键。

列索引

列索引(Column Indexes)是最常见的索引类型，涉及到单个列，它会在数据结构中存储来自该列的值的副本，允许快速查找相应列值的行。

B树 数据结构允许索引在WHERE子句中快速找到特定值、一组或一系列值，对应于 =、>、≤、BETWEEN、IN 等运算符。

复合索引

复合索引，也可以叫做 联合索引、多列索引，英文是 Multiple-Clolumn indexes。一个索引最多可以包含16列。

MySQL可以对查询中的所有列使用使用复合索引，也可以只对第一列、前两列、前三列等的查询使用复合查询。所以，如果在索引的定义中以正确的顺序指定列，则一个复合索引可以加快同一个表上的多种查询的速度。

最左匹配原则

一提到复合索引，那最左匹配原则一定是一个绕不过的知识点。下面我们就拿官方文档的例子说明。

假定有下面的这个 SELECT 查询：

SELECT * FROM table_name WHERE col_1 = val_1 AND col_2 = val_2;

如果 col_1 和 col_2 两列上存在复合索引，则可以直接获取到适当的行；如果两列上分别存在单列索引，则优化器会尝试使用索引合并优化；或者尝试 通过确定哪个索引排除了最多的行 来找到限制最严格的索引，并且使用该索引来获取行。

如果表中有一个复合索引，那么优化器可以使用复合索引的任何最左前缀来查找行。官方文档原文：

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows.

比如说，如果在 (col_1, col_2, col_3)三列上具有复合索引，那么在 (col_1)、(col_1, col_2)和(col_1, col_2, col_3) 上的具有索引搜索功能。具体什么意思呢？继续看下文。

 ※ 如果 列没有形成索引 最左 的前缀，MySQL就不能使用索引执行查找。看下面的例子：

SELECT * FROM table_name WHERE col_1 = val_1;
SELECT * FROM table_name WHERE col_1 = val_1 AND col_2 = val_2;

SELECT * FROM table_name WHERE col_2 = val_2;
SELECT * FROM table_name WHERE col_2 = val_2 AND col_3 = val_3;

如果在 (col_1, col_2, col_3) 三列上存在联合索引，则上面四个查询中，只有 前两个查询能够使用到该索引。第三个和第四个查询的确涉及到了索引列，但是不会使用到索引来执行查询，因为 (col_2) 和 (col_2, col_3) 不是 (col_1, col_2, col_3) 的最左前缀。

至于上面我标 ※ 的的那句话，对应的官方文档原文是：

MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. 

这里，看一个例子：

在 (username, age, sex)三列上具有复合索引，名称为 idx_name，而 high 列上没有任何索引。

-- 查询一
SELECT * FROM test_user WHERE username = "张三" OR age = 12;
-- 查询二
SELECT * FROM test_user WHERE username = "张三" AND (age = 12 OR age = 23);
-- 查询三
SELECT * FROM test_user WHERE username = "张三" AND sex = 1;
-- 查询四
SELECT * FROM test_user WHERE username = "张三" AND high = 180;

对于上面四条查询，先说结论：查询一 不能使用该索引，而查询二、查询三和查询四都是可以使用到 idx_name 索引的。对于这个结论，我是这么理解的：

所谓 最左匹配原则，实际上就是在匹配的时候，首先拿 username列进行匹配，匹配到了，如果查询条件涉及到了 age列，就再拿 age列 进行匹配；匹配完，如果还涉及到了 sex列，就再拿 sex列 进行匹配。

在官方文档中有这样一句话：

Any index that does not span all AND levels in the WHERE clause is not used to optimize the query. In other words, to be able to use an index, a prefix of the index must be used in every AND group.

意思就是，为了能够使用索引，必须在每个 AND组 中使用索引或索引的前缀。也就是说，必须每一个 AND组 都必须被索引覆盖，当然，不同的 AND组 可以被 不同的索引覆盖。

对于上面的查询一，从理论上说，username = "张三" 是可以通过索引的，但是 后面的 OR age = 12，问题就出在这个 OR 上。这个查询，包含两个 AND组，一个是 username = "张三"，一个是  age=12。很明显，索引前缀只覆盖到了 username="张三" 这个 AND组，而 age=12 却无法使用索引。所以，整个查询无法使用索引，如下图。

对于上面的查询二，要理解就很容易了：整个查询可以分为两部分：

第一部分：age = 12 OR age = 23，这是两个 AND 组，显然，这部分无法使用索引。

第二部分：username = "张三" AND {第一部分}，这里，把上面的第一部分看作一个整体。此时，第二部分就是一个 AND组。

经过上面的拆分，我们不难理解：首先使用索引匹配 username列，匹配完后，在匹配出的结果集中，进行一个类似“全表扫描”的操作，扫前面匹配完得到的整个结果集，并且使用 age列 进行匹配。也就是说，在这个查询中，只有 username列 的匹配使用了索引，而 age列 的匹配并不会使用到索引。对整个查询来说，仍然是使用了索引的。如下图，key字段为 idx_name，表示使用到了 idx_name 这个索引。

500

对于上面的查询三，(username, sex)并不是 (username, age, sex) 的最左前缀。和查询二一样，先使用索引匹配 username列，然后在匹配出的结果集中去匹配 sex列，此时无法使用索引，对整个查询来说仍然使用了索引。如下图，也使用到了 idx_name 索引。

而对于上面的查询四，其实过程和查询三一样。直接看结果，使用到了 idx_name 索引。

至此，我也不知道还有什么要补充的了。静待各位大佬指正。

全文索引

全文索引(FULLTEXT indexes)是在基于文本的列（CHAR、VARCHAR或者TEXT列）上创建的，以帮助加快对这些列中包含的数据的查询或DML操作，它会省略掉任何被定义为“stopwords”的词。

只有 InnoDB 和 MyISAM  存储引擎支持全文索引，并且只支持 CHAR、VARCHAR 和 TEXT 列。全文索引总是在整个列上进行的，不支持列的前缀索引。

空间索引

我们可以对空间数据类型上创建空间索引(Spatial Indexes)。InnoDB和MyISAM存储引擎支持空间类型的R-tree索引，其他存储引擎使用 B-tree 来索引空间类型（ARCHIVE引擎除外，ARCHIVE不支持空间类型索引）。

倒排索引

倒排索引(Descending Indexes)也叫作降序索引，只有 InnoDB引擎。在定义索引时，可以使用“DESC”来定义倒排索引，此时，索引键值将会以降序存储。

不使用倒排索引时，索引键值顺序排列，此时可以按照相反的顺序去扫描索引，这样做会降低性能。

使用倒排索引时，键值本身就以降序存储，此时直接顺序扫描索引即可，效率就会高很多。

倒排索引只支持 B-tree 这种存储结构，而不支持 Hash 索引。全文索引(FULLTEXT) 和 空间索引 不支持 倒排索引。 

InnoDB索引的存储结构

大多数的MySQL索引都存储在 B-tree 这种数据结构中。例外是：空间数据类型的索引使用 R-tree，MEMORY(Memory存储引擎)表也支持 Hash 索引。此外，InnoDB对 FULLTEXT索引使用倒排列表。下面，我们分别介绍下这三种存储结构的使用场景，具体的数据结构这里就不多说了。

B-tree

B-tree这种结构始终保持有序，从而可以快速查找精确匹配。例如，使用 =、>、>=、<、<= 或 BETWEEN 运算符的表达式。如果 LIKE 的参数是 不以通配符开头的常量字符串，则该索引也可以用于 LIKE 的比较。

下面是官方文档上的例子：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

假定在 key_col 列上定义了一个索引。则前两个查询可以使用索引，而后两个无法使用索引。

此外，对于下面这个查询：

SELECT * FROM tbl_name WHERE col IS NULL;

如果 col 列上面有索引，那么这个查询也会使用索引进行查询。

还需要补充的是，B-tree 可以有多个子节点，所以 B-tree 和 二叉树 又有所不同。

Hash

Hash索引实际上是将索引列的数据以“键值对”的形式进行存储。对于Hash索引，需要注意的是：

  • Hash索引仅能用于使用 = 或者 <=> 运算符的相等比较，但是这非常快。不能用于 比较运算符(比如 >、≤ 等) 的范围查找。
  • 优化器无法使用Hash索引来优化 ORDER BY 操作，因为 Hash索引无法用于按顺序搜索下一个条目。
  • 使用Hash索引时，MySQL无法确定两个值之间大约有多少行。
  • 只有完整的键能用来搜索行。

对上面四点，需要补充说明的是：

Hash索引比较的是数据经过Hash运算之后的值，5 > 3，但是 hash(5) > hash(3) 却不一定成立，所以只能用于 等值比较。

MySQL范围优化器基于 两个值之间的行数 来决定要使用哪个索引，所以，此时范围优化器会受到影响。

使用 B-tree 索引时，键的任何最左前缀都可以用来搜索行。

对于Hash索引，既然存在Hash操作，那就肯定会涉及到 Hash冲突。我目前还没看到官方文档中对这一块相关的介绍，将来看到了再补充这一块。

R-tree

R-tree 用的不是太多，空间索引(Spatial indexes)使用R-tree，这是索引多维数据(例如 地理坐标、矩形或多边形)的专用数据结构。

总结

这两天时间，翻了翻MySQL索引相关的文档，感觉还需要看一看MySQL优化器相关的知识。还没看，就已经觉得自己过几天就会忘记。但是，知识是无穷无尽的，也并不是每个人都能做到过目不忘。保持学习的目的，是为了将来能够将忘记了的知识快速捡起来。加油~

参考文档

1、https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

2、https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_clustered_index

3、https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html

4、https://dev.mysql.com/doc/refman/8.0/en/column-indexes.html

5、https://dev.mysql.com/doc/refman/8.0/en/innodb-fulltext-index.html

6、https://blog.csdn.net/u013164931/article/details/82386555

7、https://blog.csdn.net/SkySuperWL/article/details/52583579

8、https://dev.mysql.com/doc/refman/8.0/en/index-btree-hash.html

9、https://www.cnblogs.com/yuan-shuai/p/3225417.html

10、https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_b_tree

11、https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html

怕乌龟骑
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
高效mysql设置_读高效MYSQL，性能优化建议
weixin_36217920的博客
01-19 198
总结自:第一章:5分钟DBA一.鉴定性能问题:1) 查找慢SQL语句:show full processlist可以得到当前mysql所有连接中正在运行的操作,此指令将会输出当前运行的所有sql列表以及其耗时信息:id:42User:***Host:****Command:QUERYTime:3Info:select * from table2) 查询计划(QEP):可以通过Explain语句查看...
MySQL 5.7+ 实用技巧、最佳实践、索引等
yaoguoxing的博客
08-17 222
前言：本讲将讲解MySQL的实用技巧.本讲内容不适合对sql语法毫无基础的初学者。 参考内容 https://github.com/michaljuhas/sample-database https://github.com/michaljuhas/SQL-training-advanced 使用平台:wampserver x64+mysql 5.7.x 0.导入数据 1.代码书写风格 注释 缩进, tab 空行 关键词打头阵 1 = 1 (这里相当于一个关节点，便于debug使用的) 别名的可读性至
mysql创建部分索引
weixin_30535913的博客
02-28 1447
mysql中，字符串如何建立索引的（本文中截取一部分） 只对字符串的前几个字符进行索引。通过字符串的前几个字符我们已经能大概排序字符串了，剩下不能排序的可以通过遍历进行查找啊，这样只在B+树中存储字符串的前几个字符的编码，既节约空间，又减少了字符串的比较时间，还大概能解决排序的问题，何乐而不为，比方说我们在建表语句中只对name列的前10个字符进行索引可以这么写： CREATE TABLE...
部分索引
Health's Box
04-23 863
部分索引（partial index） 是建立在一个表的子集上的索引；该子集是由一个条件表达式定义的 (叫做部分索引的谓词)． 该索引只包含表中那些满足这个谓词的行． 部分索引的主要动机是为了避免对普通数值建立索引? 因为如果一个在一个普通数值(那种只占表中所有行几个百分点的数值) 上的查询不会使用索引， 那么就没有在索引中保存这些(普通)行的必要． 这样就可以减小索引的尺寸，这样就可以提
mysql极限优化_高效MYSQL性能优化--小结
weixin_42562079的博客
01-19 263
总结自:第一章:5分钟DBA一.鉴定性能问题:1) 查找慢SQL语句:show full processlist可以得到当前mysql所有连接中正在运行的操作,此指令将会输出当前运行的所有sql列表以及其耗时信息:id:42User:***Host:****Command:QUERYTime:3Info:select * from table2) 查询计划(QEP):可以通过Explain语句查看...
PostgreSQL 局部索引(Partial Index)教程
neweastsun的专栏
01-24 2053
PostgreSQL 局部索引(Partial Index)教程 本文介绍局部索引，用于提升查询性能，减少索引大小。 1. 应用场景 局部索引一般应用在where条件中使用常来值得场景。 SELECT * FROM table_name WHERE column_name = constant_value; 假设有customer表,字段如下： customer(customer_id,first_name,last_name,email,address_id,create_date,last_upda
简单介绍MySQL中索引的使用方法
09-10
在MySQL数据库中，索引是一种至关重要的工具，它能够显著提升查询性能，尤其对于大型数据集来说。索引是一种特殊的数据结构，它存储在数据库中，加速了对表中数据的查找和访问。在本篇文章中，我们将深入探讨MySQL中...
MySQL中索引优化distinct语句及distinct的多字段操作
09-10
在MySQL数据库中，索引优化是提升查询性能的关键策略之一，尤其在处理大量数据时显得尤为重要。本篇文章将深入探讨如何优化使用`DISTINCT`语句的情况，以及它与多字段操作的结合。 `DISTINCT`关键字在SQL中用于去除...
MySQL联合索引用法示例
09-10
如果查询只包含联合索引中的一个列，那么数据库可能只能使用该列的部分索引，这被称为索引的最左前缀原则。例如，对于下面的查询： ```sql SELECT * FROM employee_dept WHERE employeeid = 1; ``` 即使有联合索引...
MySQL 之索引部分
潜心做事的博客
04-16 1887
索引概述 索引的本质及作用 索引（也称「键」）是为了加速表中数据行的检索而创建的一种分散的数据结构。 数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为 O(n) 的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算...
MySQL8.0 新特性：Partial Update of LOB Column
weixin_33779515的博客
05-29 113
Update history: Update: 2018/7/28 MySQL8.0.12https://dev.mysql.com/worklog/task/?id=11328 InnoDB: Undo logging is now supported for small updates to large object (LOB) data, wh...
MySQL8.0 新特性：Partial Update of LOB Column
weixin_33811961的博客
06-11 85
摘要： MySQL8.0对json进行了比较完善的支持, 我们知道json具有比较特殊的存储格式，通常存在多个key value键值对，对于类似更新操作通常不会更新整个json列，而是某些键值。 对于某些复杂的应用，json列的数据可能会变的非常庞大，这时候一个突出的问题是：innodb并不识别json类型，对它而言这些存储统一都是LOB...
MySQL部分权限回收功能（Partial Revokes）的使用---发表在爱可生开源社区
姚远OracleACE的博客
02-08 636
部分权限回收功能的说明 MySQL数据库对于对象的操作级别分为：全局、数据库、表、字段等。粒度从粗到细。如果粗的粒度的权限满足了，将不再检验细粒度的级别，这种验证方式有的时候不方便，例如需要把100个数据库中除了某一个数据库外的访问权限赋予某个用户，需要进行99次赋权。从MySQL 8.0.16开始，MySQL推出了一种部分权限回收（Partial Revokes）的功能，可以将粗粒度赋予的权限在细粒度上回收。 使用部分权限回收功能 要使用这个功能需要将系统参数partial_revokes设置成on，这个
关于PostgreSQL 和 MySQL 预防Partial Page Write 问题的解决方式
weixin_41866717的博客
11-09 929
Partial Page Write 当前所有数据库普遍采用 Write Ahead Log 策略，即先写日志在修改磁盘数据，这样可以保证内存中丢失的数据可以通过Log恢复。 而日志记录的内容是以数据页为单位的（即数据库最小操作单元），以MySQL为例： log records contain page number for the operation as well as operation data (ie update the row) and log sequence information.在PS
MySQL系统变量explicit_defaults_for_timestamp与SQL Mode，MySQL中诡异的Timestamp自动更新
蛋壳城堡
04-10 4940
引子 最近在项目中碰到了一个问题：项目使用的ORM为JPA，Entity实体中存在下面两条属性： create_time：希望在当前条目添加时，自动设置为当前值。 update_time：希望在条目每一次修改时，自动更新为修改时的当前时间。 然而在实际操作中，却出了一些列的问题，在这里，将问题记录并解决。 MySQL版本：5.6.24-log 远程连接工具：Navicat ...
MySQL事务的特性与隔离级别
蛋壳城堡
01-01 290
引子 最近准备学习一下MySQL相关的知识。学习知识最好的方式就是去看官方文档。这里，我将根据官方文档的描述，总结一下MySQL事务的四大特性——ACID，以及MySQL数据库提供的四种隔离级别。 本文主要是翻译、整理自MySQL 8.0 的官方文档，各位朋友有兴趣的建议直接去官方文档查阅！ MySQL事务的特性 对于一个数据系统来说，必须满足ACID四大特性，这些特性都与事务紧密相关。M...
mysql 中索引的算法
最新发布
09-10
MySQL中索引的算法包括以下几种： 1. B-Tree索引：B-Tree是一种多叉树结构，用于存储和管理有序数据。MySQL中使用B-Tree索引来加速对数据的查找操作。B-Tree索引适用于各种查询操作，包括等值查询、范围查询和排序...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值