查看sql查询是否用到索引(mysql)

问题发现

我认为一条很简单的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 |
+------