MySQL笔记总结

一、sql基础

1.sql执行顺序

2.七种join

内连接（两者交集）

select * from tableA a inner join tableB b on a.key=b.key;

左外连接（左表a独有部分加上两者交集,独有部分对应另一个表字段为null）

select * from tableA a left join tableB b on a.key=b.key;

左连接（左表a独有部分）

select * from tableA a left join tableB b on a.key=b.key where b.key is null;

右外连接（右表b的独有加上两者交集）

select * from tableA a right join tableB b on a.key=b.key;

右连接（右表b独有部分）

select * from tableA a right join tableB b on a.key=b.key where a.key is null;

全连接（a表独有加b表独有加两者交集）

select * from tableA a left join tableB b on a.key=b.key

union

select * from tableA a right join tableB b on a.key=b.key;

各自独有部分

select * from tableA a left join tableB b on a.key=b.key where b.key is null

union

select * from tableA a right join tableB b on a.key=b.key where a.key is null;

二、索引

1.说明

索引是帮助数据库高效获得数据的数据结构（排好序的快速查找数据结构，有B树结构、哈希），索引一般本身也很大，往往以索引文件的形式存在磁盘。
索引优势：提高检索效率、降低数据排序成本
索引劣势：实际是索引也是一张表，保存了主键和索引字段，并指向实体表记录，也要占空间；降低更新表的速度

2.索引种类

单值索引：索引只包含单列
唯一索引：列值必须唯一，但允许有空值
复合索引：索引包含多列

3.创建索引

可以使用create index 和alter table add index

4.索引结构

BTree、hash、full-text全文、R-Tree

5.创建索引情形

适合建索引：

主键自动建立唯一索引
频繁作为查询条件的字段
与其它表关联的查询字段
高并发下倾向创建组合索引
索引字段与排序字段、统计分组字段一致，可以提供排序速度

不适合建索引：

数据太少
频繁更新的字段
字段值重复太多（比如性别字段只有男女）
where条件里用不到的字段

三、explain执行计划

1.id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。id相同的执行顺序由上往下；id不同，如果是子查询，id号会自增，值越大优先执行

2.select_type：查询类型，有6种
simple：简单的select查询，不包含子查询和union
primary：查询中包含复杂的字子部分，最外层则被标记为primary
subquery：select或者where中包含子查询
derived：在from列表中包含的子查询被标记为derived（衍生）
union：若 第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，最外层select被标记为derived
union result：从union表获得结果的select

3.table：执行的表名

4.type：查询类型，从好到差的排序是system>const>eq_ref>ref>range>index>all,一般来说得保证查询到达range级别，最好ref。
system：表只有一行记录（等于系统表），这是const的特例
const：通过索引一次就找到了，用于比较primary key或者unique索引。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值所有行，本质上也是一种索引访问
range：只检索给定范围的行，使用一个索引来选择行。一般是在where中出现了between、<、>、in等的查询
index：全索引扫描，index和all都是读全表，但是index只遍历索引数，是从索引中读取，而all是从硬盘读取。index通常比all快，因为索引文件通常比数据文件小。
all：全表扫描

5.possible_keys：可能应用到的索引，若查询字段存在索引，则会被列出来，但不一定会真的使用到。

6.key：实际使用的索引，若为null，则没有使用索引。查询中若使用了覆盖索引（覆盖索引是select查询的字段和顺序，跟创建的复合索引字段和顺序一样），则该索引仅出现在key列表中

7.key_len：显示为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。在不失精度的条件下，长度越短越好。

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

9.rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数。

10.Extra：包含不适合在其他列显示，但十分重要的额外信息
Usring filesort：mysql对数据使用了一个外部的索引排序，不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。
Using temporary：在对查询结果排序时使用了临时表，常见于排序order by和分组查询group by
Using index：表示相应的select操作中使用了覆盖索引，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
Using where：使用了where过滤
Using join buffer：使用连接缓存
impossible where：where子句的值总是false，不能返回任务数据

四、索引优化

1、索引字段使用范围查询其后面字段索引无效。

2、两表left join查询，在右边关联字段上建索引，这是因为左连接左表一定有，关键点是如何从右表搜索。右连接相反再在左表建索引。

3、如何避免索引失效：
（1）遵守最佳左前缀法则：如果索引了多列（复合索引），查询条件的列从索引的最左前开始并且不跳过索引的列。
（2）