数据库分析

关键语法

GROUP BY

HAVING

count sum max min avg

 

 

存储机制     缓存机制 （LRU）  SQL解析模块  主从同步 灾难恢复（日志管理）

索引管理  锁模块 

设计关系型数据库：主要分为两个部分 一个部分是存储部分，是用来将数据实现持久化 存储在磁盘中的

，然后就是程序实例部分来对存储进行逻辑管理 。

 

为什么要使用索引：为了避免全表扫描 通过关键字排序 来快速查找数据。

什么样信息信息能成为索引  主键 唯一键

索引的数据结构：B+树

1.非叶子节点的子树指针与关键字个数相同

2.非叶子节点的子树指针P[i],指向关键字值【K【i】，K【i+1】】的子树

3.非叶子节点仅用来索引，数据都保存在叶子节点中

4.所有叶子节点均有一个链指针指向下一个叶子节点（方便做统计）

 

B+树的磁盘读写代价更加低（一次性读取数目更多）

B+树的查询效率更加稳定

B+树更有利于对数据库的扫描

 

hash索引可以了解一下

仅仅能满足“=” “in”，不能使用范围查询

不能利用部分索引健查询

不能避免表扫描

bitmap索引

 

密集索引和稀疏索引

密集索引文件中的每一个收索码值都对应一个索引值（就是某个页在磁盘中的地址）

稀疏索引文件只有索引码的某些值建立的索引项

Innodb

如有一个主键被定义，该主键作为密集索引

若没有主键被定义 第一个唯一非空索引作为密集索引

若以上的都不满足，innodb内部会生成一个隐藏主键  （总的而言一定要有主键）

 

因为如果你通过二级索引查找时候需要回表查询操作

 

如何定位并优化慢查询sql（考察有没有sql语句优化）

1.根据慢日志去捕获慢查询sql

show variable like'%quer%' 

show status like '%slow_queries%'（本次会话中慢sql的条数）

set global slow_query_log = on  打开慢日志

set global slow_query_time = 1  慢sql时间

2.使用explain等工具分析sql

explain一般放在select前面，用来描述mysql将如何执行查询操作，以及成功执行所需要调用的函数

explain可以帮我们分析查询语句，让查询优化器更好的工作

type字段：index all 需要优化了

extra：

3.修改sql或者尽量让sql走索引路线

增加索引  或者就是修改走索引路

 

联合索引的最左匹配原则成因

where  a b

原因是联合索引mysql会一直向右匹配遇到范围查询就停止查询

索引是建立越多越好嘛

1.数据量小的表不需要建立索引，建立索引会增加额外的索引开销

2.数据变更需要维护索引，因此更多的索引意味着更多的维护成本

3.更多的索引意味着需要更多的空间

 

 

MyISAM不支持事务

MyISAM会自动增加表锁  

读锁（共享锁）

写锁（排他锁）

 

MySQL默认是自动提交事务的，

innodb事务文件与索引是绑定的  MySIAM

 

如果关闭了自动提交的话  就要通过显示调用commet来提交该事务 ，innodb是使用了二级锁。调用commet

才会统一释放。

当查询不走索引的时候，使用的是表锁。

当查询使用到索引的时候，行级锁以及GAP锁

IS IX表级别时候不用轮询 看是否有加上行锁

锁模快

1.My'ISAM与Innodb关于锁方面的区别是

   MyISAM默认的是表级锁，不支持行级锁（读锁  写锁 增删改）

   innodb默认的是使用行级锁，也支持表级锁（）

2.数据库事务的四大特性（ACID）

 

3.事务隔离级别以及各级别下的并发访问问题

4.innodb可重复读隔离级别下如何解决幻读

5.RC RR级别下innodb的非阻塞读是如何实现的

 

 

锁的分类：

锁的粒度：表级锁 行级锁 页级锁

锁级别：共享锁 排他锁

锁的方式：自动锁 显示锁

操作划分：可以分为：DML锁（数据操作） DDL锁（表结构）

使用方式划分：乐观锁 悲观锁

使用时间戳  版本号

事务作为单个逻辑单位 全部执行 要么全部失败回滚

 

• Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
• Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。保持完整性约束 （例如转钱那样）
• Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

 

 

提供适当的冗余 会将所有页面操作写到独立的文件中 数据wodonot文件中 。

transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

• READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
• READ-COMMITTED：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
• REPEATABLE-READ：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。
• SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

不可重复读 ：是指在一个事务内，多次读同一数据。

幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

 

快照读   当前读   创建快照读时候就可以确定阅读的版本号

next-key锁 行锁加gap

 

当前读

读取的是最新版本 并且保证其他并发事务不能改变当前值

 

对主键索引或者是唯一索引会用GAP锁吗

1.如果where条件全部命中，则不会用gap锁 只会使记录锁（行锁）

当用的是非主键索引时候 给二级索引还有主键索引都要加上行锁

 2.如果where条件部分命中或者是全不命中，则会加Gap锁