一、SQL优化
SQL(结构化查询语言)的优化是提高数据库查询性能的关键。以下是一些常见的SQL优化技巧:
1、使用索引
- 为经常用于查询条件的列创建索引。
- 避免在索引列上使用函数或表达式,这会导致索引失效。
- 定期维护和重建索引以保持性能。
2、优化查询语句
- 使用EXPLAIN命令来查看查询的执行计划,并据此进行优化。
- 查询语句中不要使用select *。
- 尽量减少子查询,使用关联查询(left join,right join,inner join)替代。
- 减少使用IN或者NOT IN ,使用exists,not exists或者关联查询语句替代。
- or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时,union、all会更好)。
- 应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
- 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null 值,然后这样查询: select id from t where num=0。
二、SQL优化原则
- 全值匹配:where后的筛选条件,建立复合索引。
- 最左匹配法则:严格按照复合索引顺序。
- 索引列上不进行计算
- where后的条件判断会使右侧索引失效,尽量将条件放置在最后
- where后不能使用不等于(!= 或者<>)
- is not null 也无法使用索引,但是is null是可以使用索引的
- like不能以通配符开头('%abc...')
- 字符串加单引号,不加单引号索引失效
- 左外连接:尽量小表驱动大表,尽量在右表中建立索引
- inner join 时,mysql会自己帮你把小结果集的表选为驱动表。
- 排序分组:
- 出现 1)、无过滤,不索引
- 2)、顺序错,必Using filesort
- 3)、方向反,必Using filesort
- 如何优化?尽量where后面的字段建立索引,并且若是复合索引则严格按照建立索引时的顺序写。
- 如何避免Using filesort?1.尽量排序字段建立索引
- 2.若排序顺序尽量一致(要么都降序要么都升序)
- 不要使用select * 查询数据:一般策略是查询的字段可以尽量是索引
- 尽量减少子查询
- limit 查询优化:使用覆盖索引+子查询
三、Explain命令
在MySQL中,explain语句是一个极其重要的工具,它用于分析SQL查询的执行计划,帮助我们理解MySQL是如何处理我们的SQL语句,以及如何优化查询性能。
基本用法
explain 关键字通常放在select语句前,用于查看该查询的具体执行过程。
explain select * from 表名;
Explain重要字段
1.id:ID值越大优先级越高,则先被执行
2.select_type:查询类型
simple:简单的select查询,不会包含子查询或union;
primary:查询中若包含任何复杂的子部分,最外层的查询则会标记为primary;
derived:在from列表中包含的子查询被标记为derived,mysql会递归执行这些子查询,把结果放入临时表中;
subquery:在select或者where列表中包含了子查询。
3.table:查询用到的表名
4.type:连接的类型
性能由好到差:system(存储引擎能够直接知道表的行数)>const(通过索引一次找到,通常出现在主键或唯一索引)>eq_ref(用主键或唯一索引字段作为连接表的条件)>ref(用普通索引的字段作为连接表的条件)>range(对索引列进行范围查询)>index(利用索引扫描全表)>all(全表扫描)