查看sql查询是否用到索引(mysql)_sql 查询select 有没有使用索引-优快云博客

本文链接：https://blog.youkuaiyun.com/u012620887/article/details/107023968

本文介绍了如何通过MySQL的EXPLAIN函数来分析SQL查询是否使用索引，详细解释了EXPLAIN返回的各种参数，如type、possible_keys和key等，帮助优化查询性能。当发现查询性能低下时，应确保查询至少达到range级别，最好达到ref级别，以充分利用索引。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题发现

我认为一条很简单的SQL然后跑了很久，明明我已经都建立相应的索引，逻辑也不需要优化。

SELECT a.custid, b.score, b.xcreditscore, b.lrscore
FROM (
    SELECT DISTINCT custid
    FROM sync.`credit_apply`
    WHERE SUBSTR(createtime, 1, 10) >= '2019-12-15'
        AND rejectrule = 'xxxx'
) a
    LEFT JOIN (
        SELECT *
        FROM sync.`credit_creditchannel`
    ) b
    ON a.custid = b.custid;

查看索引状态：

credit_apply表

mysql> show index from sync.`credit_apply`;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| credit_apply |          0 | PRIMARY  |            1 | applyId     | A         |     1468496 | NULL     | NULL   |      | BTREE      |         |               |
| credit_apply |          1 | index2   |            1 | custId      | A         |      666338 | NULL     | NULL   |      | BTREE      |         |               |
| credit_apply |          1 | index2   |            2 | createTime  | A         |     1518231 | NULL     | NULL   |      | BTREE      |         |               |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

或者

CREATE TABLE `credit_apply` (
  `applyId` bigint(20) NOT NULL AUTO_INCREMENT,
  `custId` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ruleVersion` int(11) NOT NULL DEFAULT '1',
  `rejectRule` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT 'DP0000',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `extra` text COLLATE utf8mb4_unicode_ci,
  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `mobile` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT '',
  PRIMARY KEY (`applyId`) USING BTREE,
  KEY `index2` (`custId`,`createTime`)
) ENGINE=InnoDB AUTO_INCREMENT=1567035 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

sync.`credit_creditchannel`表

mysql> show index from sync.`credit_creditchannel` ;
+----------------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                | Non_unique | Key_name                    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment