数据库 (四) – MySQL架构

一、mysql 架构图

  • 参考:https://blog.csdn.net/m0_37574247/article/details/113932481
  • mysql 的架构可以分为4层
    • 连接层
      MySQL向外提供的交互接口;功能主要是连接处理、授权认证、安全认证。
    • 服务层
      MySQL的核心功能,包括查询解析、分析、优化、缓存、内置函数、视图、触发器、存储过程。
    • 引擎层
      负责数据的存储和提取。常用的存储引擎由 myisam 和 innodb
    • 存储层
      数据存放位置,负责和存储引擎交互。

二、存储引擎

存储引擎比较:https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-engines.html

2.1 MyISAM 存储引擎

  • MyISAM引擎特点
    • 不支持事务
    • 表级锁定
    • 读写相互阻塞,写入不能读,读时不能写
    • 只缓存索引
    • 不支持外键约束
    • 不支持聚簇索引
    • 读取数据较快,占用资源较少
    • 不支持MVCC(多版本并发控制机制)高并发
    • 崩溃恢复性较差
    • MySQL5.5.5前默认的数据库引擎
  • **MyISAM存储引擎适用场景 **
    • 只读(或者写较少)、表较小(可以接受长时间进行修复操作)
  • MyISAM引擎文件
    • tbl_name.frm 表格式定义
    • tbl_name.MYD 数据文件
    • tbl_name.MYI 索引文件

2.2 InnoDB 存储引擎

  • InnoDB引擎特点
    • 行级锁
    • 支持事务,适合处理大量短期事务
    • 读写阻塞与事务隔离级别相关
    • 可缓存数据和索引
    • 支持聚簇索引
    • 崩溃恢复性更好
    • 支持MVCC高并发
    • 从MySQL5.5后支持全文索引
    • 从MySQL5.5.5开始为默认的数据库引擎
  • InnoDB 数据库文件
    • 所有InnoDB表的数据和索引放置于同一个表空间中
      表空间文件:datadir 定义的目录下
      数据文件:ibddata1, ibddata2, …
    • 每个表单独使用一个表空间存储表的数据和索引
      启用:innodb_file_per_table=ON (>= MariaDB 5.5)
      • 两类文件放在数据库独立目录中 数据文件(存储数据和索引):tb_name.ibd
        表格式定义:tb_name.frm
  • 参考:https://mariadb.com/kb/en/innodb-system-variables/#innodb_file_per_table
  • innnodb 和 myisam 是生产上常见的存储引擎,将它们进行对比
对比项myisaminnodb
外键不支持支持
事务不支持支持
表锁,更新数据锁表,不适合高并发行锁,更新数据锁行,适合高并发
缓存只缓存索引缓存索引和数据,内存大小影响性能
表空间
索引非聚族索引聚族索引 非聚族索引

2.3 其它存储引擎

  • Performance_Schema:Performance_Schema数据库使用
  • Memory :将所有数据存储在RAM中,以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎
  • MRG_MyISAM:使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组,并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境,如数据仓库
  • Archive :为存储和检索大量很少参考的存档或安全审核信息,只支持SELECT和INSERT操作;支持行级锁和专用缓存区
  • Federated联合:用于访问其它远程MySQL服务器一个代理,它通过创建一个到远程MySQL服务器的客户端连接,并将查询传输到远程服务器执行,而后完成数据存取,提供链接单独MySQL服务器的能力,以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境
  • BDB:可替代InnoDB的事务引擎,支持COMMIT、ROLLBACK和其他事务特性
  • Cluster/NDB:MySQL的簇式数据库引擎,尤其适合于具有高性能查找要求的应用程序,这类查找需求还要求具有最高的正常工作时间和可用性
  • CSV:CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
  • BLACKHOLE :黑洞存储引擎接受但不存储数据,检索总是返回一个空集。该功能可用于分布式数据库设计,数据自动复制,但不是本地存储
  • example:“stub”引擎,它什么都不做。可以使用此引擎创建表,但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎
  • MariaDB支持的其它存储引擎:
    OQGraph、SphinxSE、TokuDB、Cassandra、CONNECT、SQUENCE

2.4 管理存储引擎

# 查看 mysql 支持的存储引擎
show engines;

# 查看当前默认的存储引擎
show variables like '%storage_engine%';

# 设置默认的存储引擎
vim /etc/my.conf

[mysqld]

default_storage_engine= InnoDB # 查看库中所有表使用的存储引擎 show table status from db_name; # 查看库中指定表的存储引擎 show table status like ‘ tb_name ‘; show create table tb_name; # 设置表的存储引擎: CREATE TABLE tb_name(… ) ENGINE=InnoDB; ALTER TABLE tb_name ENGINE=InnoDB;

2.5 MySQL 中的系统数据库

  • mysql 数据库
    是 mysql 的核心数据库,类似于 Sql Server 中的 master 库,主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息
  • performance_schema数据库
    MySQL 5.5开始新增的数据库,主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA,用户不能创建存储引擎为PERFORMANCE_SCHEMA的表
  • information_schema数据库
    MySQL 5.0之后产生的,一个虚拟数据库,物理上并不存在information_schema数据库类似与“数据字典”,提供了访问数据库元数据的方式,即数据的数据。比如数据库名或表名,列类型,访问权限(更加细化的访问方式)

三、服务器端配置

参考:https://mariadb.com/kb/en/full-list-of-mariadb-options-system-and-status-variables/

3.1 mysqld 服务器选项

参考:https://mariadb.com/kb/en/mysqld-options/
# 获取mysqld的可用选项列表
mysqld --help --verbose
mysqld --print-defaults # 获取默认设置

# 设置服务器选项方法:
# 示例:禁止域名解析
# 在命令行中设置
shell> ./mysqld_safe --skip-name-resolve=1 

# 在配置文件my.cnf中设置
skip_name_resolve=1

3.2 服务器系统变量

分全局和会话两种
# 获取系统变量
mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW [SESSION] VARIABLES;
mysql> SELECT @@VARIABLES;

# 修改服务器变量的值:
mysql> help SET

# 修改全局变量:仅对修改后新创建的会话有效;对已经建立的会话无效
mysql> SET GLOBAL system_var_name=value;
mysql> SET @@global.system_var_name=value;

# 修改会话变量:
mysql> SET [SESSION] system_var_name=value;
mysql> SET @@[session.]system_var_name=value;

3.3 服务器状态变量

分全局和会话两种
# 状态变量(只读):用于保存mysqld运行中的统计数据的变量,不可更改
mysql> SHOW GLOBAL STATUS;
mysql> SHOW [SESSION] STATUS;

# SQL_MODE:对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置,参看:https://mariadb.com/kb/en/library/sql-mode/

# 常见MODE:
NO_AUTO_CREATE_USER
# 禁止GRANT创建密码为空的用户

NO_ZERO_DATE
# 在严格模式,不允许使'0000-00-00'的时间

ONLY_FULL_GROUP_BY
# 对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么将认为这个SQL是不合法的

NO_BACKSLASH_ESCAPES
# 反斜杠"\"作为普通字符而非转义字符

PIPES_AS_CONCAT
# 将"||"视为连接操作符而非“或运算符”

四、查询缓存

4.1 查询的执行过程

4.2 查询缓存介绍

  • 查询缓存原理( Query Cache )
    缓存 SELECT 操作或预处理查询的结果集和 SQL 语句,当有新的 SELECT 语句或预处理查询语句请求,先去查询缓存,判断是否存在可用的记录集,判断标准:与缓存的 SQL 语句,是否完全一样,区分大小写
  • 优缺点
    • 不需要对 SQL 语句做任何解析和执行,当然语法解析必须通过在先,直接从 Query Cache中获得查询结果,提高查询性能
    • 查询缓存的判断规则,不够智能,也即提高了查询缓存的使用门槛,降低效率
    • 查询缓存的使用,会增加检查和清理 Query Cache 中记录集的开销
  • 哪些查询可能不会被缓存
    • 查询语句中加了SQL_NO_CACHE参数
    • 查询语句中含有获得值的函数,包含自定义函数,如:NOW()、CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等
    • 对系统数据库的查询:mysql、information_schema 查询语句中使用SESSION级别变量或存储过程中的局部变量
    • 查询语句中使用了 LOCK IN SHARE MODE、FOR UPDATE 的语句,查询语句中类似SELECT …INTO 导出数据的语句
    • 对临时表的查询操作;存在警告信息的查询语句;不涉及任何表或视图的查询语句;某用户只有列级别权限的查询语句
    • 事务隔离级别为 Serializable 时,所有查询语句都不能缓存

4.2 查询缓存相关变量

  • 查询缓存相关的服务器变量
    • query_cache_min_res_unit:查询缓存中内存块的最小分配单位,默认4k,较小值会减少浪费,但会导致更频繁的内存分配操作,较大值会带来浪费,会导致碎片过多,内存不足
    • query_cache_limit:单个查询结果能缓存的最大值,默认为1M,对于查询结果过大而无法缓存的语句,建议使用SQL_NO_CACHE
    • query_cache_size:查询缓存总共可用的内存空间;单位字节,必须是1024的整数倍,最小值40KB,低于此值有警报
    • query_cache_wlock_invalidate:如果某表被其它的会话锁定,是否仍然可以从查询缓存中返回结果,默认值为OFF,表示可以在表被其它会话锁定的场景中继续从缓存返回数据;ON则表示不允许
    • query_cache_type:是否开启缓存功能,取值为ON, OFF, DEMAND
      query_cache_type 的值为OFF或0时,查询缓存功能关闭
      query_cache_type 的值为ON或1时,查询缓存功能打开,SELECT的结果符合缓存条件即会缓存,否则,不予缓存,显式指定SQL_NO_CACHE,不予缓存,此为默认值
      query_cache_type 的值为DEMAND或2时,查询缓存功能按需进行,显式指 定SQL_CACHE的SELECT语句才会缓存;其它均不予缓存
  • 参看:
    https://mariadb.com/kb/en/library/server-system-variables/#query_cache_type
    https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html
  • 查询缓存相关的状态变量:SHOW GLOBAL STATUS LIKE ‘Qcache%’;
    • Qcache_free_blocks:处于空闲状态 Query Cache中内存 Block 数
    • Qcache_total_blocks:Query Cache 中总Block ,当 Qcache_free_blocks 相对此值较大时,可能用内存碎片,执行FLUSH QUERY CACHE清理碎片
    • Qcache_free_memory:处于空闲状态的 Query Cache 内存总量
    • Qcache_hits:Query Cache 命中次数
    • Qcache_inserts:向 Query Cache 中插入新的 Query Cache 的次数,即没有命中的次数
    • Qcache_lowmem_prunes:记录因为内存不足而被移除出查询缓存的查询数
    • Qcache_not_cached:没有被 Cache 的 SQL 数,包括无法被 Cache 的 SQL以及由于 query_cache_type 设置的不会被 Cache 的 SQL语句
    • Qcache_queries_in_cache:在 Query Cache 中的 SQL 数量

4.3 优化查询缓存

命中率和内存使用率

# 查询缓存中内存块的最小分配单位:
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

# 查询缓存命中率 :
Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%

# 查询缓存内存使用率:
(query_cache_size – qcache_free_memory) / query_cache_size * 100%

五、索引

  • 索引:是特殊数据结构,定义在查找时作为查找条件的字段,在 MySQL 又称为键 key,索引通过存储引擎实现
  • 优点
    • 索引可以降低服务需要扫描的数据量,减少了IO次数
    • 索引可以帮助服务器避免排序和使用临时表
    • 索引可以帮助将随机I/O转为顺序I/O
  • 缺点:
    • 占用额外空间,影响插入速度
  • 索引类型
    • B+ TREE、HASH、R TREE
    • 聚簇(集)索引、非聚簇索引:数据和索引是否存储在一起
    • 主键索引、二级(辅助)索引
    • 稠密索引、稀疏索引:是否索引了每一个数据项
    • 简单索引、组合索引
    • 左前缀索引:取前面的字符做索引
      覆盖索引:从索引中即可取出要查询的数据,性能高

5.1 索引类型

5.1.1 二叉树

二叉树是一种最基本最典型的排序树,主要优点就是能快速查找

5.1.2 红黑树

红黑树是一种含有红黑结点并能自平衡的二叉查找树

5.1.3 B TREE 索引

  • B tree这里的B并不是binary(二分),而是balance平衡。也就是平衡二叉树。而B+ tree是为磁盘或其它直接存取辅助设备设计的一种平衡查找树,在B+ tree中,所有记录节点都是按键值的大小顺序放在同一层的叶子节点上,由各叶子节点指针进行连接。
  • 下图图为例:若查询的数值为5:
      - 第一次磁盘IO:在内存中定位(与17、35比较),比17小,左子树;
      - 第二次磁盘IO:在内存中定位(与8、12比较),比8小,左子树;
      - 第三次磁盘IO:在内存中定位(与3、5比较),找到5,终止。

5.1.4 B+TREE 索引

  • B+树是B树的变种,有着比B树更高的查询效率。顺序存储,每一个叶子节点到根结点的距离是相同的;左前缀索引,适合查询范围类的数据
  • B树 与 B+树的区别
    • 所有的叶子结点中包含了全部关键字的信息,及指向含有这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大的顺序链接。 (而B 树的叶子节点并没有包括全部需要查找的信息)
    • 所有的非终端结点可以看成是索引部分,结点中仅含有其子树根结点中最大(或最小)关键字。 (而B 树的非终节点也包含需要查找的有效信息)
    • 关键字的数量不同;B+树中分支结点有m个关键字,其叶子结点也有m个,其关键字只是起到了一个索引的作用,但是B树虽然也有m个子结点,但是其只拥有m-1个关键字
    • 存储的位置不同;B+树中的数据都存储在叶子结点上,也就是其所有叶子结点的数据组合起来就是完整的数据,但是B树的数据存储在每一个结点中,并不仅仅存储在叶子结点上
  • 可以使用 B+Tre e索引的查询类型
    • 全值匹配:精确所有索引列,如:姓wang,名xiaochun,年龄30
    • 匹配最左前缀:即只使用索引的第一列,如:姓wang
    • 匹配列前缀:只匹配一列值开头部分,如:姓以w开头的
    • 匹配范围值:如:姓ma和姓wang之间
    • 精确匹配某一列并范围匹配另一列:如:姓wang,名以x开头的
    • 只访问索引的查询
  • B+Tree索引的限制
    • 如不从最左列开始,则无法使用索引,如:查找名为 xiaochun,或姓为g结尾
    • 不能跳过索引中的列:如:查找姓wang,年龄30的,只能使用索引第一列
  • 特别提示:
    • 索引列的顺序和查询语句的写法应相匹配,才能更好的利用索引
    • 为优化性能,可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

5.1.5 Hash索引

  • Hash索引:基于哈希表实现,只有精确匹配索引中的所有列的查询才有效,索引自身只存储索引列对应的哈希值和数据指针,索引结构紧凑,查询性能好
  • Memory存储引擎支持显式 hash 索引,InnoDB 和 MyISAM 存储引擎不支持
  • 适用场景:只支持等值比较查询,包括=, <=>, IN()
  • 不适合使用hash索引的场景
    • 不适用于顺序查询:索引存储顺序的不是值的顺序
    • 不支持模糊匹配
    • 不支持范围查询
    • 不支持部分索引列匹配查找:如A,B列索引,只查询A列索引无效

5.1.6 空间数据索引R-Tree( Geospatial indexing )

  • MyISAM支持地理空间索引,可以使用任意维度组合查询,使用特有的函数访问,常用于做地理数据存储,使用不多
  • InnoDB从MySQL5.7之后也开始支持

5.1.7 全文索引(FULLTEXT)

  • 在文本中查找关键词,而不是直接比较索引中的值,类似搜索引擎

5.1.8 聚簇和非聚簇索引,主键和二级索引

5.2 索引优化

  • 冗余和重复索引
    • 冗余索引:(A),(A,B)
    • 重复索引:已经有索引,再次建立索引
  • 索引优化策略
    • 独立地使用列:尽量避免其参与运算,独立的列指索引列不能是表达式的一部分,也不能是函数的参数,在where条件中,始终将索引列单独放在比较符号的一侧
    • 左前缀索引:构建指定索引字段的左侧的字符数,要通过索引选择性来评估索引选择性:不重复的索引值和数据表的记录总数的比值
    • 多列索引:AND操作时更适合使用多列索引,而非为每个列创建单独的索引选择合适的索引列顺序:无排序和分组时,将选择性最高放左侧
  • 索引优化建议
    • 只要列中含有NULL值,就最好不要在此例设置索引,复合索引如果有NULL值,此列在使用时也不会使用索引
    • 尽量使用短索引,如果可以,应该制定一个前缀长度
    • 对于经常在where子句使用的列,最好设置索引
    • 对于有多个列where或者order by子句,应该建立复合索引
    • 对于like语句,以%或者‘-’开头的不会使用索引,以%结尾会使用索引
    • 尽量不要在列上进行运算(函数操作和表达式操作)
    • 尽量不要使用not in和<>操作
  • SQL语句性能优化
    • 查询时,能不要就不用,尽量写全字段名
    • 大部分情况连接效率远大于子查询
    • 多表连接时,尽量小表驱动大表,即小表 join 大表
    • 在有大量记录的表分页时使用limit
    • 对于经常使用的查询,可以开启缓存
    • 多使用explain和profile分析查询语句
    • 查看慢查询日志,找出执行时间长的sql语句优化

5.3 管理索引

# 创建索引:
CREATE INDEX [UNIQUE] index_name ON tbl_name (index_col_name[(length)],...);

ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);

# 创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);


# 示例:CREATE INDEX test ON shop.Product (product_id);
# 帮助:help CREATE INDEX;

# 删除索引:
DROP INDEX index_name ON tbl_name;

ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

# 查看索引:
SHOW INDEXES FROM [db_name.]tbl_name;

# 优化表空间:
OPTIMIZE TABLE tb_name;

# 查看索引的使用
SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;

5.4 EXPLAIN

  • 获取查询执行计划信息,用来查看查询优化器如何执行查询
  • EXPLAIN 语法
EXPLAIN tbl_name [col_name | wild]
OR
EXPLAIN [explain_type] {SQL_statement | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

SQL_statement: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

5.4.1 输出信息说明

  • 参考:https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
  • id: 当前查询语句中,每个SELECT语句的编号
  • 复杂类型的查询有三种:
    – 简单子查询
    – 用于FROM中的子查询
    – 联合查询:UNION
    注意:UNION查询的分析结果会出现一个额外匿名临时表
  • select_type:
    • 简单查询为SIMPLE
    • 复杂查询:
      • SUBQUERY 简单子查询
      • PRIMARY 最外面的SELECT
      • DERIVED 用于FROM中的子查询
      • UNION UNION语句的第一个之后的SELECT语句
      • UNION RESULT 匿名临时表
  • table:SELECT语句关联到的表
  • type:关联类型或访问类型,即MySQL决定的如何去查询表中的行的方式,以下顺序,性能从低到高
    • ALL: 全表扫描
    • index:根据索引的次序进行全表扫描;如果在Extra列出现“Using index”表示了使用覆盖索引,而非全表扫描
    • range:有范围限制的根据索引实现范围扫描;扫描位置始于索引中的某一点,结束于另一点
    • ref: 根据索引返回表中匹配某单个值的所有行
    • eq_ref:仅返回一个行,但与需要额外与某个参考值做比较
    • const, system: 直接返回单个行
    • possible_keys:查询可能会用到的索引
    • key: 查询中使用到的索引
    • key_len: 在索引使用的字节数
  • ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
    • rows:MySQL估计为找所有的目标行而需要读取的行数
    • Extra:额外信息
      • Using index:MySQL将会使用覆盖索引,以避免访问表
      • Using where:MySQL服务器将在存储引擎检索后,再进行一次过滤
      • Using temporary:MySQL对结果排序时会使用临时表
      • Using filesort:对结果使用一个外部索引排序
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇