mysql unique实现原理_MySQL 原理

mysql的三层架构

连接层：管理连接，认证鉴权

服务层：sql语句处理，调用接口

存储引擎层：存储数据和索引

mysql查询流程

1. 客户端select查询

2. mysql服务端：查询缓存

3. 解析器parser(词法分析，语法分析)

4. 预处理器 pre processor (语义分析)

5. 优化器 optimizer(执行路径成本计算，提供执行方案)

6. 执行计划(确定最优执行计划)(explain (format=json))set optimizer_track='enabled=on'

7. 执行器(按执行计划执行具体命令，调用存储引擎接口)

8. 进入插件式存储引擎，存储数据(数据不同的文件存储形式)

mysql更新过程

1. 事务开始，从内存(buffer pool)或磁盘(data file)中读取包含这条数据的数据页返回给server执行器

2. server的执行器修改数据页这一行数据为newValue

3. 记录oldValue到undo log

4. 记录newValue到rodo log

5. 调用存储引擎接口，记录数据页到buffer pool，修改目标为newValue

6. 事物提交

Mysql的存储引擎InnoDB使用的索引数据结构加强版多路平衡二叉树(B+ Tree)

特点：

只在叶子结点存储数据(在根节点/枝节点存储索引数据

叶子结点相互引用形成有序链表结构

关键字数=度数=路数

B+树的深度一般在1-3层可以满足千万级数据存储

优势：

扫库，扫表能力更强

磁盘深度更低，减少io读写

原因：一个page节点可以存储的关键字越多，一次io加载的索引越多，树的深度越低

排序能力更强

效率更加稳定

2. Hash索引

时间复杂度为O(1)

不适合范围查找、比较查找，适合等值查找(=、in)

根据索引值进行hash运算，映射一条记录存放地址

如果字段重复，存在Hash碰撞

索引的分类

普通索引：normal index

唯一索引：unique index唯一，(只有一条记录可以为空)

主键索引：primary index唯一，不为空

全文索引：full text index 全文匹配(检索搜索)用es更好

联合索引/复合索引：一次对多个字段加索引

聚集索引/聚簇索引：索引键值的逻辑顺序与表数据行的物理存储顺序是一致的

非聚集索引：二级索引

二级索引：二级索引的叶子结点是存的聚集索引的键值

索引使用原则与建议

索引的个数不要过多，占空间大

在离散度大列上建索引【列离散度越公式：select count(distinct(列名))/count(*) from 表名】

在用于where判断、order排序、join的on、group by字段上创建索引

联合索引最左匹配原则

不建议用无序的值作为索引(uuid，身份证)

复合索引把散列度高的值放在前面

尽量创建复合索引，而不是增加单列索引

过长的字段，建立前缀索引

方式1: key `pre_idx` (`字段`(6))

方式2: alter table 表名 add key(字段(5));

索引覆盖：

回表：先二级索引查询树下先找到主键索引的键值，再扫描主键索引树

如果查询只用到了索引列，那么不再扫描主键索引树，避免了回表，加快了查找

索引条件下推

只适用于二级索引，把过滤的过程放在了存储引擎层，不需要用到server层过滤

什么情况用不到索引

索引列上使用函数(replace\substra\concat\sum count avg)

索引列上使用 + - * /

字符串不加引号，出现隐式转换

like条件中带%

负向查询：not like 不能用到索引

!= (<>)和NOT IN在某些情况下可以

文件格式说明

.frm文件

任何一个表都有的文件

表结构元数据的定义的文件

innodb

索引与数据一起存放在.ibd文件

myisam

数据文件：.MYD

索引文件：.MYI

锁与事物的关系

锁是读一致性的解决方案

读一致性能够实现事务隔离

事务隔离就能够提供了事务隔离级别

事务隔离级别是事务的一大特性

锁的分类

共享锁/读锁/S锁：(行锁) shared locks

多个事务可共享一把锁，去只读数据

排他锁/写锁/X锁：(行锁)exclusive locks

不能与其他锁并存，只有获取了该锁的事务才能过进行读写

意向共享锁/读锁：(表锁)：intention shared locks

意向排他性锁/写锁：(表锁)：intention exclusive locks

(意向锁：是否能加锁的标志)

行锁与表锁的对比

innodb支持行锁

myisam不支持行锁

锁定粒度：表锁>行锁

加锁效率：表锁>行锁(需要找到行记录)

冲突概率：表锁>行锁

并发性能：表锁

保证一致性的方案

1. 加锁

2. 多版本并发控制(MVCC)

让普通的查询不需要加锁通过mvcc判断规则可以实现读一致性