MySQL常用关联查询即优化

内链接 inner join

A表和B表的共有部分
select * from A inner join B ON A.id = B.id

左连接 left join

A表的全部，B表和A表的交集  B表不匹配的全部用null 来补齐
select * from A left join b ON A.id = B.id

全链接 full outer join

2个表全部的交际
select * from A full outer join B on A.key = B.key

union 操作符

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。
多个 SELECT 语句会删除重复的数据。有去重的功能。

索引的优势

索引是帮助MySQL高效获取数据的数据结构
索引是排好序的快速查找的数据结构

索引的劣势

1，实际上索引也是一张表，该表保存了主键和索引字段，他也是占用空间的。
2，虽然索引大大提高了查询速度，同时却会降低更新表的速度，因为更新表时MySQL不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段。

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引。  

唯一索引

索引列的值必须时唯一的，但允许有空值。

复合索引

即一个索引包含多个列

MySQL索引结构

####Btree B数

hash索引

full-text全文索引

R-Tree

哪些情况下需要创建索引

1,主键自动建立唯一索引
2,频繁作为查询条件的字段应该建立索引
3,查询中与其他表关联的字段，外键关系建立索引。
4,频繁更新的字段不适合建索引
5,where条件里面用不到的字段不需要建立索引
6,查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度。
7,查询中统计或者分组的字段。

哪些情况不需要创建索引

1,表记录太少
2,经常增删该的表。

Explain 加sql

id列的分析 （重要）

select 查询的序列号。包含一组数字，表示查询中执行select字句或操作表的顺序。
1，id相同，执行顺序由上至下。
2，id不同，如果是子查询的话，id序号会递增，id值越大优先级越高，越先被执行。
3，有相同也有不同，id如果有相同，可以认为是一组，从上往下顺序执行；在所有的组中，id值越大，优先级越高。越先被执行。
## 关注点：id号每个号码，表示一趟独立的查询，一个sql查询的趟数越少越好。

select_type 查询类型

select_type代表的是查询类型，主要是用于区别普通查询，联合查询，子查询等的复杂查询。
select_type属性 以及简单的含义
SIMPLE  简单的select查询，查询中不包括子查询或者union

table

table这个简单，是基于那一张表的

type 是查询的访问类型，很重要的一个指标，结果的顺序如下（重要）

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >
一般来说，得保证查询至少达到 range 级别，最好能达到 ref。
1,ALL （全表扫描，百万计以上优化）
2,ref，非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一张索引的访问。
3,range, 只检索给定的范围行，使用一个索引来选择行，一般是在你的where子句中出现了between,<,>,in等 查询这个范围的要比全文扫描要好很多，不用扫描全部。
4,index， Index是只遍历了一个索引树。从内存里面就加载这个索引了，通常比all快，大白话来说，全表扫描索引。

possible_keys

显示可能应用在这张表中的索引，一个或多个，但不一定被查询实际使用

key （重要）

实际用到的索引
1，如果为null，则没有使用索引。
2，覆盖索引，就是理论上用不到，但是实际上用到了， 是关于复合索引这一块的。按照你建得索引的字段来查询。

key_len

同样结果的条件下，我们的精度越小越好。

ref

显示索引的那一列被使用了。如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值。

rows （重要）

根据表统计信息及索引的使用情况，找到所需记录所需要读取的行数。  行数越少越好

Extra (重要) 十分重要的额外信息

Using filesort 文件内排序，出现了这个不好。排序大大的提高了查询的速度，但是你需要根据你建立索引的顺序来使用。
Using temporary 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见排序有 order by 和分组查询 group by。group by 一定要记住，要么别建，建了就一定要使用。

左连接查询特性 左边一定都会有，右边的话不一定都会有，所以索引最好建在右边，右链接同理。

因此索引最好设置在需要经常查询的字段上

索引失效的问题

1,全值匹配我最爱。
2,最佳左前缀发则，如果有多个复合索引，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。（综合起来，带头大哥不能死，中间兄弟不能断）。
3,不要在索引列上做任何操作比如（计算，函数，自动或手动类型转换），会导致索引失效而转向全表扫描。
4,范围之后全失效。
5,尽量使用覆盖索引列查询。减少select *  只访问索引列会出现using index，很爽的。
6,MySQL在使用 不等于（!=,<>）的时候无法使用索引会导致全表扫描。 （一定要记住不使用）
7,is null,is not null 也无法使用索引。 (避免空值)
8,like以通配符开头'% like 要加右边'，左边会失效。 （利用覆盖索引，建得索引最好就是给我差的字段符合一致）
9,字符串不加单引号会导致索引失效。（varchar类型，一定不是去单引号）
10,少用or用它来链接时，索引会失效。

MySQL优化步骤

1,慢查询的开启并捕获。
2,explain + 慢sql分析
3,show profile查询sql在MySQL服务器里面的执行细节和生命周期情况
4,sql数据库服务器的参数调优。

永远小表驱动大表,即小的数据集，驱动大的数据集

1，select * from A where id in (select id from B)
当B表的数据集小于A表的数据集时用 in 由于 exists
2，select * from A where exists(select 1 from B where B.id=A.id)
当A表的数据集小于B表的数据集时，用exists优于in。
注意A表与B表的字段应建立索引。

select 1 from table where exists(select * from );
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果返回true和false。

order by 关键字优化

最好用索引的列的排序，也遵循最佳左前缀原则。

show Profile

show Profile 是MySQL提供可以用来分析当前会话中资源消耗的情况。可以用于sql的调优和测量。