Mysql基础

一、基础架构

                                                                                         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支持事务。

 

隔离性：我们最关心的，没做好会导致：

1. 脏读
2. 不可重复读
3. 幻读

对应的搞出了隔离级别：隔离性依次增强

1. 读未提交
2. 读提交
3. 可重复读
4. 串行化

解释：

读未提交和读提交一起记忆理解。一个事务读到另一个事务没有提交的更改叫读未提交，这个数据虽然改了，但没有持久化。若前面事务把它改了，在持久化，那么后面事务就读到错误的数据，称之为脏读。读提交隔离级别可以保证是更改后的最新数据，解决了脏读问题。

可重复读难理解一些，事务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’)