优化
在MySQL中,如何定位慢查询
出现场景
- 聚合查询
- 多表查询
- 表数据量过大查询
- 深度分页查询
- 表象:页面加载过慢,接口压测响应时间过长
方案一:开源工具
- 调试工具: Arthas
- 运维工具:Prometheus、Skywalking
方案二: MySQL自带慢日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time, 单位:秒,默认10秒)的所有SQL语句的日志。如果要开启慢查询日志,需要在MySQL的配置文件中配置如下信息:
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2s,SQL语句执行时间超过2s,就会视为慢查询,记录慢查询日志
long_query_time=2
SQL语句执行很慢,如何分析
可以采用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息
- 可以通过
key
或者key_len
检查是否命中了索引(索引本身是否有失效的情况) - 通过
type
字段查看sql是否有进一步的优化空间,是否存在全盘索引或全盘扫描 - 通过
extra
建议判断,是否出现了回表的情况。如果出现了,可以尝试添加索引或返回字段来修复
语法:
# 直接在select语句之前加上关键字 explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
- possible_key 当前sql可能会使用到的索引
- key 当前sql实际命中的索引
- key_len索引占用的大小
- Extra 额外的优化建议
- type 这条sql连接的类型,性能由好到差为 NULL(未使用到)、system(mysql内置的表)、const(根据主键查询)、eq_ref(主键索引查询或唯一索引查询)、ref(索引查询,返回不止一条)、range(范围查询、最低要求)、index(索引树扫描)、all(全盘扫描)
索引
了解过索引吗?(什么是索引)
- 索引是帮助MySQL高效获取数据的数据结构(有序)。
- 提高数据检索的效率,降低数据库的IO成本(不需要全盘扫描)
- 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
索引的底层数据结构(InnoDB)
Mysql的InnoDB引擎使用的B+树的数据结构来存储索引
- 相对于二叉树类型的数据结构,B+树的阶数更多,路径更短
- 磁盘读写代价相对于B树更低,非叶子节点只存储指针,叶子节点存储数据
- B+树便于扫库和区间查询,叶子节点是一个双向链表
B-tree
B-Tree,B树是一种多叉平衡查找树,相对于二叉树,B树的每个节点可以有多个分支,即多叉。
以一颗最大度数(max-degree)为5(5阶)的b-tree为例,那这个B树每个节点最多存储4个key
B+树
B+树是在B树的基础上的一种优化,使其更加适合实现外存储索引结构。所有数据都存储在叶子节点上,此外B+树的兄弟节点之间存在双向指针,同层的节点形成双向链表结构。在MySQL中,每个节点中关键字数量默认为100个,一般经历3-4次IO就能找到对应节点。
B树与B+树对比:
- 磁盘读写代价B+树更低(非叶子节点不用存储数据,可以放下更多关键字)
- 查询效率B+树更加稳定
- B+树更加便于扫库和区间查询 (兄弟节点间双向链表)
什么是聚簇索引和非聚簇索引
- 聚簇索引(聚集索引):数据与索引放在一起,B+树的叶子节点保存了整行数据,有且只有一个。一般情况下主键作为聚簇索引
- 非聚簇索引(二级索引):数据与索引分开存储,B+树的叶子节点保存对应的主键,可以有多个
什么是回表查询
通过二级索引找到对应的主键值,到聚集索引中查找整行数据,这个过程就是回表。
什么是覆盖索引
覆盖索引是指查询使用了索引,所返回的所有列在索引中全部能够找到
- 使用id查询,直接利用聚集索引查询,一次索引扫描返回数据,性能高
- 如果返回的列中没有创建索引,有可能会出发回表查询,尽量避免使用select *
MySQL超大分页怎么处理
在数据量比较大时,limit分页查询需要对数据进行排序,效率低。可以通过覆盖索引+子查询的方式来解决。
例如先分页查询数据的id字段,确定id后,再用子查询来过滤。只查询这个id列表中的数据就可以了。因为查询id的时候走的是覆盖索引,所以效率可以提升很多。
索引创建的原则有哪些
- 数据量较大,且查询比较频繁的表 , 至少表中的数据要超过10万以上⭐️
- 常作为查询条件、排序、分组的字段 ⭐️
- 字段内容区分度高
- 内容较长,使用前缀索引
- 尽量使用联合索引 ⭐️ 一条sql查询的返回值,尽量使用覆盖索引,即使一些字段的区分度不高,也放在组合索引后面的字段。
- 控制索引的数量 ⭐️ 添加索引会导致数据库增改变慢。
- 索引列不能存储NULL值,在创建表的时候使用NOT NULL约束
什么情况下索引会失效
- 违反最左前缀法则(多条索引要从第一个开始)
- 多索引使用函数
- 不要在索引列上进行运算操作,索引将失效 (where a+1=10; 无法使用)
- 出现隐式的类型转换,由于类型转换使用到CAST()函数,不存在索引
- 以%开头的Like模糊查询,索引失效
- WHERE子句中使用or关系,且or后的关键字未建立索引。
通常情况下,想要判断是否出现索引失效的情况,可以使用explain执行计划来分析。
Count(*)和Count(1)有什么区别?哪个性能最好?
count(1)、 count(*)、 count(主键字段)在执行的时候,如果表里存在二级索引,优化器就会选择二级索引进行扫描。
所以,如果要执行 count(1)、 count(*)、 count(主键字段) 时,尽量在数据表上建立二级索引,这样优化器会自动采用 key_len 最小的二级索引进行扫描,相比于扫描主键索引效率会高一些。
count(1)、 count(*)在扫描的时候不用读取主键数据,故而比count(主键字段要快)。
如何优化Count
- 使用近似值,如show table status或者explain命令,如
explain select count(*) from Table1;
- 使用额外的表保存计数
对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/right join 不会改变操作顺序。
主从复制,读写分离
如果数据库的使用场景中读的操作比较多的时候,为了避免写的操作所造成的性能影响,可以采用读写分离的架构。读写分离解决的是,数据库的写入影响了查询的效率。
其它
事务
事务的特点
事务是一组操作的集合,满足以下特性:
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败 (
undo log
) - 一致性(Consistency):事务完成时,必须所有的数据都保持一致状态
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行 (
MVCC(多版本并发控制)或锁机制
) - 持久性(Durability):事务一旦提交或回滚,它对数据库中数据的修改是永久的。(
redo log
)
并发事务带来哪些问题? 如何解决这些问题? MySQL的默认隔离级别是?
并发事务的问题
- 脏读:一个事务读取到另一个事务还没有提交的数据
- 不可重复读: 一个事务先后读取同一条记录,但两次读取的数据不同
- 幻读: 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这条数据已经存在。
如何解决这些问题
通过设置MySQL的隔离级别可以解决这些问题,MySQL中存在四种不同的隔离级别
- READ UNCOMMITTED 未提交读 无法解决任何问题
- READ COMMITTED 读已提交 可解决脏读问题
- REPEATABLE READ 可重复读 可解决脏读和不可重复读问题
- SERIALIZABLE 串行化 可解决全部问题,但是性能低
MySQL的默认隔离级别
MySQL默认的隔离级别为可重复读
undo log 和 redo log 区别
缓冲池和数据页
- 缓冲池:主内存中的一个区域,里面可以缓存磁盘上经常操作的真实数据。在执行增删改查操作时,先操作缓冲池中的数据(若缓冲池中没有数据,则从磁盘中加载并缓存),以一定的频率刷新到磁盘,从而减少磁盘IO,加快处理速度
- 数据页: 是InnoDB存储引擎管理的最小单元,每个页的默认大小为16KB。页中存储的是行数据。
redo log
重做日志,记录事务提交时数据页的修改,是用来实现事务的持久性。
该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者在内存中,后者在磁盘中。当事务提交之后会把所有修改信息都存储到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据回复使用。
undo log
回滚日志,用于记录数据被修改前的信息,作用包括两个:提供回滚和MVCC(多版本并发控制)。undo log 是逻辑日志,可以实现事务的一致性和原子性。
- 可以认为每当delete一条记录时,undo log中会记录一条对应的insert记录,反之亦然
- 当update一条记录时,会记录一条对应相反的update记录。当执行rollback时,就可以从undo log中的逻辑记录中读取到相应的内容并进行回滚。
undo log 与 redo log的区别
- redo log:记录的是数据页的物理变化,服务宕机时可以用来同步数据。
- undo log:记录的是逻辑日志,当事务回滚时,可以通过逆操作恢复原来的数据
- redo log保证了事务的持久性, undo log 保证了事务的原子性和一致性
事务的隔离性如何保证(解释一下MVCC)
事务的隔离性主要是由MVCC实现的。
其中MVCC是指多版本并发控制,用于维护一个数据的多个版本,使读写操作没有冲突。其实现主要分为三个部分,包括隐藏字段, undo log日志和readview读视图。
隐藏字段是指在MySQL中每个表都设置了隐藏字段,有一个是trx_id,记录每一次操作的事务id,是自增的;另一个字段是roll_pointer(回滚指针),指向上一个版本的事务版本记录地址。
undo log的主要作用是记录回滚日志,存储老版本数据,在内部会形成一个版本链,在多个事务并行操作某一行记录时,记录不同事务修改数据库的版本,通过roll_pointer指针形成一个链表。
readView解决的是一个事务查询选择版本的问题,其中包括四个重要字段。
一个事务去访问记录的时候,除了自己的更新记录总是可见之外,还有这几种情况:
- 如果记录的
trx_id
值小于 Read View 中的min_trx_id
值,表示这个版本的记录是在创建 Read View 前已经提交的事务生成的,所以该版本的记录对当前事务可见。 - 如果记录的
trx_id
值大于等于 Read View 中的max_trx_id
值,表示这个版本的记录是在创建 Read View 后才启动的事务生成的,所以该版本的记录对当前事务不可见。 - 如果记录的
trx_id
值在 Read View 的min_trx_id
和max_trx_id
之间,需要判断trx_id
是否在m_ids
列表中:- 如果记录的
trx_id
在m_ids
列表中,表示生成该版本记录的活跃事务依然活跃着(还没提交事务),所以该版本的记录对当前事务不可见。 - 如果记录的
trx_id
不在m_ids
列表中,表示生成该版本记录的活跃事务已经被提交,所以该版本的记录对当前事务可见。
- 如果记录的
不同隔离级别读快照的规则不同,最终访问的结果页不一样。如果是rc隔离级别,会在每一次快照读的时候生成ReadView;如果是rr隔离级别,仅在事务中第一次执行快照的时候生成ReadView,后续不断复用第一次生成的ReadView。
锁
MySQL中存在哪些锁
- 全局锁
- 表级锁
- 表锁
- 元数据锁
- 意向锁
- 自增锁
- 行级锁
- Record Lock 记录锁
- Gap Lock 间隙锁
- Next-Key Lock 临键锁,Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定记录本身。
全局锁
- 使用全局锁
// 开启全局锁
flush tables with read lock;
// 释放全局锁
unlock tables;
开启全局锁后,整个数据库处于只读状态,主要应用于全库逻辑备份。
如果引擎支持可重复读,可以在备份前开启事务,避免加全局锁。
表级锁
- 表锁
如果我们想对学生表(t_student)加表锁,可以使用下面的命令:
//表级别的共享锁,也就是读锁;
lock tables t_student read;
//表级别的独占锁,也就是写锁;
lock tables t_stuent write;
// 释放锁
unlock tables
-
元数据锁
无需显示的使用元数据锁,在操作表时会自动加锁- 对一张表进行 CRUD 操作时,加的是 MDL 读锁;
- 对一张表做结构变更操作的时候,加的是 MDL 写锁;
在事务执行期间,MDL一直持有,事务结束后才释放
什么时候mysql会发生死锁
两个事务即使生成的间隙锁的范围是一样的,也不会发生冲突,因为间隙锁目的是为了防止其他事务插入数据,因此间隙锁与间隙锁之间是相互兼容的。
在执行插入语句时,如果插入的记录在其他事务持有间隙锁范围内,插入语句就会被阻塞,因为插入语句在碰到间隙锁时,会生成一个插入意向锁,然后插入意向锁和间隙锁之间是互斥的关系。
如果两个事务分别向对方持有的间隙锁范围内插入一条记录,而插入操作为了获取到插入意向锁,都在等待对方事务的间隙锁释放,于是就造成了循环等待,满足了死锁的四个条件:互斥、占有且等待、不可强占用、循环等待,因此发生了死锁。
MySQL主从同步
主从同步原理
MySQL主从复制的核心是二进制文件
- 二进制日志(BINLOG)记录了所有
DDL
(数据定义语言)语句和DML
(数据操纵语言)语句,但不包括数据查询(SELECT、SHOW)语句。
分库分表
存储引擎
MySQL存储引擎架构
MySQL存储引擎采用插件式架构,支持多种存储引擎。春初引擎是基于表的,而不是基于数据库。默认存储引擎是InnoDB
。
MyISAM和InnoDB有什么区别
- 是否支持行级锁:
MyISAM
只有表级锁(table-level locking),而InnoDB
支持行级锁(row-level locking)和表级锁,默认为行级锁。 - 是否支持事务:
MyISAM
不提供事务支持。InnoDB
提供事务支持,实现了 SQL 标准定义了四个隔离级别,具有提交(commit)和回滚(rollback)事务的能力。并且,InnoDB 默认使用的REPEATABLE-READ
(可重读)隔离级别是可以解决幻读问题发生的(基于 MVCC 和 Next-Key Lock)。 - 是否支持外键:
MyISAM
不支持,而InnoDB
支持。但是实际开发中不建议使用外键,建议在业务代码中进行约束。 - 是否支持数据库异常崩溃后的安全恢复:
MyISAM
不支持,而InnoDB
支持。使用InnoDB
的数据库在异常崩溃后,数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于redo log
。 - 是否支持MVCC:
MyISAM
不支持,而InnoDB
支持。 - InnoDB 的性能比 MyISAM 更强大。