mySQL 优化思路

优化思路:

一.SQL语句优化

 

1.尽可能不要使用 select * from table , 不要返回一些根本用不到的列值 . 应该指定相应的列名

2.select column  from student where name like '%李%'  ,在like语句中 尽量避免'%'开头 否则会导致全盘扫描

3.应尽量避免在 where 子句中使用!=或<>操作符， 这时会进行全盘扫描 .

　MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。

4.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
   select id from t where num is null

   解决方法,可以为此列设置默认值 , 例如 0  , 则上述select  可以替换为 :  select id from t where num = 0

5.in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值 可以使用 between,非连续的值可以使用exists 代替 in 是一个好的选择

6.能使用where子句就不要使用having子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

7.使用 union 关键字 替换 where 子句中的各个条件间的or 

8.避免过多的表联结, 联结时非常消耗性能的

9.对经常查询的列,  经常在where子句中作为条件的列, 或经常范围查询的列,以及经常使用 order by 排序的列,可以为这些列建立索引,可以大幅提高性能

10.对于外键列,也可以单独建立索引,可以提高 表联结时的效率

11.应尽量避免在where子句中对字段进行函数操作

12.insert 语句的提升:

　　a.生产中, 可能会出现并发访问数据库,进行增删改查的情况, 一般情况查的优先级更高一些. 同时insert 时,需要建立索引 耗时较久,同时还可能造成后续select语句性能的降低

   　　此时可以通过降低 insert 语句的优先级, 来提高性能 ;语法格式: insert low_priority into ... . 同样也适用于delete update语句

　　b.多行插入执行1次  性能要高于一行插入执行多次

13.使用join时, 做到小结果集 驱动 大结果集 ,减少 nested loop 的次数

二.索引

 

1.哪些列应该建立索引?

 

a.经常在select中出现的列, 即经常用来搜索的列

b.经常在where子句中出现的列

c.经常需要排序的列 

d.主键列(创建表时,已经为主键列创建了索引,所以不需要手动创建)

e.外键列(用作与其他表建立联结条件的列) ,这些列可以提高联结时的性能

f.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的

 

2.哪些列不合适建立索引?

 

a.查询中很少使用的列, 这些列设置索引,并不能显著的提升查询的性能, 并且增加了维护的成本,降低了insert 等语句的性能

b.数据值比较少的列,或者是唯一性太差的字段 ,例如:性别  状态  类别

c.大文本类型的字段不应该创建索引 ,例如:数据类型为text, image和bit的列

d.如果一个表的增删改操作 远远大于 select 操作, 那么不应该额外建立索引

 

3.索引带来的优缺点?

 

优点:合适的索引 可以很好地提升select 语句执行的效率

缺点:索引过多,会增加 insert,update,delete 语句的维护的开销, 也会降低这部分语句的执行效率

 

 

三.数据库结构优化

 

1.范式优化

可以消除冗余,节约空间

 

2.反范式优化

适当的使用冗余,有时可以减少表的联结,从而提升性能

 

3.合理的拆分表

 

四.硬件

提升相关硬件

 

五.缓存

 

合理使用查询缓存,减少数据库的访问压力

 

六.数据库主从,读写分离

主库主要负责写操作

从库负责读操作

从库 同步 主库中的数据