InnoDB存储引擎学习总结-第四章 表
常用命令
insert into z select null,1,2,3;
set autocommit=0;
DELIMITER//
DELIMETER;
create PROCEDURE procedure_name();//创建存储过程。
call procedure_name();//调用存储过程
delete from #tb_name#;//删除表的数据
drop #tb_name#;//删除表，包括定义和数据。
一、索引组织表
在InnoDB存储引擎中，表都是根据主键顺序组织存放的。如果未定义主键，或选取第一个定义的非空唯一索引或者自动创建隐含索引列。
二、InnoDB逻辑存储结构
- 表空间
每张表的表空间存放数据页、索引页、插入缓冲Bitmap页。而回滚信息，插入缓冲索引页，系统事务信息，二次写缓冲还是还是放到共享表空间中。 - 段
表空间由各个段组成：数据段、索引段、回滚段。 - 区
区是由连续的页组成的，每个区1M=64个连续的页. - 页
数据页、undo页、系统页、事务数据页等。 - 行
数据是按行存放的，一页存放的数据行是有限制的（显而易见）。
三、行记录格式
show table status like 'tb_name'\G;
可以看到表的存储引擎、行记录格式、等
- compact格式(压紧)
固定长度Char字段在需要时填充0x20以完全占用其长度空间。
对于null值，不管CHAR或者VARCHAR都不占存储空间。 - Redundant（冗余）
对于null值，varchar不占用空间，char需要占用空间
在该格式下，CHAR将占用可能存放的最大值字节数（10个字符，utf8编码，则为30字节） - varchar 列的最大值是65535字节。
四 约束
Primary key
Unique key
Foreign key
Default
NOT NULL
- 约束的创建
随表创建：
create table u( id int,
name varchar(120),
id_card CHAR(18),
primary key(id),
unique key(name));
alter table新增唯一约束：
alter table u add unique key id_card_unique(id_card);
外键约束：
alter table u add forign key (id) references u (id);
NOT NULL约束： id_card CHAR(18) NOT NULL
- ENUM和SET约束
ep:create table a (id int, sex enum('male', 'female')); - 触发器实现约束
触发器可以作用于insert、update、delete语句的前或者后，所以一张表最多可以对应6个触发器。
ep:
delimiter //
create trigger #trg_name# before update on #tb_Name#
for each row
begin
if new.usercash - old.usercash > 0 then
nsert into usercash_err_log select Null, old.userid,old.cash,new.cash,user(),now();
set new.cash = old.cash;
end if;
end;
//
五、外键约束
myIsam存储引擎不支持外键
外键的定义
alter table add foreign key #keyName#(colName) references (#tbName#(colName))
on update cascade
on delete set null;
外键定义中当对父表就行delete和update操作时，对子表可以的操作有：
- cascade;父表发生delete或者update时，子表也delete或者update
- set null;父表发生delete或者update时,对子表中相应数据设置为null值
- no action;父表发生delete或者update时,抛出错误，不允许该操作发生。
- restrict;mysql中 同no action。
定义外键的列会默认创建索引（避免死锁，why?）
导入数据最好关闭外键检测，避免浪费太多时间
set foreign_key_checks = 0;
六、视图
视图是虚表，可以让程序不关心基表的结构，能起到一个安全层的作用。
创建视图
create view view_name as
select * from tb_name where id < 10;
or
create view view_name as
select id as v_id, name as v_name from tb_name where id < 10 with check option;
with check option 选项：针对可更新视图，检查值的合法性。
七、分区
7.1 分区概述
- 分区并不是面向存储引擎层的，所以不只是innodb有分区功能，myisam也有分区。
- mysql只支持水平分区（按行分区）
- mysql是局部分区索引（一个分区中即放数据又放索引）
- 是否开启分区功能 show variables like '%partition%' or show plugins;
- 启用分区，数据库不一定更快
- 分区列 必须是唯一索引或主键（如果存在的话）的一部分。
7.2 range分区
create table t(
id int
)
partition by range(id) ( //可以结合函数 partition by range(YEAR(date))
partition p0 values less than (10),
partition p1 values less than (20),
partition p2 values less than maxvalue);
可以用于range分区的函数
year();to_days();to_seconds();unix_timestamp()等函数；
而表达式不行。
7.3list 分区
demo:
create table t(
id int
)
partition by list(id) (
partition p1 values in (1,3,5,7,9),
partition p2 values in (2,4,6,8,10));
使用分区时，注意引擎对事务的支持。如在插入数据时，遇到分区未定义的值，MyISAM引擎会将之前的行都插入，而InnoDB会回滚。
7.4 hash分区
partition by hash (expr);//expr为一个返回整数值的表达式
demo
create table t(
id int
)
partition by hash (id)
partitions 4;
对于自增主键，hash分区能将数据较均匀的分布。
7.5 key分区
不同于hash分区使用用户定义的函数分区，key使用mysql体统的函数分区； partition by key (id)
7.6 columns 分区
可以直接使用非整形数据分区（前面四种不行）
支持的数据类型
- 所有整型。float和double不支持
- 日期类型。date和datetime.其余不支持
- 字符串。CHAR、VARCHAR、BINARY、VARBINARY。BLOB和TEXT不支持
demo：略
7.7 子分区
对于特别大的表可以在分区上再进行分区。
7.8 分区中的NULL值
- mysql分区总是视NULL值小于非NULL值，同order by 一样。
- 对 range分区，NULL值会被放在最左边的分区。
- 对list分区，需特定指明NULL值放到 哪个分区。
- 对hash和key分区，任何分区函数会将NULL值作为0考虑。
7.9分区和性能
- 对于一般的OLTP(在线事务处理)程序，大部分通过索引返回几条记录即可，最好不要考虑分区。
- 对于OTAP（在线分析处理）的应用，数据量一般很大（上亿），可以考虑分区。
- 对于一张大表，一般的B+树需要2-3次磁盘io.
- 分区之后可能导致某些查询开销变大：
如对一个表的主键进行分区，那么根据主键列的查询可能会有1次或0次io的优化，但是如果有其他索引列，对这些列的条件查找需要扫描每个分区，事情就变得不那么美了（mysql局部分区索引，分区中包含数据和索引）。 - 查看语句分区使用情况：
explain partitions #sql语句#
