针对MySQL查询数据慢的问题排查以及解决方案
(1)比如说下面这个条件,针对where后面很多条件的时候可以给用到的条件添加组合索引
select userName from user
where a=111
and b=222
and c=333
and d=444
and e=555
group by userName
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )--最左原则
如果group by慢的话 可以使用distinct来代替
select distinct userName from user
where a=111
and b=222
and c=333
and d=444
and e=555
(2)针对慢的问题,思路要清晰首先可以用explain计划查看,是否应用了自己的组合索引 ,如果索引过多会导致部分索引选不上导致失效,这种情况可以直接强制使用组合索引,具体如下:
explain select userName
from user
where a=111
and b=222
and c=333
and d=444
and e=555
group by userName
select userName
from user
force index('user_abc')
where a=111
and b=222
and c=333
and d=444
and e=555
group by userName