五百万数据查询慢问题优化
导致索引失效的几个方面:
- 1、字段类型隐式转换
- 2、查询条件中包含or
- 3、like 通配符% 错误使用
- 4、联合索引最左匹配原则
- 5、索引列使用MySQL函数,索引失效
- 6、索引列存在计算,使用(+、-、*、/),索引失效
- 7、使用(!= 或者 < >,not in),导致索引失效
- 8、使用is null, is not null,导致索引失效
- 9、左连接、右连接关联字段编码不一致,索引失效
- 10、使用了select *,导致索引失效
- 11、两字段列做比较,导致索引失效
- 12、order by使用,导致索引失效
- 13、参数不同,导致索引失效
- 14、group by 使用违反最左匹配原则,导致索引失效
通过 EXPLAIN + sql 查看sql 语句的执行计划, 观察是否使用到了索引
(1) 分页查询 count(*) 查询慢问题
sql 语句:
注意where 语句可能会导致索引失效问题 count(x) 后面不要跟 order by 排序语句 , 会导致索引失效
eg:
select count(id) from log where xxx order by time desc
优化方法 :
1.使用索引:确保查询的字段上有适当的索引。如果没有索引,COUNT(*)
将会执行全表扫描,导致性能下降。可以考虑在查询的字段上创建索引,以加快查询速度
ALTER TABLE log ADD INDEX log_id_index (id);
2.使用 SHOW TABLE STATUS 或 EXPLAIN SELECT COUNT(*) FROM table_name 获取大概条数
select table_rows from information_schema.TABLES where table_schema = 'csfadb' and table_name = 'log';
EXPLAIN SELECT COUNT(*) FROM log //要使用自定义实体介绍结果
(2) find_in_set(xxx,‘value1,value2’) in 、 or 等导致索引失效拆分替换为 UNION , 或者调整where 条件顺序, 遵循最左匹配原则,保证使用到索引
select distinct main from log WHERE type in(2,3) //优化为
select distinct main from log WHERE type=2 UNION select distinct main from log WHERE type=3;
gourp by 分组查询
注意 gourp by 分组查询需要使用联合索引, 并保证 where 条件语句也能走索引, 只创建分组字段索引并不会走索引查询
(1) 将 having 条件语句改为 where 条件语句
(2) 创建联合索引
select main as 'key', count(id) as 'num' from log WHERE 1=1 and find_in_set(type,'2,3') group by main
ALTER TABLE log ADD INDEX `main__type_index`(`main`, `type`) USING BTREE //需要创建 main 与 type 两个字段的联合索引
//优化后版本
select main as 'key', count(id) as 'num' from log WHERE 1=1 and type in (1,3) group by main