一、基础架构
MySQL逻辑架构图
看着图能讲一条SQL语句的执行流程讲出来,了解MySQL的框架,便于下面学习。
二、日志系统
更新流程涉及两个重要的日志模块
- redo log(重做日志):存储引擎层实现的
- binlog(归档日志):Server层实现的,存储引擎层可以用
- crash-safe:数据库发生异常是可以恢复
用到的技术
- WAL技术:先写日志,再写磁盘。
解释:
InnoDB引擎会先将更新记录写到redo log,并跟新内存,就算完成了更新。系统空闲时,再讲这个操作记录写到磁盘中。
实例分析:
create table T (id int primary key, c int);
update T set c = c+1 where id =2; //将T 表中id为2的行数据中c值加一
执行流程如下:
update语句执行流程
1、执行器让InnoDB引擎取出Id为2的行数据,如果这行所在的数据页在内存中,直接返回给执行器。否则从磁盘读入内存,再返回给执行器。
2、在执行器中将该行c的值加一,得到新的一行数据。再调用引擎接口写入这行新数据。
3、引擎将这行数据更新到内存中,同时将这个更新操作记录到redo log中,此时 redo log 处于 prepare 状态。然后告知执行器执行完成,随时可以提交事务。
4、执行器生成这个操作的 binlog,并把 binlog 写入磁盘
5、执行器调用引擎的提交事物接口,引擎把刚刚写入的 redo log 改成提交(commit)状态,更新完成。
二、为什么要两段提交?
答:两段提交即:InnoDb写redo log日志进入prepare状态、执行器写binlog日志、redo log日志提交(commit),binlog记录的是执行了那些sql语句,redolog是记录操作的数据什么,两段提交可以保证数据库结果状态与sql语句的一致性。避免sqlr日志没有记录,而值改变了。或者有sql语句记录,但值没变。
三、事务隔离
事务:保证一组数据库操作要么全部成功、要么全部失败。现实中的转账过程,包括查询余额、做加减法、更新余额,这些操作必须保证一体。特性:ACID(原子性、一致性、隔离性、持久性)
如何支持事务:MySQL的事务支持是在引擎层实现的,原生的MyISAM引擎不是支持事务,InnoDB支持事务。
隔离性:我们最关心的,没做好会导致:
- 脏读
- 不可重复读
- 幻读
对应的搞出了隔离级别:隔离性依次增强
- 读未提交
- 读提交
- 可重复读
- 串行化
解释:
读未提交和读提交一起记忆理解。一个事务读到另一个事务没有提交的更改叫读未提交,这个数据虽然改了,但没有持久化。若前面事务把它改了,在持久化,那么后面事务就读到错误的数据,称之为脏读。读提交隔离级别可以保证是更改后的最新数据,解决了脏读问题。
可重复读难理解一些,事务A从启动到提交过程中,只看得到自己事务中的数据变化。事务B对共享数据做改变,且提交了事务,事务A是看不到的。只有当事务A也提交了,才get到数据被改变了。
串行化比较好理解,事务A提交后,事务B才能开始。抛弃了并行
隔离级别的实现原理:
默认隔离级别可重复读,在开启事务是就创建一视图(可认为是静态的),事务提交前一直用。读提交也用到视图,读未提交直接返回记录上的最新值,串行化是加锁避免并行访问。
重点理解可重复读:多版本并发控制(MVCC)
四、索引
【要知道索引有哪些类型,不同类型的使用场景,索引的底层数据结构B+树,为什么用它,好处是?】
学习新的数据库先要关心它的底层数据模型,才能从原理上理解它的适用场景。
1. 主键索引的叶子结点存储了整一行的内容(聚簇索引),使用主键可以快速获取到整行的数据。
2. 非主键索引的叶子结点存储的是主键的值,所以主键字段占用空间不宜过大。同时,其查找数据的过程称为“回表”,需要先查找自己得到主键值,再在主键索引上边查找数据内容。
3. 索引的实现由存储引擎来决定,InnoDB使用B+树(N叉树,比如1200叉树),把整颗树的高度维持在很小的范围内,同时在内存里缓存前面若干层的节点,可以极大地降低访问磁盘的次数,提高读的效率。
4. B+树的插入可能会引起数据页的分裂,删除可能会引起数据页的合并,二者都是比较重的IO消耗,所以比较好的方式是顺序插入数据,这也是我们一般使用自增主键的原因之一。(选择自增主键是为了防止,插入时数据页分裂,删除时数据页合并。)
5. 在Key-Value的场景下,只有一个索引且是唯一索引,则适合直接使用业务字段作为主键索引。
详细分析:
1、回表:回到主键搜索数索引的过程
select * from T where k between 3 and 5;
存在两棵索引树,每次查询时需要根据k索引找到主键Id,再通过Id找到行记录。
改进:覆盖索引
select id from T where k between 3 and 5;
只需要查非主键表,不需要回表
2、联合索引:【使用场景?】
目的是减少回表的次数,建立(name,age)联合索引,当查询
select * from tuser where name like '张%' and age = 10 and ismale = 1; 时不会四次回表,因为InnoDB 在 (name,age) 索引内部就判断了 age 是否等于10,对于不等于 10 的记录,直接判断并跳过。在我们的这个例子中,只需要对 ID4、ID5 这两条记录回表取数据判断,就只需要回表 2 次。
五、索引的使用
原则是你写sql语句是真的用上了索引,而不是全表扫描。
1、最左前最匹配原则
按所键索引向右匹配查询范围,直到遇到 > < between like 就停止匹配。所以一旦中间索引遇到 > < between like 后续索引不在使用。
2、索引建立在用于join、where判断、order by 排序字段上,不用给有大量重复值字段建索引。
3、避免where子句中对字段进行null值判断。
4、where子句用orl连接的查询条件,有一个字段无索引会导致引擎放弃索引进而全表扫描。
5、模糊查询导致全表扫描: select id from T where name like ‘%abc%’
6、where字句中使用参数,导致全表扫描。(sql运行时才解析参数,而执行计划是编译时进行选择的,参数是未知的。)
解决方式:强制插叙使用索引: select id from t with(index(索引名)) num = @num
7、避免在 where 子句中对字段进行表达式操作和函数操作,这将导致引擎放弃使用索引而进行全表扫描
如:select id from T where num/2 = 100 应该为: select id from T where num = 100*2
8、索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引。
六、给表字段加索引
1、普通索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
2、多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
3、添加主键索引
alter table 'table_name' add primary key ('column')
4、添加唯一索引
alter table 'table_name' add unique ('column')
唯一索引在此处保证数据记录的唯一性,许多场合不是为了加快访问速度,而是为了限制数据的唯一性。
5、全文索引
alter table 'table_name' add fulltext (‘column’)