mysql数据结构对比sql_# 技术笔记：mysql（索引、sql、数据结构...）

一# Mysql

Mysql关系型数据库(数据结构是表单的形式)

非关系型数据库实际上是一种数据库结构化储存方法的集合

第一范式：每一列属性都是不可在分割的值 确保每一列的原子性

第二范式：每一行的数据只能和其中一列有关

第三范式：数据之间不能有传递 每个属性都和主键有直接关系

储存过程(最终以文件形式储存到硬盘) sql调优 事务 读写分离(配置多台mysql config中设定主从 Mycat中间件)

事务特性ACID：

原子性(要么sql全部完成要么全部失败)

隔离性(并发环境下不同事务操作相同数据 事务有各自的空间)

持久性(事务成功结束 数据持久到数据库)

一致性(事务不会破坏数据的完整性和逻辑)

事务隔离：

DEFAULT default(spring默认事务隔离)

未提交读(read uncommited) :脏读，不可重复读，虚读都有可能发生

已提交读 (read commited):避免脏读。但是不可重复读和虚读有可能发生

可重复读 (repeatable read) :避免脏读和不可重复读.但是虚读有可能发生(mysql默认事务隔离)

串行化 (serializable) :避免以上所有读问题 会影响效率 但是更加安全

脏读：一个事务读到另一个事务未提交的数据

不可重复读：一个事务2次读取同一行数据 会有不同的结果

幻读：一个事务执行两次查询 结果不一致

二# 索引(本质是排好顺的数据结构)

聚集索引：叶子节点包含了完整的数据记录，索引和数据放在同一个文件

非聚集索引：索引和数据不在同一个文件，要根据索引下的磁盘文件地址，去另外的数据文件里查数据

联合索引：多个字段，最左前缀原理，从左到右依次查找

(select语句前加上explain 可以查看是否生效)

索引失效：

(1)索引本身失效

(2)查询到数据量大于表数据的30%

(3)索引上使用函数

(4)使用like%通配符 进行模糊查询会使索引失效

(5)(！= 或者<>)不能使用索引

(6)使用 is null 或者 is not null 也不能使用索引

(7)sql中用or来连接 索引也会失效

三#数据结构

Data structure:Data Structure Visualization​www.cs.usfca.edu

二叉树 (K,V 索引字段,数据磁盘位置 左小右大)

红黑树 (根节点是黑色 单边增长 自动平衡)

hash表

B-tree

1.叶节点具有相同的深度，叶节点的指针为空

2.所有索引元素不重复

3.节点中的数据索引从左到右递增排列

红黑二叉树

1.根节点是黑色 单边增长 自动平衡

2.磁盘I/O

hash表

根据hash散列值来定位索引磁盘位置

B+tree(多插平衡树)

1.非叶子节点不存储data，只存索引(处于中间位置的索引作为冗余索引，用来组成B+tree的结构)

2.叶子节点(没有子节点的节点)包含所有的索引字段

3.叶子节点用双向指针连接(存储相邻节点的磁盘文件位置)，提高区间访问性能

4.下层小于右边的父节点

5.查找时 从根节点出发，把节点rod到内存中做比对(磁盘IO)，再到对应的ibd 文件查到具体数据

6.节点大小16384 16kb (int4个字节 bigint8个字节 指针+索引 一个节点能放1170个索引)

7.非主键索引，叶子节点只存储主键，查到主键再做一次主键索引，为了保持一致性和节省存储空间

存储引擎(innoDB)是表级别的(形容数据表)

表数据文件本身就是按B+tree组织的一个索引结构文件

MVCC多版本并发控制

四# 数据库备份

1.当主库发生修改 会将修改当sql语句记录到二进制日志文件中

2.从库利用IO线程 读取主库的二进制文件信息 将数据写入中继日志中

3.从库读取中继日志 运行sql语句 将数据同步到从库

远程操控数据库 需要关闭Linux防火墙 开启mysql对外访问权限

数据库读写分离：

双主模式 是实现高可用的前提

主机中安装Amoneba或者mycat 代理服务器

数据库分库分表：

垂直业务拆分(没有直接关联的用户和商品)

水平一张表分为多张表

config中配置node节点 也就是逻辑库

入库根据ID%2=？求模的方式进行入库

热点数据加缓存或者索引

分布式锁：

数据库锁、redis锁、zookepper锁

实现了lock接口是synchronized(this)的加强版

在循环或者方法上加锁(并发条件下不加锁的话会多卖或者重卖)trylock 尝试加锁

Mysql大概存800w数据量

五# 数据库优化

1.优化sql语句

尽可能根据主键查询

用具体字段替代select *

尽量避免在where字句中对字段进行函数操作

如果做多表查询时 尽可能提前确定数据 减少笛卡尔积

3.建立数据库索引和索引优化(where子句中经常使用的列 最好设置索引)

4.利用redis提高查询的效率

5.如果用户查询依然很慢,则建议分库分表(主要将核心业务进行分库分表)

问题：

1.mysql为什么不用红黑数和hash表：

对于海量数据查找的节点太多了还是太慢了，树的高度不可控，不支持范围查找

2.聚集索引和非聚集索引的区别：

聚集索引叶子节点包含了完整的数据记录，索引和数据放在统一个文件里，根据索引查到叶子节点直接就能拿到数据，

非聚集索引查到叶子节点之后，只能得到数据的磁盘地址，根据具地址去另外的文件里得到数据

3.为什么innoDB必须要主键，且整型自增主键

如果没有主键，mysql也会找一列或者创建隐藏列来确保唯一性，索引用整型的主键比较效率高

4最左前缀原理是什么：

按照索引的从左到右依次查找，如果sql没有从最左的字段查，会在数据结构排序失效，从而导致索引失效

联合索引的B+tree

5.为什么非主键索引结构叶子节点存储的是主键值

为了保证一致性和节省存储空间

6.B-tree和B+tree的区别

主要的两个区别

1.只有叶子节点有数据，减少树的高度

2.叶子节点之间有双向指针，支持范围查找

参考：