mysql查询表效率优化_[mysql] 单表数万行查询效率优化

问题描述

自己搭建了测试服务，目前做了一个线上监控，覆盖主流程接口，每 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，具体介绍参看

总结

了解新增删除索引的语法

了解多列索引，覆盖索引，最左原则的概念

学习分析查询语句涉及的范围，即 explain 关键字，及其结果分析

从目前来看，覆盖索引，效率最高

参考