1. 学习背景
在 DBeaver - 数据库表 - 属性 - DDL 中存放数据表的每个字段说明、唯一键、主键、分区等信息
定义了数据在数据库中的结构、关系以及权限
2. 数据定义语言（DDL）定义
DDL全称是Data Definition Language，即数据定义语言，定义语言就是定义关系模式、删除关系、修改关系模式以及创建数据库中的各种对象，比如表、聚簇、索引、视图、函数、存储过程和触发器等等。
数据定义语言是由SQL语言集中负责数据结构定义与数据库对象定义的语言，并且由CREATE、ALTER、DROP和TRUNCATE四个语法组成。
- create，创建，用于创建数据库中的表。
- alter，更改，用于在已有的表中添加、修改或删除列。
- drop，删除，删除索引、表和数据库。可以回滚。
- truncate，清空表或者说是截断表，不会删除表。不可以回滚。
备注：delete，删除，但是它是DML语句，可回滚。会走事务，执行时会触发trigger。
从执行速度上来说，drop > truncate >> DELETE
3. PARTITION 分区
对数据进行分区
3.1. 具体的sql语句
- 创建表的时候，添加分区
CREATE TABLE `test`.`test_pa`(
`id` int(11) NOT NULL,
`t` date NOT NULL,
PRIMARY KEY (`id`, `t`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic PARTITION BY RANGE (to_days(t))
PARTITIONS 3
(PARTITION `p737899` VALUES LESS THAN (737899) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `p737999` VALUES LESS THAN (737999) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `p738000` VALUES LESS THAN (738000) ENGINE = InnoDB MAX_ROWS = 0 MIN_ROWS = 0 )
;
- 直接添加分区
alter table test_pa add partition (PARTITION pmax VALUES LESS THAN maxvalue ENGINE = InnoDB )
- 修改/覆盖/合并分区
alter table test_pa REORGANIZE PARTITION p100,p200,pmax into (
PARTITION p100 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p200 VALUES LESS THAN (200) ENGINE = InnoDB
)
- 查询具体分区的下数据
select * FROM test_pa partition(p737899)
其中 p737899 是分区名
3.2. MySQL分区的限制
- 最大分区数目不能超过1024
- 如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内
- 不支持外键
- 不支持全文索引（fulltext）
- 不支持查询缓存query cache
3.3. 分区的优势
1、分区表对业务透明，只需要维护一个表的数据结构。
2、DML操作加锁仅影响操作的分区，不会影响未访问分区。
3、通过分区交换快速将数据换入和换出分区表。
4、通过TRUNCATE操作快速清理特定分区数据。
5、通过强制分区仅访问特定分区数据，减少操作影响。
6、通过大数据量分区能有效降低索引层数，提高查询性能。
3.4. MySQL支持的分区类型
（1）RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
（2）LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
（3）HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
（4）KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
4. innodb存储引擎
在创建表的同时，最后面加上 ENGINE=InnoDB
MySQL版本>=5.5 默认的存储引擎，MySQL推荐使用的存储引擎。支持事务，行级锁定，外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。
4.1. 优势
存储结构：InnoDB 存储引擎将数据按照页（Page）的方式组织在磁盘上，并且采用B+树作为索引结构，使得数据检索效率相对较高。
事务支持：InnoDB 提供完全的 ACID（原子性、一致性、隔离性、持久性）事务支持，适用于需要高可靠性和数据一致性的应用场景，如银行交易、电子商务等。
行级锁定：InnoDB 实现了行级锁定机制，这意味着在执行事务时只锁定受影响的行，而非整个表，这大大提高了并发环境下多用户读写数据的性能。
外键约束：支持外键约束，确保数据的一致性和参照完整性，防止非法的数据操作。
缓冲池：内存管理中，InnoDB 使用了一个称为缓冲池的内存区域，用于缓存表数据和索引，减少磁盘 I/O 操作，提高数据读写的效率。
崩溃恢复：提供完善的崩溃恢复机制，包括 redo 日志（重做日志）和 undo 日志（撤销日志），保证即使在系统崩溃后也能恢复到崩溃前的一致状态。
内存结构：InnoDB 内部有多个内存区域，如redo log buffer、undo logs、buffer pool manager等，用于管理事务、缓存数据以及辅助事务处理过程。
关键特性：
- 多版本并发控制（MVCC）：InnoDB 采用 MVCC 来实现高并发下的读写操作，允许在不阻塞读操作的情况下进行写操作，提升了并发性能，降低了死锁概率。
- 插入缓冲（Insert Buffer）：优化对非唯一二级索引插入操作的性能。
- 两次写（Double Write）：用于保护数据页免受意外宕机造成的数据损坏。
- 自适应哈希索引（Adaptive Hash Index）：自动为经常使用的查询创建哈希索引，提高查询速度。
- 异步IO（Async I/O）：提高磁盘I/O性能，减少等待时间。