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

查询顺序

越大优先级越高

2 select_type

查询类型：

简单查询、主查询、子查询、联合查询、虚拟查询

3 table

查询哪一张表

4 type

查询所使用的索引类型

system>const>eq_ref>ref>range>index>ALL

all没有索引

index整个索引树

range部分索引树

ref

多表关联的情况下，关联字段为索引

或者在单表中复合索引中的一个字段被使用

复合索引(最佳做前缀：btree)

Locals,namess

Locals:ref

Names:index

Names,locals

Locals:index

Names:ref

Range

只查找部分索引(范围索引)

Ref：

当查询类型是ref使用到索引时

显示被使用的索引的具体字段(有可能是一个常量)

possible_keys/key

possible_keys：查询到的表中的索引(可能被用到也可能用不到)

key：实际被使用到的索引列

key_len

计算公式：

编码格式所占字节数*长度+是否为空(1)+是否可变(2)

Extra

首先避免文件内部排序(查询时排序ordery要充分利用索引，不要跳过索引列排序)

避免在大量数据时，进行二次排序使用临时空间

说明：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 也会用到索引！

全职匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写*；

不等空值还有OR，索引影响要注意；

VAR引号不可丢， SQL优化有诀窍。