MySQL入门第十一天（数据库优化）

目录

11.1存储引擎

1.什么是存储引擎

2.存储引擎的选择

3.Innodb存储引擎

存储格式

增加表空间大小

多版本控制

4.MyISAM存储引擎

11.2索引

1.索引概述

2.索引的基本操作

3.索引的使用原则

11.3锁机制

1.认识锁机制

2.表级锁

3.行级锁

11.4分表技术

11.5分区技术

1.分区概述

2.分区管理

11.6数据碎片与维护

---

11.1存储引擎

1.什么是存储引擎

存储引擎：可以看作是数据表存储数据的一种格式，不同的格式具有的特性也各不相同。

举例说明：只有InnoDB存储引擎支持事务、外键、行级锁等特性，而MyISAM则支持压缩机制等特性。

存储引擎的特点：本身是MySQL数据库服务器的底层组件之一，最大的特点是采用“可插拔”的存储引擎架构。

“可插拔”的理解：指的是对正在运行的MySQL服务器依然可根据实际需求使用特定语句加载（插入，INSTALL PLUGIN语句）或卸载（拔出，UNINSTALL PLUGIN语句）所需的存储引擎文件。

2.存储引擎的选择

首先看一下MySQL当前支持哪些存储引擎。

SHOW ENGINES;

执行以上SQL语句，运行的结果中含有6个字段。

Engine （存储引擎）、 Support （是否支持 ）

Comment （注释说明）、 Transactions （是否支持事务 ）

XA（是否支持分布式事务）和Savepoints（是否支持事务的保存点设置）。

存储引擎	默认是否支持	是否支持事务	是否支持分布式事务	是否支持保存点	描述
InnoDB	DEFAULT	YES	YES	YES	支持事务、行级锁和外键
MyISAM	YES	NO	NO	NO	支持表锁、全文索引
MRG_MYISAM	YES	NO	NO	NO	相同MyISAM表的集合
MEMORY	YES	NO	NO	NO	内存存储，速度快但数据容易丢失，适用于临时表
CSV	YES	NO	NO	NO	数据以文本方式存储在文件中

存储引擎	默认是否支持	是否支持事务	是否支持分布式事务	是否支持保存点	描述
ARCHIVE	YES	NO	NO	NO	适用存储海量数据，有压缩功能，但不支持索引
BLACKHOLE	YES	NO	NO	NO	黑洞引擎，写入的数据都会消失，适合做中继存储
PERFORMANCE_SCHEMA	YES	NO	NO	NO	适用于性能架构
FEDERATED	NO	NULL	NULL	NULL	用于访问远程的MySQL数据库

InnoDB存储引擎：在MySQL 5.7版本中被指定为默认的存储引擎。

特性 ：是 MySQL 中第一个提供外键约束的表引擎，尤其对事务处理的能力，是 MySQL 其他存储引擎无法与之比拟的。

优势 ：用于完成事务、回滚、崩溃修复和多版本并发控制的事务安全处理。

缺点 ：读写效率一般。

MyISAM存储引擎：在MySQL5.5以前的版本中是MySQL的默认存储引擎。

特性 ：是基于 ISAM 存储引擎发展起来的，不仅解决了 ISAM 的很多不足，还增加了很多有用的扩展。例如，数据的全文索引、压缩与加密、支持复制与备份的恢复等。

优势 ：与 InnoDB 相比， MyISAM 的优点是处理速度快。

缺点 ：与 InnoDB 相比， MyISAM 的缺点是不支持事务的完整性和并发性。

3.Innodb存储引擎

适用的场景：非常适合业务逻辑比较强，修改操作比较多的项目。

存储格式

默认InnoDB数据表都共用一个表空间文件ibdata1，每张数据表都会在对应的数据库下创建一个与表同名的结构文件（数据库/表名.frm）。

ibdata1 的位置 ：通常位于 data 目录下，与数据库文件处于同级目录。

ibdata1 的作用 ：集中存储数据和索引。

设置数据表的独立表空间文件：全局变量innodb_file_per_table。

# ① 查看默认是否共用同一个表空间文件
SHOW VARIABLES LIKE 'innodb_file_per_table';
# ② 开启每个表独立的表空间文件
SET GLOBAL innodb_file_per_table=ON;

若①查询的结果为 OFF ，表示所有 InnoDB 表都共用同一个表空间文件。

若想要每张数据表都有一个独立的表空间文件，可按照②的方式实现。

全局变量 innodb_file_per_table 值的变化，不会影响已经含有独立表空间的表。

增加表空间大小

数据在实际的存储过程中，可能会遇到存储空间不足的情况，此时可采用MySQL提供的方式扩充InnoDB系统表空间的大小。

1.配置为自动扩展

自动扩展表空间的实现方式非常简单，只需为表空间中最后一个数据文件（利用系统变量innodb_data_file_path获取）指定autoextend属性，每次自动增加的空间大小由系统变量innodb_autoextend_increment设置，以兆字节（MB）为单位。

SHOW VARIABLES LIKE 'innodb_data_file_path';

SHOW VARIABLES LIKE 'innodb_autoextend_increment';

2.在表空间达到指定大小后，将数据存储到另一个文件中

在表空间中存储的数据达到上限时，也可以将数据存到另外一个指定的文件中。

第 1 步 ：关闭 MySQL 服务器。

第 2 步 ：去掉 innodb_data_file_path 系统变量的 autoextend 属性。

第 3 步 ：将 ibdata1 可以存储的数据大小设置为一个固定值，在其后添加分号“ ; ”以及另外一个文件的路径和大小。

       第4步：启动MySQL服务器。

演示ibdata1达到12M时，将数据添加到另一个指定的文件ibdata2中，当ibdata2达到50M时再自动扩展。

① 关闭MySQL服务器，打开配置文件my.ini，添加以下配置。
innodb_data_file_path = ibdata1:12M;ibdata2:50M:autoextend
② 开启MySQL服务器，在ibdata1同目录下可看到新创建的文件ibdata2，然后登录MySQL服务器后，查看innodb_data_file_path系统变量已修改为以上设置的值。

多版本控制

多版本控制的实现方式：通过保存更改前的数据信息来处理多用户并发、事务回滚等情况的发生，从而保证数据读取的一致性。

多版本控制的内部实现原理：InnoDB存储引擎会为每条记录添加3个隐藏的字段，分别为DB_TRX_ID、DB_ROLL_PTR和DB_ROW_ID。

DB_TRX_ID 字段 ：表示最后一个插入或更新此记录的事务标识符，其中删除操作也被视为更新操作。

DB_ROLL_PTR 字段 ：表示滚动指针，用于指向 MySQL 中撤销日志的记录，用于事务的回滚操作，并在事务提交后会立即删除。

DB_ROW_ID字段：用于保存新增记录的ID。

4.MyISAM存储引擎

MyISAM的特点：表占用的空间小，数据写入速度快等特点。

MyISAM数据表的文件扩展名分别为frm、myd和myi，文件名与表名相同。

扩展名	功能说明
frm	用于存储表的结构
myd	用于存储数据，是MYData的缩写
myi	用于存储索引，是MYIndex的缩写

MyISAM 表的数据移植非常方便，只需将数据库下表中对应的 3 个文件复制到另一个数据库下即可。

数据在表中的存储，MyISAM与InnoDB的存储格式不同。

• MyISAM 表采用“堆组织”的方式存储数据，换言之，就是数据在 MyISAM 表中的保存顺序与插入顺序完全相 同。

• InnoDB 表则采用“索引组织”方式存储数据，也就是数据会按照主键的顺序将记录显示到对应的位置，即使没有主键 InnoDB 也会自动选择表中符合条件的字段或采用 InnoDB 内置的 ROWID 作为主键。

11.2索引

1.索引概述

索引：是一种特殊的数据结构，可以看做是利用MySQL提供的语法将数据表中的某个或某些字段与记录的位置建立一个对应的关系，并按照一定的顺序排序好。

目的：就是为了快速定位指定数据的位置。

类型：

 

普通索引 ：是 MySQL 中的基本索引类型，使用 KEY 或 INDEX 定义，不需要添加任何限制条件，作用是加快对数据的访问速度。

唯一性索引 ：由 UNIQUE INDEX 定义，创建唯一性索引的字段需要添加唯一性约束，用于防止用户添加重复的值。

主键索引 ：由 PRIMARY KEY 定义的一种特殊的唯一性索引，用于根据主键自身的唯一性标识每条记录，防止添加主键索引的字段值重复或为 NULL 。

若在 InnoDB 表中数据保存的顺序与主键索引字段的顺序一致时，可将这种主键索引称为“ 聚簇索引 ”。一般聚簇索引指的都是表的主键

一张数据表中只能有一个聚簇索引。

全文索引：由FULLTEXT INDEX定义，用于根据查询字符提高数据量较大的字段查询速度。此索引在定义时字段类型必须是CHAR、VARCHAR或TEXT中的一种，在MySQL5.7版本中，仅MyISAM和InnoDB存储引擎支持全文索引。

空间索引 ：由 SPATIAL INDEX 定义在空间数据类型字段上的索引，提高系统获取空间数据的效率。仅 MyISAM 和 InnoDB 存储引擎支持空间索引，还要保证创建索引的字段不能为空。

2.索引的基本操作

创建索引的3种方式

CREATE TABLE 方式，与数据表同时创建。

ALTER TABLE 方式， 对已创建的数据表进行添加 。

CREATE INDEX 方式， 对已创建的数据表进行添加 ， 不能添加主键索引 。

# 方式1：CREATE TABLE创建数据表时添加索引
CREATE TABLE  数据表名(
字段名 数据类型 [约束条件]
…
PRIMARY KEY [索引类型] (字段列表)[索引选项] ,
{INDEX|KEY} [索引名称] [索引类型] (字段列表)[索引选项],
UNIQUE [INDEX|KEY] [索引名称] [索引类型] (字段列表)[索引选项],
{FULLTEXT|SPATIAL} [INDEX|KEY] [索引名称] (字段列表)[索引选项] 
)[表选项];
# 方式2：ALTER TABLE向已创建的数据表添加索引
ALTER TABLE 数据表名 
ADD PRIMARY KEY[索引类型] (字段列表)[索引选项]  
|ADD {INDEX|KEY} [索引名称] [索引类型] (字段列表)[索引选项]
|ADD UNIQUE [INDEX|KEY] [索引名称] [索引类型] (字段列表)[索引选项]
|ADD FULLTEXT [INDEX|KEY] [索引名称] (字段列表)[索引选项]
|ADD SPATIAL [INDEX|KEY] [索引名称] (字段列表)[索引选项], …;
# 方式3：CREATE INDEX向已创建的数据表添加索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称
[索引类型] ON 数据表名 (字段列表)[索引选项][算法选项 | 锁选项]

创建索引的语法在使用时只需要确定3点：

一是确定采用哪种方式创建索引，选择对应的语句。

二是创建哪些索引。

三是为各索引设置选项

索引可设置的选项

索引选项	语法
索引类型	USING {BTREE | HASH}
字段列表	字段[(长度)[ASC | DESC]]
索引选项	KEY_BLOCK_SIZE [=] 值 | 索引类型| WITH PARSER 解析器插件名 | COMMENT '描述信息'
算法选项	ALGORITHM [=] {DEFAULT|INPLACE|COPY}
锁选项	LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

主键索引不能设置索引名称，其他索引的名称也可以省略，默认使用建立索引的字段表示，复合索引则使用第一个字段的名称作为索引名。

只有字段列表是必选项，其余均为可选的。

索引选项中 KEY_BLOCK_SIZE 仅可在 MyISAM 存储引擎的表中使用，表示索引的大小（以字节为单位）， WITH PARSER 只能用于全文索引。

对于全文和空间索引不能设置索引类型。

索引类型在不同的存储引擎中也不相同，如 InnoDB 和 MyISAM 支持 BTREE ，而 MEMORY 则同时支持 BTREE 和 HASH 。

① 查看指定表中创建的索引信息
SHOW CREATE TABLE 数据表名称;
② 查看指定表中的索引信息
SHOW {INDEXES|INDEX|KEYS} FROM 表名;
③ 分析执行的SQL语句
{EXPLAIN | DESCRIBE | DESC}
{SELECT | DELETE | INSERT | REPLACE | UPDATE} statement

虽然对于MySQL而言，EXPLAIN、DESCRIBE和DESC表示的含义相同。但是在实际应用中，通常使用DESCRIBE和DESC获取表结构相关的信息，EXPLAIN用于获取执行查询的相关数据，如是否引用索引，可能用到的索引等。

主键索引在删除时，需要考虑该主键字段是否含有AUTO_INCREMENT属性，若有则需在删除主键索引前删除该属性，否则程序会报错误提示信息。

删除含有AUTO_INCREMENT属性的主键索引

# ① 删除主键字段的AUTO_INCREMENT属性
ALTER TABLE 数据表 MODIFY 字段名 字段类型
# ② 删除主键索引
ALTER TABLE 数据表 DROP PRIMARY KEY 或 DROP INDEX `PRIMARY` ON 数据表

当使用DROP INDEX删除主键索引时，其后的PRIMARY由于是MySQL中的保留字，因此必须使用反引号（`）包裹。

在MySQL中删除主键索引以外的其他索引时，根据索引名称采用以下语法中的任意一种都可以完成删除操作。

# 语法1
ALTER TABLE 数据表 DROP INDEX 索引名
# 语法2
DROP INDEX 索引名 ON 数据表 [算法选项][锁选项]

3.索引的使用原则

索引在使用时虽然可以提高查询速度，降低服务器的负载，但是相应的，索引的使用也会占用物理空间，给数据的维护造成很多麻烦，并且在创建和维护索引时，其消耗的时间会随着数据量的增加而增长。

 导致索引失效的sql语句：

1.like查询（“%”和“_”开头）索引失效

2.or运算都要具有索引，否则索引失效

3.where条件字段是字符串类型，必须加引号

4.组合索引字段单独使用，左边生效，右边失效（左原则)

如何选择合适的列创建索引：

1.为常作条件，排序，分组，联合操作的字段建立索引

2.选择唯一性索引

3.选择较小的数列行，为较长的字符串使用前缀索引

11.3锁机制

1.认识锁机制

锁机制 就是为了保证多用户并发操作时，能使被操作的数据资源保持一致性的设计规则。

锁机制在不同存储引擎中的表现也有一定的区别。

根据存储引擎的不同，MySQL中常见的锁有两种

表级锁（如MyISAM、MEMORY存储引擎）

行级锁（如InnoDB存储引擎——特殊）

表级锁： 是 MySQL 中锁的作用范围（锁的粒度）最大的一种锁。

锁定范围 ：是用户操作资源所在的整个数据表。

优势 ：有效的避免了死锁的发生，且具有加锁速度快、消耗资源小的特点。

缺陷 ：因其锁定的粒度大，在并发操作时发生锁冲突的概率也大。

行级锁 ：是 MySQL 中锁的作用范围最小的一种锁。

锁定范围 ：仅锁定用户操作所涉及的记录资源。

优势 ：能减少锁定资源的竞争，较高并发处理能力，提升系统的整体性能。

缺陷 ：因其锁定的粒度过小，每次加锁和解锁所消耗的资源也会更多，发生死锁的可能性更高。

根据锁在MySQL中的状态也可将其分为“隐式”与“显式”。

“ 隐式 ” 锁 指的是 MySQL 服务器本身对数据资源的争用进行管理，它完全由服务器自动执行。

“ 显式 ” 锁 指的是用户根据实际需求，对操作的数据显式的添加锁，同样在使用完数据资源后也需要用户对其进行解锁。

2.表级锁

表级锁：根据操作的不同可以分为读锁和写锁。

读锁：表示用户读取（如SELECT查询）数据资源时添加的锁，其他用户不可修改或增加数据资源，但是可以读取该数据资源，因此读锁也可称为共享锁。

写锁：表示用户对数据资源执行写（如INSERT、UPDATE、DELETE等）操作时添加的锁，除了当前添加写锁的用户外，其他用户都不能对其进行读/写操作，因此写锁也可以称为排他锁或独占锁。

MyISAM存储引擎表：是MySQL数据库中最典型的表级锁。

1.“隐式”读/写的表级锁

“隐式”读的表级锁：当用户对MyISAM存储引擎表执行SELECT查询操作前，服务器会“自动”地为其添加一个表级的读锁。

“隐式”写的表级锁：执行INSERT、UPDATE、DELETE等写操作前，服务器会“自动”地为其添加一个表级的写锁。

“隐式”表级锁要如何解锁：直到操作完毕，服务器再“自动”地为其解锁。

“隐式”表级锁的生命周期：SQL语句的执行时间，且该生命周期的持续时间一般都比较短暂。

“隐式”读的表级锁与“隐式”写的表级锁添加的优先级顺序：

默认服务器“自动”添加“隐式”锁时，表的更新操作优先于表的查询操作。

添加写锁时，若表中没有任何锁则添加，否则将其插入到写锁等待的队列中。

添加读锁时，若表中没有写锁则添加，否则将其插入到读锁等待的队列中。

2.“显式”读/写的表级锁

LOCK TABLES 数据表名 READ [LOCAL]| WRITE, …

LOCK TABLES 可以同时锁定多张数据表 。

READ 表示表级的读锁，添加此锁的用户 可读但 不 能写，其 他用户可以读 取，执行写 操 作会 进入等待队列 。

WRITE 表示表级的写锁，添加此锁的用户 可执行读 / 写操作，在释放锁之前，不允许其他用户访问与操作。

表级锁的问题：锁定的粒度大，多用户访问会造成锁竞争，降低并发处理能力。

从数据库优化的角度来考虑：尽量减少表级锁定时间，提高多用户的并发能力。

如何释放“显式”表级锁：UNLOCK TABLES语句

用户设置的“显式”表级锁仅在当前会话内有效，若会话期间内未释放锁，在会话结束后也会自动释放。

3.行级锁

InnoDB 存储引擎的锁机制 相对于 MyISAM 存储引擎的锁复杂一些。

InnoDB 存储引擎 既有表级锁又有行级锁 。

InnoDB 表级锁的应用与 MyISAM 表级锁的 相同 。

InnoDB存储引擎的表什么时候添加表级锁，什么时候添加行级锁呢？

只有通过索引条件检索的数据， InnoDB 存储引擎才会使用行级锁，否则将使用表级锁。

InnoDB 的行级锁根据操作的不同也分为共享锁和排他锁。

1.“隐式”行级锁

“隐式”行级排他锁：当用户对InnoDB存储引擎表执行INSERT、UPDATE、DELETE等写操作前，服务器会“自动”地为通过索引条件检索的记录添加行级排他锁。

“隐式”行级排他锁要如何解锁：直到操作语句执行完毕，服务器再“自动”地为其解锁。

“隐式”行级排他锁的生命周期：语句的执行时间可以看作是“隐式”行级锁的生命周期，且该生命周期的持续时间一般都比较短暂。

延长“隐式”行级排他锁的生命周期：通常情况下，若要增加行级锁的生命周期，最常使用的方式是事务处理，让其在事务提交或回滚后再释放行级锁，使行级锁的生命周期与事务的相同。

2.“显式”行级锁

对于InnoDB表来说，若要保证当前事务中查询出的数据不会被其他事务更新或删除，利用普通的SELECT语句是无法办到的，此时需要利用MySQL提供的“锁定读取”的方式为查询操作显式的添加行级锁。

SELECT 语句 FOR UPDATE|LOCK IN SHARE MODE

FOR UPDATE：表示在查询时添加行级排他锁

LOCK IN SHARE MODE：表示在查询时添加行级共享锁。

用户在向InnoDB表显式添加行级锁时，InnoDB存储引擎首先会“自动”地向此表添加一个意向锁，然后再添加行级锁。

意向锁 是一个隐式的表级锁，多个意向锁之间不会产生冲突且互相兼容。

意向锁 是由 MySQL 服务器根据行级锁是共享锁还是排他锁，自动添加意向共享锁或意向排他锁，不能人为干预。

意向锁的作用 ：就是标识表中的某些记录正在被锁定或其他用户将要锁定表中的某些记录。

相对行级锁 ，意向锁的锁定粒度更大，用于在行级锁中添加表级锁时判断它们之间是否能够互相兼容。

好处 ：就是大大节约了存储引擎对锁处理的性能，更加方便的解决了行级锁与表级锁之间的冲突。

InnoDB表中当前用户的意向锁若与其他用户要添加的表级锁冲突时，有可能会发生死锁而产生错误。

默认当InnoDB处于REPEATABLE READ（可重复读）的隔离级别时，行级锁实际上是一个next-key锁，它是由间隙锁（gap lock）和记录锁（record lock）组成。

记录锁 （ record lock ）就是前面讲解的行锁。

间隙锁 （ gap lock ）指的是在记录索引之间的间隙、负无穷到第 1 个索引记录之间或最后 1 个索引记录到正无穷之间添加的锁。

       间隙锁的作用：在并发时防止其他事务在间隙插入记录，解决事务幻读问题。

在执行SELECT…FOR UPDATE时，若检索时未使用索引，则InnoDB存储引擎会给全表添加一个表级锁，并发时不允许其他用户进行插入。另外，若查询条件使用的是单字段的唯一性索引，InnoDB存储引擎的行级锁不会设置间隙锁。

间隙锁的使用虽然解决了事务幻读的情况，但是也会造成行锁定的范围变大，若在开发时想要禁止间隙锁的使用，可以将事务的隔离级别更改为READ COMMITTED（读取提交）。

 

查看InnoDB表的锁

InnoDB存储引擎的锁比较复杂，读者可以在添加一个行锁后，使用SHOW ENGINE INNODB STATUS语句查看当前表中添加的锁的类型。

SHOW ENGINE INNODB STATUS语句

查看时要保证开启系统变量 innodb_status_output_locks 才能获取锁定的信息。

11.4分表技术

水平分表

当一个表很大时，即使创建索引查询速度也很慢

把一个巨大的表按算法分割成小表，加快速度查询

用户id%表个数

缺点：增加了项目复杂度

11.5分区技术

1.分区概述

对于单表数据量过大的问题，除了可以使用分表技术，在物理上创建多张数据表解决外，还可以使用MySQL本身支持的分区技术提高数据库的整体性能。

分区技术：就是在操作数据表时可以根据给定的算法，将数据在逻辑上分到多个区域中存储。在分区中还可以设置子分区，将数据存放到更加具体的区域内。

分区技术可以使一张数据表中的数据存储在不同的物理磁盘中，相比单个磁盘或文件系统能够存储更多的数据，实现更高的查询吞吐量。

若在WHERE子句中包含分区条件，系统只需扫描相关的一个或多个分区而不用全表扫描，从而提高查询效率。

MySQL中分区技术在使用时对存储引擎以及锁有一定的要求，具体内容如下。

分区技术不适用于 MERGE 、 CSV 或 FEDERATED 存储引擎。

InnoDB 分区表不能设置外键，同样与外键相关的主表和从表也不能被分区。

MySQL5.7 中 InnoDB 表不支持子分区在多个磁盘中存储，目前仅有 MyISAM 存储引擎支持。

同一个分区表的所有分区必须使用相同存储引擎。当建表时未指定存储引擎，在创建分区时必须设置存储引擎。

MySQL5.6 及更早的版本中，对分区的 MyISAM 表进行操作时，会锁定所有的分区，直到操作完成后才会释放锁。而在 MySQL5.7 中，仅会锁定与操作相关的分区，不会影响其他分区。

2.分区管理

创建分区

CREATE TABLE 数据表名称
[(字段与索引列表)][表选项]
PARTITION BY 分区算法(分区字段)[ PARTITIONS 分区数量]
[SUBPARTITION BY 子分区算法(子分区字段)[ SUBPARTITIONS 子分区数量]]
[(
PARTITION 分区名 [VALUES 值][其他选项][(SUBPARTITION 子分区名 [其他选项])],
…
)];

分区是在表选项后添加 PARTITION BY 实现。

一个表最多仅可以创建 1024 个分区。

分区算法有 4 种，分别为 LIST 、 RANGE 、 HASH 和 KEY 。

每种算法对应的分区字段不同

RANGE/LIST (表达式) 或 COLUMNS(字段列表)
HASH(表达式)
KEY [ALGORITHM={1|2}](字段列表)

KEY算法的ALGORITHM选项用于指定key-hashing函数的算法

值等于1：适用于MySQL5.1。

默认是2：适用于MySQL5.5及以后版本。

子分区算法仅支持 HASH和KEY。

在指定分区算法和分区字段后， RANGE 和 LIST 分区 必须用 PARTITION…VALUES 具体 定义每个分区选项 ，且只有这两个算法有 VALUES 选项。

# RANGE算法必须使用LESS THAN而不能用IN
PARTITION 分区名 VALUES LESS THAN {(表达式 | 值列表) | MAXVALUE}
# LIST算法必须使用IN而不能用LESS THAN
PARTITION 分区名 VALUES IN (值列表)

增加分区

# ① 已创建的数据表没有创建分区，添加分区的方式
ALTER TABLE 数据表名称 PARTITION BY 分区算法…;
# ② 已创建的数据表含有分区，添加分区的方式
# LIST或RANGE分区
ALTER TABLE 数据表名称 ADD PARTITION (分区选项, …);
# HASH或KEY分区
ALTER TABLE 数据表名称 PARTITIONS 数量;

当添加分区的数据表已经含有数据时，会按照分区的算法将已有的数据分配到不同的分区中。

删除分区

# 删除HASH、KEY分区
ALTER TABLE 数据表名称  COALESCE PARTITION 数量;
# 删除RANGE、LIST分区
ALTER TABLE 数据表名称 DROP PARTITION分区名称;

删除 HASH 与 KEY 算法分区时，会将该分区内的数据重新整合到剩余的分区 。

删除 RANGE 与 LIST 算法分区时 ， 会同时删除分区中保存的数据。

当数据表的分区仅剩一个时，不能通过以上的方式删除，只能利用 DROP TABLE 的方式删除表。

若在开发中仅要清空各分区表中的数据，不删除对应的分区文件，可以使用以下的语句实现。

ALTER TABLE 数据表名称 TRUNCATE PARTITION {分区名称 | ALL}

11.6数据碎片与维护

在MySQL数据库中，DELETE删除一条记录时，仅删除了数据表中保存的数据，而记录占用的存储空间会被保留。

长期删除数据、添加数据的过程中，索引文件和数据文件都将产生“空洞”，形成很多不连续的碎片，造成数据表占用空间变大，但表中记录数却很少的情况发生。

1.OPTIMIZE TABLE（支持MySQL中常见的存储引擎MyISAM和InnoDB）重新组织表中数据和关联索引数据的物理存储，减少存储空间并提高访问表时的I/O效率。

OPTIMIZE TABLE 表名;

InnoDB存储引擎的数据表不支持OPTIMIZE TABLE操作

• 给出第一条记录进行报错。

• 系统自动用 ALTER TABLE…FORCE 重新构建表并整理相关数据碎片，释放未使用的存储空间，返回第 2 条记录信息。

• Op 表示执行 optimize 操作， Msg_type 表示信息的类型，除此之外还有 error 、 info 和 warning ； Msg_text 表示具体的返回信息内容。

2.使用ALTER TABLE将数据表的存储引擎修改为当前数据表的存储引擎，实现对数据碎片的整理。

ALTER TABLE 数据表 ENGINE='当前存储引擎';