Mysql5.7遇到的问题总结(高级部分)

在学习mysql高级部分explain对sql语句调优的时候遇到如下问题,
查看explain中的extra字段 Using temporary,当出现该值的时候也即是mysql使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表,常见于排序order by 和group by
在这里插入图片描述
问题原因:ONLY_FULL_GROUP_BY的意思是:对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中,也就是说查出来的列必须在group by后面出现,否则就会报错,或者这个字段出现在聚合函数里面。

查看sql_model参数命令:
SELECT @@GLOBAL.sql_mode;
在这里插入图片描述
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
第一项默认开启ONLY_FULL_GROUP_BY了,导致出现了错误,需要把它的默认关掉。
解决方法: 命令行输入 :
set sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;
在这里插入图片描述
一.单表查询(索引优化案例)
在Article表中,查询category_id = 1,且comments>1的情况下,views最多的article_id;
在这里插入图片描述
sql语句:explain select id,author_id from article where category_id = 1 and comments > 1 order by views DESC limit 1;
在这里插入图片描述
看到Extra中的信息,Using filesort,并且type类型为全表查询,虽然结果能查出来但是sql语句并未进行优化,所以要建索引值,根据判断where后面所带的条件都可以做index的索引条件。create index idx_article_ccv on article(category_id,comments,views);type变成了range可以接受,但是Using filesort是无法接受的,如下所示仍然没有解决相应的问题
在这里插入图片描述
因为按照BTree索引的工作原理,先排序category_id.如果遇到相同的category_id,则再排序comments,如果遇到相同的comments则再排序views.当comments字段在联合索引处于中间位置,因为comments条件是一个范围值(所谓的range),所以MySQL无法利用索引再对后面的views部分进行检索,即range类型查询字段后面的索引失效。换句话说,该索引需要重建,如下所示此时type类型为ref,并且Extra爷只有Usingwhere达到了sql优化效果
在这里插入图片描述

二.两张表查询(索引优化案例)
两张表一张是class,一张是book
在这里插入图片描述在这里插入图片描述
sql语句连接两张表查询
SELECT * from class LEFT JOIN book ON class.card = book.card;
创建右表索引
ALTER TABLE book ADD INDEX idx_bc (card);
explain SELECT * from class LEFT JOIN book ON class.card = book.card;
type出现ref即可
在这里插入图片描述
那么在左表创建索引也即是
ALTER TABLE class ADD INDEX idx_cc (card);
explain SELECT * from class LEFT JOIN book ON class.card = book.card;
在这里插入图片描述
根据上述两种情况可以得出,两表连接查询的sql语句索引所建应该是根据LEFT 和RIGHT相反的方向创建索引,当然索引建后后也可以更改sql语句的位置
三.三张表查询(索引优化案例)
与两表查询类似,在相反的方向的表当中分别添加相应的索引。

四.MySQL锁机制
表锁偏向于读操作(MyISAM)
1.(MyISAM)表锁中读锁的案例分析
在这里插入图片描述
①在session1中对mylock加了读锁,session2可以对mylock以及其他表进行读操作.
②session1 可以对mylock加了read锁的表进行读操作,但不可读其他没有锁定的表,因为前面的栈仍然未清空。
③对锁定的表mylock不可增删改操作,而其他session对mylock进行增删改操作会一直等待获得锁.
在这里插入图片描述
2.(MyISAM)表锁对写锁的案例
在这里插入图片描述

①在session1中对mylock加了写锁,当前session1对锁定表的增删改查都可以执行
②当session1开启写锁后,其他session对该表的查询被阻塞,需要等待锁的释放
在这里插入图片描述
简而言之,对MyISAM表进行操作,读锁会阻塞写,但不会堵塞读。而写则会把读和写都堵塞;
SQL:show status like ‘table%’;
Table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1;
Table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在着较严重的表级锁争用情况;
在这里插入图片描述
行锁偏向于写操作(Innodb)
3.mysql 默认innodb然后默认后面自动提交事务,先把自动提交手动关闭
show variables like ‘autocommit’;
set autocommit = 0;

① 当session1当中自动提交关闭后,事务未提交并且mysql默认的事务隔离级别是repeatable-read已经解决了脏读问题。
② 所以session2当中查询test_innodb_lock读取不到session1中事务未提交的数据;
③ 当session1 对某行进行写操作的时候,session2对该行进行操作时会阻塞状态,只有当session1中的事务
commit提交事务释放锁以后,session2 才可以进行更改
在这里插入图片描述
在这里插入图片描述
4.索引失效行锁升级为表锁
在进行索引失效问题的时候最严重的问题就是行锁升级成表锁
1.varchar类型没有加单引号,Mysq内部使用隐式转换,并未对事务进行提交,导致索引失效后,本身innodb的行锁将升级为表锁。
在这里插入图片描述
而session2在对该表进行写操作的时候,本身不是对相同行进行操作的sql语句也会因为表锁导致堵塞。
在这里插入图片描述
5.间隙锁的危害
在表当中a = 2 的 所在行并不不再相应的b值,当session1进行如下的sql语句的时候,update test_innodb_lock set b = ‘0628’ where a > 1 and a < 6;当我们使用范围条件而不是相等条件检索数据,并请求共享或排他锁,innodb会给符合条件的已有数据记录的索引项加锁;对键值在条件范围内但不存在的记录,称为间隙。
在这里插入图片描述
在这里插入图片描述
此时a = 2的行记录并不存在,但仍然被上了锁,只有当session1提交事务以后,session2才可以对其进行更改。
在这里插入图片描述
6.如何锁定一行
在session1会话中使用sql语句,select * from test_innodb_lock where a = 8 for update;
在这里插入图片描述
此时会话2进行修改sql语句的时候就会发生堵塞只有当session1提交事务以后才可以进行更改.
在这里插入图片描述
在这里插入图片描述

7.如何分析行锁定
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
show status like ‘innodb_row_lock%’;
比较重要的主要是如下三项:
Innodb_row_lock_time_avg(等待平均时长)
Innodb_row_lock_waits(等待总次数)
Innodb_row_lock_time(等待总时长)

8.行锁优化建议
①尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
②合理设计索引,尽量减小锁的范围
③尽可能较少的检索条件,避免间隙锁的产生(range范围,会将即使不在表中的值,但在范围之内的值对其加锁)
④尽量控制事务的大小,减少锁定资源量和时间长度
⑤尽可能低级别事务隔离

9.主从复制
在这里插入图片描述
主要涉及三个线程:
binlog 线程、I/O 线程和 SQL 线程。
① binlog 线程 : 负责将主服务器上的数据更改写入二进制日志中。
② I/O 线程 : 负责从主服务器上读取二进制日志,并写入从服务器的中继日志中。
③ SQL 线程 : 负责读取中继日志并重放其中的 SQL 语句。

为什么要进行主从复制?
因为主服务器处理写操作以及实时性要求比较高的读操作,而从服务器处理读操作
读写分离能提高性能的原因在于:
① 主从服务器负责各自的读和写,极大程度缓解了锁的争用;
② 从服务器可以使用 MyISAM,提升查询性能以及节约系统开销;
③ 增加冗余,提高可用性。
读写分离常用代理方式来实现,代理服务器接收应用层传来的读写请求,然后决定转发到哪个服务器。
在这里插入图片描述

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

破晓以胜

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值