广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >MySQL 中这么多索引该怎么选择
  • 541

0
分享到

MySQL 中这么多索引该怎么选择

2024-04-02 19:04:59 541人浏览 薄情痞子
摘要

目录前言Mysql 单字段索引问题组合索引唯一索引和普通索引总结前言 索引的本质是存储引擎用于快速查询记录的一种数据结构。特别是数据表中数据特别多的时候，索引对于数据库的性能就愈发重要。 在数据量比较大的时候，不恰当的索

前言

索引的本质是存储引擎用于快速查询记录的一种数据结构。特别是数据表中数据特别多的时候，索引对于数据库的性能就愈发重要。

在数据量比较大的时候，不恰当的索引对于数据库的性能的影响是非常大的。在实际的应用中常常会遇见使用错误的索引而导致一系列问题，所以，选择正确的索引对于 mysql 数据库来说相当重要。

下面我们就来一起聊聊在 Mysql 数据库中该怎么选择正确的索引。

在了解怎么选择索引之前，我先给你举一个例子。如果我们在字典中用拼音查询某一个字，首先我们得根据拼音字母进而找到对应的页码。索引也是这个原理。

当我们查询一条数据的时候，我们首先在索引中查询到对应的值，然后根据匹配到的索引去找到对应数据。

例如：

mysql> select name from city where fid = 1;
+--------------+
| name         |
+--------------+
| 浦东新区      |
+--------------+
1 row in set (0.00 sec)

如果我们在fid字段上建立索引，那么 MySQL 数据库就会使用索引找到fid = 1的行，然后返回包含fid = 1的行中的所有数据。

对于 MySQL 数据库来说，索引是由存储引擎实现的，所以不同的存储引擎提供的索引也不一样。下面我们就来了解一下 MySQL 数据库中各种索引的优缺点。

MySQL 单字段索引问题

在 MySQL 数据库中，索引不能够使用表达式，具体如下：

mysql> explain select * from city where fid + 1 = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from city where fid = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | index_1       | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

从结果上讲，select * from city where fid + 1 = 2; 和 select * from city where fid = 1;是完全一致的。

但是，在explain表达式中可以看出select * from city where fid + 1 = 2;是无法命中索引的。这是因为 MySQL 数据库无法解析fid + 1 = 2这个表达式，所以我们在使用索引时，索引的列不能够是一个表达式。

总之，通常情况下，对于单个字段的索引来说，必须直接使用，不能够使用一个表达式。

组合索引

我们经常会遇见这样一个场景，假设要求查询fid=1或者name='青浦区'，这个时候我们查询的SQL语句如下：

select * from city where fid = 1 or name = '青浦区';

这个时候，我们如果要想提高查询速度，一般就会选择在fid字段和name字段上分别加上一个索引，但实际上这种做法是不恰当的。

具体如下：

mysql> explain select * from city where name = '青浦区' or fid = 1;
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | index_1,index_2 | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

我们可以看出，本次查询并没有使用到任何索引。

具体步骤如下：

  • 首先，根据name字段全表扫描查询出name = '青浦区'包含的所有结果；
  • 其次，再根据fid字段全表扫描查询出fid = 1包含的所有结果；
  • 最后，通过UNION ALL将所有的结果组合到一起并返回。

在这一过程中，MySQL 数据库需要通过全表扫描两次才能查询出结果。如果有更多的条件，查询的次数会更多。所以，在大多数情况下，多个条件查询在多个字段上建立索引并不能够提高MySQL的查询性能。

为了解决多个字段同时需要索引的这一问题，MySQL 5.0之后的版本中提供了一个组合索引。它主要是将所有的字段组合建立一个索引，这样就可以直接利用索引匹配，而不需要全表扫描了。

具体如下：

mysql> explain select * from city where name = '青浦区' or fid = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | city  | NULL       | index | index_3       | index_3 | 772     | NULL |    5 |    36.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

我们可以看出，利用了组合索引之后的查询是使用到了索引，具体如下：

  • 首先，根据索引匹配出name = '青浦区'的所有的内容；
  • 第二次查询仍然是根据fid字段全表扫描查询出fid = 1包含的所有结果；
  • 最后，通过UNION ALL将所有的结果组合到一起并返回。

在这一过程中，MySQL 数据库需要通过索引匹配两次就能查询出结果。所以，在大多数情况下，当有多个条件查询时，组合索引可以有效地提高MySQL的查询性能。

讲完单字段索引和组合索引之后，下面我们可以聊一下唯一索引和普通索引的区别以及使用场景。

唯一索引和普通索引

说起唯一索引和普通索引，有朋友可能就非常熟悉。普通索引的主要特征就是提高了查询的速度，唯一索引的主要特征除了提高查询的速度外就是所有字段的值唯一。

那么，我现在提一个问题，唯一索引和普通索引都应该在什么场景下使用呢？一定是需要唯一值的场景下才使用唯一索引吗？下面我们就来对比着聊一下普通索引和唯一索引。

为了加强了解，我们从读写性能方面来聊一下普通索引和唯一索引。

假设现在我们有一个订单系统，订单号唯一，那么我们看一下订单号在使用唯一索引和普通索引的情况下读的性能。

具体如下：

mysql> select * from sp_order where order_id = 52355096;
+----+----------+---------+--------------+-------------+-----------+------------+-------------+-------------+
| id | order_id | user_id | order_number | order_price | order_pay | pay_status | create_time | update_time |
+----+----------+---------+--------------+-------------+-----------+------------+-------------+-------------+
|  1 | 52355096 |     410 | DD52355096   |      332.44 | 2         | 1          |  1509051984 |  1507411372 |
+----+----------+---------+--------------+-------------+-----------+------------+-------------+-------------+
1 row in set (0.00 sec)

在 order_id 字段上设置唯一索引时，具体步骤如下：

  • MySQL 首先会在B-Tree的子树上查询 order_id = 52355096；
  • 再根据查询到的索引值，通过主键索引查询出对应的记录；
  • 组装结果并返回。

在 order_id 字段上设置普通索引时，具体步骤如下：

  • MySQL 首先会在B-Tree的子树上查询 order_id = 52355096；
  • 继续向下匹配，直至匹配到 order_id 不等于 52355096时；
  • 再根据查询到的索引值，通过主键索引查询出对应的记录；
  • 组装结果并返回。

唯一索引与普通索引之间对比之后，可以发现：普通索引比唯一索引多了一个步骤，就是唯一索引匹配成功之后直接返回，而普通索引还需要往下继续匹配直至条件不符合为止。

那么，在这个过程当中，普通索引与唯一索引之间的性能差多少呢？其实是微乎其微的。这是因为B-Tree算法将相邻或相近的数据都放在相邻的子树之中，索引查询性能相差无几。

聊完普通索引与唯一索引读的性能之后，我们再来聊一下写的性能。

具体如下：

mysql> update sp_order set order_price = '888' where order_id = 52355096;

对于MySQL来说，写的过程如下。

  • 首先判断需要修改的数据是否在Buffer Pool之中。

    • 如果该数据在Buffer Pool之中，则直接修改逻辑记录到Buffer Pool中的数据。
    • 如果该数据不在Buffer Pool之中，MySQL 会将这一修改的过程记录在Change Buffer之中。之后如果该条数据被查询到，则会将该修改过程merge到Buffer Pool之中，确保数据一致性。

  • 之后，再统一写入磁盘。

那么对于普通索引来说，完全适用于这一过程；但是对于唯一索引来说，按着这种方式修改数据则会影响 MySQL 数据库的性能。这是因为唯一索引在修改数据之前，还需要判断该条数据是否唯一，这样的话就需要将所有的数据全部扫描一遍，进而达到数据唯一。那么这样就不需要使用Change Buffer了，因为在修改之前，唯一索引会将所有的数据全部读取到Buffer Pool之中，直接在内存修改即可。但是不可避免的是，唯一索引会将所有的数据全部独到内存之中，无异于一次全表扫描。

于是，我们可以得出：唯一索引和普通索引都适用于读的场景，而唯一索引不适用于写的场景。

总结

本次我从根本上给你介绍了各种索引的情况。

  • 对于单个字段的索引来说，要直接使用，而不能写成一个表达式，写成表达式将会无法命中索引。
  • 对于多个字段需要索引来说，一般需要创建组合索引，这样有利于命中索引，但是一定要注意组合索引的前缀性。
  • 对于索引的类型，我还给你介绍了唯一索引和普通索引，在读的场景比较多的情况下普通索引和唯一索引都能胜任，不过在写场景比较多的情况下，普通索引的性能要优于唯一索引。

在实际应用中，我们通常建议使用普通索引，对于需要唯一的字段，我们一般在代码的层面去控制其唯一性。

到此这篇关于MySQL 中这么多索引该怎么选择的文章就介绍到这了,更多相关MySQL 索引选择内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: MySQL 中这么多索引该怎么选择

本文链接: https://www.lsjlt.com/news/34203.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • MySQL 中这么多索引该怎么选择
    目录前言MySQL 单字段索引问题组合索引唯一索引和普通索引总结前言 索引的本质是存储引擎用于快速查询记录的一种数据结构。特别是数据表中数据特别多的时候，索引对于数据库的性能就愈发重...
    99+
    2022-11-13
  • MySQL 中这么多索引该怎么选择
    目录前言mysql 单字段索引问题组合索引唯一索引和普通索引总结前言 索引的本质是存储引擎用于快速查询记录的一种数据结构。特别是数据表中数据特别多的时候，索引对于数据库的性能就愈发重要。 在数据量比较大的时候，不恰当的索...
    99+
    2022-09-26
  • MySQL innodb怎么选择一个聚簇索引
    这篇文章主要介绍了MySQL innodb怎么选择一个聚簇索引，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。对于innodb，主键毫无疑问是...
    99+
    2022-10-18
  • Mysql索引该怎么设计与优化
    小编给大家分享一下Mysql索引该怎么设计与优化，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！什么是索引？数据库索引是一种数据结构，它以额外的写入和存储空间为代价来提高数据库表上数据检索操作的速度。通俗来说，索引类似于书的...
    99+
    2023-06-08
  • MySQL多列索引怎么用
    这篇文章将为大家详细讲解有关MySQL多列索引怎么用，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。    针对此问题进行测试：假设某个表有一...
    99+
    2022-10-18
  • 为什么mysql优化器选择了聚集索引
    本篇内容介绍了“为什么mysql优化器选择了聚集索引”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！通过这个...
    99+
    2022-10-18
  • ORA-01502 索引或这类索引的分区处于不可用状态该怎么办
    本篇文章为大家展示了ORA-01502 索引或这类索引的分区处于不可用状态该怎么办，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。 OR...
    99+
    2022-10-19
  • MySQL索引为什么能让查询效率提高这么多
    本篇内容介绍了“MySQL索引为什么能让查询效率提高这么多”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！背...
    99+
    2022-10-18
  • 类型转换对MySQL选择索引有什么影响
    这篇文章主要讲解了“类型转换对MySQL选择索引有什么影响”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“类型转换对MySQL选择索引有什么影响”吧！遇到了几...
    99+
    2022-10-18
  • Vue中Element分组+多选+可搜索Select选择器怎么实现
    这篇文章主要为大家展示了“Vue中Element分组+多选+可搜索Select选择器怎么实现”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“Vue中El...
    99+
    2022-10-19
  • MongoDB中什么情况下索引会选择策略
    这篇“MongoDB中什么情况下索引会选择策略”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“MongoDB中什么情况下索引会...
    99+
    2023-06-29
  • MongoDB 中索引选择B-树的原因是什么
    这期内容当中小编将会给大家带来有关MongoDB 中索引选择B-树的原因是什么，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。一、B-树和B+树的区别很明显，我们要想弄清楚...
    99+
    2022-10-18
  • mysql中怎么查看索引
    本篇文章为大家展示了mysql中怎么查看索引，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。SHOW INDEX FROM tbl_name [FROM db_nam...
    99+
    2022-10-18
  • MySQL中怎么使用索引
    今天就跟大家聊聊有关MySQL中怎么使用索引，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。　　MySQL何时使用索引　　对一个键码使用>,&g...
    99+
    2022-10-18
  • mysql中聚集索引、辅助索引、覆盖索引、联合索引怎么用
    这篇文章主要介绍了mysql中聚集索引、辅助索引、覆盖索引、联合索引怎么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。聚集索引（Clustered Index）聚集索引就是...
    99+
    2023-06-29
  • MySQL数据库索引选择使用B+树的原因是什么
    这篇文章主要介绍MySQL数据库索引选择使用B+树的原因是什么，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！一、二叉查找树（1）二叉树简介：二叉查找树也称为有序二叉查找树，满足二叉查...
    99+
    2022-10-18
  • 怎么在Mysql中使用索引
    本篇文章为大家展示了怎么在Mysql中使用索引，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。为何要有索引一般的应用系统，读写比例在10:1左右，而且插入操作和一般的...
    99+
    2022-10-18
  • MySQL中覆盖索引怎么用
    这篇文章主要为大家展示了“MySQL中覆盖索引怎么用”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL中覆盖索引怎么用”这篇文章吧。查看测试表结构：mys...
    99+
    2022-10-18
  • 怎么在Mysql中索引下推
    这期内容当中小编将会给大家带来有关怎么在Mysql中索引下推，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本...
    99+
    2023-06-14
  • 怎么在mysql中修改索引
    这篇文章将为大家详细讲解有关怎么在mysql中修改索引，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。1、说明MySQL没有提供修正索引的直接指令。一般来说，必须删除原索引，根据需要制作同名索...
    99+
    2023-06-15
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作