数据库三范式
- 每一列都已经是不可拆分的最小单元
- 保证每张表只描述一件事
- 不传递非主键的的依赖
事物的四大特性
- 原子性 要么执行，要么都不执行
- 一致性 事物执行前后，数据从一个有效状态迁移到另一个有效状态，这个有效可以是数据库月数，也可以是我们的业务月数，在ACID中，我们利用AID来保证C
- 隔离性 事物之间根据不同的隔离级别具有隔离属性
- 持久性 事物一旦提交，就不能再被修改、回滚
事物问题
- 脏读 读到其他事物未提交的数据
- 幻读 读到其他事物已提交插入的数据，数量不一致
- 不可重复读 读到其他事物已提交修改的数据，值不一致
事物隔离级别
- 串行化 所有请求串行执行，规避所有事物问题
- 可重复读 在同一个事物中能读到相同的数据，规避不可重读和脏读，mysql通过mvcc解决了幻读
- 读已提交 只会读到其他事物已经提交的数据，规避脏读
- 读未提交 可以读到所有状态数据
- readonly（oracle特有事务， 串行化、读已提交、readonly）
Mysql默认事物隔离级别
可重复读。
历史原因跟binlog有关系，以前的binlog是statement形式，就是条sql，在非可重复读场景下会有问题；
binlog模式
- statement模式，记录每条提交的sql
- row模式，记录每一行修改后的数据 新版本对row模式也做了优化，并不是所有的修改都会以row 来记录，像遇到表结构变更的时候就会以statement模式来记录
- mixed模式，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也是在statement和row之间选择一种。
Mysql的引擎类型
- innodb 支持事务 表锁、行锁、间隙锁
- myIsam 不支持事务 表锁 适用于只读或写少，数据行少的场景
- dbd 不支持事务 表锁 页面锁
- memory 不支持事务 表锁
MyIsam和Innodb的区别
- MyIsam 不支持事务；表锁；非聚簇索引；存储文件有三个，表结构、索引、数据文件
- Innodb 支持事物；表锁、行锁；聚簇索引；存储文件两个，表机构、聚簇索引文件
Innodb事务实现
通过transaction命令或者begin命令开启，每一个事务创建的时候会分配一个唯一自增的事务ID，数据行存在一个隐藏字段就是上一次修改此行记录的事务ID
Innodb逻辑存储结构
- 表空间 共享表空间：undolog、事务 私有表空间：表结构、索引、数据
- 段Or表
- 区 物理上连续的几个页
- 页Or块 16K
text这些大文本是如何存储的
使用溢出页，在表私有空间中，有一些位置存储大型数据比如text，聚簇索引上存储数据开头的一部分数据和这个溢出页的地址
Mysql中的锁
- 表锁 LOCK TABLES 给表显式加表锁
- 行锁 在索引上实现的，所以如果要加行锁，必须是走索引查询的语句，否则会降级为表锁
- 页锁
- 间隙锁 用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）；可以防止当前读的幻读
- 读锁 select默认不加读锁，可以使用 lock in share mode；排斥写锁
- 写锁 update、delete、insert默认加写锁；select for update；排斥读锁和写锁
- 意向锁 为了同时支持行锁与表锁，在行锁加读锁时，需要先申请表级意向锁，自动的
Mysql中的索引数据结构
B+Tree
Hash
fulltext 全文索引 倒排
RTree 三维空间上的索引，每个节点维护了多个指针指向空间上的相邻点
为什么用B+树
索引很大不能存储在内存，需要存储在磁盘上，这样就会有IO问题，磁盘一次可以预读页数据，要充分利用预读，索引的数据结构要尽量保证在查询过程中减少IO次数，索引存储在逻辑存储结构页上，一页为16k，查找数据过程
B+树在可以在一页上维护2048个节点，两层就可以索引400w行数据（理论上）
B+树的叶子节点上维护了指向上一条和下一条记录的指针，形成一个双向链表，范围查找效果好；
Hash 无法实现范围查找
红黑树 树高问题
B树 每个节点都存储数据，每页上的数据量就会少，就比B+树高
什么是聚簇索引
innodb的文件组成只有两个，一个索引文件、一个数据库描述文件；而数据存储在索引文件中
innodb的table的主键索引的，叶子节点，存储了这行记录的完整数据，以索引查询数据的时候，能直接拿到数据，不需要再去另外的磁盘查询数据，减少IO
Mysql执行流程
查询等过程如下：权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
更新等流程如下：分析器----》权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit
Mysql执行计划explain
id 有顺序的编号，查询顺序，有几个select就有几行
selectType 查询类型
table 表名
type 重要标志
>const 索引一次命中，匹配一条记录
>system 表中只有一行记录
>eq_ref 唯一索引扫描，只有一条记录
>ref 非唯一索引扫描返回匹配的某个值
>range 质检所给定范围的行，使用一个索引来选择 一般用于between <>
>index 只遍历索引树
>all 全表扫描
key 使用的索引
rows 估算扫描行数
filtered 有效行与扫描行的百分比 越高越好
extra 扩展信息
>using where 检索过程使用了where过滤 性能好
>using index 使用了覆盖索引 性能好
>using filesort 使用了文件排序，sql语句用到了orderby 但是orderby的列没有索引，性能差
>using temporary 使用临时表保存结果 性能极差 多见于groupby语句
什么是覆盖索引
一次查询使用的索引包含所有返回信息，不需要回表
sql优化
- 尽量使用较低的隔离级别；
- 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
- 选择合理的事务大小，小事务发生锁冲突的几率也更小
- 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
- 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
- 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
- 不要申请超过实际需要的锁级别
- 除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
- 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能
undolog redolog binlog relaylog
undolog 隐藏字段undolog指针，事务修改时，老数据进入undolog，更新数据行上的undolog指针；用于事务回滚，和控制可见性；undolog是一段存储空间，会被覆盖丢弃
redolog 事物执行期间，记录的事物修改的数据，存储A->B；数据库宕机时，回滚进行中的事务
binlog 数据库日志文件，用于记录数据库sql记录，和数据主从同步，只记录已提交的事务
relaylog 从库维护的binlog缓冲，主从同步时，binglog进入relaylog，从库执行relaylog的binlog
当前读和快照读
当前读
当前读指的是读取数据当前最新数据。update、insert、delete、select for update（排他锁）、select lock in share mode。读取数据需要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
快照读
快照读指的是在读取数据时，生成读取快照，在同一个事物中可能会一直读取此快照的数据。快照读读到的数据可能不是最新的，可能是历史版本的数据，这些历史版本的数据就是从undo log中获取的。
事物中的select 不加锁的情况会执行快照读，快照读依赖readview来实现。
快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。
MVCC可见性逻辑
多版本并发控制，减少数据库使用过程中的读写冲突，无锁。
ReadView 读视图，维护当前活跃的事物列表trx_list，活跃的最小事务low_limit_id，即将生成的下一个事物up_limit_id，还有一个当前事物trx_id
1.比较trx_id是否小于low_limit_id 或者为当前事物ID，如果为true，则代表修改此行数据的事物早已提交或者就是当前事务进行的修改，当前记录可见。否则进入下一步判断。
2.比较trx_id是否大于等于up_limit_id，如果为true，则代表修改此行记录的事物晚于当前读视图创建，当前记录不可见，根据DB_ROLL_PTR undo log指针找到上一条记录，从新进行可见性分析。否则进入下一步判断
3.判断trx_id是否在trx_list列表中，如果在，代表修改此行记录的事物还未提交，当前事务不可以读取当前记录，根据DB_ROLL_PTR undo log指针找到上一条记录，从新进行可见性分析。否则说明数据在readview生成的时候已经提交，当期事物可以读取当前记录。
事物隔离级别与MVCC
在RR级别下，事物进行快照读时会检查当前事物是否已经创建过ReadView，如果存在，则使用已经创建的，这也是实现可重复读的方法。
在RC级别下，事物每一次快照读都会创建一个新的ReadView，这样就会造成不可重复的和幻读的问题。
Mysql如何解决的幻读
当前读 行锁和间隙锁
快照读 MVCC
Mysql主从同步原理
事务提交之后，由主库记入binlog，并同步给从库，进入从库的relaylog，从库从relaylog中读取binlog，执行sql；
binlog 三种模式，statement rows mixed
replace和insert
replace语句在执行过程中会尝试insert，而insert过程首先会检查主键或唯一索引约束，insert语句的insert过程一旦检测到冲突会直接抛出异常，replace语句的insert过程检测到冲突会选择进行delete或直接update操作；
repace语句在执行insert过程中如果检测到主键冲突，则会删除冲突数据然后进行插入操作；如果检测到唯一约束冲突，则直接进行update操作；
Mysql对replace和insert语句有优化，多条insert操作会合并成insert into table values(),(),()…… binlog中只有一条；多条replace操作会合并成replace into table values(),(),()…… binlog中只有一条；洗数据时尽量使用这两种语法，可以有效降低主从延迟
Mysql中如何实现分页
limit offset,size 语法，会有深度分页问题，越来越慢；推荐使用 >pxId limit 1000的写法，会利用主键索引
count(*) count(1) count(col)区别
count() 和count(1) 都是统计行数，而count(col) 是统计col列非null的行数
官方文当说count() 和count(1)的优化是一样的
count(col)慢是因为 要从索引树中拿出来所有的该列判断是否为空，没有索引更惨，要全表扫描
反正用count(*)就对了
网友评论