MySQL 解释 - 需要索引建议/反馈-6ren

MySQL 解释 - 需要索引建议/反馈

转载作者：可可西里更新时间：2023-11-01 08:35:35

<分区>

我有一个包含大约 500 万行的数据库，我遇到了查询耗时很长(超过一分钟)的问题。我希望这些信息足以让别人给我一些建议，如果我需要发布更多信息，请告诉我。

提前感谢您的任何建议

EXPLAIN SELECT count( * ) AS count
FROM vtiger_time
INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_time.timeid
INNER JOIN vtiger_crmentityrel ON ( vtiger_crmentityrel.relcrmid = vtiger_crmentity.crmid
OR vtiger_crmentityrel.crmid = vtiger_crmentity.crmid )
LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid
WHERE vtiger_crmentity.deleted =0
AND (
    vtiger_crmentityrel.crmid =211294
    OR vtiger_crmentityrel.relcrmid =211294
)


+----+-------------+---------------------+-------------+-------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
| id | select_type | table               | type        | possible_keys                                   | key            | key_len | ref                            | rows  | Extra                                    |
+----+-------------+---------------------+-------------+-------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
|  1 | SIMPLE      | vtiger_crmentityrel | index_merge | crmid,relcrmid                                  | crmid,relcrmid | 4,4     | NULL                           |  5881 | Using union(crmid,relcrmid); Using where | 
|  1 | SIMPLE      | vtiger_crmentity    | ref         | PRIMARY,deleted,deleted_2,crmentity_multi_index | deleted_2      | 4       | const                          | 84424 | Using where; Using index                 | 
|  1 | SIMPLE      | vtiger_users        | eq_ref      | PRIMARY                                         | PRIMARY        | 4       | crm.vtiger_crmentity.smownerid |     1 | Using index                              | 
|  1 | SIMPLE      | vtiger_groups       | eq_ref      | PRIMARY                                         | PRIMARY        | 4       | crm.vtiger_crmentity.smownerid |     1 | Using index                              | 
|  1 | SIMPLE      | vtiger_time      | eq_ref      | PRIMARY,timeid                               | PRIMARY        | 4       | crm.vtiger_crmentity.crmid     |     1 | Using index                              | 
+----+-------------+---------------------+-------------+-------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+

另外这些是我当前设置的索引

+------------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_crmentity |          0 | PRIMARY                    |            1 | crmid       | A         |      755968 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          0 | crmid                      |            1 | crmid       | A         |      755968 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crmentity_IDX0             |            1 | smcreatorid | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crmentity_IDX1             |            1 | smownerid   | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crmentity_IDX2             |            1 | modifiedby  | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | deleted                    |            1 | deleted     | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | deleted                    |            2 | smownerid   | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | smownerid                  |            1 | smownerid   | A         |         199 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | smownerid                  |            2 | deleted     | A         |         199 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | deleted_2                  |            1 | deleted     | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | deleted_2                  |            2 | smownerid   | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | smownerid_2                |            1 | smownerid   | A         |         385 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | smownerid_2                |            2 | deleted     | A         |         758 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crm_ownerid_del_setype_idx |            1 | smownerid   | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crm_ownerid_del_setype_idx |            2 | deleted     | A         |          15 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crm_ownerid_del_setype_idx |            3 | setype      | A         |         613 |     NULL | NULL   | YES  | BTREE      |         | 
| vtiger_crmentity |          1 | crmentity_multi_index      |            1 | crmid       | A         |      755968 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crmentity_multi_index      |            2 | smownerid   | A         |      755968 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentity |          1 | crmentity_multi_index      |            3 | deleted     | A         |      755968 |     NULL | NULL   |      | BTREE      |         | 
+------------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table               | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_crmentityrel |          1 | crmid    |            1 | crmid       | A         |      223960 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_crmentityrel |          1 | relcrmid |            1 | relcrmid    | A         |       12442 |     NULL | NULL   |      | BTREE      |         | 
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+--------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_users |          0 | PRIMARY       |            1 | id            | A         |          39 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_users |          1 | idx_user_name |            1 | user_name     | A         |          39 |     NULL | NULL   | YES  | BTREE      |         | 
| vtiger_users |          1 | user_password |            1 | user_password | A         |          39 |     NULL | NULL   | YES  | BTREE      |         | 
+--------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+

+---------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_groups |          0 | PRIMARY             |            1 | groupid     | A         |           5 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_groups |          1 | groupname           |            1 | groupname   | A         |           5 |     NULL | NULL   | YES  | BTREE      |         | 
| vtiger_groups |          1 | idx_groups_123group |            1 | groupname   | A         |           5 |     NULL | NULL   | YES  | BTREE      |         | 
+---------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_time |          0 | PRIMARY     |            1 | timeid   | A         |      591772 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_time |          0 | timeid   |            1 | timeid   | A         |      591772 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_time |          1 | relatedto   |            1 | relatedto   | A         |        1405 |     NULL | NULL   | YES  | BTREE      |         | 
| vtiger_time |          1 | date_start  |            1 | date_start  | A         |        7129 |     NULL | NULL   | YES  | BTREE      |         | 
| vtiger_time |          1 | relatedto_2 |            1 | relatedto   | A         |        3269 |     NULL | NULL   | YES  | BTREE      |         | 
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vtiger_timecf |          0 | PRIMARY     |            1 | timeid   | A         |      591324 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_timecf |          0 | timeid   |            1 | timeid   | A         |      591324 |     NULL | NULL   |      | BTREE      |         | 
| vtiger_timecf |          1 | timeid_2 |            1 | timeid   | A         |      591324 |     NULL | NULL   |      | BTREE      |         | 
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

添加如下索引后

ALTER TABLE vtiger_crmentity ADD INDEX TMP_deletion_smownerid_crmid (smownerid, deleted,crmid);

新解释:

+----+-------------+---------------------+-------------+--------------------------------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
| id | select_type | table               | type        | possible_keys                                                            | key            | key_len | ref                            | rows  | Extra                                    |
+----+-------------+---------------------+-------------+--------------------------------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+
|  1 | SIMPLE      | vtiger_crmentityrel | index_merge | crmid,relcrmid                                                           | crmid,relcrmid | 4,4     | NULL                           |  5891 | Using union(crmid,relcrmid); Using where | 
|  1 | SIMPLE      | vtiger_crmentity    | ref         | PRIMARY,crmid,deleted,deleted_2,crmentity_multi_index,_deletion_crmid | deleted        | 4       | const                          | 84424 | Using where; Using index                 | 
|  1 | SIMPLE      | vtiger_users        | eq_ref      | PRIMARY                                                                  | PRIMARY        | 4       | crm.vtiger_crmentity.smownerid |     1 | Using index                              | 
|  1 | SIMPLE      | vtiger_groups       | eq_ref      | PRIMARY                                                                  | PRIMARY        | 4       | crm.vtiger_crmentity.smownerid |     1 | Using index                              | 
|  1 | SIMPLE      | vtiger_time      | eq_ref      | PRIMARY,timeid                                                        | PRIMARY        | 4       | crm.vtiger_crmentity.crmid     |     1 | Using index                              | 
+----+-------------+---------------------+-------------+--------------------------------------------------------------------------+----------------+---------+--------------------------------+-------+------------------------------------------+

文章推荐： php - 在 7 天日历中显示当前周的 mysql 数据

文章推荐： mysql - 按星期几分组销售

文章推荐： Android以编程方式更改应用程序图标

文章推荐： mysql - 如何在 mysql 中将两个 varchar 值与前导零进行比较

SQL - NOT IN 解释
我在一个项目中工作，该项目需要 SQL 结果的最佳性能，并且希望优化查询，但经过反复试验后，我在 IN 方面遇到了一些问题。 -- THIS RETURNS NO RESULTS AT ALL. SE
SQL NOT IN 解释
在尝试创建一个实际上非常简单的 SQL 语句时，我发现自己迷失了方向。我有一个包含 3 个表的数据库: 食谱 - 存储一些用于 cooking 的食谱名称配料食谱 - 将配料与食谱链接成分 -
PHP函数hebrev()解释
我正在尝试理解 PHP 中的 Hebrev 函数。 https://php.net/manual/en/function.hebrevc.php 它说:“将逻辑希伯来语文本转换为视觉文本”。但我不明白
Java语法解释
嗨，我在 Grid view 的 android 文档中发现了一段代码对于以下代码。 gridview.setOnItemClickListener(new OnItemClickListener()
InfiniBand 解释
谁能解释一下 InfiniBand 是什么？与以太网相比的主要区别是什么，这些差异如何使其比以太网更快？在官方description从 mellanox 写到 Introduce InfiniBan
Java运算符，解释
这个问题已经有答案了: How are java increment statements evaluated in complex expressions (1 个回答) 已关闭 8 年前。我知道
MySQL :/*! */解释
我正在阅读 MySQL 教程，我遇到了这个: SELECT /*! SQL_NO_CACHE */ user FROM users; 为什么优化提示 SQL_NO_CACHE 包含在: /*!
javascript - 解释 $(this)
我无法理解$(this)，我做了一个剪刀石头布的版本，并应用了 jQuery 让用户在计算机上选择按钮选项。我希望有人能解释一下 $(this) 指的是什么，它是 btn-primary 吗？该函数在
C++ "while"解释
我不是很确定 while(choice == 1 || choice ==2);谁能解释一下。我明白这一点 if(choice ==1) displayMonthly(rainfall); e
iOS图层动画-解释
let flyRight = CABasicAnimation(keyPath: "position.x") flyRight.toValue = view.bounds.size.width/2 f
解释:int型默认值为0的问题
目录解释:int型默认值为0 但我们尝试发现并不能通过：原因： int的默认值为0，而Integer的默认值为null
sql - 合并和案例陈述 - 解释？
我正在处理一个查询，自从一个 SSRS 服务器传输到另一个服务器后，它似乎没有按预期执行，并且 where 语句的一部分中出现了以下行找出不同之处，或者至少从我能找到的地方来看。 where COA
email - 解释 VERP
我正在制作一个退回检测程序，读取退回邮件。我们的设置是发送电子邮件，在发送的邮件中添加一个 noreply@domain.tl。一些收件人不再存在，因此我们想要读取退回邮件，并检测它发送给谁。我已经崩
math - 解释 - 通过控制点曲线的公式
我有一个关于公式通过控制点弯曲的问题。如您所知，HTML Canvas 有 quadraticCurveTo(x1, y1, x2, y2)与 x1 and x2作为控制点。但是，当您尝试使用它绘
Erlang emakefile 解释
我有一个 Emakefile看起来像: %% -- %% %% -- {'/Users/user/projects/custom_test/trunk/*', [debug_info, {out
scala - 解释 - 不涉及反射
我有一个非常简单的问题。这不仅适用于 spray-json，而且我已经阅读了 argonaut 和 circe 的类似声明。所以请赐教。在 spray-json 中，我遇到了 There is no
ffmpeg scale2ref 解释？
我正在为视频添加水印。我试图让水印与视频尺寸成比例。我已经使用 scale2ref 看到了十几个不同的答案，但没有解释实际发生了什么，所以我发现很难知道如何实现/更改配置以适应我的情况。当前覆盖命令
Haskell长度+ map 解释？
因为我正在学习语言，所以我在玩 Haskell，我只是发现了一些我不理解的东西，我找不到解释。如果我尝试运行此代码: map (`div` 0) [1,2,3,4] 我得到一个除以 0 的异常，这是预
.net - 未将对象引用设置为对象的实例 - 解释？
我正在寻找解决错误对象引用未设置到对象实例的步骤/指南。以及问题发生原因的解释。我正在寻找更一般的解释，所以如果我收到错误，我应该采取什么步骤来查找问题。我经常看到有人提供特定代码段的帖子，而其他人
reactjs - `componentWillReceiveProps` 解释
我最近想升级我的知识React ，所以我从组件生命周期方法开始。让我好奇的第一件事是这个componentWillReceiveProps .所以，文档说当组件接收新的(不一定是更新的) Prop 时

可可西里

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

MySQL 解释 - 需要索引建议/反馈