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

作者：蒓子2502883107 | 来源：互联网 | 2023-07-04 11:49

1.衡量指标TPS:TransactionsPerSecond指服务器每秒处理事务的个数，支持事务的存储引擎如InnoDB等特有的一个性能衡量标准。计算方式:&#

1.衡量指标

TPS: Transactions Per Second 指服务器每秒处理事务的个数，支持事务的存储引擎如InnoDB 等特有的一个性能衡量标准。计算方式:（事务提交数com_commit + 事务回滚数com_rollback） / 服务器运行时间uptimeQPS: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 primarykeys), 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 &＃39;%have_query_cache%&＃39; --查询数据缓存是否开启 show variables like &＃39;%query_cache_type%&＃39; --查询数据缓存size大小 show variables like &＃39;%query_cache_size%&＃39;

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 &＃39;%binlog%format%&＃39;; 更改复制方式 set global binlog_format = &＃39;ROW&＃39;; set global binlog_format = &＃39;STATEMENT&＃39;;注意：如果从库宕机了然后再恢复了，如何再去执行主库的复制呢？从库会存在两个文件master.info、relay_log.info这两文件记录的当前对主库复制执行的位置，具体执行到哪里了。

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 = 3306socket = /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.cnfcp 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(&＃39;新密码&＃39;); //如果报错，请看此文档 https://blog.csdn.net/hj7jay/article/details/65626766(转载)开放远程连接 update user set user.Host=&＃39;%&＃39; where user.User=&＃39;root&＃39;; 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.pidcharacter-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.pidcharacter-set-server=utf8 lower_case_table_names=1 autocommit=1

5.将data目录修改权限所有者为Mysql 并设置权限为774chown -R mysql:mysql datachmod -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|3307vim /etc/profileexport MYSQL_HOME=/usr/local/mysql5.7.27 export PATH=$PATH:$MYSQL_HOME/bin重新生效 /etc/profilesource /etc/profile

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

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

1.配置主从异步复制上面文档已经创建了3306主 3307从两个MySql实例，关闭3306和3307实例，完成如下配置进入data中3306创建log3306文件夹用来存放bin-log二进制文件,并且修改3306/my.cnf 3307/my.cnf3306/my.cnfserver_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 &＃39;rep1&＃39;@&＃39;ip自己填&＃39; IDENTIFIED BY &＃39;密码&＃39;;FLUSH PRIVILEGES;3.在主3306上查询二进制日志名和偏移量show master status;

5.登录从机3307执行同步语句change master to master_host=&＃39;ip&＃39;, master_user=&＃39;rep1&＃39;, master_password=&＃39;密码&＃39;, master_log_file=&＃39;mysql-bin.000001&＃39;,master_log_pos=154;启动slavestart 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 2013https://www.microsoft.com/zh-cn/download/confirmation.aspx?id=40784下载完成之后重新执行即可mysqld --initialize-insecure --user=mysql

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