MySQL

清欢_x
174 阅读17分钟

范式

三大范式是数据库设计中的一种规范，主要用于减少数据冗余和提高数据完整性

三大范式

  1. 第一范式（1NF） ：每个列都不可以再拆分，即每个字段的数据都是原子性的
  2. 第二范式（2NF） ：在满足第一范式的基础上，非主键列完全依赖于主键，而不能只与主键的某部分相关（针对联合索引）
  3. 第三范式（3NF）：在满足第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键

反范式设计

反范式设计是数据库设计的一种策略，它在某些情况下可以提供性能优势和更简单的查询操作。然而，反范式设计也需要谨慎使用，因为它引入了数据冗余，可能增加数据维护的复杂性。

  • 为了性能和读取效率而适当的违反对数据库设计范式的要求
  • 为了查询的性能，允许存在部分冗余数据

  1. 增加冗余列：在多个表中具有相同的列，它常用来在查询时避免连接操作。

  2. 增加派生列：增加的列可以通过表中其他数据计算生成。它的作用是在查询时减少计算量，从而加快查询速度。

  3. 重新组表：如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。

  4. 分割表：有时对表做分割可以提高性能。表分割有两种方式，水平分割和垂直分割。

反范式设计的目标是通过增加冗余、聚合的手段来提升性能。反范式就是相对范式化而言的，换句话说，就是允许少量的冗余，通过空间来换时间。但是，反范式设计可能会导致数据更新时的复杂性增加，因为需要管理冗余列，查询所有数据需要连接操作。因此，在设计数据库时，我们需要根据具体的应用需求和性能考虑来决定是否使用反范式设计。

字段数据类型优化

基本原则

  • 更小通常更好
  • 简单就好
  • 尽量避免null

无符号

使用无符号时，占用空间不会变小，但是正数的上限能够多一倍

  • int 类型: 2^31-1
  • int unsinged 类型：2^32-1

长度

  • int(1)
  • int(11) 这两者的存储没有任何区别，只是对于一些可视化客户端用来显示的长度
类型长度无符号最大有符号最大位运算表示
tinyint1字节 8位2^8-1 2552^7-1 1271 << 8 - 1
smallint2字节2^16-1 655352^15-11 << 16 - 1
mediumint3字节2 ^ 24 - 12 ^ 23 - 11 << 24 - 1
int4字节 32位
bigint8字节 64位

浮点型

float、double和decimal都是用于存储数值的数据类型，但它们之间存在一些区别：

  1. Float：它是一个单精度浮点数，存储大小为4字节，精度大约为7位小数。

  2. Double：它是一个双精度浮点数，存储大小为8字节，精度大约为15位小数。

  3. Decimal：它是一个精确的小数值，存储大小取决于精度和标度。decimal类型用于存储精确的小数，如货币。

主要的区别在于精度和存储大小。float和double类型的值在存储和处理时可能会有轻微的精度损失，而decimal类型则可以提供精确的计算，不会有精度损失。

如果需要高精度和精确的计算，建议使用decimal。如果存储空间有限，或者不需要非常精确的值，可以使用float或double。

案例分析

数据特别大，是浮点型，还需要确保精度、确保效率，应该使用哪种存储？

可以使用bigint来存储

  • 假设要保留5位小数位
  • 可以将该数值乘 100000 然后再进行处理
  • 可以避免float、double不精确 也可以避免decimal效率不高的问题

字符串类型

  • char
    • 定长
    • 密码、MD5、hash值之类的定长字符串使用char
    • 最大长度为255个字节
  • varchar
    • 不定长
    • 需要1-2个字节额外记录字符串的长度
    • 长度在1-255范围之内使用1个字节记录
    • 长度超过255 则使用2个字节来记录长度
      • 由此也可见得varchar的最大长度为65535个字节
      • 最大长度虽然为65535个字节，但是MySQL的行大小限制是65535字节，包括所有字段
  • blob
    • 二进制
    • 没有字符集
    • 无排序规则
  • text
    • 有字符集
    • 有排序规则
  • enum
    • 存储优化
    • 使用整数代表字符串
    • 在枚举中放数字的意义不大
  • set

注意事项

  • 如果char、varchar都可以，优先选择char
  • 如果使用blob、text类型的字段，可以建一张子表来进行存储。（浪费存储空间、查询效率低）
    • 这样的设计其实是不合理的
      • 文件类型可以只保存URL
      • text类型则可以将数据存储至mongo中，数据库中存一个id即可

blob与text

BLOB和TEXT字段用于存储大量数据。BLOB字段用于存储二进制数据，而TEXT字段用于存储字符数据。这些类型的字段可能会影响查询性能的原因主要有以下几点：

  1. 外部存储：当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1 ~ 4个字节存储一个指针，然后在外部存储区域存储实际的值。

  2. 磁盘临时表：由于MySQL内存临时表暂不支持BLOB、TEXT类型，如果包含他们的查询就要用到基于磁盘的临时表，性能会很低。

  3. 空洞问题：执行了大量的删除操作后，会在数据库表中留下很大的“空洞”，以后要填入这些“空洞”的记录在插入的性能上会有影响。

为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。此外，如果只需要排序前面一小部分字符，则可以减小max_sort_length的配置，或者使用 ORDER BY SUBSTRING (column,length)。需要注意的是，MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。因此，在设计数据库结构和编写查询语句时，需要充分考虑这些因素，以提高查询性能。

日期与时间

mysql中存储时间的最小粒度是微秒(5.6.4版本之后）

  • datetime
    • 1001年-9999年
  • timestamp
    • 1970年-2038年

索引

索引是一种能提高数据库查询效率的数据结构，可以比作一本字典的目录，可以帮你快速找到对应的记录。索引一般存储在磁盘的文件中，它是占用物理空间的。

  • 提高查询效率的数据结构
  • 一般存储在磁盘中
  • 过多的索引会影响插入更新的性能

哈希索引

  • 不支持范围查找
  • 不支持索引排序 （order by)
  • 不支持联合索引
  • 存在hash冲突

B+Tree

B+树是通过二叉查找树、平衡二叉树、B树演化而来

  • 在磁盘设备上，通过B+树可以有效存储数据
  • 记录存储在叶子节点上，非叶子节点存储索引信息；而且记录按照索引列的值由小到大排序
  • B+树含有非常有效的扇出，在查找一个记录的时候，可以有效减少IO操作

innodb索引

  • 聚簇索引
    • 记录与索引存储在一起
    • innodb的主键索引
  • 非聚簇索引
    • 记录与索引分开存储
    • innodb的二级索引
    • myisam的所有索引
  • 二级索引
    • 非聚簇索引
  • 回表
    • 通过二级索引获取主键，然后通过主键索引来找到一个完整和行记录
    • 查询优化器会判断使用
      • 二级索引+ 回表
      • 全表扫描
      • 如果回表的次数过多，会更倾向于使用全表扫描
  • 联合索引
    • 最左匹配原则
    • 减少索引的数量
  • 覆盖索引
    • 减少回表
  • 自适应hash索引
    • 是innodb的三大特性之一，还有两个是 buffpool、双写缓冲区
    • 自适应即不需要我们自己处理，当InnoDB引擎根据查询统计发现某一查询满足hash索引的数据结构的特点，就会给其建立一个hash索引
    • hash索引底层的数据结构是hash表，其数据特点就是比较适合在内存中使用，自适应hash索引存在于InnoDB架构中的缓存中
    • 自适应hash索引只适合搜索等值的查询，如 select * from table where index_col='xxx, 对于其他查找类型，如范围查找，是不能使用的

三星索引

  • 索引将相关的记录放到一起获得一星
  • 如果索引中的数据顺序和朝朝中的排列顺序已知则获得二星
  • 如果索引中的列包含了查询中需要的全部列则获得三星

MySQL调优

架构调优 > MySQL调优 > 硬件和OS调优

MySQL调优的核心是索引

慢查询

核心原因：数据太多了

  • 请求了不需要的数据

    • 查询不需要的记录
    • 总是取出全部列
    • 重复查询相同的数据

  • 是否在扫描额外的记录

    • 响应时间
      • 服务时间 + 排队时间
    • 扫描的行数和返回的行数
    • 扫描的行数和访问类型
  1. 使用覆盖索引
  2. 如果有汇总数据，可以增加一个表
  3. 重写复杂的sql
-- 查看慢查询日志是否开启
show variables like '%slow_query_log%';

-- 开启慢查询日志，只对当前数据库生效，并且重启数据库后失效
set global slow_query_log = 1;

-- 查看慢查询日志的阈值，默认10s 
show variables like '%long_query_time%';

-- 设置阈值 
set long_query_time = 3;

-- 查询当前系统的慢日志
show global status like '%Slow_queries%';

单表访问方法

访问单个表大致分为两种：

  • 使用全表扫描
  • 使用索引进行扫描
    • 针对主键或唯一二级索引的等值查询
    • 针对普通二级索引的等值查询
    • 针对索引列的范围查询
    • 直接扫描整个索引

const

意思是常数级别的，代价是可以忽略不计的。不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效（这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录）。

对于唯一二级索引来说，查询该列为NULL值的情况比较特殊，比如这样：

SELECT * FROM single_table WHERE key2 IS NULL;

因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说 上边这个语句不可以使用const访问方法来执行

ref

有时候我们对某个普通的二级索引列与常数进行等值比较，比如这样：

SELECT * FROM single_table WHERE key1 = 'abc';

由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少，则回表的代价还是比较低的，所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。MySQL就把这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref。

  • 二级索引列值为NULL的情况

    不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法。

  • 对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法，比方说下边这几个查询：

    SELECT * FROM single_table WHERE key_part1 = 'god like';

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

    但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref了，比方说这样：

    SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来，就像下边这个查询：

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;

range

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

我们当然还可以使用全表扫描的方式来执行这个查询，不过也可以使用二级索引 + 回表的方式执行，如果采用二级索引 + 回表的方式来执行的话，那么此时的搜索条件就不只是要求索引列与常数的等值匹配了，而是索引列需要匹配某个或某些范围的值，在本查询中key2列的值只要匹配下列3个范围中的任何一个就算是匹配成功了：

  • key2的值是1438
  • key2的值是6328
  • key2的值在38和79之间。

MySQL把这种利用索引进行范围匹配的访问方法称之为：range。

index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

由于key_part2并不是联合索引idx_key_part最左索引列，所以我们无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下边这两个条件：

  • 它的查询列表只有3个列：key_part1, key_part2, key_part3，而索引idx_key_part又包含这三个列。
  • 搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中。

也就是说我们可以直接通过遍历idx_key_part索引的叶子节点的记录来比较key_part2 = 'abc'这个条件是否成立，把匹配成功的二级索引记录的key_part1, key_part2, key_part3列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多（聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键），而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多，MySQL把这种采用遍历二级索引记录的执行方式称之为：index。

all

最直接的查询执行方式就是全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引

使用索引还是全表扫描

这个是根据代价来的

例如 我的表有1w条记录，然后分别执行以下SQL

explain SELECT * FROM single_table WHERE key2 > 8000; -- type = range
explain SELECT * FROM single_table WHERE key2 > 500;  -- type = all

注意事项

1

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

查询优化器会识别到这个查询中的两个搜索条件：

  • key1 = 'abc'
  • key2 > 1000

优化器一般会根据single_table表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少，选择那个扫描行数较少的条件到对应的二级索引中查询。然后将从该二级索引中查询到的结果经过回表得到完整的用户记录后再根据其余的WHERE条件过滤记录。一般来说，等值查找比范围查找需要扫描的行数更少（也就是ref的访问方法一般比range好，但这也不总是一定的，也可能采用ref访问方法的那个索引列的值为特定值的行数特别多），所以这里假设优化器决定使用idx_key1索引进行查询，那么整个查询过程可以分为两个步骤：

  • 步骤1：使用二级索引定位记录的阶段，也就是根据条件key1 = 'abc'从idx_key1索引代表的B+树中找到对应的二级索引记录。
  • 步骤2：回表阶段，也就是根据上一步骤中找到的记录的主键值进行回表操作，也就是到聚簇索引中找到对应的完整的用户记录，再根据条件key2 > 1000到完整的用户记录继续过滤。将最终符合过滤条件的记录返回给用户。

这里需要特别提醒大家的一点是，因为二级索引的节点中的记录只包含索引列和主键，所以在步骤1中使用idx_key1索引进行查询时只会用到与key1列有关的搜索条件，其余条件，比如key2 > 1000这个条件在步骤1中是用不到的，只有在步骤2完成回表操作后才能继续针对完整的用户记录中继续过滤。

所有搜索条件都能使用索引的情况

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;
SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;

索引合并

可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询：

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

执行计划

id

select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。

id的情况有三种，分别是：

  • id相同表示加载表的顺序是从上到下。
  • id不同id值越大，优先级越高，越先被执行。
  • id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
avatar
文章
阅读
粉丝