关于SQL优化

在开始之前我们首先需要明白我们查询效率低下的原因可能有哪些？

1.SQL语句质量太差

2.数据库表设计不合理，导致某些查询需要关联很多表，这里就可以看出，数据库表适当的增加一些冗余字段会减少使用关联查询的几率。

3.没有合理的利用索引

4.如果上面三种情况都避免了，那么数据量确实非常大当然查询也会很慢，这个时候我们往往不能单纯从数据库层面解决问题了，还需要对我们应用程序做出相应的调整。

首先针对第一点有如下建议：

1. 尽量不要使用"SELECT * ..." 的方式获取数据

数据库会将“*”解析成具体的列名，这个会消耗一定的时间

2.减少关联查询（LEFT JOIN 、INNER JOIN、RIGHT JOIN）的使用

我们在设计数据库时，数据表有时候需要包含一些看似无用的字段，这些字段的作用就是避免后面写SQL的时候使用关联查询，如果必须要用到关联查询时，数据量大时，我们可以考虑能否将关联查询拆分成不同的查询语句,这种方式很多时候也能提高查询的效率。

3.关于IN、NOT IN、EXISTS、NOT EXISTS的使用

设A是主表，B是子查询表

如果A表数据量大于B表时，IN的效率高于EXISTS

如果A表数据量小于B表时，IN的效率低于EXISTS

不论A表的数据量大还是B表的数据量大，此时NOT EXISTS的效率都要高于NOT IN

针对第二点有如下建议：

1.合理的增加表中冗余的字段，减少表的关联查询

2.考虑设计中间表，对于一些统计数据，因此对于一些对实时性要求不是很高的统计，我们可以设计一张中间表，来存储统计数据，这样可以避免每天统计查询都需要直接查询原始数据。

3.为字段选择合适的数据类型，在满足业务逻辑的条件下优先选用占用空间小的数据类型。如对于一些表示状态、类型（量少）的字段用可以用tinyint比char或varchar查询的速度快

针对索引有如下建议：

1.索引并不是越多越好的，使用索引：1.需要额外的磁盘空间保存索引。2.对插入更新删除等操作，由于更新索引会增加额外的开销。

2.建索引通常要遵循如下条件：

1. 多数查询经常使用的列
2. 很少进行修改操作的列
3. 索引需要建立在数据差异化大的列上

 在使用索引时也需要注意，有一些条件会造成索引失效：

1. 使用like关键字模糊查询时，% 放在前面索引不起作用，只有“%”不在第一个位置，索引才会生效（like '%文'--索引不起作用）。
   如：SELECT name FROM user WHERE account LIKE '%123'此时索引不起作用
2. 使用联合索引时，只有查询条件中使用了这些字段中的第一个字段，索引才会生效
   如：account和phone建立了联合索引，第一个字段是account
          SELECT name FROM user WHERE account='admin' AND phone='123'   索引有效
          SELECT name FROM user WHERE phone='123' AND account='admin' 索引有效
          SELECT name FROM user WHERE account='admin' 索引有效
          SELECT name FROM user WHERE phone='123'       索引无效              
3. 使用OR关键字的查询，查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都具有索引，查询语句中两个列的索引都有效时，整个查询用到的索引才会生效，否则索引不生效。
   如：account、phone都建立了索引，name没有建立索引
          SELECT name FROM user WHERE account='admin' OR phone='123' 索引有效
          SELECT name FROM user WHERE account LIKE '%admin' OR phone='123' 索引无效
          SELECT name FROM user WHERE account='admin' OR name='张三'  索引无效
4. 尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
   如：SELECT name FROM user WHERE account <> 'admin'  索引无效
5. 尽量避免在where子句中对索引字段进行函数操作,将导致引擎放弃使用索引而进行全表扫描。
   如：SELECT name FROM user WHERE LOWER(account_name) LIKE 'zhang3%'不能命中索引导致全表扫描
   这个sql语句其实有一点问题，char和varchar类型在查询时是忽略大小写的
6. WHERE子句中尽量避免使用NULL判断，否则数据库会放弃使用索引，而进行全表扫描。
   如：SELECT name FROM user WHERE phone IS NULL不会使用索引，可以用特殊的数字代表特殊的状态，-1代替null
         SELECT name FROM user WHERE phone = -1会使用phone的索引
7. 应尽量避免在 where 子句中对索引字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
   如：select id from t where num/2=100  应改为: select id from t where num=100*2 
8. 对查询进行优化，应尽量避免全表扫描，首先应考虑在where以及order by涉及的列上建立索引。
9. 并不是所有的索引对查询都有效，sql是根据表中的数据来进行查询优化的，当索引列有大量数据重复时，sql查询不会去利用索引。
   如一表中有字段sex：‘男’,‘女’几乎各占一半，那么即使在sex上建立了索引也对查询效率起不了作用。
10. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
11. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
12. order by 索引 ，不起作用的问题（除了主键索引之外）：
    1、如果select 只查询索引字段，order by 索引字段会用到索引，要不然就是全表排列；
    2、如果有where 条件，比如where type=1 order by type asc . where条件中已经使用了type字段的索引，这样order by 不会用到type的索引！