不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。
一、 索引的建立和使用
- 定义主键的数据列一定要建立索引
- 定义有外键的数据列一定要建立索引
- 对于经常查询的数据列最好建立索引
- 对于需要在指定范围内的快速或频繁查询的数据列
- 经常用在WHERE子句中的数据列
- 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用
- 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
- 对于定义为text、image和bit的数据类型的列不要建立索引
- 对于经常存取的列避免建立索引
- 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作
- 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用
二、SQL语句的执行原理
- 服务器在接收到查询请求后,并不会马上去数据库查询,而是在数据库中的计划缓存中找是否有相对应的执行计划,如果存在,就直接调用已经编译好的执行计划,节省了执行计划的编译时间
- 语法效验、语义效验、权限验证
- 针对SQL进行优化,选择不同的查询算法以最高效的形式返回
- 语句执行，执行顺序：
1） FROM 子句返回初始结果集
2） WHERE 子句排除不满足搜索条件的行
3） GROUP BY 子句将选定的行收集到 GROUP BY 子句中各个唯一值的组中
4） 选择列表中指定的聚合函数可以计算各组的汇总值
5） 此外，HAVING 子句排除不满足搜索条件的行
6） 计算所有的表达式
7） 使用order by对结果集进行排序 - where条件执行原理及效率
首先要了解Where 条件执行方向是从右向左的（如多条件判断下，会从最后一个条件来判断过滤数据的，依次向前推进判断）
1） 注意SQL运算符（非、与、或）优先级别，级别越高放最后
2） 在同运算符内字段值数据范围越大的查询字段放最后
三、优化SQL语句的若干方法
- WHERE 语句中，小表字段写左边
- 在WHERE中尽量不要使用OR
- 操作符号： IN & NOT IN操作符
NOT IN操作是强列推荐不使用的，NOT IN会多次扫描表
推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替
而Exists比IN更快，最慢的是NOT操作
使用in时，在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，这样可以减少判断的次数
另外，多表连接查询时，用IN，sql会先尝试转换成多表连接，转换不成功则先执行IN里面的查询，再查询外层表记录。 - 注意union和union all的区别。union比union all多做了一步distinct操作。能用union all的情况下尽量不用union
- 查询时尽量不要返回不需要的行、列。另外在多表连接查询时，尽量改成连接查询，少用子查询
- Between在某些时候比IN速度更快,Between能够更快地找到范围
- 从右到左的顺序处理FROM子句中的表名，选择数据量少的表作为基础表
- 没有必要时不要用DISTINCT和ORDER BY，它们增加了额外的开销
- 计算记录条数
和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(CONTRACT_NO) - 减少对表的查询
- 使用表的别名
