MySQL学习笔记设计规范和调优篇

Posted on Edited on In Views:
这是学习MySQL的笔记设计规范和调优篇，主要是数据库、表的设计规范，以及数据库服务端性能调优。

范式

在关系型数据库中，关于数据表设计的基本原则、规则就成为范式（Normal Form，简称NF）。

关系型数据库有六种常见范式，按照范式级别，从低到高分别是：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）、和第五范式（5NF，又称完美范式）。

数据库的范式设计越高阶，冗余度越低，同时高阶的范式一定符合低阶范式的要求。一般来说，在关系型数据库设计中，最高也就遵循到BCNF，普遍还是3NF，但是有些时候为了提高某些查询性能，还会破坏范式规则，也就是反规范化。

image-20221121173644172

键和相关属性的概念

范式的定义会使用到主键和候选键，数据库中的键（Key）由一个或者多个属性组成。数据表中常用的几种键和属性的定义：

  • 超键：能唯一标识元组的属性集叫做超键（例如包含主键或者唯一键，加上其他字段，都可以叫做超键）
  • 候选键：如果超键不包括多余的属性，那么这个超键就是候选键（最小的超键，例如唯一键）
  • 主键：用户可以从候选键中选择一个作为主键
  • 外键：如果数据表R1中的某属性集不是R1的主键，而是另一个数据表R2的主键，那么这个属性集就是数据表R1的外键
  • 主属性：包含在任一候选键中的属性称为主属性（例如可作为主键以及唯一键的列的属性）
  • 非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性

通常，也将候选键称为码，把主键也成为主码。因为键可能是由多个属性组成的，针对单个属性，还可以用主属性和非主属性来区分。

第一范式（1NF）

第一范式主要是确保数据表中的每个字段的值必须具有原子性，也就是说数据表中每个字段为不可再拆分的最小数据单元。

例如员工的手机号有两个，这个时候可以将这条记录拆开成两条记录，每条记录一个手机号，这种就不符合1NF。

属性的原子性是主观性的，取决于应用程序是否需要分别处理，例如将姓名是否拆开取决于是否单独处理姓或者单独处理名（检索条件、更新）。

第二范式（2NF）

在1NF上的基础上，第二范式满足数据表中的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖之间的一部分。

例如联合主键，多个字段唯一确定一条数据，这个时候是完全依赖主键。

在联合主键中，某一个字段就可以唯一确定一条数据，这个时候就是部分依赖。部分依赖的情况下，可以拆分出多个表。

对于非主属性来说，并非完全依赖候选键，这样会产生的问题：

  1. 数据冗余
  2. 插入异常
  3. 删除异常
  4. 更新异常

将表拆开之后，没张数据表都符合第二范式，避免异常情况。

1NF强调字段属性的原子性，2NF强调一张表就是一个独立的对象，一张表只表达一个意思。

2NF要求实体的属性完全依赖主关键字。如果存在不完全依赖，那么这个属性和住关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

第三范式（3NF）

在1NF和2NF的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段。（即，不能存在非主属性A依赖于非主属性B，非主属性B依赖于主键C的情况，即存在A->B->C的决定关系）通俗地讲，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。

这里的主键可以扩展为候选键。

符合3NF后的数据模型通俗地讲，2NF和3NF通常以这句话概括：“每个非键属性依赖于键，依赖于整个键，并且除了键别无他物。”

小结

第一范式，确保每列的原子性。

第二范式，确保每列都和主键完全依赖。

第三范式，确保每列都和主键列直接相关，而不是间接相关。

范式的优点：有助于消除数据库中的数据冗余，3NF通常被认为在性能、扩展性和数据完整性方面达到了最好的平衡。

范式的缺点：降低查询的效率，查询时可能需要关联多张表查询，还可能使一些索引失效。

实际开发过程中，可能会为了性能和读取效率违反范式话的原则，通过增加少量的冗余或重复的数据来提高数据库的读性能，减少关联查询，JOIN表的次数，实现空间换取时间的目的。因此在实际的设计过程中要理论结合实际，灵活运用。

反范式化

当读取数据时产生大量的关键查询，影响数据库整体性能，此时反范式化也是一种优化思路，例如增加冗余字段来提高数据库的读性能。

例如经常查询员工表时，要获取department_name，需要联表查询，经常查询的话，就可以在员工表上增加department_name字段，不用每次查询都联表。

反范式化的问题：

  1. 存储空间变大
  2. 一个表中的一个字段更改了，另外一个冗余表的字段也要更改
  3. 如果采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常消耗系统资源
  4. 在数据量小的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加复杂

使用场景：

当冗余信息有价值或者能大幅度提高查询效率的时候，采取反范式化更有优势。

增加冗余字段的建议：

  • 冗余字段不需要经常修改
  • 冗余字段查询的时候不可或缺

例如：历史快照、历史数据的数据仓库的设计。（数据仓的目的在于分析数据，而数据库设计的目的在于捕获数据；数据仓库存储一般是历史数据，数据库对数据的增删改实时性要求强；数据仓库设计上更偏向采用反范式设计，数据库设计需要尽量避免冗余。）

BCNF

在3NF的基础上进行了改进，使得数据库冗余度更小，提出巴斯范式（巴斯-科德范式）BCNF，称为是修正的第三范式，或扩充的第三方是。

若一个关系达到了第三范式，并且它只有一个候选键，或者它的每个候选键都是单属性，则该关系自然达到BCNF。

在3NF的基础上，消除了主属性对候选键的部分依赖或者传递依赖关系。

如果在关系R中，U为主键，A属性是主键的一个属性，若存在A->Y，Y为主属性，则该关系不属于BCNF。

第四范式（4NF）

多值依赖的概念：

  • 多值依赖即属性之间的一对多关系，记为K->->A
  • 函数依赖事实上是单值依赖，所以不能表达属性值之间的一对多关系
  • 平凡的多值依赖：全集U=K+A，一个K可以对应于多个A，即K->->A。此时整个表就是一组一对多关系。
  • 非平凡的多值依赖：全集U=K+A+B，一个K可以对应于多个A，也可以对应于多个B，A与B互相独立，即K->->A，K->->B。整个表有多组一对多关系，且有：“一”部分是相同的属性集合，“多”部分是相互独立的属性集合。

第四范式在满足BCNF的基础上，消除非平凡且非函数依赖的多值依赖。（即把同一表内的多对多关系删除。）

例如职工表（职工编号、职工孩子姓名、职工选修课程），一个职工有多个孩子，多个课程，不符合4NF。此时可以将表分为两个表，职工表一（职工编号、职工孩子姓名），职工表二（职工编号、职工选修课程），符合4NF。

第五范式（5NF）、域键范式

在4NF基础之上，还有第五范式（5NF）和域键范式（DKNF）。

5NF：满足4NF的基础上，消除不是由候选键所蕴含的连接依赖。如果关系模式R中的每一个连接依赖均由R的候选键所隐含，乘次关系模式符合5NF。

函数依赖是多指依赖的一种特殊情况，而多值依赖实际上是连接依赖的一种特殊情况。但连接依赖不像函数依赖和多值依赖可以由语义直接导出，而是在关系连接运算时才反映出来。存在连接依赖的关系模式仍可能遇到数据冗余及插入、修改、删除异常等问题。

5NF处理的是无损连接问题，这个范式基本没有实际意义，因为无损连接很少出现，而且难以察觉。而域键范式试图定义一个终极范式，该范式考虑所有的依赖和约束类型，但是实用价值也是最小的，只存在理论研究中。

案例

例如进货单表

image-20221121215107751

迭代1次：考虑1NF：将property拆开

image-20221121215137546

迭代2次：考虑2NF：定义主键，将单号和条码标识每一条记录，并且部分字段依赖他们单独的字段，因此拆开成三张表

进货单头表：

image-20221121215437352

进货单明细表：

image-20221121215458407

商品信息表：

image-20221121215640824

在商品信息表中，可能出现重复条形码，因此最好是增加一个自增主键。

image-20221121215723143

迭代3次：考虑3NF，解决依赖关系，将进货单头表中供应商编号和供应商名称的冗余性去掉。

供货商表：

image-20221121220014166

进货单头表：

image-20221121220031790

但是在进货单明细表中，进货价、数量、进货金额有冗余性，这里就可以反范式化，以业务为主，实际使用时，会经常用到进货价和进货金额，这里就可以不拆开。

ER模型（Entity-Relationship）

通过ER模型（实体关系模型）可以看到数据库全貌，可以看到数据表、表中字段、数据表与数据表之间的关系。ER模型是用来描述现实生活中客观存在的事务、事务的属性，以及事物之间关系的一种数据模型。在开发基于数据库的信息系统的设计阶段，通常是用ER模型来描述信息需求和信息特性，帮助我们理清业务逻辑，从而设计优秀的数据库。

ER模型的要素：

实体：可以看做数据对象，用矩形表示。实体分为两类，强实体和若实体：强实体指不依赖于其他实体的实体；弱实体指的是对另一个实体有很强的依赖关系的实体。

属性：指实体的特性，在ER模型中用椭圆形表示

关系：指实体之间的联系，在ER模型中用菱形表示

从系统整体的角度出发，可以独立存在的是实体，不可再分的是属性。也就是说，属性不能包含其他属性。

关系的类型：

一对一：实体之间的关系是一一对应的。

一对多：一遍的实体通过关系，对应多个另外一边的实体。

多对多：关系两边的实体都可以通过啊滚西对应多个对方的实体。

image-20221121221557837

ER模型可以通过PowerDesigner 15更清晰的表现出来

数据表的设计原则

  1. 数据表的个数越少越好：RDBMS的核心在于实体和联系的定义，也就是ER图，数据表越少，证明实体和联系设计得越简介，既方便理解又方便操作。
  2. 数据表中的字段个数越少越好：字段个数越多，数据冗余的可能性越大，通常会在数据冗余和检索效率中进行平衡。
  3. 数据表中联合主键的字段个越少越好：联合主键中的字段越多，占用索引空间越大。
  4. 使用主键和外键越多越好：数据库的设计实际上是定义各种表以及各种字段之间的关系。这些关系越多，证明这些实体之间的冗余度越低，利用度越高。

数据库对象编写建议

关于库

  1. 【强制】库的名称必须控制在32个字符以内，只能使用英文字母、数字和下划线，建议以英文字母开头。

  2. 【强制】库名中英文一律小写，不同单词采用下划线分割，须见名知意。

  3. 【强制】库的名称格式：业务系统名称_子系统名称。

  4. 【强制】库名禁止使用关键字（如type、order等）

  5. 【强制】创建数据库时必须显式指定字符集，并且字符集只能是utf8或者utf8mb4。

    例如 create database dbtest default character set 'utf8';

  6. 【建议】对于程序连接数据库账号，遵循权限最小原则。

    使用数据库账号只能子啊一个DB下使用，不准跨库。程序使用的账号，原则上不准有drop权限。

  7. 【建议】临时库以tmp_为前缀，并以日期为后缀；

    ​ 备份库以bak_为前缀，并以日期为后缀。

关于表、列

  1. 【强制】表和列的名称必须控制在32个字符以内，表名只能使用英文字母、数字和下划线，建议以英文字母开头。

  2. 【强制】表名、列名一律小写，不同单词采用下划线分割。须见名知意。

  3. 【强制】表名要求有模块名强相关，同一模块的表名尽量使用统一前缀，比如crm_fund_item

  4. 【强制】创建表时必须显式指定字符集为utf8或utf8mb4。

  5. 【强制】表名、列名禁止使用关键字（如type、order等）。

  6. 【强制】创建表时必须显式指定表存储引擎类型。如无特殊需求，一律为InnoDB。

  7. 【强制】建表必须有comment。

  8. 【强制】字段命名应尽可能使用表达实际含义的英文单词或缩写。如公司ID，不要使用corporation_id，而用corp_id即可

  9. 【强制】布尔值类型的字段命名为is_描述。如member表上表时是否为enable的会员的字段命名为is_enable。

  10. 【强制】禁止在数据库中存储图片、文件等大的二进制数据

    通常文件很大，短时间内造成数据量快速增长，数据库读取数据时，通常会进行大量随机I/O操作，文件很大时，I/O操作很耗时。通常存储于文件服务器，数据库只存储文件地址信息。

  11. 【建议】建表时关于主键：表必须有主键

    1. 强制要求主键为id，类型为int或bigint，且为auto_increment建议使用unsigned无符号型。
    2. 标识表里每一行主体的字段不要设为主键，建议设为其他字段如user_id,order_id等，并建立unique key索引。因为如果设为主键且主键值为随机插入，则会导致InnoDB内部页分裂和大量随机I/O，性能下降。

  12. 【建议】核心表（如用户表）必须有行数据的创建时间字段（create_time)和最后更新时间字段(update_time)，便于查问题。

  13. 【建议】表中所有字段尽量都是NOT NULL属性，业务可以根据需要定义default值。

    因为NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。

  14. 【建议】所有存储相同数据的列名和列类型必须一致（一般作为关键列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率低）

  15. 【建议】中间表（或临时表）用于保留中间结果集，名称以tmp_开头

    备份表用于备份或抓取源表快照，名称以bak_开头。中间表和备份表定期清理。

  16. 【建议】造表时，可以使用可视化工具，可以确保表、字段相关的约定都能设置上。

关于索引

  1. 【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值禁止被更新。

  2. 【强制】InnoDB和MyISAM存储引擎表，索引类型必须为BTREE

  3. 【建议】主键的名称以pk_开头，唯一键以uni_或者uk_开头，普通索引以idx_开头，一律使用小写格式，以字段的名称或缩写作为后缀。

  4. 【建议】多单词组成的columnname，取前几个单词首字母，加末字母组成column_name，例如sample表member_id上的索引：idx_sample_mid

  5. 【建议】单个表上的索引个数不能超过6个

  6. 【建议】在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面

  7. 【建议】在多表JOIN的SQL里，保证被驱动表的连接列上有索引，这样JOIN执行效率最高

  8. 【建议】建表或加索引时，保证表里互相不存在冗余索引。

    比如表里已经存在key(a,b)，则key(a)为冗余索引，需要删除。

SQL编写

  1. 【强制】程序端SELECT语句必须指定具体字段名称，禁止写成*。

  2. 【建议】程序端INSERT语句指定具体字段名称，不要写成INSERT INTO t1 VALUES(...)。

  3. 【建议】除静态表或小表（100行以内），DML语句必须有WHERE条件，且使用索引查找。

  4. 【建议】INSERT INTO ... VALUES(xx),(xx)... 这里的xx的值不要超过5000个。值过多虽然上线很快，但会引起主从同步延迟。

  5. 【建议】SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。

  6. 【建议】线上环境，多表JOIN不要超过5个表

  7. 【建议】减少使用ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做。ORDER BY、GROUP BY、DISTINCT这些语句较为耗费CPU。

  8. 【建议】包含了ORDER BY、GROUP BY、DISTINCT这些查询的语句，WHERE条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

  9. 【建议】对单表的多次alter操作必须合并为一次。

    对超过100W行的大表进行alter table要谨慎操作，因为这个过程会产生表锁，期间阻塞对该表的所有写入，对业务可能会产生极大影响。

  10. 【建议】批量操作数据时，需要控制事务处理的间隔时间，b进行必要的sleep。

  11. 【建议】事务包含SQL不超过5个。

    较长的SQL数据会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。

  12. 【建议】事务里更新语句尽量基于主键或UNIQUE KEY，如UPDATE ... WHERE id=xxx；

    否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。

数据库其他调优策略

调优目标：

  1. 节省系统资源，增大吞吐量
  2. 提高响应速度
  3. 减少系统瓶颈，提高MySQL整体性能

定位问题

  • 产品使用反馈：用户反馈的结果其实是页面响应、接口响应
  • 日志分析：查看操作日志和系统日志
  • 服务器资源使用监控：CPU、内存、I/O等使用情况
  • 数据库内存状况监控：活动会话（Active Session）监控
  • 其他：对事务、锁等待进行监控

调优维度和步骤

  1. 选择适合的DBMS：针对性的事务性处理、安全性要求选择不同的DBMS；针对数据处理和使用，选择不同的数据库，键值对、文档型、搜索引擎、列式存储和图形数据库。

  2. 优化表设计：

    1. 遵循第三范式
    2. 如果查询过多，需要使用多表联查，则可以采用反范式进行优化
    3. 表字段数据类型选择

  3. 优化逻辑查询：分为逻辑查询优化和物理查询优化，对SQL进行重写，SQL的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除等。

  4. 优化物理查询：正确使用索引。

  5. 使用Redis或Memcached作为缓存：对于响应要求高的场景（响应时间短，吞吐量大），可以考虑内存数据库。

  6. 库级优化：考虑主从架构

    1. 读写分离：master负责写请求，slave负责读请求。（双主双从）
    2. 数据分片：数据库分库分表

优化MySQL服务器

一方面是针对硬件进行优化，另外一方面是对MySQL服务的参数进行优化。

优化服务器硬件

  1. 配置较大的内存：缓存越大，减少磁盘IO，性能也就越高。
  2. 配置高速磁盘系统：磁盘IO高，响应速度也更快。
  3. 合理分布磁盘IO：将磁盘IO分散在多个设备上，提高并行操作能力。
  4. 配置多处理器：MySQL是多线程的数据库，多处理器可同时执行多个线程。

优化MySQL的参数

配置参数都在my.cnf或者my.ini文件的[mysqld]组中。

  1. innodb_buffer_pool_size：表和索引的最大缓存
  2. key_buffer_size：索引缓冲区的大小
  3. table_cache：同时打开表的个数
  4. query_cache_size：查询缓存的大小 （MySQL 8.0失效）
  5. query_cache_type：查询缓存类型
  6. sort_buffer_size：需要进行排序的线程分配的缓冲区的大小
  7. join_buffer_size：联合查询操作所能使用的缓冲区大小
  8. read_buffer_size：每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节）
  9. innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件
  10. Innodb_log_buffer_size ：事务日志所使用的缓冲区
  11. max_connections：允许连接到MySQL数据库的最大数量
  12. back_log：用于控制MySQL监听TCP端口时设置的积压请求栈大小
  13. thread_cache_size：线程池缓存线程数量的大小
  14. wait_timetou：一个请求的最大连接时间
  15. interactive_timeout：表示服务器在关闭连接钱等待行动的秒数
  16. innodb_buffer_pool_instance：将InnoDB的缓存区分成几个部分，可以提高系统的并行处理能力。

优化数据库结构

拆分表：冷热数据分离

操作频次很高的数据称为热数据，频次很低的数据称为冷数据，冷热数据分离，可以减小表的宽度。

  1. 减少磁盘I/O，保证热数据的内存缓存命中率
  2. 更有效的利用缓存，避免读入无用的冷数据

增加中间表

把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

但是需要注意：在修改、删除时，要保证数据的一致性。

增加冗余字段

将多表查询改成使用冗余字段，利用反范式化。

优化数据类型

优先选择符合存储需要的最小的数据类型。

情况1：对整数类型数据进行优化。可以使用INT，对于非负的数据，使用无符号整形。

情况2：既可以使用文本类型也可以使用整数类型的字段，要选择整数类型。相比，整数类型占用存储空间更少。

情况3：避免使用TEXT、BLOB数据类型：内存临时表不支持这两种类型，在排序等操作时，就不能使用内存临时表，而是使用磁盘临时表。对于这种数据，MySQL还要进行二次查询，会导致SQL性能变差。如果一定要用，建立将TEXT、BLOB列分离到单独的扩展表中，查询时只获取需要的列。

情况4：避免使用ENUM类型：修改ENUM值需要使用ALTER语句，ENUM类型的ORDER BY效率很低，需要额外操作。使用TINYINT代替ENUM类型。

情况5：使用TIMESTAMP存储时间：相比DATETIME占用8个字节，TIMESTAMP占4个字节，并且具有自动赋值以及自动更新的特性。

情况6：用DECIMAL代替FLOAT和DOUBLE存储精确浮点数：DECIMAL更精确。

优化插入记录的速度

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况可以分别进行优化，这里分为MyISAM和InnoDB。

  1. MyISAM的表：
    1. 禁用索引：alter table table_name disable keys;插入之后，再开启索引：alter table table_name enable keys;
    2. 禁用唯一性检查：插入数据时，MySQL会对插入的记录进行唯一性检查，这种检查会降低插入速度。禁用set unique_checks=0;，启用 set unique_checks=1;
    3. 使用批量插入：减少网络IO交互次数、解析次数、优化次数。
    4. 使用 LOAD DATA INFILE 批量导入：导入速度比INSERT语句快。
  2. InnoDB的表：
    1. 禁用唯一性检查
    2. 禁用外键检查
    3. 禁用自动提交：DML默认执行一次会提交一次，提交一次就会刷盘一次，比较浪费IO，插入数据之前禁用自动提交，set autocommit=0;插入之后恢复自动提交，set autocommit=1;

使用非空约束

如果业务允许，尽量建议使用非空约束。好处是

  1. 进行比较和计算时，省去对NULL值的字段判断是否为空的开销，提高存储效率
  2. 非空字段也容易创建索引。NULL列需要额外的空间来保存，使用非空约束，可以节省存储空间，每个字段1个bit

分析表、检查表与优化表

分析表

分析表主要是分析关键字的分布。

1
2
3
-- 取消将ANALYZE语句写到binlog中，以便在主从架构中，从服务器能够同步数据
ANALYZE LOCAL TABLE employee;
ANALYZE NO_WRITE_TO_BINLOG TABLE employee;

使用 ANALYZE TABLE语句的过程中，数据库系统会自动对表加一个只读锁，可以用于MyISAM存储引擎和InnoDB引擎，不能作用于视图。执行语句过程中，会更新索引的区分度（cardinality的值），该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使用。

检查表

检查表主要是检查表是否存在错误。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 检查表 
CHECK TABLE employee;
-- OPTION参数只能针对 MyISAM 表 
-- 不扫描行，不检查错误的连接
CHECK TABLE employee OPTION=QUICK;
-- 只检查没有被正确关闭的表 
CHECK TABLE employee OPTION=FAST;
-- 只检查上次检查后被更改的表和没有被正确关闭的表 
CHECK TABLE employee OPTION=CHANGED;
-- 扫描行，以验证被删除的连接是有效的，也可以计算各行的关键字校验和，并使用计算除的校验和验证这一点。
CHECK TABLE employee OPTION=MEDIUM;
-- 对每行的所有关键字进行一个全面的关键字查找。这可以确保表是100%一致的，但是画的时间较长 
CHECK TABLE employee OPTION=EXTENDED;

在执行过程中，会对表加一个只读锁。对于MyISAM类型的表，可以更新关键字统计数据。CHECK TABLE也可以检查视图是否具有错误，比如视图定义中引用的表已经不存在。

优化表

优化表主要是消除删除或者更新造成的空间浪费。当表进行大量删除，或者更新，使用优化表可以整理碎片。只针对VARCHAR,BLOB,TEXT类型的字段。

方式1：OPTIMIZE TABLE

使用 OPTIMIZE TABLE语句的过程中，数据库系统会自动对表加一个只读锁，可以用于MyISAM存储引擎和InnoDB引擎。

1
2
3
4
5
-- 针对 InnoDB 
OPTIMIZE TABLE employee; 
-- Table does not support optimize, doing recreate + analyze instead
-- 说明InnoDB的优化方式，是先创建一个新表，然后拷贝表到新表中
-- 官方文档这个就是优化信息，而不是报错

大表优化

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降。一些常见的优化措施如下：

  1. 限定查询的范围

    禁止不带任何限制数据范围条件的查询语句。例如查询历史订单时，可以控制一个月的范围。

  2. 读写分离

    主库负责写，从库负责读。

    • 一主一从

      image-20221122162926181

      • 双主双从

        image-20221122162959378

    1. 垂直拆分：当数据量达到千万级以上，需要把一个数据库切成多分，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。

    如果数据表过多，可以采用垂直分库，将关联的数据表不是在同一个数据库上。

    如果表中的列过多，可以采用垂直分表，将一张数据表拆分成多张数据表，把经常一起使用的列放到同一张表里。

    垂直拆分的优点：数据列变小，查询时减少读取Block的次数，减少I/O次数。

    垂直拆分的缺点：主键会出现冗余，并引起JOIN操作。

    1. 水平拆分：
    • 尽量控制单表数据量的大小，建议控制在1000万以内。
    • 将大的数据表按照某个属性维度拆分不同的小表，例如时间、主键取模
    • 水平拆分最好分库，提升MySQL并发能力

    水平拆分能够支撑非常大的数据量存储，但分片事务难以解决，跨节点Join性能较差，有利有弊。

    补充一下数据库分片的两种常见方案：

    • 客户端代理：分片逻辑在应用端，通过第三方封装的插件实现。例如当当网的Sharding-JDBC
    • 中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。例如Mycat

其他调优策略

  1. 服务器语句超时处理：在MySQL 8.0中可以设置服务器语句超时的限制，单位可以达到毫秒级。当中断的执行语句超过设置的毫秒数后，服务器将终止查询影响不大的事务或连接，然后报错给客户端

    1
    2
    3
    4
    -- 全局配置，不会对已经连接的会话生效，而是对后续连接的会话生效
    SET GLOBAL max_execution_time=2000;
    -- 会话配置
    SET SESSION max_execution_time=2000;

  2. 创建全局通用表空间：MySQL 8.0中可以通过全局表空间让所有的数据库的表共享，相比独立表空间，使用手动创建共享表空间可以节约元数据方面的内存。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    -- 创建名为 mitaka1 共享表空间  
    CREATE TABLESPACE mitaka1 ADD datafile 'mitaka1.ibd' file_block_size=16k;
    -- 指定表空间  
    CREATE TABLE t11(
    id INT,
    `name` VARCHAR(10)
    )ENGINE=INNODB DEFAULT charset utf8mb4 TABLESPACE mitaka1;
    -- 也可以增加表到全局表空间 
    ALTER TABLE t1 TABLESPACE mitaka1;
    -- 释放全局表空间  
    DROP TABLESPACE mitaka1;
    -- 需要先删除表 3120 - Tablespace `mitaka1` is not empty.
    DROP TABLE t11;

  3. 隐藏索引对调优的帮助：MySQL 8.0中可以将所以设置为隐藏，可以用于测试这个索引是否有作用。

    需要注意：

    1. 即使隐藏的索引，也会实时更新
    2. 主键不能设置为 invisible

推荐阅读：

PowerDesigner 15 概述