查询SQL尽量不要使用select *,而是具体字段
反例
select * from test
正例
select id,name,age,remark from test
理由
节省资源、减少网络开销。
可能用到覆盖索引,减少回表,提高查询效率。
避免在where子句中使用 or 来连接条件
反例
select * from test where id=1 or age>20
正例
使用union all
select * from test where id=1 union allselect * from test where age>20
理由
使用or可能会使索引失效,从而全表扫描;
虽然mysql是有优化器的,出于效率与成本考虑,遇到or条件,索引还是可能失效的;
尽量使用数值替代字符串类型
正例
- 主键(id):primary key优先使用数值类型int,tinyint
- 性别(sex):0代表女,1代表男;数据库没有布尔类型,mysql推荐使用tinyint
理由
因为引擎在处理查询和连接时会逐个比较字符串中每一个字符;
而对于数字型而言只需要比较一次就够了;
字符会降低查询和连接的性能,并会增加存储开销;
使用varchar代替char
- varchar变长字段按数据内容实际长度存储,存储空间小,可以节省存储空间;
- char按声明大小存储,不足补空格;
- 其次对于查询来说,在一个相对较小的字段内搜索,效率更高;
理由
1、char的长度是固定的,而varchar2的长度是可以变化的。
例如:存储字符串“101”,对于char(10),表示你存储的字符将占10个字节(包括7个空字符),在数据库中它是以空格占位的,而同样的varchar2(10)则只占用3个字节的长度,10只是最大值,当你存储的字符小于10时,按实际长度存储。
2、char的效率比varchar2的效率稍高。
varchar2比char节省空间,在效率上比char会稍微差一点,既想获取效率,就必须牺牲一点空间,这就是我们在数据库设计上常说的“以空间换效率”。
where中使用默认值代替null
反例
select * from test where age is not null
正例
select * from test where age > 0
理由
where语句中索引列使用了负向查询,可能会导致索引失效
负向查询包括:NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等
负向查询应尽量避免出现。否则引擎将放弃使用索引而进行全表扫描
避免在where子句中使用!=或<>操作符
inner join 、left join、right join,优先使用inner join
三种连接如果结果相同,优先使用inner join,如果使用left join左边表尽量小
- inner join 内连接,只保留两张表中完全匹配的结果集;
- left join会返回左表所有的行,即使在右表中没有匹配的记录;
- right join会返回右表所有的行,即使在左表中没有匹配的记录;
理由
- 如果inner join是等值连接,返回的行数比较少,所以性能相对会好一点;
- 使用了左连接,左边表数据结果尽量小,条件尽量放到左边处理,意味着返回的行数可能比较少;
- 这是mysql优化原则,就是小表驱动大表,小的数据集驱动大的数据集,从而让性能更优;
清空表时优先使用truncate
truncate table在功能上与不带where子句的 delete语句相同:二者均删除表中的全部行。但 truncate table比 delete速度快,且使用的系统和事务日志资源少。
delete语句每次删除一行,并在事务日志中为所删除的每行记录一项。truncate table通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
truncate table删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 drop table语句。
避免在索引列上使用内置函数
使用索引列上内置函数,索引失效
like通配符可能会导致索引失效
MySQL优化器的最终选择,不走索引
即使完全符合索引生效的场景,考虑到实际数据量等原因,最终是否使用索引还要看MySQL优化器的判断。当然你也可以在sql语句中写明强制走某个索引。
使用explain分析你SQL执行计划
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级别。
Extra常用关键字
- Using index:只从索引树中获取信息,而不需要回表查询;
- Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。需要回表查询。
- Using temporary:mysql常建一个临时表来容纳结果,典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时;
总结其它优化方式
1、 设计表的时候,所有表和字段都添加相应的注释。
2、 SQL书写格式,关键字大小保持一致,使用缩进。
3、 修改或删除重要数据前,要先备份。
4、 很多时候用 exists 代替 in 是一个好的选择
5、 where后面的字段,留意其数据类型的隐式转换。(字符串和整数 "100" 100)
6、 尽量把所有列定义为NOT NULL
NOT NULL列更节省空间,NULL列需要一个额外字节作为判断是否为 NULL的标志位。NULL列需要注意空指针问题,NULL列在计算和比较的时候,需要注意空指针问题。
7、 伪删除设计
8、 数据库和表的字符集尽量统一使用UTF8
(1)可以避免乱码问题;
(2)可以避免,不同字符集比较转换,导致的索引失效问题;
9、 select count(*) from table;
这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。
10、 避免在where中对字段进行表达式操作
(1)SQL解析时,如果字段相关的是表达式就进行全表扫描 ;
(2)字段干净无表达式,索引生效;
11、 关于临时表
(1)避免频繁创建和删除临时表,以减少系统表资源的消耗;
(2)在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log;
(3)如果数据量不大,为了缓和系统表的资源,应先create table,然后insert;
(4)如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除。先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定;
12、 索引不适合建在有大量重复数据的字段上,比如性别,排序字段应创建索引
13、 去重distinct过滤字段要少
14、 尽量避免大事务操作,提高系统并发能力
15、 所有表必须使用Innodb存储引擎
版权声明:内容来源于互联网和用户投稿 如有侵权请联系删除