Mysql

如何定位慢查询

1.运维工具skywalking

2.mysql自带的慢日志查询，可设置时间

SQL语句执行慢，如何分析

采用mysql自带的分析工具EXPLAIN

1.通过key与key-len检查是否命中了索引，若本身已经添加了索引也可以判断索引是否有失效的情况

2.通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描

3.通过extra建议判断是否出现了回表的情况，若出现了可以尝试添加索引或修改返回字段来修复

索引

什么是索引？

1.索引是帮助mysql高效获取数据的数据结构

2.提高数据检索的效率，降低数据库的IO成本

3.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引的底层数据结构了解过吗

Mysql的InnoDB引擎采用的是B+树的数据结构来存储

1.阶数更多，路径更短

2.磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据

3.由于叶子节点是一个双向链表，所以B+树便于扫库和区间查询

聚簇（集）索引

什么是聚簇索引，什么是非聚簇索引？

• 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。

• 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

• 说明：InnoDB存储引擎主键使用的索引是聚簇（聚集）索引，叶子节点上存储主键值和数据行（数据点），辅助索引叶子节点上存储主键值。Myisam存储引擎中主键使用的索引称为非聚簇索引，因为其和辅助索引的叶子节点上存储的是指向数据的指针。

• 聚簇索引并不是一种索引类型，而是一种数据存储方式。InnoDB中聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行数据记录，也将聚簇索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分。

• 辅助索引不会对表中的实际数据进行重新组织，而是在独立的数据结构中存储索引信息，用于加速查询操作

• 辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行（辅助索引➡️主键索引➡️数据行）

• 两种索引的比较，聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

• 不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白：

  • 1、为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
  • 2、用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

  InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上。

  若使用“where id = 14”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。（主键查找，直接在主索引的B+树上查找叶子结点上的数据）

  若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（非聚集索引：辅助索引）

回表查询

通过二级索引找对应的主键值，到聚簇索引中查找整行数据，这个过程就是回表

什么是覆盖索引

覆盖索引指查询使用了索引，返回的列必须在索引中全部能够找到

1.使用id查询，直接走聚簇索引查询，一次索引扫描直接返回数据，性能高

2.若返回的列中没有创建索引，有可能会触发回表查询，所以尽量避免使用select *

MySQL超大分页怎么处理

在数据量比较大时，limit分页查询需要对数据进行排序，效率低

解决方案：覆盖索引+子查询

联合索引

Mysql可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。通过联合索引可以有效地加速复杂查询，尤其是在多条件筛选和排序的情况下，不过需要权衡索引带来的查询性能提升与维护索引地开销

索引创建的原则

1.数据量较大且查询较频繁的表

2.常常作为查询条件、排序、分段的字段

3.字段内容区分度高

4.尽量使用联合索引

5.要控制索引的数量，单独的单列索引会增加索引的数量，而联合索引可以减少索引的数量，从而减少维护索引的开销和内存占用。

6.频繁更新的字段不适合创建索引

什么情况下索引会失效

1.违反最左前缀法则

2.范围查询右边的列，不能使用索引

3.不要再索引上进行运算操作

4.字符串不加单引号

5.以%开头的like模糊查询

谈一谈对SQL的优化地经验

1.表的设计优化，数据类型的选择

2.索引优化，索引创建原则

3.sql语句优化，避免索引失效，避免使用select*

4.主从复制、读写分离，不让数据的写入影响读操作

5.分库分表

Mysql事务

四大特性ACID

1.原子性：事务是不可分割的最小单元，要么全部成功要么全部失败

2.一致性：事务完成时，必须使所有的数据保持一致的状态

3.隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境运行

4.持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的（放入磁盘）

并发事务带来哪些问题？怎么解决这些问题？Mysql的默认隔离级别是什么？

1.脏读：一个事务读到另外一个事务还没有提交的数据

2.不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同

3.幻读：一个事务按照条件查询数据时，没有对应的数据行，但在插入数据时，又发现这行数据已经存在，好像出现了幻影

默认策略是RR（REPEATABLE READ）

MVCC是什么？

多版本并发控制，维护一个数据的多个版本，使得读写操作没有冲突。

有三个内容：隐藏字段、undo log和read view

1.隐藏字段：a.trx-id（事务id），记录每一次操作的事务id，是自增的 b.roll_pointer（回滚指针），指上一个版本的事务版本记录地址

2.undo log：a.回滚日志：存储老版本的数据

​ b.版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll- pointer指针形成一个链表

3.read view：解决的是一个事务查询选择版本的问题，根据read view的匹配规则和当前的一些事物id判断该访问哪个版本的数据

不同的隔离级别快照读是不同的，最终访问的结果不一样

RC:每一次执行快照读时生成readview

RR:仅在事务中第一次执行快照读时生成readview后续复用

Mysql主从同步

Mysql主从复制的核心就是二进制日志

二进制文件（binlog）记录了所有的数据定义语言和数据操纵语言，但不包括数据查询语言

1.主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中

2.从库读取主库的二进制日志文件Binlog，写入到从库的中继日志RelayLog

3.从库重做中继日志中的事件，将改变反映它自己的数据

Mysql三大日志

1.binlog日志：用于记录数据库执行的写入性操作（更新数据操作，不包括查询）信息，以二进制的形式保存在磁盘中。binlog时mysql的逻辑日志，并且由server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志

2.redo log日志：是InnoDB存储引擎独有的，它让Mysql拥有了崩溃恢复能力，mysql为了保证数据的一致性，最简单的做法就是在每次事务提交的时候将该事务设计修改的数据页全部刷新到磁盘中。但是这样做有严重的性能问题。解决办法是先将数据写入内存中，在批量把内存中的数据统一刷回磁盘。为了避免将数据刷回磁盘过程中，因为掉电或系统故障带来的数据丢失问题，InnoDB采用redolog解决这个问题。

redelog包括两部分：一个是内存中的日志缓冲（redo log buffer），另一个是磁盘上的日志文件（redo log file）。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写至redo log file。这种先写日志后写磁盘的技术就是WAL（Write-Ahead Logging）技术。binlog日志只用于归档，只依靠binlog是没有crash-safe能力的。但只有redo log也不行，因为redo log是InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要binlog和redolog二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

3.undo log回滚日志：记录了数据的逻辑变化，是实现MVCC的关键。