MySql 连接查询及 Explain关键字

1、连接查询 ---------七种 JOIN

a LEFT JOIN b ON a.id = b.id 返回 a 表全部数据以及 a 表和 b 表有 相同id 的数据

 

a RIGHT JOIN b ON a.id = b.id 返回 b 表全部数据以及 b 表和 a 表有 相同id 的数据

 

a INNER JOIN b ON a.id = b.id 返回 a b 两表 id相同 的数据

 

a LEFT JOIN B ON a.id = b.id where b.id is null 返回a表有而 b 表没有的数据（a的独有数据）

 

a RIGHT JOIN B ON a.id = b.id where a.id is null 返回 b 表有而 a 表没有的数据（b的独有数据）

 

a LEFT JOIN b ON a.id = b.id UNION a RIGHT JOIN b ON a.id = b.id

返回 a表 和 b 表 的共有数据 + a表的独有数据 + b表的独有数据

 

a LEFT JOIN b ON a.id = b.id where b.id is null UNION a RIGHT JOIN b ON a.id = b.id where a.id is null

返回 a 表的独有数据 + b 表的独有数据

 

 

2、索引( Index )

定义：帮助MySql高效获取数据的 数据结构 ------ 排好序的快速查找数据结构

 

 

 

3、Sql优化

 

EXPLAIN:

id select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序

id相同，执行顺序由上而下

如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id如果相同，可以认为是一组，从上往下顺序执行;在所有组中，id值越大，优先级越高，越先执行

select_type

table

partitions

type 从最好到最差依次是： system > const > eq_ref > ref > range > index > ALL

system：表只有一行记录（等于系统表），这是const类型的实例，平时不会出现，可以忽略不计

const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快；

如将主键置于where列表中，MySql就能将该查询转换为一个常量

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

ref：非唯一性索引扫描，返回匹配某个单独值的所有行，会返回所有匹配的某个单独值的行，可能会找到多个符合的行

range：只检索给定范围的行

index：全索引扫描，只扫描索引树

ALL：全表扫描

 

possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key：实际使用的索引。如果为NULL，则没有使用索引

key_len：表示索引中使用的字节数。在不损失精确度的情况下，长度越短越好。它显示的值为索引字段的最大可能长度，并非实际使用长度。

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

rows：每张表有多少行被优化器查询

Extra：包含不适合在其他列中显示但十分重要的额外信息（只列出三个最重要的）

Using filesort：在使用 order by 进行排序时，如果没有用到 建索引的字段进行排序，那MySql将进行一次对数据的排序。

Using temporary：产生了临时表

Using index：使用了索引。直接返回索引对应的数据而不要在发起查询的请求

 

 

4、利用索引对Sql进行优化

单表：查询条件若为范围性的，索引会失效；所以索引应建立等于查询的字段上；

 

两表连接查询：左查询索引建立在右表，右查询建立在左表；

 

三表连接查询：永远用小结果集驱动大的结果集； 保证被驱动表上join字段以及被索引

 

 

 

5、索引失效（应该避免）

 

10条索引失效常见原因

 

 

 

6、小表驱动大表

优化原则：小表驱动大表，即小的数据集驱动大的数据集

select * from A where id in (select id from B)

当B表的数据集必须小于A表的数据集时，用in 优于 exists

 

select * from A where exists (select 1 from B where B.id = A.id)

当A表的数据集小于B表的数据集时，用exists 优于 in

 

 

 

7、Order By字句排序优化