MySQL性能与锁
1 MySQL性能优化之查看执行计划explain.
explain的参数:
1、id:在整个查询中SELECT的位置;
2、select_type:查询的类型,包括没有子查询的简单查询、UNION、子查询、外部查询、外部查询中的子查询或FROM语句中的子查询等;
3、table:所查询的表名;
4、type:连接如何执行的情况。这里存在很多值,范围从const(最佳)到ALL(最差);
all < index < range < index_subquery < unique_subquery < index_merge < ref_or_null < ref < eq_ref < const<system
5、possible_keys:为了提高查询速度,在MySQL中可以使用的索引;
6、key:实际使用的索引;
7、key_len:索引的长度;
8、ref:使用哪一列或常数与key一起从表中选择行;
9、rows:MySQL需要在相应表中为了成功进行查询,进行检验的行的数量。为了得出总行数,MySQL必须扫描处理整个查询,再乘以每个表的行值;
10、Extra:其他信息,涉及MySQL如何处理查询,比如说,使用WHERE语句、使用一个索引、利用一个临时表等;
重点
type:访问类型,查看SQL到底是以何种类型访问数据的。
key:使用的索引,MySQL用了哪个索引,有时候MySQL用的索引不是最好的,需要force index()。
rows:最大扫描的列数。
extra:重要的额外信息,特别注意损耗性能的两个情况,using filesort和using temporary。
参考网站:
https ://segmentfault. com/a/190000008131735.
https://blog. csdn. net/rewiner120/article/details/ 70598797.
什么是数据库的性能:
- 用查询的响应时间度量性能,性能即响应时间。
-
优化性能,在一定工作负载下,降低查询的响应时间。
2 MySQL性能优化之慢查询
性能优化思路:
- 首先需要使用慢查询功能,去获取所有查询时间比较长的SQL语句。
- 使用explain去查看该SQL的执行计划
- 使用showprofile去查看该SQL执行时的性能问题。
介绍:
数据库查询快慢是影响项目性能的一大因素,对于数据库,我们除了要优化 SQL,更重要的是得先找到需要优化的 SQL。
l MySQL 数据库有一个“慢查询日志”功能,用来记录查询时间超过某个设定值的SQL,这将极大程度帮助我们快速定位到症结所在,以便对症下药。
开启慢查询功能
![](https://upload-images.jianshu.io/upload_images/6081878-857b2b21ae2adb7a.png?imageMogr2/auto-orient/strip|imageView2/2/w/454/format/webp)
- 合理的创建及使用索引(考忠数据的增删情况)。
- 合理的冗余字段(尽量建- -些大表,考虑数据库的三范式和业务设计的取舍)。
- 使用SQL要注意- -些细节: select 语句中尽量不要使用*,WHERE语句中尽量不要使用1=1. in语句(建议使用exists)、 注意组合索引的创建顺序按照顺序组着查询条件、尽量查询粒度大的大的SQL放到最左边、尽量建立组合索引。
- 合理利用慢查询日志、explain 执行计划查询、show profile 查看SQL执行时的资源使用情况。
Query Profiler是MYSQL自带的一种query诊断分析工具,通过它可以分析出一条SQL语句的性能瓶颈在什么地方。
通常我们是使用的explain(关注 参数 有 type key,ref,rows),以及slow query log都无法做到精确分析,但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况,比如CPU,IO等,以及该SQL执行所耗费的时间等。不过该工具只有在MYSQL 5.0.37以及以上版本中才有实现。
默认的情况下,MYSQL的该功能没有打开,需要自己手动启动
show profile 和** show profiles** 语句可以展示当前会话退出session后,profiling重置为0中执行语句的资源使用情况.
开启Profile功能
l Profile 功能由MySQL会话变量 : profiling控制,默认是OFF关闭状态。
l 查看是否开启了Profile功能:
** select @@profiling;**
show variables like ‘%profil%’;
![](http://upload-images.jianshu.io/upload_images/6081878-b6f808e084432d2f.png?imageMogr2/auto-orient/strip|imageView2/2/w/305/format/webp)
l 开启profile功能
- set profiling=1; --1是开启、0是关闭
4 MySQL锁
MySQL 三种类型(级别)锁定机制
- 行级锁定
- 页级锁定
- 表级锁定
InnoDB引擎的锁机制
说明:
5 集群搭建之主从复制
主服务器配置
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 'root'@'%' IDENTIFIED BY '123456';
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'root'@'%' identified by 'root';
#一般不用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
从服务器配置
l 第一步:修改my.conf文件
[mysqld]
server-id=134
l 第二步:删除UUID文件
错误处理: 如果出现此错误: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. 因为是mysql是克隆的系统所以mysql的uuid是一样的,所以需要修改。 |
解决方法: 删除/var/lib/mysql/auto.cnf文件,重新启动服务。 |
l 第三步:重启并登陆到MySQL,进行配置从服务器
mysql>change master to master_host='192.168.25.134',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=120
注意语句中间不要断开,master_port为mysql服务器端口号(无引号),master_user为执行同步操作的数据库账户,“120”无单引号(此处的120就是show master status 中看到的position的值,这里的mysql-bin.000001就是file对应的值)。
l 第四步:启动从服务器复制功能
mysql>start slave;
l 第五步:检查从服务器复制功能状态:
mysql> show slave status
……………………(省略部分)
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
……………………(省略部分)
注:Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。
以上操作过程,从服务器配置完成。
集群搭建之读写分离
读写分离的理解
HAC : High Availability Cluster.
MySQL的主从复制,只会保证主机对外提供服务,而从机是不对外提供服务的,只是在后台为主机进行备份
开源的中间件有Mysql Proxy,Atlas。