面试常问
如果业务中出现查询需要很长时间才返回的情况,可以使用慢查询日志进行慢语句查询
MySQL 的慢查询日志记录的内容是:在 MySQL 中响应时间超过参数 long_query_time(单位秒,默认值 10)设置的值并且扫描记录数不小于 min_examined_row_limit(默认值0)的语句。
默认情况下,慢查询日志中不会记录管理语句,如果需要记录的请做如下设置,设置log_slow_admin_statements = on 让管理语句中的慢查询也会记录到慢查询日志中。默认情况下,也不会记录查询时间不超过 long_query_time 但是不使用索引的语句,可通过配置log_queries_not_using_indexes = on 让不使用索引的 SQL 都被记录到慢查询日志中(即使查询时间没超过 long_query_time 配置的值)。
一般使用慢查询的步骤如下:
mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)
mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like "datadir";
mysql> show global variables like "slow_query_log_file";
SELECT * FROM mysql.slow_log
或者打开日志文件查看
有时慢查询正在执行,已经导致数据库负载偏高了,而由于慢查询还没执行完,因此慢查询日志还看不到任何语句。此时可以使用 show processlist 命令判断正在执行的慢查询。show processlist 显示哪些线程正在运行
explian可以分析某个语句的状态,使用很简单,直接在查询语句前加上explain就可以,例如
mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)
expain出来的信息有10列,分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释:
列名 | 描述 |
---|---|
id | 在一个大的查询语句中,每个SELECT关键字都对应一个唯一的id |
select_type | SELECT关键字对应的查询的类型 |
table | 表名 |
partitions | 匹配的分区信息 |
type | 针对单表的访问方法 |
possible_keys | 可能用到的索引 |
key | 实际使用的索引 |
key_len | 实际使用的索引长度 |
ref | 当使用索引列等值查询时,与索引列进行等值匹配的对象信息 |
rows | 预估的需要读取的记录条数 |
filtered | 针对预估的需要读取的记录,经过搜索条件过滤后剩余记录条数的百分比 |
Extra | 一些额外的信息 |
一般如果建立了索引,我们会看type字段,这个字段有如下:
以上介绍的顺序是由快到慢的
有时候查询慢不一定是写的有问题,随着业务拓展,数据越来越多,查询效率也会随之降低,这时候可能就需要进行分表分库操作来缓解
当查询慢的主要原因是数据量太大时,一般采用分表
如果单库达到了性能瓶颈,同时并发量还挺高的时候,考虑采用分库的方法
优化的地方都离不开局部性原理,对于经常访问的热点数据,可以采用缓存的方式存储,先访问缓存,如果缓存没有命中,再到数据库里查找
对于使用缓存和数据库保证一致性的方法在另一篇文章有详细介绍
https://mp.weixin.qq.com/s/4W7vmICGx6a_WX701zxgPQ
上面说到的分库也算是分布式下的一种情形,不过每个节点保存的不同数据,用于分担单机压力。这里说的主要是主从结构的分布式,为了实现高可用和缓解高并发的情况
数据库主从结构可以是一对多,一对一,多对一
MySQL 主从复制涉及到三个线程:
一个在主节点的线程:log_dump_thread
从库会生成两个线程:一个 I/O 线程,一个 SQL 线程
主库会生成一个 log dump 线程,用来给从库 I/O 线程传 Binlog 数据。
从库的 I/O 线程会去请求主库的 Binlog,并将得到的 Binlog 写到本地的 relay log (中继日志)文件中。
SQL 线程,会读取 relay log 文件中的日志,并解析成 SQL 语句逐一执行。
分布式事务一般有两段式提交和三段式提交,这两种提交方式可以参考这里
对于两段式提交的过程如下: