java中间件 - MySQL调优

简述MyISAM和InnoDB的区别？
MyISAM：
不支持事务，但是每次查询都是原子的
支持表级锁，每次菜哦是对整个表加锁
存储表的总行数；
一个MyISAM表有三个文件：索引文件、表结构文件、数据文件；
采用非聚簇索引，索引文件的数据域存储执行数据文件的指针。
辅索引与主索引基本一致，但是辅索引不用保证唯一性。
InnoDB
支持ACID的事务，支持事务的四种隔离级别
支持行级锁及外键约束：因此可以支持写并发
不存储总行数
一个InnoDB引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；
主键索引采用聚簇索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，在访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整

索引类型以及对数据库性能的影响？
普通索引：允许被索引的数据列包含重复的值
唯一索引：可以保证数据记录的唯一性
主键：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY来创建
联合索引：索引可以覆盖多个数据列，如像INDEX（columnA，columnB）索引
全文索引：通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。
索引可以极大的提高数据的查询速度
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能
但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件
索引需要占物理空间，除了数据库表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚簇索引很多，一旦聚簇索引改变，那么所有非聚簇索引都会跟着变。

从数据库查询数据需要注意的地方还是蛮多的，其中很多都是平时积累来的。比如说：

1. 是否能使用「覆盖索引」，减少「回表」所消耗的时间。意味着，我们在select 的时候，一定要指明对应的列，而不是select *
2. 考虑是否组建「联合索引」，如果组建「联合索引」，尽量将区分度最高的放在最左边，并且需要考虑「最左匹配原则」
   3.对索引进行函数操作或者表达式计算会导致索引失效
   4.利用子查询优化超多分页场景。比如 limit offset , n 在MySQL是获取 offset + n的记录，再返回n条。而利用子查询则是查出n条，通过ID检索对应的记录出来，提高查询效率。
   5.通过explain命令来查看SQL的执行计划，看看自己写的SQL是否走了索引，走了什么索引。通过show profile 来查看SQL对系统资源的损耗情况（不过一般还是比较少用到的）

6.在开启事务后，在事务内尽可能只操作数据库，并有意识地减少锁的持有时间（比如在事务内需要插入&&修改数据，那可以先插入后修改。因为修改是更新操作，会加行锁。如果先更新，那并发下可能会导致多个事务的请求等待行锁释放）

mysql执行计划怎么看？
执行计划就是sql的执行查询的顺序，以及如果使用索引查询，返回结果集的行数。
在 select 前添加 EXPAIN

1.id：是一个有顺序的编号，是查询的顺序号，有几个select就显示几行。id的顺序是按照select出现的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。
2.selectType表示查询中每个select字句的类型
3.table：表示该语句查询表
4.type：优化sql的重要字段，也是我们判断sql性能和优化程度重要指标，他的取值范围：
system：表中只有一行记录，相当于系统表
const：通过索引一次命中，匹配一行数据
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
ref：非唯一索引扫描，返回匹配某个值的所有
range：只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>；
index：只遍历索引树
ALL：表示全表扫描，这个类型的查询是性能最差的查询之一。那么基本上就是随着表的数据增多，执行效率越慢
执行效率由上到下，最好是避免ALL和index
5.possible_keys：它表示Mysql在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一定会用到
6.key：此字段是mysql当前查询时所真正使用到的索引，他是possible的子集
7.key_len:表示查询优化器使用索引的字节数，这个字段可以评估组合索引是否完全被使用，这也是我们优化sql时，评估索引的重要指标
8.ref:显示索引的那一列被使用了，如果可能，是一个常量const
9.rows:mysql查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入的类型之类的问题，说明要优化空间越大
10:filtered:返回结果的行占需要读到的行（rows列的值）的百分比，就是百分比越高，说明需要查询到数据越准确，百分比越小，说明查询到的数据量大，而结果集少
11.Extra:
using filesort：表示mysql对结果集进行外部排序，不能通过索引顺序达到排序效果，一般有using filesort都建议优化去掉，因为这样的查询cpu资源消耗大，延时大
using index：覆盖索引扫描，表示查询在索引树中就可查询所需数据，不用扫描表数据文件，往往说明性能不错
using temporary：查询有使用临时表，一般出现于排序，分组和多表join的情况，查询效率不高，建议优化
using where：sql使用了where过滤，效率较高

随后，就考虑另一种情况，能不能在查询之前，直接走一层缓存（Redis）。
而走缓存的话，又要看业务能不能忍受读取的「非真正实时」的数据（毕竟Redis和MySQL的数据一致性需要保证），如果查询条件相对复杂且多变的话（涉及各种group by 和sum），那走缓存也不是一种好的办法，维护起来就不方便了…
再看看是不是有「字符串」检索的场景导致查询低效，如果是的话，可以考虑把表的数据导入至Elasticsearch类的搜索引擎，后续的线上查询就直接走Elasticsearch了。
MySQL->Elasticsearch需要有对应的同步程序(一般就是监听MySQL的binlog，解析binlog后导入到Elasticsearch)
如果还不是的话，那考虑要不要根据查询条件的维度，做相对应的聚合表，线上的请求就查询聚合表的数据，不走原表。
比如，用户下单后，有一份订单明细，而订单明细表的量级太大。但在产品侧(前台)透出的查询功能是以「天」维度来展示的，那就可以将每个用户的每天数据聚合起来，在聚合表就是一个用户一天只有一条汇总后的数据。
查询走聚合后的表，那速度肯定杠杠的（聚合后的表数据量肯定比原始表要少很多）
思路大致的就是「以空间换时间」，相同的数据换别的地方也存储一份，提高查询效率

除了读之外，写性能同样有瓶颈，怎么办？
如果在MySQL读写都有瓶颈，那首先看下目前MySQL的架构是怎么样的
如果是单库的，那是不是可以考虑升级至主从架构，实现读写分离。
简单理解就是：主库接收写请求，从库接收读请求。从库的数据由主库发送的binlog进而更新，实现主从数据一致（在一般场景下，主从的数据是通过异步来保证最终一致性的）
mysql主从复制中主要有三个线程：master（binlog dump thread）、slave（I/O thread、sql thread），Master一条线程和slave中的两条线程
主节点 binlog，主从复制的基础是主库记录数据库的所有变更记录到binlog，binlog是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
主节点log dump线程，当binlog 有变动时，log dump线程读取其内容并发送给从节点
从节点（I/O）线程接收binlog内容，并将其写入到relay log文件中
从节点的sql线程读取relay log文件内容对数据更新进行重放，最终保证主从数据库的一致性
注：主从节点使用binlog文件 + position 偏移量来定为主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从position的位置发起同步

由于mysql默认的复制方式是异步，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了，由此产生两个概念

全同步复制
主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响
半同步复制
和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

借鉴文章：
http://javainterview.gitee.io/luffy/2021/08/19/08-MySQL/03.%20MySQL%E8%B0%83%E4%BC%98/