您的位置: 首页  >  IT文章  >  MySQL性能优化 集群搭建之读写分离：

MySQL性能优化 集群搭建之读写分离：

分类: IT文章 • 2022-03-30 19:36:10

MySQL性能优化 集群搭建之读写分离：

性能优化的思路：

  1. 首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句

  2. 使用explain去查看该SQL的执行计划

  3. 使用show profile去查看该SQL执行时的性能问题

慢查询：

数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化 SQL，更重要的是得先找到需要优化的 SQL。

MySQL 数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL，这将极大程度帮助我们快速定位到症结所在，以便对症下药。

MySQL的慢查询日志功能，默认是关闭的，需要手动开启。

查询慢查询是否开启

MySQL性能优化 集群搭建之读写分离：

slow_query_log    ：是否开启慢查询日志，ON 为开启，OFF 为关闭，如果为关闭可以开启。

slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

临时开启慢查询功能

在 MySQL 执行 SQL 语句设置，但是如果重启 MySQL 的话将失效

set global slow_query_log = ON;

set global long_query_time = 1;

永久开启慢查询功能

修改/etc/my.cnf配置文件，重启 MySQL, 这种永久生效.

[mysqld]

slow_query_log = ON

slow_query_log_file = /var/log/mysql/slow.log

long_query_time = 1

查看执行计划explain:

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.

使用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 explain 就可以了, 例如:

 MySQL性能优化 集群搭建之读写分离：

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

select_type: SELECT 查询的类型.

table: 查询的是哪个表

partitions: 匹配的分区

type: join 类型  (性能关系：ALL < index < range ~ index_merge < ref < eq_ref < const < system)

possible_keys: 此次查询中可能选用的索引

key: 此次查询中确切使用到的索引.

ref: 哪个字段或常数与 key 一起被使用

rows: 显示此查询一共扫描了多少行. 这个是一个估计值.

filtered: 表示此查询条件所过滤的数据的百分比

extra: 额外的信息

使用show profile：

Query Profiler是MYSQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的性能瓶颈在什么地方。

通常我们是使用的explain,以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MYSQL 5.0.37以及以上版本中才有实现。

语句：

show profile 和 show profiles 语句可以展示当前会话(退出session后,profiling重置为0) 中执行语句的资源使用情况.

show profiles :以列表形式显示最近发送到服务器上执行的语句的资源使用情况

show profile: 展示最近一条语句执行的详细资源占用信息,默认显示 Status和Duration两列

 开启Profile功能

* select @@profiling;
* show variables like ‘%profil%’;查询是否开启Profile功能

  * set profiling=1; --1是开启、0是关闭

MySQL的性能优化细节：

1.合理的创建及使用索引 (考虑数据的增删情况)。

2.合理的冗余字段 (尽量建一些大表 ,考虑数据库的三范式和业务设计的取舍)。

3.使用SQL要注意- 些细节: select语句中尽量不要使用*，count(*) , WHERE语句中尽量不要使用1=1、in语句(建议使用exists人注意组合索引的创建顺序按照顺序组着查询条件、尽量查询力度大的SQL放到最左边、尽量建立组合索引

4.合理利用慢查询日志、 explain 执行计划查询、show profile 查看SQL执行时的资源使用情况

MySQL事务处理

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理DDL、DML、DCL操作，比如 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：

Atomicity（原子性）

Consistency（稳定性、一致性）

Isolation（隔离性）

Durability（可靠性、持久性）

MySQL事务支持：

BEGIN或START TRANSACTION；显式地开启一个事务；

COMMIT:COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；

ROLLBACK:回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

事务并发问题：

在事务的并发操作中可能会出现一些问题：

脏读：一个事务读取到另一个事务未提交的数据。

不可重复读：一个事务因读取到另一个事务已提交的数据。导致对同一条记录读取两次以上的结果不一致。update操作

幻读：一个事务因读取到另一个事务已提交的数据。导致对同一张表读取两次以上的结果不一致。insert、delete操作

事务隔离级别（用来解决事务并发问题）：

① Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

② Read committed (读已提交)：可避免脏读的发生。

③ Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

④ Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

如何查看和设置隔离级别：

在MySQL数据库中查看当前事务的隔离级别：

select @@tx_isolation;

在MySQL数据库中设置事务的隔离 级别：

   set  [glogal | session]  transaction isolation level 隔离级别名称;
   set tx_isolation=’隔离级别名称;’

MySQL锁：

* 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

* 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

* 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

表级锁：

MySQL的表级锁定有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

MySQL 实现的表级锁定的争用状态变量：show status like 'table%';

 MySQL性能优化 集群搭建之读写分离：

* table_locks_immediate：产生表级锁定的次数；
* table_locks_waited：出现表级锁定争用而发生等待的次数；
手动增加表锁

lock table 表名称 read(write),表名称2 read(write)，其他;

查看表锁情况

show open tables;

删除表锁

unlock tables;

行级锁：

InnoDB的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

MySQL性能优化 集群搭建之读写分离：



Innodb所使用的行级锁定状态查看：show status like 'innodb_row_lock%';


MySQL性能优化
集群搭建之读写分离：


* Innodb_row_lock_current_waits：当前正在等待锁定的数量；


* Innodb_row_lock_time：从系统启动到现在锁定总时间长度；


* Innodb_row_lock_time_avg：每次等待所花平均时间；


* Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；


* Innodb_row_lock_waits：系统启动后到现在总共等待的次数；


对于这5个状态变量，比较重要的主要是：


Innodb_row_lock_time_avg（等待平均时长）


Innodb_row_lock_waits（等待总次数）


Innodb_row_lock_time（等待总时长）这三项。


集群搭建之主从复制：


主服务器配置


第一步：修改my.conf文件：


在[mysqld]段下添加：




#启用二进制日志

log-bin=mysql-bin

#服务器唯一ID，一般取IP最后一段

server-id=133




第二步：重启mysql服务




service mysqld restart




第三步：建立帐户并授权slave




mysql>GRANT FILE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456';
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'backup'@'%' identified by '123456'; 


刷新权限


mysql> FLUSH PRIVILEGES;


  


查看mysql现在有哪些用户


mysql>select user,host from mysql.user;




第四步：查询master的状态


mysql> show master status;


 从服务器配置：



第一步：修改my.conf文件


[mysqld]


server-id=134


第二步：删除UUID文件


第三步：配置从服务器


mysql>change master to master_host='192.168.25.134',master_port=3306,master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=120 


第四步：启动从服务器复制功能


mysql>start slave;


第五步：检查从服务器复制功能状态：


mysql> show slave status




MySQL-Proxy配置



创建mysql-proxy.cnf文件


MySQL性能优化
集群搭建之读写分离：



MySQL-Proxy启动域测试


mysql-proxy  --defaults-file=mysql-proxy.cnf配置文件的地址



 


 


 


 


 


 








															

																					

								

									相关推荐