索引的威力--记一次MySQL存储过程优化

一、背景：

最近接手一个老项目，在MySQL存储过程中采用【insert.... select ....】的语句，执行时发现超过130s（之后停止存储过程，没有继续执行），实际是从30多万条数据中查询后，真正要插入数据只有1800多条。我在创建了多个单列索引后，同样的语句在1s内（<0.5s）就执行完成了，速度提升了100倍，体会到了数据库索引带来的巨大威力。

这个存储过程去掉具体的业务后，出现问题的语句是这么写的：

INSERT INTO da_tb_market_index_commodity_detail(
`third_level_code`, `data_type`,
`data_date`, 
`commodity_images`,
`commodity_name`
)SELECT 
`third_level_code`, 
`data_type`,
`data_date`, 
`commodity_images`,
`commodity_name`
from da_tb_market_index_commodity_detail_temp 
where third_level_code=category_code and data_date=data_time and data_type=type_str and price_section=section
AND (commodity_ID, data_date,data_type,third_level_code,price_section) NOT IN 
(SELECT commodity_ID, data_date,data_type,third_level_code,price_section FROM da_tb_market_index_commodity_detail);

二、解决问题过程：

因为这个存储过程执行时间超过30s，不太正常，于是我在navicat中，输入 show processlist; 命令查看正在执行的任务，发现这个存储过程还在执行insert语句。因为时间太长，担心表锁死，所以我通过查看Info列中找到任务对应的Id，执行kill命令 ，停止正在执行的存储过程。

分析这段代码，首先是not in的查询条件执行会比较慢，可以使用left join来优化，这会提供一些性能，当时因为有时间要求，没有采取这个优化策略。另一个是在数据库表da_tb_market_index_commodity_detail中增加索引，来提高查询的性能，我首先建了组合索引 （commodity_ID, data_date,data_type,third_level_code,price_section）Index，效果有比较大的提升，之后我创建了多个单列索引，发现速度更快，在1s内完成了存储过程，大大超出我的想象。索引创建如下图：

三、涉及知识点：

1、索引的创建：建表时和建表后都可以创建索引， 注：索引方法默认使用B+TREE。以下是创建表时的存储过程：

CREATE TABLE 表名(
字段名 数据类型 [完整性约束条件], ……，
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]);

说明：
UNIQUE:可选。表示索引为唯一性索引。
FULLTEXT:可选。表示索引为全文索引。
SPATIAL:可选。表示索引为空间索引。
INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是    一样的。
索引名:可选。给创建的索引取一个新名称。
字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。
长度:可选。指索引的长度，必须是字符串类型才可以使用。
ASC:可选。表示升序排列。
DESC:可选。表示降序排列。

2、单列索引和组合索引的区别联系

对于只涉及单列的查询，单列索引可以快速定位到符合条件的记录，提高查询效率。相对于组合索引，单列索引的维护成本较低，因为每次数据更新时只需要更新一个索引。

组合索引可以同时考虑多个列的组合，对于涉及这些列的查询，可以更有效地定位到符合条件的记录，提高查询效率。组合索引的大小会随着索引列的增加而增加，占用更多的存储空间，特别是当索引包含大量的列时，可能会导致索引过大，影响性能。更新组合索引列的值时，需要同时更新索引的多个列，可能会增加更新的代价和时间。

本次性能优化，既有单列查询，也有组合列查询，综合比较后，采用多个单列索引性能更好。

四、总结

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，可以大大加快查询的速度，使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

数据库表中数据少的时候，加不加索引，对于查询性能没有影响。一旦数据库数据较大，比如本例中表的数据超过30多万，对于比较复杂的一些查询性能是有明显的优势的。

我现在做的电商系统，每天数据增长很快，后续如果插入数据影响性能（比如插入数据时，用户在界面上查询数据响应比较慢），我规划把目前的单库改成读写分离模式，把插入数据和查询数据分开，增加更好的用户体验。

技术不断发展，希望和大家一起进步，加油！