问题描述

自己搭建了测试服务，目前做了一个线上监控，覆盖主流程接口，每 3 分钟一次，累计约有 8 万条记录

表结构：

DROP TABLE IF EXISTS `tb_api_test_result`;
CREATE TABLE `tb_api_test_result` (
    `result_id` int(11) NOT NULL AUTO_INCREMENT,
    `suite_id` int(11) NOT NULL DEFAULT '0',
    `case_id` int(11) NOT NULL DEFAULT '0' ,
    `result` enum('Pass','Fail','Skip','') NOT NULL DEFAULT ''  COMMENT '用例测试结果',
    `error_info` text NOT NULL COMMENT '异常堆栈信息',
    `res_body` text NOT NULL COMMENT '接口响应内容',
    `case_start_time` int(10) NOT NULL DEFAULT '0' COMMENT '用例开始时间',
    `case_end_time` int(10) NOT NULL DEFAULT '0' COMMENT '用例结束时间',
    `case_run_time` int(10) NOT NULL DEFAULT '0' COMMENT '用例执行用时',

    PRIMARY KEY (`result_id`),
    KEY `idx_project_id` (`case_id`),
    KEY `idx_suite_id` (`suite_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='接口测试用例结果表';

执行以下查询：

SELECT result_id, case_run_time FROM tb_api_test_result 
WHERE case_id=9 AND result='Pass' 

筛选出 id 为 9 且结果为 pass 的用例的执行耗时和主键

用时：6.7~10 秒

如何优化

我们都知道增加索引可以加快查询的速度，于是

ALTER TABLE tb_api_test_result ADD INDEX idx_caseId_caseRunTime (`case_id`, `case_run_time`)

结果如何？仍然用了 6.7~10 秒
为啥，不是加了索引就应该变快么？

网上查了一下，可以建一个覆盖索引，以提高效率，于是又建了一个索引

ALTER TABLE tb_api_test_result ADD INDEX idx_resultId_caseRunTime (`result_id`, `case_id`, `result`, `case_run_time`)

再查，还是要那么久，到底嘛意思，崩溃

接下来，尝试，除了主键索引，其他索引全删掉，只留一个覆盖索引，这次结果如何呢？
哇，0.1 秒就查完了，哈哈

后来问了小伙伴才知道，索引还有一个最左前缀原则

新增索引

ALTER TABLE tableName ADD INDEX indexName (`colName1`, [ `colName2`, ...])

删除索引

DROP INDEX indexName ON tableName;

覆盖索引

需要查询的内容在该索引内，没有查询额外的列

最左原则

这个我也说不好，看别人的吧

Explain

借助 explain 这个关键字，我们可以了解该查询语句需要查询的范围

未建索引，或未建查询列的索引

EXPLAIN SELECT result_id, case_run_time FROM tb_api_test_result 
WHERE case_id=9 AND result='Pass' 

看到的是需要查整张表的

按查询条件，建立多列索引

ALTER TABLE tb_api_test_result ADD INDEX idx_caseId_result (`case_id`, `result`)

按查询条件和 select 的列，建立覆盖索引

ALTER TABLE tb_api_test_result ADD INDEX idx_resultId_caseRunTime (`result_id`, `case_id`, `result`, `case_run_time`)

这里主要看 type，有 system,const,eq_ref,ref,range,index,all，具体介绍参看

总结

1. 了解新增删除索引的语法
2. 了解多列索引，覆盖索引，最左原则的概念
3. 学习分析查询语句涉及的范围，即 explain 关键字，及其结果分析
4. 从目前来看，覆盖索引，效率最高

参考

高性能 MySQL，第三版
MySQL 单表查询优化技巧