MySQL优化
1. SQL语句优化
-
对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by涉及的列上建立索引 .
-
避免在 where 子句中使用 !=或<> 操作符,否则将引擎放弃使用索引而进行全表扫描 .
-
避免在 where 子句中对字段进行 null 值 判断,否则将导致引擎放弃使用索引而进行全表扫描 .
select id from t where num is null select id from t where num=0
-
避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描 ,使用该关键字必须前后俩个条件中的列都是索引才会使用索引.使用 in 或者 union.
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
-
避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描
select id from t where num/2=100 应改为: select id from t where num=100*2
-
当只要一行数据时使用 limit 1
-
查询条件中尽量不要使用函数.使用函数的话查询将不会走缓存.例如 NOW() 和 RAND()
-
只取出自己需要的 Columns;不要select *
-
关联查询时,永远用小结果集驱动大的结果集
-
不要写一些没有意义的查询,如需要生成一个空表结构
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
create table #t(...)
-
用 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个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
-
尽量不使用子查询,可以使用连接查询(JOIN)代替子查询,连接查询时不需要建立临时表,其速度比子查询快。另外,join关联的条件,最好也是索引字段 .
-
尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型.
-
尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些.
-
在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert.
-
避免大事务操作,提高系统并发能力.
-
避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理.
2. 数据库结构优化
-
选择合适的数据类型
数字:
空间越小越好
能用整数就不要用小数
字符串:
能用数字就用数字。(用字典方式)
长度可不固定就不固定
日期类型:
用数字(毫秒值)
Timestamp:存的是毫秒值 -
不要使用TEXT、BLOB类型
-
把字段定义为NOT NULL并且提默认值
a)null的列使索引/索引统计/值比较都更加复杂,对MySQL来说更难优化
b)null 这种类型MySQL内部需要进行特殊处理,增加数据库处理记录的复杂性;同等条件下,表中有较多空字段的时候,数据库的处理性能会降低很多
c)null值需要更多的存储空间,无论是表还是索引中每行中的null的列都需要额外的空间来标识
d)对null 的处理时候,只能采用is null或is not null,而不能采用=、in、<、<>、!=、not in这些操作符号。如:where name!=’zhangsan’,如果存在name为null值的记录,查询结果就不会包含name为null值的记录.
-
将字段很多的表分解成多个表
垂直分表:
字段过多,按列分表
水平分表(慎用,会导致逻辑变复杂):
表内数据过多,超过1000万,需要水平分表:按行分。mycat自动分库分表
- 增加中间表
- 合理适当增加冗余字段
- 尽量不要设计表关联字段(外键)
.