MySQL
2023-03-21 11:01:32 0 举报AI智能生成
MySQL知识架构
作者其他创作
大纲/内容
基础篇
MySQL的安装与配置
略
SQL基础
SQL分类
DDL
（Data Definition Languages）
（Data Definition Languages）
数据定义语句
定义了不同的数据段、数据库、表、列、索引等数据库对象。
常用的语句关键字主要包括create、drop、alter等。
常用的语句关键字主要包括create、drop、alter等。
DML
（Data Manipulation Language）
（Data Manipulation Language）
数据操纵语句
用于添加、删除、更新和查询数据库记录，并检查数据完整性。
常用的语句关键字主要包括 insert、delete、update和select等。
常用的语句关键字主要包括 insert、delete、update和select等。
DCL
（Data Control Language）
（Data Control Language）
数据控制语句
用于控制不同数据段直接的许可和访问级别的语句。
这些语句定义了数据库、表、字段、用户的访问权限和安全级别。
主要的语句关键字包括grant、revoke等。
这些语句定义了数据库、表、字段、用户的访问权限和安全级别。
主要的语句关键字包括grant、revoke等。
数据类型
数值类型
日期时间类型
字符串类型
运算符
算数运算符
比较运算符
逻辑运算符
位运算符
运算符的优先级
常用函数
字符串函数
数值函数
日期和时间函数
流程函数
其他函数
开发篇
存储引擎
事务安全
InnoDB（5.5之后默认）
MVCC
BDB(BerkeleyDB)
BDB被包含在为支持它的操作系统发布的MySQL-Max二进制分发版里。
非事务安全
MyISAM{5.5之前默认}
MERGE
MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。
MEMORY
MEMORY存储引擎提供"内存中"表。
ARCHIVE
ARCHIVE存储引擎被用来无索引地，非常小地覆盖存储的大量数据。
EXAMPLE
EXAMPLE存储引擎是一个"存根"引擎，它不做什么。
你可以用这个引擎创建表，但没有数据被存储于其中或从其中检索。
这个引擎的目的是服务，在 MySQL源代码中的一个例子，它演示说明如何开始编写新存储引擎。
同样，它的主要兴趣是对开发者。
这个引擎的目的是服务，在 MySQL源代码中的一个例子，它演示说明如何开始编写新存储引擎。
同样，它的主要兴趣是对开发者。
FEDERATED
FEDERATED存储引擎把数据存在远程数据库中。
在MySQL 5.1中，它只和MySQL一起工作，使用MySQL C Client API。
在未来的分发版中，我们想要让它使用其它驱动器或客户端连接方法连接到另外的数据源。
在MySQL 5.1中，它只和MySQL一起工作，使用MySQL C Client API。
在未来的分发版中，我们想要让它使用其它驱动器或客户端连接方法连接到另外的数据源。
CSV
存储引擎把数据以逗号分隔的格式存储在文本文件中。
BLACKHOLE
BLACKHOLE存储引擎接受但不存储数据，并且检索总是返回一个空集。
NDB Cluster
NDB Cluster是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎。
参考资料
InnoDB与Myisam区别
索引
定义
索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。
索引是针对表而建立的，它是由数据页面以外的索引页面组成的，
每个索引页面中的行都含有逻辑指针，以便加速检索物理数据。
每个索引页面中的行都含有逻辑指针，以便加速检索物理数据。
特点
优点
大大提高查询速度。
显著减少查询中分组和排序的时间。
加速表与表的连接。
通过创建唯一索引，保证每一行数据的唯一性。
缺点
创建索引需要对表加锁，可能影响表的其他数据操作。
索引需要单独磁盘空间存储，如果创建大量索引，可能会比数据文件更快达到大小上限。
写操作需要维护索引，索引越多耗时越长，影响写操作的效率。
分类
字段个数
单列索引
以单独一列创建的索引。
复合索引
（联合索引、组合索引）
（联合索引、组合索引）
以多个字段组成的索引。
字段特性
主键索引
使用主键字段生成的索引，其他都是辅助索引。
唯一索引
唯一索引的索引内容不能重复。
辅助索引
以非主键字段生成，又称次级索引、二级索引、普通索引。
前缀索引
基于字段前几个字符或者几个bytes建立的索引。
是否聚簇
聚簇索引
叶子节点data域保存完整数据行记录的就是聚簇索引。
特点
聚簇索引具有唯一性。一个表只有一个聚簇索引。
聚簇索引默认是主键。
如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。
如果没有这样的索引，InnoDB 会隐式定义一个主键（row_id隐藏列）来作为聚簇索引。
如果没有这样的索引，InnoDB 会隐式定义一个主键（row_id隐藏列）来作为聚簇索引。
非聚簇索引
叶子节点data域只保存主键值或数据地址的就是非聚簇索引。
参考资料
聚簇索引与非聚簇索引
数据结构
全文索引
倒排索引
对非结构化数据进行内容精确查找分析的索引。
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引。
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引。
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
关系型数据库不常用，当下流行方案ElasticSearch。
B树索引
Hash索引
R索引
数据结构索引分析
数组
数组查询时需要将数据加载到内存，数据量大时会占用大量内存。不适合用作索引。
查询时需要全表扫描也不适合数据库数据的查取。
链表
链表查询不用全部载入内存，但仍需要全表扫描，不适合用作索引。
Hash
hash索引在查找上具有速度优势，适合缓存数据库这类在直接查询上追求效率的场景。
Hash索引在范围查找场景需要进行全表扫描，不适合经常需要范围分析的数据库。
二叉树
二叉搜索树
在顺序递增或者递减的情况，二叉数据可能退化为链表，查询时需要全表扫描。
平衡二叉树
AVL树
红黑树
平衡二叉树解决了二叉树退化为链表的问题，
但每个节点存储一个数据，数据库大量数据的情况下，二叉树的高度会很高，IO次数太多，查询效率较低。
但每个节点存储一个数据，数据库大量数据的情况下，二叉树的高度会很高，IO次数太多，查询效率较低。
B树索引
前言
二叉树不适合做数据库索引，原因在于其高度太高会导致IO次数多，降低查询效率。
在降低查找树高度的思路下，多叉树适合需求场景。
B树的B意为Balance，即指多叉平衡树。
与二叉树的区别，也是其能降低树的高度的原因在于，B树允许每个节点有更多的子节点。
与二叉树的区别，也是其能降低树的高度的原因在于，B树允许每个节点有更多的子节点。
B树
B树结构
特点
叶子节点和非叶子节点均存储数据。
关键字只存在于一个节点，即关键字在B树内不重复。
按照索引查询，可能在非叶子节点命中返回。
最好情况为O(1)根节点命中。最差需要遍历到叶子节点，查询效率不稳定。
最好情况为O(1)根节点命中。最差需要遍历到叶子节点，查询效率不稳定。
性能接近二分查找。
增删改数据时需要重新维护B树，具体看参考资料
B+树
B+树结构
特点
B+树与B树相似，是多路平衡查找树。
不同B树
非叶子节点只存储关键字，叶子节点存储关键字和数据。
关键字可以重复。
非叶子节点可以看做索引，因为节点中只包含关键字。
数据库具体实现时，叶子节点增加双向指针，提升查询效率。
查询效率稳定，每次查询都需要查到叶子节点才返回，即B+树的高度。
B*树
B*树是B+树的扩展，大体相同，部分不同，具体看参考资料
参考资料
B树和B+树
数据结构索引总结
数据库索引评估标准
读效率
直接查询
范围查询
条件查询
写效率
发生写操作时，索引的维护效率。
IO效率
查询数据时需要的IO次数。
数组和链表查询时需要进行全表扫描，效率低下。
Hash索引在进行范围和条件查询时需要进行全表扫描，效率低下。对直接查询效率高，适合做缓存索引。
二叉树类型索引，树高度太高，IO次数太多，极大影响查询效率。
B树索引，非叶子节点存储数据，查询效率不稳定，且写操作索引维护复杂度高。
B+树索引，解决了前述各类缺点。
且在实际引用中，数据库B+树高度一般为3~4，查询效率稳定。
另B+树索引实用中都存储在缓存中，进一步减少IO次数，实际一次查询只需要一次IO，耗时为几十毫秒级别。
且在实际引用中，数据库B+树高度一般为3~4，查询效率稳定。
另B+树索引实用中都存储在缓存中，进一步减少IO次数，实际一次查询只需要一次IO，耗时为几十毫秒级别。
综述，B+树是当前最合适做索引的数据结构。
R树索引
R树是一种空间索引树，分析和解决的是二维及以上维度的索引问题。理解上可类比B树在一维空间的索引原理。
专业名词
扇区
磁盘块
内存页
局部性原理
磁盘预读
InnoDB数据页
视图
存储过程
触发器
事务
安全
分区
优化篇
SQL优化
优化数据库对象
锁
锁是协调多个进程或者线程并发访问某一资源的机制。
MySQL锁的分类
全局锁
FTWRL
加全局锁后整个数据库处于只读状态。
命令
加锁
flush tables with lock
解锁
ublock tables
应用
全库逻辑备份
表级锁（table lock）
种类
表锁
元数据锁（MDL）
意向锁
AUTO-INC锁
特点
开销小，加锁快。
不会出现死锁。
锁粒度大，锁冲突概率最高，并发度最低。
应用
MyISAM
页面锁（page lock）
种类
特点
开销和加锁时间介于表锁和行锁之间。
会死锁。
锁粒度介于表锁与行锁之间，并发度也介于二者之间。
应用
BDB
行级锁（record lock）
种类
Record Lock
Gap Lock
Next-key Lock
特点
开销大，加锁慢。
会死锁。
锁粒度小，锁冲突概率最低，并发度最高。
应用
InnoDB
MyISAM表锁
InnoDB的锁
优化MySQL Server
磁盘IO
应用优化
运维篇
日志
慢查询
备份与恢复
权限与安全
监控
架构篇
复制
MySQL Cluster
高可用架构
MMM架构
MHA架构
参考资料
MySQL
细节重点清晰
MySQL简要重点
开发关注的部分重点
MySQL实战原理
存储引擎归纳细致
MySQL架构设计图
MySQL流程图，很精细
MySQL流程图
重要知识点的流程图示
Mysql Buffer Pool
Mysql调优
innodb
Mysql慢查询
从根儿上理解MySQL
详细
MySQL参数优化
MySQL语句执行流程
MySQL索引B+树数据结构
书籍
《深入浅出MySQL：数据库开发、优化与管理维护》
0 条评论
下一页
