术语
| 描述 | |
|---|---|
| 数据结构 | 关系型数据库由表组成 |
| 元组 | 表中的每行（即数据库中的每条记录）就是一个元组 |
| 空值 | Mysql难以优化引用可空列查询，它会使索引、索引统计和值比较更加复杂，你应该用0、一个特殊的值或者一个空串代替空值。 |
| 主码 | 我们在建立数据库的时候，需要为每张表指定一个主码，主码也叫主键，唯一标识这一个元组 |
| 超码 | 一个或多个属性的集合，这些属性的组合可以使我们在一个实体集中唯一的标识一个实体 |
| 候选码 | 候选码就是可以区别一个元组的属性或属性的集合 |
| 参照完整性 | 参照完整性主要是定义外码,将一个关系的主码放在另一个关系中,作为该关系的属性,就称其为外码 |
| 笛卡尔积 | R×S，R中的每一行分别与S中的每一行两两组合的结果 |
| 等值连接 | R.X=S.X，取两者中属性值相等的元组 |
| 自然连接 | R.X=S.X，是一种特殊的等值连接，取两者中属性值相等的元组，结果中重复属性去掉 |
| 左连接 | 无论是否符合语句的表连接条件都会把左表的记录全部查询出来，右表只匹配符合条件的数据行，如果右表中没有匹配行则为null(右表第一条记录开始扫描符合连接条件的记录与左表记录合并存到结果集，直到找不到符合条件的记录并生成一个为NULL的右表列) |
SQL
| 描述 | |
|---|---|
| 数据查询语言DQL | SELECT |
| 数据操纵语言DML | INSERT/UPDATE/DELETE |
| 数据定义语言DDL | 表结构创建/视图/索引/同义词/簇/约束 |
| 数据控制语言DCL | 数据库权限以及事物操作 |
| 视图 | 视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库只存放视图的定义，而不存放视图对应的数据 |
| 索引 | 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息 |
| 同义词 | 相当于表的别名 |
| 簇 | 节省了磁盘存储空间，因为原来需要单独存放多张表，现在可以将联结的部分作为共享列的存储，适合经常查询、当DML较少的表 |
| 约束 | 控制表属性的手段 |
约束
| 描述 | |
|---|---|
| 概念 | 约束用于限制加入表的数据的类型 |
| primary key | 不重复，也不会出现空值 |
| foreign key | 外键 |
| unique | 唯一约束不允许出现重复的值，但是可以为多个NULL |
| not null | 非空 |
| auto_increment | 自增 |
| default | 默认值 |
| 外键 | 描述 |
|---|---|
| 外键 | 如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键 |
| 优点 | 保证数据的完整性与关联性 |
| 缺点 | DML操作的时候，可能会锁表，当数据量大的时候，性能差 |
| 默认值 | 描述 |
|---|---|
| B树不存NULL，因此索引用不到NULL，如果字段为NULL会直接走全表查询，建议使用默认值给一个0或者'' |
触发器
| 描述 | |
|---|---|
| 概念 | 触发器是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发 |
| INSTEAD OF 触发器 | DML操作之前，检查分支条件触发各种动作 |
| AFTER 触发器 | DML操作成功之后触发 |
| 优点 | 描述 |
|---|---|
| 触发器是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法（强制校验/级联操作/类型转换） |
| 缺点 | 描述 |
|---|---|
| 可移植性差，维护困难，触发器对表的每一行都会处理一个事务, 数据量大且长时间的操作，对数据库造成巨大压力 |
事件
| 描述 | |
|---|---|
| 概念 | 数据库按自定义的时间周期触发某种操作 |
| 优点 | 描述 |
|---|---|
| 不再依赖外部程序，适合实时性要求较高的环境 |
| 缺点 | 描述 |
|---|---|
| 开启和关闭事件需要具有超级用户权限，程序不可以调用，不适合复杂处理 |
游标
| 描述 | |
|---|---|
| 概念 | 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。尽管游标能遍历结果中的所有行，但他一次只指向一行 |
| 优点 | 描述 |
|---|---|
| 可以对某个查询结果集进行单行操作，适合数据量小并且需要重复操作的结果集使用 |
| 缺点 | 描述 |
|---|---|
| 因为游标是循环逐行处理，数据量大的时候效率低下 ，占用内存也非常多 |
储存过程
| 储存过程 | 描述 |
|---|---|
| 概念 | 存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中。 |
| 优点 | 描述 |
|---|---|
| 效率 | 开发速度快，将复杂操作用存储过程封装 |
| 安全 | 可设定只有某些用户才具有对指定存储过程的使用权 |
| 耦合 | 一定程度降低了业务系统与数据库的耦合 |
| 性能 | 存储过程大大地减少了业务系统与数据库的交互，减少网络通信量 |
| 执行速度快 | 存储过程只在创建时进行编译，以后每次执行存储过程都不需要重新编译，而一般SQL语句没执行一次就需编译一次，所以使用存储过程可提高数据库的执行速度 |
| 缺点 | 描述 |
|---|---|
| 移植性差 | 不同数据库的厂商语法不一致，当切换到其他厂商的数据库系统时，需要重写原有的存储过程 |
| 调试差 | 调试困难 |
| 场景 | 描述 |
|---|---|
| 业务操作不要放在存储过程，与无关业务的，各种报表，很复杂的查询比较适合 |
SQL表设计
| SQL表设计 | 描述 |
|---|---|
| 概念 | 基于ER模型的设计方法，3NF的设计方法，统一建模语言（UML）方法等 |
| ER模型 | 描述 |
|---|---|
| 概念 | 提供不受任何DBMS约束的面向用户的表达方法，在数据库设计中被广泛用作数据建模的工具 |
| 矩形 | 实体集 |
| 椭圆 | 实体属性 |
| 菱形 | 联系集 |
| 线段 | 描述属性与实体集关系，或者实体集与联系集关系 |
image.png
| UML | 描述 |
|---|---|
| UML | 统一建模语言，用于说明、可视化、构建和编写一个正在开发的、面向对象的、软件密集系统的制品的开放方法 |
| 3NF | 描述 |
|---|---|
| 1NF | 每一列都是不可分割的基本数据项（不要企图把多个属性存储到某一个列里面） |
| 2NF | 一个表必须有主键,即每行数据都能被区分 |
| 3NF | 一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有沉余字段 |
可读(参考阿里巴巴建表规范)
| 命名 |
|---|
| 表名一般以【模块名称_详细表名】来实现，同一个模块的前缀是一样的 |
| 表达是与否概念的字段，必须使用 is_xxx 的方式命名 |
| 一些作为多对多连接的表，能够使用两个表的前缀作为表名 |
| 大小写规则不统一，如：user_id，userId |
| 使用完整名称，提高可读性 |
| 唯一索引名为 uk_字段名；普通索引名则为 idx_字段名 |
| 附属表拆分后，附属表id与主表id保持一致 |
建议
| 表必备三字段：id, gmt_create, gmt_modified |
| 禁止使用复杂数据类型（数组，自定义类型等） |
| 触发器/储存过程/视图/函数操作， 最好别用 |
| 考虑分布式，不使用外键约束 |
| 任何字段如果为非负数,必须是无符号类型 |
| 存在TEXT或者VARCHAR字节很大的时候，可以考虑拆分到其他表，内存中存储的数据条数可以大大增加 |
扩展
| 扩展性 | 描述 |
|---|---|
| 手动添加列 | 造成mysql锁表，数据量大可能长时间导致服务不可用 |
| 添加表存储 | 大量数据join性能较差 |
| 预留字段 | 空间浪费 |
| JSON | 1：描述（Key）+业务信息（Value） 2：MongoDB，设计复杂 |
性能
| 字节范围越小越快， 因为它们占用更少内存和CPU缓存 |
| 整型比字符操作代价更低,因为字符集和校对规则 |
| Mysql难以优化引用可空列查询，它会使索引、索引统计和值比较更加复杂 |
日期和时间类型
| 类型 | 字节 | 格式 | 描述 |
|---|---|---|---|
| DATE | 3 | YYYY-MM-DD | |
| TIME | 3 | HH:MM:SS | |
| YEAR | 1 | YYYY | |
| DATETIME | 8 | YYYY-MM-DD HH:MM:SS | |
| TIMESTAMP | 4 | YYYYMMDD HHMMSS | 通常情况下应尽量使用TIMESTAMP，因为它比DATETIME效率更高 |
整数类型
| 类型 | 字节 | 描述 |
|---|---|---|
| TINYINT | 1 | |
| SMALLINT | 2 | |
| MEDIUMINT | 3 | |
| INT或INTEGER | 4 | |
| BIGINT | 8 |
根据业务选择合适的范围，非负数选择无符号
小数类型
| 类型 | 字节 | 描述 |
|---|---|---|
| FLOAT | 4 | |
| DOUBLE | 8 | |
| DECIMAL | float 和 double 在存储的时候，存在精度损失的问题,小数直接使用decimal |
字符串类型
| 类型 | 字节 | 描述 |
|---|---|---|
| CHAR | 0-255字节 | 直接分配好内存（占用定义的空间大小），适合字符串长度几乎相等 |
| VARCHAR | 0-65535 字节 | 不预先分配存储空间，varchar类型存储占用实际数据大小的空间 |
| TEXT | 0-65 535字节 | |
| BLOB | 0-65 535字节 | 存储例如图片、音视频这种文件的二进制数据的 |
| 注意点 |
|---|
| 如果一个数据表存在varchar字段，则表中的char字段将自动转为varchar字段 |
| varchar(10)比varchar(100)消耗内存更少，因为varchar的数据缓存到内存中时，需要预先分配的缓存空间等于定义时最大的数据空间 |
| InnoDB 引擎单一字段索引的长度最大为 767 字节。当使用 UTF-8 字符集，每一个字符使用 3 字节来存储，即 255*3=765，所以一般都不会设置超过varchar(255) |
物理存储介质
image.png
| 储存器 | 描述 |
|---|---|
| 高速缓冲存储器 | 高速缓冲存储器主要是用来在内存和CPU之间作个数据缓冲的桥梁，因为CPU的处理速度是所有计算机硬件中最快的，内存转换的速度跟不上CPU的处理速度，需要有个缓冲区域 |
| 主存储器 | 是计算机硬件的一个重要部件，其作用是存放指令和数据，并能由中央处理器（CPU）直接随机存取，内存也被称为主存储器 |
| 快闪存储器 | 闪存是一种非易失性存储器，即断电数据也不会丢失。 |
| 磁盘 | 通常整一个数据库存储在磁盘，为了可以数据访问：先经过主存储器（内存操作），指令完成之后才写回磁盘 |
| 光盘 | 光存储器是指用光学方法从光存储媒体上读取和存储数据的一种设备。DVD之类的 |
| 磁带 | 以磁带为存储介质，由磁带机及其控制器组成的存储设备，是计算机的一种辅助存储器 |
I/O
| I/O | 描述 |
|---|---|
| 数据库存储I/O | 数据库运行中会不断和存储端产生交互，这个过程会产生I/O，而主主存储器的大小会决定存储的I/O数量 |
| 优化 |
|---|
| 数据库I/O是以Page的方式占用内存，如何数据库每行数据越小，Page中的行数就越多，处理速度越快，所以尽可能的选择适合的数据类型，字符编码 |
| 由于查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。如若不是为了调试数据库的目的建议不要开启查询日志 |
| 在MYSQL中，对带有可变字符串类型的数据进行了删除操作，依然保存在表的数据文件之中，过大的数据文件会导致MySQL执行相关数据操作时需要耗费更多的性能和时间，清理碎片时MySQL会锁定表，MySQL官方建议一般根据实际情况，只需要每周或者每月整理一次即可 |
| MYSQL默认配置性能低下，需要调整配置参数提高性能 |
文件组织
| 文件组织 | 描述 |
|---|---|
| 快 | 快是数据在文件中存储和读取的基本单元 ,一个块可能包含多条记录，每条记录都存储在相同的块中，对于大数据(图片，视频)，需要单独存储，并在记录中保存指向大数据的指针 |
| 映射文件 | 数据库映射到多个文件中，同一文件中只存储固定长度的记录或者构建特殊的文件，支持容纳不定长度的记录。将数据库映射到文件的两种方法：定长记录与变长记录 |
| 定长记录 | 定长记录占用固定大小的存储空间 |
| 变长记录 | 对于记录中的定长属性，分配存储它们的值所需的字节数 |
| 文件中记录组织方式 | 堆文件组织/顺序文件组织/散列文件组织/多表聚簇文件组织 |
数据库缓存区
| 数据库缓存区 | 描述 |
|---|---|
| 缓冲区 | 主存储器中的一部分，用于存储磁盘块拷贝。每个块总有一个拷贝存放在磁盘上，负责缓冲器的空间分配的子系统就是缓冲区管理器 |
| 缓冲区管理器 | 当缓冲区中没有剩余空间时，在新块读入缓冲区之前必须把一个块从缓冲区中移除，多数操作系统使用 LRU 策略 |
| LRU | 如果一个数据在最近一段时间没有被访问到，那么在将来它被访问的可能性也很小，也就是说，当限定的空间已经存满数据时，应当把最久没有被访问到的数据淘汰 |
磁盘访问模式：
| 访问模式 | 描述 |
|---|---|
| 顺序访问 | 连续的请求会请求与处于相同或相邻磁道上连续的块 |
| 随机访问 | 相继的请求会请求随机位于磁盘上的块，每次请求都需要一次磁盘寻道，数据传输率明显低于顺序访问模式 |
临时表
| 临时表 | 描述 |
|---|---|
| 临时表概念 | MySQL临时表分为内存临时表和磁盘临时表 ，磁盘临时表从5.7版本开始临时表的默认存储引擎变为innodb |
| MySQL临时表类型 | MySQL临时表类型 分为 外部临时表 与 内部临时表 |
| 外部临时表 | 通过create temporary table语法创建的临时表,这类表在会话结束后，会被自动清理 |
| 内部临时表 | 通常在执行复杂SQL，那么MySQL内部将使用自动生成的临时表以辅助SQL的执行 |
| 内部临时表以及出现场景 |
|---|
| 使用临时表一般都意味着性能比较低，特别是使用磁盘临时表，性能更慢，因此我们在实际应用中应该尽量避免临时表的使用 |
| GROUP BY的列没有索引 |
| ORDER BY和GROUP BY 使用了表的列不同 |
| 在JOIN查询中，ORDER BY或者GROUP BY使用了不是第一个表的列 |
| ORDER BY中使用了DISTINCT关键字 |
| 表包含TEXT或者BLOB列 |
| GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列 |
| 使用UNION或者UNION ALL时，SELECT子句中包含大于512字节的列 |
全表扫描
| 全表扫描 |
|---|
| 通常在数据库中，对无索引的表进行查询一般称为全表扫描 |
| 使用NULL做为判断条件 |
| 左模糊和全模糊都不能成功利用索引 |
| OR语句连接的条件中包含的列没有全部建立索引 |
| 查询条件中使用了不等于操作符<>或!= ，X ！= 0 改为 X >0 OR X < 0 |
| IN：一般情况下IN中的条件太多，优化器倾向于全表扫描。否则照样会走索引的，查询条件，如果子查询结果集比主查询结果集小应该用IN, 否则子查询结果集大用EXISTS |
| NOT IN 和NOT EXISTS，NOT IN 全表扫描，NOT EXISTS子查询依然用到索引 |
| WHERE 子句中对字段进行表达式操作 如：where num/2 = 100 |
| WHERE 子句中对字段进行函数操作 |
索引
| 索引 | 描述 |
|---|---|
| 索引概念 | 索引是一种数据结构，以索引表的形式存储在磁盘中，索引查找过程中就要产生磁盘I/O消耗 |
| 哈希索引 | 只有Memory引擎支持哈希索引，哈希索引基于哈希表实现，存储引擎都会对所有的索引列计算一个哈希码，将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针 |
| 哈希索引查询范围 | 哈希索引只支持等值比较查询，=、in、<=>（等价于：a=1 <=> a=3-2） |
| B+Tree | InnoDB也使用B+Tree作为索引结构 ，数据都在叶子节点上，具有顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址 |
| B+Tree单列索引范围 | <,<=,=,>,>=,between,like（右边模糊）适用索引 |
| 聚簇索引 | 在InnoDB中，即存储主键索引值，又存储行数据，称之为聚簇索引 |
| 非聚簇索引 | Myisam引擎的索引文件和数据文件是独立分开的，则称之为非聚簇索引。 |
| 最左原则 | 创建联合索引时，索引信息存储在叶节点的Data域，数据库进行检索的时候，会根据联合索引的字段从左开始匹配，直到匹配完成 |
| 普通索引 | 当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引，想检索同时多个字段就考虑使用复合索引 |
| 唯一索引 | 索引列的所有值都必须不一致 |
| 主键索引 | 主键 |
| 复合索引 | 将多个字段创建到一个域里，空间会更小 |
| 全文索引 | 全文索引可以在VARCHAR或者 TEXT类型的列上创建 |
| 索引的代价 | 创建索引会创建索引文件，DML操作时，也会修改索引文件修改，降低DML性能 |
| 驱动表与基础表 | 索引设计与优化:每次加载相当于一次随机读,所以普遍的优化方案是小表作为驱动表 |
EXPLAIN
| EXPLAIN | 概念 | 作用 |
|---|---|---|
| id | SELECT识别符,相当sql执行序号 | sql执行顺序，id相同由上至下，不同表示：id越大执行优先级越高 |
| select_type | sql查询类型 | DEPENDENT SUBQUERY （会严重消耗性能）：子查询的查询方式依赖于外面的查询结果，如：SELECT * FROM TABLE_A WHERE x = ( SELECT x FROM TABLE_B WHERE id = 1) |
| table | 所访问数据库中表名称 | 所访问数据库中表名称 |
| type | 索引类型级别 | ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好） |
| possible_keys | 查询涉及到的字段上的引将被列出，NULL代表没有索引 | |
| Key | 实际使用的索引，NULL代表没有索引 | |
| key_len | 显示索引字段的长度（字段字节*3） | |
| ref | const表示：表的连接匹配条件用到的值，NULL代表：连接条件没走索引 | |
| rows | 通过索引查询到的 数据数目 | |
| filtered | 按表条件过滤的行百分比 |
| Extra |
|---|
| 通常在数据库中，对无索引的表进行查询一般称为全表扫描 |
| Using filesort：无法利用索引完成的排序操作则会出现, 消耗大 |
| Using temporary ： 使用了临时表，消耗大 |
| Using index ：不用读取表中所有信息，仅通过索引就可以获取所需数据（性能最好） |
| Using where：需要读取表信息 |
SQL执行顺序
| 执行顺序 |
|---|
| FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY |
SQL书写顺序
| 关键字 | 执行顺序 | 注意点 | |
|---|---|---|---|
| 索引 | sql解析顺序 | 索引创建顺序和sql解析顺序保持一致 | |
| FROM | 从右到左 | 将数据量最小的表作为基础表（最后面的表），3 个以上的表连接查询选择交叉表作为基础表（交集表） | |
| WHERE | 从右到左 | 过滤掉最大数量记录的条件必须写在WHERE子句的末尾 | |
| GROUP BY | 左往右分组 | 将不需要的记录在GROUP BY 之前过滤掉，即在GROUP BY前使用WHERE来过虑 | |
| HAVING | WHERE子句在GROUP BY前限制记录的数目,那就能减少这方面的开销 | 消耗大 | |
| SELECT | 数据库解析过程中，会将 * 转化为列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间 | ||
| ORDER BY | 从左到右排序 | 消耗大 |
| ORDER BY |
|---|
| 两种排序算法：双路排序和单路排序(MySQL4.1之后默认使用) |
| 双路排序:两次磁盘扫描,先从磁盘读取排序字段并在缓冲区进行排序，再从磁盘取其他字段 |
| 单路排序 : 全部读取并在缓冲区按照排序列排序(如果数据量太大超出缓冲区容量则进行分批读取，造成多次I/O) |
| 缓存区大小由参数 : read_rnd_buffer_size |
| 单路排序:max_length_for_sort_data |
慢查询日志
| 慢查询日志 |
|---|
| 慢查询日志是MySQL提供的一种日志记录，它记录MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值（默认值为 10秒）的sql语句，该sql语句会被记录到慢查询日志中 |
| 慢查询日志会对数据库性能带来影响，只在调优时开启 |
Show Profile
| Show Profile |
|---|
| Show Profile是可以用来分析当前会话过程中执行的语句的资源使用情况 |
全局查询日志
| 全局查询日志 |
|---|
| 全局查询日志用于记录SQL语句的执行，可以查看语句执行时间 |
事物
| 描述 | |
|---|---|
| 概念 | 一个数据库事务通常包含了一个序列的对数据库的读/写操作 |
| 目的 | 为数据库操作序列提供了一个从失败中恢复到正常状态的方法，在异常状态下仍能保持一致性，为了实现将数据库状态恢复到一致状态的功能，通常需要维护事务日志以追踪事务中所有影响数据库数据的操作 |
| 原子性 | 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行 |
| 一致性 | 数据完整性约束的遵循 |
| 隔离性 | 多个事务并发执行时，一个事务的执行不应影响其他事务的执行 |
| 持久性 | 已被提交的事务对数据库的修改应该永久保存在数据库中 |
并发控制
| 并发控制 | 描述 |
|---|---|
| 并发控制概念 | 数据库是容许多个事物并发执行，为了保证数据事物互不干扰而出现的一种机制：并发控制 |
事物并发问题
| 描述 | |
|---|---|
| 脏读 | 读取了未提交事物并回滚 |
| 不可重复读 | 相关数据项被更新（数据发生了更改） |
| 幻读 | 相关数据项新增了数据（数据行数不一致） |
事务隔离级别
| 事务隔离级别 | 描述 | 并发问题 |
|---|---|---|
| 未提交读(Read uncommitted) | 一个事务可以读到另外一个事务未提交的数据 | 脏读/不可重复读/幻读 |
| 提交读(Read committed) | 事务未提交，其他事务不能读该数据 | 不可重复读/幻读 |
| 可重复读(Repeatable reads) | 同一事务多次读，读到的数据项都是一致的 | 幻读 |
| 可序列化(Serializable) | 整个表加上锁 |
锁协议
| 锁协议 | 描述 |
|---|---|
| 锁协议概念 | 事物申请加锁约定的规矩称为锁协议 |
| 加锁方式 | 基本锁类型：共享锁与排它锁 |
| 一级锁协议(未提交读) | 事物修改数据项时，加写锁 |
| 二级锁协议(提交读) | 基于一级锁协议，写锁释放(写锁释放完代表事物已经提交了) ，加读锁，读完释放 |
| 三级锁协议(可重复读) | 基于一级锁协议，写锁释放，加读锁，事物结束释放（事物结束释放代表：其他事物获取不到写锁，不能进行写操作） |
| 四级锁协议(可序列化) | 与事物相关的表都加表锁 |
