数据库架构

如何设计一个关系型数据库

• 实例与os文件系统相对应
• io时间长，io一次将块和页至于内存，相近数据访问概率高

索引

• 数据量很大时，避免全表扫描
• 类似字典
• 提升查询速度
• 主键，唯一键，普通键作为索引

如何生成索引

二叉查找树

• 左小右大
• 对半搜索，O(logn)

• 查找速度快
• 可能会很深，导致IO增多，速度降低（每深一层一次IO）

B-Tree

• 平衡多路查找树

  

• 平衡

B+-Tree

B+TREE更有优势

• 磁盘读写代价低
• 查询效率稳定（所有查询路径相同）
• 有利于数据库的扫描，范围查询（叶子相连）

Hash索引

• 只能满足=，不满足IN（hash运算前后关系不确定）
• 无法避免排序运算
• 不能利用部分索引
• 不能避免行扫描
• 大量hash相等，性能低下，变为线性

BitMap索引

• 锁力度大
• 应用范围小，关键字有限

密集索引&稀疏索引

• 密集索引：每个搜索码值对应一个索引
• 稀疏索引：只为索引码的某些值建立索引项

代表：（mysql两个引擎）

• InnoDB：密集索引（数据索引存储在.ibd文件）
• MyISAM：稀疏索引（数据索引分开存储.MYI.MYD）

检索过程：

如何调优sql

• 根据慢日志定位慢查询sql

show variables like ‘%quer%’; //查看相关变量

show status like ‘%slow_queries’; //慢查询数量

set global slow_query_log = on;

set global long_query_time = 1; //需要重连

• Explain分析sql

加在执行语句前，查看结果项

type：index，all需要优化

extra：Using filesort/Using temporary

• 修改sql尽量让sql走索引

mysql联合索引最左匹配原则

index（a，b）

只用b select 不走索引

索引越多越好吗

• 数据量小不需要建立索引
• 数据变更需要维护索引，维护成本变高
• 更多索引占用更多空间

锁

数据库锁的分类

按粒度划分

• 表级锁
• 行级锁
• 页级锁

按锁级别划分

• 共享锁
• 排他锁

按枷锁方式划分

• 自动锁
• 显式锁

按操作划分

• DML锁（对数据操作）
• DDL锁（对表结构操作）

按使用方式划分

• 乐观锁（基于数据版本）
• 悲观锁（对外界修改保守态度）

MyISAM&InnoDB锁的区别

• MyISAM默认表级锁，不支持行级锁

对一个表进行操作时，会将表锁，其他session不能操作

读锁（共享锁）不释放，无法增加写锁

读锁（共享锁）不释放，可以增加读锁

写锁（排他锁）不释放，无法增加读锁

写锁不释放，无法增加写锁

lock tables table_name read;

unlock tables;

• InnoDB默认行级锁，也支持表级锁

InnoDB采用二段锁

行级锁只锁一行数据

但是不走索引时，整张表都会被锁住

MyISAM适合的场景

• 频繁执行全表count语句（保存行数变量）
• 对数据增删改频率低，查询频率高（增删改锁表）
• 没有事务

InnoDB适合的场景

• 数据增删改查都频繁（行级锁）
• 可靠性要求高
• 支持事务

数据库事务四大特性

ACID

• 原子性（Atomic）操作一体化
• 一致性（Consistency）总体一致eg转账
• 隔离性（Isolation）并发互不影响
• 持久性（Durability）可恢复，储存持久

事务隔离级别以及各级别下并发访问

事务并发访问可能引起的问题

• 更新丢失

一个事务的更新覆盖了另一个事务的更新

（mysql所有事务隔离级别已经避免）

• Dirty Read

一个事务读到另一个事务未提交的更新数据（回滚发生问题）

select @@tx_isolation; //查看事务隔离级别

set session transaction isolation level read uncommitted; //设置隔离级别

（READ-COMMITTED事务隔离级别以上可避免）

• 不可重复读

A多次读取数据，B在过程中更新数据，导致事务A读取数据不一致

（REPEATABLE-READ事务隔离级别以上可避免）InnoDB default

• 幻读

事务A读取与搜索条件匹配的若干行，事务B插入或删除行的方式修改了A的结果集，导致事务A像出现幻觉

（SERIALIZABLE事务隔离级别以上可避免）Top Level

事务隔离级别

事务隔离级别越高，串行程度越高，并行化越低，效率越低

Oracle默认为READ-COMMITTED，MySQL默认为REPEATABLE-READ

InnoDB可重复读隔离级别下避免幻读

表象：快照读（非阻塞读）——伪MVCC（多版本并发控制）

• 当前读：select…lock in share mode, select…for update

update, delete, insert（）

加了锁的增删改查，读取最新版本，并且锁住避免其他事务修改当前记录

• 快照读：不加锁（SERIALIZABLE事务隔离级别不成立）

可能读到的是历史版本数据

内在：next-key锁（行锁+gap锁）

RR级别以上支持gap锁，防止两次当前读出现幻读

不锁内容本身，锁周围数据

对主键和唯一键索引时：

• where条件全部命中，不会加gap锁，只加记录锁

• where条件部分命中或全不命中，加gap锁

gap锁用在非唯一索引或不走索引的当前读中

1、非唯一索引

(6,9], (9,11] 被锁住（不仅id在范围内，而是范围内所有数据，可能id是20也会被锁住）

官方定义：

2、不走索引

对所有gap上锁

RC、RR级别下InnoDB的非阻塞读的实现

• 数据行里DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID字段

DB_TRX_ID：最后一次对本行数据做修改的事务ID

DB_ROLL_PTR：回滚指针，undo日志记录

DB_ROW_ID：行号

• undo log，提交后丢弃
• update undo log，回滚，快照都需要此log

• read view，能看到的数据，根据算法处理

关键语法

GROUP BY

• SELECT子句中的列名必须为分组列或列函数
• 列函数对于GROUP BY子句定义的每个组返回一个结果

1、查询所有同学的学号、选课数、总成绩

select student_id,count(course_id),sum(score)

from score

group by student_id

GROUP BY会缓存临时数据表

2、查询所有同学的学号、姓名、选课数、总成绩

select s.student_id,stu.name,count(s.course_id),sum(s.score)
from
    score s,
    student stu
where
s.student_id = stu.student_id
group by s.student_id;

HAVING

• 通常与GROUP BY子句一起使用
• WHERE过滤行，HAVING过滤组
• 出现顺序：WHERE>GROUP BY>HAVING

3、查询平均成绩大于60的同学的学号和平均成绩

select student_id,avg(score)
from score
group by student_id
having avg(score)>60

4、查询没有学全所有课的同学的学号、姓名

select stu.student_id,stu.name
from
student stu,
score s
where stu.student_id = s.student_id
group by s.student_id
having count(*) <
(select count(*) from course)