概念的性的东西,先记一下。
1.什么时候索引会失效
- 最佳左前缀法则
(联合索引第一个一般是有序的,在第一个确定的情况下,再排序第二个。所以第一个没有确定的话,直接用第二个排序那就不能用索引查询。) - 计算、函数、类型转换(自动或手动)导致索引失效
- 类型转换导致索引失效
- 范围条件右边的列索引失效
- 不等于(!= 或者<>)索引失效
- is null可以使用索引,is not null无法使用索引
- like以通配符%开头索引失效
- OR 前后存在非索引的列,索引失效
- 数据库和表的字符集统一使用utf8mb4
2.查询优化
2.1关联查询优化
- 保证被驱动表的JOIN字段已经创建了索引
- 需要JOIN 的字段,数据类型保持绝对一致。
- LEFT JOIN 时,选择小表作为驱动表,大表作为被驱动表 。减少外层循环的次数。
- INNER JOIN 时,MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。 能够直接多表关联的尽量直接关联,不用子查询。(减少查询的趟数)
- 不建议使用子查询,建议将子查询SQL拆开结合程序多次查询,或使用 JOIN 来代替子查询。
- 衍生表建不了索引
2.2子查询优化
子查询的执行效率不高
在MySQL中,可以使用连接(JOIN)查询来替代子查询。连接查询 不需要建立临时表 ,其 速度比子查询要快 ,如果查询中使用索引的话,性能就会更好。
原因:
① 执行子查询时,MySQL需要为内层查询语句的查询结果 建立一个临时表 ,然后外层查询语句从临时表
中查询记录。查询完毕后,再 撤销这些临时表 。这样会消耗过多的CPU和IO资源,产生大量的慢查询。
② 子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都 不会存在索引 ,所以查询性能会受到一定的影响。
③ 对于返回结果集比较大的子查询,其对查询性能的影响也就越大。
2.3排序优化
order by 加索引
2.4优先考虑覆盖索引
什么是覆盖索引,就是你查询的where条件中的条件和查询结果一样,或者一个索引包含了满足查询结果的数据就叫做覆盖索引。
因为覆盖索引少了回表的操作,这样的效率就很高。
其他优化
什么时候用in和exit?
小表驱动大表用exit
大表驱动小表用in
通过limit 1 提高效率
limit 1,这样查到之后就不会往后面查了,避免了全表扫描
为什么不使用select * 呢
select *没办法使用覆盖索引
而且还有列转换,影响性能。