MySql必知必会

发布时间：2024年01月23日

21.如何做慢查询优化？

MySQL 慢查询的相关参数解释：

  • slow_query_log：是否开启慢查询日志，ON(1)表示开启,
    OFF(0) 表示关闭。
  • slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。
  • long_query_time： 慢查询阈值，当查询时间多于设定的阈值时，记录日志。

慢查询配置方式

  1. 默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的
mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | ON                           |
| slow_query_log_file | /var/lib/mysql/test-slow.log |
+---------------------+------------------------------+
  1. 可以通过设置slow_query_log的值来开启
mysql> set global slow_query_log=1;
  1. 使用 set global slow_query_log=1 开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
-- 编辑配置
vim /etc/my.cnf

-- 添加如下内容
slow_query_log =1
slow_query_log_file=/var/lib/mysql/ruyuan-slow.log

-- 重启MySQL
service mysqld restart

mysql> show variables like '%slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/lib/mysql/ruyuan-slow.log |
+---------------------+--------------------------------+
  1. 那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数 long_query_time控制，默认情况下long_query_time的值为10秒.
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql>  show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
  1. 修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到呢？注意：使用命令 set global long_query_time=1 修改后，需要重新连接或新开一个会话才能看到修改值。
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
  1. log_output 参数是指定日志的存储方式。log_output='FILE' 表示将日志存入文件，默认值是’FILE’。log_output='TABLE' 表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log 表中。
mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件.

  1. 系统变量 log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+

mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+

3) 慢查询测试

  1. 执行 test_index.sql 脚本,监控慢查询日志内容
[root@localhost mysql]# tail -f /var/lib/mysql/ruyuan-slow.log 
/usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
  1. 执行下面的SQL,执行超时 (超过1秒) 我们去查看慢查询日志
SELECT * FROM test_index WHERE  
hobby = '20009951' OR hobby = '10009931' OR hobby = '30009931' 
OR dname = 'name4000' OR dname = 'name6600' ;
  1. 日志内容

我们得到慢查询日志后，最重要的一步就是去分析这个日志。我们先来看下慢日志里到底记录了哪些内容。

如下图是慢日志里其中一条SQL的记录内容，可以看到有时间戳，用户，查询时长及具体的SQL等信息.

# Time: 2022-02-23T13:50:45.005959Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 3.724273  Lock_time: 0.000371 Rows_sent: 5  Rows_examined: 5000000
SET timestamp=1645624245;
select * from test_index where hobby = '20009951' or hobby = '10009931' or hobby = '30009931' or dname = 'name4000' or dname = 'name6600';
  • Time: 执行时间
  • User: 用户信息 ,Id信息
  • Query_time: 查询时长
  • Lock_time: 等待锁的时长
  • Rows_sent:查询结果的行数
  • Rows_examined: 查询扫描的行数
  • SET timestamp: 时间戳
  • SQL的具体信息

慢查询SQL优化思路

1) SQL性能下降的原因

在日常的运维过程中，经常会遇到DBA将一些执行效率较低的SQL发过来找开发人员分析，当我们拿到这个SQL语句之后，在对这些SQL进行分析之前，需要明确可能导致SQL执行性能下降的原因进行分析，执行性能下降可以体现在以下两个方面：

  • 等待时间长

    锁表导致查询一直处于等待状态，后续我们从MySQL锁的机制去分析SQL执行的原理

  • 执行时间长

    1.查询语句写的烂
2.索引失效 
3.关联查询太多join 
4.服务器调优及各个参数的设置

2) 慢查询优化思路

  1. 优先选择优化高并发执行的SQL,因为高并发的SQL发生问题带来后果更严重.

    比如下面两种情况:
   SQL1: 每小时执行10000次, 每次20个IO 优化后每次18个IO,每小时节省2万次IO
   SQL2: 每小时10次,每次20000个IO,每次优化减少2000个IO,每小时节省2万次IO
   SQL2更难优化,SQL1更好优化.但是第一种属于高并发SQL,更急需优化 成本更低

  2. 定位优化对象的性能瓶颈(在优化之前了解性能瓶颈在哪)

    在去优化SQL时,选择优化分方向有三个: 
  1.IO(数据访问消耗的了太多的时间,查看是否正确使用了索引) , 
  2.CPU(数据运算花费了太多时间, 数据的运算分组 排序是不是有问题) 
  3.网络带宽(加大网络带宽)

  3. 明确优化目标

    需要根据数据库当前的状态
数据库中与该条SQL的关系
当前SQL的具体功能
最好的情况消耗的资源,最差情况下消耗的资源,优化的结果只有一个给用户一个好的体验

  4. 从explain执行计划入手

    只有explain能告诉你当前SQL的执行状态

  5. 永远用小的结果集驱动大的结果集

    小的数据集驱动大的数据集,减少内层表读取的次数

类似于嵌套循环
for(int i = 0; i < 5; i++){
	for(int i = 0; i < 1000; i++){

	}
}
如果小的循环在外层,对于数据库连接来说就只连接5次,进行5000次操作,如果1000在外,则需要进行1000次数据库连接,从而浪费资源，增加消耗.这就是为什么要小表驱动大表。

  6. 尽可能在索引中完成排序

    排序操作用的比较多,order by 后面的字段如果在索引中,索引本来就是排好序的,所以速度很快,没有索引的话,就需要从表中拿数据,在内存中进行排序,如果内存空间不够还会发生落盘操作

  7. 只获取自己需要的列

    不要使用select  * ,select * 很可能不走索引,而且数据量过大

  8. 只使用最有效的过滤条件

    误区 where后面的条件越多越好,但实际上是应该用最短的路径访问到数据

  9. 尽可能避免复杂的join和子查询

    每条SQL的JOIN操作 建议不要超过三张表
将复杂的SQL, 拆分成多个小的SQL 单个表执行,获取的结果 在程序中进行封装
如果join占用的资源比较多,会导致其他进程等待时间变长

  10. 合理设计并利用索引

    如何判定是否需要创建索引?
 1.较为频繁的作为查询条件的字段应该创建索引.
 2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件.（唯一性太差的字段主要是指哪些呢？如状态字段，类型字段等等这些字段中的数据可能总共就是那么几个几十个数值重复使用）（当一条Query所返回的数据超过了全表的15%的时候，就不应该再使用索引扫描来完成这个Query了）.
 3.更新非常频繁的字段不适合创建索引.（因为索引中的字段被更新的时候，不仅仅需要更新表中的数据，同时还要更新索引数据，以确保索引信息是准确的）.
 4.不会出现在WHERE子句中的字段不该创建索引.

如何选择合适索引?
 1.对于单键索引，尽量选择针对当前Query过滤性更好的索引.
 2.选择联合索引时,当前Query中过滤性最好的字段在索引字段顺序中排列要靠前.
 3.选择联合索引时,尽量索引字段出现在w中比较多的索引.

22.Hash索引有哪些优缺点？

MySQL中索引的常用数据结构有两种: 一种是B+Tree,另一种则是Hash.

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。

image.png

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值,如果出现哈希码值相同的情况会拉出一条链表.

Hsah索引的优点

  • 因为索引自身只需要存储对应的Hash值,所以索引结构非常紧凑, 只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引 .
  • 没有哈希冲突的情况下,等值查询访问哈希索引的数据非常快.(如果发生Hash冲突,存储引擎必须遍历链表中的所有行指针,逐行进行比较,直到找到所有符合条件的行).

Hash索引的缺点

  • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
  • 哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找。
  • 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。

23.说一下InnoDB内存相关的参数优化？

Buffer Pool参数优化

1.1 缓冲池内存大小配置

一个大的日志缓冲区允许大量的事务在提交之前不写日志到磁盘。因此，如果你有很多事务的更新，插入或删除操作，通过设置这个参数会大量的减少磁盘I/O的次数数。
建议: 在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的60% - 80%.

  • 查看缓冲池大小

    mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

mysql> select 134217728 / 1024 / 1024;
+-------------------------+
| 134217728 / 1024 / 1024 |
+-------------------------+
|            128.00000000 |
+-------------------------+

  • 在线调整InnoDB缓冲池大小
    innodb_buffer_pool_size可以动态设置，允许在不重新启动服务器的情况下调整缓冲池的大小.

    mysql> SET GLOBAL innodb_buffer_pool_size = 268435456; -- 512
Query OK, 0 rows affected (0.10 sec)

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+

    监控在线调整缓冲池的进度

    mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------------------------+
| Variable_name                    | Value                                                        |
+----------------------------------+----------------------------------------------------------------------+
| Innodb_buffer_pool_resize_status | Size did not change (old size = new size = 268435456. Nothing to do. |
+----------------------------------+----------------------------------------------------------------------+

1.3 InnoDB 缓存性能评估

当前配置的innodb_buffer_pool_size是否合适，可以通过分析InnoDB缓冲池的缓存命中率来验证。

  • 以下公式计算InnoDB buffer pool 命中率:

    命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests+innodb_buffer_pool_reads)* 100

参数1: innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
参数2: innodb_buffer_pool_read_requests：表示从内存中读取页的请求数。

    mysql> show status like 'innodb_buffer_pool_read%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 0     |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
| Innodb_buffer_pool_read_requests      | 12701 |
| Innodb_buffer_pool_reads              | 455   |
+---------------------------------------+-------+

-- 此值低于90%，则可以考虑增加innodb_buffer_pool_size。
mysql> select 12701 / (455 + 12701) * 100 ;
+-----------------------------+
| 12701 / (455 + 12701) * 100 |
+-----------------------------+
|                     96.5415 |
+-----------------------------+

1.4 Page管理相关参数

查看Page页的大小(默认16KB),innodb_page_size只能在初始化MySQL实例之前配置，不能在之后修改。如果没有指定值，则使用默认页面大小初始化实例。

mysql> show variables like '%innodb_page_size%'; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

Page页管理状态相关参数

mysql> show global status like '%innodb_buffer_pool_pages%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_pages_data    | 515   |
| Innodb_buffer_pool_pages_dirty   | 0     |
| Innodb_buffer_pool_pages_flushed | 334   |
| Innodb_buffer_pool_pages_free    | 15868 |
| Innodb_buffer_pool_pages_misc    | 0     |
| Innodb_buffer_pool_pages_total   | 16383 |
+----------------------------------+-------+

pages_data: InnoDB缓冲池中包含数据的页数。 该数字包括脏页面和干净页面。

pages_dirty: 显示在内存中修改但尚未写入数据文件的InnoDB缓冲池数据页的数量（脏页刷新）。

pages_flushed: 表示从InnoDB缓冲池中刷新脏页的请求数。

pages_free: 显示InnoDB缓冲池中的空闲页面

pages_misc: 缓存池中当前已经被用作管理用途或hash index而不能用作为普通数据页的数目

pages_total: 缓存池的页总数目。单位是page。

24.InnoDB日志相关的参数优化了解过吗？

1.日志缓冲区相关参数配置

日志缓冲区的大小。一般默认值16MB是够用的，但如果事务之中含有blog/text等大字段，这个缓冲区会被很快填满会引起额外的IO负载。配置更大的日志缓冲区,可以有效的提高MySQL的效率.

  • innodb_log_buffer_size 缓冲区大小

    mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+

  • innodb_log_files_in_group 日志组文件个数
    日志组根据需要来创建。而日志组的成员则需要至少2个，实现循环写入并作为冗余策略。

    mysql> show variables like 'innodb_log_files_in_group';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+

  • innodb_log_file_size 日志文件大小
    参数innodb_log_file_size用于设定MySQL日志组中每个日志文件的大小(默认48M)。此参数是一个全局的静态参数，不能动态修改。
    参数innodb_log_file_size的最大值，二进制日志文件大小（innodb_log_file_size * innodb_log_files_in_group）不能超过512GB.所以单个日志文件的大小不能超过256G.

    mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+

2.日志文件参数优化

首先我们先来看一下日志文件大小设置对性能的影响

  • 设置过小
    1. 参数 innodb_log_file_size设置太小，就会导致MySQL的日志文件( redo log）频繁切换，频繁的触发数据库的检查点（Checkpoint），导致刷新脏页到磁盘的次数增加。从而影响IO性能。
    2. 处理大事务时，将所有的日志文件写满了，事务内容还没有写完，这样就会导致日志不能切换.
  • 设置过大
    参数 innodb_log_file_size如果设置太大，虽然可以提升IO性能，但是当MySQL由于意外宕机时，二进制日志很大，那么恢复的时间必然很长。而且这个恢复时间往往不可控，受多方面因素影响。

优化建议:

如何设置合适的日志文件大小 ?

  • 根据实际生产场景的优化经验,一般是计算一段时间内生成的事务日志（redo log）的大小， 而MySQL的日志文件的大小最少应该承载一个小时的业务日志量(官网文档中有说明)。

想要估计一下InnoDB redo log的大小，需要抓取一段时间内Log SequenceNumber（日志顺序号）的数据,来计算一小时内产生的日志大小.

Log sequence number

自系统修改开始，就不断的生成redo日志。为了记录一共生成了多少日志，于是mysql设计了全局变量log sequence number，简称lsn，但不是从0开始，是从8704字节开始。

-- pager分页工具, 只获取 sequence的信息
mysql> pager grep sequence;
PAGER set to 'grep sequence'

-- 查询状态,并倒计时一分钟
mysql> show engine innodb status\G select sleep(60);
Log sequence number 5399154
1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

-- 一分时间内所生成的数据量 5406150
mysql> show engine innodb status\G;
Log sequence number 5406150

-- 关闭pager
mysql> nopager;
PAGER set to stdout

有了一分钟的日志量,据此推算一小时内的日志量

mysql> select (5406150 - 5399154) / 1024 as kb_per_min;
+------------+
| kb_per_min |
+------------+
|     6.8320 |
+------------+

mysql> select (5406150 - 5399154) / 1024 * 60 as kb_per_min;
+------------+
| kb_per_min |
+------------+
|   409.9219 |
+------------+

太大的缓冲池或非常不正常的业务负载可能会计算出非常大(或非常小)的日志大小。这也是公式不足之处，需要根据判断和经验。但这个计算方法是一个很好的参考标准。

25.InnoDB IO线程相关参数优化了解过吗？

数据库属于 IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。从内存中读取一个数据库数据的时间是微秒级别，而从一块普通硬盘上读取一个IO是在毫秒级别。要优化数据库，IO操作是必须要优化的，尽可能将磁盘IO转化为内存IO。

1) 参数: query_cache_size&have_query_cache
MySQL查询缓存会保存查询返回的完整结果。当查询命中该缓存，会立刻返回结果，跳过了解析，优化和执行阶段。
查询缓存会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这个表相关的所有缓存都将失效。

  1. 查看查询缓存是否开启
-- 查询是否支持查询缓存
mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

-- 查询是否开启查询缓存 默认关闭
mysql> show variables like '%query_cache_type%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
  1. 开启缓存,在my.ini中添加下面一行参数
query_cache_size=128M
query_cache_type=1

query_cache_type:
设置为0，OFF,缓存禁用
设置为1，ON,缓存所有的结果
设置为2，DENAND,只缓存在select语句中通过SQL_CACHE指定需要缓存的查询
  1. 测试能否缓存查询
  mysql> show status like '%Qcache%';
  +-------------------------+---------+
  | Variable_name           | Value   |
  +-------------------------+---------+
  | Qcache_free_blocks      | 1       |
  | Qcache_free_memory      | 1031832 |
  | Qcache_hits             | 0       |
  | Qcache_inserts          | 0       |
  | Qcache_lowmem_prunes    | 0       |
  | Qcache_not_cached       | 1       |
  | Qcache_queries_in_cache | 0       |
  | Qcache_total_blocks     | 1       |
  +-------------------------+---------+
  • Qcache_free_blocks:缓存中目前剩余的blocks数量（如果值较大，则查询缓存中的内存碎片过多）
  • Qcache_free_memory:空闲缓存的内存大小
  • Qcache_hits:命中缓存次数
  • Qcache_inserts: 未命中然后进行正常查询
  • Qcache_lowmem_prunes:查询因为内存不足而被移除出查询缓存记录
  • Qcache_not_cached: 没有被缓存的查询数量
  • Qcache_queries_in_cache:当前缓存中缓存的查询数量
  • Qcache_total_blocks:当前缓存的block数量

优化建议: Query Cache的使用需要多个参数配合，其中最为关键的是 query_cache_size 和 query_cache_type ，前者设置用于缓存 ResultSet 的内存大小，后者设置在何场景下使用 Query Cache。

MySQL数据库数据变化相对不多，query_cache_size 一般设置为256MB比较合适 ,也可以通过计算Query Cache的命中率来进行调整

( Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100) )
  1. 参数: innodb_max_dirty_pages_pct 该参数是InnoDB 存储引擎用来控制buffer pool中脏页的百分比，当脏页数量占比超过这个参数设置的值时，InnoDB会启动刷脏页的操作。
-- innodb_max_dirty_pages_pct 参数可以动态调整，最小值为0， 最大值为99.99，默认值为 75。
mysql> show variables like 'innodb_max_dirty_pages_pct';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| innodb_max_dirty_pages_pct | 75.000000 |
+----------------------------+-----------+

优化建议: 该参数比例值越大，从内存到磁盘的写入操作就会相对减少，所以能够一定程度下减少写入操作的磁盘IO。但是，如果这个比例值过大，当数据库 Crash 之后重启的时间可能就会很长，因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中.最大不建议超过90,一般重启恢复的数据在超过1GB的话,启动速度就会变慢.

3) 参数: innodb_old_blocks_pct&innodb_old_blocks_time
innodb_old_blocks_pct 用来确定LRU链表中old sublist所占比例,默认占用37%

mysql> show variables like '%innodb_old_blocks_pct%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+

innodb_old_blocks_time 用来控制old sublist中page的转移策略，新的page页在进入LRU链表中时，会先插入到old sublist的头部，然后page需要在old sublist中停留innodb_old_blocks_time这么久后，下一次对该page的访问才会使其移动到new sublist的头部，默认值1秒.

mysql> show variables like '%innodb_old_blocks_time%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000  |
+------------------------+-------+

优化建议: 在没有大表扫描的情况下，并且数据多为频繁使用的数据时，我们可以增加innodb_old_blocks_pct的值，并且减小innodb_old_blocks_time的值。让数据页能够更快和更多的进入的热点数据区。

26.什么是写失效？

InnoDB的页和操作系统的页大小不一致，InnoDB页大小一般为16K，操作系统页大小为4K，InnoDB的页写入到磁盘时，一个页需要分4次写。

如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了4K，就宕机了，这种情况叫做部分写失效（partial page write），可能会导致数据丢失。

image.png

双写缓冲区 Doublewrite Buffer

为了解决写失效问题，InnoDB实现了double write buffer Files, 它位于系统表空间，是一个存储区域。

在BufferPool的page页刷新到磁盘真正的位置前，会先将数据存在Doublewrite 缓冲区。这样在宕机重启时，如果出现数据页损坏，那么在应用redo log之前，需要通过该页的副本来还原该页，然后再进行redo log重做，double write实现了InnoDB引擎数据页的可靠性.

默认情况下启用双写缓冲区，如果要禁用Doublewrite 缓冲区，可以将 innodb_doublewrite设置为0。

mysql> show variables like '%innodb_doublewrite%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_doublewrite | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

数据双写流程

image.png

  • step1：当进行缓冲池中的脏页刷新到磁盘的操作时,并不会直接写磁盘,每次脏页刷新必须要先写double write .
  • step2：通过memcpy函数将脏页复制到内存中的double write buffer .
  • step3: double write buffer再分两次、每次1MB, 顺序写入共享表空间的物理磁盘上, 第一次写.
  • step4: 在完成double write页的写入后，再将double wirite buffer中的页写入各个表的独立表空间文件中(数据文件 .ibd), 第二次写。

为什么写两次 ?

可能有的同学会有疑问，为啥写两次，刷一次数据文件保存数据不就可以了，为什么还要写共享表空间 ?其实是因为共享表空间是在ibdbata文件中划出2M连续的空间，专门给double write刷脏页用的, 由于在这个过程中，double write页的存储是连续的，因此写入磁盘为顺序写，性能很高；完成double write后，再将脏页写入实际的各个表空间文件，这时写入就是离散的了.

27.什么是行溢出？

行记录格式

1) 行格式分类

表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和DML操作的性能。如果在单个page页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更新时所需的I/O更少。

InnoDB存储引擎支持四种行格式：Redundant、Compact、Dynamic 和 Compressed .

查询MySQL使用的行格式,默认为: dynamic

mysql> show variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+

指定行格式语法

CREATE TABLE <table_name(column_name)> ROW_FORMAT=行格式名称
ALTER TABLE <table_name> ROW_FORMAT=行格式名称

2) COMPACT 行记录格式

Compact 设计目标是高效地存储数据，一个页中存放的行数据越多，其性能就越高。

Compact行记录由两部分组成: 记录放入额外信息 和 记录的真实数据.

image.png

记录额外信息部分

服务器为了描述一条记录而添加了一些额外信息(元数据信息)，这些额外信息分为3类，分别是: 变长字段长度列表、NULL值列表和记录头信息.

  • 变长字段长度列表

    MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型，这些变长的数据类型占用的存储空间分为两部分：

    1. 真正的数据内容
    2. 占用的字节数

    变长字段的长度是不固定的，所以在存储数据的时候要把这些数据占用的字节数也存起来，读取数据的时候才能根据这个长度列表去读取对应长度的数据。

    在 Compact行格式中，把所有变长类型的列的长度都存放在记录的开头部位形成一个列表，按照列的顺序逆序存放,这个列表就是 变长字段长度列表。

  • NULL值列表

    表中的某些列可能会存储NULL值，如果把这些NULL值都放到记录的真实数据中会比较浪费空间，所以Compact行格式把这些值为NULL的列存储到NULL值列表中。( 如果表中所有列都不允许为 NULL，就不存在NULL值列表 )

  • 记录头信息

    记录头信息是由固定的5个字节组成，5个字节也就是40个二进制位，不同的位代表不同的意思，这些头信息会在后面的一些功能中看到。

    名称大小(单位:bit)描述
    预留位11没有使用
    预留位21没有使用
    delete_mask1标记该记录是否被删除
    min_rec_mask1标记该记录是否是本层B+树的非叶子节点中的最小记录
    n_owned4表示当前分组中管理的记录数
    heap_no13表示当前记录在记录堆中的位置信息
    record_type3表示当前记录的类型:
    0 表示普通记录,
    1 表示B+树非叶子节点记录,
    2 表示最小记录,3表示最大记录
    next_record16表示下一条记录的相对位置

    1. delete_mask

      这个属性标记着当前记录是否被删除，占用1个二进制位，值为0 的时候代表记录并没有被删除，为1 的时候代表记录被删除掉了

    2. min_rec_mask

      B+树的每层非叶子节点中的最小记录都会添加该标记。

    3. n_owned

      代表每个分组里，所拥有的记录的数量，一般是分组里主键最大值才有的。

    4. heap_no

      在数据页的User Records中插入的记录是一条一条紧凑的排列的，这种紧凑排列的结构又被称为堆。为了便于管理这个堆，把记录在堆中的相对位置给定一个编号——heap_no。所以heap_no这个属性表示当前记录在本页中的位置。

    5. record_type

      这个属性表示当前记录的类型，一共有4种类型的记录， 0 表示普通用户记录， 1 表示B+树非叶节点记录， 2 表示最小记录， 3 表示最大记录。

    6. next_record

      表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量，可以理解为指向下一条记录地址的指针。值为正数说明下一条记录在当前记录后面，为负数说明下一条记录在当前记录的前面。

  • 记录真实数据部分

    记录的真实数据除了插入的那些列的数据，MySQL会为每个记录默认的添加一些列（也称为隐藏列），具体的列如下：

    image.png

    列名是否必须占用空间描述
    row_id否6字节行ID,唯一标识一条记录
    transaction_id是6字节事务ID
    roll_pointer是7字节回滚指针

    生成隐藏主键列的方式有:

    1. 服务器会在内存中维护一个全局变量，每当向某个包含隐藏的row_id列的表中插入一条记录时，就会把该变量的值当作新记录的row_id列的值，并且把该变量自增1。
    2. 每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间的页号为7的页面中一个Max Row ID的属性处。
    3. 当系统启动时，会将页中的Max Row ID属性加载到内存中，并将该值加上256之后赋值给全局变量，因为在上次关机时该全局变量的值可能大于页中Max Row ID属性值。
      4.

    3) Compact中的行溢出机制

    什么是行溢出 ?

    MySQL中是以页为基本单位,进行磁盘与内存之间的数据交互的,我们知道一个页的大小是16KB,16KB = 16384字节.而一个varchar(m) 类型列最多可以存储65532个字节,一些大的数据类型比如TEXT可以存储更多.

    如果一个表中存在这样的大字段,那么一个页就无法存储一条完整的记录.这时就会发生行溢出,多出的数据就会存储在另外的溢出页中.

    总结: 如果某些字段信息过长，无法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出页，该字段被称为页外列。

    Compact中的行溢出机制

    InnoDB 规定一页至少存储两条记录(B+树特点)，如果页中只能存放下一条记录，InnoDB存储引擎会自动将行数据存放到溢出页中.
    当发生行溢出时，数据页只保存了前768字节的前缀数据，接着是20个字节的偏移量，指向行溢出页.

    image.png

28.如何进行JOIN优化？

JOIN 是 MySQL 用来进行联表操作的，用来匹配两个表的数据，筛选并合并出符合我们要求的结果集。

JOIN 操作有多种方式，取决于最终数据的合并效果。常用连接方式的有以下几种:

image.png

什么是驱动表 ?

  • 多表关联查询时,第一个被处理的表就是驱动表,使用驱动表去关联其他表.
  • 驱动表的确定非常的关键,会直接影响多表关联的顺序,也决定后续关联查询的性能

驱动表的选择要遵循一个规则:

  • 在对最终的结果集没有影响的前提下,优先选择结果集最小的那张表作为驱动表

3) 三种JOIN算法

1.Simple Nested-Loop Join（ 简单的嵌套循环连接 )

  • 简单来说嵌套循环连接算法就是一个双层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果.

  • 这种算法是最简单的方案，性能也一般。对内循环没优化。

  • 例如有这样一条SQL:

    -- 连接用户表与订单表 连接条件是 u.id = o.user_id
select * from user t1 left join order t2 on t1.id = t2.user_id;
-- user表为驱动表,order表为被驱动表

  • 转换成代码执行时的思路是这样的:

    for(user表行 uRow : user表){
    for(Order表的行 oRow : order表){
        if(uRow.id = oRow.user_id){
            return uRow;
        }
    }
}

  • 匹配过程如下图image.png

  • SNL 的特点

    • 简单粗暴容易理解，就是通过双层循环比较数据来获得结果
    • 查询效率会非常慢,假设 A 表有 N 行，B 表有 M 行。SNL 的开销如下：
      • A 表扫描 1 次。
      • B 表扫描 M 次。
      • 一共有 N 个内循环，每个内循环要 M 次，一共有内循环 N * M 次

2) Index Nested-Loop Join（ 索引嵌套循环连接 ）

  • Index Nested-Loop Join 其优化的思路: 主要是为了减少内层表数据的匹配次数 , 最大的区别在于，用来进行 join 的字段已经在被驱动表中建立了索引。
  • 从原来的 匹配次数 = 外层表行数 * 内层表行数 , 变成了 匹配次数 = 外层表的行数 * 内层表索引的高度 ，极大的提升了 join的性能。
  • 当 order 表的 user_id 为索引的时候执行过程会如下图：

image.png

注意：使用Index Nested-Loop Join 算法的前提是匹配的字段必须建立了索引。

3) Block Nested-Loop Join( 块嵌套循环连接 )

如果 join 的字段有索引，MySQL 会使用 INL 算法。如果没有的话，MySQL 会如何处理？

因为不存在索引了，所以被驱动表需要进行扫描。这里 MySQL 并不会简单粗暴的应用 SNL 算法，而是加入了 buffer 缓冲区，降低了内循环的个数，也就是被驱动表的扫描次数。

  • 在外层循环扫描 user表中的所有记录。扫描的时候，会把需要进行 join 用到的列都缓存到 buffer 中。buffer 中的数据有一个特点，里面的记录不需要一条一条地取出来和 order 表进行比较，而是整个 buffer 和 order表进行批量比较。

  • 如果我们把 buffer 的空间开得很大，可以容纳下 user 表的所有记录，那么 order 表也只需要访问一次。

  • MySQL 默认 buffer 大小 256K，如果有 n 个 join 操作，会生成 n-1 个 join buffer。

    mysql> show variables like '%join_buffer%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+

mysql> set session join_buffer_size=262144;
Query OK, 0 rows affected (0.00 sec)

4) JOIN优化总结

  1. 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)
  2. 为匹配的条件增加索引(减少内层表的循环匹配次数)
  3. 增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）
  4. 减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多

29.索引哪些情况下会失效？

  1. 查询条件包含 or，会导致索引失效。
  2. 隐式类型转换，会导致索引失效，例如 age 字段类型是 int，我们 where age = “1”，这样就会触发隐式类型转换
  3. like 通配符会导致索引失效，注意:”ABC%” 不会失效，会走 range 索引，”% ABC” 索引会失效
  4. 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
  5. 对索引字段进行函数运算。
  6. 对索引列运算（如，+、-、*、/），索引失效。
  7. 索引字段上使用（!= 或者 < >，not in）时，会导致索引失效。
  8. 索引字段上使用 is null， is not null，可能导致索引失效。
  9. 相 join 的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算
  10. mysql 估计使用全表扫描要比使用索引快，则不使用索引。

30.什么是覆盖索引？

覆盖索引是一种避免回表查询的优化策略: 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

具体的实现方式:

  • 将被查询的字段建立普通索引或者联合索引，这样的话就可以直接返回索引中的的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。
EXPLAIN SELECT user_name,user_age,user_level FROM users 
WHERE user_name = 'tom' AND user_age = 17;

image.png

覆盖索引的定义与注意事项:

  • 如果一个索引包含了 所有需要查询的字段的值 (不需要回表)，这个索引就是覆盖索引。
  • MySQL只能使用B+Tree索引做覆盖索引 (因为只有B+树能存储索引列值)
  • 在explain的Extra列, 如果出现 **Using index 表示 使用到了覆盖索引 , 所取的数据完全在索引中就能拿到
文章来源:https://blog.csdn.net/m0_63694520/article/details/135770403
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
最新文章