天道酬勤,学无止境

《MySQL 性能优化》之 InnoDB 存储引擎

文章目录

      • InnoDB 概述
      • InnoDB 系统结构
      • InnoDB 内存结构
        • 缓冲池
        • 变更缓冲
        • 日志缓冲
        • 自适应哈希索引
      • InnoDB 磁盘结构
        • 表空间
        • 表和索引
        • 双写缓冲
        • 重做日志
        • 回滚日志

上一篇我们介绍了 MySQL 服务器的体系结构,其中插件式存储引擎是 MySQL 与其他数据库管理系统的最大区别。InnoDB 作为 MySQL 默认的存储引擎应用最为广泛;因此,本篇我们来介绍一下 InnoDB 存储引擎。

InnoDB 概述

InnoDB 是一个具有高可靠性和高性能的通用存储引擎,也是 MySQL 5.5 之后的默认存储引擎。因此,如果CREATE TABLE语句没有指定ENGINE选项,默认创建的就是 InnoDB 表。

📝使用SHOW VARIABLES LIKE 'default_storage_engine';命令可以查看默认的存储引擎。

在进一步讨论 InnoDB 体系结构之前,我们先介绍几个 InnoDB 存储引擎的关键特性:

  • InnoDB 表的数据修改操作(DML)具有事务安全性(ACID),支持事务提交、事务回滚以及故障恢复,能够保障数据的一致性和完整性;
  • InnoDB 采用更细粒度的行级锁和类似 Oracle 的一致性读(MVCC),能够提高并发性和性能。
  • InnoDB 按照主键索引(clustered index)的顺序组织表中的数据,优化了基于主键字段的查询。
  • InnoDB 支持外键约束(FOREIGN KEY),能够维护多个表之间的数据完整性。

当然,InnoDB 存储引擎提供的功能远远不止与此;正是由于这些强大的功能,使得 MySQL 能够像 Oracle、Microsoft SQL Server 等商业数据库一样大量应用在企业系统中。

InnoDB 系统结构

下图显示了 InnoDB 存储引擎的内存结构和磁盘结构。
InnoDB
记住这张图可以帮助我们理解 InnoDB 的体系结构,接下来我们分别讨论 InnoDB 的内存结构和磁盘结构。

InnoDB 内存结构

InnoDB 提供了自己的内存组件,主要包括缓冲池(Buffer Pool)、变更缓冲(Change Buffer)、日志缓冲(Log Buffer)以及自适应哈希索引(Adaptive Hash Index)技术。

缓冲池

缓冲池是 InnoDB 在内存中的一个缓冲区域,主要用于缓存访问过的表和索引等数据。缓冲池利用内存直接处理数据,避免磁盘操作,从而加快了数据处理的速度。

📝在专用的 MySQL 服务器上,通常会给缓冲池分配多达 80% 的物理内存。

以下命令显示了 InnoDB 缓冲池相关的配置:

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 8388608        |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 8388608        |
+-------------------------------------+----------------+
11 rows in set (0.00 sec)

其中,innodb_buffer_pool_chunk_size 表示每个缓冲块的大小;innodb_buffer_pool_instances 表示缓冲池的实例个数,每个实例由数量相同的缓冲块组成;innodb_buffer_pool_size 表示总的缓冲池大小,是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数。

缓冲池管理算法

为了提高大量读取操作时的效率,缓冲池被划分为页(page),每个页可能包含多行数据。为了提高缓存管理的效率,缓冲池被实现为页组成的链接列表。最终缓冲池使用特定的 LRU(最近最少使用)算法进行管理,从而将频繁访问的数据保留在缓存中,将最少使用的缓存页移除。

下图演示了缓冲池管理的 LRU 算法。
buffer pool
InnoDB 使用 LRU 算法略有改动,缓存池被分为两个部分:头部的 5/8 是最近被访问过的一个新的子列表,尾部的 3/8 是最近较少访问的一个旧的子列表。这个比例由系统变量 innodb_old_blocks_pct 控制:

mysql> show variables like 'innodb_old_blocks_pct';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set (0.00 sec)

当一个新的页需要被缓存时,最近最少使用的页将被剔除,新页将被放入缓存池的新旧子列表的中间;这种方式被称为中间点插入策略(midpoint insertion strategy)。用户提交的操作(例如 SQL 查询)或者 InnoDB 的预读(read-ahead)操作都会导致新页的缓存。

一方面,访问旧子列表中的页将会使得它被移动到新子列表的头部,变得更新。如果是用户操作引起的访问,该页将会立即被移动到新的子列表中;如果是预读操作引起的访问,不会立即导致移动,也可能根本不会移动。

另一方面,没有被访问的缓存页将会逐渐被移动到列表的尾部,变得更旧。新子列表和旧子列表中的页都会随着其他页的前移变得更旧;旧子列表中的页还会随着新页的加入变得更旧,最终到达列表的最尾部并且被剔除。

我们可以输入SHOW ENGINE INNODB STATUS命令,利用 InnoDB 标准监控输出查看缓冲池的使用指标,相关信息显示在 BUFFER POOL AND MEMORY 部分:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8585216
Dictionary memory allocated 380485
Buffer pool size   512
Free buffers       249
Database pages     259
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 997, created 142, written 156
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 259, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

其中的 Buffer pool size 是缓冲池分配的数据页数量(512),乘以 innodb_page_size(16384)等于缓冲池的大小(8388608)。

📝关于 Buffer Pool 的配置和优化我们将会在 MySQL 实例优化的部分进行介绍。

变更缓冲

变更缓冲缓存了那些不在缓冲池中的二级索引(secondary index)页的修改操作。INSERTUPDATE或者DELETE操作导致的变更将会在此缓冲,随后再合并(由其他读取操作引起)到缓冲池中。下图演示了变更缓冲的作用过程。

change buffer
与聚集索引(clustered index)不同,二级索引通常是非唯一索引,索引的插入、更新、删除通常是顺序随机的操作。将变更进行缓存,并且在随后读入缓冲池时进行合并,能够避免将辅助索引页从磁盘读入缓冲池所需的大量随机 I/O。

当系统处于空闲状态或在缓慢关闭期间运行清除操作,定期将更新后的索引页写入磁盘。相对于每次将数据即写入磁盘,这种清除操作可以更有效地写入多个连续的索引值。

在内存中,变更缓冲属于缓冲池的一部分。在磁盘上,变更缓冲属于系统表空间的一部分;当数据库服务器关闭时,索引变更将会被缓冲到磁盘中。

系统变量 innodb_change_buffering 决定了何种类型的操作会被缓冲,默认为 ALL。

如果索引中包含降序索引列或主键中包含降序索引列,就不会对二级索引进行变更缓冲。

我们同样可以输入SHOW ENGINE INNODB STATUS命令,利用 InnoDB 标准监控输出查看变更缓冲的状态信息:

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

因为变更缓冲最开始只支持插入操作,所以显示为 INSERT BUFFER AND ADAPTIVE HASH INDEX。

📝关于 Change Buffer 的配置和优化我们将会在 MySQL 实例优化的部分进行介绍。

日志缓冲

日志缓冲是重做日志(Redo Log)的内存缓冲,日志缓冲的大小由变量 innodb_log_buffer_size 决定,默认为 16 MB。日志缓冲的内容会定期刷新到磁盘文件。设置一个大的日志缓冲使得大型事务不必在提交之前将重做日志数据写入磁盘。因此,如果存在需要更新、插入或者删除大量数据的事务,可以通过增加日志缓冲的大小减少磁盘 I/O。

系统变量 innodb_flush_log_at_trx_commit 用于控制日志缓冲写入磁盘的方式。默认值为 1,即每次事务提交都会刷新缓冲到磁盘,满足 ACID 特性。

系统变量 innodb_flush_log_at_timeout 用于控制日志缓冲刷新到磁盘的频率。默认值为 1 秒,即每隔 1 秒刷新一次。

📝关于 Log Buffer 的配置和优化我们将会在 MySQL 实例优化的部分进行介绍。

自适应哈希索引

InnoDB 包含了一个监控索引查找的机制,当 InnoDB 发现哈希索引可以提高查询的性能时会自动创建哈希索引。哈希索引基于索引键的一个前缀部分创建,可能只包含了 B+树索引中的一些值,通常时频繁访问的索引页。

当一个表能够差不多完全加载到内存中,哈希索引可以直接定位到所有数据,因此能够提高查询性能。自适应哈希索引特性由变量 innodb_adaptive_hash_index 设置,默认为 ON。但是由于它需要占用缓冲池的内存,只能用于等值查询,而且只在特定的情况下有效,因此 MySQL 5.6 开始建议关闭该选项。

我们可以利用SHOW ENGINE INNODB STATUS命令查看自适应哈希索引的使用情况,相关的数据也显示在 INSERT BUFFER AND ADAPTIVE HASH INDEX 部分。

InnoDB 磁盘结构

InnoDB 提供的磁盘存储组件主要包括表空间(Tablespace)、(Table)、索引(Index)、重做日志(Redo Log)、回滚日志(Undo Logs)以及双写缓冲(Doublewrite Buffer)。

表空间

表空间是一个逻辑上的存储概念,用于存储数据表、索引、回滚(Undo)数据等。一个表空间对应操作系统上的一个或者多个文件。从逻辑概念上来说,表空间又是由段(Segment)组成,段由区间(Extent)组成,区间由页(Page)组成,页最终由行(Row)组成。
tablespace
一个 InndoDB 表通常对应一个数据段,而区间是磁盘分配的基本单位,页(默认为 16 KB)是 InndoDB 管理磁盘的最小单位,与操作系统的页(通常是 4 KB)概念不同。

InnoDB 提供的表空间包括:系统表空间(System Tablespace)、独立表空间(File-Per-Table Tablespaces)、通用表空间(General Tablespaces)、回滚表空间(Undo Tablespaces)以及临时表空间(Temporary Tablespaces)。

系统表空间

系统表空间用于存储双写缓冲和变更缓冲。如果创建表和索引时不使用独立表空间或通用表空间,它们也会被存储到系统表空间;不推荐这种做法。在 MySQL 8.0 之前,系统表空间中还包含了 InnoDB 数据字典信息;从 MySQL 8.0 开始, InnoDB 使用统一的 MySQL 数据字典存储元数据。

系统表空间可以拥有一个或多个数据文件。默认情况下在数据目录中创建一个名为 ibdata1 的系统表空间数据文件。系统表空间数据文件的大小和数量由系统参数 innodb_data_file_path 进行控制。

独立表空间

独立表空间(File-Per-Table Tablespaces)用于存储单个 InnoDB 表的数据和索引,每个表空间在文件系统中对应单个数据文件。举例来说,如果我们为 test 数据库创建一个表 t1,MySQL 会在数据目录下的 test 子目录中创建一个数据文件 t1.idb。

InnoDB 默认使用独立表空间创建表,可以使用系统变量 innodb_file_per_table 进行控制。如果禁用该参数,InnoDB 将会默认在系统表空间中创建表。

通用表空间

通用表空间是一种共享的 InnoDB 表空间,可以供多个表和索引使用。通用表空间比独立表空间具有更高的内存利用率。MySQL 服务器将会缓存表空间的元数据,包含多个表的通用表空间需要的内存比多个独立表空间更少。

通用表空间可以像独立表空间一样在 MySQL 数据目录内部或者外部创建数据文件,从而为关键的表指定单独的存储,例如 RAID 或者 DRBD,提高数据访问的性能。

通用表空间使用 CREATE TABLESPACE 语句创建。

回滚表空间

回滚表空间用于存储回滚日志,回滚日志记录中包含了撤销事务对聚集索引记录所作的最新修改所需的信息。回滚记录存储在回滚日志段中,回滚日志段存储在回滚段中。系统变量 innodb_rollback_segments 决定了每个回滚表空间分配的回滚段数量。

MySQL 实例初始化时会创建两个回滚表空间。 默认的回滚表空间在 innodb_undo_directory 参数指定的目录中创建,如果没有定义该参数,则在数据目录中创建。默认回滚表空间的数据文件名为 undo_001 和 undo_002,对应数据字典中的回滚表空间名为 innodb_undo_001 和 innodb_undo_002。

从 MySQL 8.0.14 开始,可以使用 CREATE UNDO TABLESPACE 增加额外的回滚表空间;一个 MySQL 实例最多可以存在 127 个回滚表空间,包括默认的两个回滚表空间。

临时表空间

InnoDB 存在两种临时表空间:会话临时表空间(session temporary tablespaces)和一个全局临时表空间(global temporary tablespace)。

会话临时表空间用于存储用户创建的临时表;当 InnoDB 被设置为磁盘内部临时表的存储引擎时,会话临时表空间也用于优化器创建的内部临时表。从 MySQL 8.0.16 开始,磁盘内部临时表的存储引擎永远都是 InnoDB;在此之前由参数 internal_tmp_disk_storage_engine 决定 。

系统变量 innodb_temp_tablespaces_dir 决定了会话临时表空间的文件目录,默认为数据目录下的 #innodb_temp 子目录。 表 INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES 存储了会话临时表空间的元数据,表 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 存储了当前活动的用户临时表的元数据。

全局临时表空间(ibtmp1)存储了用户临时表修改信息的回滚段数据。系统变量 innodb_temp_data_file_path 定义了全局临时表空间数据文件的相对路径、名称、大小以及属性。如果没有指定该参数,默认在 innodb_data_home_dir 目录中创建一个名为 ibtmp1 的自动扩展的数据文件,初始大小略微大于 12 MB。

表和索引

表是数据库中存储数据的主要对象,使用CREATE TABLE语句创建。

CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

其中,ENGINE 用于指定表的存储类型;如果不指定,MySQL 默认使用 InnoDB 存储引擎。使用以下命令查看表的信息:

mysql> SHOW TABLE STATUS LIKE 't%' \G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-02-17 14:32:40
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

InnoDB 表按照索引的组织方式存储数据,被称为聚簇索引(clustered index)。具体来说,

  • 如果指定了 PRIMARY KEY,InnoDB 使用主键作为聚簇索引。推荐使用生成之后不会更改、不为空且不重复、经常作为查询条件的字段作为主键,例如各种编号。如果没有逻辑上唯一且非空的字段,可以使用自增字段 AUTO_INCREMENT 作为主键。
  • 如果没有定义 PRIMARY KEY,MySQL 使用第一个非空且唯一的索引字段作为 InnoDB 表的聚集索引。
  • 如果没有定义 PRIMARY KEY 也没有合适的 UNIQUE,InnoDB 会在内部生成一个 行 ID 字段,并且创建一个隐藏的聚集索引 GEN_CLUST_INDEX 。InnoDB 为表中的每一行生成一个递增的 ID 值,并且按照该顺序存储数据。

除了聚簇索引之外的索引被称为二级索引(secondary indexes)。InnoDB 二级索引中的每个索引记录都包含了主键索引列的值,以及二级索引的字段。InnoDB 使用主键值查找聚集索引中的数据行。因此,如果主键字段很长,二级索引就需要占用更多的磁盘空间,查找的效率就会更低。这也就是为什么 InnoDB 推荐使用简单的数字作为主键。

📝关于索引和优化我们将会在 MySQL 模式优化的部分进行介绍。

双写缓冲

双写缓冲是系统表空间中的一个存储区域;在 InnoDB 将缓冲池刷新到数据文件之前,会先将缓冲页写入该区域。如果在写入数据页的过程中,出现了操作系统、存储系统或者 mysqld 进程崩溃,InnoDB 可以利用双写缓冲存储的缓冲页进行故障恢复。

由于 InnoDB 的数据页大小往往和操作系统数据页大小不一致,例如 InnoDB 为 16 KB,操作系统为 4 KB;此时 InnoDB 刷新一个数据页,操作系统需要刷新 4 个数据页,在系统故障时可能只刷新了部分数据页。双写缓冲会先把缓冲池的数据写入共享表空间,然后再刷新数据页;如果在这个过程中发生系统崩溃,InnoDB 可以从共享表空间获取到要刷新的数据,然后重新执行写入。

虽然数据需要写入两次,双写缓冲并不会导致两倍的 I/O 负载或者操作,因为双写缓冲只需要写入一个连续的数据块,只有一次 fsync() 系统调用。

双写缓冲由系统变量 innodb_doublewrite 控制,默认值为 ON。如果文件系统或者存储设备提供了防止部分写失效的功能,可以禁用双写缓冲。

重做日志

重做日志用于故障恢复时修复未完成事务的数据,它位于磁盘中,与内存中的日志缓冲相对应。在正常操作过程中,重做日志记录了表中的数据修改信息。当系统出现异常关闭后,重新启动时自动利用重做日志恢复未更新到数据文件中的修改。

默认情况下,重做日志物理上由两个文件 ib_logfile0 和 ib_logfile1 组成。MySQL 使用循环的方式写入重做日志文件。

回滚日志

回滚日志由一组回滚日志记录组成,这些记录属于单个读写事务。回滚日志记录包含了回滚一个事务对聚集索引记录的最新修改所需的信息。另外,如果另一个事务需要查看原始的数据(一致性读),将会从回滚日志记录中返回未修改前的数据。

回滚日志存储在回滚日志段中,后者包含在回滚段中;回滚段存储在回滚表空间以及全局临时表空间中。

下一篇我们将会讨论 MySQL 中的事务管理与并发控制,欢迎关注❤️、评论📝、点赞👍!

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • MySQL默认数据库之 information_schema库
    MySQL默认数据库之 information_schema库 官网: https://dev.mysql.com/doc/refman/5.7/en/information-schema.html sys 系统库中有一部分视图的数据来自information_schema。那么,什么是information_schema?information_schema组成对象又有哪些? 以下内容主要针对MySQL 5.7版本进行整理。 | 什么是information_schema information_schema提供了对数据库元数据、统计信息、以及有关MySQL Server的信息访问(例如:数据库名或表名,字段的数据类型和访问权限等)。该库中保存的信息也可以称为MySQL的数据字典或系统目录。 在每个MySQL 实例中都有一个独立的information_schema,用来存储MySQL实例中所有其他数据库的基本信息。information_schema数据库下包含多个只读表(非持久表),所以在磁盘中的数据目录下没有对应的关联文件,且不能对这些表设置触发器。虽然在查询时可以使用USE语句将默认数据库设置为information_schema,但该库下的所有表是只读的,不能执行INSERT、UPDATE、DELETE等数据变更操作。 针对information
  • mysql优化 个人笔记 非礼勿扰 -m07
    Mysql基本架构图两种优化方式RBO 基于规则优化CBO 基于成本优化 这个用的多Mysql 日志1. binlog (server) 数据库data目录下的binlog文件show VARIABLES like '%binlog%' binary log恢复数据过程找到最近的一次全量备份数据从备份的时间点开始,将备份的binlog取出来,重放到恢复的那个时刻2. redolog (存储引擎 innodb) 数据库data目录下有这俩文件当发生数据修改时,innodb存储引擎会将记录下入到redo log中,并更新 内存,此时更新就算完成了,同时innodb引擎会在何时的时机将记录写入到磁盘中redo log是固定大小的 是循环写入的有了redo log后 innodb就可以保证即使数据库发生异常重启,之前的记录也不会丢失 叫做 crash-safewal write ahead log 数据写入的时候 不是直接写入磁盘 而是先写入内存 合适时机才会写入磁盘 写入性能上感觉是提升了redolog 存储的过程 三种方式加入数据存储过程中 mysql服务器断电了 重启之后 会去redolog中 找日志 然后进行重写保证实物的持久化3. undolog(Innodb存储引擎)Undo log 是为了实现实物的原子性 在Mysql数据库Innodb存储引擎中, 还用Undo log
  • 干货!MySQL优化原理分析及优化方案总结
    说起MySQL优化的话,想必大部分人都不陌生了。在我们的记忆储备里也早已记住了这些关键词:避免使用SELECT*、避免使用NULL值的判断、根据需求适当的建立索引、优化MySQL参数......但是你对于这些优化技巧是否真正的掌握了及其相应的工作原理是否吃透了呢?在我们的实际开发过程中你能充分应用到吗?我觉得还有待考察。所以,本文将详细介绍MySQL优化技巧以及其相应的技术原理,希望大家看完以后,能更清楚直接的了解这些优化方案,并应用到我们的工作岗位中。01原理分析1MySQL架构原理在此之前我们需要先了解一下MySQL的各个部分组件的工作结构,便于我们理解MySQL服务器。如下图:MySQL工作架构大致可以分为三层,最上面为客户端,比如:连接处理、授权认证、安全等功能都在这一层处理。MySQL的核心服务在中间这一层,包括查询解析、分析、优化、缓存、内置函数。所有的跨存储引擎的功能也在这一层实现:存储过程、触发器、视图等。最下层为存储引擎负责MySQL中的数据存储和提取。和Linux下的文件系统类似,每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信,这些API接口屏蔽了不同存储引擎间的差异。2MySQL查询原理我们想让MySQL获得更高的查询性能,首先需要先了解MySQL怎样进行优化和执行查询的。先来看一下,当我们向MySQL发送了一个请求的时候,它都做了些什么呢
  • Day20 PythonWeb全栈课程课堂内容
    Day20 PythonWeb全栈课程课堂内容 1. 索引索引是什么?索引的原理(B+Tree)索引的使用索引案例查询适合建立索引的情况不适合建立索引的情况索引类型 2. 账户管理账户管理创建用户修改用户删除用户授予权限查看所有用户创建账户、授权创建账户&授权 示例1step1:使用root登录step2:创建账户并授予所有权限step3:退出root的登录step4:使用laowang账户登录 示例2 权限列表 3. 数据库存储引擎数据库存储引擎服务层连接管理器查询缓存解析器查询优化器执行器 存储引擎层查看存储引擎 4. MySQL引擎之MyISAM什么是锁?锁的类型锁的粒度MyISAM存储引擎特性MyISAM存储引擎限制适合场景 5. MySQL引擎之InnoDBMySQL引擎之Innodb系统表空间和独立表空间如何选择?Innodb存储引擎的特性MyISAM和InnoDB对比 6. MySQL引擎之CSV文件系统存储特点特点使用场景创建会遇到的问题 7. MySQL引擎之Memory功能特点如何选择存储引擎参考条件应用举例 1. 索引 索引的使用主要是为了解决MySQL查询方面的效率问题。由于当数据库的数据两很大时,查找数据会变得很慢,所以使用索引可以提高查询效率。 加了索引之后可以提高查询效率,但是会降低更新速度。 索引是什么? 索引是一种特殊的文件
  • 【DB笔试面试422】平时看哪些技术类的书籍?
    Q题目 平时看哪些技术类的书籍? A答案 根据个人的情况来回答即可。比如作者比较偏好Oracle数据库,曾经看过的书籍有《收获不止Oracle》、《基于Oracle的SQL优化》、《Oracle DBA工作笔记》、《RAC核心技术详解》等。除此之外,关于MySQL数据库,有《深入浅出MySQL数据库开发、优化与管理维护(第2版)》、《MySQL 技术内幕:InnoDB存储引擎》、《MySQL DBA工作笔记》、《MySQL管理之道:性能调优、高可用与监控》、《MySQL运维内参:MySQL、Galera、Inception核心原理与最佳实践》等。推荐大家去当当网购买,比较便宜。Oracle推荐的书籍入门:《Oracle数据库技术实用详解:教你如何成为10g OCP》《涂抹Oracle:三思笔记之一步一步学Oracle》原理和实践:《深入理解Oracle》《构建Oracle高可用环境》《Oracle DBA实战攻略:运维管理、诊断优化、高可用与最佳实践》《Oracle DBA工作笔记》SQL优化:《基于Oracle的SQL优化》《收获,不止SQL优化》《剑破冰山:Oracle开发艺术》内核技术:《Oracle RAC核心技术详解》《Oracle内核技术揭秘》《Oracle核心技术》备份恢复:《Oracle.Database.11g.RMAN备份与恢复》新特性:《Oracle 18c
  • 美团面试官:说说你对MySQL中InnoDB的了解!
    前言 InnoDB 存储引擎作为我们最常用到的存储引擎之一,充分熟悉它的的实现和运行原理,有助于我们更好地创建和维护数据库表,整理的一份MySQL学习笔记希望能帮助到你们。 InnoDB 体系架构 InnoDB 主要包括了:内存池、后台线程以及存储文件。 内存池又是由多个内存块组成的,主要包括缓存磁盘数据、redo log 缓冲等; 后台线程则包括了 :Master Thread、IO Thread 以及 Purge Thread 等; 由 InnoDB 存储引擎实现的表的存储结构文件一般包括表结构文件(.frm)、共享表空间文件(ibdata1)、独占表空间文件(ibd)以及日志文件(redo 文件等)等。 1. 内存池 我们知道,如果客户端从数据库中读取数据是直接从磁盘读取的话,无疑会带来一定的性能瓶颈,缓冲池的作用就是提高整个数据库的读写性能。 客户端读取数据时,如果数据存在于缓冲池中,客户端就会直接读取缓冲池中的数据,否则再去磁盘中读取;对于数据库中的修改数据,首先是修改在缓冲池中的数据,然后再通过 Master Thread 线程刷新到磁盘上。 理论上来说,缓冲池的内存越大越好。缓冲池中不仅缓存索引页和数据页,还包括了 undo 页,插入缓存、自适应哈希索引以及 InnoDB 地锁信息等等。 InnoDB 允许多个缓冲池实例,从而减少数据库内部资源的竞争
  • 解读MySQL性能调优“金字塔”
    计算机是一种实验的科学,性能优化是实战的艺术 蒸汽机的改进不是一蹴而就的,MySQL性能的改进也是贯穿整个MySQL发展史的。MySQL之父Monty在1981年写了MySQL的第一行代码以后,在开源的帮助下MySQL成长为目前最流行的开源数据库,同样其也凝聚了非常多的开发者、DBA、工程师的心血。 本文选自《千金良方:MySQL性能优化金字塔法则》一书,将从整体上介绍性能调优的几个方面,并借用“金字塔”理论依次介绍了硬件和系统调优、MySQL 调优以及架构调优的一些原则和方法。 本文介绍的三种调优方法是按照金字塔的调优顺序排列的,如下图所示。一般来说,自底向上调优的效果是成反比的,而越往下层调优效果越好,但是难度也越大。 按照依赖关系(架构调优要求DBA对MySQL本身有一定的了解,MySQL调优依赖于系统和硬件的相关知识)和对专业知识要求的难易程度,我们按照自上而下的顺序(硬件和系统调优、MySQL调优、架构调优)描述案例,而DBA在实际应用过程中接触和优化的顺序其实是相反的。在进行优化时,首先需要关注和优化的应该是架构,如果架构不合理,那么DBA能做的事情其实是比较有限的。 对于架构调优,在系统设计时首先需要充分考虑业务的实际情况,是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做;然后考虑写的并发量有多大,是否需要采用分布式;最后考虑读的压力是否很大
  • MySQL覆盖索引(Covering Index)
    MySQL覆盖索引(Covering Index) mysql高效索引之覆盖索引 概念 如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index),也就是平时所说的不需要回表操作 判断标准 使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询 注意 1、覆盖索引也并不适用于任意的索引类型,索引必须存储列的值 2、Hash 和full-text索引不存储值,因此MySQL只能使用B-TREE 3、并且不同的存储引擎实现覆盖索引都是不同的 4、并不是所有的存储引擎都支持它们 5、如果要使用覆盖索引,一定要注意SELECT 列表值取出需要的列,不可以是SELECT *,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降,不能为了利用覆盖索引而这么做 InnoDB 1、覆盖索引查询时除了除了索引本身的包含的列,还可以使用其默认的聚集索引列 2、这跟INNOB的索引结构有关系,主索引是B+树索引存储,也即我们所说的数据行即索引,索引即数据 3、对于INNODB的辅助索引,它的叶子节点存储的是索引值和指向主键索引的位置,然后需要通过主键在查询表的字段值,所以辅助索引存储了主键的值 4、覆盖索引也可以用上INNODB 默认的聚集索引 5、
  • 【DB笔试面试422】平时看哪些技术类的书籍?
    Q 题目 平时看哪些技术类的书籍? A 答案 根据个人的情况来回答即可。比如作者比较偏好Oracle数据库,曾经看过的书籍有《收获不止Oracle》、《基于Oracle的SQL优化》、《Oracle DBA工作笔记》、《RAC核心技术详解》等。除此之外,关于MySQL数据库,有《深入浅出MySQL数据库开发、优化与管理维护(第2版)》、《MySQL 技术内幕:InnoDB存储引擎》、《MySQL DBA工作笔记》、《MySQL管理之道:性能调优、高可用与监控》、《MySQL运维内参:MySQL、Galera、Inception核心原理与最佳实践》等。推荐大家去当当网购买,比较便宜。 Oracle推荐的书籍 入门:《Oracle数据库技术实用详解:教你如何成为10g OCP》《涂抹Oracle:三思笔记之一步一步学Oracle》 原理和实践:《深入理解Oracle》《构建Oracle高可用环境》《Oracle DBA实战攻略:运维管理、诊断优化、高可用与最佳实践》《Oracle DBA工作笔记》 SQL优化:《基于Oracle的SQL优化》《收获,不止SQL优化》《剑破冰山:Oracle开发艺术》 内核技术:《Oracle RAC核心技术详解》《Oracle内核技术揭秘》《Oracle核心技术》 备份恢复:《Oracle.Database.11g.RMAN备份与恢复》 新特性:
  • 20个数据库常见面试题讲解()
    20个数据库常见面试题讲解()进了互联网公司,整天也就是搬砖,等到了面试的时候,发现数据库方面,忘得一塌糊涂,抽时间整理了一些数据库方面的题。欢迎大家向我推荐你在面试过程中遇到的问题,我会把大家推荐的问题添加到下面的常用面试题清单中供大家参考。1.事务四大特性(ACID)原子性、一致性、隔离性、持久性?2.事务的并发?事务隔离级别,每个级别会引发什么问题,MySQL默认是哪个级别?3.MySQL常见的三种存储引擎(InnoDB、MyISAM、MEMORY)的区别?4.MySQL的MyISAM与InnoDB两种存储引擎在,事务、锁级别,各自的适用场景?5.查询语句不同元素(where、jion、limit、group by、having等等)执行先后顺序?6.什么是临时表,临时表什么时候删除?7.MySQL B+Tree索引和Hash索引的区别?8.sql查询语句确定创建哪种类型的索引?如何优化查询?9.聚集索引和非聚集索引区别?10.有哪些锁(乐观锁悲观锁),select 时怎么加排它锁?11.非关系型数据库和关系型数据库区别,优势比较?12.数据库三范式,根据某个场景设计数据表?13.数据库的读写分离、主从复制,主从复制分析的 7 个问题?14.使用explain优化sql和索引?15.MySQL慢查询怎么解决?16.什么是 内连接、外连接、交叉连接、笛卡尔积等?17
  • MySQL索引之哈希索引和自适应哈希索引(Adaptive Hash Index)
    MySQL索引之哈希索引和自适应哈希索引(Adaptive Hash Index) 官网:https://dev.mysql.com/doc/refman/5.6/en/innodb-adaptive-hash.html 索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。 从MySQL逻辑架构来看,MySQL有三层架构,第一层连接,第二层查询解析、分析、优化、视图、缓存,第三层,存储引擎。 索引通过分开查询片,节省了扫描查找时间,大大提升查询效率。 大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。 索引主要在存储引擎层上,不同的引擎也就有不同的B-Tree算法。 0x01.Hash Index 哈希索引只有Memory, NDB两种引擎支持,Memory引擎默认支持哈希索引,如果多个hash值相同,出现哈希碰撞,那么索引以链表方式存储。 但是,Memory引擎表只对能够适合机器的内存切实有限的数据集。 要使InnoDB或MyISAM支持哈希索引,可以通过伪哈希索引来实现,叫自适应哈希索引。 主要通过增加一个字段,存储hash值,将hash值建立索引,在插入和更新的时候,建立触发器,自动添加计算后的hash到表里。 直接索引 假如有一个非常非常大的表,如下:CREATE TABLE IF NOT
  • MySQL之性能优化
    文章目录 一、一些概念知识1、各数据库存储引擎a、MyISAM存储引擎b、InnoDB存储引擎c、MEMORY存储引擎d、MERGE存储引擎 2、MySQL的两种索引a、B-Tree索引b、hash索引c、索引的相关概念d、存储引擎及文件格式比较e、建索引的目的 二、sql的优化1、大批量插入数据2、优化INSERT语句3、查询优化4、不使用索引的情况5、建议不建立索引的情况 三、explain执行计划1、explain的结果解析a、select_typeb、typec、Extra MySQL执行计划的局限 四、MySQL配置文件1、配置文件2、缓存配置3、Innodb缓存4、连接数5、线程池相关参数6、慢查询日志 五、MySQL主从复制1、MySQL复制的原理2、主从复制配置 一、一些概念知识 1、各数据库存储引擎 mysql的存储引擎是针对表进行设置的,一个库的不同表可以设置不同的存储引擎,mysql默认支持多种存储引擎,以适用不同领域的数据库应用需要,主要的几个数据库引擎如下: a、MyISAM存储引擎 5.5之前默认的存储引擎,不支持事务、不支持外键,表级锁,内存和硬盘空间占用率低,其优势是访问速度快,对事务完整性没有要求,以select、insert为主的应用基本上都可以使用这个引擎; b、InnoDB存储引擎 5.5之后默认的存储引擎,提供了具有提交
  • 面试官:说一说 MySQL 与 PostgreSQL 的区别,如何技术选型?
    问题?如果打算为项目选择一款免费、开源的数据库,那么你可能会在MySQL与PostgreSQL之间犹豫不定。MySQL与PostgreSQL都是免费、开源、强大、且功能丰富的数据库。你主要的问题可能是:哪一个才是最好的开源数据库,MySQL还是PostgreSQL呢?该选择哪一个开源数据库呢?在选择数据库时,你所做的是个长期的决策,因为后面如果再改变决定将是非常困难且代价高昂的。你希望一开始就选择正确。两个流行的开源数据库MySQL与PostgreSQL常常成为最后要选择的产品。对这两个开源数据库的高层次概览将会有助于你选择最适合自己需要的。MySQLMySQL相对来说比较年轻,首度出现在1994年。它声称自己是最流行的开源数据库。MySQL就是LAMP(用于Web开发的软件包,包括 Linux、Apache及Perl/PHP/Python)中的M。构建在LAMP栈之上的大多数应用都会使用MySQL,包括那些知名的应用,如 WordPress、Drupal、Zend及phpBB等。一开始,MySQL的设计目标是成为一个快速的Web服务器后端,使用快速的索引序列访问方法(ISAM),不支持ACID。经过早期快速的发展之 后,MySQL开始支持更多的存储引擎,并通过InnoDB引擎实现了ACID。MySQL还支持其他存储引擎,提供了临时表的功能(使用MEMORY存 储引擎)
  • 【揭秘】数据库面试葵花宝典,让你面试一次过
    大家可以叫我老张,网名superZS!一直从事数据库行业10余年,工作于某数据库服务公司,兼数据库资深讲师,就面试中大家遇到的比较困惑的数据库问题,和刚进入数据库领域的同学们,我在这里给大家做一个详细的总结,希望对大家在工作或者面试中有所帮助,老师会倾囊相授,道行尚浅,大家相互学习!让我们努力学习技术,为了拿到高薪,追到心仪的姑娘,而奋斗吧!葵花宝典Question 1:你目前接触的mysql版本是什么?除了官方版本,还接触过其他的mysql分支版本嘛?产生分支的原因许多开发人员认为有必要将其拆分成其他项目,并且每个分支项目都有自己的专长。该需求以及Oracle对核心产品增长缓慢的担忧,导致出现了许多开发人员感兴趣的子项目和分支三个流行MySQL分支:Drizzle、MariaDB和Percona Server(包括XtraDB引擎)MariaDB不仅是mysql的替代品,主要还是创新和提高mysql自有技术。 新功能介绍multi-source replication 多源复制表的并行复制galera cluster集群spider水平分片tokuDB存储引擎XtraDB是innodb存储引擎的增强版,可用来更好地发挥最新的计算机硬件系统性能,还包含在高性能模式下的新特性。它可以向下兼容,因为它是在innodb基础上构建,所以他有更多的指标和扩展功能。而且它在cpu多核的条件下
  • mysql优化 个人笔记 非礼勿扰 -m04
    1. 索引干啥的加快查询效率!! 帮助mysql高效获取数据结构2. 索引怎么用-- 添加索引 ALTER TABLE `test` ADD INDEX `n_uid_title` (`uid`, `title`) USING BTREE ;-- 删除索引ALTER TABLE `test` DROP INDEX `n_uid_title` ;3. 索引存在哪儿磁盘 ? 内存 ? 当然是磁盘了~ sql查询的时候 会把磁盘中索引加载到内存。4. Mysql 简单架构客户端->服务端 服务端:连接器-》分析器-》优化器-》执行器 存储引擎:InnoDB MyISAM memory5. 知识点不同的存储引擎,数据文件与索引文件存储的位置不同,因此有了分类:聚簇索引 数据和索引放在一起 *.frm 存放的是表结构 *.idb 存放的是数据文件和索引文件show VARIABLES like '%per_table%' mysql innodb 默认情况下会把所有数据放到表空间中(表空间就是一个数据库的概念)不会为每一个表保存一份数据文件 ,如果需要单独使用文件保存 需要设置innodb_file_per_table=ON非聚簇索引 数据和索引单独一个文件 *.frm 存放表结构 *.MYI 存放索引数据 *.MYD 存放实际数据6. 哈希表做索引
  • Mysql - InnoDB三大特性之双写缓冲区(Double Write Buffer)
    双写缓冲区是InnoDB的三大特性之一,还有两个是 Buffer Pool简称BP、自适应Hash索引。doublewrite缓冲区是一个存储区,在该存储区中,InnoDB将页面写入InnoDB数据文件中的适当位置之前,先从缓冲池中刷新页面 。如果在页面写入过程中存在操作系统,存储子系统或意外的mysqld进程退出,则InnoDB可以在崩溃恢复期间从doublewrite缓冲区中找到页面的良好副本。注意:系统恢复后,MySQL 可以根据redolog 进行恢复,而mysql在恢复的过程中是检查page的checksum,checksum就是pgae的最后事务号,发生partial page write 问题时,page已经损坏,找不到该page中的事务号,就无法恢复。 为什么需要双写?个人理解宏观上还是与InnoDB需要支持事务(ACID)特性有关,而底层的原因是为了解决Partial Write Page问题。 之前在分析Mysql - InnoDB引擎对事务ACID的实现原理分析时个人认为已经透彻的分析了事务的实现过程,而为了实现事务InnoDB引入了比较多的组件,设计的特别复杂,InnoDB级别包括:(行锁、临建锁、间隙锁)锁和加锁规则、MVCC、redo log、undo log、视图(Read View)。而官方文档也在隔离型和持久性上面明确指向了数据双写机制,如下图
  • MySQL覆盖索引(Covering Index)
    MySQL覆盖索引(Covering Index) mysql高效索引之覆盖索引概念如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index),也就是平时所说的不需要回表操作判断标准使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询 注意1、覆盖索引也并不适用于任意的索引类型,索引必须存储列的值2、Hash 和full-text索引不存储值,因此MySQL只能使用B-TREE3、并且不同的存储引擎实现覆盖索引都是不同的4、并不是所有的存储引擎都支持它们5、如果要使用覆盖索引,一定要注意SELECT 列表值取出需要的列,不可以是SELECT *,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降,不能为了利用覆盖索引而这么做 InnoDB1、覆盖索引查询时除了除了索引本身的包含的列,还可以使用其默认的聚集索引列2、这跟INNOB的索引结构有关系,主索引是B+树索引存储,也即我们所说的数据行即索引,索引即数据3、对于INNODB的辅助索引,它的叶子节点存储的是索引值和指向主键索引的位置,然后需要通过主键在查询表的字段值,所以辅助索引存储了主键的值4、覆盖索引也可以用上INNODB 默认的聚集索引5、 innodb引擎的所有储存了主键ID
  • MySQL索引之哈希索引和自适应哈希索引(Adaptive Hash Index)
    索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。 从MySQL逻辑架构来看,MySQL有三层架构,第一层连接,第二层查询解析、分析、优化、视图、缓存,第三层,存储引擎。索引通过分开查询片,节省了扫描查找时间,大大提升查询效率。大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。索引主要在存储引擎层上,不同的引擎也就有不同的B-Tree算法。0x01.Hash Index哈希索引只有Memory, NDB两种引擎支持,Memory引擎默认支持哈希索引,如果多个hash值相同,出现哈希碰撞,那么索引以链表方式存储。但是,Memory引擎表只对能够适合机器的内存切实有限的数据集。要使InnoDB或MyISAM支持哈希索引,可以通过伪哈希索引来实现,叫自适应哈希索引。主要通过增加一个字段,存储hash值,将hash值建立索引,在插入和更新的时候,建立触发器,自动添加计算后的hash到表里。直接索引假如有一个非常非常大的表,如下:CREATE TABLE IF NOT EXISTS `User` ( `id` int(10) NOT NULL COMMENT '自增id', `name` varchar(128) NOT NULL DEFAULT '' COMMENT '用户名', `email`
  • MySQL存储引擎概述
    目录一、mysql存储引擎概述1.1 什么是存储引擎?1.2 mysql支持哪些存储引擎?二、各种存储引擎的特性2.1 概览2.2 各种存储引擎的特性2.3 各种搜索引擎介绍三、常用存储引擎及适用场景四、存储引擎在mysql中的使用4.2.1 在建表时指定4.2.2 在配置文件中指定4.1 存储引擎相关sql语句4.2 指定存储引擎建表五、mysql的工作流程一、mysql存储引擎概述1.1 什么是存储引擎?MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。例如,如果你在研究大量的临时数据,你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者,你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎,可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎,以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性
  • MySQL 5.7参考手册 目录
    Mysql现在在电商广泛应用,为了更好的处理数据库。MySQL 5.7参考手册 是权威的mysql文档,其中包含了NDB7.5 和 NDB 7.6的内容MySQL 5.7参考手册目录:序言和法律声明 1一般信息 1.1关于本手册 1.2排版和语法约定 1.3 MySQL数据库管理系统概述1.3.1什么是MySQL? 1.3.2 MySQL的主要特点 1.3.3 MySQL历史 1.4 MySQL的新功能5.7 1.5在MySQL 5.7中添加,不推荐或删除的服务器和状态变量和选项 1.6 MySQL信息源 1.6.1 MySQL网站 1.6.2 MySQL邮件列表 1.6.3 MySQL论坛上的MySQL社区支持 1.6.4互联网中继聊天(IRC)上的MySQL社区支持 1.6.5 MySQL企业版 1.7如何报告错误或问题 1.8 MySQL标准合规性 1.8.1 MySQL扩展到标准SQL 1.8.2 MySQL与标准SQL的差异 1.8.3 MySQL如何处理约束 1.9学分 1.9.1 MySQL的贡献者 1.9.2文件和翻译 1.9.3支持MySQL的软件包 1.9.4用于创建MySQL的工具 1.9.5 MySQL的支持者 2安装和升级MySQL 2.1一般安装指导 2.1.1要安装哪个MySQL版本和分发版 2.1.2如何获取MySQL 2.1