为何MariaDB的ANALYZE对前置通配符查询显示USING INDEX？

你看到的Using index是覆盖索引的标识，不是说数据库用索引加速了LIKE "%thename%"的条件过滤，核心原因如下：

• InnoDB二级索引的特性：你创建的certificatedata_idx2是基于username的二级索引，InnoDB的二级索引叶子节点会自动包含主键字段（也就是你的fingerprint）。你的查询只需要返回fingerprint，过滤条件又用到username——这两个字段都在这个二级索引里，不需要去扫描体积更大的主键索引（聚簇索引）获取数据，所以触发了覆盖索引优化，Extra列显示Using index。
• 全索引扫描的选择：执行计划里的type: index说明数据库是在全扫描这个二级索引，并没有利用索引的有序性快速定位符合LIKE "%thename%"的行（前置通配符的LIKE确实没法利用B+树索引做范围查找）。优化器选择扫描二级索引而非聚簇索引，是因为二级索引的体积远小于聚簇索引（聚簇索引包含所有字段，比如大字段base64Cert），全扫描起来更高效。

你的执行计划：

MariaDB [ejbca]> ANALYZE SELECT fingerprint FROM CertificateData WHERE username LIKE "%thename%";
+------+-------------+-----------------+-------+---------------+----------------------+---------+------+---------+------------+----------+------------+--------------------------+
| id   | select_type | table           | type  | possible_keys | key                  | key_len | ref  | rows    | r_rows     | filtered | r_filtered | Extra                    |
+------+-------------+-----------------+-------+---------------+----------------------+---------+------+---------+------------+----------+------------+--------------------------+
|    1 | SIMPLE      | CertificateData | index | NULL          | certificatedata_idx2 | 1003    | NULL | 1421198 | 1539326.00 |   100.00 |       0.00 | Using where; Using index |
+------+-------------+-----------------+-------+---------------+----------------------+---------+------+---------+------------+----------+------------+--------------------------+

表结构（AWS上的MariaDB版本：10.6.16-MariaDB-log）：

CREATE TABLE `CertificateData` (
  `fingerprint` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `base64Cert` longtext DEFAULT NULL,
  `cAFingerprint` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `certificateProfileId` int(11) NOT NULL,
  `endEntityProfileId` int(11) DEFAULT NULL,
  `crlPartitionIndex` int(11) DEFAULT NULL,
  `expireDate` bigint(20) NOT NULL,
  `issuerDN` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `notBefore` bigint(20) DEFAULT NULL,
  `revocationDate` bigint(20) NOT NULL,
  `revocationReason` int(11) NOT NULL,
  `rowProtection` longtext DEFAULT NULL,
  `rowVersion` int(11) NOT NULL,
  `serialNumber` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `status` int(11) NOT NULL,
  `subjectAltName` varchar(2000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `subjectDN` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `subjectKeyId` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `accountBindingId` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `tag` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `type` int(11) NOT NULL,
  `updateTime` bigint(20) NOT NULL,
  `username` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `certificateRequest` longtext DEFAULT NULL,
  `invalidityDate` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`fingerprint`),
  UNIQUE KEY `certificatedata_idx12` (`serialNumber`,`issuerDN`),
  KEY `certificatedata_idx2` (`username`),
  KEY `certificatedata_idx4` (`subjectDN`),
  KEY `certificatedata_idx5` (`type`),
  KEY `certificatedata_idx6` (`issuerDN`,`status`),
  KEY `certificatedata_idx7` (`certificateProfileId`),
  KEY `certificatedata_idx11` (`subjectKeyId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

内容的提问来源于stack exchange，提问作者Mark Wright