一、数据库架构
关系型数据库:架构、索引、锁、语法、理论范式
Q:如何设计一个关系型数据库?
程序实例 存储管理、缓存机制、SQL解析、日志管理(binlog)、权限划分、容灾机制、索引管理、锁模块 |
存储(文件系统) |
数据库以块或者页作为最小存储单位
二、索引模块
为什么要使用索引
- 快速查询数据
什么样的信息能成为索引
- 主键、唯一键以及普通键等
索引的数据结构
- 生成索引,建立二叉查找树进行二分查找
- 生成索引,建立B-Tree结构进行查找
- 生成索引,建立B+-Tree结构进行查找
- 生成索引,建立Hash结构进行查找
二叉查找树
左子树的节点值均小于等于根节点的值,右子树的节点值均大于等于根节点的值。
查找的时间复杂度为:O(logn)
缺点:每查找一次,就需进行一次IO,且二叉树存在退化成线性结构的可能,查询时间复杂度退化为:O(n)
平衡二叉树
任意根节点的左右子树的高度差不超过1。
B-Tree(平衡多路查找树)
定义:
- 根节点至少包含两个孩子
- 树中每个节点最多含有m个孩子(m>=2),m取决于节点容量和其他配置
- 除根节点和叶节点外,其他每个节点至少有ceil(m/2)个孩子
- 所有叶子节点都位于同一层
每一个节点尽可能多存储信息,减低树的高度,减少查询时的IO次数
假设每个非终端节点中包含有n个关键字信息,其中
a) ki(i=1...n)为关键字,且关键字按顺序升序排序k(i-1)<=ki
b) 关键字的个数n必须满足:[ceil(m/2)-1]<=n<=m-1
c) 非叶子节点的指针:P[1],P[2],...P[m];其中P[1]指向关键字小于k[1]的子树;P[m]指向关键字大于k[m]的子树;其他P[i]指向关键字属于(k(i-1),ki)的子树。
B+树
B+树是B树的变体,其定义基本与B树相同,除了:
- 非叶子节点的子树指针与关键字个数相同
- 非叶子节点的子树P[i],指向关键字值(K[i],K[i+1])的子树
- 非叶子节点仅用来索引,数据都保存在叶子节点中
- 所有叶子节点均有一个链指针指向下一个叶子节点(顺序排列,可用于范围统计)
结论:B+树更适合用来做存储索引
- B+树的磁盘读写代价更低
- B+树的查询效率更加稳定
- B+树更有利于对数据库的扫描
Hash索引
缺点:
- 仅仅能满足“=”,“IN”,不能使用范围查询。(hash后的值与原来的值的大小关系不一定一致)
- 无法被用来避免对数据的排序操作
- 不能利用部分索引键查询(组合索引)
- 不能避免表扫描
- 遇到大量hash值相等的情况后性能并不一定就会比B-Tree索引高
BitMap索引是个神器
只适用于某字段的值只有固定的几个值的情况!!!
另外“锁”力度非常大,适用于并发较少、统计较多的系统
密集索引和稀疏索引的区别
- 密集索引文件中的每个搜索码值都对应一个索引值
- 稀疏索引文件只为搜索码的某些值建立索引项(如只保存了键位信息)
InnoDB
- 若一个主键被定义,该主键则为密集索引
- 若没有主键被定义,该表的第一个唯一非空索引则作为密集索引
- 若不满足以上条件,innodb内部会生成一个隐藏主键(密集索引)
- 非主键索引存储相关键位和其对应的主键值,包含两次查找
如何定位并优化慢查询sql
具体场景具体分析,大致思路:
-
根据慢日志定位慢查询sql
使用命令:show variables like '%quer%';查看:
slow_query_log,需打开:set global slow_query_log = on;;
slow_query_log_file,慢sql日志文件;
long_query_time,慢sql时间设定:set global long_query_time = 1;可能需要重连或重启mysql才能生效,或者直接修改配置文件my.ini永久保存;
使用命令:show status like 'slow_queries';查询慢查询数量
-
使用explain等工具分析sql
关注以下几列:
id:越大越先执行,复合查询越里面越先执行
type:表示mysql找到所需信息的方式。(“index”和“all”表示全表扫描)
extra:
-
修改sql或者尽量让sql走索引
注:mysql查询优化器会优先走索引,且走条件更严格的索引(如唯一索引)来排除更多的数据。
联合索引的最左匹配原则的成因
1、最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 3 and b = 4 and c > 5 and d = 6 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则可以用到,a,b,d的顺序可以任意调整。
2、=和in可以乱序,比如a = 1 and b = 2 and c = 3建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。
mysql会首先对复合索引的第一个字段进行排序。再对第二个、第三个...字段进行排序。如果复合索引为两个字段,则第一个字段的数据就是有序的,第二个字段就是无序的,用第二个字段进行搜素,就用不到索引。
索引是建立得越多越好吗
- 数据量小的表不需要建立索引,建立会增加额外的索引开销
- 数据变更需要维护索引,因此更多的索引意味着更多的维护成本
- 更多的索引也意味着需要更多的空间
三、锁模块
MyISAM与InnoDB关于锁方面的区别是什么
- MyISAM默认用的是表级锁,不支持行级锁。当使用select语句时,会将整个表加上一个读锁,此时另外的查询语句不会被影响,读锁也叫共享锁,写锁会被影响;使用update、insert、delete语句时,会将整个表加上一个写锁,其他的查询和更新语句都会受影响,写锁也叫排他锁。查询语句也可以加排他锁,在select语句后面加 for update
- InnoDB默认用的是行级锁,也支持表级锁。InnoDB默认使用二段锁,即:加锁和解锁,先对同一个事务里的一批操作加上锁,commit后再统一解锁,支持事务,自动提交。InnoDB的select语句不会加共享锁,即可以同时更新select的那些行,显示加共享锁:lock in share mode;加上之后,不能对select的那些行进行更新,必须等select语句commit后才能更新,但是是可以读的。当操作不走索引的时候用的是表级锁。
表级锁跟索引无关;行级锁除了主键id外其余索引都会被加上共享锁或排它锁。
共享锁和排他锁的兼容性:
排他锁 | 共享锁 | |
排他锁 | 冲突 | 冲突 |
共享锁 | 冲突 | 兼容 |
MyISAM适合的场景
- 频繁执行全表count语句
- 对数据进行增删改的频率不高,查询非常频繁
- 没有事务
InnoDB适合的场景
- 数据增删改查都非常频繁
- 可靠性要求比较高,要求支持事务
数据库锁的分类:
- 按锁的粒度划分,可分为表级锁,行级锁,页级锁
- 按锁级别划分,可分为共享锁,排他锁
- 按加锁方式划分,可分为自动锁,显式锁
- 按操作划分,可分为DML锁(对数据操作),DDL锁(对表结构变更)
- 按使用方式划分,可分为乐观锁,悲观锁
数据库事务的四大特性
ACID
- 原子性(Atomic),全都做或全都不做。
- 一致性(Consistency),指数据库的数据应满足完整性
- 隔离性(Isolation),多个事务并发执行时,一个事务不应该影响其他事务的执行
- 持久性(Durability),事务一旦提交,其修改应该永久保存在数据库中,用于数据恢复
事务隔离级别以及各级别下的并发访问问题
事务并发访问引发的问题以及如何避免
- 更新丢失——mysql所有事务隔离级别在数据库层面上均可避免
- 脏读——READ-COMMITTED事务隔离级别以上可避免。设置当前事务隔离级别:set session transation isolation level read uncommitted;开启事务:start transation。指一个事务读到另一个事务的未提交数据,例如session1更新了数据,但是还未提交,session2此时读到的数据即为session1更新后的数据,但是session1因为某些原因回滚了,session2读到的由session1更新后的数据就是不存在的,即为脏读。将读的事务隔离级别设置为read committed;(即只能读取事务提交后的数据)可避免这种情况,oracle数据库默认的事务隔离级别就是read committed
- 不可重复读——REPEATABLE-READ事务隔离级别以上可避免。事务A多次读取某条数据,事务B在事务A读取的过程中进行了更新并提交,导致事务A多次读取同一数据时结果不一致。解决办法:将事务隔离级别再调大一级:REPEATABLE-READ(可重复度),mysql默认的事务隔离级别就是REPEATABLE-READ。此时事务A每次读的数据都是一样的,别的事务所做的更新不会影响事务A读的结果,但是事务A的更新是基于其它事务提交后的结果再进行操作。既避免了脏读,又保证了一致性。
- 幻读——SERIALIZABLE事务隔离级别可避免(最高隔离级别)。事务A读取若干行,事务B以插入或删除行的方式来修改事务A的结果集,导致事务A像出现幻觉一样。例如session1使用当前读:select * from xx;session2使用插入操作:insert into xx values(...);此时session1进行更新xx表,发现多出来一条数据,就像出现幻觉一样,即为幻读。此隔离级别下所有的数据库操作都会自动加上锁。session1读的事务(会自动加上lock in share mode)提交后session2的更新操作才能执行。
InnoDB可重复读隔离级别下如何避免幻读
- 表象:快照读(非阻塞读)--伪MVCC
- 内在:next-key锁(行锁+gap锁)
当前读和快照读
- 当前读:select ... lock in share mode,select ... for update
- 当前读:update,delete,insert
- 快照读:不加锁的非阻塞读(前提是事务隔离级别不为serializable),select。为了多并发,读取的数据可能不是最新版本。
当前读:读取数据的最新情况,并加锁
RC事务隔离级别下,当前读和快照读读到的数据是一样的;RR事务隔离级别下,当前读能读到最新数据,快照读可能读到历史版本也可能读到最新版本。RR事务隔离级别下首次创建快照读决定了读取数据的版本。
next-key锁(行锁+gap锁)
- 行锁
- Gap锁(会锁住数据周围的间隙),比如session1:delete ... where id=9,session2:insert ... id=10;session2会被锁住。
对主键索引或者唯一索引会用Gap锁吗
- 如果where条件全部命中,则不会用Gap锁,只会加记录锁。
- 如果where条件部分命中或全不命中,则会加Gap锁。
Gap锁会用在非唯一索引或者不走索引的当前读中
RC、RR级别下的InnoDB的非阻塞读(快照读)如何实现
- 数据行里的DB_TRX_ID(最后一次对本行数据操作的事务ID)、DB_ROLL_PTR(回滚指针)、DB_ROW_ID(行ID)字段
- undo日志(insert undo log、update undo log)。当对数据进行更新操作时就会记录undo日志,存储老版数据。
- read view。决定select语句可见哪个版本的数据
四、语法部分
关键语法
- GROUP BY
- HAVING
- 统计相关:COUNT、SUM、MAX、MIN、AVG
GROUP BY
- 满足“SELECT子句中的列名必须为分组列或列函数”
- 列函数对于group by子句定义的每个组各返回一个结果
- group by里出现某个表的字段,select里面的列要么是该group by里出现的列,要么是别的表的列或者带有函数的列
HAVING
- 通常与group by子句一起使用
- where过滤行,having过滤组
- 出现在同一sql的顺序:where>group by>having
- 如果没有group by,having的作用同where