数据库 MySQL
2024-03-24 21:17:49 0 举报
AI智能生成
登录查看完整内容
数据库 MySQL
作者其他创作
大纲/内容
关系型数据库(RDB,Relational Database)就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系(一对一、一对多、多对多)。
MySQL、PostgreSQL、Oracle、SQL Server、SQLite(微信本地的聊天记录的存储就是用的 SQLite) ……。
数据库
MySQL 是一种关系型数据库,主要用于持久化存储我们的系统中的一些数据比如用户信息。
定义
成熟稳定,功能完善。
开源免费。
文档丰富,既有详细的官方文档,又有非常多优质文章可供参考学习。
开箱即用,操作简单,维护成本低。
兼容性好,支持常见的操作系统,支持多种开发语言。
社区活跃,生态完善。
事务支持优秀, InnoDB 存储引擎不会有任何性能损失,并且可以解决幻读问题发生的。
支持分库分表、读写分离、高可用。
优点
数值类型:整型(TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT)、浮点型(FLOAT 和 DOUBLE)、定点型(DECIMAL)
字符串类型:CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等。
日期时间类型:YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。
表示不允许负值的无符号整数,将正整数的上限提高一倍。
UNSIGNED
CHAR 是定长字符串,VARCHAR 是变长字符串。
CHAR 和 VARCHAR
DECIMAL 是定点数,FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值,FLOAT/DOUBLE 只能存储近似的小数值。
DECIMAL 和 FLOAT/DOUBLE
BLOB 类型主要用于存储二进制大对象,例如图片、音视频等文件。
不推荐使用,很少使用 TEXT 类型,只偶尔会用到,而 BLOB 类型则基本不常用。
TEXT 和 BLOB
DATETIME 类型没有时区信息,TIMESTAMP 和时区有关。
DATETIME 和 TIMESTAMP
NULL 代表一个不确定的值,就算是两个 NULL,它俩也不一定相等。
''的长度是 0,是不占用空间的,而 NULL 是需要占用空间的。
NULL 会影响聚合函数的结果。
查询 NULL 值时,必须使用 IS NULL 或 IS NOT NULLl 来判断。
NULL 和 ''
字段类型
MySQL 主要分为 Server 层和引擎层。
连接器: 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存: 执行查询语句的时候,会先查询缓存(MySQL 8.0 版本后移除,因为这个功能不太实用)。
分析器: 没有命中缓存的话,SQL 语句就会经过分析器,分析器分为词法分析和语法分析。
优化器: 按照 MySQL 认为最优的方案去执行。
执行器: 执行语句,然后从存储引擎返回数据。 执行语句之前会先判断是否有权限,如果没有权限的话,就会报错。
Server 层
插件式存储引擎:主要负责数据的存储和读取,采用的是插件式架构,支持 InnoDB、MyISAM、Memory 等多种存储引擎。
引擎层
查询语句的执行流程:权限校验(如果命中缓存)--->查询缓存--->分析器--->优化器--->权限校验--->执行器--->引擎。
更新语句执行流程:分析器---->权限校验---->执行器--->引擎---redo log(prepare 状态)--->binlog--->redo log(commit 状态)。
结构
MySQL 5.5.5 之前,MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后,InnoDB 是 MySQL 的默认存储引擎。
存储引擎采用的是 插件式架构 ,支持多种存储引擎。存储引擎是基于表的,而不是数据库。
MyISAM 只有表级锁(table-level locking),而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
MyISAM 不支持事务,而 InnoDB 支持,并实现了 SQL 标准定义了四个隔离级别,具有提交(commit)和回滚(rollback)事务的能力。
MyISAM 不支持外键,而 InnoDB 支持。
MyISAM 不支持数据库异常崩溃后的安全恢复,而 InnoDB 支持。
MVCC 可以看作是行级锁的一个升级,可以有效减少加锁操作,提高性能。
MyISAM 不支持 MVCC,而 InnoDB 支持。
虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构,但是两者的实现方式不太一样。InnoDB 引擎中,其数据文件本身就是索引文件。相比 MyISAM,索引文件和数据文件是分离的。
InnoDB 的性能比 MyISAM 更强大,随着 CPU 核数的增加,差距更明显。
MyISAM 和 InnoDB
存储引擎
索引(Index)是一种用于快速查询和检索数据的数据结构,其本质可以看成是一种排序好的数据结构。
通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
创建索引和维护索引需要耗费许多时间。
索引需要使用物理文件存储,也会耗费一定空间。
缺点
B 树, B+树 和 Hash、红黑树。在 MySQL 中,无论是 Innodb 还是 MyIsam,都使用了 B+树作为索引结构。
哈希表是键值对的集合,通过键(key)即可快速取出对应的值(value),因此哈希表可以快速检索数据(接近 O(1))。
哈希算法(也叫散列算法)
原理
InnoDB 存储引擎中存在一种特殊的“自适应哈希索引”(Adaptive Hash Index),结合了 B+Tree 和哈希索引的特点。
Hash 冲突
因为 Hash 索引不支持顺序和范围查询,MySQL 没有使用其作为索引的数据结构。
Hash 表
二叉查找树(Binary Search Tree)是一种基于二叉树的数据结构。
左子树所有节点的值均小于根节点的值。
右子树所有节点的值均大于根节点的值。
左右子树也分别为二叉查找树。
特点
二叉查找树的性能非常依赖于它的平衡程度,这就导致其不适合作为 MySQL 底层索引的数据结构。
二叉查找树(BST)
计算机科学中最早被发明的自平衡二叉查找树。
保证任何节点的左右子树高度之差不超过 1,因此也被称为高度平衡二叉树。
由于 AVL 树需要频繁地进行旋转操作来保持平衡,因此会有较大的计算开销进而降低了查询性能。
AVL 树
红黑树是一种自平衡二叉查找树,通过在插入和删除节点时进行颜色变换和旋转操作,使得树始终保持平衡状态。
每个节点非红即黑。
根节点总是黑色的。
每个叶子节点都是黑色的空节点(NIL 节点)。
如果节点是红色的,则它的子节点必须是黑色的(反之不一定)。
从根节点到叶节点或空子节点的每条路径,必须包含相同数目的黑色节点(即相同的黑色高度)。
因为红黑树的平衡性相对较弱,高度较高的树的查询可能会导致多次磁盘 IO 操作,这也是 MySQL 没有选择红黑树的主要原因。
红黑树
B 树也称 B-树,全称为 多路平衡查找树 ,B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced (平衡)的意思。
大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。
B+树与 B 树相比,具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。
B 树 & B+树
BTree 索引:MySQL 里默认和最常用的索引类型。
哈希索引:类似键值对的形式,一次即可定位。
RTree 索引:一般不会使用,仅支持 geometry 数据类型,优势在于范围查找,效率较低,通常使用搜索引擎如 ElasticSearch 代替。
对文本的内容进行分词,进行搜索。目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。一般不会使用,效率较低,通常使用搜索引擎如 ElasticSearch 代替。
按数据结构
聚簇索引(聚集索引):索引结构和数据一起存放的索引,InnoDB 中的主键索引就属于聚簇索引。
非聚簇索引(非聚集索引):索引结构和数据分开存放的索引,二级索引(辅助索引)就属于非聚簇索引。
按底层存储方式
主键索引:加速查询 + 列值唯一(不可以有 NULL)+ 表中只有一个。
普通索引:仅加速查询。
唯一索引:加速查询 + 列值唯一(可以有 NULL)。
覆盖索引:一个索引包含(或者说覆盖)所有需要查询的字段的值。
联合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并。
全文索引:对文本的内容进行分词,进行搜索。
按应用维度
类型
隐藏索引:也称为不可见索引,不会被优化器使用,但是仍然需要维护,通常会软删除和灰度发布的场景中使用。主键不能设置为隐藏。
降序索引:MySQL 8.x 版本才开始真正支持降序索引。另外,在 MySQL 8.x 版本中,不再对 GROUP BY 语句进行隐式排序。
函数索引:从 MySQL 8.0.13 版本开始支持在索引中使用函数或者表达式的值,也就是在索引中可以包含函数或者表达式。
MySQL 8.x 新特性
数据表的主键列使用的就是主键索引。
一张数据表有只能有一个主键,并且主键不能为 null,不能重复。
主键索引(Primary Key)
二级索引(Secondary Index)又称为辅助索引,是因为二级索引的叶子节点存储的数据是主键。也就是说,通过二级索引,可以定位主键的位置。
唯一索引,普通索引,前缀索引等索引属于二级索引。
唯一索引(Unique Key):唯一索引也是一种约束。不能出现重复的数据,但是允许数据为 NULL,一张表允许创建多个唯一索引。
普通索引(Index):普通索引的唯一作用就是为了快速查询数据,一张表允许创建多个普通索引,并允许数据重复和 NULL。
前缀索引(Prefix):前缀索引只适用于字符串类型的数据。
全文索引(Full Text):全文索引主要是为了检索大文本数据中的关键字的信息,是目前搜索引擎数据库使用的一种技术。
二级索引
聚簇索引(Clustered Index)即索引结构和数据一起存放的索引,并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。
查询速度非常快、对排序查找和范围查找优化。
依赖于有序的数据、更新代价大。
聚簇索引(聚集索引)
非聚簇索引(Non-Clustered Index)即索引结构和数据分开存放的索引,并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎,不管主键还是非主键,使用的都是非聚簇索引。
更新代价比聚簇索引要小 。
依赖于有序的数据、可能会二次查询(回表)。
非聚簇索引(非聚集索引)
覆盖索引(Covering Index),一个索引包含(或者说覆盖)所有需要查询的字段的值。
覆盖索引
使用表中的多个字段创建索引,就是 联合索引,也叫 组合索引 或 复合索引。
联合索引
在使用联合索引时,MySQL 会根据联合索引中的字段顺序,从左到右依次到查询条件中去匹配,如果查询条件中存在与联合索引中最左侧字段相匹配的字段,则就会使用该字段过滤一批数据,直至联合索引中全部字段匹配完成,或者在执行过程中遇到范围查询(如 >、< )才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询,并不会停止匹配。所以,我们在使用联合索引时,可以将区分度高的字段放在最左边,这也可以过滤更多数据。
最左前缀匹配原则
索引下推(Index Condition Pushdown) 是 MySQL 5.6 版本中提供的一项索引优化功能,可以在非聚簇索引遍历过程中,对索引中包含的字段先做判断,过滤掉不符合条件的记录,减少回表次数。
索引下推
不为 NULL 的字段。
被频繁查询的字段。
被作为条件查询的字段。
频繁需要排序的字段。
被经常频繁用于连接的字段。
选择合适的字段创建索引
被频繁更新的字段应该慎重建立索引。
建议单张表索引不超过 5 个!
限制每张表上的索引数量
尽可能的考虑建立联合索引而不是单列索引。
注意避免冗余索引。
字符串类型的字段使用前缀索引代替普通索引。
SELECT * 不会直接导致索引失效,但它可能会带来浪费、无法使用索引覆盖。
创建了组合索引,但查询条件未遵守最左匹配原则。
在索引列上进行计算、函数、类型转换等操作。
以 % 开头的 LIKE 查询比如 LIKE '%abc'。
查询条件中使用 OR,且 OR 的前后条件中有一个列没有索引,涉及的索引都不会被使用到。
IN 的取值范围较大时会导致索引失效,走全表扫描(NOT IN 和 IN 的失效场景相同)。
发生隐式转换。
避免索引失效
删除长期未使用的索引。
知道如何分析语句是否走索引查询,EXPLAIN 执行计划。
使用建议
索引
主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中,比较重要的还要属二进制日志 binlog(归档日志)和事务日志 redo log(重做日志)和 undo log(回滚日志)。
InnoDB 存储引擎独有的,它让 MySQL 拥有了崩溃恢复能力。
redo log 是物理日志,记录内容是“在某个数据页上做了什么修改”,属于 InnoDB 存储引擎。
MySQL 实例挂了或宕机了,重启时,InnoDB存储引擎会使用redo log恢复数据,保证数据的持久性与完整性。
作用
MySQL 中数据是以页为单位,你查询一条记录,会从硬盘把一页的数据加载出来,加载出来的数据叫数据页。
数据页
过程
每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成。
组成
事务提交:当事务提交时,log buffer 里的 redo log 会被刷新到磁盘。
log buffer 空间不足时:log buffer 中缓存的 redo log 已经占满了 log buffer 总容量的大约一半左右,就需要把这些日志刷新到磁盘上。
事务日志缓冲区满:InnoDB 使用一个事务日志缓冲区(transaction log buffer)来暂时存储事务的重做日志条目。
Checkpoint(检查点):InnoDB 定期会执行检查点操作,将内存中的脏数据刷新到磁盘,并且会将相应的重做日志一同刷新,以确保数据的一致性。
后台刷新线程:InnoDB 启动了一个后台线程,负责周期性(每隔 1 秒)地将脏页刷新到磁盘,并将相关的重做日志一同刷新。
正常关闭服务器:MySQL 关闭的时候,redo log 都会刷入到磁盘里去。
刷盘时机
设置参数 innodb_flush_log_at_trx_commit,默认值为1。
0:设置为 0 的时候,表示每次事务提交时不进行刷盘操作。性能最高,但也最不安全。
1:设置为 1 的时候,表示每次事务提交时都将进行刷盘操作。性能最低,但也最安全。
2:设置为 2 的时候,表示每次事务提交时都只把 log buffer 里的 redo log 内容写入 page cache(文件系统缓存)。
刷盘策略
硬盘上存储的 redo log 日志文件不只一个,而是以一个日志文件组的形式出现的,每个的redo日志文件大小都是一样的。
它采用的是环形数组形式,从头开始写,写到末尾又回到头循环写。
write pos:是当前记录的位置,一边写一边后移。
checkpoint:是当前要擦除的位置,也是往后推移。
如果 write pos 追上 checkpoint ,表示日志文件组满了,这时候不能再写入新的 redo log 记录。MySQL 得停下来,清空一些记录,把 checkpoint 推进一下。
重要属性
日志文件组
redo log(重做日志)
binlog(归档日志)保证了 MySQL 集群架构的数据一致性。
MySQL数据库的数据备份、主备、主主、主从都离不开binlog,需要依靠binlog来同步数据,保证数据一致性。
binlog 是逻辑日志,记录内容是语句的原始逻辑,类似于“给 ID=2 这一行的 c 字段加 1”,属于MySQL Server 层。
binlog会记录所有涉及更新数据的逻辑操作,并且是顺序写。
binlog 日志有三种格式,可以通过 binlog_format 参数指定。
记录的内容是SQL语句原文。
update_time=now() 这里会获取当前系统时间,直接执行会导致与原库的数据不一致。
问题
statement
记录的内容不再是简单的SQL语句了,还包含操作的具体数据。
需要更大的容量来记录,比较占用空间,恢复与同步时会更消耗IO资源,影响执行速度。
row
statement 和 row 的混合,属于折中方案。
MySQL会判断这条SQL语句是否可能引起数据不一致,如果是,就用row格式,否则就用statement格式。
mixed
记录格式
事务执行过程中,先把日志写到 binlog cache,事务提交的时候,再把 binlog cache 写到 binlog 文件中。
write,是指把日志写入到文件系统的 page cache,并没有把数据持久化到磁盘,所以速度比较快。
fsync,才是将数据持久化到磁盘的操作。
write 和 fsync 的时机,可以由参数 sync_binlog 控制,默认是1。为0时,表示每次提交事务都只 write,由系统自行判断什么时候执行 fsync。为1时,表示每次提交事务都会执行 fsync,就如同 redo log 日志刷盘流程 一样。为N(N>1)时,表示每次提交事务都 write,但累积N个事务后才 fsync。
写入机制
binlog(归档日志)
为了解决两份日志(redo log 和 binlog)之间的逻辑一致问题,InnoDB存储引擎使用两阶段提交方案。
将 redo log 的写入拆成了两个步骤 prepare 和 commit,这就是两阶段提交。
MySQL 根据 redo log 日志恢复数据时,发现 redo log 还处于 prepare 阶段,并且没有对应 binlog 日志,就会回滚该事务。
两阶段提交
在 MySQL 中,恢复机制是通过 回滚日志(undo log) 实现的,所有事务进行的修改都会先记录到这个回滚日志中,然后再执行相关的操作。
保证事务的原子性。
目的
undo log(回滚日志)
日志
事务是逻辑上的一组操作,要么都执行,要么都不执行。
原子性(Atomicity):事务是最小的执行单位,不允许分割。
一致性(Consistency):执行事务前后,数据保持一致。
隔离性(Isolation):并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的。
持久性(Durability):一个事务被提交之后,它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响。
只有保证了事务的持久性、原子性、隔离性之后,一致性才能得到保障。也就是说 A、I、D 是手段,C 是目的!
ACID特性
一个事务读取数据并且对数据进行了修改,这个修改对其他事务来说是可见的,即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据,但第一个事务突然回滚,导致数据并没有被提交到数据库,那第二个事务读取到的就是脏数据。
脏读(Dirty read)
在一个事务读取一个数据时,另外一个事务也访问了该数据,那么在第一个事务中修改了这个数据后,第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失,因此称为丢失修改。
丢失修改(Lost to modify)
指在一个事务内多次读同一数据。在这个事务还没有结束时,另一个事务也访问该数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况,因此称为不可重复读。
不可重复读(Unrepeatable read)
幻读与不可重复读类似。它发生在一个事务读取了几行数据,接着另一个并发事务插入了一些数据时。在随后的查询中,第一个事务就会发现多了一些原本不存在的记录,就好像发生了幻觉一样,所以称为幻读。
幻读(Phantom read)
锁可以看作是悲观控制的模式。
锁控制方式下会通过锁来显示控制共享资源而不是通过调度手段,MySQL 中主要是通过 读写锁 来实现并发控制。
共享锁(S 锁):又称读锁,事务在读取记录的时候获取共享锁,允许多个事务同时获取(锁兼容)。
排他锁(X 锁):又称写锁/独占锁,事务在修改记录的时候获取排他锁,不允许多个事务同时获取。
锁
多版本并发控制(MVCC,Multiversion concurrency control)可以看作是乐观控制的模式。
MVCC 是一种并发控制机制,用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性。
通过在每个数据行上维护多个版本的数据来实现的。
MVCC 的实现依赖于:隐藏字段、Read View、undo log。
当一个事务要对数据库中的数据进行修改时,MVCC 会为该事务创建一个数据快照,而不是直接修改实际的数据行。
MVCC
控制方式
READ-UNCOMMITTED(读取未提交) :最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交) :允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读) :对同一字段的多次读取结果都是一致的,可以阻止脏读和不可重复读,但幻读仍有可能发生。
SERIALIZABLE(可串行化):最高的隔离级别,完全服从 ACID 的隔离级别。所有的事务依次逐个执行,可以防止脏读、不可重复读以及幻读。
MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读)。
InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的,主要有下面两种情况:快照读:由 MVCC 机制来保证不出现幻读。当前读:使用 Next-Key Lock 进行加锁来保证不出现幻读,Next-Key Lock 是行锁(Record Lock)和间隙锁(Gap Lock)的结合,行锁只能锁住已经存在的行,为了避免插入新行,需要依赖间隙锁。
隔离级别
事务
锁是一种常见的并发事务的控制方式。
MyISAM 仅仅支持表级锁(table-level locking),一锁就锁整张表,这在并发写的情况下性非常差。InnoDB 不光支持表级锁(table-level locking),还支持行级锁(row-level locking),默认为行级锁。
MySQL 中锁定粒度最大的一种锁(全局锁除外),是针对非索引字段加的锁,对当前操作的整张表加锁。实现简单,资源消耗也比较少,加锁快,不会出现死锁。不过,触发锁冲突的概率最高,高并发下效率极低。表级锁和存储引擎无关,MyISAM 和 InnoDB 引擎都支持表级锁。
表级锁
MySQL 中锁定粒度最小的一种锁,是 针对索引字段加的锁 ,只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小,并发度高,但加锁的开销也最大,加锁慢,会出现死锁。行级锁和存储引擎有关,是在存储引擎层面实现的。
InnoDB 的行锁是针对索引字段加的锁,表级锁是针对非索引字段加的锁。
记录锁(Record Lock):也被称为记录锁,属于单个行记录上的锁。
间隙锁(Gap Lock):锁定一个范围,不包括记录本身。
临键锁(Next-Key Lock):Record Lock+Gap Lock,锁定一个范围,包含记录本身,主要目的是为了解决幻读问题。
在 InnoDB 默认的隔离级别 REPEATABLE-READ 下,行锁默认使用的是 Next-Key Lock。但是,如果操作的索引是唯一索引或主键,InnoDB 会对 Next-Key Lock 进行优化,将其降级为 Record Lock,即仅锁住索引本身,而不是范围。
行锁定方式
行级锁
共享锁和排他锁
由数据引擎自己维护的,用户无法手动操作意向锁,在为数据行加共享/排他锁之前,InooDB 会先获取该数据行所在在数据表的对应意向锁。
意向共享锁(Intention Shared Lock,IS 锁):事务有意向对表中的某些记录加共享锁(S 锁),加共享锁前必须先取得该表的 IS 锁。
意向排他锁(Intention Exclusive Lock,IX 锁):事务有意向对表中的某些记录加排他锁(X 锁),加排他锁之前必须先取得该表的 IX 锁。
分类
意向锁
快照读(一致性非锁定读)就是单纯的 SELECT 语句。
比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。
快照读
当前读 (一致性锁定读)就是给行记录加 X 锁或 S 锁。
当前读
当前读和快照读
InnoDB 中的自增主键会涉及一种比较特殊的表级锁。
自增锁(AUTO-INC Locks)
不建议用 MySQL 直接存储文件(比如图片),推荐使用 FastDFS、MinIO(推荐)。
INET_ATON(),INET_NTOA()
可以将 IP 地址转换成整形数据存储,性能更好,占用空间也更小。
MySQL 为我们提供了 EXPLAIN 命令,来获取执行计划的相关信息。
EXPLAIN 语句并不会真的去执行相关的语句,而是通过查询优化器对语句进行分析,找出最优的查询方案,并显示对应的信息。
EXPLAIN 执行计划支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。
SELECT 标识符,是查询中 SELECT 的序号,用来标识整个查询中 SELELCT 语句的顺序。
id 如果相同,从上往下依次执行。id 不同,id 值越大,执行优先级越高,如果行引用其他行的并集结果,则该值可以为 NULL。
id
查询的类型,主要用于区分普通查询、联合查询、子查询等复杂的查询。
SIMPLE:简单查询,不包含 UNION 或者子查询。
PRIMARY:查询中如果包含子查询或其他部分,外层的 SELECT 将被标记为 PRIMARY。
SUBQUERY:子查询中的第一个 SELECT。
UNION:在 UNION 语句中,UNION 之后出现的 SELECT。
DERIVED:在 FROM 中出现的子查询将被标记为 DERIVED。
UNION RESULT:UNION 查询的结果。
select_type
查询用到的表名,每行都有对应的表名。
<derivedN> : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。
<subqueryN> : 本行引用了 id 为 N 的表所产生的的物化子查询结果。
table
查询执行的类型,描述了查询是如何执行的。所有值的顺序从最优到最差排序为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system:如果表使用的引擎对于表行数统计是精确的(如:MyISAM),且表中只有一行记录的情况下,访问方法是 system ,是 const 的一种特例。
const:表中最多只有一行匹配的记录,一次查询就可以找到,常用于使用主键或唯一索引的所有字段作为查询条件。
eq_ref:当连表查询时,前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式。
ref:使用普通索引作为查询条件,查询结果可能找到多个符合条件的行。
index_merge:当查询条件使用了多个索引时,表示开启了 Index Merge 优化,此时执行计划中的 key 列列出了使用到的索引。
range:对索引列进行范围查询,执行计划中的 key 列表示哪个索引被使用了。
index:查询遍历了整棵索引树,与 ALL 类似,只不过扫描的是索引,而索引一般在内存中,速度更快。
ALL:全表扫描。
type
possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ,则表示没有可能用到的索引。
possible_keys
key 列表示 MySQL 实际使用到的索引。如果为 NULL,则表示未用到索引。
key
key_len 列表示 MySQL 实际使用的索引的最大长度;当使用到联合索引时,有可能是多个列的长度和。
在满足需求的前提下越短越好。如果 key 列显示 NULL ,则 key_len 列也显示 NULL 。
key_len
rows 列表示根据表统计信息及选用情况,大致估算出找到所需的记录或所需读取的行数,数值越小越好。
rows
包含了 MySQL 解析查询的额外信息,通过这些信息,可以更准确的理解 MySQL 到底是如何执行查询的。
Using filesort:在排序时使用了外部的索引排序,没有用到表内索引进行排序。
Using temporary:MySQL 需要创建临时表来存储查询的结果,常见于 ORDER BY 和 GROUP BY。
Using index:表明查询使用了覆盖索引,不用回表,查询效率非常高。
Using index condition:表示查询优化器选择使用了索引条件下推这个特性。
Using where:表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
Using join buffer (Block Nested Loop):连表查询的方式,表示当被驱动表的没有使用索引的时候,MySQL 会先将驱动表读出来放到 join buffer 中,再遍历被驱动表与驱动表进行查询。
当 Extra 列包含 Using filesort 或 Using temporary 时,MySQL 的性能可能会存在问题,需要尽可能避免。
注意
Extra
结果分析
执行计划
所有数据库对象名称必须使用小写字母并用下划线分割。
所有数据库对象名称禁止使用 MySQL 保留关键字。
数据库对象的命名要能做到见名识意,并且最后不要超过 32 个字符。
临时库表必须以 tmp_ 为前缀并以日期为后缀,备份表必须以 bak_ 为前缀并以日期 (时间戳) 为后缀。
所有存储相同数据的列名和列类型必须一致。
数据库命名规范
所有表必须使用 InnoDB 存储引擎。
数据库和表的字符集统一使用 UTF8,支持emoji,则需要采用 utf8mb4。
所有表和字段都需要添加注释。
尽量控制单表数据量的大小,建议控制在 500 万以内(并不是数据库限制)。
谨慎使用 MySQL 分区表,建议采用物理分表的方式管理大数据。
经常一起使用的列放到一个表中。
禁止在表中建立预留字段。
禁止在数据库中存储文件(比如图片)这类大的二进制数据。
不要被数据库范式所束缚。
禁止在线上做数据库压力测试。
禁止从开发环境,测试环境直接连接生产环境数据库。
数据库基本设计规范
优先选择符合存储需要的最小的数据类型。
避免使用 TEXT,BLOB 数据类型,最常见的 TEXT 类型可以存储 64k 的数据。
避免使用 ENUM 类型。
尽可能把所有列定义为 NOT NULL。
一定不要用字符串存储日期。
同财务相关的金额类数据必须使用 decimal 类型。
单表不要包含过多字段。
数据库字段设计规范
限制每张表上的索引数量,建议单张表索引不超过 5 个。
禁止使用全文索引。
禁止给表中的每一列都建立单独的索引。
不要使用 UUID、MD5、HASH 字符串列作为主键,主键建议使用自增 ID 值。
每个 InnoDB 表必须有个主键。
1、出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列。
2、包含在 ORDER BY、GROUP BY、DISTINCT 中的字段。
3、并不要将符合 1 和 2 中的字段的列都建立一个索引, 通常将 1、2 中的字段建立联合索引效果更好。
4、多表 join 的关联列。
常见索引列建议
区分度最高的放在联合索引的最左侧(区分度=列中不同值的数量/列的总行数)。
尽量把字段长度小的列放在联合索引的最左侧(因为字段长度越小,一页能存储的数据量越大,IO 性能也就越好)。
使用最频繁的列放到联合索引的左侧(这样可以比较少的建立一些索引)。
选择索引列的顺序
避免建立冗余索引和重复索引(增加了查询优化器生成执行计划的时间)。
避免 InnoDB 表进行索引的二次查询,也就是回表操作。
可以把随机 IO 变成顺序 IO 加快查询效率。
好处
对于频繁的查询优先考虑使用覆盖索引
不建议使用外键约束(foreign key),但一定要在表与表之间的关联键上建立索引。
外键可用于保证数据的参照完整性,但建议在业务端实现。
外键会影响父表和子表的写操作从而降低性能。
索引 SET 规范
索引设计规范
尽量不在数据库做运算,复杂运算需移到业务应用里完成。
优化对性能影响较大的 SQL 语句。
充分利用表上已经存在的索引。
禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询。
禁止使用不含字段列表的 INSERT 语句。
建议使用预编译语句进行数据库操作。
避免数据类型的隐式转换。
避免使用子查询,可以把子查询优化为 join 操作。
避免使用 JOIN 关联太多的表。
减少同数据库的交互次数。
in 的值不要超过 500 个。
对应同一列进行 or 判断时,使用 in 代替 or。
禁止使用 order by rand() 进行随机排序。
WHERE 从句中禁止对列进行函数转换和计算。
在明显不会有重复值时使用 UNION ALL 而不是 UNION。
拆分复杂的大 SQL 为多个小 SQL。
程序连接不同的数据库使用不同的账号,禁止跨库查询。
数据库 SQL 开发规范
对于大表使用 pt-online-schema-change 修改表结构。
禁止为程序使用的账号赋予 super 权限。
对于程序连接数据库账号,遵循权限最小原则。
数据库操作行为规范
规范
执行查询语句的时候,会先查询缓存。不过,MySQL 8.0 版本后移除,因为这个功能不太实用。
查询缓存
让聚集索引尽量地保持递增顺序插入,避免了随机查询,从而提高了查询效率。
MyISAM 引擎的自增值保存在数据文件中。
InnoDB 引擎的自增值,其实是保存在了内存里,并没有持久化。首次打开表时,会去找自增值的最大值 max(id),然后将 max(id)+1 作为这个表当前的自增值。
MySQL 8.0 版本后,自增值的变更记录被放在了 redo log 中,提供了自增值持久化的能力。
存储位置
在 MySQL 里面,如果字段 id 被定义为 AUTO_INCREMENT,在插入一行数据的时候,自增值的行为。
如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段。
如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值。
根据要插入的值和当前自增值的大小关系,自增值的变更结果也会有所不同。假设某次要插入的值是 insert_num,当前的自增值是 autoIncrement_num:如果 insert_num < autoIncrement_num,那么这个表的自增值不变。如果 insert_num >= autoIncrement_num,就需要把当前自增值修改为新的自增值,
自增行为
分布式 id 为了避免两个库生成的主键发生冲突,我们可以让一个库的自增 id 都是奇数,另一个库的自增 id 都是偶数。
自增值不连续场景 1
自增初始值和自增步长设置不为 1。
唯一键冲突。
事务回滚。
自增值不连续场景 2
批量插入(如 insert...select 语句)。
自增值不连续场景 3
自增主键
当操作符左右两边的数据类型不一致时,会发生隐式转换。
当 where 查询操作符左边为数值类型时发生了隐式转换,那么对效率影响不大,但还是不推荐这么做。
当 where 查询操作符左边为字符类型时发生了隐式转换,那么会导致索引失效,造成全表扫描效率极低。
字符串转换为数值类型时,非数字开头的字符串会转化为0,以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。
隐式转换
其它
数据库 MySQL
0 条评论
回复 删除
下一页