一、数据库主从同步的工作原理
主从复制原理:
上边这张交互图就清楚的标记出了Master节点如何同步到Slave节点
1、首先Master上的修改、删除、新增操作都会被记录到一个叫做binlog的文件中,它是一个二进制日志文件。
2、Slave通过I/O线程读取binlog文件并写入到自己的relaylog(中继日志)中。
3、主库会生成一个 log dump 线程,用来给从库 i/o线程传binlog。
4、Slave另一个SQL线程会读取自身的relaylog文件,并把对应的操作在自己的节点上执行一次,达到同步的效果。
二、数据库分库分表的设计方法
1、拆分的原因
a.单表或库的数据量太大
b.硬件不能升级或无法升级
2、分库分表方案
a.业务拆分:用户、订单、商品可以单独放在不同的表、不同库。
b.垂直拆分:商品表中的不同发类的商品比如:电子产品和体育用品,分在不同的表或者库。
c.水平拆分:一致性哈希算法。
三、mysql重点监控指标
1、QPS
每秒钟查询数量
show global status like 'Question%';
结果=查询的数量/查询的时间 =9033/0.03
2、TPS
其中
Com_commit=show global status like 'Com_commit';
Com_rollback=show global status like 'Com_rollback';
3、线程连接数
a.使用最大的连接数:
show global status like 'MAX_USER_CONNECTIONS';
b.线程数:
show global status like 'Threads%';
4、Query Cache
查询缓存用于缓存select查询结果,就是下次接收到查询请求时,不再执行实际查询处理而直接返回结果,适用于大量查询且很少改变表中的数据。
show status like 'Qcache%';
结果:
通过下面这个公式计算:
5、锁定状态
show status like '%lock%';结果如下:
6、主从延时
showslavestatus
四、mysql的慢查询
执行速度超过定义的时间的查询
参数:
常用的命令:
1、
2、
3、
五、sql语句的性能分析
explain select * from user;结果如下:
返回值中的属性的解释
1、id
2、select_type
3、table
4、type(重要)
连接类型。有多个参数,先从最佳类型到最差类型介绍重要且困难
system
表仅有一行,这是const类型的特列,平时不会出现,这个也可以忽略不计
const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,
index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)
all:这个是全表扫描数据文件,最差的一种。
5、possible_keys提示使用哪个索引会在该表中找到行,不太重要
6、keysMYSQL使用的索引,简单且重要
7、key_lenMYSQL使用的索引长度
8、refref列显示使用哪个列或常数与key一起从表中选择行。
9、rows显示MYSQL执行查询的行数,简单且重要,数值越大越不好,说明没有用好索引
10、Extra该列包含MySQL解决查询的详细信息。
六、mysql的索引
1.1、MySQL索引是什么?
索引是一个单独的、存储在磁盘上的数据库结构 ,包含着对数据表里所有记录的引用指针。
1.2、 MySQL索引的存储类型有哪些?
MySQL中索引的存储类型有两种,即 BTree 和 Hash。
1.3、MySQL索引在哪里实现的?
索引是在存储引擎中实现的。(MySQL 的存储引擎有:InnoDB、MyISAM、Memory、Heap)
InnoDB / MyISAM 只支持 BTree 索引
Memory / Heap 都支持 BTree 和 Hash 索引
其中MyISAM的优点:1、读的性能比InnoDB高;2、索引与数据分开,使用了压缩,从而提高了内存的使用率。
缺点:1、不支持事务;2、写入数据时,直接锁表。
其中InnoDB的优点:1、支持事务;2、支持外键;3、支持行级锁;
缺点:1、不支持fulltext索引(全文索引);2、行级锁并不绝对,当不确定扫描范围时,锁全表;3、索引与数据是紧密捆绑的,没有使用压缩导致体积庞大、
1.4、存储引擎是什么?
存储引擎就是指表的类型以及表在计算机上的存储方式。
1.5、索引的优缺点有哪些?
优点:
提高数据的查询的效率(类似于书的目录)
可以保证数据库表中每一行数据的唯一性(唯一索引)
减少分组和排序的时间(使用分组和排序子句进行数据查询)
被索引的列会自动进行分组和排序
缺点:占用磁盘空间
降低更新表的效率(不仅要更新表中的数据,还要更新相对应的索引文件)
1.6、索引的分类:
其中有主键索引、唯一索引、普通索引、全文索引、组合索引
1、主键索引:它是一种特殊的唯一索引,不允许有空值。一般在建表的时候同时创建主键索引。
2、唯一索引:索引的列的值必须唯一,但允许有空值。
3、普通索引:最基本的索引,它没有任何限制。
4、全文索引:类型为fulltext,只适用于MyISAM表的一个索引类型。被索引列的数据类型只能是以下三种的组合char、varchar、text
5、组合索引:也叫多列索引,在多列上同时创建索引,使得多列的组合值唯一。
七、mysql数据库的实时监控
orzdba
Orzdba是淘宝DBA团队使用perl语言开发出来的一个脚本,可以监测系统,cpu,磁盘,innodb引擎内部实时信息的运维自动化工具,非常实用。github上有大佬用python改写了,直接clone下来即可使用。 linux下直接执行`git clone /cnlubo/orzdba`即可,执行`tree orzdba/`查看目录下文件,最主要的就是orzdba.py这个脚本了。
八、mysql集群监控工具介绍
天兔LEPUS
参考的博客:
1、数据库主从同步原理解析_码厚炮的博客-CSDN博客_数据库主从同步原理
2、一文搞懂 MySQL 索引_daydreamed的博客-CSDN博客_mysql索引
3、MYSQL explain详解_Venlenter的博客-CSDN博客_mysql explain
如果觉得《数据库性能测试-mysql篇》对你有帮助,请点赞、收藏,并留下你的观点哦!