定位慢查询
慢查询
表象:页面加载过慢、接口压测响应时间过长(超过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
日志位置:/var/lib/mysql/localhost-slow.log
面试题
面试官: MySQL中,如何定位慢查询?
回答:
- 嗯,我们当时在做压力测试时发现有些接口响应时间非常慢,超过了2秒。
- 因为我们的系统部署了运维监控系统Skywalking,在它的报表展示中可以看到哪个接口慢,并且能分析出接口中哪部分耗时较多,包括具体的SQL执行时间,这样就能定位到出现问题的SQL。
- 如果没有这种监控系统,MySQL本身也提供了慢查询日志功能。
- 可以在MySQL的系统配置文件中开启慢查询日志,并设置SQL执行时间超过多少就记录到日志文件,比如我们之前项目设置的是2秒,超过这个时间的SQL就会记录在日志文件中,我们就可以在那里找到执行慢的SQL。
分析慢查询
采用 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息
语法
在 SELECT 语句前加上 EXPLAIN/DESC
参数
- possible_key 当前sql可能会使用到的索引
- key 当前sql实际命中的索引
- key_len 索引占用的大小
- Extra 额外的优化建议
- Using where、Using Index--查找使用了索引,需要的数据都在索引列中能找到,不需要回表查询数据
- Using index condition--查找使用了索引,但是需要回表查询数据
- 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
建议可以判断是否出现回表情况,如果出现,可以尝试添加索引或修改返回字段来优化。
存储引擎
概念
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。
存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。
特性 | MyISAM | InnoDB | MEMORY |
---|---|---|---|
事务安全 | 不支持 | 支持 | 不支持 |
锁机制 | 表锁 | 表锁/行锁 | 表锁 |
外键 | 不支持 | 支持 | 不支持 |
MySql体系结构
InnoDB特点
概念
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在 MySQL 5.5 之后,InnoDB是默认的 MySQL 存储引擎。
特点
- DML操作遵循ACID模型,支持事务
- 行级锁,提高并发访问性能
- 支持外键,FOREIGN KEY约束,保证数据的完整性和正确性
文件
- xxx.ibd:xxx代表的是表名,innoDB引擎的每张表都会对应这样一个表空间文件,存储该表的表结构(frm、sdi)、数据和索引。
- xxx.frm:存储表结构(MySQL8.0时,合并在表名.ibd中)
面试题
面试官: MYSQL支持的存储引擎有哪些,有什么区别?
回答:
- 在mysql中提供了很多的存储引擎,比较常见有InnoDB、MyISAM、Memory
- InnoDB存储引擎是mysql5.5之后是默认的引擎,它支持事务、外键、表级锁和行级锁
- MyISAM是早期的引擎,它不支持事务、只有表级锁、也没有外键,用的不多
- Memory主要把数据存储在内存,支持表级锁,没有外键和事务,用的也不多
索引
概念
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构(B+树),这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
索引的数据结构
B+Tree
:优化BTree(非叶子节点:索引+指针、叶子节点:索引+数据[地址])
B+Tree的好处
- 降低树的高度
- 叶子节点按照索引排好序,支持范围查找,速度会很快
- 千万条数据,只有2次磁盘IO
聚簇索引和非聚簇索引
特点
分类 | 含义 | 特点 |
---|---|---|
聚集索引 | 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
二级索引 | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚簇索引选取规则
- 如果存在主键,主键索引就是聚簇索引。
- 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚簇索引。
- 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚簇索引。
回表查询
通过二级索引找到对应的主键值,到聚簇索引中查找整行数据,这个过程就是回表
索引覆盖
概念
覆盖索引是指查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到。
MySql超大分页处理
一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化
SELECT * FROM t,(SELECT id FROM t ORDER BY id LIMIT 9000000,10) a where t.id = a.id;
索引创建原则
- 针对于数据量较大,且查询比较频繁的表建立索引。
- 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
- 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
- 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
- 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
- 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
索引失效
- 违反最左前缀法则:如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列。匹配最左前缀法则,走索引。
- 范围查询右边的列,不能使用索引 。
- 不要在索引列上进行运算操作,索引将失效。
- 字符串不加单引号,造成索引失效。
- 以%开头的Like模糊查询,索引失效。如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
面试题
面试官: 了解过索引吗?(什么是索引)
回答:
嗯,索引在项目中非常常见,它是一种帮助MySQL高效获取数据的数据结构,主要用来提高数据检索效率,降低数据库的I/O成本。同时,索引列可以对数据进行排序,降低数据排序的成本,也能减少CPU的消耗。
面试官: 索引的底层数据结构了解过吗?
回答:
- MySQL的默认存储引擎InnoDB使用的是B+树作为索引的存储结构。
- 选择B+树的原因包括:节点可以有更多子节点,路径更短;磁盘读写代价更低,非叶子节点只存储键值和指针,叶子节点存储数据;B+树适合范围查询和扫描,因为叶子节点形成了一个双向链表。
面试官: B树和B+树的区别是什么呢?
回答:
B树和B+树的主要区别在于:
- B树的非叶子节点和叶子节点都存放数据,而B+树的所有数据只出现在叶子节点,这使得B+树在查询时效率更稳定。
- B+树在进行范围查询时效率更高,因为所有数据都在叶子节点,并且叶子节点之间形成了双向链表。
面试官: 什么是聚簇索引什么是非聚簇索引?
回答:
聚簇索引是指数据与索引放在一起,B+树的叶子节点保存了整行数据,通常只有一个聚簇索引,一般是由主键构成。
非聚簇索引则是数据与索引分开存储,B+树的叶子节点保存的是主键值,可以有多个非聚簇索引,通常我们自定义的索引都是非聚簇索引。
面试官: 知道什么是回表查询吗?
回答:
回表查询是指通过二级索引找到对应的主键值,然后再通过主键值查询聚簇索引中对应的整行数据的过程。
面试官: 知道什么叫覆盖索引吗?
回答:
覆盖索引是指在SELECT查询中,返回的列全部能在索引中找到,避免了回表查询,提高了性能。使用覆盖索引可以减少对主键索引的查询次数,提高查询效率。
面试官: MySQL超大分页怎么处理?
回答:
超大分页通常发生在数据量大的情况下,使用 LIMIT
分页查询且需要排序时效率较低。
可以通过覆盖索引和子查询来解决。
首先查询数据的ID字段进行分页,然后根据ID列表用子查询来过滤只查询这些ID的数据,因为查询ID时使用的是覆盖索引,所以效率可以提升。
面试官: 索引创建原则有哪些?
回答:
创建索引的原则包括:
- 表中的数据量超过10万以上时考虑创建索引。
- 选择查询频繁的字段作为索引,如查询条件、排序字段或分组字段。
- 尽量使用复合索引,覆盖SQL的返回值。
- 如果字段区分度不高,可以将其放在组合索引的后面。
- 对于内容较长的字段,考虑使用前缀索引。
- 控制索引数量,因为索引虽然可以提高查询速度,但也会影响插入、更新的速度。
面试官: 什么情况下索引会失效?
回答:
索引可能在以下情况下失效:
- 没有遵循最左匹配原则。
- 使用了模糊查询且
%
号在前面。 - 在索引字段上进行了运算或类型转换。
- 使用了复合索引但在中间使用了范围查询,导致右边的条件索引失效。
SQL优化
表设计优化
参考阿里开发手册
比如设置合适的数值(tinyint int bigint),要根据实际情况选择
比如设置合适的字符串类型(char和varchar)char定长效率高,varchar可变长度,效率稍低
SQL语句优化
- SELECT语句务必指明字段名称(避免直接使用select * )
- SQL语句要避免造成索引失效的写法
- 尽量用union all代替union union会多一次过滤,效率低
- 避免在where子句中对字段进行表达式操作
- Join优化:能用inner join就不用left join、right join,如必须使用 一定要以小表为驱动,内连接会对两个表进行优化,优先把小表放到外边,把大表放到里边。left join 或 right join,不会重新调整顺序。
主从复制、读写分离
如果数据库的使用场景读的操作比较多的时候,为了避免写的操作所造成的性能影响可以采用读写分离的架构。
读写分离解决的是,数据库的写入,影响了查询的效率。
面试题
面试官: SQL的优化经验有哪些?
回答:
SQL优化可以从以下几个方面考虑:
- 建表时选择合适的字段类型。
- 使用索引,遵循创建索引的原则。
- 编写高效的SQL语句,比如避免使用
SELECT *
,尽量使用UNION ALL
代替UNION
,以及在表关联时使用INNER JOIN
。 - 采用主从复制和读写分离提高性能。
- 在数据量大时考虑分库分表。
面试官: 创建表的时候,你们是如何优化的呢?
回答:
创建表时,我们主要参考《嵩山版》开发手册,选择字段类型时结合字段内容,比如数值类型选择 TINYINT
、INT
、BIGINT
等,字符串类型选择 CHAR
、VARCHAR
或 TEXT
。
面试官: 在使用索引的时候,是如何优化呢?
回答:
在使用索引时,我们遵循索引创建原则,确保索引字段是查询频繁的,使用复合索引覆盖SQL返回值,避免在索引字段上进行运算或类型转换,以及控制索引数量。
面试官: 你平时对SQL语句做了哪些优化呢?
回答:
我对SQL语句的优化包括指明字段名称而不是使用 SELECT *
,避免造成索引失效的写法,聚合查询时使用 UNION ALL
代替 UNION
,表关联时优先使用 INNER JOIN
,以及在必须使用 LEFT JOIN
或 RIGHT JOIN
时,确保小表作为驱动表。
事务
概念
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
ACID
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另外一个事务还没有提交的数据。 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”。 |
隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted 未提交读 | √ | √ | √ |
Read committed 读已提交 | × | √ | √ |
Repeatable Read(默认) 可重复读 | × | × | √ |
Serializable 串行化 | × | × | × |
面试题
面试官: 事务的特性是什么?可以详细说一下吗?
回答:
- 事务的特性是ACID,即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。
- 例如,A向B转账500元,这个操作要么都成功,要么都失败,体现了原子性。
- 转账过程中数据要保持一致,A扣除了500元,B必须增加500元。
- 隔离性体现在A向B转账时,不受其他事务干扰。
- 持久性体现在事务提交后,数据要被持久化存储。
面试官: 并发事务带来哪些问题?
回答:
- 并发事务可能导致脏读、不可重复读和幻读。
- 脏读是指一个事务读到了另一个事务未提交的“脏数据”。
- 不可重复读是指在一个事务内多次读取同一数据,由于其他事务的修改导致数据不一致。
- 幻读是指一个事务读取到了其他事务插入的“幻行”。
面试官: 怎么解决这些问题呢?MySQL的默认隔离级别是?
回答:
解决这些问题的方法是使用事务隔离。
MySQL支持四种隔离级别:
- 未提交读(READ UNCOMMITTED):解决不了所有问题。
- 读已提交(READ COMMITTED):能解决脏读,但不能解决不可重复读和幻读。
- 可重复读(REPEATABLE READ):能解决脏读和不可重复读,但不能解决幻读,这也是MySQL的默认隔离级别。
- 串行化(SERIALIZABLE):可以解决所有问题,但性能较低。
日志
缓冲池(buffer pool):主内存中的一个区域,里面可以缓存磁盘上经常操作的真实数据,在执行增删改查操作时,先操作缓冲池中的数据(若缓冲池没有数据,则从磁盘加载并缓存),以一定频率刷新到磁盘,从而减少磁盘IO,加快处理速度。
数据页(page):是InnoDB存储引擎磁盘管理的最小单元,每个页的大小默认为16KB。页中存储的是行数据。
redo log
重做日志,记录的是事务提交时数据页的物理修改,是用来实现事务的持久性。
该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者是在内存中,后者在磁盘中。
当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。
undo log
回滚日志,用于记录数据被修改前的信息,作用包含两个:提供回滚和MVCC(多版本并发控制)。
undo log和redo log记录物理日志不一样,它是逻辑日志。
可以认为当delete一条记录时,undo log中会记录一条对应的insert记录,反之亦然,当update一条记录时,它记录一条对应相反的update记录。
当执行rollback时,就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
undo log可以实现事务的一致性和原子性。
MVCC
概念
全称Multi-Version Concurrency Control,多版本并发控制。
指维护一个数据的多个版本,使得读写操作没有冲突MVCC的具体实现,主要依赖于数据库记录中的隐式字段、undo log日志、readView。
原理
记录中的隐藏字段
隐藏字段 | 含义 |
---|---|
DB_TRX_ID | 最近修改事务ID,记录插入这条记录或最后一次修改该记录的事务ID。 |
DB_ROLL_PTR | 回滚指针,指向这条记录的上一个版本,用于配合undo log,指向上一个版本。 |
DB_ROW_ID | 隐藏主键,如果表结构没有指定主键,将会生成该隐藏字段。 |
undo log
回滚日志,在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候,产生的undo log日志只在回滚时需要,在事务提交后,可被立即删除。
update、delete的时候,产生的undo log日志不仅在回滚时需要,mvcc版本访问也需要,不会立即被删除。
undo log版本链
readview
概念
ReadView(读视图)是快照读SQL执行时MVCC提取数据的依据,记录并维护系统当前活跃的事务(未提交的)id。
核心字段
字段 | 含义 |
---|---|
m_ids | 当前活跃的事务ID集合 |
min_trx_id | 最小活跃事务ID |
max_trx_id | 预分配事务ID,当前最大事务ID+1(因为事务ID是自增的) |
creator_trx_id | ReadView创建者的事务ID |
不同的隔离级别,生成ReadView的时机不同:
- READ COMMITTED:在事务中每一次执行快照读时生成ReadView。
- REPEATABLE READ:仅在事务中第一次执行快照读时生成ReadView,后续复用该ReadView。
当前读
读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁。
我们日常的操作,如:select ... lock in share mode(共享锁),select ... for update、update、insert、delete(排他锁)都是一种当前读。
快照读
简单的select(不加锁)就是快照读,快照读,读取的是记录数据的可见版本,有可能是历史数据,不加锁,是非阻塞读。
- Read Committed:每次select,都生成一个快照读。
- Repeatable Read:开启事务后第一个select语句才是快照读的地方。
面试题
面试官: undo log和redo log的区别是什么?
回答:
redo log记录的是数据页的物理变化,用于服务宕机后的恢复,保证事务的持久性。
undo log记录的是逻辑日志,用于事务回滚时恢复原始数据,保证事务的原子性和一致性。
面试官: 事务中的隔离性是如何保证的呢?(你解释一下MVCC)
回答:
- 事务的隔离性通过锁和多版本并发控制(MVCC)来保证。
- MVCC通过维护数据的多个版本来避免读写冲突。
- 底层实现包括隐藏字段、undo log和read view。隐藏字段包括trx_id和roll_pointer。undo log记录了不同版本的数据,通过roll_pointer形成版本链。
- read view定义了不同隔离级别下的快照读,决定了事务访问哪个版本的数据。
主从同步、分库分表
主从同步
概念
MySQL主从复制的核心就是二进制日志。
二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和DML(数据操纵语言)语句,但不包括数据查询(SELECT、SHOW)语句。
步骤
- Master主库在事务提交时,会把数据变更记录在二进制日志文件Binlog中。
- 从库读取主库的二进制日志文件Binlog,写入到从库的中继日志Relay Log 。
- slave重做中继日志中的事件,将改变反映它自己的数据。
分库分表
目的
- 分担访问压力
- 解决存储压力
时机
- 单表的数据量达1000W或20G以后。
- 优化已解决不了性能问题。
- IO瓶颈(磁盘IO、网络IO)、CPU瓶颈(聚合查询、连接数太多)。
垂直拆分
垂直分库
概念
以表为依据,根据业务将不同表拆分到不同库中。
特点
- 按业务对数据分级管理、维护、监控、扩展
- 在高并发下,提高磁盘IO和数据量连接数
垂直分表
概念
以字段为依据,根据字段属性将不同字段拆分到不同表中。
特点
- 冷热数据分离。
- 减少IO过渡争抢,两表互不影响。
水平拆分
水平分库
概念
将一个库的数据拆分到多个库中。
特点
- 解决了单库大数量,高并发的性能瓶颈问题。
- 提高了系统的稳定性和可用性。
水平分表
概念
将一个表的数据拆分到多个表中(可以在同一个库内)。
特点
- 优化单一表数据量过大而产生的性能问题。
- 避免IO争抢并减少锁表的几率。
面试题
面试官: MySQL主从同步原理是什么?
回答:
MySQL主从复制的核心是二进制日志(Binlog)。
步骤如下:
- 主库在事务提交时记录数据变更到Binlog。
- 从库读取主库的Binlog并写入中继日志(Relay Log)。
- 从库重做中继日志中的事件,反映到自己的数据中。
面试官: 你们项目用过MySQL的分库分表吗?
回答:
我们采用微服务架构,每个微服务对应一个数据库,是根据业务进行拆分的,这个其实就是垂直拆分。
面试官: 那你之前使用过水平分库吗?
回答:
使用过。
当时业务发展迅速,某个表数据量超过1000万,单库优化后性能仍然很慢,因此采用了水平分库。
我们首先部署了3台服务器和3个数据库,使用mycat进行数据分片。
旧数据也按照ID取模规则迁移到了各个数据库中,这样各个数据库可以分摊存储和读取压力,解决了性能问题。