从上到下,连接层、服务层、引擎层、存储层
最上层是一些客户端和连接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限
第二层架构主要完成大多数的核心服务功能,如SSL接口,并完成缓存的查询,SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如过程、函数等。在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化如确定查询表的顺序,是否利用索引等,最后生成相应的执行操作。如果是select语句,服务器还会查询内部的缓存。如果缓存空间足够大,这样在解决大量读取操作的环境中能够很好的提升系统性能。
存储引擎层,存储引擎真正的负责了Mysql中数据的存储和提取,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们根据自己的实际需求进行选取。
数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互
mysql调优:
性能监控:
mysql提供了基础的性能监控,show profile(查看数据库每一部分消耗的时间)
比如一共执行5条SQL语句,再执行show profile,此时显示的是最后一条的整体执行时间;执行show profiles可以查看5条SQL的整体耗时;执行show profile query 2,可以查看query_id是2的这条SQL语句的耗时
show profile + 关键字 //可以查看其他的相关信息 比如: show profile all for query id:显示所有信息 show profile block io for query id:显示快io操作的次数 show profile contextswitches for query id:显示上下文切换次数,被动和主动 show profile cpu for query id:显示用户cpu时间、系统cpu时间 show profile ipc for query id:显示发送和接受的消息数量 show profile page faults for query id:显示页错误数量 show profile source foe query id:显示源码中的函数名称与位置 show profile swaps for query id:显示swap的次数
select * 最根本的问题就是IO
提供了更加完善的性能监控,在未来将要取代show profile
通过show database; 可以看到Performance Schema模块(默认开启,也可关闭)
可以在show profile监控的基础上添加对事件的监控
详细可以查看官网
1.更小的通常更好
能正确存储的最小数据类型,优点是占用磁盘、内存和CPU都少
2.简单就好
3.尽量避免使用null
在数据库中null不等于null
对游湖和索引和值的比较都很复杂
对比项 | MyISAM | InnoDB |
---|---|---|
主外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行级锁 | 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作 | 行锁,操作时只锁一行,不对其他行有影响,适合高并发的操作 |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引还要缓存真是数据,对内存要求较高,而且内存大小对性能由决定性的影响 |
表空间 | 小 | 大 |
关注点 | 性能 | 事务 |
默认安装 | 是 | 是 |
如果要设计一个文件索引,可行的设计方式是:
但是这样的设计方式最大的一个特点就是,当文件量大的时候,效率低
OLAP:联机分析处理
OLTP:联机事务处理
此处只讲解B+树,其他的一些数据结构自行百度
mysql中使用到的数据结构:
举例,查看数据库中不同的模块的文件组织形式有两种
第一种InnoDB:
第二种MyISAM:
二叉树:两个枝杈,并且根节点的值大于左子树,小于右子树
缺点:如遇到极端境况会退化成一条链表,效率不高
AVL树:最高子树和最低子树差值为一,进行旋转保持平衡,查找效率高,插入效率低
红黑树:最高子树不超过最低子树的2倍即可,降低旋转次数,提高插入性能,不足之处就是树的深度
B树
B+树
局部性原理
数据的访问有聚集成群的倾向,在一段时间内,仅使用其中的一小部分(空间局部性);或者最近访问过的数据很快又被访问的可能性很大(时间局部性)
磁盘预读的单位是页
磁盘预读(预读的长度一般是页的整数倍)
页的大小通常是4K,与操作系统有关
简单对比各种数据结构
hash表的索引格式是链式
缺点:
二叉树和红黑树:
缺点:
无论是二叉树还是红黑树,都会因为树的深度过深而造成IO次数变多,影响数据读取的效率
B树结构:
MySQL InnoDB B+树,叶子节点直接放置整条数据
注意:
回表:就是上述情况,比如主键是id,索引列是name,这样查找name=‘AAA’,就会是先查找name,找到后根据name对应的id,再到另一个索引树中找到id对应的完整的一条数据
聚簇索引:数据和索引放在同一个文件中(比如:InnoDB)
非聚簇索引:数据和索引分开放在两个文件中(比如:MyISAM)
分布式程序不建议主键自增,会引发页分裂和页合并的问题
普通单机程序建议使用主键自增
比如主键是id,并且给name添加了索引,此时数据库会创建两个B+Tree,如果执行select * from table where name = ?,会先在name和id的树中找到id,再用id在另一个树中找到整条数据,这就是回表
上述情况中,如果查询select id from table where name = ?,直接查询一个树就能得到结果,不用回表就叫索引覆盖
select * from table where name = ?and age = ?
这条语句执行的话,会从磁盘先查出符合name = ?的所有数据,加入到mysql server中,再根据age进行筛选
有了索引下推后,会根据name和age来拉取数据,不用在server层做数据的筛选
组合索引,比如name+age,一定是先匹配name再匹配age
举例:
上述例子中1,2,4会使用索引,4会经过优化成为1
CBO:基于成本的优化
RBO:基于效率的优化
索引下推唯一的缺点是需要在磁盘上多做数据筛选,原来的筛选是放在内存中的,现在放到了磁盘查找数据,并且所有的数据是聚集存放,所以性能不会有影响,而且整体的IO量会大大减少,反而会提升性能
MRR:
全称是mult_range read
在内存中做排序
FIC
全称是fast index create
没有FIC时索引的修改过程:
有了FIC后的索引修改过程:
FIC给当前表添加一个share锁,不会有创建临时文件的资源消耗,还是在源文件中,但是此时如果有人发起DML操作,很明显数据完全不一致,所以添加share锁,读取没问题,但是DML会有问题
事务的原则
原子性
一致性
隔离性
持久性
在此级别,事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称为能读,实际中很少使用。
大多数数据库系统默认的隔离级别是提交读。此级别满足前面提到的隔离性的简单定义,也就是说,一个事务从开始知道提交之前,所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读,因为两次执行同样的查询,可能会得到不一样的结果,这就是脏读。
此级别解决了脏读问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上,可重复读隔离级别还是无法解决另外一个幻读问题。所谓的幻读,指的是当某个事务在取某个范围的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围记录时,会产生幻行。InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC Mutiversion Concurrency Control)解决了幻读问题。
最高的隔离级别。它通过强制事务串行执行,避免了前面说的幻读问题。简单来说,串行化会在读取的每一行数据都加锁,所以它可以造成大量的超时和锁争用的问题。实际应用中很少使用这个隔离级别,只有在非常需要保持一致性,但是可以接受没有并发的情况下,才考虑采用该级别。
总结:MySQL的事务默认级别为可重复读,可以通过配置文件修改
隔离级别由低到高
隔离级别越低,效率越高,安全性越低
隔离级别越高,效率越低,安全性越高
脏读、幻读概念
隔离级别的底层实现
在企业网站中,后端mysql数据库只有一台时,会出现单点故障,服务不可用,无法处理大量的并发请求甚至数据丢失等等大问题
应对上述问题,增加mysql数据库服务器,对数据进行备份,通过主从复制的方式来同步数据,在通过读写分离来提升数据库的并发负载能力
基于语句的复制(默认):
在服务器上执行语句,从服务器执行同样的语句
基于行的复制:
把改变的内容复制到从服务器
混合类型的复制:
一旦发现基于语句无法精确复制时,就会采用基于行的复制
log-bin=mysql-bin 开启二进制日志
二进制日志必须开启,因为数据的同步实质上就是其他的MySQL数据库服务器将这个数据变更的二进制日志在本机上再执行一遍
详细步骤:
在分机上执行:
mysql>CHANGE MASTER TO
>MASTER_HOST=’192.168.95.11’,
>MASTER_USER=’mysql12’,
>MASTER_PASSWORD=’mysql12’,
>MASTER_LOG_FILE=’mysql-bin.000048’,
>MASTER_LOG_POS=432;
设置主机的相关信息
参考:https://www.cnblogs.com/phpstudy2015-6/p/6485819.html#_label2
读写分离就是只在主服务器上写,只在从服务器上读
主数据库处理事务性查询,而从数据库处理select查询
数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库
因为数据库的写操作比较耗时,写1万条数据到oracle可能要3分钟
但是数据库读取1万数据可能只要5秒
所以读写分离,解决的是,数据库的写入,影响了查询的效率
读写分离的基础是主从复制
MySQL Cluster 是MySQL官方出品的分布式数据库解决方案,使用的数据库引擎为NDB,跟单机下的MyISAM和Innodb引擎有所不同,操作界面之一就是MySQL,此外提供原生API,可以节省资源并加快执行速度。该方案比业界其他MySQL集群方案在数据量大时有更大优势,开发者使用上跟单库操作几乎无差异,原先使用MySQL的话几乎可以无缝迁移,就可以享受集群带来的力量。当然也有个明显的缺点:内存开销非常大,如果要选择该方案,需要足够的硬件内存资源。下面我们详细地讲述MySQL Cluster 的部署使用
参考:https://blog.51cto.com/bangbangba/1710062