数据库SQL调优

一、合理使用索引，避免索引失效引起全表查询

※※※ 可以通过explain来分析SQL执行计划，看是否用到了索引

例：explain select *  from table_name

explain得到的字段key（使用到的索引）,rows（MYSQL估计为了查找目标行而需要读取的行数：）,possible_keys（查询可能会使用的索引）等。主要关注type。

type:以怎样的方式查找表中的行的方式（性能逐渐变好）

• All:   全表扫描
• Index:  根据索引的次序进行全表扫描，若在extra出现using index表示使用覆盖索引，而非全表扫描
• Range:  根据索引实现的范围扫描
• Ref:  根据索引返回表中匹配某单个值的所有行
• Eq_ref:  仅返回一个行，但需要和某个参考值作比较
• Const,system:  根据具有唯一性的索引（比如主键）查找时，返回的是一行
• NULL：类似于覆盖查询 

id:当前查询中，每个Select语句的编号
复杂类型的查询三种：
简单子查询：
用于from中的子查询
联合查询，union
注意：union查询的分析结果中会出现一个额外的匿名临时表

select_type:

• 简单查询为simple
• 复杂查询：
  • Subquery:简单子查询
  • Derived:用于from中的子查询
  • Union:用于union第一个之后的select语句

possible_keys:  查询可能会使用的索引
key:  使用到的索引
key_len:  索引中使用的字节数，比如索引有70个字节数，只是用了20个
ref:  在利用key所表示的索引完成查询时，所用的列或某常量值
rows:  MYSQL估计为了查找目标行而需要读取的行数：
Extra:额外信息

• Using index:会使用覆盖索引，以避免访问表
• Using where:服务器将在存储引擎检索后，再进行一次过滤
• Using temporary:对结果排序时会使用临时表
• Using filesort:对结果使用一个外部索引排序

1、在经常需要进行检索的字段上创建索引，一个表的索引数最好不要超过6个，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率。

2、避免在索引上使用计算，在索引列上操作or, not in , !=,<> 等

效率低：

 select * from user where salary*22>11000(salary是索引列) 

效率高：

 select * from user where salary>11000/22(salary是索引列) 

3、模糊查询，比如以%开头的like查询 会导致索引失效

4、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

select id from t where num is null 

可以在num上设置默认值0，确保表中num列没有null值，

然后这样查询：

 select id from t where num=0 

5、如果查询条件有or, 并且or的前后条件中有一个列没有索引，则涉及的索引都不会用到

6、对于连续的数值，能用 between 就不要用 in 了：

 select id from t where num between 1 and 3

7、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

8、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用

二、使用varchar/nvarchar 代替 char/nchar ，如果文本字段非空且始终是固定长度的（例如，US 邮编，其始终具有“XXXXX-XXXX”形式的规范表示），那么推荐使用char。

三、分页查询

四、不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段

五、 如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志

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

七、避免隐式类型转换造成的全表扫描

person 表的字符串类型字段FLevel 为人员的级别，在FAge 字段上建有索引。
我们执行下面的SQL语句用于检索所有级别等于10的员工：

• SELECT FId,FLevel,FName FROM person WHERE FLevel=10

在这个SQL语句中，将字符串类型字段FLevel与数值10进行比较，由于在大部分数据库中隐式转换类型中数值类型的优先级高于字符串类型，因此DBMS会对FAge字段进行隐式类型转换，相当于执行了下面的SQL语句：

• SELECT FId,FAge,FName FROM person WHERE TO_INT(FAge)=10

由于在索引字段上进行了计算，所以造成了索引失效而使用全表扫描。因此应将
SQL语句做如下修改：

• SELECT FId,FAge,FName FROM person WHERE FLevel='10';