数据库性能测试-mysql篇

一、数据库主从同步的工作原理

主从复制原理：
上边这张交互图就清楚的标记出了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、主从延时

show slave status

四、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、keys MYSQL使用的索引，简单且重要

7、key_len MYSQL使用的索引长度

8、ref   ref列显示使用哪个列或常数与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 https://github.com/cnlubo/orzdba`即可，执行`tree orzdba/`查看目录下文件，最主要的就是orzdba.py这个脚本了。

八、mysql集群监控工具介绍

天兔LEPUS

参考的博客：

1、数据库主从同步原理解析_码厚炮的博客-CSDN博客_数据库主从同步原理

2、一文搞懂 MySQL 索引_daydreamed的博客-CSDN博客_mysql索引

3、MYSQL explain详解_Venlenter的博客-CSDN博客_mysql explain