一.MySQL优化
如何定位慢查询
方案1：Mysql自带慢日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志如果要开启慢查询日志,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
配置完成后,通过以下指令重新启动MySQL服务器进行测试,查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log
方案2：开源工具
问:MySQL中,如何定位慢查询
系统部署运维的监控系统Skywalking ，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题
如果，项目中没有这种运维的监控系统，其实在MySQL中也提供了慢日志查询的功能，可以在MySQL的系统配置文件中开启这个慢日志的功能，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中，
SQL语句执行的很慢,怎么分析?
如果一个SQL语句执行很慢,可以采用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息
possible_key 当前ssql可能会使用到的索引
key 当前sql实际命中的索引
key_len 索引占用的大小
Extra额外的优化建议
type 代表这条sql语句链接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all
system:查询系统中的表
const:根据主键查询
eq_ref:主键索引查询或唯一索引查询
ref:索引查询
range:范围查询
index:索引树查询
all:全盘扫描
问:那这个SQL语句执行很慢, 如何分析呢？
如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况，比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复
索引
索引（index）是指:帮助Mysql高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构(B+树),这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引
索引的底层数据结构是B+树
B树：B-Tree,B树是一种多叉路衡查找树,相对于二叉树,B树每个节点可以有多个分支,即多叉。以一颗最大度数(max-degree)为5(5阶)的b-tree为例,那这个b树每个节点最多存储4个key
B+树:B+树是在BTree基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用的B+Tree实现其索引结构
面试官:了解过索引吗？（什么是索引）
索引在项目中还是比较常见的，它是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗
面试官:索引的底层数据结构了解过嘛
MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是：第一阶数更多，路径更短，第二个磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据，第三是B+树便于扫库和区间查询，叶子节点是一个双向链表
面试官:B树和B+树的区别是什么呢？
第一：在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定
第二：在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表
二.聚簇索引 非聚簇索引 回表
聚簇索引:将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 必须有,而且只能有一个
二级索引:将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 可以存在多个
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引
- 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引
- 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引
问:什么是聚簇索引什么是非聚簇索引?
将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 必须有,而且只能有一个
将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 可以存在多个
问:知道什么是回表查询嘛?
回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表
三.覆盖索引 超大分页优化
覆盖索引:指查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到.
NySQL超大分页:在数据量比较大的时候,如果进行limit分页查询,在查询时,越往后,分页查询效率越低
优化超大分页的思路:
一般分页查询时,通过创建覆盖索引能够比较好的提升性能,可以通过覆盖索引加子查询形式进行优化
问:什么叫覆盖索引?
覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *，尽量在返回的列中都包含添加索引的字段
问:MySQL超大分页怎么处理?
超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决
先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了
因为查询id的时候，走的覆盖索引，所以效率可以提升很多
四.索引的创建原则
问:索引创建原则有哪些？
个情况有很多，不过都有一个大前提，就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。
还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。
如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致新增改的速度变慢。
五.什么情况下索引会失效
(1).违反了最左前缀法则
如果索引了多列,要遵循最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列,匹配最左前缀法则,走索引:
这里的数据中，name是最左前缀索引
如果跳过name直接查询status和address,会出现错误,因为没有遵守最左前缀法则
直接查询status也不可以
还有一种情况:如果你符合最左前缀法则(即查询了name),但你跳过了status直接查询address,这样的话只有name会生效
(2). 范围查询右边的列,不能使用索引
查询语句中不能使用类似于上述语句"status>'1'" 这种范围查询,范围查询右边的列不可以使用查询
(3).索引上使用运算操作,索引会失效
(4).字符串不加单引号,造成索引失效
主要是由于在查询的时候,没有对字符串加单引号,MySQL的查询优化器,会自动的进行类型转换,造成索引失效
(5).以%开头的like模糊查询,索引失效。如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引会失效
问:什么情况下索引会失效?
六.sql的优化
- 表设计优化
- 索引优化
- SQL语句优化
- 主从复制、读写分离
- 分库分表
表的设计优化
- 比如设置合适的数值(tinyint int bigint),主要是根据实际情况进行选择
- 比如设置合适的字符串类型
SQL语句优化
主从复制、读写分离 如果数据库的使用场景读的操作比较多的时候,为了避免写的操作所造成的性能影响 可以采用读写分离的架构。读写分离解决的是:数据库的写入影响的查询效率
问:sql的优化的经验
这个在项目还是挺常见的，当然如果直说sql优化的话，我们会从这几方面考虑，比如
建表时数据类型的使用、使用索引、sql语句的编写、主从复制，读写分离，还有一个是如果量比较大的话，可以考虑分库分表
七.事务
问：事务的特性是什么？可以详细说一下吗？
ACID，分别指的是：原子性、一致性、隔离性、持久性；我举个例子：
A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要么都成功，要么都失败
在转账的过程中，数据要一致，A扣除了500，B必须增加500
在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰
在转账的过程中，持久性体现在事务提交后，要把数据持久化（可以说是落盘操作）
问:并发事务带来的问题?怎么解决?MySQL的隔离级别?
并发事务问题:脏读、不可重复读、幻读
隔离级别:读未提交、读已提交、可重复读、串行化
八.事务-undo log和redo-log
redo log
重做日志,记录的是事务提交时数据页的物理修改,用来实现事务的持久性
undo log
回滚日志,实现事务一致性和原子性
问:undo log和redo log的区别
其中redo log日志记录的是数据页的物理变化，服务宕机可用来同步数据，而undo log 不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据，比如我们删除一条数据的时候，就会在undo log日志文件中新增一条delete语句，如果发生回滚就执行逆操作
九.MVCC
MVCC,全称Multi-Version Concurrency Control,多版本并发控制。指维护一个数据的多个版本，使读写操作没有冲突
MVCC实现原理
隐藏字段:
在mysql中给每个表都设置了隐藏字段，有一个是trx_id(事务id)，记录每一次操作的事务id，是自增的；另一个字段是roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址
undo log
readview
readview中包含了四个核心字段:
readview版本链数据访问规则
问:事务中的隔离性是如何保证的呢？(你解释一下MVCC)
事务的隔离性是由锁和mvcc实现的。
其中mvcc的意思是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是undo log日志，第三个是readView读视图
隐藏字段是指：在mysql中给每个表都设置了隐藏字段，有一个是trx_id(事务id)，记录每一次操作的事务id，是自增的；另一个字段是roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址
undo log主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表
readView解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是rc隔离级别，每一次执行快照读时生成ReadView，如果是rr隔离级别仅在事务中第一次执行快照读时生成ReadView，后续复用
十.乐观锁和悲观锁(这俩锁与MVCC的区别)
悲观锁:
悲观锁总是假设最坏的情况，认为共享资源每次被访问的时候就会出现问题(比如共享数据被修改)，所以每次在获取资源操作的时候都会上锁，这样其他线程想拿到这个资源就会阻塞直到锁被上一个持有者释放。也就是说，共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程。
悲观锁的缺点:
高并发的场景下，激烈的锁竞争会造成线程阻塞，大量阻塞线程会导致系统的上下文切换，增加系统的性能开销。并且，悲观锁还可能会存在死锁问题（线程获得锁的顺序不当时），影响代码的正常运行。
乐观锁
乐观锁总是假设最好的情况，认为共享资源每次被访问的时候不会出现问题，线程可以不停地执行，无需加锁也无需等待，只是在提交修改的时候去验证对应的资源（也就是数据）是否被其它线程修改了（具体方法可以使用版本号机制或 CAS 算法）
乐观锁的缺点:
高并发的场景下，乐观锁相比悲观锁来说，不存在锁竞争造成线程阻塞，也不会有死锁问题，在性能上往往会更胜一筹。但是，如果冲突频繁发生（写占比非常多的情况），会频繁失败并重试，这样同样会非常影响性能，导致 CPU 飙升。
乐观锁悲观锁与MVCC的区别
MVCC解决的问题是读写互相不阻塞的问题，每次更新都产生一个新的版本，读的话可以读历史版本。
一个读写事务在运行的过程中在访问数据之前先加读/写锁这种实现叫做悲观锁，悲观体现在，先加锁，独占数据，防止别人加锁。乐观锁呢，读写事务，在真正的提交之前，不加读/写锁，而是先看一下数据的版本/时间戳，等到真正提交的时候再看一下版本/时间戳，如果两次相同，说明别人期间没有对数据进行过修改，那么就可以放心提交。乐观体现在，访问数据时不提前加锁。在资源冲突不激烈的场合，用乐观锁性能较好。
十一.主从同步原理
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。
MySQL 主从复制主要用途:
读写分离 在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
高可用HA
架构扩展 随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。
MySQL主从复制的核心就是二进制日志:
二进制日志(binlog)记录了所有的DDL(数据定义语言)语句和DML(数据操纵语言)语句,但不包括数据查询语句,主从复制步骤分为:
第一：主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
第二：从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
第三：从库重做中继日志中的事件，将改变反映它自己的数据
十二.分库分表
MySQL的分库分表是指数据库中随着我们的系统运行，存储在关系型数据库的数据量会越来越大，系统的访问的压力也会随之增大，查询也随着变得缓慢，如果添加索引的话，会发现影响到了新增和删除的性能，如果我们将数据库分散到不同的表上，单表的索引大小就得到了控制，对索引以及表结构的变更会变得很方便和高效；
拆分策略分为两种：垂直和水平:
垂直分库:
以表为依据,根据业务将不同表拆分到不同库中
特点:
- 将业务对数据分级管理、维护、监控、扩展
- 在高并发下，提高磁盘IO和数据量连接数
垂直分表:
以字段为依据,根据字段属性将不同字段拆分到不同表中
特点:
- 冷热数据分离
- 减少IO过度争抢,两表互不影响
水平分库:
将一个库的数据拆分到多个库里
特点:
- 解决了单库大数量,高并发的性能瓶颈问题
- 提高了系统的稳定性和可用性
水平分表:
将一个表的数据拆分到多个表中(可以在同一个库内)
特点:
- 优化单一表数据量过大而产生的性能问题
- 避免IO争抢并减少锁表的几率。
