MySQL 面试之性能调优篇

一、MySQL调优思路

整天大概主要有三个维度：

• SQL语句优化：包括索引优化、特定查询优化；
• 对频率控制优化：包括读缓存，写缓冲
• 分库分表；

SQL语句优化：面试中考察最多的点

• 表结构优化

1.1 拆分字段
1.2 字段类型的选择
1.3 字段类型的大小限制
1.4 合理的增加冗余字段
1.5 新建字段一定要有默认值

• 索引方面

2.1 索引字段的选择
2.2 利用好的mysql支持索引下推，覆盖索引等功能
2.3 唯一索引和普通索引的选择

• 查询语句方面

3.1 避免索引失效
3.2 合理的书写where条件的顺序
3.3 小表驱动大表
3.4 可以使用force index（）强制使用索引

即使我们认为我们写的sql语句非常完美，但是mysql可不会这么认为，所以mysql人家需要自己检查一下我们的sql语句是否达到人家认为的完美………………

• 执行计划explain：可以获取MySQL语句的执行计划，包括使用到的索引，扫描行数，表如何链接等信息都会显示；
  
  Extra:

  • using index:覆盖索引，不回表；
  • using where:回表查询；
  • using filesort:需要额外的排序，不能通过索引得到排序结果

• 慢查询日志：用来记录MySQL在响应时间超过阀值的语句，这个值通常为10s；慢查询日志是默认关闭的，我们需要手动开启；

分库分表
随着业务持续扩张，单表新增一定会达到极限，分表是把一个数据库中的数据表拆分成多张表，通过分布式思路提供可扩展的性能；

分表方式：

• 水平分表:将一张大表拆成多个结构相同的字表。
• 垂直分表:将一张表的数据根据场景切分成多张表；

开源组件–Mycat：数据库中间件，本质是提供代理服务，对数据库进行访问，提供包括读写分离、分库分表；

二、性能实战

1、 如果线上insert导致cpu很高，怎么解决？

• 查看是不是请求量突然飙升导致的，如果是攻击，增加对应的防攻击方法；
• 查看是否因为数据规模达到一个值，导致MySQL处理能力发生了下降；
• 查看是否索引过多导致的，这种情况下我们需要清理非必要索引；

思考：索引过多为什么会导致性能下降？
很多人都觉得建立很多的索引，我们的sql语句执行时间会很快，这没问题；但是建立一个索引，就相当于一颗B+树，如果我们建了10个单个索引，就相当于10次随机I/O，这样磁盘性能就会慢很多；

2、count操作怎么优化？

• 用Redis缓存来计数。每次服务启动，将个数加载进Redis。
• 为count的筛选条件建立联合索引。这样可以实现索引覆盖，不用回表。这种适合用于where条件的情况；
• 多维护一个计数器，通过事务的原子性，维持一个准确的计数。适用于对数据精度高，读多写少场景。

3、如果初期我们没做分表，但是数据库已经3000w数据，此时如何分库分表？

• 双写读老阶段：通过中间件，对write sql同时进行两次转发，也就是双写，保持新数据一致，同时开始历史数据拷贝。
• 读写双读阶段：采用灰度策略，一部分流量读老表，一部分流量读新表，读新表的部分一开始，还可以同时多读一次旧表数据，进行比对检查，观察无误后，随着时间慢慢切量到新表。
• 双写读新阶段：此时基本稳定，可以只读新表，为了保证安全，建议还是多双写一段时间，防止有问题出现。
• 写新读新阶段：此时完成了分表的迁移，老表数据可以做个冷备。

4、如果sql语句各方面来说都已经足够优秀了，但是mysql查询压力太大，我们应该怎么办？？

• 二八原则：请求压力是否符合二八原则，80%的压力都集中在20%的数据；加redis缓存；
• 分库分表；

5、如果写入压力过大呢？

• 写缓冲：增加消息队列来缓解；

6、索引失效的场景有哪些？

• 最左前缀法则：带头索引不能死，中间索引不能断；
• 不要在索引列上做任何操作：计算，函数，手动转换类型，不然会导致索引失效而转向全表扫描；
• 不能继续使用索引中的范围条件，between ,<,>，in右边的列；
• 索引字段上使用！=，<,>,判断时，会导致索引失效而转向全表扫描；
• 索引字段上使用is null/is not null判断时，会导致索引失效而转向全表扫描；
• 索引字段使用like以通配符开头（“%字符串”）时，会导致索引失效而转向全表扫描；
• 索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描；隐式类型转换
• 索引字段使用or时，会导致索引失效而转向全表扫描；使用or时，它的前后字段都要加索引；
• 把两个建了单独索引的列用来做对比，索引就会失效；
• order by 前面没有where条件，索引失效；
  

7、什么是回表？
概念：通过数据库索引扫描出该索引数据所在的行，取到主键id,在通过主键id取出主键索引数中的数据。即基于非主键索引的查询需要多扫描一颗索引树；

拓展：
MyISAM的叶子节点存储的是对应数据的指针；
InnoDB有两大索引：主键索引和普通索引；
主键索引顾名思义就是主键id字段创建的索引，普通索引就是非主键id字段创建的索引；
主键索引的叶子结点存储的是行记录；而普通索引的叶子节点存储的是对应的主键id;

举个例子：
表user字段有：id（主键）、name(普通索引),sex三个字段

select * from user where name = '1';
//查找记录应该是：先找到name =1的，然后拿到主键id，在根据主键id查到所有的数据；

所以：回表就是先定位主键值，在定位行记录，需要扫描两次表；

8、什么是索引下推？
定义：如果存在某些被索引的列的判断条件时，MySQL将和一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器；在MySQL5.6版本之后才推出；
目的：为了优化查询；可以减少存储引擎查询基础表的次数，也可以减少Mysql服务器从存储引擎接收数据的次数；

举个例子：
user表中：id,name,age，address字段，联合索引（name,age）；

比如我们需要查找姓名第一个字为陈的用户;
select * from user where name like '陈%';
此时肯定用到了联合索引

那如果我们新加一个where条件啊呢？age =20的

select * from user where name like '陈%' and age = 20;
在5.6之前的版本中，忽略age字段，根据name查出来id值，在根据id值进行回表，查出age =20的；此时有个回表2次的过程；
而在MySQL5.6之后，我们就直接判断了这两个条件，对于age不等于20的记录直接跳过，拿着这个id去主键索引树中回表查询全部数据，这次只需回表1次；

数据库在取出索引的同时，会根据where条件直接过滤掉不满足条件的记录，减少回表次数，大大提升了查询的效率；

9、什么是覆盖索引？
覆盖索引是指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取，可以减少回表的次数；

只要在一颗索引树上就能获取SQL所需的所有列数据，无需回表，速度更快；
举个例子：
表user字段有：id、name(普通索引),sex三个字段

select id,name from user where name = '1';
//能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。

而
select id,name,sex from user where name = '1';
//此时需要回表，sex必须回表才能查到

如果把(name)单列索引升级为联合索引(name, sex)就不同了。通过name的索引树可以获取id,name,sex。无需回表，即可拿到数据；
或者selext id,name from user where name = '1';

覆盖索引的常见使用场景：

• 列查询回表优化:上面的例子，name升级联合索引；
• 全表count查询:select count(name) from user;
• 分页查询：

10、什么是最左前缀原则？
在where条件中出现的字段，如果只有组合索引中的部分列，则这部分列的触发索引顺序，是按照定义索引的时候的顺序从前到后触发，最左面一个列触发不了，之后的所有列索引都无法触发。【索引最左匹配】

举个例子：表user，建立联合索引（a,b,c）;
selet * from user where b = ‘’ and a = ‘’ and c = ‘’;
很多人认为这条sql语句会用不到索引，但结果能用到，是为sql优化器会自动解析成where a= “” and b= “” and c="";
select * from user where a ="" and c="";
这条sql语句也只有a是走索引的；

反过来想如果想让where a = “” and b = “” and c = “”；走索引，那如何建立联合索引？
（a,b,c）,(a,c,b),(b,c,a),(b,a,c),(c,a,b),(c,b,a)都可以，应为优化器会自动优化成走索引的顺序；

对于(a,b,c）联合索引来说，查询(a,b)可以用到这个联合索引，但是查询（b,c）就没有办法使用这个联合索引，因为b,c列的有序性都是依托于a列的存在；

11、普通索引和唯一索引该怎么选择？
先说一下两者的区别在考虑使用场景；
唯一索引和普通索引的不同点在于普通索引查找到满足条件的第一个记录后，还会继续查找下一个记录，知道碰到第一个不满足该条件的记录；而对于唯一索引来说，一旦找到一个满足条件的记录后，就会立即停止继续检索；

查询：

• 当普通索引为条件时查询到的数据会一直扫描，知道扫完整张表；
• 当唯一索引为查询条件时，查到该数据会直接返回，不会继续扫描表；
  更新：
• 普通索引会直接将操作更新到change buffer中，然后结束；
• 唯一索引需要判断数据是否冲突；

唯一索引更加适合查询的场景，普通索引适合插入的场景；
12、前缀索引了解吗，为什么要建前缀索引？

前缀索引就是选取字段的前几个字节建立索引。InooDB限制了每列索引的最大长度不能超过767字节，所以，对于某些比较长的字段，如果确实有建立索引的必要，使用前缀索引不仅能够避免索引长度超过限制，而且相对于普通索引来说，占用的空间和查询成本更小；
不过前缀索引设置长度不合适可能会导致两个问题：
第一个：可能会增加记录扫描次数与回表次数，影响性能。所以，前缀索引长度的选取还是很重要的，可能前缀定义长一点，就能够大幅度减少扫描次数和回表次数；所以建立前缀索引的时候，需要在占用空间和搜索效率之间做一个权衡；
索引选取的越长，占用的磁盘空间越大，相同的数据页能放下的索引值就越少，搜索的效率也就越低；
第二个：使用前缀索引没办法用覆盖索引，因为innodb不能确定前缀索引的定义是否截断了完整的信息，就算是完全踩中了前缀索引，innodb还得回表确认一次到底是不是满足条件了；
13、binlog是做什么的？

binlog是归档日志，属于Server层的日志，是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息；

主要用于：主从复制，数据恢复；

14、undo log是做什么？
undo log是InnoDB存储引擎的日志，用于保证数据的原子性，保存了事务发生之前的数据的一个版本，也就是说记录的数据是修改之前的数据，可以用于回滚，同时可以提供多版本并发控制下的读；
主要作用：事务回滚，多版本并发控制（MVCC）；

undo log是每次操作的反向操作；
InnoDB根据undo log得到数据的历史版本，从而实现多版本控制；

undo log如何与某条行记录产生联系呢？如何通过这条行记录找到它拥有的undo log呢？
InnoDB存储引擎中每条记录都有两个隐藏的字段：trx_id和roll_pointer;
trx_id：最近更新这条行记录的事务ID
roll_pointer：指向之前生成的undo log；

ReadView机制：用来判断当前事务能够看见哪些版本的
通过undo log版本链和ReadView机制，可以保证一个事务只可以读到该事务自己修改的数据或该事务开始之前的数据。

MVCC和ReadView机制实例讲解
15、relaylog是做什么？
relay log是中继日志，在主从同步的时候用，他是一个中介临时的日志文件，用于存储从master节点同步过来的binlog日志内容；
master 主节点的binlog传到slave从节点后，被写入relay log里，从节点的slave sql线程从relay log里读取日志然后应用到slave从节点本地。从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致；

16、redo log是做什么的？
redo log是InnoDB存储引擎特有的一种日志，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来；
可以做数据恢复并且提供crash-safe能力；
当有增删改相关的操作时，会先记录到InnoDB中，并修改缓存页中的数据，等到mysql闲下来的时候才会真正的将redo log中的数据写入到磁盘当中；

17、redo log是怎么记录日志的？
InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写。
所以，如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生内存抖动现象，从肉眼的角度来观察会发现mysql会宕机一会，此时就是正在刷盘了。

18、redo log和binlog的区别？

• redo log是InnoDB独有的日志，而binlog是server层的，所有的存储引擎都有使用到；
• redo log记录了具体的数值，对某个页做了什么修改，binlog记录的是操作内容；
• binlog大小达到上线或者flush log会生成一个新的文件，而redo log有固定的大小只能循环使用；
• binlog日志没有crash-safe的能力，只能用于归档。而redo log有crash-safe能力；

19、binlog日志都得三种格式

• statement：基于sql语句的复制；一条会修改的数据的SQL语句都会记录在binlog中
• Row:基于行的复制；不记录SQL语句的上下文相关信息，仅保存那条记录被修改；
• Mixed：混合复制；

20、MySQL主从之间是怎么同步数据的？

• master主库将此次更新的事件类型写入到主库的binlog文件中；
• master创建log dump线程通知slave需要更新数据
• slave向master节点发送请求，将该binlog文件内容存到本地的relay log中
• salve开启sql线程读取relay log中的内容，将其中的内容在本地重新执行一遍，完成主从数据同步
  【同步策略】：
• 全同步复制：主库强制同步日志到从库，等全部从库执行完才返回客户端，性能差；
• 半同步复制：主库收到至少一个从库确认就认为操作成功，从库写入日志成功返回ack确认；

21、主从延迟怎么解决？

**主备延迟常见原因**：

• 备库机器性能差：如果主备机器的性能差别大，直接导致备库的同步速度跟不上主库的生产节奏；
• 备库干私活：除了正常的读业务外，是否有被其他特殊业务征用；
• 大事务：大事务执行时间比较长，备库虽然能很快拿到binlog，但是备库执行binlog也需要花费5分钟，而且备库只有等到事务执行完提交，备库才能对外；

解决办法：

• 升级备库的机器配置
• MySQL5.6之后提供了一种并行复制的方法，通过将SQL线程转换为多个work线程来进行重放
• 分库分表，避免单表单库过大带来额外的复制压力
• 避免长事务
• 避免让数据库进行大量的运算

主备切换策略：

当主库A发生故障不可用时，开始进入主备切换；

• 判断B库seconds_behind_master是否小于设定的阈值；
• 将A库改为只读状态，将readonly设置为true.断掉A库的写入操作，保证不会有新的写流量进来；
• 判断B库的seconds_behind_master，直到为0；
• 修改B库为读、写状态
• 客户端的请求达到B库；
• 主备切换完成