优化器

查询优化器

Optimizer–查询优化器, 获取执行 SQL 查询的最佳方案(并非一定最佳). 大多数的 optimizer 总是或多或少的在所有可能的”查询评估方案”中搜索最佳方案. 一般而言, 优化器所需要 Check 的”查询评估方案”越少, 它编译一个查询所需要发费的时间越少, 但是优化器可能为了中和查询或者预估时(为了避免遍历所有的-查询评估方案)错过最佳方案, 所以尽可能确保”查询评估方案”的数量, 例如联合查询的表数量.

工具

  1. 命令explain: 查看 SQL 的执行计划, 用于调试, 在进行数据库设计, 出现慢查询 BUG 时, 使用explain来查看执行计划, 有针对性的解决问题.

  2. Optimizer Trace, 另外可以通过配置, 查看 MYSQL Optimizer的”查询评估方案”选择过程, 从而找出问题, 配置如下:

    1
    2
    3
    4
    show variables like "%trace%";
    set optimizer_trace = "enabled=on";
    # 执行查询语句
    select * from information_schema.optimizer_trace\G

hint: 使用hint强制走某一个索引, 手动为Optimizer选择某一条固定的道路(改革开发总设计, 共产主义的基本道路是不会变的), 但是有时候不建议这种强关联:

  • 索引不存在时, 直接报错
  • 随着数据分布的改变, 可能执行计划最优解发生变动.

Monitor:

  • 首先, 建立健全的监控体系, 当慢查询数量累计到一定程度时, 应该迅速的解决并寻找最本质的原因并解决;
  • 其次, 尽可能从索引结构上优化, 去除不必要的索引, 调整参数来优化.

排查逻辑

处理思路

一般而言，线上数据库如果出现问题，一般是如下几个方面导致的：

  • 撰写的SQL语句执行出错或者非法，俗称为业务代码Bug，例如慢查询、长事务、死锁等
  • 硬件故障：硬件问题如磁盘故障、内存故障、网络问题等都可能导致数据库无法正常运行或数据丢失。
  • 配置错误：不正确的配置可能导致数据库性能下降或不稳定，例如内存、缓冲区大小、并发连接数等
  • 全问题：数据库面临的安全风险包括未经授权的访问、SQL注入、数据泄露等。
  • 网络连接问题：网络连接不稳定、延迟或中断可能导致数据库连接问题或无法访问数据库。

那么，我们排查问题的思路是怎样的呢？

  • ①分析问题：根据理论知识+经验分析问题，判断问题可能出现的位置或可能引起问题的原因，将目标缩小到一定范围。
  • ②排查问题：基于上一步的结果，从引发问题的“可疑性”角度出发，从高到低依次进行排查，进一步排除一些选项，将目标范围进一步缩小。
  • ③定位问题：通过相关的监控数据的辅助，以更“细粒度”的手段，将引发问题的原因定位到精准位置。
  • ④解决问题：判断到问题出现的具体位置以及引发的原因后，采取相关措施对问题加以解决。

连接信息

在MySQL中，使用SHOW FULL PROCESSLIST或者select * from information_schema.PROCESSLIST;命令可以查看当前正在运行的所有连接和查询的信息。输出结果11, root, 172.14.230.1:57678, NULL, Query, 0, init, show FULL PROCESSLIST的各列代表的意思如下：

  • Id：连接或查询的标识符。
  • User：连接的用户名。
  • Host：连接的主机名。
  • db：当前操作的数据库。
  • Command：正在执行的命令类型，如”Query”表示查询语句，”Sleep”表示空闲状态, Connect表示连接等，通过sleep数量过多则一般意味着客户端并及时关闭连接。另外，数据查询和执行通常只需要不到0.01秒。
  • Time：连接或查询已经运行的时间，以秒为单位。
  • State：连接或查询的当前状态。
  • Info：当前连接或查询的附加信息，如执行的SQL语句。

其中State是很重要的列，下面是其可能得值：

状态 描述
init 初始化连接
waiting 等待操作或锁
sending 发送响应数据
reading 读取请求数据
updating 更新数据
creating 创建数据
closing 关闭连接
starting 启动操作
checking 检查状态
copying 复制数据
locked 被锁定
flushing 刷新数据
optimizing 优化操作
repairing 修复数据
killing 终止操作
unknown 未知状态

数据库连接的本质，在MySQL内部是一条条的工作线程，要牢记的一点是：操作系统在创建一条线程时，都需要为其分配相关的资源，如果一个客户端尝试与数据库建立新的连接时，此刻正好有一个数据库连接在执行某个操作，导致CPU被打满，这时就会由于没有资源来创建新的线程，因此会向客户端直接返回连接异常的信息。

查询日志

  1. 查询慢日志

查看show variables like 'slow_query_log%';对应的输出文件，检查是否有慢查询日志，该选项一般都是开启的。另外，可以手动调低慢查询的时间阈值来扩大排查SQL语句的范围。一旦查询到慢日志，就需要定位到具体的某个SQL，然后判断何种原因：

  • 查看Look_time的耗时，判断本次执行缓慢，是不是由于并发事务导致的长时间阻塞。
  • 查询大事务锁： 一般来说在开发环境中没有问题的SQL语句，放到线上环境出现执行缓慢的情况，多半原因是由于并发事务抢占锁，造成当前事务长时间无法获取锁资源，此时需要去定位操作相同行数据的大事务并优化。若所有的锁状态都比较大，意味着你当前这个MySQL节点承载的并发压力过高，此时就需要进行架构优化了。
  • 使用explain索引分析工具：如果慢查询不是因为锁导致的，那么大概率是SQL执行出现问题，此时就需要使用explain判断索引的使用情况
  1. 临时开启全量日志，进行日志查看，但是该操作会加大CPU消耗，通过将全量日志的输出放到表格中，然后实时观察：
1
2
3
# 开启
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

此时，就可以通过SELECT * from mysql.general_log ORDER BY event_time DESC;查询是否有周期性执行的SQL命令。

网络故障

此类情况可能是服务端和数据库端网络问题导致的，其排查方向如下：

  • 检查防火墙和安全组设置：确保服务器之间的通信端口是开放的，并且没有被防火墙或安全组限制。
  • 检查MySQL服务器的白名单和访问限制：确保允许从后端服务所在的机器访问数据库，并且登录的用户IP在允许范围内。
  • 检查网络连接和交换机设置：如果不同网段之间存在连接问题，可以检查网络连接是否正常，交换机的连接超时时间是否合理设置。
  • 检查网络带宽和使用情况：确认不同网段之间的网络带宽是否满足需求，并查看具体的带宽使用情况，可能需要增加带宽或优化网络传输方式。
  • 检查中间件设置：如果使用了中间件代理，如MyCat或MySQL-Proxy，确保相关的白名单和超时时间配置正确设置。

CPU过高

下面是一个简单的CPU过高排查步骤。

  1. top命令查看后台高CPU的进程，这里就以MySQL进程为例：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[root@localhost ~]# top
top - 14:09:20 up 2 days, 16 min,  3 users,  load average: 0.45, 0.15, 0.11
Tasks:  98 total,   1 running,  97 sleeping,   0 stopped,   0 zombie
%Cpu(s):100.0 us,  0.0 sy,  0.0 ni,  0.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :   997956 total,   286560 free,   126120 used,   585276 buff/cache
KiB Swap:  2097148 total,  2096372 free,      776 used.   626532 avail Mem 

   PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
 76661 root      20   0 2249432  25708  11592 S 99.9  2.6   0:28.32 mysql
   636 root      20   0  298936   6188   4836 S  0.3  0.6   3:39.52 vmtoolsd
     1 root      20   0   46032   5956   3492 S  0.0  0.6   0:04.27 systemd
     2 root      20   0       0      0      0 S  0.0  0.0   0:00.07 kthreadd
     3 root      20   0       0      0      0 S  0.0  0.0   0:04.21 ksoftirqd/0
     5 root       0 -20       0      0      0 S  0.0  0.0   0:00.00 kworker/0:0H
     7 root      rt   0       0      0      0 S  0.0  0.0   0:00.00 migration/0
     8 root      20   0       0      0      0 S  0.0  0.0   0:00.00 rcu_bh
     9 root      20   0       0      0      0 S  0.0  0.0   0:11.97 rcu_sched
     .......

从如上结果中不难发现，PID为76661的MySQL进程对CPU的占用率达到99.9%，此时就可以确定，机器的CPU利用率飙升是由于该进程引起的。

  1. 查看该进程中CPU占用率最高的线程：
1
2
3
4
5
6
7
8
9
10
[root@localhost ~]# top -Hp 76661
.....省略系统资源相关的信息......
   PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
 77935 root      20   0 2249432  26496  11560 R 99.9  2.7   3:43.95 mysql
 77915 root      20   0 2249432  26496  11560 S  0.0  2.7   0:00.00 mysql
 77916 root      20   0 2249432  26496  11560 S  0.0  2.7   0:00.08 mysql
 77917 root      20   0 2249432  26496  11560 S  0.0  2.7   0:00.00 mysql
 77918 root      20   0 2249432  26496  11560 S  0.0  2.7   0:00.00 mysql
 77919 root      20   0 2249432  26496  11560 S  0.0  2.7   0:00.00 mysql
 .......

从中可以看出PID为77935的线程对CPU资源的占用率却高达99.9%

  1. 5.7版本：在数据库的performance_schema.threads表中找到thread_os_id找到该线程对应的SQL语句信息，从而定位到出现问题的SQL语句。
  2. 5.6版本：通过SHOW ENGINE INNODB STATUS中的TRANSACTIONS找到OS线程和MySQL线程关系，最后通过show processlist获取运行线程信息。

磁盘IO过高

一般而言磁盘IO过高的原因有如下几个原因：

  • a. 内存不够：这是最经常的原因，内存不够导致系统用到了Swap，导致磁盘IO暴增
  • b. 大批量变更库中数据，需要执行大量写入操作，如主从数据同步时就会出现这个问题。
  • c. 整体并发过高，磁盘I/O频率跟不上，比如是机械硬盘材质，读写速率过慢。
  • d. 内存中的BufferPool缓冲池过小，大量读写操作需要落入磁盘处理，导致磁盘利用率过高
  • e. 频繁创建和销毁临时表，导致内存无法存储临时表数据，因而转到磁盘存储，导致磁盘飙升。
  • f. 执行某些SQL时从磁盘加载海量数据，如超12张表的联查，并每张表数据较大，最终导致IO打满。
  • g. 日志刷盘频率过高，其实这条是b，c的附带情况，毕竟日志的刷盘频率，跟整体并发直接挂钩。

此时就需要iotop找出磁盘IO开销最大的线程，然后利用pstack工具查看其堆栈信息，从堆栈信息来判断具体是啥原因导致的，一般磁盘IO过高可以使用如下方式解决：

  • 如果磁盘不是SSD材质，请先将磁盘升级成固态硬盘，MySQL对SSD硬盘做了特殊优化
  • 在项目中记得引入Redis降低读压力，引入MQ对写操作做流量削峰
  • 调大内存中BufferPool缓冲池的大小，最好设置成机器内存的70~75%左右
  • 撰写SQL语句时尽量减少多张大表联查，不要频繁的使用和销毁临时表

事务锁

说明

当我们使用任何ORM连接MYSQL数据库的时候, 每次更新DML操作一般都是一个原子性的事务操作, 在真正提交一个事务之前, mysql会对该操作记录增加行锁, 避免其他事务对该记录进行操作, 其他操作会触发异常: Lock wait timeout exceeded; try restarting transaction, 下面以flask sqlalchemy为例来简单说明:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 1. 进程1, 以flask shell 启动并更改某个记录, 首先开启全量日志
user = User.query.get(34)
db.session.delete(user)
# 此时并未同mysql服务器进行联动, 实际上仍然为开始事务
user2 = User.query.get(35)
# 在查询新的记录的时候会将delete语句也一起发送到mysql, 直接对记录34进行了行锁

# 2. 进程2在上面操作结束之后进入如下操作会报错
user = User.query.get(34)
user.name = 'age'
db.session.commit()

# 3. 强制杀死进程1,或者输入db.session.commit或者rollback, 进程2重新执行, 无任何问题

# 4. 另外, 多进程同时进行某一条记录的更新操作不会触发行锁机制, 在真正db.session.commit之前, 一般不会和MYSQL服务器有任何互动操作, 例如如下操作:
# a. 进程1
user = User.query.get(34)
db.session.delete(user)
db.session.commit()

# b. 进程2
user = User.query.get(34)
user.name = 'wahah'
db.session.commit()

# 此时进程2不会出现上面的Lock错误, 而是出现异常: sqlalchemy.orm.exc.ObjectDeletedError

这也是大部分事务提交异常之后未及时进行 rollback异常处理会引发的二次报错, 实际上此时查看show processlist;会发现有锁住的进程任务, 或者使用超级用户查看锁住的事务: SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查询锁信息

我们可以通过show status like 'innodb_row_lock_%'查询MySQL整体的锁状态（innodb），其输出含义如下：

  • Innodb_row_lock_current_waits：当前正在阻塞等待锁的事务数量。
  • Innodb_row_lock_time：从MySQL启动到现在，所有事务总共阻塞等待的总时长。
  • Innodb_row_lock_time_avg：平均每次事务阻塞等待锁的平均阻塞时长。
  • Innodb_row_lock_time_max：从MySQL启动到现在，最长的一次事务阻塞等待的时间。
  • Innodb_row_lock_waits：从MySQL启动到现在，所有事务总共阻塞等待的次数

指标用于衡量Innodb引擎中的行级锁使用情况，可以根据这些指标的变化情况来判断数据库中的锁竞争情况和性能瓶颈。

碎片空间

碎片问题

space fragment在大规模的使用delete()语句之后, 实际上磁盘空间并未释放, 当再次进行插入操作的时候, mysql会尝试再次利用该端空间, 但是会产生类似”内存碎片”的问题. 对于大的数据只能寻找下一个碎片空间或者整体空间, 在一定情况下, 降低了性能/空间的利用率. 那么碎片化产生的原理是什么呢? 不同的Mysql Engine产生碎片的原因各种各样, 这里以innoDB引擎简单的介绍为何删除操作会产生碎片:

  • 表数据存在页(page)中, 每一页都存放多个记录
  • 记录以树形结构(B+)组织, 表中数据和辅助索引都是用B+树结构, 这种B+索引被称为聚族索引
  • 每一个聚族索引的叶子节点包含行中所有字段的值, 辅助索引的叶子节点包含索引和主键列

每次删除行记录这些行仅仅被标记为”已删除”, 而不是真的从索引中物理删除, 此时空间并未真正释放回收, 虽然innodb的purge线程会异步的清理这些没用的索引键和行, 但是依然未将释放的空间还给操作系统以便其他进程使用, 从而导致这些页面产生了很多空洞. 当运行频繁删除操作业务时, 必然会在数据文件中造成不连续的空白空间, 插入新的数据时这些空白空间会被重复使用, 这就产生数据的存储位置不连续, 即物理存储顺序和逻辑排序顺序不同问题, 此现象就被称为数据碎片. 碎片还有如下几种类型:

  • 行碎片: 数据行被存储为多个地方的多个片段, 即使查询只从索引中访问一行记录, 其会导致查询性能大大下降.
  • 行间碎片: 逻辑上顺序的页, 或者行在磁盘上不是顺序存储的, 这些操作会影响全表扫描和聚族索引扫描
  • 剩余空间碎片: 数据页中有大量的空余空间, 导致服务器读取大量不需要的数据

下面是查看数据库碎片空间的语句:

1
2
3
4
5
6
7
8
9
10
11
12
13
# 列出所有已经产生碎片的表: 
select table_schema db, table_name, data_free, engine from information_schema.tables 
where table_schema not in ('information_schema', 'mysql')  and data_free > 0;

# 查看表空间的碎片总大小(Data_free): 
SHOW TABLE STATUS LIKE '表名'\G
SHOW Table Status\G;

# 磁盘空间上的表文件:
*.idb/*.myd---数据文件, *.frm---结构文件

# 查看index的检索效率(建议在执行optimize前后进行对比):
show index from ad_visit_history from test1;

mysql中有一个默认数据库information_schema, 该数据库中的tables表中包含了当前数据库创建的所有其数据库信息. 例如查看所有数据库的空间大小, 输出两列: DB Name, DB Size in MB, 则sql查询语句如下:

1
2
3
4
#  查看 DATABASE 空间大小
SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
GROUP BY table_schema;

若想查询指定的数据库空间大小或者细化到某个表的空间大小:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 1. 数据库空间
select concat(round(sum(data_length/1024/1024),2), 'MB') as data
from information_schema.tables
where table_schema='bamboodba';

-- 2. 指定表空间, 通过sum函数自动完成累加
select concat(round(sum(data_length/1024/1024),2), 'MB') as data
from information_schema.tables
where table_schema='bamboodba' and table_name='book';

-- 3. 指定数据库的所有表空间
select
    table_schema as '数据库',
    table_name as '表名',
    table_rows as '记录数',
    truncate(data_length/1024/1024, 2) as '数据容量(MB)',
    truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='bamboodba'
order by data_length desc, index_length desc;

碎片和表空间

  1. 连续插入情况下的碎片问题
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# a. 创建表
user testdb;
create table frag_tab_myisam (
  id  int,
  name varchar(1024),
  PRIMARY KEY (`id`)
) engine=MyISAM;

# b. 插入数据(插入1000条数据)
insert into frag_tab_myisam(name) values('first record');
insert into frag_tab_myisam(name) values('second record');
insert into frag_tab_myisam(name) values('third record');
insert into frag_tab_myisam(name) values('fourth record');
insert into frag_tab_myisam(name) values('fifth record');

# c. 查看表碎片, 可以看到输出中Data_free字段的值为0, 表示不存在碎片
show table status from drcc2 like 'frag_tab_myisam'\G;
  1. 删除DML操作之后产生碎片
1
2
3
4
5
# a. 删除不联系
delete from drcc2.frag_tab_myisam where id =1;
delete from drcc2.frag_tab_myisam where id =3;
# b. 查看表碎片
show table status from drcc2 like 'frag_tab_myisam'\G;

上面的操作是无法达到效果的, 可能是最新版本的MYSQL已经优化了大部分碎片工作, 但是工作环境中的一个频繁进行创建
操作的表是出现碎片信息的:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
*************************** 1. row ***************************
           Name: XX_task
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 170250
 Avg_row_length: 724
    Data_length: 123387904
Max_data_length: 0
   Index_length: 0
      Data_free: 6291456
 Auto_increment: 178895
    Create_time: XXX
    Update_time: XXX
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.09 sec)

在最新版本InnoDB引擎下, 如果开启共享表空间, 则创建表并插入数据的时候就会默认产生Data_free值, 在少量的delete和insert测试下, 该数值也未发生变化, 后续有时间需要再看下, 这里先简单介绍下共享表空间和独立表空间概念:

  • 共享表空间: 所有表数据, 所有索引文件都放在一个单独的表空间(ibdata1)里面, 这个表空间可以有多个文件组成, 这样可以让一个表可以跨多个文件存在, 其大小限制不再是文件大小限制, 一个表空间的最大限制为64TB, 所以innodb中单表最大可以打到64TB.
  • 独立表空间: 某一个数据库的所有表数据, 索引文件全部放在一个文件中, 默认这个表空间的文件路径在data目录下, 默认文件名为ibdata1, 初始化为10M.

查看数据库独立表空间的命令: show variables like 'innodb_data%';. 那么, 共享表空间和独立表空间各自的优缺点都有哪些呢? 对于独立表空间, 其优点如下:

  • a. 每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动
  • b. 表空间可以回收, Drop table操作自动回收表空间
  • c. 对于频繁删除的表, 可以使用alter table TableName engine=innodb;或者truncate table来回缩空间
  • d. 表空间碎片不会太严重的影响性能
1
2
3
4
5
6
7
8
9
10
11
12
13
14
例如本地一个数据库目录的表文件列表:

total 70656
-rw-r-----  1 1  admin    114688  3 12 16:35 alembic_version.ibd
-rw-r-----  1 1  admin    589824  3 12 17:05 area.ibd
-rw-r-----  1 1  admin    131072  3 12 17:01 city.ibd
-rw-r-----  1 1  admin    163840  3  9 21:47 comments.ibd
-rw-r-----  1 1  admin    131072  3 10 23:00 follows.ibd
-rw-r-----  1 1  admin    147456  3  9 21:45 posts.ibd
-rw-r-----  1 1  admin    147456  3 12 16:58 province.ibd
-rw-r-----  1 1  admin    147456  3  7 22:06 roles.ibd
-rw-r-----  1 1  admin  17825792  3 12 17:05 street.ibd
-rw-r-----  1 1  admin  16777216  3 12 16:17 users.ibd

当然, 其缺点也是比较明显:

  • 单表过大, 容易造成存储空间不足

共享表空间的优缺点如下:

1
2
3
4
5
6
7
8
9
10
# 1. 优点
a. 表空间可以分为多个文件存放在磁盘上, 一个表可以分成多个文件存放, 表大小不收磁盘大小限制(类似分布式)
b. 所有表的数据和文件放在一起方便管理

在设置表未共享表空间之后, 创建新的表`create table book2(id int,name varchar(255));`, 此时可以并未在该数据库目录下发现book2.ibd文件, 实际上该表存放在共享表空间中, 即数据根目录下的ibdata1, 此次该文件的大小已经增长.
show variables like 'innodb_data_file_path';

# 2. 缺点
a. 多表和索引在表空间中的混合存储, 但触发大量删除操作后表空间会有大量的空隙
b. 表空间不能回缩, 即使删除表也无法解决回缩文件, 例如实际上占用空间10M, 但是表空间大小为10G

独立表空间和共享表空间的转换命令:

1
2
3
4
5
6
7
8
9
# 1. 查看当前表空间管理类型: ON-独立表空间, OFF-共享表空间
show variables like "innodb_file_per_table"

# 2. 修改表空间管理方式
set global innodb_file_per_table=1   # 独立表
set global innodb_file_per_table=0   # 共享表

# 3. 更改已创建表从共享到独立的方法, 在已设置innodb_file_per_table=1的前提下
alter table table_name engine=innodb;

问题修复

在mysql中可以使用optimize table和alter table xxxx engine=innodb来降低碎片, 其中optimize table会重组表和索引的物理存储, 减少对存储空间使用和提升访问表时的IO效率.

1
2
3
4
5
6
# a. 方式1
optimize table table_name; 
# b. 方式2
alter table table_name engine='innodb';
# c. 对数据库操作
mysqlcheck -uroot -p --optimize --databases DATABASE_NAME

参考