七种连接
查询表A和表B共有
` select * from t_emp a inner join t_dept b on a.deptId = b.id;`A、B两表共有+A的独有
select * from t_emp a left join t_dept b on a.deptId = b.id;A、B两表共有+B的独有
select * from t_emp a right join t_dept b on a.deptId = b.id;A的独有
select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null; B的独有
select * from t_emp a right join t_dept b on a.deptId = b.id where a.deptId is null; A独有+B独有
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;索引
索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
索引的缺点
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，
都会调整因为更新所带来的键值变化后的索引信息
索引的分类
- 主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引
- 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
- 唯一索引：索引列的值必须唯一，但允许有空值
- 符合索引：即一个索引包含多个列
创建索引
CREATE INDEX 索引名称
ON 表名称 (列名称)删除索引
DROP INDEX 索引名称 ON 表名称; 查看索引
SHOW INDEX FROM 表名称
B树
- 一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）
- 如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
- B 树相对于平衡二叉树，每个节点存储了更多的键值（key）和数据（data），并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的 B 树为 3 阶 B 树。
B+树
所有data信息都移动叶子节点中，而叶子节点和子节点之间会有个指针指向，这个也是B+树的核心点，这样可以大大提升范围查询效率，也方便遍历整个树。
在B+树中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值息，这样可以大大加大每个节点存储的key值数量，降低B+树的高度。
- 非叶子节点不再存储数据，数据只存储在同一层的叶子节点上;
- 叶子节点之间，增加了链表，获取所有节点;
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引(where 后面的语句)
- 查询中与其它表关联的字段，外键关系建立索引
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
哪些情况不需要创建索引
- 表记录太少
- 经常增删改的表
- Where条件里用不到的字段不创建索引
- 数据重复且分布平均的表字段
Explain
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
执行计划包含的信息
各字段解释
id
查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
- id相同，执行顺序由上至下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同和不同的同时存在：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
select_type
- SIMPLE：简单的 select 查询,查询中不包含子查询或者UNION
- PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
- DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)
- SUBQUERY：在SELECT或WHERE列表中包含了子查询
- UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；
- UNION RESULT：从UNION表获取结果的SELECT
type
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system>const>eq_ref>ref>range>index>ALL
- system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
- const：表示通过索引一次就找到了,const用于primary key或者unique索引。因为只匹配一行数据，所以很快
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行。
- range：只检索给定范围的行,使用一个索引来选择行
- index：index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
- all：将遍历全表以找到匹配的行
- possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
- key
实际使用的索引。如果为NULL，则没有使用索引
- key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
- ref
显示索引的哪一列被使用了，也可能是一个常数。哪些列或常量被用于查找索引列上的值
Extra
包含不适合在其他列中显示但十分重要的额外信息
- Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
- Using temporary ：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
- USING index：表示相应的select操作中使用了覆盖索引(Covering Index)。
- Using where：表明使用了where过滤
索引失效
- 建立索引的顺序和查询的顺序一样
索引idx_staffs_nameAgePos建立索引时 以name ， age ，pos 的顺序建立的。查询时按照建立的顺序
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
- 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
- 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
我们以name ， age ，pos 的顺序建立索引，由于使用了age>11这个条件，pos索引将不能用到
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
- mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
- is not null 也无法使用索引,但是is null是可以使用索引的
- like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
- 字符串不加单引号索引失效： 底层进行转换使索引失效，使用了函数造成索引失效，相当于在列上进行了操作导致索引失效。
mysql
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。