@Toby-wei 2016-08-27T03:03:49.000000Z 字数 2434 阅读 1293

sql优化常见策略

数据库

常见的优化策略
从最核心的开始说起

1. 选择正确的存储引擎

这个是最核心的了，选错存储引擎那其他的都是白忙活了。那最常用的就是InnoDB了，那如果事务性要求不高的话，可以选择myisam了。

2. 不能用存在null值的列做索引

不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

3. where后面的索引不一定100%有效

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

尽量避免null类型的列
使得索引、索引统计、值比较都比较复杂、使用更多的存储空间、null的列为索引时，每个索引记录需要额外一个字节、

datetime timestamp
timestamp只使用datetime一半的存储空间。并且会根据时区变化。

整数类型
tinyint、smallint、mediumint、int、bigint分别使用8/16/24、32/64位存储空间
unsigned属性表示不允许负值

字符串类型
varchar char的区别
varchar存储可变长的字符串、需要用额外1、2一个字节记录字符串长度信息、适合最大长度比平均长度大很多。。列的更新很少，碎片不是问题
char是定长的，适合经常变更的数据、因为不容易产生碎片。也适合非常短的列、比如char(1)存储y和n的值，char只需要一个字节，而varchar需要两个字节，额外一个字节存储长度信息

4. mysql手册里推荐的优化策略

4.1 MySQL怎样优化WHERE子句

去除不必要的括号：
((a AND b) AND c OR (((a AND b) AND (c AND d))))应该写为 -> (a AND b AND c) OR (a AND b AND c AND d)

常量重叠：
(a<b AND b=c) AND a=5
改为-> b>5 AND b=c AND a=5

去除常量条件(由于常量重叠需要)：
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
·改为： -> B=5 OR B=6

尽量避免在 where 子句中使用!=或<>操作符,容易走全表扫描

首先应考虑在 where 及 order by 涉及的列上建立索引。

尽量避免在 where 子句中对字段进行 null 值判断,否则会走全表扫描,例如：
select id from t where num is null会走全表扫描。可以设成默认值0

避免在 where 子句中使用 or 来连接条件.否则会走全表扫描。解决办法是多个条件用union all来连接。

like查询%不要前置，这样会走全表扫描。例如：
select id from t where name like ‘%cc%’,酱紫不好。

in 和 not in 也要慎用，否则会导致全表扫描.尽量用between and替换

如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2

很多时候用 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)

只有低选择性的数据行才有索引，一般根据经验来说，查出的数据量大于表数据量21%以上的话不走索引。

根据经验，一个表的索引数最好不要超过6个

尽量使用数字型字段.若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

避免使用select *

4.2 复合索引优化

索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引。

复合索引有个重要的原则就是最左前缀原则。
复合索引起作用的原则是
1. 必须包含最左边一个索引
2. 索引顺序不能改变
如果 (col1，col2，col3)有一个索引，则起作用的索引是col1、(col1, col2)、(col1, col3);
这就是最左前缀代表的意义。

4.3 复合索引对排序的优化

复合索引只对和索引中排序相同或相反的order by 语句优化。

在创建复合索引时，每一列都定义了升序或者是降序。如定义一个复合索引：
CREATE INDEX idx_example ON table1 (col1 ASC, col2 DESC, col3 ASC)
其中有三列分别是：col1 升序，col2 降序， col3 升序。现在如果我们执行两个查询

1：Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC和索引顺序相同

2：Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC和索引顺序相反

查询1，2 都可以别复合索引优化。

如果查询为：

Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC排序结果和索引完全不同时，此时的查询不会被复合索引优化。