MySQL 基础

Created at 2019-05-09 Updated at 2020-07-29 Category 基础 Tag MySQL views

基本架构

客户端-连接器-查询缓存-分析器-优化器-执行器-引擎

数据类型

整型

类型 大小（字节）
tinyint 8
smallint 16
mediumint 24
int 32
bigint 64
  • 属性：unsigned
  • 长度：可以为整数类型指定宽度，他不会限制值的合法范围，只会影响字符的显示个数,当长度够会默认零填充到前面 如：int(11)

实数类型

float，double 为浮点类型，decimal 可以存储比 bigint 还大的整数，可以用于存储精确小数。
CPU 原生支持浮点运算，但是不支持 decimal 类型的计算，因此 decimal 的计算比浮点类型需要更高的代价。

字符串类型

类型 大小（字节） 说明
char 0-255 定长，如果长度设置为 n，不管 value 实际多长都会占用 n 个字符空间（用空格填充）。
varchar 0-65535 可变长度，占用的空间是 value 的实际大小，varchr 使用 1 或 2 个额外字节记录字符串长度，列长度小于 255 字节，使用 1 个字节表示，否则用两个。
text 0-65535
blob 0-65535

  • varchar(n) 与 char(n) 括号中 n 代表字符的个数，不是字符的个数。所以使用中文的时候（UTF-8）可以插入 n 个中文。

  • value 的值超过 char 和 varchar 设置的 n 之后，字符串会被截断。

  • char 适合存储很短的字符串，或者所有值接近同一个长度

  • 对于经常变更的数据，char 比 varchar 更好，char 不容易产生碎片

  • 在进行存储和检索时，会保留 varchar 末尾的空格，而会删除 char 末尾的空格。

  • blob/text 查询会使用临时表,导致性能开销

枚举类型

内部存储的是整数

日期和时间类型

  • MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。
  • timestamp 比 datetime 空间效率高
  • 如果需要存储微妙，可以使用 bigint 存储

列属性

auto_increment、default、not null、zero fill

存储引擎

InnoDB

  • 默认事务性索引
  • 数据存储在共享表空间，可以通过配置分开
  • 实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ 间隙锁（Next-Key Locking）防止幻影读。
  • 主索引是聚簇索引，在索引中保存数据，从而避免直接读取磁盘，对查询性能有很大的提升
  • 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
  • 对主键查询的性能高于其他类型的存储引擎
  • 通过一些机制和工具支持真正的热备份
  • 支持崩溃后的安全恢复
  • 支持行级锁
  • 支持外键

MyISAM

  • 索引与行记录分开存储，叫做非聚簇索引
  • 拥有全文索引，压缩表，空间数据索引
  • 不支持事务
  • 不支持行级锁
  • 不支持崩溃后的安全恢复
  • 表存储在两个文件，MYD和MYI

其他表引擎

Archive、Blackhole、CSV、Memory

三大范式

数据库的范式设计越高阶，冗余度就越低，高范式一定符合低阶范式的要求。

  • 1NF：表中的任何属性都是原子性的，不可再分。
  • 2NF：属性要完全依赖于主键，消除部分子函数依赖。
  • 3NF：属性不依赖于其他非主属性，消除传递依赖。

反范式设计

有时候为了提高运行效率，就必须降低范式标准，适当保留冗余数据。增加字段，允许冗余，达到以空间换时间的目的。

存储过程

为以后的使用而保存的一条或多条 MySQL 语句的集合。存储过程就是有业务逻辑和流程的集合。

使用场景

  • 通过把处理封装在容易使用的单元中，简化复杂的操作
  • 保证数据的一致性
  • 简化对变动的管理

触发器

提供给程序员和数据分析元来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程

使用场景

  • 可通过数据库中的相关表实现级联更改
  • 实时监控某张表中的某个字段的更改而需要做出相应的处理
  • 某些业务编号的生成

安全性

  • 使用预处理防止 sql 注入
  • 写入数据库的数据要进行特殊字符的转义
  • 查询错误信息不返回给用户，记录到日志
  • 定期数据备份
  • 不给查询用户root权限
  • 关闭远程访问数据库权限

Mysql权限管理

  • host user password 这三个信息存储在mysql.user中
  • 赋予权限 grant 权限(all,create,update…) on . to user@’host’ identified by ‘password’
  • 针对某个库授权 grant all on ecshop.* to user@’host’
  • 修改用户密码 update user set password=password(‘111111’) where XXX;
  • 收回权限 revoke all on . from user@’host’

主从复制

在主库上把数据更改记录到二进制日志，从库将主库的日志复制到自己的中继日志，从库中读取中继日志中的事件，将其重放到从库的数据中

工作原理

  1. 主服务器将用户对数据库更新的操作以二进制格式保存到 Binary Log 日志文件中，然后由 Binary Dump 线程将 Bina Log 日志文件传输给从服务器。
  2. 从服务器通过一个 I/O 线程将主服务器的 Bina Log 日志文件中的更新操作复制到一个叫 Relay Log 的中继日志文件中
  3. 从服务器通过另一个 SQL 线程将 Relay Log 中继日志文件中的操作依此在本地执行

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

  • binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
  • I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Replay log）中。
  • SQL 线程 ：负责读取重放日志并重放其中的 SQL 语句。

解决的问题

  • 数据分布：随意停止或开始复制，并在不同的地理位置分布数据备份
  • 负载均衡：将第三个服务器的压力
  • 高可用和故障切换：帮助应用程序避免单节点失败
  • 升级测试：可以使用更高版本的 MySQL 作为从库

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

  • 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
  • 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
  • 增加冗余，提高可用性。
  • 读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

数据库缓存

  • 极大的解决数据库服务器压力
  • 提高应用数据的响应速度
  • 常见的缓存形式：内存缓存，文件缓存
  • 用户请求–>数据查询–>连接数据库服务器并查询数据–>将数据缓存起来(HTML，内存，JSON，序列化数据)–>显示给客户端
  • 用户再次请求或新用户访问–>数据查询–>直接从缓存中获取数据–>显示给客户端

MySql的查询缓存

  • 启用MySql查询缓存 query_cache_type 查询缓存类型，有0、1、2三个取值。
    0不使用查询缓存。
    1始终使用查询缓存。也可以关闭查询缓存 SELECT SQL_NO_CACHE * FROM my_table WHERE condition;
    2按需使用查询缓存。 SELECT SQL_CACHE * FROM my_table WHERE condition;
  • query_cache_size 默认情况下为0，表示查询缓存预留的内存为0，无法使用查询缓存 可以临时更改 SEGLOBAL query_cache_size=134217728
  • 查询缓存可以看做是SQL文本和查询结果的映射
  • 第二次查询的SQL和第一次查询的SQL完全相同，则会使用缓存
  • SHOW STATUS LIKE ‘Qchace_hits’; 查看命中次数

1.表的结构或数据发生改变时，查询缓存中的数据不再有效

  • 清理缓存
  1. FLUSH QUERY CACHE;//清理查询缓存的内存碎片
  2. RESET QUERY CACHE;//从查询缓存中移出所有查询
  3. FLUSH TABLES;//关闭所有打开的表，同时该操作将会清空查询缓存中的内容

InnoDB 架构

InnoDB 内存结构包含四大核心组件：

  1. 缓冲池
  2. 写缓冲
  3. 自适应哈希索引
  4. 日志缓冲

缓冲池

目的是提升 InnoDB 性能，加速读请求，避免每次数据访问都进行磁盘 IO。

缓存表与索引数据，把磁盘上的数据加载到缓冲池。

因为磁盘预读特性，缓冲池一般也是按页缓存读取数据。

MySQL 不能直接用传统的 LRU 缓冲池算法，因为

  1. 预读失败
  2. 缓冲池污染

优化预读失败的方法

  1. 将 LRU 分为两个部分
    • 新生代
    • 老年代
  2. 新生代的尾连接着老年代的头
  3. 新页加入到缓冲池时，只加入到老年代的头
    • 如果数据真正被读取（预读成功），才会加入到新生代的头部
    • 如果数据没有被读取，则会比新生代里的“热数据页”更早被淘汰出缓冲池
      （1）缓冲池(buffer pool)是一种常见的降低磁盘访问的机制；

（2）缓冲池通常以页(page)为单位缓存数据；

（3）缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；

（4）InnoDB对普通LRU进行了优化：

将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题

页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题

写缓冲

目的是提升InnoDB性能，加速写请求，避免每次写入都进行磁盘IO。

它是一种应用在非唯一普通索引页(non-unique secondary index page)不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更(buffer changes)，等未来数据被读取时，再将数据合并(merge)恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘IO，提升数据库性能。

自适应哈希索引

目的是提升InnoDB性能，加速读请求，减少索引查询的寻路路径。

当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。key 是索引键值，value 是索引记录页面的位置。

日志缓冲

目的是提升InnoDB性能，极大优化 redo 日志性能，并提供了高并发与强一致性的折衷方案。

参考文章

Table of Content

  1. 基本架构
  2. 数据类型
    1. 整型
    2. 实数类型
    3. 字符串类型
    4. 枚举类型
    5. 日期和时间类型
    6. 列属性
  3. 存储引擎
    1. InnoDB
    2. MyISAM
    3. 其他表引擎
  4. 三大范式
    1. 反范式设计
  5. 存储过程
    1. 使用场景
  6. 触发器
    1. 使用场景
  7. 安全性
    1. Mysql权限管理
  8. 主从复制
    1. 工作原理
    2. 解决的问题
    3. 读写分离
  9. 数据库缓存
    1. MySql的查询缓存
  10. InnoDB 架构
    1. 缓冲池
    2. 写缓冲
    3. 自适应哈希索引
    4. 日志缓冲
  11. 参考文章
Site by Cellophane using Hexo & Random

Hide