首页  

sql优化建议     所属分类 sql 浏览量 1193
避免使用select *
只取需要的字段,节省资源、减少网络开销。
尽可能走覆盖索引了,避免回表查询。

不使用 select * 的七个理由

查询结果只有一条或者只要最大/最小一条记录,建议用limit 1
避免全表扫描


避免在where子句中使用or
or可能会使索引失效,导致全表扫描。

select * from user where userid=1 or age =18

select * from user where userid=1 
union all 
select * from user where age = 18


limit分页优化
反例:select id,name,age from employee limit 10000,10
正例:
方案一  返回上次查询的最大记录(偏移量)
select id,name from employee where id>10000 limit 10
方案二  order by + 索引
select id,name from employee order by id  limit 10000,10


like语句优化
select userId,name from user where userId like '%123';
使用前缀索引
select userId,name from user where userId like '123%';



使用where条件限定要查询的数据,避免返回多余的行

避免在索引列上使用内置函数
查询最近七天内登陆过的用户
select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();
select userId,loginTime from loginuser where  loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);

避免在 where 子句中对字段进行表达式操作
select * from user where age-1 =10
select * from user where age =11

Inner join 、left join、right join,优先使用Inner join,如果是left join,左边表结果尽量小

Inner join 只保留两张表中完全匹配的结果集
left join  返回左表所有的行,即使在右表中没有匹配的记录。
right join 返回右表所有的行,即使在左表中没有匹配的记录。

避免在 where 子句中使用!=或<>  ,可能导致索引失效


联合索引 最左匹配原则
联合索引idx_userid_age userId在前,age在后
无法利用索引
select * from user where age = 10

以下语句符合最左匹配原则,会走索引
select * from user where userid=10
select * from user where userid=10 and age=10

考虑在 where 及 order by 涉及的列上建立索引,避免全表扫描

插入数据过多,考虑批量插入

尽量利用覆盖索引,避免回表查询

慎用distinct关键字

SELECT DISTINCT * from  user
select DISTINCT name from user

删除冗余和重复索引
KEY `idx_userId_age` (`userId`,`age`)
组合索引(A,B)相当于创建了(A)和(A,B)索引


如果数据量较大,优化修改/删除语句 ,避免一次性删除更新太多记录,尽量小批量执行

分批进行删除,如每次500
delete user where id<500
delete product where id>=500 and id<1000
一次性删除太多数据,可能导致 lock wait timeout exceed

避免使用 null 字段 
全表扫描
select * from user where age is not null
走索引
select * from user where age>0

!= <>  is null  is not null 有可能使索引失效 ,避免使用


避免多表连接

exist  in 合理利用
select * from A where deptId in (select deptId from B);
select * from A where exists (select 1 from B where A.deptId = B.deptId)
小表驱动大表,小的数据集驱动大的数据集
选择最外层循环小的,如果B的数据量小于A,适合用in,如果B的数据量大于A,适合用exist


尽量用 union all 替换 union
使用union,不管检索结果有没有重复,都会尝试进行合并,然后在输出最终结果前进行排序。
如果已知检索结果没有重复记录,使用union all 代替union

控制索引数量

尽量使用数字型字段

索引不适合建在有大量重复数据的字段上,如性别这类型数据库字段。

避免向客户端返回过多数据行

尽可能使用varchar/nvarchar 代替 char/nchar

group by 之前先过滤结果集

select job,avg(salary) from employee  group by job having job ='president' or job = 'managent'
select job,avg(salary) from employee where job ='president' or job = 'managent' group by job

如何字段类型是字符串,where条件要加引号,否则索引失效



explain 使用 
type possible_keys key
索引类型 type
ALL range




小表驱动大表 用小表的数据集驱动大表的数据集。 假设有order和user两张表,记录数 order表 10000,user 100条 查所有有效的用户下过的订单列表 select * from order where user_id in (select id from user where status=1) select * from order where exists (select 1 from user where order.user_id = user.id and status=1) in 适用于左边大表,右边小表 exists 适用于左边小表,右边大表 select id,name,age from user where id > 1000000 limit 20; 用连接查询代替子查询 select * from order where user_id in (select id from user where status=1) 子查询优点 简单,结构化 缺点 执行子查询时,需要创建临时表,查询完毕后,需要再删除这些临时表,有额外的性能消耗。 select o.* from order o inner join user u on o.user_id = u.id where u.status=1 join的表不宜过多 join过多,索引选择复杂 , 如果没有命中索引 ,nested loop join 分别从两个表读数据进行两两对比 left join 两个表的交集 + 左表剩下的数据 inner join 两个表交集 inner join ,mysql会自动选择两张表中的小表,去驱动大表 left join ,mysql默认用左表 驱动 右表 ,如果左边的表数据很多时,会有现性能问题 left join ,尽量 左边 小表,右边大表 提升group by 效率 去重 分组 select user_id,user_name from order group by user_id having user_id <= 200; 优化 分组前先过滤 select user_id,user_name from order where user_id <= 200 group by user_id
mysql explain 简介 mysql索引失效的几种情况

上一篇     下一篇
Random和SecureRandom

熵池与SecureRandom

老程序员的20条编码原则

jvm codecache 相关整理

JVM MemoryUsage中init,committed,used,max说明

堆外内存使用实例