连接查询和索引

存储引擎

查看所有引擎以及当前使用的引擎：

1 2	show engines; show variables like '%storage_engine%';

主要分为MyISAM与InnoDB

性能下降的原因

1、查询语句写的烂

2、索引失效

3、关联查询太多的join

4、服务器调优及各个参数的设置（缓冲、线程数等）

常见的join查询

查询顺序：
手写：

机读：

七种连接的情况：

# 内连接 左、右表共有
select * from t_emp e inner join t_dept d on e.deptid=d.id;

# 左外 左表独有+左、右表共有
select * from t_emp e left join t_dept d on e.deptid=d.id;

# 左表有，右表没有
select * from t_emp e left join t_dept d on e.deptid=d.id 
where d.id is null;

# 全有 左表独有+左、右表共有+右表独有
# mysql不支持全外
# select * from  t_emp e full out join t_dept d on e.deptid=d.id;
# 使用union联合查询 union:联合加去重
select * from  t_emp e left join t_dept d on e.deptid=d.id union
select * from  t_emp e right join t_dept d on e.deptid=d.id;

# 左、右表各自独有 左表独有+右表独有
select * from  t_emp e left join t_dept d on e.deptid=d.id 
where d.id is null
union
select * from  t_emp e right join t_dept d on e.deptid=d.id
where e.deptid is null;

索引

索引简介

索引就是帮助mysql高速获取数据的一种数据结构，目的在于提高查找效率，类比字典，如果查询mysql这个词，首先定位到m字母，然后依次定位到之后的字母，直到找到为止。

简单的理解为 ” 排好序的快速查找数据结构 “ 。

为了加快Col2的查找，数据库维护了一个查找二叉树（B树），每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在快速获取相应的数据。

索引也很大，不能完全存储在内存中，一般以索引文件的形式存储在磁盘中。

索引分类

单值索引(主键索引)

一个索引只包含单个列，一个表可以有多个单值索引。（建议不超过5个）

normal索引

全文索引

唯一索引

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

复合索引

一个索引包含了多个列

基本语法

创建

1	create [unique] index indexname on table(cloumnname(length));

1	alter table add [unique] index [indexname] on (columnname(length));

删除

1	drop index [indexname] on table;

查看

1	show index from table

索引适用的条件

主键自动建立唯一索引；
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，组合索引性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

索引不适用的条件

表记录太少
经常增删改的表或者字段
Where 条件里用不到的字段不创建索引
过滤性不好的不适合建索引

索引失效的情况

索引失效分析工具：

可以使用explain命令加在要分析的sql语句前面，在执行结果中查看key这一列的值，如果为NULL，说明没有使用索引

在使用like“%字段%”进行查询时，是进行全表扫描的，不会使用索引，因为索引是遵循最左匹配的原则的，所以说当扫描到第一个是“%”时，并不会使用索引进行查询。
or语句前后没有同时使用索引
查询时没有用到联合索引的第一个字段
在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0
对索引字段进行计算操作、字段上使用函数

sql优化

正确使用索引
select不要带*
where后面接索引字段，or两边接索引字段
join条件要有索引字段
减少子查询，连表查询