一、表的介绍
1. 表的概念
- 表是关系数据库的核心
- 表 = 关系
- 表是记录的集合
- 二维表模型易于人的理解
- MySQL默认存储引擎都是基于行(记录)存储
- 每行记录都是基于列进行组织的
2. 表是数据的集合
select * from table_name limit 1;
集合是无序的，上面的SQL语句的意思是 从表(集合)中 随机 选出一条数据，结果是不确定的, 不能简单的认为是取出第一条数据。
select * from table_name order by col_name limit 1;
只有通过 order by 排序之后取出的数据，才是确定的
二、表的创建
1.临时表
临时表主要的作用是给当前登录的用户存储临时数据或者临时结果的。
- 临时表使用注意事项：
1.临时表是 SESSION 级别的, 当前用户logout或者其他用户登录上来，是无法看到这张表的 2.show tables命令不显示temp table 3.当临时表和普通表同名时，当前用户只能看到同名的临时表 4.创建表时带上 if not exists 进行表的存在性检查；同时建议在临时表的表名前面加上统一的 prefix 5.在MySQL 5.6.X 中没有 innodb_temp_data_file_path 变量 6.MySQL5.7.X 把临时 表结构 放在 tmpdir ，而数据 表数据 放在 datadir 7.MySQL5.6.X 把临时 表结构 和 表数据 都放在 tmpdir 8.不要和SQL优化器在排序过程中内部创建的临时表相混淆。
1.1 查看相关初始化参数
1.查看临时表的默认的存储引擎
mysql> show variables like '%storage%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)、
2.查看临时表的数据存放位置
mysql> show variables like '%temp_data%';
+----------------------------+-----------------------+
| Variable_name | Value |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)
3.查看临时表的定义的存放目录
mysql> show variables like '%tmp%';
+----------------------------------+----------+
| Variable_name | Value |
+----------------------------------+----------+
| default_tmp_storage_engine | InnoDB |
| innodb_tmpdir | |
| internal_tmp_disk_storage_engine | InnoDB |
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 67108864 |
| tmpdir | /tmp |
+----------------------------------+----------+
7 rows in set (0.00 sec)
1.2 临时表创建测试
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.00 sec)
mysql> show create table temp_a\G
*************************** 1. row ***************************
Table: temp_a
Create Table: CREATE TEMPORARY TABLE `temp_a` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql> insert into test_temp values(123);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_temp;
+------+
| a |
+------+
| 123 |
+------+
1 row in set (0.00 sec)
这个时候另外开一个session，是查看不到这个表的
mysql> show processlist;
+-----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+----------+------------------+
| 155 | root | localhost | test | Sleep | 584 | | NULL |
| 156 | root | localhost | test | Sleep | 22 | | NULL |
| 157 | root | localhost | test | Query | 0 | starting | show processlist |
+-----+------+-----------+------+---------+------+----------+------------------+
3 rows in set (0.00 sec)
mysql> select * from test_temp;
ERROR 1146 (42S02): Table 'test.test_temp' doesn't exist
临时表的表定义存放位置
mysql> system ls -l /tmp
-rw-r----- 1 mysql mysql 8554 Jul 11 14:44 #sql5eef_9c_1.frm
drwx------ 3 root root 4096 Jun 13 11:42 systemd-private-9727ba712abb4e158d6af38d2f846420-ntpd.service-5jZaxg
临时表的数据存放位置
mysql> system ls -l /u01/mysql/mysql_data/ibtmp*
-rw-r----- 1 mysql mysql 146800640 Jul 11 14:44 /u01/mysql/mysql_data/ibtmp1
1.3 临时表与普通表同名的问题
1.创建一张与test_temp同名的普通表
mysql> show create table test_temp;
+-----------+---------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------+
| test_temp | CREATE TEMPORARY TABLE `test_temp` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table test_temp (a int);
Query OK, 0 rows affected (0.02 sec)
2.发现show create table的时候还是显示的临时表的定义
mysql> show create table test_temp;
+-----------+---------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------+
| test_temp | CREATE TEMPORARY TABLE `test_temp` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.插入一条记录，并查询，发现显示的是临时表的数据
mysql> insert into test_temp values(456);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_temp;
+------+
| a |
+------+
| 123 |
| 456 |
+------+
2 rows in set (0.00 sec)
4.执行删除命令，发现第一次删除的是临时表，第二次才会删除普通表
//第一次删除test_temp表，留下的是普通表，删除的是临时表
mysql> drop table test_temp;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table test_temp;
+-----------+-----------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------+
| test_temp | CREATE TABLE `test_temp` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//第二次删除test_temp表，删除的是普通表
mysql> drop table test_temp;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test_temp;
ERROR 1146 (42S02): Table 'test.test_temp' doesn't exist
mysql>
5.为了避免出现这些问题，建议用if not exists 语法来创建表
mysql> create temporary table if not exists test_temp (a int);
Query OK, 0 rows affected (0.00 sec)
mysql> show create table test_temp;
+-----------+---------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------+
| test_temp | CREATE TEMPORARY TABLE `test_temp` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2. 普通表的创建
2.1 基本语法
mysql> create table test_table(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> show create table test_table\G
*************************** 1. row ***************************
Table: test_table
Create Table: CREATE TABLE `test_table` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
2.2 alter table语法
1.添加列
mysql> alter table test_table add column name varchar(10);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test_table \G
*************************** 1. row ***************************
Table: test_table
Create Table: CREATE TABLE `test_table` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
2.删除列
mysql> alter table test_table drop column name ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> show create table test_table \G
*************************** 1. row ***************************
Table: test_table
Create Table: CREATE TABLE `test_table` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
注意：
当表记录很大的时候， alter table 会很耗时，影响性能 ONLINE DDL 5.6以后对在线DDL操作进行了优化，以提高性能。
三、外键
1. 外键示例
mysql> CREATE TABLE product ( -- 商品表
-> category INT NOT NULL, -- 商品种类
-> id INT NOT NULL, -- 商品id
-> price DECIMAL,
-> PRIMARY KEY(category, id) -- 主键是 (category, id)
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE customer ( -- 客户表
-> id INT NOT NULL, -- 客户id
-> PRIMARY KEY (id) -- 主键是 id
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE product_order ( -- 订单表
-> no INT NOT NULL AUTO_INCREMENT, -- number，自增长
-> product_category INT NOT NULL, -- 商品种类
-> product_id INT NOT NULL, -- 商品id
-> customer_id INT NOT NULL, -- 客户id
-> PRIMARY KEY(no), -- 主键是 no
-> INDEX (product_category, product_id), -- 对 (product_category, product_id) 做索引
-> INDEX (customer_id), -- 对 customer_id 做索引
-> FOREIGN KEY (product_category, product_id) -- 两个外键约束
-> REFERENCES product(category, id) -- 字段 product_category 引用自 product表的category
-> -- 字段 product_id 引用自 product表的id
-> ON UPDATE CASCADE ON DELETE RESTRICT, -- 级联跟新 和 严格模式删除
-> FOREIGN KEY (customer_id)
-> REFERENCES customer(id)
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)
2. 外键的级联更新与删除示例
1.创建一张父表与子表，让子表的外键关联父表的主键id
mysql> create table parent (
-> id int not null,
-> primary key (id)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> create table child (
-> id int,
-> parent_id INT,
-> index par_ind (parent_id),
-> foreign key (parent_id)
-> references parent(id)
-> on delete cascade on update cascade -- 比官网例子增加 update cascade
-> );
Query OK, 0 rows affected (0.02 sec)
2.插入一条数据，id=1，parent_id=1，直接报错 ，因为此时parent表中没有任何记录
mysql> insert into child values(1,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
3.先在parent中插入记录，然后在child中插入记录，且parent_id是在parent中存在的，正常插入。
mysql> insert into parent values(1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into child values(1,1);
Query OK, 1 row affected (0.01 sec)
4.插入parent_id=2的记录，报错。因为此时parent_id=2的记录不存在
mysql> insert into child values(1,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 1 |
+------+-----------+
1 row in set (0.00 sec)
mysql> select * from parent;
+----+
| id |
+----+
| 1 | //根据表结构的定义（Foreign_key），这个值就是 child表中的parent_id
+----+
1 row in set (0.00 sec)
5.更新parent表的id，可以看到child表的相关记录也被级联更新了
mysql> update parent set id=100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from parent;
+-----+
| id |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)
mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 100 | //自动变化，这是on update cascade的作用，联级更新，parent更新，child也跟着更新
+------+-----------+
1 row in set (0.00 sec)
6.删除parent id=100这条记录，发现child的这条记录也想要的被删除了
mysql> delete from parent where id=100;
Query OK, 1 row affected (0.01 sec)
mysql> select * from parent;
Empty set (0.00 sec)
-- id=1，parent_id=100的记录跟着被删除了，这是on delete cascade的作用
mysql> select * from child;
Empty set (0.00 sec)
7. 删除 之前的外键，新增加一个使用严格模式的外键
mysql> alter table child drop foreign key child_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table child add foreign key(parent_id)
-> references parent(id) on update cascade on delete restrict;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into parent values(50);
Query OK, 1 row affected (0.01 sec)
mysql> insert into child values(3,50);
Query OK, 1 row affected (0.01 sec)
8.插入会和之前一样会提示错误
mysql> insert into child values(3,51);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE)
9.删除parent表的记录报错了，因为这个时候child的外键使用的是严格模式，如果要删除parent这条记录，必须要先把child的相应记录删除了才行
mysql> delete from parent where id=50;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE)
mysql> delete from child where parent_id=50;
Query OK, 1 row affected (0.01 sec)
mysql> delete from parent where id=50;
Query OK, 1 row affected (0.00 sec)
3. 外键的选项及说明
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
其中reference_option的选项有以下四种：
- CASCADE
- 在父表上update/delete记录时，同步update/delete掉子表的匹配记录
- SET NULL
- 在父表上update/delete记录时，将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)
- NO ACTION （updatedelete 后面说明都不写表示 no action == restrict）
- 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
- RESTRICT
- 同no action, 都是立即检查外键约束
使用说明：
- CASCADE
- 删除：删除主表时自动删除从表。删除从表，主表不变
- 更新：更新主表时自动更新从表。更新从表，主表不变
- SET NULL
- 删除：删除主表时自动更新从表值为NULL。删除从表，主表不变
- 更新：更新主表时自动更新从表值为NULL。更新从表，主表不变
- RESTRICT、NO ACTION
- 删除：从表记录不存在时，主表才可以删除。删除从表，主表不变
- 更新：从表记录不存在时，主表才可以更新。更新从表，主表不变
注意：
外键约束，可以让数据进行一致性更新，但是会有一定的 性能损耗 ，线上业务使用不多。 通常上述级联更新和删除都是由应用层业务逻辑进行判断并实现。 trigger不会受外键cascade行为的影响,即不会触发trigger。
四、表碎片
1. 表碎片产生的现象及原因
- 现象： 有时候在表中删除了大量的无用数据，但是发现文件的大小没有发生变化，这是因为在删除数据后，遗留了大量的数据碎片所导致的。
- 原因： 在MySQL中delete删除数据的操作，并不会把数据文件真实删除，只是将数据文件的标识位删除，也没有整理数据文件，所以不会彻底的释放空间。当大量数据删除的时候，被删除数据的空间就会越来越大，当有新数据写入的时候会去占用这片空间，但又不是彻底占用。这就是所谓的碎片空间。这种存储空间在读取效率方面比正常占用的空间的效率要低很多！所以我们需要对这种表碎片进行整理。
2. 碎片计算方法及整理方法
mysql> show table status like 'employees'\G
*************************** 1. row ***************************
Name: employees
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 298124
Avg_row_length: 51
Data_length: 15245312
Max_data_length: 0
Index_length: 4726784
Data_free: 2097152
Auto_increment: NULL
Create_time: 2018-07-11 16:29:36
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
2.1 计算方法：
碎片大小=数据总大小-实际表空间文件大小 数据总大小=Data_length + Index_length=15245312+4726784=19972096 实际表空间文件大小=RowsAvg_row_length=29812451=15204324 碎片大小=数据总大小 - 实际表空间文件大小 = (19972096 - 15204324)/1024/1024≈4.55M
2.2 整理方法：
1.alter table table_name engine=innodb; 2.备份数据表，删除删掉，再重新导入 3.使用percona公司提供的pt-online-schema-change工具
./pt-online-schema-change --user=root --password=123456 --host=localhost --alter="engine=innodb" d=employees , t=employees --excute
4.在MySQL5.7开始，直接支持online ddl
五、表的统计信息
1. 统计信息存放位置
统计信息的数据字典：information_schema.tables中
2. 如何查看统计信息
Show index from table 或查看 information_schema.statistics表
Show table status 或查看 information_schema.tables表
3. 收集统计信息
3.1 手动收集：
Analyze table table_name； 适用于innodb和mysiam存储引擎。除非执行计划不准确，否则不要轻易执行该操作，如果是很大的表该操作会影响表的性能。
3.2 自动收集：
以下行为会自动触发统计信息的收集
- 第一次打开表的时候
- 表修改的行超过1/6或者20亿条时
- 当有新的记录插入时
- 执行show index from tablename或者执行show table、查询information_schema.tables\statistics 时
3.3 开启参数innodb_stats_on_metadata触发收集
当开启参数innodb_stats_on_metadata后访问以下表也会触发统计信息的收集 在访问以下表时，innodb表的统计信息可自动收集
information_schema.TABLES
information_schema.STATISTICS
information_schema.PARTITIONS
information_schema.KEY_COLUMN_USAGE
information_schema.TABLE_CONSTRAINTS
information_schema.REFERENTIAL_CONSTRAINTS
information_schema.table_constraints
4. 相关参数说明：
mysql> show variables like '%innodb_stats%';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
+--------------------------------------+-------------+
8 rows in set (0.00 sec)
Innodb_stats_sample_pages：每次收集统计信息时采样的页数，默认为20。
innodb_stats_persistent：默认on，将analyze table产生的统计信息保存于磁盘，直至下次analyze table为止，这样避免了统计信息动态更新，保证了执行计划的稳定，对于大表也节省了收集统计信息的所需资源。