Fk零基础学习MySql(基础篇2)

1.衡量指标

TPS: Transactions Per Second 指服务器每秒处理事务的个数，支持事务的存储引擎如InnoDB
等特有的一个性能衡量标准。

计算方式:（事务提交数com_commit + 事务回滚数com_rollback） / 服务器运行时间uptime


QPS:Queries Per Second 指服务器每秒查询量。同时适用于InnoDB和MyISAM引擎。

计算方式: 总查询次数questions / 服务器运行时间uptime 

2.MySqlSlap压力测试工具

MySqlSlap是从MySql5.1.4版本官方开始提供的压力测试工具。

在安装MySql5.1.4以后的版本时，MySqlSlap已经附属携带了，可以直接使用。


MySqlSlap常用参数：

--create-schema=name 指定测试的数据库名，默认是mysqlslap
 
--engine=name   创建测试表所使用的存储引擎，可指定多个
 
--concurrency=N 模拟N个客户端并发执行。可指定多个值，以逗号分隔

--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)，比如并发 是10，总次数是100
，那么10个客户端各执行10个 

--iterations=N 迭代执行的次数，即重复的次数（相同的测试进行N次，求一个平均值），指的是整个步骤
的重复次数，包括准备数据、测试load、清理
 
--commit=N 执行N条DML后提交一次

--auto-generate-sql，-a 自动生成测试表和数据，表示用mysqlslap工具自己生成的 SQL脚本来测试
并发压力。

--auto-generate-sql-load-type=name 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者
混合的。 取值包括：read (scan tables), write (insert into tables), key (read primary
 keys), update (update primary keys), or mixed (half inserts, half scanning selects). 
默认值是：mixed.

--auto-generate-sql-add-autoincrement 对生成的表自动添加auto_increment列
--number-char-cols=name  自动生成的测试表中包含N个字符类型的列，默认1
--number-int-cols=name 自动生成的测试表中包含N个数字类型的列，默认1 
--debug-info 打印内存和CPU的信息

编写一个简单案例测试一下：

--concurrency=500 500并发客户端
--iterations=1 迭代循环一次操作
--auto-generate-sql 自动生成测试表和数据
--auto-generate-sql-addauto-increment 对生成的表自动添加auto_increment列
--engine=InnoDB 使用数据库引擎
--number-of-queries=500 总查询量 


mysqlslap -uroot -padmin --concurrency=500 --iterations=1 --auto-generate-sql --auto-
generate-sql-add-autoincrement --engine=InnoDB --number-of-queries=500

3.MySql逻辑架构

第一层：连接层
第二层：服务层
第三层：引擎层
第四层：存储层

MySql服务是多用户的，每一个连接进来都会分配一个线程。

连接层

MySql缓存：缓存的是SQL的执行计划和SQL语句。默认不开启数据缓存（Buffers）

服务层


--查询Mysql版本是否支持数据缓存
show variables like  '%have_query_cache%'
--查询数据缓存是否开启
show variables like  '%query_cache_type%'
--查询数据缓存size大小
show variables like  '%query_cache_size%'

4.MySql存储引擎

查询MySql支持的存储引擎

show ENGINES

5.CSV存储引擎

CSV存储引擎：数据以文本方式存储在文中中。

.csv 文件存储内容
.csm 文件存储表的元数据。（表状态、数据量等） 
.frm 表结构

元数据被定义为：描述数据的数据，对数据及信息资源的描述性信息。


特点:
1.所有的列都不能为null
2.不支持索引
3.可以直接编辑.csv文件修改或新增内容

6.Archive存储引擎

.ARZ 数据存储在.ARZ文件中，以zlib形式对数据进行压缩
.frm 表结构

特点：
1.只支持insert和select操作
2.只允许在自增ID列上添加索引

使用场景：
日志和数据采集

7.Memory存储引擎

1.数据都是保存到内存中
2.支持HASH索引和BTree索引
3.不支持Blog和Text等大字段
4.Memory存储引擎使用表级锁
5.表中存储数据最大由max_heap_table_size参数决定

注意：

数据是存储在内存，重启MySql服务数据将会丢失。

9.MySql表锁

MySql的表级锁有两种模式

1.表共享读锁（Table Read Lock）
2.表独占写锁（Table Write Lock）

10.MySql表锁--表级共享读锁

MyISAM存储引擎支持表级锁，所以先创建两张表方便下文测试。

create table if not exists t_read01(
 id int comment "id",
 t_name varchar(10) comment "t_name"
)ENGINE=MyISAM default CHARACTER SET utf8mb4 comment "测试读锁read01";

create table if not exists t_read02(
 id int comment "id",
 t_name varchar(10) comment "t_name"
)ENGINE=MyISAM default CHARACTER SET utf8mb4 comment "测试读锁read02";

表共享读锁：

在某一个会话中给某张表添加表级读锁，其它会话也可进行查询。

-- 加锁
lock table 表名 read


-- 解锁
UNLOCK TABLES;

在已添加读锁的会话中，无法操作已添加读锁的表。在其它会话中，对已加读锁的表操作时，操作会处于等待
状态，当读锁解除后，其它会话等待的操作便会进行。

操作：insert、update、delete等

在加锁的会话中，无法操作其它表，其它会话不影响操作。

11.MySql表锁--表级独占写锁

表级独占写锁：

当前加锁的会话，可以对加锁的表进行读写操作，其它会话对表的读写操作会进入等待状态，表解锁
后其它会话的操作便可执行。


添加独占写锁:

lock table 表名 write


-- 解锁
UNLOCK TABLES;

12.InnoDb行锁

读锁：当一个事务对某几行添加读锁时，其它事务对这几行可以进行读操作，但不允许进行写操作。
同时其它事务无法对这几行数据添加写锁，但允许添加读锁。


写锁：当一个事务对某几行添加写锁时，不允许其它事务对这几行进行写操作，允许进行读操作。
不允许其它事务对这几行数据添加读锁或者写锁。

13.MySql事务隔离级别

事务：

一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务。

事务必须满足4个条件（ACID）：
 
原子性（Atomicity，或称不可分割性）
一致性（Consistency）
隔离性（Isolation，又称独立性）
持久性（Durability）。
 
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环
节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行
过一样。
 
一致性：在事务执行完毕后，数据的变化符合我们预设的规则，而达到一个预期的结果。
 
隔离性：指一个事务在执行操作的时候，不被其他事务干扰。就是一个事务内部操作的数据对并发的其它事
务是隔离的，并发执行的各个事务之间相互不干扰。
 
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

脏读：事务A读取了事务B修改未提交的数据，然后事务B进行回滚，此刻事务A读取就是脏数据。

不可重复读：事务A先读取了一个字段的值，在事务A还没结束的时候，事务B修改了这个字段的值并commit
提交了事务。然后事务A重新读取了这个字段的值，此刻读取的值与之前读取的值不相同，这就是不可重复
读。

幻读：事务A先读取了一个表中的所有记录，在事务A还没结束的时候，事务B给这张表新增了几条记录并commit
提交了事务。然后事务A重新读取了表中的所有记录，发现多出了几条记录，疑似出现幻读。


MySql默认的事务隔离级别：可重复读

1.读未提交：允许事务读取其它事务修改未提交的数据。会出现 脏读、不可重复读、换读。

2.读已提交：允许事务读取其它事务修改并提交的数据。可以避免脏读出现，但 不可重复读、幻读还会出现。

3.可重复读：确保事务在操作某些数据期间，该数据相对其它事务具有隔离性，其它事务无法操作这些数据。
可以避免脏读、不可重复读，但是幻读还会出现。

4.串行化：所有并发问题都能解决、性能低下不建议考虑。

注意：串行化隔离下 读写操作都会给整张表加锁。

14.慢查询分析工具【pt_query_digest】

教程链接

https://www.cnblogs.com/luyucheng/p/6265873.html

15.MySql主从复制

主库会将DDL、DML等操作当作一个events事件,存进bin-log二进制文件中，会通过Master IO推送
到从库上，从库会开启一个IO线程，接收主库推送过来的bin-log二进制文件，然后写到Relays bin-log二进制
文件，然后会创建一个SQL Thread线程读取Relays bin-log中内容，在从库中执行，完成后会将执行内容从
Relays bin-log中删除。


二进制日志文件Binlog有三种格式：
Statement:基于 SQL语句级别的 Binlog,每条修改数据的SQL都会保存到Binlog里。

Row:基于行级别,记录每一行数据的变化,也就是将每行数据的变化都记录到 Binlog 里面, 记录得非常详细,
但是并不记录原始SQL; 在复制的时候, 并不会因为存储过程或触发器造成主从库数据不一致的问通, 但是
记录的日志量较 Statement格式要大得多 。

Mixed:混合Statement和Row模式,默认情况下采用 Statement模式记录,某些情况下会切换到 Row模式。

在 binlog_format设置为 Row格式时, MySQL实际上在 Binlog中逐行记录数据的变更, Row格式比 Statement
格式更能保证从库数据的一致性(复制的是记录,而不是单纯操作 SQL)。当然, Row格式下的 Binlog的日志量很
可能会增大非常多,在设置时需要考虑到磁盘空间间题。



查看当前复制方式
show variables like '%binlog%format%';
更改复制方式
set global binlog_format = 'ROW'; 
set global binlog_format = 'STATEMENT';



注意：
如果从库宕机了然后再恢复了，如何再去执行主库的复制呢？

从库会存在两个文件master.info、relay_log.info这两文件记录的当前对主库复制执行的位置，具体执行到
哪里了。

16.Linux安装MySql

1.
检查系统是否有自带的mysql
rpm -qa|grep mysql
如果有进行强行卸载
rpm -e --nodeps mysql-libs-5.1.52-1.el6_0.1.x86_64
检测系统是否自带mariadb
rpm -qa|grep mariadb
rpm -e --nodeps mariadb-libs-5.5.64-1.el7.x86_64

2.
Mysql 编译安装需要使用perl，先把这些编译工具安装好
yum -y install perl
yum -y install perl-devel
yum -y install 'perl(Data::Dumper)'
yum -y install  libaio-devel.x86_64
yum -y install numactl

下载MySql.tar.gz,上传到服务器
https://downloads.mysql.com/archives/community/
解压
tar -zxvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz -C /usr/local

3.
创建data文件,用来存放数据
mv mysql-5.7.27-linux-glibc2.12-x86_64/ mysql3306
mkdir /usr/local/mysql3306/data

4.
在support-files目录中创建my_default.cnf

[mysqld]

#设置mysql的安装目录
basedir = /usr/local/mysql3306
#设置mysql数据库的数据存放目录
datadir = /usr/local/mysql3306/data
#设置端口
port = 3306

socket = /tmp/mysql.sock
#设置字符集
character-set-server=utf8
#日志存放目录
log-error = /usr/local/mysql3306/data/mysqld.log
pid-file = /usr/local/mysql3306/data/mysqld.pid

 

5.

复制my_default.cnf 到/etc/my.cnf

cp my_default.cnf /etc/my.cnf


编译安装并初始化mysql
cd /usr/local/mysql3306/bin
./mysqld --initialize --user=root --datadir=/usr/local/mysql3306/data --basedir=/usr/local/mysql3306

 

6.

复制support-files文件中mysql.server启动脚本

cp mysql.server /etc/init.d/mysql3306

7
启动MySql(init.d/启动脚本的名字 上面复制的是mysql3306)
service mysql3306 start

修改环境变量
vi /etc/profile
export MYSQL_HOME_3306=/usr/local/mysql3306
export PATH=$PATH:$MYSQL_HOME_3306/bin
重新生效文件
source /etc/profile

进入mysql3306/bin目录下 复制一份mysql启动脚本为 mysql3306。（后面准备在安装3307 方便区分）

mysql3306 -uroot -p
输入上文日志中临时生成的密码

设置新密码(用临时生成的密码进入mysql，更新密码)
set password for root@localhost = password('新密码');
//如果报错，请看此文档
https://blog.csdn.net/hj7jay/article/details/65626766(转载)



开放远程连接
update user set user.Host='%' where user.User='root';
flush privileges;

 

如果启动失败，设置权限后还是无法启动，参考如下文档
https://blog.csdn.net/qevery678/article/details/96422599/

 

17.Linux安装MySql(单服务安装多实例)

要吐血了 真的要吐血了~~~

参考了一篇博客,各种坑 各种关键字写错 搞的我电脑都想砸了~~~~

环境：
CentOs7
mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz

实现：单服务上启动多个MySql实例.

1.解压文件到/usr/local/

tar -zxvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz  -C /usr/local

2.

进入mysql5.7.27目录创建data目录，在data目录中创建3306、3307

3.

初始化数据库，指定到这3306、3307个文件夹

cd /usr/local/mysql5.7.27/bin

./mysqld --initialize --user=root --datadir=/usr/local/mysql5.7.27/data/3306 --basedir=/usr/local/mysql5.7.27

./mysqld --initialize --user=root --datadir=/usr/local/mysql5.7.27/data/3307 --basedir=/usr/local/mysql5.7.27

4.
在3306 3307文件下创建各自的my.cnf


[client]

port=3306
socker=/usr/local/mysql5.7.27/data/3306/mysql.sock
default-character-set=utf-8

[mysqld]
port=3306
socket=/usr/local/mysql5.7.27/data/3306/mysql.sock
datadir=/usr/local/mysql5.7.27/data/3306
log-error=/usr/local/mysql5.7.27/data/3306/error.log
pid-file=/usr/local/mysql5.7.27/data/3306/mysql.pid

character-set-server=utf8
lower_case_table_names=1
autocommit=1



[client]

port=3307
socker=/usr/local/mysql5.7.27/data/3307/mysql.sock
default-character-set=utf-8

[mysqld]
port=3307
socket=/usr/local/mysql5.7.27/data/3307/mysql.sock
datadir=/usr/local/mysql5.7.27/data/3307
log-error=/usr/local/mysql5.7.27/data/3307/error.log
pid-file=/usr/local/mysql5.7.27/data/3307/mysql.pid

character-set-server=utf8
lower_case_table_names=1
autocommit=1

5.

将data目录修改权限所有者为Mysql 并设置权限为774

chown -R mysql:mysql data

chmod -R 774 data  

进入mysql5.7.27/bin启动

其中 --defaults-file是指定配置文件，&表示后台启动

./mysqld_safe --defaults-file=/usr/local/mysql5.7.27/data/3306/my.cnf &

./mysqld_safe --defaults-file=/usr/local/mysql5.7.27/data/3307/my.cnf &

设置一下环境变量，然后登录3306|3307

vim /etc/profile

export MYSQL_HOME=/usr/local/mysql5.7.27
export PATH=$PATH:$MYSQL_HOME/bin


重新生效 /etc/profile

source /etc/profile

18.MySql主从复制--异步复制

主库执行完Commit后，在主库写入Binlog日志后即可成功返回客户端，无需等Binlog日志传送给从库。

1.配置主从异步复制

上面文档已经创建了3306主 3307从两个MySql实例，关闭3306和3307实例，完成如下配置

进入data中3306创建log3306文件夹用来存放bin-log二进制文件,并且修改3306/my.cnf 3307/my.cnf

3306/my.cnf

server_id=1
log-bin=/usr/local/mysql5.7.27/data/3306/log3306/mysql-bin.log

---------------------------------------------------------------------

3307/my.cnf

添加server-id 区分实例id不能相同
server-id=2

2.启动3306 3307实例。

在3306中创建用户

GRANT REPLICATION SLAVE ON *.* To 'rep1'@'ip自己填' IDENTIFIED BY '密码';

FLUSH PRIVILEGES;


3.在主3306上查询二进制日志名和偏移量

show master status;

5.登录从机3307

执行同步语句

change master to master_host='ip', master_user='rep1', master_password='密码', master_log_file='mysql-bin.000001',master_log_pos=154;

启动slave

start slave;

执行show slave status;查看配置是否成功

19.MySql主从复制--半同步复制

在 MySQL5.5之前, MySQL的复制是异步操作,主库和从库的数据之间存在一定的延迟,这样存在一个隐患:当在主
库上写人一个事务并提交成功,而从库尚未得到主库推送的 Binlog日志时,主库宕机了,例如主库可能因磁盘损
坏、内存故障等造成主库上该事务 Binlog丢失,此时从库就可能损失这个事务,从而造成主从不一致。
而半同步复制，是等待其中一个从库也接收到Binlog事务并成功写入Relay Log之后，才返回Commit操作成功给
客户端；如此半同步就保证了事务成功提交后至少有两份日志记录，一份在主库Binlog上，另一份在从库的
Relay Log上，从而进一步保证数据完整性；半同步复制很大程度取决于主从网络RTT（往返时延），以插件 
semisync_master/semisync_slave 形式存在。

20.分库分表

分表两种方式：

垂直拆分：

将一张表中的其他字段拆分成独立的一张或多张表。一般就是将主键+常用字段列 放在一张表，主键+不常用
列 放在另外一张表。


水平拆分：

将某张表中的数据按照某种逻辑拆分成一张或多张表，拆分出来的表与原表的结构相同。从而降低单表的
数据量提高查询效率。

常见的几种水平分表法：
按时间分：可以按月份划分，一个月一张表。
按区间分：每个表的自增id，按id的区间来划分，如1~1000一张表，1001~2000一张表。涉及到删除数据，
一般删除数据做状态修改，只是物理删除。
按hash值分：根据表中某个字段对分表总数取模，余数就是需要操作的表。


分库分表第三方产品
京东金融的ShardingSphere 
开源的Cobar和MyCat
其他第三方的中间件Oneproxy

21.windows安装MySql

1.下载MySql

国内镜像地址：http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/


备注：
本人用的是MySql5.7.30

2.解压配置环境变量

MYSQL_HOME

这个很简单，不截图了。环境变量不会配置的话也不建议往下看了

3.已管理员身份打开CMD  进入解压MySql的bin目录


执行如下命令，生成data数据目录 
mysqld --initialize-insecure --user=mysql

注意：执行mysqld --initialize-insecure --user=mysql可能会抛出msvcr120.dll丢失错误。

 

解决方案：


下载Visual C++ Redistributable Packages for Visual Studio 2013

https://www.microsoft.com/zh-cn/download/confirmation.aspx?id=40784

下载完成之后重新执行即可

mysqld --initialize-insecure --user=mysql

4.安装MySql 然后继续执行命令安装MySql

mysqld -install

5.启动服务

net start MySQL

6.登录MySql

因为之前没设置密码，所以密码为空，不用输入密码，直接回车即可

mysql -u root -p

7.设置root用户密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

flush privileges;  

8.退出后然后使用密码登录

quit

mysql -u root -p

9.完结