你肯定遇到过面试官问你:你对SQL优化了解多少?怎么优化的?
这时候的你是不是能够侃侃而谈呢?其实不管是谁,就算不是详细赘述,就算不是在工作中真正的实践过,或多或少都能说出不少东西。这篇文章初始目的是简单收录我日常工作学习中关于SQL优化的各种知识点,我就来个多多益善。
前面我也有其他关于SQL优化相关的文章,详情看参阅:
《SQL优化-explain的用法(实例解析)》
《SQL优化-单表优化》
《SQL优化-多表优化》
《避免索引失效的一些原则》
下面具体简单介绍我收录的优化点,会持续收集更新:
1、索引
2、复合索引
3、复合索引,不要跨列或无序使用,否则会使组合索引失效
复合索引,尽量使用全索引匹配
4、不要在索引上进行任何操作(计算、函数、类型转换),否则索引失效
5、复合索引不能使用不等于(!=或<>)或is null(is not null),否则自身以及右侧索引全部失效。
6、索引不会包含有 NULL 值的列
只要列中包含有 NULL 值都将不会被包含在索引中,复合索引中只要有一列含有 NULL 值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为 NULL。
7、使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个 CHAR(255)的 列,如果在前 10 个或 20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和 I/O 操作。
8、索引覆盖
索引覆盖(using index)—100%使用索引的情况,尽量使用索引覆盖。
SQL优化,是一种概率层面的优化,并不是说建了索引就一定使用了索引。至于是否实际使用了我们的优化,需要通过explain进行推测。
9、索引下推
10、like尽量以“常量”开头,不要以‘%’开头,否则索引失效。
11、尽量不要使用类型转换(显示、隐式),否则索引失效。
数字类型的字段我们使用的是字符,如我们用‘123’代替123,这样就会导致索引失效。
12、尽量不要使用or,否则索引失效。
13、exist和in的合理使用。
14、order by优化
有两种算法:双路排序、单路排序(根据IO的次数)
MySQL4.1之前,默认使用双路排序;
双路:扫描两次磁盘(1.从磁盘读取排序字段,对排序字段进行排序(在buffer(缓存区)中进行排序);2.扫描其他字段)。IO较消耗性能
MySQL4.1之后,默认使用单路排序;
只读取一次(全部字段),在buffer中进行排序。但这单路排序会有一定的隐患(不一定真的是“单路 1次IO”,有可能多次IO—原因:如果数据量特别大,则无法将所有字段的数据一次性读取完毕,因此会进行“分片读取、多次读取”)。
注意:单路排序比双路排序会占用更多的buffer。
单路排序在使用时,如果数据量大,可以考虑调大buffer的容量大小:set max_length_for_sort_data = 1024 单位byte
如果max_length_for_sort_data值太低,则MySQL会自动从单路->双路(太低:需要排序的列的总大小超过了max_length_for_sort_data定义的字节数)
提高order by查询的策略:
a.选择使用单路、多路;调整buffer的容量大小;
b.避免select * …(->程序还要去计算表示哪些字段)
c.复合索引不要跨列使用,避免using firesort
d.保证全部的排序字段 排序的一致性(都是升序或降序)
15、SQL慢查询排查