Mysql数据库的优化技术
对mysql优化是一个综合性的技术,主要包括
一. 表的设计合理化(符合 3NF)3范式
二. 添加适当索引(index)[常见索引:普通索引、主键索引、唯一索引、全文索引、unique]
三. 查询语句的防止全表扫描技术。
四. 分表技术(水平分割、垂直分割)
五. 读写(写:update/delete/add)分离
六. 存储过程[模块化编程,可以提高速度]
七. 对配置优化[配置最大并发数,调整缓存大小](mysql默认100)
八. 定时的去清除一些不需要的数据,定时进行碎片化整理(MyISAM)
九. 服务器硬件升级
######重点讲述:二,三
二:
对于索引来说,并不是越多越好,索引的增加会降低 插入、删除、更新 的语句的效率,增加一条记录
时,数据库除了会记录该条信息。还会针对该条记录的索引字段,创建索引,加重资源的开销。
所以我们创建索引时应该注意以下几点:
1.表的主键、外键必须有索引;
2.经常与其他表进行连接的表,在连接字段上应该建立索引;
3.经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
4.经常需要 order by 的语句[考虑]建立索引
5.索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
6.索引应该建在选择性高的字段上;
7.复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
a.正确选择复合索引中的主列字段,一般是选择性较好的字段;
b.复合索引的几个字段是否经常同时以AND方式出现在Where子句中?
单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
c.如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
d.如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
8.频繁进行数据操作的表,不要建立太多的索引;
三:
防止全表扫是指查询某张表时,该表建立了索引,但是由于select语句语法的问题导致并没有使用索引,
最后进行了全表扫描,这样降低了查询的效率。所以针对全表扫描我们应该注意以下几点:
1.应尽量避免在‘where’子句中对字段进行‘null’值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null;
2.应尽量避免在‘where’子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
select * from emp where id <> 9527;
3.应尽量避免在‘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;
4.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如
select id from t where num/2=100;
可以这样查询:
select id from t where num=100*2;
5.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3)='abc'; --name以abc开头的id
可以这样查询:
select id from t where name like 'abc%'
6.很多时候用 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)
7.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会
增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只
需要比较一次就够了。
8.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。