Mysql

Mysql

逻辑架构

1. 连接层
   • 连接客户端:java的程序,以及一些可视化工具
   • 在这一层进行授权以及连接
2. 服务层
   1. 查询缓存
   2. sql解析器
   3. 预编译器
   4. 优化器
3. 引擎层
4. 存储层(系统磁盘)

工作流程

1.建立连接,账号授权(连接层)--->{2.查询缓存-->解析器-->预编译-->优化器}-->查询引擎-->存储层

• 在连接层进行授权
• 开始查询的第一步就是检查有没有命中查询缓存
  • 命中查询缓存直接返回结果
  • 没有继续解析优化
• 解析器将sql转化为机器码生成对应的sql执行树
• 预处理器:在预处理器对权限进行验证-->验证表名列名以及权限
• 优化器:选择较优的执行路径

sql查询缓存命中的条件

• 开启了查询缓存
• 该查询sql是第二次或以上执行
• 两次sql之间不存在增删改的操作

查询引擎

InnoDB:从Mysql5.5版本以后就是默认的查询引擎

MyISAM:5.5版本之前默认的查询引擎

二者之间的对比:

外键,事务,行锁,聚簇索引,macc(数据一致性)--->InnoDB都支持,MyISAM不支持

表锁都支持

[这里我们可以说：主键索引其实就是聚簇索引，这两个名字是从不同的角度来说的。
——> 主键索引：是指这个索引是根据主键字段来创建的
——> 聚簇索引：是指这个索引的树形结构中是否同时包含了『原始数据』和『索引目录』]

索引

概念

索引是帮助mysql高效获取数据的一种数据结构(B+Tree)

关于Tree这种数据结构

二叉树:左子树的键值小于根的键值，右子树的键值大于根的键值。

当正常时二叉树的特点是左小右大,当数据比较分散时查询可以提升效率但当出现极端数据时就会出现问题

若数据出现大小顺序排列时,查找一个数字可能还是会查询所有的数据

平衡二叉树

平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1，就达到了所谓的平衡。需要通过一系列复杂的旋转达到平衡。

B-Tree（平衡多叉查找树）

上图中的每走一次箭头，都意味着一次磁盘IO，意味着磁臂去寻址，很慢(在磁盘中读取数据是较慢的)。

每一个节点，意味着一个页空间，一个页空间默认大小为16KB，可设置。

B-Tree就是为了磁盘等外设存储设备的机理设计的一种平衡查找树，利用了磁盘块空间，把磁盘块空间充分利用，多存储几个键值、指针，通过这样的方式，可以减少树的深度，也就意味着减少磁盘的随机IO次数，加快访问速度。

• BTree 的体型：尖锐、高瘦
  • BTree 每个节点都存原始数据，都是数据节点，没有目录节点。
  • 每个节点的默认大小 16 KB，存了数据，能够用来存『主键 + 页码』的空间就不够了
  • 每个节点容纳子节点的数量就很少，导致深度增加
  • 对于查询来说：深度增加 1 层，I/O 次数增加一次
  • 所以性能方面 BTree 不如 B+Tree

B+Tree结构

与B-Tree相比B+Tree只在叶子节点存储数据，能存放更多的数据，减少IO次数；

并且在叶子节点存在相互的指向

索引的创建方式

在创建表的时候创建

CREATE TABLE customer (
	id INT (10) UNSIGNED AUTO_INCREMENT,
	customer_no VARCHAR (200),
	customer_name VARCHAR (200),
	PRIMARY KEY (id),       # 主键索引
	KEY (customer_name),    # 单值索引 ,可自己指定索引名
	UNIQUE (customer_name), # 唯一索引,可自己指定索引名
	KEY (customer_no, customer_name) # 复合索引/组合索引,可自己指定索引名
);

表创建好之后添加索引

索引类型	索引特性
单列索引	一个字段就是一个索引,表中可以有多个单列索引
联合索引	多个字段组成一个索引
唯一索引	索引字段必须唯一,不能重复
主键索引	当设置主键字段之后就会自动生成

索引的失效情况:

• 过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
• 计算或者函数会导致索引失效
• 范围条件右边的索引失效
• 使用否定关键字---->!= <> is not null
• 进行模糊查询时以"%"通配符开头
• 类型转换也会导致索引失效--->设计实体类属性时，一定要与数据库字段类型相对应，否则会出现类型转换的情况。

以下情况不要创建索引

• MySQL 中，一张数据库表中记录数量小于 300 万条时，即使创建索引也不会让搜索速度有明显提升。
• 经常增删改的表，建立索引提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件，重新排布索引，这个操作需要对索引表做全表扫描。不仅索引表的全表扫描操作本身非常耗时，而且索引重新排布时不可用，此时执行查询操作没有索引可用，还是要回到原始数据库表做全表扫描。
• where 条件、排序、分组都用不到的字段
• 数据重复，过滤性不好的字段
• 无法排序的字段
• 不要创建冗余或重复的索引

sql优化

• 驱动表选择较小的表
• 保证被驱动表的join字段是有索引的
• 尽量使用外连接而不要使用子查询

覆盖索引

查询所需要的结果在一颗索引树上就能全部获得

如何实现索引覆盖？

常见的方法是：将被查询的字段，建立到联合索引里去.

回表

聚簇索引:

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引

• 如果表定义了主键，则Primary Key 就是聚集索引；
• 如果表没有定义主键，则第一个非空唯一索引（Not NULL Unique）列是聚集索引；
• 否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

普通索引

InnoDB普通索引的叶子节点存储主键值

（1）先通过普通索引定位到主键值id=5；

（2）在通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。