项目中发现数据查询很慢，导致前端超时等待的问题。经过日志打印发现，查询sql耗时10秒以上，相关sql如下：

sql">select distincttablemodel.*from pjtask_model tablemodelJOIN buss_type_permission a ON (tablemodel.fields_data_id = a.db_category_id)wherea.status = '0'ANDa.del_flag = '0'ANDtablemodel.del_flag = '0'AND (a.validity_time IS NULLORa.validity_time > sysdate())AND (a.permission_user_id = 1ORa.permission_role_id IN (SELECTb.role_idFROMsys_user_role bWHEREb.user_id =1)ORa.permission_dept_id IN (SELECTc.dept_idFROMsys_user cWHEREc.user_id =1))and (a.permission_type = 0 or tablemodel.pj_flag = 2)and tablemodel.status  in("JDZT100010","JDZT10000","JDZT10001","JDZT10002","JDZT10006","JDZT10007","JDZT100061")order bytablemodel.create_time desc

经过查看此sql，决定查看执行计划。在sql前添加EXPLAIN。代码如下：

sql">EXPLAIN select distincttablemodel.*from pjtask_model tablemodelJOIN buss_type_permission a ON (tablemodel.fields_data_id = a.db_category_id)wherea.status = '0'ANDa.del_flag = '0'ANDtablemodel.del_flag = '0'AND (a.validity_time IS NULLORa.validity_time > sysdate())AND (a.permission_user_id = 1ORa.permission_role_id IN (SELECTb.role_idFROMsys_user_role bWHEREb.user_id =1)ORa.permission_dept_id IN (SELECTc.dept_idFROMsys_user cWHEREc.user_id =1))and (a.permission_type = 0 or tablemodel.pj_flag = 2)and tablemodel.status  in("JDZT100010","JDZT10000","JDZT10001","JDZT10002","JDZT10006","JDZT10007","JDZT100061")order bytablemodel.create_time desc

 运行结果：

mysql索引介绍可以参考：

MYSQL explain详解-CSDN博客

可以看出，联表条件为ON (tablemodel.fields_data_id = a.db_category_id)，但执行计划展示出来的信息，表示联表没有使用到索引查询，而是全量扫描表信息。

sql">select distincttablemodel.*from pjtask_model tablemodelJOIN buss_type_permission a ON (tablemodel.fields_data_id = a.db_category_id)

查看buss_type_permission（a） 表结构，发现确实没有db_category_id字段的索引。

所以我们添加索引：

再来运行执行计划语句，结果如下：

可以看到表buss_type_permission（a）的 key列，使用了db_category_id索引，rows也从6984改为了9，极大缩小了扫描数据的范围，提升sql查询效率。