day039学习笔记

sql引擎

InnoDB

两个文件存储（表结构，数据索引） 5.6之后的默认存储引擎
- 支持事务，行级锁，表级锁，外键（只有innodb）
- 索引结构通过b+树实现
- 主键是聚集索引，其他都是辅助索引（不设置主键默认添加空的主键列-看不到）
Memory

只有表结构存储在硬盘上，其他都在内存里，server断电消失
MyIsam

三个文件存储（表结构，数据，索引） 5.5及之前的默认存储引擎
- 只支持表级锁
- 索引结构通过b+树实现
- myisam都是辅助索引
show engines;显示所有引擎信息

事务和锁

事务
- 概念：将多个指令当做一个完整的命令执行（有一条为成功即视作失败）
- 开启事务 begin/start transactions
- 提交事务commit（若超时未提交则回滚至开启事务之前）
- 特点
  - 持久性
  - 一致性
  - 原子性
  - 隔离性（每个事务独立）
锁
- 行级锁
  
  修改数据时，将整行数据锁住（修改大量数据时用表锁较合适）
```
#参考线程锁，
begin;
select id from s2 where name="alex" for update;
commit;
```
- 表级锁
  
  修改某个数据时会把整张表锁住

索引原理

数据库数据结构
- 线性结构（查询效率低）
  - 列表：连续，占用空间大，删改不灵活，索引取值块，基于数组，list
  - 链表：根据指向，节点，占用空间和删改都灵活，通过索引取值非常慢 deque
- 树形结构tree--二叉树（一个节点最多有两个子节点）
  - 根节点root（唯一）
  - 分支节点branch（多层）
  - 叶子节点（一层）leaf
  - 树的高度，从根节点到叶子节点的层数（树的高度越短，查询步数越少）
计算机的磁盘读取原理
- cpu的计算速度：500million条指令/s 约1亿+python的计算指令
- 计算机的读取效率
  - 速度：7200r/min--120r/s--9ms/r
  - 平均寻道（找到数据）时间 5ms
  - 找到磁道的平均时间 5ms
  - 读一次磁盘的时间 10ms（可执行1百万python指令）
```
#python查看
import dis
def fun():
    a =1
    b =2
    return a + b
dis.dis(fun)
```
- 磁盘预读性原理
  - 每一次读取数据的单位是 block块（linux上默认4096字节）
  - 能够有效避免重复读取文件消耗的IO时间
实际的数据库中数据的存储方式
- 平衡树 balance tree b-tree
  - 根节点区间，分支节点，存范围及对应内存地址（树的高度越低越健康）
  - 根据算法自动调整根节点（维护树的平衡）
- b+树
  - 链式结构，分支、叶子节点互相指向
  - 仅在叶子节点存放数据
回表索引
- 聚簇索引/聚集索引：数据和索引存在一棵树上
- 辅助索引/非聚集索引：数据和索引不存在一棵树上
- 先从辅助索引查索引，然后从聚簇索引查
- 每一个字段都可以创建索引
- 对于一个字段创建的索引在当前字段作为条件时可以起到加速查询的作用
mysql索引的创建与删除
- 创建索引
```
create index 索引名 on 表名（字段名）;
mysql> create index ind_id on s1(id);
```
- 删除索引
```
drop index 索引名 on 表名;
drop index ind_id on s1;
```
- 索引的优缺点
  - 优点
    
    加速查询效率
  - 缺点
    - 拖慢写的速度（减少写的频率,删除索引解决）
    - 占用更多的硬盘空间（相对不重要）

正确使用索引

对哪个字段创建了索引，条件就使用那个字段
索引在条件列不能参与计算，不能调用函数
如果列中重复值多，那么不适合创建索引（如性别-重复率超过10%）
尽量不使用范围查询，范围越小效率越高
- limit初始值越大越慢，使用between and代替
使用like "a%" 效率高于 like "%a"
and 相连的多个条件，如果有一个索引，都可以被命中
```
select * from s1 where id =1000000 and name = "eva";
```

or 相连的多个条件，都有索引，才可以被命中

select count(*) from s1 where id =1000000 or name = "eva";

联合索引和最左侧前缀原则
- 联合索引
  - 创建create index mix_ind on s1(id,name,email);
  - 使用
    - 条件带有联合索引最左侧字段时才可以命中索引
    - 从使用了范围的那个字段之后的所有条件都无法命中索引
```
select * from s1 where id >1000000 and email ="eva2000000@oldboy";#都不会命中
select * from s1 where id =1000000 and email like "%@oldboy";#id部分仍可命中，后面不会
```
    - 使用or根本命中不了联合索引
primary key /unique key /index key都是索引

其他补充

执行计划（分析器中）

指令会在分析器中进行分析，使用什么索引，能否使用
```
#explain
explain  select * from s1 where id =1000000 and email like "%@oldboy";
```
索引合并

创建的两个索引原本是独立的，在特殊的情况下临时合并成一个使用（union）
```
explain  select * from s1 where id =1000000 or email like "%@oldboy";
```
覆盖索引

将索引当做条件，并使用这个索引进行计算，即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。
```
# using index
select count(*) from s1 where id >10000;
```

总结

1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。 
2. 一个表中可以有多个唯一性索引，但只能有一个主键。
3. 主键列不允许空值，而唯一性索引列允许空值。 
4. 索引可以提高查询的速度。 
主键和索引都是键，不过主键是逻辑键，索引是物理键，意思就是主键不实际存在，而索引实际存在在数据库中

posted @ 2019-11-05 08:17 寂静四月阅读(68) 评论(0) 收藏举报

刷新页面返回顶部

day039学习笔记

sql引擎

事务和锁

索引原理

正确使用索引

公告