sql优化总结

 

由于数据量比较庞大，大部分表数据上百万，甚至有的表数据上千万。所以
在系统中做sql优化比较多，特此写一篇博客总结一下关于sql优化方面的经验。

--导致查询缓慢的原因
1、数据量过大

2、表设计不合理

3、sql语句写得不好

4、没有合理使用索引

-- 针对SQL语句的优化

1、查询语句中不要使用 *

2、尽量减少子查询，使用关联查询（left join,right join,inner  join）替代

3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代

4、or 的查询尽量用 union或者union all 代替
(在确认没有重复数据或者不用剔除重复数据时，union all会更好)

5、合理的增加冗余的字段（减少表的联接查询）

6、增加中间表进行优化（这个主要是在统计报表的场景，
后台开定时任务将数据先统计好，尽量不要在查询的时候去统计）

7、建表的时候能使用数字类型的字段就使用数字类型（type,status...），数字类型的字段作为条件查询比字符串的快

8、那些可以过滤掉最大数量记录的条件必须写在WHERE子句的最末尾

-- 索引优化

如果针对sql语句已经没啥可以优化的，那我们就要考虑加索引了。

--说索引前先说说explain查看sql的执行计划

1 id
 SELECT识别符。这是select查询序列号。这个不重要

2 select_type
表示查询中每个select子句的类型（简单OR复杂）
有以下几种值：
 1 simple
 查询中不包含查询或者UNION（联合查询）
 2 PRIMARY
 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
 3 UNION
 表示连接查询的第2个或后面的查询语句。
 4 DEPENDENT UNION
 UNION 中的第二个或者后面的select语句，取决于外面的查询
 5 UNION RESULT
 连接查询的结果
 6 SUBQUERY
 子查询中的第一个select语句
 7 DEPENDENT SUBQUERY
 子查询中的第一个select语句，取决于外面的查询
 8 DERIVED
 select（from子句的子查询）

3 table 表示查询的表

4 type
表示表的连接类型
以下的连接类型的顺序是从最佳类型到最差类型
 1 syste
 表仅有一行，这是const类型的特例，平时不会出现
 2 const
 数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于PRIMARY KEY
 或者UNIQUE查询，可理解为是最优化的。
 3 eq_ref
 mysql手册是这样说的： 对于每个来自前面的表的行组合，从该表中读取一行。
 这可能是最好的联接类型，除了const类型。他用在一个索引的所有部分被联接使用并且并且索引是UNIQUE或PRIMARY KEY    eq_ref可以用于使用=比较带索引的列。

 4 ref
 查询条件索引既不是UNIQUE 也不是PRIMARY KEY 的情况，ref可用于=或<或>操作符的带索引的列。

 5 ref_or_null
 该联接类型如同ref，但是添加了Mysql可以专门搜索包含null值的行，在解决子查询中经常使用该联接类型的优化。

 以上这五种情况都是很理想的索引使用情况。

 6 index
 该连接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。

 7 ALL
 对于每个来自先前的表的行组合，进行完整的表扫描。

5 possible_key
 指出Mysql能使用哪个索引在该表中找到行。
 如果该列为NULL 说明没有使用索引，可以对该列创建索引来提高性能
6 Key
 显示mysql实际决定使用的索引，如果没有选择索引，键是null
 可以强制使用索引或者忽略索引：
 强制使用索引：USE index（列名）
 忽略使用索引：IGNORE INDEX(列名)
7 key_len
 显示mysql决定使用的键长度。如果键是NULL则长度为NULL。
 注意：key_len 是确定了mysql将实际使用的索引长度

8 ref
 显示使用哪个列或常数与key一起从表中选择行
9 rows
 显示mysql认为它执行查询时必须检查的行数
10 extra
 关于MYSQL如何解析查询的额外信息。Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
 说明：extra列返回的描述的意义
 Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。
 Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
 Range checked for each Record（index map:#） ：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。
 Using filesort ：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
 Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
 Using temporary ：看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
 Where used ：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

 

使用explain查看sql执行计划后，我们主要先看下type属性，表示连接的类型，如果是ALL这种那就需要优化了，

再看下possible_key属性，表示可以使用的索引，如果没有则为null，key属性表示mysql实际决定使用的索引，如果没有选择索引，键是null，

rows 表示mysql认为它执行查询时必须检查的行数，行数越多效率越低。

 

--索引类型

 主键索引，唯一索引，组合索引，普通索引

--什么是索引

 数据库索引是数据库管理系统中的一个排序的数据结构，以协助快速查询，更新数据库表中数据，索引的实现通常使用B树(B-tree)以及其变种B+tree（一些高效率的算法）

--使用索引时有些不生效的情况

 1、使用like关键字模糊查询时，% 放在前面索引不起作用，只有“%”不在第一个位置，索引才会生效（like '%文'--索引不起作用）

 2、使用联合索引时，只有查询条件中使用了这些字段中的第一个字段，索引才会生效

 3、使用OR关键字的查询，查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则索引不生效。
 4、尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

 5、对查询进行优化，应尽量避免全表扫描，首先应考虑在where以及order by涉及的列上建立索引。

 6、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

  　　select id from t where num/2=100
  　　应改为:
  　　select id from t where num=100*2
 7、尽量避免在where子句中对字段进行函数操作,将导致引擎放弃使用索引而进行全表扫描。

 8、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

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

 10、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，

 　　因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，
 　　若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

 11、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

 　　这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

 12、mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。

　　 因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。

 13、order by 索引 ，不起作用的问题（除了主键索引之外）：

  　　1、 如果select 只查询索引字段，order by 索引字段会用到索引，要不然就是全表排列；
  
 　　 2、如果有where 条件，比如where vtype=1 order by vtype asc . 这样order by 也会用到索引！