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，提升查询性能以及节约系统开销；
③ 增加冗余，提高可用性。
读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。