MySQL的性能分析,事务处理,锁,集群搭建
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL的性能分析,事务处理,锁,集群搭建，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7419字，纯文字阅读大概需要11分钟。
内容图文
MySQL性能分析介绍
show profiles 这个命令非常强大，能清晰的展示每条SQL的持续时间。通常结合show profile 命令可以更加详细的展示其耗时信息。这样就能很容易的分析出，到底慢在哪个环节了。比较遗憾的是，在MySQL中，该命令默认是关闭状态的。在使用之前，我们首先得启用它：
-
开启命令：
set profiling = ON;
或：
set profiling = 1;
-
查看是否生效：
mysql> show variables like "profiling"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | ON | +---------------+-------+ 1 row in set (0.00 sec)
Value的取值范围有两个：其中 ON 为开启状态，OFF为关闭状态。
值得注意的是：通过上述命令开启后仅在当前会话有效。
l Query Profiler是MYSQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的性能瓶颈在什么地方。
l 通常我们是使用的explain,以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MYSQL 5.0.37以及以上版本中才有实现。
l 默认的情况下，MYSQL的该功能没有打开，需要自己手动启动。
开启Profile功能
l Profile 功能由MySQL会话变量 : profiling控制,默认是OFF关闭状态。
l 查看是否开启了Profile功能:
* select @@profiling;
* show variables like ‘%profil%’;
示例
1.查看是否打开了性能分析功能
select @@profiling;
2.打开 profiling 功能
set profiling=1;
3.执行sql语句
4.执行 show profiles 查看分析列表
5.查询第二条语句的执行情况
show profile for query 2;
6.可指定资源类型查询
show profile cpu,swaps for query 2;
MySQL事务处理
1.1 事务概述
l 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
l 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
l 事务用来管理DDL、DML、DCL操作，比如 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：
l Atomicity（原子性）
l Consistency（稳定性、一致性）
l Isolation（隔离性）
l Durability（可靠性、持久性）
对于ACID的解释如下：
l 原子性：构成事务的的所有操作必须是一个逻辑单元，要么全部执行，要么全部不执行。
l 稳定性（一致性）：数据库在事务执行前后状态都必须是稳定的。
l 隔离性：事务之间不会相互影响。
l 可靠性（持久性）：事务执行成功后必须全部写入磁盘。
事务并发问题
在事务的并发操作中可能会出现一些问题：
l 脏读：一个事务读取到另一个事务未提交的数据。
l 不可重复读：一个事务因读取到另一个事务已提交的数据。导致对同一条记录读取两次以上的结果不一致。update操作
l 幻读：一个事务因读取到另一个事务已提交的数据。导致对同一张表读取两次以上的结果不一致。insert、delete操作
事务隔离级别
为了避免上面出现的几种情况，在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同
l 四种隔离级别：
现在来看看MySQL数据库为我们提供的四种隔离级别（由低到高）：
① Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
② Read committed (读已提交)：可避免脏读的发生。
③ Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
④ Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
MySQL锁
概述
数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。
总的来说，MySQL各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。
l 行级锁定（row-level）
行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
l 表级锁定（table-level）
和行级锁定相反，表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。
当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。
l 页级锁定（page-level）
页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
l 总的来说，MySQL这3种锁的特性可大致归纳如下：
* 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
* 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
* 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
1.1 InnoDB引擎的锁机制
共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
说明：
1）共享锁和排他锁都是行锁，意向锁都是表锁，应用中我们只会使用到共享锁和排他锁，意向锁是mysql内部使用的，不需要用户干预。
2）对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。
3）InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！。
集群搭建之主从复制
复制概述
Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。
请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。(所以一般会把从服务器设置成只读read_only= 1)
主从复制实践
1.1.1 主服务器配置
l 第一步：修改my.conf文件：
在[mysqld]段下添加：
|
#启用二进制日志 log-bin=mysql-bin #服务器唯一ID，一般取IP最后一段 server-id=133 |
l 第二步：重启mysql服务
service mysqld restart
l 第三步：建立帐户并授权slave
mysql>GRANT FILE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456';
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'backup'@'%' identified by '123456';
#一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。
刷新权限
mysql> FLUSH PRIVILEGES;
查看mysql现在有哪些用户
mysql>select user,host from mysql.user;
l 第四步：查询master的状态
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 120 | db1 | mysql | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set
内容总结
以上是互联网集市为您收集整理的MySQL的性能分析,事务处理,锁,集群搭建全部内容，希望文章能够帮你解决MySQL的性能分析,事务处理,锁,集群搭建所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。