当前位置：首页 > news >正文

SQL编写优化技巧

news 2025/8/18 13:32:23

一、底层原理

sql慢是因为没有走索引，因此需要添加索引然它走索引
联合索引需要匹配最左匹配原则
（索引回表）如果查询列超出索引的key，会导致回表，回表数量多，则会走全表扫描

索引是分聚集索引、非聚集索引的，因此如果select * from id = 1,这个是直接走聚集索引也就是主键索引，叶子节点存储的是全部列的数据，这样就不需要回表，但是如果是select * from name = ‘xx’ ,name列创建的索引是非聚集索引，叶子节点是存储的是（id，name)，这样会通过ID去聚集索引再去找。

索引分为聚集索引和非聚集索引
一次sql查询，只会查询一次索引
inner join \ left join \right join 需要区分驱动表和被驱动表，驱动表应该为小表，被驱动表为大表，需要在被驱动表添加索引

inner join 有优化，left join ,左边的表则为驱动表，右边的表为被驱动表。right join 右边为驱动表，左表为被驱动表。

group by 应该先过滤再group by
,<,in,between等等,都是可以使用索引的

二、sql优化

查询SQL尽量不要使用select *，而是具体字段
避免在where子句中使用 or 来连接条件

or 不走索引，可以使用union all或者union来连接不同条件的查询，虽然两者都不会走索引，但是or的话，可能会全表扫描+索引扫描+合并，如果它一开始就走全表扫描，直接一遍扫描就搞定；

尽量使用数值替代字符串类型
使用varchar代替char
避免在where子句中使用!=或<>操作符
避免在索引列上使用内置函数
优化like语句，尽量采用右模糊查询，即like ‘…%’，是会使用索引的；
使用explain分析你SQL执行计划

1、type
system：表只有一行记录，这个是const的特例，一般不会出现，可以忽略
const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。
eq_ref：唯一性索引扫描，表中只有一条记录与之匹配。一般是两表关联，关联条件中的字段是主键或唯一索引。select * from a,b where a.id = b.id
ref：非唯一行索引扫描，返回匹配某个单独值的所有行; select * from a where a.key = 1;
range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；
index：遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。all和index都是读全表，但index是从索引中检索的，而all是从硬盘中检索的。select id from b;
all：全表扫描；
性能排名：system > const > eq_ref > ref > range > index > all。
实际sql优化中，最后达到ref或range级别。
2、Extra常用关键字
Using filesort：使用外部的索引排序，而不是按照表内的索引顺序进行读取。（一般需要优化）
Using temporary：使用了临时表保存中间结果。常见于排序order by和分组查询group by（最好优化）
Using index：只从索引树中获取信息，而不需要回表查询；
Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。需要回表查询。

数据库和表的字符集尽量统一使用UTF8

字符不一致也不会走索引

3. using filesort

Using filesort表示在索引之外，需要额外进行外部的排序动作。导致该问题的原因一般和order by有者直接关系，一般可以通过合适的索引来减少或者避免。
Using filesort 的含义很简单，就是使用了排序操作，和file没有任何关系，出现这个选项的常见情况就是 Where 条件和 order by 子句作用在了不同的列上。