MySQL篇

场景

MySQL-优化

在MySQL中，如何定位慢查询?

聚合查询
多表查询
表数据量过大查询
深度分页查询

表象:页面加载过慢、接口压测响应时间过长(超过1s)

如何定位慢查询 ?
方案一：开源工具
调试工具: Arthas
运维工具: Prometheus、Skywalking
方案二： MySQL自带慢日志
慢查询日志记录了所有执行时间超过指定参数 (long_query_time，单位:秒，默认10秒)的所有SQL语句的日志如果要开启慢查询日志，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
开启MySQL慢日志查询开关
slow_query_log=1
设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息
/var/lib/mysql/localhost-slow.log

总结：

  1. 介绍一下当时产生问题的场景(我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟)
  2. 我们系统中当时采用了运维工具 ( Skywalking )，可以监测出哪个接口，最终因为是sql的问题
  3. 在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中(调试阶段)

那这个SQL语句执行很慢,如何分析呢?

一个SQL语句执行很慢, 如何分析
可以采用EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息

possible_key 当前sql可能会使用到的索引 key 当前sql实际命中的索引 key_len索占用的大小 Extra额外的优化建议 通过key和key_len 查看是否可能会命中索引 type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range index、all system：查询系统中的表 const：根据主键查询 eq_ref：主键索引查询或唯一索引查询ref:索引查询 range：范围查询 index：索引树扫描 all：全盘扫描

总结：
可以采用MySQL自带的分析工具EXPLAIN
通过key和key_len检查是否命中了索引(索引本身存在是否有失效的情况)
通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

了解过索引吗?(什么是索引)

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构(B+树)，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引的底层数据结构了解过嘛 ?
B+树
B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，lnnoDB存储引擎就是用B+Tree实现其索引结构
B树与B+树对比:

  1. 磁盘读写代价B+树更低;
  2. 查询效率B+树更加稳定;
  3. B+树便于扫库和区间查询

总结：
索引(index)是帮助MySQL高效获取数据的数据结构(有序)
提高数据检索的效率，降低数据库的IO成本(不需要全表扫描)
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

MySQL的InnoDB引采用的B+树的数据结构来存储索引
阶数更多，路径更短
磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据
B+树便于扫库和区间查询，叶子节点是一个双向链表

什么是聚簇索引什么是非聚簇索引?

什么是聚集索引，什么是二级索引(非聚集索引)
什么是回表?

聚集索引选取规则:
如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一(UNIQUE)索作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

总结：
聚簇索引(聚集索引):数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个
非聚簇索引(二级索引):数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个
通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表

知道什么叫覆盖索引嘛?

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到

MYSQL超大分页怎么处理 ?

可以使用覆盖索引解决
在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。
因为，当在进行分页查询时，如果执行 limit 9000000,10，此时需要MySQL排序前9000010 记录，仅仅返回9000000- 9000010 的记录，其他记录丢弃，查询排序的代价非常大。

优化思路: 一般分页查询时，通过创建 覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

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

  1. 使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
  2. 如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *
    MYSQL超大分页怎么处理?
    在数据量比较大时，limit分页查询，需要对数据进行排序，效率低题:
    覆盖索引+子查询

索引创建原则有哪些？

  1. 先陈述自己在实际的工作中是怎么用的
  2. 主键索
  3. 唯一索引
  4. 根据业务创建的索(复合索引)

索引创建原则有哪些？
1). 针对于数据量较大，且查询比较频繁的表建立索引。单表超过10万数据(增加用户体验)
2). 针对于常作为查询条件 (where)、排序 (order by)、分组 (group by) 操作的字段建立索引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7). 如果索引列不能存储NULL值，请在创建表时使用NOT ULL约束。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

总结：
1).数据量较大，且查询比较频繁的表 重要
2).常作为查询条件、排序、分组的字段 重要
3).字段内容区分度高
4).内容较长，使用前缀索引
5).尽量联合索 重要
6).要控制索引的数量 重要
7)如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它

什么情况下索引会失效？

索引失效的情况有很多，可以说一些自己遇到过的，不要张口就得得得说一堆背诵好的面试题(适当的思考一下，回想一下，更真实)
给tb seller创建联合索引，字段顺序: name，status，address

那快读判断索引是否失效了呢? 执行计划explain
1).违反最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引；

违反最左前缀法则，索引失效：

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

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

根据前面的两个字段 name，status 查询是走索引的，但是最后一个条件address 没有用到索引

3).不要在索引列上进行运算操作，索引将失效

4).字符串不加单引号，造成索引失效。

由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

5).以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

总结：

  1. 违反最左前缀法则
  2. 范围查询右边的列，不能使用索引
  3. 不要在索引列上进行运算操作， 索引将失效
  4. 字符串不加单引号，造成索引失效。(类型转换)
  5. 以%开头的Like模糊查询，索引失效

谈一谈你对sql的优化的经验

  1. 表的设计优化
  2. 索引优化 参考优化创建原则和索引失效
  3. SQL语句优化
  4. 主从复制、读写分离
  5. 分库分表

表的设计优化(参考阿里开发手册《嵩山版》)

  1. 比如设置合适的数值(tinyint int bigint)，要根据实际情况选择
  2. 比如设置合适的字符串类型(char和varchar) char定长效率高，varchar可变长度，效率稍低

SQL语句优化

  1. SELECT语句务必指明字段名称 (避免直接使用select *)
  2. SQL语句要避免造成索引失效的写法
  3. 尽量用union all代替union union会多一次过滤，效率低
  4. 避免在where子句中对字段进行表达式操作
  5. Join优化 能用innerjoin 就不用left join right join，如必须使用一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序

主从复制、读写分离
如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询的效率。

总结：

  1. 表的设计优化，数据类型的选择
  2. 索引优化，索引创建原则
  3. sql语句优化，避免索引失效，避免使用select *….
  4. 主从复制、读写分离，不让数据的写入，影响读操作
  5. 分库分表

MySQL-事务

事务的特性是什么?可以详细说一下吗?

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

ACID是什么? 可以详细说一下吗?

  1. 原子性(Atomicity): 事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  2. 一致性(Consistency): 事务完成时，必须使所有的数据都保持一致状态。
  3. 隔离性(lsolation): 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  4. 持久性 (Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务带来哪些问题? 怎么解决这些问题呢? MySQL的默认隔离级别是?

  1. 并发事务问题:脏读、不可重复读、幻读
  2. 隔离级别: 读未提交、读已提交、可重复读、串行化

并发事务问题
脏读：一个事务读到另外一个事务还没有提交的数据
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。

怎么解决并发事务的问题呢?
解决方案:对事务进行隔离

总结：
并发事务带来哪些问题? 怎么解决这些问题呢? MySQL的默认隔离级别是?

并发事务的问题:

  1. 脏读:一个事务读到另外一个事务还没有提交的数据。
  2. 不可重复读:一个事务先后读取同一条记录，但两次读取的数据不同
  3. 幻读:一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

隔离级别:
READ UNCOMMITTED 未提交读 脏读、不可重复读、幻读
READ COMMITTED 读已提交 不可重复读、幻读
REPEATABLE READ 可重复读 幻读
SERIALIZABLE 串行化

undo log和redo log的区别

  1. 缓冲池(bufer pool):主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度

  2. 数据页(page):是lnnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据

redo log
重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性
该日志文件由两部分组成:重做日志缓冲(redo og bufer)以及重做日志文件(redo og fle),前者是在内存中，后者在磁盘中当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时，进行数据恢复使用。

undo log
回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚 和 MVCC(多版本并发控制)。undo og和redo log记录物理日志不一样，它是逻辑日志。

  1. 可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然
  2. 当update一条记录时，它记录一条对应相反的update记录。当执行rolback时，就可以从undo og中的逻辑记录读取到相应的内容并进行回滚。
    undo log可以实现事务的一致性和原子性

总结：
undo log和redo log的区别

  1. redo log:记录的是数据页的物理变化，服务宕机可用来同步数据

  2. undo log:记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据

  3. redo log保证了事务的持久性，undolog保证了事务的原子性和一致性

好的，事务中的隔离性是如何保证的呢?

锁:排他锁(如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁)
mvcc:多版本并发控制

解释一下MVCC
全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突

MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView。

MVCC–实现原理

  1. 记录中的隐藏字段

  2. undo log
    回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
    当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
    而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。

  3. undo log版本链

    不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

  4. readview
    ReadView(读视图)是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id。
    当前读
    读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:
    select … lock in share mode(共享锁)，
    select …for update、
    update、 insert、 delete(排他锁)都是一种当前读。

快照读
简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
Read Committed: 每次select，都生成一个快照读。
Repeatable Read: 开启事务后第一个select语句才是快照读的地方。

ReadView中包含了四个核心字段：

版本链数据访问规则：
trx_id: 代表是当前事务ID

  1. trx_id == creator_trx_id?可以访问该版本 成立，说明数据是当前这个事务更改的。
  2. trx_id < min_trx_id?可以访问该版本 成立，说明数据已经提交了。
  3. trx_id>max_trx_id ?不可以访问该版本 成立，说明该事务是在ReadView生成后才开启。
  4. min_trx_id <=trx_id<= max_trx_id?如果trx_id不在m_ids中是可以访问该版本的 成立，说明数据已经提交。

不同的隔离级别，生成ReadView的时机不同
READ COMMITTED: 在事务中每一次执行快照读时生成ReadView。
REPEATABLE READ: 仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

RC隔离级别下，在事务中每一次执行快照读时生成ReadView。

总结：
事务中的隔离性是如何保证的呢?(你解释一下MVCC)
MySQL中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突
隐藏字段:
trx_id(事务id)，记录每一次操作的事务id，是自增的
roll_pointer(回滚指)，指向上一个版本的事务版本记录地址

undo log:
回滚日志，存储老版本数据
版本链:多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表

readView解决的是一个事务查询选择版本的问题

MySQL主从同步原理

MySQL主从复制的核心就是二进制日志
二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句，但不包括数据查询SELECT、SHOW)语句

  1. 主库在事务提交时，会把数据变更记录在二进制日志文件 Binog 中
  2. 从库读取主库的二进制日志文件 Binlog，写入到从库的中继日志 Relay Log
  3. 从库重做中继日志中的事件，将改变反映它自己的数据

你们项目用过分库分表吗

分库分表的时机

  1. 前提，项目业务数据逐渐增多，或业务发展比较迅速 单表的数据量达1000W或20G以后
  2. 优化已解决不了性能问题(主从读写分离、查询索引…)
  3. IO瓶颈 (磁盘IO、网络IO)、CPU瓶颈(聚合查询、连接数太多)

垂直分库
垂直分库:以表为依据，根据业务将不同表拆分到不同库中。

特点:

  1. 按业务对数据分级管理、维护、监控、扩展
  2. 在高并发下，提高磁盘IO和数据量连接数

垂直分表
垂直分表:以字段为依据，根据字段属性将不同字段拆分到不同表中。
拆分规则：

  1. 把不常用的字段单独放在一张表
  2. 把text，blob等大字段拆分出来放在附表中 特点:
  3. 冷热数据分离
  4. 减少IO过渡争抢，两表互不影响

水平分库
水平分库:将一个库的数据拆分到多个库中。
路由规则：

  1. 根据id节点取模
  2. 按id也就是范围路由，节点1(1-100万)节点2(100万-200万) 特点:
  3. 解决了单库大数量，高并发的性能瓶颈问题
  4. 提高了系统的稳定性和可用性

水平分表
水平分表:将一个表的数据拆分到多个表中(可以在同一个库内)。

特点:

  1. 优化单一表数据量过大而产生的性能问题;
  2. 避免10争抢并减少锁表的几率;

分库分表的策略有哪些
新的问题和新的技术
分库之后的问题:

  1. 分布式事务一致性问题
  2. 跨节点关联查询
  3. 跨节点分页、排序函数
  4. 主键避重

分库分表中间件:

  1. sharding-sphere
  2. mycat

总结：
业务介绍：

  1. 根据自己简历上的项目，想一个数据量较大业务(请求数多或业务累积大)
  2. 达到了什么样的量级(单表1000万或超过20G)
    具体拆分策略：
    1，水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题
    2，水平分表，解决单表存储和性能的问题
    3，垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数
    4，垂直分表，冷热数据分离，多表互不影响