SQL优化及注意事项
来源:互联网 发布:淘宝助理无法显示图片 编辑:程序博客网 时间:2024/05/01 00:18
1.where子句中有"!="或"<>"操作符时将不使用索引而进行全表扫描。
select * from emp where amount != 0 (不使用)
select * from emp where amount > 0 (使用)
2.where条件中对字段增加处理函数将不使用该列的索引。
select * from emp where to_char(date,'yyyymmdd')='20151003' (不使用)
select * from emp where date = to_char('20151003','yyyymmdd') (使用)
3.避免在索引列上使用IS NULL和 IS NOT NULL。
select * from emp where t_code is not null (不使用)
select * from emp where t_code > 0 (使用)
4.通配符 % 的使用。
select * from emp where name like '%A' (不使用索引)
select * from emp where name like 'A%' (使用索引)
5、使用多表连接时,在from子句中,将记录数少的表放在后面,可提高执行效率。
6、多表查询时,如果其中一个表的记录数量明显大于其他表,则可以先对此表进行查询后,再与其他小表进行表连接。
实例:
select a.id, b.name, c.addr, sum(a.account) tot_amount
from table_name_1 a, table_name_2 b, table_name_3 c
where substr(a.depaddr,1,7) = substr(b.depaddr,1,7) and a.id = c.id
group by b.name, c.addr, a.id;
替代方案:
select b.name, c.addr, a.id, a.tot_amount
from (select id, depaddr, sum(account) tot_amount
from table_name_1 group by depaddr, id) a
table_name_2 b, table_name_3 c
where substr(a.depaddr,1,7) = substr(b.depaddr,1,7) and a.id = c.id
group by b.name, c.addr, a.id;
注意:由于table_name_1表的记录数远远大于table_name_2表和table_name_3表中的记录数, 所以首先从table_name_1表中查询需要的记录,此时记录数已经被大量缩小,然后再和其他两个表连接,速度将会得到很大改善!
7、查询数量较大时,使用表连接代替IN,EXISTS,NOT IN,NOT EXISTS等。
实例:
a、使用IN:
select sum(col2) from tab1 where col1 in (select col1 from tab2);
使用EXISTS::
select sum(col2) from tab1 a where exists (select * from tab2 where col1=a.col1);
替代方案:
a、使用连接:
select sum(a.col2) from tab1 a, tab2 b where a.col1=b.col2;
b、使用外连接:
select sum(a.col2) from tab1 a, tab2 b
where a.col1=b.col2 and b.col1 is null;
8、应尽量避免在where子句中使用or来连接条件,否则将导致不使用索引而进行全表扫描。
select id from t where num=10 or num=20 (不使用)
select id from t where num=10 union all select id from t where num=20 (使用)
注意:
一、核心
1.不在数据库做运算:cpu计算务必移至业务层
2.控制单表数据量:单表记录控制在1000w
3.控制列数量:字段数控制在20以内
4.平衡范式与冗余:为提高效率牺牲范式设计,冗余数据
5.拒绝:拒绝大sql,大事物,大批量
二、字段类
1.用好数值类型
tinyint(1Byte)
smallint(2Byte)
mediumint(3Byte)
int(4Byte)
bigint(8Byte)
2.字符转化为数字
用int而不是char(15)存储ip
3.优先使用enum或set
例如:`sex` enum (‘F’, ‘M’)
4.避免使用NULL字段
NULL字段很难查询优化,NULL字段的索引需要额外空间,NULL字段的复合索引无效
bad case:
`name` char(32) default null
`age` int not null
good case:
`age` int not null default 0
5.少用text/blob
varchar的性能会比text高很多,实在避免不了blob,请拆表
四、sql类
1.sql语句尽可能简单
2.不用select *
消耗cpu,io,内存,带宽
这种程序不具有扩展性
3.OR改写为IN()
or的效率是n级别
in的消息时log(n)级别
in的个数建议控制在200以内
select id from t where phone=’159′ or phone=’136′; => select id from t where phone in (’159′, ’136′);
4.性能分析工具
show profile;
mysqlsla;
mysqldumpslow;
explain;
show slow log;
show processlist;
show query_response_time(percona)
select * from emp where amount != 0 (不使用)
select * from emp where amount > 0 (使用)
2.where条件中对字段增加处理函数将不使用该列的索引。
select * from emp where to_char(date,'yyyymmdd')='20151003' (不使用)
select * from emp where date = to_char('20151003','yyyymmdd') (使用)
3.避免在索引列上使用IS NULL和 IS NOT NULL。
select * from emp where t_code is not null (不使用)
select * from emp where t_code > 0 (使用)
4.通配符 % 的使用。
select * from emp where name like '%A' (不使用索引)
select * from emp where name like 'A%' (使用索引)
5、使用多表连接时,在from子句中,将记录数少的表放在后面,可提高执行效率。
6、多表查询时,如果其中一个表的记录数量明显大于其他表,则可以先对此表进行查询后,再与其他小表进行表连接。
实例:
select a.id, b.name, c.addr, sum(a.account) tot_amount
from table_name_1 a, table_name_2 b, table_name_3 c
where substr(a.depaddr,1,7) = substr(b.depaddr,1,7) and a.id = c.id
group by b.name, c.addr, a.id;
替代方案:
select b.name, c.addr, a.id, a.tot_amount
from (select id, depaddr, sum(account) tot_amount
from table_name_1 group by depaddr, id) a
table_name_2 b, table_name_3 c
where substr(a.depaddr,1,7) = substr(b.depaddr,1,7) and a.id = c.id
group by b.name, c.addr, a.id;
注意:由于table_name_1表的记录数远远大于table_name_2表和table_name_3表中的记录数, 所以首先从table_name_1表中查询需要的记录,此时记录数已经被大量缩小,然后再和其他两个表连接,速度将会得到很大改善!
7、查询数量较大时,使用表连接代替IN,EXISTS,NOT IN,NOT EXISTS等。
实例:
a、使用IN:
select sum(col2) from tab1 where col1 in (select col1 from tab2);
使用EXISTS::
select sum(col2) from tab1 a where exists (select * from tab2 where col1=a.col1);
替代方案:
a、使用连接:
select sum(a.col2) from tab1 a, tab2 b where a.col1=b.col2;
b、使用外连接:
select sum(a.col2) from tab1 a, tab2 b
where a.col1=b.col2 and b.col1 is null;
8、应尽量避免在where子句中使用or来连接条件,否则将导致不使用索引而进行全表扫描。
select id from t where num=10 or num=20 (不使用)
select id from t where num=10 union all select id from t where num=20 (使用)
注意:
一、核心
1.不在数据库做运算:cpu计算务必移至业务层
2.控制单表数据量:单表记录控制在1000w
3.控制列数量:字段数控制在20以内
4.平衡范式与冗余:为提高效率牺牲范式设计,冗余数据
5.拒绝:拒绝大sql,大事物,大批量
二、字段类
1.用好数值类型
tinyint(1Byte)
smallint(2Byte)
mediumint(3Byte)
int(4Byte)
bigint(8Byte)
2.字符转化为数字
用int而不是char(15)存储ip
3.优先使用enum或set
例如:`sex` enum (‘F’, ‘M’)
4.避免使用NULL字段
NULL字段很难查询优化,NULL字段的索引需要额外空间,NULL字段的复合索引无效
bad case:
`name` char(32) default null
`age` int not null
good case:
`age` int not null default 0
5.少用text/blob
varchar的性能会比text高很多,实在避免不了blob,请拆表
四、sql类
1.sql语句尽可能简单
2.不用select *
消耗cpu,io,内存,带宽
这种程序不具有扩展性
3.OR改写为IN()
or的效率是n级别
in的消息时log(n)级别
in的个数建议控制在200以内
select id from t where phone=’159′ or phone=’136′; => select id from t where phone in (’159′, ’136′);
4.性能分析工具
show profile;
mysqlsla;
mysqldumpslow;
explain;
show slow log;
show processlist;
show query_response_time(percona)
0 0
- SQL优化及注意事项
- Oracle Sql优化注意事项
- sql 优化 注意事项
- sql优化和注意事项
- sql优化注意事项
- sql 优化注意事项
- 安装SQL Server 数据库时注意事项及优化SQL Server 数据库服务器配置
- jquery性能优化及注意事项
- SQL基础及安装注意事项
- SQL语法基础及注意事项
- SQL优化-索引 (五)其他注意事项
- Hive优化----编写SQL时注意事项
- 关于SQL优化的一些注意事项
- Mybatis中SQL性能优化注意事项
- SQL技巧及优化
- mysql执行效率优化注意事项及要点
- 深入分析PHP优化及注意事项
- mysql 索引优化、使用原则及注意事项
- Notepad++配置c++、python、java与C#
- 刚开始写博客,希望自己能坚持下来,希望能将自己的经验、感悟、知识留下来,助己及人。
- STS 数据分析
- 实战c++中的智能指针unique_ptr系列-- unique_ptr与lambda的错误结合(尤其是捕获lambda中的unique_ptr)
- linux配置静态IP地址
- SQL优化及注意事项
- 彻底搞定C语言指针详解
- 制作android native service
- AJAX 跨域请求-JSONP获取json数据
- 【Unity】游戏体销毁重新实例化GameObject后相同结构动画的衔接
- ios remote debug
- 3611: [Heoi2014]大工程
- 为明年找实习和找工作做一些计划
- 完整版move.js移动框架