程序员经验分享-hwhale

mysql 使用主键而不是索引

2024-03-23

我有一个相当大的表，有几百万行：

ID (primary)
countrycode
status
flag_cc

我尝试了以下sql语句，但速度很慢：

SELECT id, countrycode, status, flag_cc FROM table WHERE ID>=200000 AND countrycode=3 AND status=1 AND flag_cc=0

所以我认为添加索引来紧固该查询是个好主意：

ADD INDEX myindex(id, countrycode, status, flag_cc)

然后我问：

EXPLAIN SELECT id, countrycode, status, flag_cc FROM table WHERE ID>=200000 AND countrycode=3 AND status=1 AND flag_cc=0

但mysql想使用主键而不是我的键。所以我使用了 FORCE INDEX 并将主键与我的键进行了比较。遗憾的是主键要快得多。

怎么可能呢？如果主键太慢，是否可以优化该查询？


您的问题基本上是“什么是好的索引？”。您可能需要考虑在 MySQL 文档中、在 stackoverflow 上以及使用任何搜索引擎来阅读它们。

考虑一个类似于大百科全书中的索引的索引。定义了很多主题，因此索引可以帮助您更快地找到所需内容。

但索引中应该包含什么？类别（科学、娱乐、人物……）？然后，当您找到该类别时，每个类别中仍然有大量文章。假设总共有 10 000 篇文章，其中 1000 篇属于科学类别。如果您正在寻找一些科学的东西，那么您仍然可以找到 1000 篇文章来查找您的确切文章。用数据库术语来说，该索引没有良好的基数：如果您没有其他任何东西但不够具体以真正加快速度，那就太好了。对于以字母开头的索引（字母表中的 26 个字母，因此使用索引，您可以将要查找的文章数量除以大约 26，这也不是很具体），情况也是如此。

在数据库中，这意味着主键是一个非常适合建立索引的字段：该字段的一个值恰好对应于数据中的一个值，因此一旦使用索引来查找它，就没有什么可查找的了；您已经找到了具体记录。

另一方面，真/假标志仅将数据最多分为两组，因此即使在使用索引后仍然留下大量数据可供查看。

当然也有例外。例如，具有真/假列的表。通常这是一个不好的索引列。但您可能知道所有记录中只有 0.01% 的该列值为“true”，并且您的查询查找的是真值，而不是假值。在这种情况下，该真/假列是一个很好的索引列。

然后是范围问题：您不是在搜索特定的 ID，而是搜索它们的整个范围，因此即使 ID 是唯一的，它仍然会将索引的整个部分（以及数据）标记为“事物”使用索引后仍然可以查看'。因此，虽然它具有良好的基数，但它可能不是用于此特定查询的最佳索引。

另一个问题是，当您不搜索索引的第一列时，MySQL 无法查看多列索引。因此，索引（ID、国家/地区代码、状态、flag_cc）意味着 MySQL 仍然必须开始使用 ID 索引，这在查询中是范围条件，上一段解释了为什么这样做不好。只有在应用了索引的 ID 部分之后，如果 MySQL 确定这仍然值得付出努力，它才能从国家/地区代码部分开始。这可能就是为什么 MySQL 想要使用你的主键索引，即使你已经给了它另一个选项。

将所有这些信息应用到您的表上：您的 where 子句包含所有列，因此从基数最高（最不同的值）的列开始构建索引，并且该索引不用作范围 where 子句（因此不ID). If flag_cc包含很多不同的值，请使用它。如果status or countrycode包含更多不同的值，请使用其中之一。根据您索引的第一列的具体程度，对单个列进行索引可能就足够了。如果没有，请尝试将具有次佳基数的列添加到索引中，等等。

当然，请记住索引（通常但并非总是）可以加快查找速度，但会减慢更新、插入和删除速度！

所以你看，这不是一个很简单的问题。还要考虑到我概述的内容只是索引冰山一角。

Sources:
http://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/ http://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/
https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

mysql 使用主键而不是索引 的相关文章

  • 如何在 MySQL 查询编辑器中对列重新排序？

    我想移动专栏OtherSupport below Amount2 是否有捷径可寻 ALTER TABLE myTable MODIFY OtherSupport VARCHAR 50 AFTER Amount2

  • 如何在codeigniter中引用数据库连接？

    如何在 CodeIgniter 数据库处理程序对象中手动调用 PHP 数据库函数 如何检索连接 dbc 或者调用类似的函数mysql real escape string dbc variable 您可以调用任何 mysql 本机函数并访问

  • MySQL中Join同表临时表

    我喜欢在 MySQL 中加入一个失败的临时表 这个想法很简单 CREATE TEMPORARY TABLE temp table LIKE any other table srsly it does not matter which tab

  • Laravel 雄辩的 withCount() 应该比 with() 慢

    所以我问这个的原因是在我当前的应用程序中withCount 与仅通过以下方式获取关系的所有数据相比 响应时间几乎增加了三倍with 并只是从前端获取长度 javascript 我认为使用的要点withCount 是为了加快查询速度 但也许我

  • 如何在 MySQL 中使用 INET_ATON 进行通配符搜索 IP 地址？

    我发现这个方法可以使用 INET ATON 将 IP 地址作为整数存储在 MySQL 数据库中 https stackoverflow com a 5133610 4491952 https stackoverflow com a 5133

  • 如何将mysql数据库移动到另一个安装点

    我有一个 MySQL 数据库 它变得越来越大 我想将整个数据库移动到另一个安装点 在那里我有足够的存储空间 我希望传输当前数据 并将新数据保存到新位置 软件堆栈 在 FreeBSD 6 上运行的 MySQL 5 当然其他答案也是有效的 但如

  • MYSQL数据库删除行后需要进行后期优化

    我有一个当前为 10GB 的日志表 它有很多过去两年的数据 我真的觉得目前我不需要那么多 我是否错误地认为在表中保存多年的数据不好 表越小越好 我的桌子都有 MYISAM 引擎 我想删除 2014 年和 2015 年的所有数据 很快我就会删

  • MySQL 连接逗号分隔字段

    我有两张桌子 第一个表是batch在字段 batch 中包含逗号分隔的学生 ID 的表 batch id batch 1 1 2 2 3 4 第二个表是分数 marks id studentid subject marks 1 1 Engl

  • 如何在具有动态列的表中插入值 Jdbc/Mysql

    我想在具有动态列的表中添加值 我设法创建一个包含动态列的表 但我不知道如何插入数据 Create Table sql CREATE TABLE MyDB myTable level INTEGER 255 int columnNumber

  • 选择不带 FROM 但有多于一行的选择

    如何在不从现有表中进行选择的情况下生成 2 行 2 列的表 我正在寻找的是一个返回的选择语句 e g id value 1 103 2 556 Use UNION http dev mysql com doc refman 5 0 en u

  • MySQL REPLACE 在自动递增行中

    假设我有一个 MySQL 表 其中包含三列 id a and b和名为id is an AUTO INCREMENT场地 如果我将如下查询传递给 MySQL 它将正常工作 REPLACE INTO table id a b VALUES 1

  • MySQL：如何获取上次更新的更改

    我正在使用 MySQL 和 PHP 开发数据库应用程序 此时我正在尝试获取上次更新引起的更改 我解决问题的第一个方法是 使用 SELECT 获取 旧 状态 使用 UPDATE 进行更改 使用 SELECT 获取 新 状态 将数组与 php

  • 是否可以从数据库转储生成 knex 种子文件？

    就我而言 我使用的是 mysql 但是 我正在寻找一种通用解决方案 用于从当前运行的数据库或数据库转储生成 knex 种子文件 我可以就像是 https github com tgriesser knex issues 944 issuec

  • InnoDB 或 MyISAM - 为什么不两者都使用呢？

    我读过各种关于两者之间哪个更好的主题InnoDB and MyISAM 看来争论的都是使用还是其他 根据表的不同 是否不能同时使用两者 这样做会有什么缺点 据我所知 发动机可以在CREATE TABLE命令 因此 某些经常读取的表可以设置为

  • 带参数的 MySQL 脚本

    我想创建一个部署脚本 以某种方式模拟 Oracle 部署脚本 其中使用 param 您可以使用之前声明的参数 我需要为不同数据库上的不同用户自动调用此脚本 例如我的脚本应该是 USE param DROP TABLE IF EXISTS T

  • 为通用字符选择表排序规则

    我正在开发一个需要存储通用字符的后端 我选择了utf8mb4用于此目的的表编码 我还必须选择表格排序规则 最直接的选择是选择utf8mb4 general ci表整理 除了一般的排序规则之外 还有大约20种其他排序规则可供选择 更具体的排序

  • 在 MySQL 表中存储用户密码的最佳 PHP 哈希方法？

    我已经阅读 Stack Overflow 问题大约 15 分钟了 每一个问题似乎都与我之前读到的问题相矛盾 Bcrypt SHA1 MD5 等 我目前对我的密码进行 MD5 但我想让我的数据库在发生泄露时更加安全 我知道这个问题已经被问了一

  • Mysql 连接到服务器：用户 root@localhost 的访问被拒绝

    edit9 是否有可能我只是缺少文件夹的一些权限 我真的非常非常感谢更多的建议 edit3 由于这篇文章没有得到足够的回复 而且这绝对是至关重要的 我尽快完成这件事 我重建了我的帖子以显示我认为到目前为止我已经扣除的内容 注意 通过许多不同

  • 在 while 循环内查询可以吗？

    我在一个数据库中有两个表 我正在查询第一个表限制 10 然后循环结果 在 while 循环内 我使用第一个查询中的数据作为参数再次执行另一个查询 以下是该脚本的示例

  • MySQL-分割字符串

    我的问题与这篇文章类似 MySQL 中的 反向 GROUP CONCAT https stackoverflow com questions 17308669 reverse group concat in mysql 然而 而不是反转gr

随机推荐

  • IQueryable C# 选择

    这是我的代码 但我需要仅选择要在我的 Datagridview 中显示的列 我需要代码来仅选择某些列 示例 Select t gt t usu Login t gt t usu Login public List

  • github http 克隆返回“您在服务器上运行 git update-server-info 了吗”

    我正在尝试在 Windows 上克隆一个 github 项目 我在防火墙后面 所以无法使用 ssh 跑步 git clone https github com user project git 正在返回https github com us

  • Pandas 数据框将唯一值转换为列名

    我有以下格式的 pandas 数据框 input df gw mac mac val status 0 AC233FC01403 AC233F264A4C 21 Outwards 1 AC233FC015F6 AC233F264A4C 37

  • AngularJs 路由链接中没有主题标签？

    我最近开始学习 AngularJs 进行 Web 开发 到目前为止我很喜欢它 但是 我不太确定在视图之间路由时是否在链接中包含主题标签 我主要关心的是 Google 如何缓存网站上的页面以及链接是否双向工作 即用户是否可以直接单击 www

  • 使用 google test 将类型名和字符串传递给参数化测试

    有没有一种方法可以使用谷歌的测试将类型和字符串传递给参数化测试 我想要做 template

  • mongoskin 只能在 mongodb 1.4 及更早版本上运行吗？

    我正在尝试了解nodejs express 和 mongodb 我正在运行 mongodb v 2 0 6 和最新的 Nodejs 和 Express 并尝试通过 mongoskin 将 Express 应用程序与 mongodb 连接 问

  • caffe全卷积cnn - 如何使用裁剪参数

    我正在尝试为我的问题训练一个完全卷积网络 我正在使用实施https github com shelhamer fcn berkeleyvision org https github com shelhamer fcn berkeleyvis

  • 重复并连接字符串 N 次

    在 Ruby 中 我可以使用以下命令重复字符串 n 次 E G my string 2 gt my stringmy string 在 R 中是否有同样简单的方法来做到这一点 您可以使用replicate or rep replicate

  • 是否有 Ruby 方法来确定同一类的两个实例的所有实例变量是否相等？

    是否有一种 Ruby 方法可以根据两个对象的所有实例变量是否相等来比较它们 该方法的行为类似于此代码 class Coordinates attr reader x y def initialize x y x x y y end end

  • 规范化/标准化 numpy 重新排列

    我想知道规范化 标准化 numpy 的最佳方法是什么recarray是 为了清楚起见 我不是在谈论数学矩阵 而是在谈论一个记录数组 其中还包含例如文本列 例如标签 a np genfromtxt iris csv delimiter dty

  • 使用 Python 套接字连接不同网络上的 2 个设备

    因此 如果有一个客户端和服务器具有各自的动态 IPv4 地址 并且两者都通过 2 个单独的路由器连接到互联网 那么如何让客户端和服务器使用其动态 IPv4 地址和路由器的静态 IPv4 相互连接Python 3 中的地址 一般来说 我对网络

  • System.Drawing - 参数无效

    经过更多测试后 我发现此问题可能是由于图像未及时加载以克隆到位图并显示所致 这可能还是不可能 注意 是的 标题中还有其他与此错误相关的问题 但从一些研究来看 它似乎是一个含糊不清的错误 有许多可能的原因 我没有发现任何与我的情况相同的问题

  • Angular 代码与我的 python django 应用程序配合不佳

    由于某种原因 我无法让我的角度代码与我的 python django 应用程序很好地配合 当我提交页面时 它会将所有空值保存在我的数据库中 并且我的获取响应也无法正常工作 因为没有返回任何内容 任何帮助将不胜感激 我还提供了屏幕截图 以便更

  • 错误消息存储在SESSION中

    将错误消息存储在中是一个好习惯吗SESSION 例如重定向后 通过 url 传递对我来说不是一个解决方案 我想知道这是否是一个好的解决方案 因为 用户并发提交会导致问题吗 一篇耗时很长的文章 而ajax内容是从另一个选项卡获取的 这可能会搞

  • ldap 组件 Symfony3 配置和设置

    编写 Symfony3 应用程序并尝试配置 symfony ldap 组件来验证用户身份并从我的 AD 服务器检索用户属性 据我了解 我需要安装LDAP组件 https symfony com doc current components

  • 如何使用 while() 循环在 vertx 上运行无限循环

    我想在不同线程上的 verx 上运行无限循环 应该是这样的 vertx executeBlocking future gt while true some logic e g waiting on blocking code 事实是 在 v

  • java中的模糊逻辑

    我得到了一个涉及模糊逻辑的新项目模块 是为了确定不同的水凝物 http en wikipedia org wiki Precipitation meteorology Hydrometeor使用模糊逻辑方法 我对模糊逻辑是什么知之甚少 所以

  • Phonegap InAppBrowser - 后退按钮不会转到上一页

    我的应用程序使用 Phonegap 我需要在 InAppBrowser 中显示外部链接 但看起来后按钮没有按预期工作 如果我这样做 var ref window open www example com a html blank locat

  • 在 OS X 上的 virtualenv 中安装 scrapy 加密时发生错误 [关闭]

    Closed 这个问题需要调试细节 help minimal reproducible example 目前不接受答案 我正在安装 scrapypip in virtualenv on OS X 10 11 当它安装密码学时 它说 buil

  • mysql 使用主键而不是索引

    我有一个相当大的表 有几百万行 ID primary countrycode status flag cc 我尝试了以下sql语句 但速度很慢 SELECT id countrycode status flag cc FROM table

热门标签