review（kettle&sql&linux）执行计划 索引

链接数据库需要的信息
1、主机
方便找到服务器在哪里 可以让两者之间传输数据
丢包率 问询与回复次数不一
2、端口
相当于服务器里面每个程度的名牌
在一个主机内端口号不允许重复
如何传输数据
通过IP/TCP协议
常用端口号：
Apache Derby: 1527
oracle：1521
MySQL：3306
Apache hive：10000
Green plum: 5432
sqlserver: 1433
3、用户名
4、密码
5、对象
一个服务器上可以安装多个数据库
数据库可以有多个实例 比如创建orcl orcl2这些端口号可以一样，因为实例名是不一样的，一样可以区分
通过不同的服务名可以连接不同的数据库

数据是如何传输的
socket
seversocket
inputstream
outputstream
在服务端定义服务以及端口号
在客户端进行连接
IP/TCP协议

连接之前先打开服务，再用客户端连接

如何判断是否是本地的库
通过任务管理器查看，如果有显示，那么则是本地的，没有的话则是服务器的

数据库的构架

（1）表空间 存储段区块的位置 表空间的大小取决于制定文件的大小 逻辑空间
一个表空间可以存放多个表的内容
表空间（Tablespace）：Oracle 数据库逻辑上由一个或多个表空间组成。表空间物理上由一个或多个数据文件构成。
（2）段 （Segment） 表头 叫做段 其实就是一张空表 一旦生成了一个段 oracle就会为它分配一个空间（叫做区）
（3）区（Extent） 大小是不确定的，如果不够再去分配 一块一块地分配 （16kb） 存放数据的地方
（4）块（Data Block） 若干个连续存储的数据块组成一个区。数据块是数据库 I/O 最小的单位。数据块的大小是操作系统块大小的整数倍
rowid
where 其实筛选的是rowid

truncate比较快的原因：
删除的维度是不一样的
delete 在块里面删除 一行一行地删除 没有truncate的权限，所以无法实现
truncate 整个区都删除

关键字的执行顺序
from
on
join
where
group by
多行函数 avg sum
having
select
distinct
order by

最后一个执行的是order by
因为其要先取出符合条件的数据，然后再去开辟一个内存空间进行排序
所以执行速度会比较慢
如果不加索引的话，使用order by是比较慢的

上述语句证明关键字的执行顺序是
where>select>order by
所以在sql中，除了order by后面可以加别名，其他情况下禁止别名（子查询除外）

SQL在执行的时候是从后往前执行的
比如下面这个语句是首相筛选department_id，再去筛选employee_id
所以大范围条件写后面先去执行，小范围条件写前面后面执行


查看执行计划的方式（oracle）


虽然两个条件的筛选都花了1秒钟的时间，但是筛选第一个条件占了1%CPU
筛选第二个条件使用了50%的CPU
在筛选的过程中
等值条件永远比非等值条件快

• =最快
• 大于、大于等于 小于 小于等于> >= < <=次之
• 不等于 第三
• like 最慢 （模糊）

如果条件修改为department_id =5000，那么扫描完这个条件就不会再去继续了
（如果后面的查询结果为空那么就停止执行）
一般来说where and 情况下执行的时候，从后往前执行
但是有索引的话，就先查询带有索引的列
如果都有索引，那么还是从后往前执行

MySQL被oracle收购
MySQL先出来 开源（没钱赚）
Oracle性能更好一些 甲骨文公司 安全
所以他俩是一家公司的

MySQL索引比oracle更好
因为MySQL具有innoDB引擎
MySQL的执行计划也比oracle美观

执行计划中的执行顺序可以通过ID 和 缩进看出
前面的*表示是不是在真正地执行
不显示返回多少行结果

组合索引与主键索引的区别
添加了索引就一定会按照顺序排列
在创建表之后如果指定主键，那么就一定会有主键索引,那么数据就会排序

主键索引永远最大 是优先级最高的索引，也是所有索引中耗费空间最大的索引
比如下面对id3也创建了索引，但是没有id3列进行排序，因为主键索引优先级最高

MySQL的执行计划解释

key_len 索引的长度
range 范围扫描
simple 简单查询

虽然id4存在于组合中，但是没带上id3所以不可以使用
最左前缀原则
任何组合索引，查询条件比如带上最左侧列才可以使用
比如上述情况，必须带有id3才可以使用组合索引

建了索引不一定会提高执行效率
看是不是能走所以索引

type：
range 索引范围扫描
all 全表扫描
const 等值查询 最快 就是组合的那种情况
index 全索引扫描

实际走了主键索引

总结：
主键索引与联合索引的区别：
1、依赖的列的个数不同（主键索引1个列，联合索引多个列）
2、主键索引的效率更高
3、主键索引耗费空间更大
4、主键索引的优先级高
5、组合索引存放的是最左侧列的范围 叶子节点存放索引列以及主键列

按照索引的结构划分：
1、B树
2、B+ tree
3、位图bitemap

B+ tree 结构
根节点（主键的rowid的范围）
分支节点
叶子节点（所有的数据会存在叶子节点上）叶子节点之间会有连接线
主键索引的叶子节点会包含所有的列 所以它占空间最大（跟表的大小不相上下）

要用主键索引必须有主键（也就是说where条件里面要有主键，select不一定）

叶子节点连接起来的原因：
对于大于小于效率更好，比如下图要找>2的数据，那么找到2下面的叶子节点数据，那么叶子右边的数据就是满足要求的

组合索引的主要区别在于叶子节点上

**回表 **
对id3做索引（即便是联合索引也只取一列）与主键一样的
但是在叶子节点会存放id3 id4以及主键三列的数据（索引列+一个主键）
所以每一个索引里面必须有主键
为什么要存放主键？
如果我们只查找id3 id4的话，那么只能得到这两列
一般我们select*的话，只找到三四列是没有用的，还是要全表扫描的
加了主键的话，后续可以根据主键去找其他列的数据（这就是回表），还是比全表扫描要快的

一般where后面经常跟啥就在什么上面建立索引

因为要回表四次，所以直接全表扫描

index 全索引扫描
覆盖索引
要查询的列都在索引里面
如果不是覆盖索引，回表次数过多，那么就会全表扫描

联合索引+主键 也是覆盖索引 全索引扫描

一个表不可以超过5个索引

SQL跑得慢
方法之一：查看执行计划 看有没有走索引 没有索引？ 没走索引？

索引失效？
1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) //想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2.对于多列索引，不是使用的第一部分，则不会使用索引(最左前缀原则)
3.like查询是以%开头
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引 //where id = ‘123’（123是字符串类型）

隐式转换
会把字符串类型转换为数字，然后再进行比较（走索引）

如果数值和字符串作比较，一般会将字符串转换为数值
只要数据列的类型没变，那么就可能走索引

Is null 与 is not null可以走索引

Ref 非等值

空值与空值之间不能比较
唯一索引不能限制空值

Null的叶子节点只存空 不存放主键 不存在回表

但是非空可以拿主键回表

只要是覆盖索引，那必然是要走索引的，因为不需要回表
加上主键也属于是覆盖索引


将id !=2 拆成id >2 or id <2

B TREE 只能做全索引扫描 叶子不连
B+ TREE 可以做范围扫描 叶子连着
目前oracle支持B+ TREE
Or可以走索引 （不提倡）



主键一定会排序

Using filesort
表空间文件 .dbf （databasefile）


Group by 也要走索引
排好序的相同的内容放在一起更方便

Ref 匹配的内容

关联走索引（why?排好序的关联更快一点）


MySQL 默认是小表关联大表 先把小表放到内存空间

经常where group by select join的列建立索引

单独的group by也可以走索引 没有最左前缀原则
但是 where要遵循最左前缀原则


Using temporary可以使用索引 临时表 关联和group by都可能用到临时表
Using index 走了覆盖索引
Using index condition（非主键索引） 先进入索引，再在索引里面进行筛选 不是走的覆盖索引
主键索引一定是覆盖索引
只有组合索引和非主键索引有 using index这个标签

Type：（速度由快到慢，性能从优到劣）
Const 主键索引等值查询
Ref 非主键索引等值查询
Range 主键与非主键索引等值
Index 全索引扫描
All 全表扫描

Key_len

相同的数据类型的列的索引 非主键比主键+1 例子见上表
因为它是int类型的，占四个字节


Group by的时候走了两个索引

Ref 匹配的对象 一般只有关联的时候才会有
Rows返回的行数