标签：体系 语句 事务 复习 索引 MySQL 数据库系统 数据 日志

SQL和优化

sql执行过程：

1:SQL解析 语法检查 语义检查:语句是否有意义，例如，语句中的表和列是否存在

2:解析器:将 SQL 接口传递过来的 SQL 语句进行解析，翻译成 MySQL 自己能认识的语言

3:MySQL 查询优化器MySQL 会帮我去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划，比如你创建了多个索引，MySQL 会依据成本最小原则来选择使用对应的索引

4:优化器执行选出最优索引等步骤后，会去调用存储引擎接口，开始去执行被 MySQL 解析过和优化过的 SQL 语句,正执行 SQL 的动作是在存储引擎中完成的

5:执行 SQL 的时候都会将其数据加载到内存中，这块内存就是 InnoDB 中一个非常重要的组件：缓冲池(缓存)该条记录会被加上一个独占锁

6：在将该条语句加载到 Buffer Pool 中的时候同时会往 undo 日志文件中插入一条日志，也就是将 id=1 的这条记录的原来的值记录下来

1. 准备更新一条 SQL 语句
2. MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（BufferPool）中
3. 在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
4. innodb 会在 Buffer Pool 中执行更新操作
5. 更新后的数据会记录在 redo log buffer 中

7：MySQL 提交事务的时候，会将 redo log buffer 中的数据写入到 redo 日志文件中 刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置

值为 0 表示不刷入磁盘

值为 1 表示立即刷入磁盘

值为 2 表示先刷到 os cache

7.myslq 重启的时候会将 redo 日志恢复到缓冲池中

优化案例

更新中

数据库存储

Mysql

mysql引擎主要是InnoDB和MyISAM，在发展中，MyISAM因为不支持事务基本被淘汰，现在所使用的都是InnoDB

区别

• InnoDB支持事物，而MyISAM不支持事物
• InnoDB支持行级锁，而MyISAM支持表级锁
• InnoDB支持MVCC, 而MyISAM不支持
• InnoDB支持外键，而MyISAM不支持
• InnoDB不支持全文索引，而MyISAM支持

PostgreSQL

引擎为heap表引擎，存储postgresql 的用户表和系统表的实际数据以及索引数据

索引

索引提升速度的原理

MySQL 的流程是这样的，扫描所有行，把所有行加载到内存后，再按 age 排序生成一张临时表，再把这表排序后将相应行返回给客户端，更糟的，如果这张临时表的大小大于 tmp_table_size 的值（默认为 16 M），内存临时表会转为磁盘临时表，性能会更差，如果加了索引，索引本身是有序的 ，所以从磁盘读的行数本身就是按 age 排序好的，也就不会生成临时表，就不用再额外排序 ，无疑提升了性能

数据结构

如果没有索引，走的是普通的全表遍历

mysql采用的是b+ tree

不采用红黑树的原因：

MySQL 数据量很大的时候，索引的体积也会很大，可能内存放不下，所以需要从磁盘上进行相关读写，如果树的层级太高，则读写磁盘的次数（I/O交互）就会越多，性能就会越差

红黑树的层高不可靠,所以使用b+tree缩短层高

B-tree 节点中的每个关键字都有数据，而 B+tree 中间节点没有数据，只有索引，B+tree 因为叶子节点通过双向链表进行连接，找到指定范围内的下限后，直接通过链表顺序遍历

聚集索引

主键索引和行记录时存储在一起的，因此叫做聚集索引（clustered index）。除了聚集索引，其他所有都叫做非聚集索引, 在InnoDB中，只存在一个聚集索引

• 若表存在主键，则主键索引就是聚集索引；
• 若表不存在主键，则会把第一个非空的唯一索引作为聚集索引；
• 否则，会隐式定义一个rowid作为聚集索引
• 聚集索引的叶子节点存储的是主键和行记录，非聚集索引的叶子节点存储的是主键

索引失效

当sql中含有or时可能使索引失效

负向查询，可能会导致索引失效 not like != not in

索引字段可以为null，使用is null或is not null时，可能会导致索引失效

在索引列上使用内置函数，一定会导致索引失效

比如下面语句中索引列login_time上使用了函数，会索引失效：

select` `* ``from` ````user``` ``where` `DATE_ADD(login_time, INTERVAL 1 ``DAY``) = 7;

对索引列进行运算，一定会导致索引失效

运算如+，-，*，/等，如下：

select` `* ``from` ````user``` ``where` `age - 1 = 10;

like通配符可能会导致索引失效

ke查询以%开头时，会导致索引失效。解决办法有两种：

将%移到后面，如：

select` `* ``from` ````user``` ``where` ````name``` ``like` `'李%'``;

利用覆盖索引来命中索引。

select` `name` `from` ````user``` ``where` ````name``` ``like` `'%李%'``;

MySQL优化器的最终选择，不走索引

最左匹配

联合索引中，优先走最左边列的索引

例如某表现有索引(a,b,c)，现在你有如下语句：

select * from t where a=1 and b=1 and c =1;

这样可以利用到定义的索引(a,b,c)

select * from t where a=1 and b=1;

这样可以利用到定义的索引(a,b,c),用上a,b

select * from t where b=1 and a=1;

这样可以利用到定义的索引(a,b,c),用上a,b(mysql有查询优化器)

select * from t where a=1;

这样也可以利用到定义的索引(a,b,c),用上a

select * from t where b=1 and c=1;

这样不可以利用到定义的索引(a,b,c)

select * from t where a=1 and c=1;

这样可以利用到定义的索引(a,b,c)，但只用上a索引，b,c索引用不到

也就是说通过最左匹配原则可以定义一个联合索引，使得多种查询条件都可以用到该索引

跨a，c不会使c的索引生效

事务

概念

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体。

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

并发问题

　1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

　　2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

　　3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

脏读：一个事务读取到了另一个事务未提交的数据（两个事务修改同一字段），则发生了脏读，若该事务发生回滚，读取的数据无效

幻读：一个事务读取了另一个事务的字段，当该事务有添加了新的字段，再取读取时，发现多了一行或几行，发生幻读

不可重复读：一个事务读取一个字段，另一个事务修改了该字段并提交，再次读取该字段时发现数据不一样，发生不可重复读

脏写：事务 B 去修改了事务 A 修改过的值，但是此时事务 A 还没提交，所以事务 A 随时会回滚，导致事务 B 修改的值也没了

解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

事务隔离级别

四种隔离级别

读未提交（Read uncommitted）：

这种事务隔离级别下，select语句不加锁

此时，可能读取到不一致的数据，即“读脏 ”。这是并发最高，一致性最差的隔离级别

2.读已提交（Read committed）：

可避免 脏读 的发生

在互联网大数据量，高并发量的场景下，几乎 不会使用上述两种隔离级别

3.可重复读（Repeatable read）：

MySql默认隔离级别

可避免 脏读 、不可重复读 的发生。

4.串行化（Serializable ）：

可避免 脏读、不可重复读、幻读 的发生

MVCC

多版本并发控制

MVCC是为了解决什么问题?
锁机制可以控制并发操作，但开销大，所以用mvcc代替锁

InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现，存储的是事务的id，每开始一个新事务，版本号就会递增

并发事务时，InnoDB只会查找事务ID小于等于当前事务ID的数据行，所以不受新插入数据的影响

场景：并发事务时，执行一条插入语句id为1，此时另外一个事务也往该表插入了一条数据id为3，在执行查询时（id=2）不会查到id=3的数据，解决了幻读问题

日志与锁

日志是数据库修复的重要东西

• 二进制日志：该日志文件会以二进制的形式记录数据库的各种操作，但不记录查询语句
• 错误日志：该日志文件会记录 MySQL 服务器的启动、关闭和运行错误等信息。
• 通用查询日志：该日志记录 MySQL 服务器的启动和关闭信息、客户端的连接信息、更新、查询数据记录的 SQL 语句等
• 慢查询日志：记录执行事件超过指定时间的操作，通过工具分析慢查询日志可以定位 MySQL 服务器性能瓶颈所在

Redo Log（重做日志）：

Redo Log记录的是物理日志，也就是磁盘数据的修改。
用来保证服务崩溃后，仍能把事务中变更的数据持久化到磁盘上。

1. 从磁盘加载数据到内存
2. 在内存中修改数据
3. 把新数据写到Redo Log Buffer中
4. 把Redo Log Buffer中数据持久化到Redo Log文件中
5. 把Redo Log文件中数据持久化到数据库磁盘中

Undo Log（回滚日志）：

Undo Log记录的是逻辑日志，用来回滚事务时，恢复到修改前的数据。

比如：当执行一条insert语句时，Undo Log就记录一条相反的delete语句

标签：体系,语句,事务,复习,索引,MySQL,数据库系统,数据,日志
From： https://www.cnblogs.com/saylk/p/16773119.html