[大数据]MySQL

MySQL

基础知识

mysql读写过程

• 读

  • 1 查看缓存中是否存在id，
  • 2 如果有 则从内存中访问，否则要访问磁盘，
  • 3 并将索引数据存入内存，利用索引来访问数据,
  • 4 对于数据也会检查数据是否存在于内存，
  • 5 如果没有则访问磁盘获取数据，读入内存。
  • 6 返回结果给用户。

• 写

  • 1 先写undo log
  • 2 在内存更新数据
  • 3 记录变更到redo log，prepare
  • 4 写入binlog
  • 5 redo log 第二阶段，commit
  • 6 返回给client
  • 如果有slave
  • 第4步之后 经过slave 服务线程 io_thread 写到从库的relay log ，再由sql thread 应用relay log 到从库中。

数据库事务

select * from information_schema.innodb_trx;ch查询正在处理的事务

事务四大特性ACID

• 原子性

  • 通过类似于加锁的机制，将执行语句整体提交

• 一致性

• 隔离性

  • 数据快照，事务之间进行隔离

• 持久性

  • 数据不会凭空增加或者减少

事务类型

• 扁平事务
• 带保存点的扁平事务
• 链事务
• 嵌套事务
• 分布式事务

事务的并发问题

• 脏读

  • 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

• 幻读

  • 系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

并发写问题的解决方式就是行锁，而解决幻读用的也是锁，叫做间隙锁，MySQL 把行锁和间隙锁合并在一起，解决了并发写和幻读的问题，这个锁叫做 Next-Key锁。

• 不可重复读

  • 事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

使用事务

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

• begin
• start transaction(mysql)
• commit

伪事务

适用于不支持事务的数据库表中，如MyIsam 使用锁的方式实现事务的效果，

LOCK TABLES table_name read/write，……

UNLOCK TABLES

• 读方式锁表
• 写方式锁表
• 避免死锁

事务日志

数据库锁

锁粒度

• 行级锁

  • 开启事务时添加，结束事务时释放，两阶段锁协议

  • 死锁

    • 超时时间
    • 死锁机制，事务回滚

  • 热点行

• 页级锁

  • 开销和加锁时间界于表锁和行锁之间
  • 会出现死锁
  • 锁定粒度界于表锁和行锁之间，并发度一般

• 表级锁

  • lock table read/write

  • MDL(metadata lock)

    • 自动添加 读锁不互斥 写锁互斥
    • 多个事务之前操作，查询时修改字段容易让线程池饱满

  • 行共享 (ROW SHARE)

  • 行排他(ROW EXCLUSIVE)

  • 共享锁(SHARE)

  • 共享行排他(SHARE ROW EXCLUSIVE)

  • 排他(EXCLUSIVE)

• 全局锁

  • 全库逻辑备份

间隙锁

• 数据前后

  合理使用索引可以减小锁的范围，提高效率

加锁方式

• 自动锁

• 显示锁

  • 子主题 1

使用方式

• 乐观锁
• 悲观锁

操作方式

• DML锁
• DDL锁

读写锁

• 读锁
• 写锁

锁级别

• 排他锁
• 共享锁

特点

轻量级

高性能

支持主从部署

• MySQL Cluster

  • 行锁机制更好的支持多线程多用户并发。
  • 更好的支持读写混合语句以及扩展。
  • 可选择磁盘存储介质永久保存数据
  • Shared-nothing和分布式架构保证无单点故障。99.999% 可用性
  • 数据自己主动分布在各个节点，应用开发人员无需考虑分区或分片解决方式。
  • 支持MEMORY中不支持的变长数据类型（包含BLOB 和 TEXT）。
  • 子主题 7

未设置主键时自动生成主键

• 自增主键多为数字型 索引查找时速度较快 且相较于string类型占用长度更短
• 避免自增主键用尽

存储引擎

存储引擎的本质是一种文件访问机制

MyIsam

5.1之前默认使用该存储引擎

• 索引数据结构

  • 静态索引
  • 非聚集
  • 叶子节点存储数据地址

• 适用场景

  • 适合读多写少，并发量低的场景

• 数据文件

  • .MYD数据文件
  • .MYI索引文件

• 存储格式

  • 静态表

    • 表中字段为定长字段
    • 存储迅速
    • 去掉数据末尾空格

  • 动态表

    • 需要定期执行OPTIMIZE TABLE

  • 压缩表

• 优缺点

  • 优点

    • 读取速度快

    • 占用内存和存储资源较少

    • 顺序存储

    • 缓存总行数单独存储

      • count（）比较快

    • 支持BLOB和TEXT的前500个字符索引

    • 支持全文索引

    • 数据和索引单独存储

    • 支持延迟更新索引，极大地提升了写入性能

    • 支持压缩表

  • 缺点

    • 不支持事务
    • 插入或更新数据时需要锁表
    • 不支持外键
    • 不支持崩溃后安全恢复

• 锁

  • 支持表级锁

• 自增长

  • 自动增长列必须是索引

InnoDB

默认存储引擎

• 索引数据结构

  • B+树

    每个节点对应一个page page大小一般为16k， 非叶子结点只有键值-指针，叶子结点包含完整数据
    叶子结点为双向链表结构

    对非主键字段设置的索引其实也是非聚集索引,他们的 data 域实际上存储的是对应主键的值,当使用非主键字段作为条件查询时,它会读取到它们各自所在的 data 域(即:主键的值),然后再通过查询主键所在位置获取到完整的记录。

  • 聚集

  • 主键索引叶子节点存储数据

• 数据文件

  • 单个文件 以.ibd结尾
  • 索引和数据保存在同一个文件中
  • 文件大小受操作系统限制

• 适用场景

  • 经常更新的表
  • 事务
  • 多条SQL组合提交

• 优缺点

  • 优点

    • 查询效率更高
    • 支持事务、回滚、崩溃修复
    • 支持自动增加列属性
    • 支持行级锁，粒度更小 支持更多的并发
    • 采用MVCC来支持高并发，有可能死锁

  • 缺点

    • 非主键索引需要二次遍历
    • 不支持全文索引
    • 需要更多内存和存储空间

• 锁

  • 支持表级锁
  • 支持行级锁

• 自增长

  • 自动增长列必须是索引
  • 如果是组合索引，则必须是组合索引的第一列

• 清空表操作

  • truncate table

Memory

• 数据结构

  • hash索引

  • B树索引

    • 部分查询
    • 通配查询

• 存储类型

  • 基于内存

• 优缺点

  • 优点

    • 访问快基于内存 使用 hash索引
    • MEMORY表能够有多达每一个表64个索引，每一个索引16列，以及3072字节的最大键长度
    • MEMORY存储引擎支持HASH和BTREE索引

  • 缺点

    • 服务关闭数据丢失
    • 假设删除行。内存表不会回收内存，仅仅有整张表全部删除的时候。才进行内存回收。同一时候仅仅有在同一张表中插入新行时才会使用之前删除行的内存空间。 要释放已删除行所占用的内存空间。能够使用ALTER TABLE ENGINE=MEMORY对表进行强制重建。当内容过期要释放整张内存表。能够运行DELETE 或 TRUNCATE TABLE清除全部行，或者使用DROP TABLE删除表
    • server须要足够内存来维持全部在同一时间使用的MEMORY表

• 特征

  • MEMORY支持AUTO_INCREMENT列
  • MEMORY不能包括BLOB或TEXT列.
  • MEMORY表支持INSERT DELAYED
  • 非暂时的MEMORY表在全部client之间共享。就像其他不论什么非暂时表。
  • MEMORY表内容存储在内存中，它会作为动态查询队列创建内部暂时表的共享介质
  • MEMORY表最大值受系统变量 max_heap_table_size 限制，默觉得16MB，要改变MEMORY表限制大小，须要改变max_heap_table_size 的值。该值在 CREATE TABLE 时生效并伴随表的生命周期，(当你使用 ALTER TABLE 或 TRUNCATE TABLE命令时，表的最大限制将改变。或重新启动MYSQL服务时, 全部已存在的MEMORY表的最大限制将使用max_heap_table_size 的值重置。
  • 当MySQLserver启动时。假设你想填充MEMORY表，你能够使用–init-file选项。比如。你能够把INSERT INTO … SELECT 或LOAD DATA INFILE这种语句放入这个文件里以便从持久稳固的的数据源装载表。
  • 假设你正使用复制，当主server被关闭且重新启动动之时，主server的MEMORY表变空。但是从server意识不到这些表已经变空。所以假设你从它们选择数据。它就返回过时的内容

Archive

Federated

TokuDB

• 数据结构

  • Fractal-tree

    • FIFO队列用来缓存更新操作

• 数据文件

• 适用场景

  • 访问频率不高的数据
  • 写入性能高

数据文件

数据文件在物理磁盘中并不一定为连续的，因此查询数据需要查找整个磁盘

数据文件

• 其他
• mysql 8

重做日志

• 记录数据库变更记录的文件，用于系统异常crash(掉电)后的恢复操作

回滚日志

• 也存在于mysql 的ibdata文件，用户记录事务的回滚操作

归档日志

• 事务提交之后，记录到归档日志中。

中继日志

• 从master 获取到slave 的中转日志文件，sql_thread 则会应用relay log

其他日志

文件访问顺序

对于以上文件的IO访问顺序可以分为顺序访问 比如binlog ，redolog ，relay log是顺序读写，datafile，ibdata file是随机读写，这些IO访问的特点决定了在os 配置磁盘信息时候，如何考虑分区 ，比如顺序写可以的log 可以放到SAS 盘 ，随机读写的数据文件可以放到ssd 或者fio 高性能的存储。

索引

索引目的

• 减少IO操作，提高查询效率

索引原理

• 减少磁盘寻址时间

• 减少每次加载的无效数据

• 索引节点大小为页大小

  • innondb页大小默认为16k

• 磁盘扇区大小为页大小的整数倍

• 将页数据加载到内存中读取

索引结构

• hash索引

  • 适用场景：匹配查找

• b+索引

  • 适用场景：范围查找
  • 数据结构

• 全文索引

  • 适用场景

索引类型

• 主键索引

• 唯一索引

• 普通索引

• 联合索引

• 全文索引

  全文索引是指对char、varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。

  MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
  MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
  只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

  • 最小搜索长度
  • 最大搜索长度

• 倒序索引

• 聚集索引

• 非聚集索引

数据存储方式

• 聚簇索引

• 非聚簇索引

  • 稠密索引
  • 稀疏索引

查询效果

• 覆盖索引

  • 用 explain 的结果，extra 列会出现:using index

索引原则

• 索引列一般为条件筛选

• 重复度低的列

• 较小的数据列

• 索引列不能参与计算

• 数据较长的列使用前缀索引

• 多个列同时作为查询条件时使用联合索引

• 当or操作较多时，建议不使用索引

• 避免多个索引范围查找

• 避免冗余索引、重复索引

• 删除长期未使用索引

• 权衡索引与ACID的效率问题

• 最左前缀原则

  联合索引

SQL优化

使用limit对查询结果的记录进行限定
避免select *，将需要查找的字段列出来
使用连接（join）来代替子查询
拆分大的delete或insert语句
可通过开启慢查询日志来找出较慢的SQL
不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
不用函数和触发器，在应用程序实现
避免%xxx式查询
少用JOIN
使用同类型进行比较，比如用’123’和’123’比，123和123比
尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

• explain执行计划分析

  • table

    • 显示这一行的数据是关于哪张表的

  • type

    • 显示连接使用了何种类型

      • system

        • 表只有一行

      • const

        • 表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

      • eq_reg

        • 在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

      • ref

        • 这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

      • fulltext

      • ref_or_null

      • index_merge

      • unique_subquery

      • index_subquery

      • range

        • 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

      • index

        • 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

      • ALL

        • 这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

    • 从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

  • possible_keys

    • 显示可能应用在这张表中的索引
    • 如果为空，没有可能的索引

  • key

    • 实际使用的索引
    • 如果为NULL，则没有使用索引
    • 可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

  • key_len

    • 使用的索引的长度
    • 在不损失精确性的情况下，长度越短越好

  • ref

    • 显示索引的哪一列被使用了

  • rows

    • MYSQL认为必须检查的用来返回请求数据的行数

  • Extra

    • 关于MYSQL如何解析查询的额外信息

    • Distinct

      • 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

    • Not exists

      • MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

    • Range checked for each Record

      • 没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

    • Using filesort

      • 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

    • Using index

      • 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

    • Using temporary

      • 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

    • Where used

      • 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

• 分析查询日志

  • 生产环境禁用，仅用于测试环境对生成的SQL语句的执行分析

• 使用SQL分析工具

  • 小米SOAR

• 缓存优化

• 分页查询

  • 利用延迟关联或者子查询优化超多分页场景

• 优化原则

  • 范围索引放最后

  • 使用join 代替子查询

    • 减少在内存中创建临时表

  • 不要使用count(列名)或count(常量)来替代count(*)

    • count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行

  • count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。

  • 当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为 NULL，因此使用 sum()时需注意 NPE 问题

    • SELECT IFNULL(SUM(column), 0) FROM table;

  • 使用ISNULL()来判断是否为NULL值

  • 代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句

  • 不得使用外键与级联，一切外键概念必须在应用层解决

  • 禁止使用存储过程，存储过程难以调试和扩展，更没有移植性

  • 对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名(或 表名)进行限定

索引失效

• 索引列计算

  • 加减乘除
  • 函数计算

• 索引列类型转换

• 比较符号

  • <>
  • not in
  • not exits
  • !=

• 使用* 代替字段

• 字段类型不同

索引维护

• 页分裂
• 页合并
• 索引长度

XMind - Trial Version