sql优化常见策略

本贴最后更新于 1930 天前,其中的信息可能已经时移世易
常见的优化策略
从最核心的开始说起
###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 `排序结果和索引完全不同时,此时的查询不会被复合索引优化。

备注:部分来自《mysql 技术内幕》

  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    309 引用 • 602 回帖
  • 索引
    23 引用 • 28 回帖
  • select
    16 引用 • 14 回帖 • 1 关注

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...