SQL优化-策略
一、SQL优化方案(建议及策略)
- 对查询进行优化,应尽量避免全表扫描,首先应考虑在
WHERE
及ORDER BY
涉及的列上建立索引。 - 尽量避免在 WHERE 子句中对字段进行
NULL
值判断,创建表时NULL
是默认值,但大多数时候应该使用NOT NULL
,或者使用一个特殊的值,如 0,-1 作为默认值。 - 尽量避免在
WHERE
子句中使用!=
或<>
操作符。MySQL 只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN
,以及某些时候的LIKE
。 - 尽量避免在
WHERE
子句中使用OR
来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,可以使用UNION
合并查询:select id from t where num=10 union all select id from t where num=20。
IN
和NOT IN
也要慎用,否则会导致全表扫描。对于连续的数值,能用BETWEEN
就不要用IN
:select id from t where num between 1 and 3
。- 下面的查询也将
导致全表扫描
:select id from t where name like‘%abc%’
或者select id from t where name like‘%abc’
若要提高效率,可以考虑全文检索
。而select id from t where name like‘abc%’
才用到索引。 WHERE
子句中使用参数,也会导致全表扫描
。- 尽量
避免在 WHERE 子句
中对字段进行表达式操作,应尽量避免在 WHERE 子句中对字段进行函数操作; - 很多时候用
EXISTS
代替IN
是一个好的选择:select num from a where num in(select num from b)
。用下面的语句替换:select num from a where exists(select 1 from b where num=a.num)
。 索引
固然可以提高相应的SELECT 的效率
,但同时也降低了 INSERT 及 UPDATE 的效率
。因为INSERT 或 UPDATE 时有可能会重建索引
,所以怎样建索引需要慎重考虑
,视具体情况而定。一个表的索引数最好不要超过 6 个
,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
二、SQL优化 20 连击。
1、查询SQL语句时尽量不要使用select *
,而是具体字段
。
1、反例。
SELECT * FROM hcuser
2、正例。
SELECT id,username,userid,tel FROM hcuser
3、理由。
1. 节省服务器资源、减少服务器网络开销。
2. 可能用到覆盖索引,减少回表,提高查询效率。
2、避免
在where子句
中使用 or
来连接条件。
1、反例。
SELECT id,username,userid,tel FROM hcuser WHERE id=1 OR userid=5000
2、正例。
使用union all
SELECT id,username,userid,tel FROM hcuser WHERE id=1
UNION ALL
SELECT id,username,userid,tel FROM hcuser WHERE userid=5000
分两条SQL去写。(不推荐,需要查询两次)
SELECT id,username,userid,tel FROM hcuser WHERE id=1
SELECT id,username,userid,tel FROM hcuser WHERE userid=5000
3、理由。
1.使用or可能会使索引失效,从而全表扫描;
2.对于or没有索引的salary这种情况,假设它走了id的索引,但是走到salary查询条件时,它还得全表扫描;
3.也就是说整个过程需要三步:全表扫描+索引扫描+合并。如果它一开始就走全表扫描,直接一遍扫描就搞定;
4.虽然mysql是有优化器的,出于效率与成本考虑,遇到or条件,索引还是可能失效的;
3、尽量
使用数值替代字符串
类型。
1、正例。
1.主键(id):primary key优先使用数值类型int,tinyint
2.性别(sex):0代表女,1代表男;数据库没有布尔类型,mysql推荐使用tinyint
2、理由。
1.因为引擎在处理查询和连接时会逐个比较字符串中每一个字符;
2.而对于数字型而言只需要比较一次就够了;
3.字符会降低查询和连接的性能,并会增加存储开销;
4、使用varchar代替char
。
1、反例。
`address` char(100) DEFAULT NULL COMMENT '地址'
2、正例。
`address` varchar(100) DEFAULT NULL COMMENT '地址'
3、理由。
1.varchar变长字段按数据内容实际长度存储,存储空间小,可以节省存储空间;
2.char按声明大小存储,不足补空格;
3.其次对于查询来说,在一个相对较小的字段内搜索,效率更高;
5、varchar与char
的区别?(技术延伸及理论)。
char的长度是固定
的,而varchar2的长度是可以变化
的。
例如,存储字符串“101”,对于char(10),表示你存储的字符将占10个字节(包括7个空字符),
在数据库中它是以空格占位的,而同样的varchar2(10)则只占用3个字节的长度,10只是最大值,
当你存储的字符小于10时,按实际长度存储。
char的效率比varchar2的效率稍高
。- 什么时候用char,什么时候用varchar2呢?
char和varchar2
是一对矛盾的统一体
,两者是互补的关系,varchar2比char节省空间
,在效率上比char会稍微差一点
,既想获取效率,就必须牺牲一点空间,这就是我们在数据库设计上常说的“以空间换效率”。
varchar2虽然比char节省空间
,但是假如
一个varchar2列经常被修改
,而且每次被修改的数据的长度不同,这会引起“行迁移”现象
,而这造成多余的I/O
,是数据库设计中要尽力避免
的,这种情况下用char代替varchar2会更好一些
。char中
还会自动补齐空格
,因为你insert到一个char字段自动补充了空格的
,但是select后空格没有删除
,因此char类型查询的时候一定要记得使用trim
。
如果开发人员细化使用rpad()技巧
将绑定变量转换为某种能与char
字段相比较的类型(当然,与截断trim数据库列相比,填充绑定变量的做法更好一些
,因为对列应用函数trim很容易导致无法使用该列上现有的索引
),可能必须考虑到经过一段时间后列长度的变化。如果字段的大小有变化,应用就会受到影响,因为它必须修改字段宽度。
正是因为以上原因,定宽的存储空间可能导致表和相关索引比平常大出许多,还伴随着绑定变量问题
,所以无论什么场合都要避免使用char类型
。
6、where中使用默认值代替null
。
1、反例。
SELECT id,username,userid,tel,age FROM hcuser WHERE age IS NOT NULL
2、正例。
SELECT id,username,userid,tel,age FROM hcuser WHERE age>0
3、理由。
1.并不是说使用了is null或者 is not null就会不走索引了,这个跟mysql版本以及查询成本
都有关;
2.如果mysql优化器发现,走索引比不走索引成本还要高,就会放弃索引,这些条件 !=,<>,is
null,is not null经常被认为让索引失效;
3.其实是因为一般情况下,查询的成本高,优化器自动放弃索引的;
4.如果把null值,换成默认值,很多时候让走索引成为可能,同时,表达意思也相对清晰一点;
7、避免在where子句中使用!=或<>操作符。
1、反例。
SELECT id,username,userid,tel,age FROM hcuser WHERE age !=50
SELECT id,username,userid,tel,age FROM hcuser WHERE age<>50
2、理由。
1.使用!=和<>很可能会让索引失效。
2.应尽量避免在where子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。
3.实现业务优先,实在没办法,就只能使用,并不是不能使用。
8、inner join 、left join、right join,三者中优先使用inner join。
1、三种连接
如果结果相同
,优先
使用inner join
,如果使用left join左边表尽量小
。
- inner join 内连接,只保留两张表中完全匹配的结果集;
- left join会返回左表所有的行,即使在右表中没有匹配的记录;
- right join会返回右表所有的行,即使在左表中没有匹配的记录;
2、为什么呢?
- 如果inner join是等值连接,返回的行数比较少,所以性能相对会好一点;
- 使用了左连接,左边表数据结果尽量小,条件尽量放到左边处理,意味着返回的行数可能比较少;
- 这是mysql优化原则,就是小表驱动大表,小的数据集驱动大的数据集,从而让性能更优;
9、提高group by语句的效率。
1、反例。
先分组,再过滤
。
select job, avg(salary) from employee
group by job
having job ='develop' or job = 'test';
2、正例。
先过滤,后分组
。
select job,avg(salary) from employee
where job ='develop' or job = 'test'
group by job;
3、理由。
1.可以在执行到该语句前,把不需要的记录过滤掉。
10、清空表
时优先使用truncate
。
truncate table
在功能上与不带 where子句的 delete语句相同
:二者均删除表中的全部行
。但truncate table比 delete速度快
,且使用的系统和事务日志资源少
。delete语句每次删除一行,并在事务日志中为所删除的每行记录一项
。truncate table通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放
。truncate table删除表中的所有行,但表结构及其列、约束、索引等保持不变
。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用drop table
语句。- 对于由
foreign key约束引用的表
,不能使用 truncate table
,而应使用不带 where子句的 DELETE 语句
。由于truncate table不记录在日志中
,所以它不能激活触发器
。 truncate table不能用于参与了索引视图的表
。
11、操作delete
或者update
语句,加个limit或者循环分批次删除
。
降低写错SQL
的代价。(容错率)。
清空表数据可不是小事情,一个手抖全没了,删库跑路?
如果加limit,删错也只是丢失部分数据,可以通过binlog日志快速恢复的
。- SQL效率很可能更高。
SQL中加了limit 1,如果第一条就命中目标return
,没有limit的话,还会继续执行扫描表
。 - 避免长事务。
delete
执行时,如果age加了索引,MySQL会将所有相关的行加写锁和间隙锁
,所有执行相关行会被锁住
,如果删除数量大,会直接影响相关业务无法使用
。 - 数据量大的话,容易把CPU打满。
如果你删除数据量很大
时,不加limit限制一下记录数
,容易把cpu打满
,导致越删越慢
。 - 锁表。
一次性删除太多数据
,可能造成锁表
,会有lock wait timeout exceed的错误
,所以建议分批操作
。
12、UNION操作符。
UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如:
select username,tel from user
union
select departmentname from department
这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。推荐方案:采用UNION ALL操作符替代UNION
,因为UNION ALL
操作只是简单的将两个结果合并后就返回。
13、批量插入性能提升。
1、多条提交。
INSERT INTO user (id,username) VALUES(1,'哪吒编程');
INSERT INTO user (id,username) VALUES(2,'妲己');
2、批量提交。
INSERT INTO user (id,username) VALUES(1,'哪吒编程'),(2,'妲己');
3、理由。
1.默认新增SQL有事务控制,导致每条都需要事务开启和事务提交,而批量处理是一次事务开启
和提交,效率提升明显,达到一定量级,效果显著,平时看不出来。
14、表连接不宜太多,索引不宜太多,一般5个以内。
1、表连接不宜太多,一般5个以内。
- 关联的表个数越多,编译的时间和开销也就越大。
- 每次关联内存中都生成一个临时表。
- 应该把连接表拆开成较小的几个执行,可读性更高。
- 如果一定需要连接很多表才能得到数据,那么意味着这是个糟糕的设计了。
- 阿里规范中,建议多表联查三张表以下。
2、索引不宜太多,一般5个以内。
-
索引并不是越多越好,虽其提高了查询的效率,但却会降低插入和更新的效率;
-
索引可以理解为一个就是一张表,其可以存储数据,其数据就要占空间;
-
索引表的数据是排序的,排序也是要花时间的;
-
insert或update时有可能会重建索引,如果数据量巨大,重建将进行记录的重新排序,所以建索引需要慎重考虑,视具体情况来定;
-
一个表的索引数最好不要超过5个,若太多需要考虑一些索引是否有存在的必要;
15、避免在索引列上使用内置函数。
1、反例。
SELECT id,username FROM hcuser WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();
2、正例。
SELECT id,username FROM user WHERE birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);
3、理由。
1.使用索引列上内置函数,索引失效。
16、组合索引。
排序时应按照组合索引中各列的顺序进行排序,即使索引中只有一个列是要排序的,否则排序性能会比较差。
create index IDX_USERNAME_TEL on hcuser(deptid,position,createtime);
select username,tel from hcuser where deptid= 1 and position = 'java测试' order by deptid,position,createtime desc;
实际上只是查询出符合 deptid= 1 and position = 'java测试'
条件的记录并按createtime
降序排序,但写成order by createtime desc性能较差
。
17、复合索引最左特性。
1、创建复合索引。
ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)
2、满足复合索引的最左特性,哪怕只是部分,复合索引生效。
SELECT id,name FROM employee WHERE NAME='真的喜欢你吖'
3、没有出现左边的字段,则不满足最左特性,索引失效。
SELECT id,name FROM employee WHERE salary=5000
4、复合索引全使用,按左侧顺序出现 name,salary,索引生效。
SELECT id,name FROM employee WHERE NAME='真的喜欢你吖' AND salary=5000
5、虽然违背了最左特性,但MySQL执行SQL时会进行优化,底层进行颠倒优化。
SELECT id,name FROM employee WHERE salary=5000 AND NAME='真的喜欢你吖'
6、理由。
1.复合索引也称为联合索引,当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了
(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。
2.联合索引不满足最左原则,索引一般会失效。
18、优化like语句。
1、反例。
select id,name from citys where name like '%大连' (不使用索引)
2、正例。
select id,name from citys where name like '大连%' (使用索引) 。
3、理由。
1.首先尽量避免模糊查询,如果必须使用,不采用全模糊查询,也应尽量采用右模糊查询, 即
like ‘…%’,是会使用索引的;
2.左模糊like ‘%...’无法直接使用索引,但可以利用reverse + function index的形式,
变化成 like ‘…%’;
3.全模糊查询是无法优化的,一定要使用的话建议使用搜索引擎。
19、使用explain分析你SQL执行计划。
1、type。
- system:表仅有一行,基本用不到;
- const:表最多一行数据配合,主键查询时触发较多;
- eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型;
- ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取;
- range:只检索给定范围的行,使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range;
- index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小;
- all:全表扫描;
- 性能排名:system > const > eq_ref > ref > range > index > all。
- 实际sql优化中,最后达到ref或range级别。
2、Extra常用关键字。
- Using index:只从索引树中获取信息,而不需要回表查询;
- Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。需要回表查询。
- Using temporary:mysql常建一个临时表来容纳结果,典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时;
20、其它优化方式。
- 设计表的时候,所有表和字段都添加相应的注释。
- SQL书写格式,关键字大小保持一致,使用缩进。
- 修改或删除重要数据前,要先备份。
- 很多时候用 exists 代替 in 是一个好的选择.
- where后面的字段,留意其数据类型的隐式转换。
- 尽量把所有列定义为NOT NULL。
- 伪删除设计。
- 数据库和表的字符集尽量统一使用UTF8。
- select count(*) from table,这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。
- 避免在where中对字段进行表达式操作。
- 索引不适合建在有大量重复数据的字段上,比如性别,排序字段应创建索引。
- 去重distinct过滤字段要少。
- 尽量避免大事务操作,提高系统并发能力。
- 所有表必须使用Innodb存储引擎。
- 尽量避免使用游标,游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。