MySQL高级特性
分区表
主要目的是将数据按照一个较粗的粒度分在不同的表中；
分区表是一个独立的逻辑表，但是底层由多个物理子表组成；
实现分区的代码实际上是对一组底层表的句柄对象的封装； 分区表对于SQL层来说是一个完全封装底层实现的黑盒子，对应用透明；
在底层文件系统，每一个分区表都有一个使用#命名的表文件；
mysql> CREATE TABLE `table_partition` (
-> `ftime` datetime NOT NULL,
-> `c` int(11) DEFAULT NULL,
-> KEY (`ftime`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-> PARTITION BY RANGE (YEAR(ftime))
-> (PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
-> PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
-> PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
-> PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (0.13 sec)
ls -lh /usr/local/mysql/data/db_test
total 7584
-rw-r----- 1 _mysql _mysql 128K 6 23 15:43 table_partition#P#p_2017.ibd
-rw-r----- 1 _mysql _mysql 128K 6 23 15:43 table_partition#P#p_2018.ibd
-rw-r----- 1 _mysql _mysql 128K 6 23 15:43 table_partition#P#p_2019.ibd
-rw-r----- 1 _mysql _mysql 128K 6 23 15:43 table_partition#P#p_others.ibd
MySQL在创建时使用Partition by子语句定义每个分区存放的数据； 在执行查询时，优化器会根据分区定义过滤分区，查询只需要查找包含重要数据的分区就可以了；
适用场景：
1.表非常大以至于无法全部放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据；
2.分区表数据更容易维护；方便清除整个分区，可以对一个独立分区进行优化、检查、修复等操作；
3.分区表的数据可以分布在不同的物理设备上，高效利用多个硬件设备；
4.可以用分区表来避免某些特殊的瓶颈，如InnoDB的单个索引的互斥访问，ext3文件系统的inode锁竞争等；
5.可以备份和恢复独立的分区，在大数据集的场景有用；
限制：
1.一个表最多只能有1024个分区；
2.分区表中无法使用外键约束；
3.如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来；
原理
从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分；
- select查询
查询分区时，分区层先打开并锁定所有底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据；
insert、delete、update操作也都会“先打开并锁住所有的底层表”；
分区表的类型
如何使用
在数据量超大的时候，B-Tree索引就无法起作用了，除非是索引覆盖查询；否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录； 如果数据量巨大，将产生大量随机IO；随之，数据库的响应时间将大到不可接受的程度；
为了保证大量数据的可扩展性，一般有两种策略：
1.全量扫描数据， 不要任何索引；
2.索引数据，并分离热点 如果数据有明显的热点，并且除了这部分数据，其他数据很少被访问到，可以将这部分数据单独放在一个分区
可能的问题
1.NULL值会使分区过滤无效；
2.分区列和索引列不匹配
3.选择分区的成本可能很高；
4.打开并锁住所有底层表的成本可能很高；
5.维护分区的成本可能很高
分区实现中的一些限制：
1.所有分区都必须使用相同的存储引擎；
2.分区函数可以使用的函数和表达式有限；
3.有些存储引擎不支持分区；
4.对于MyISAM表，使用分区时需要打开更多的文件描述符；
5.对于MyISAM分区表，不能再使用load_index_into_cache操作；
查询优化
分区最大的优势就是优化器可以根据分区函数来过滤一些分区；
所以对于访问分区表，在where条件中带入分区列很重要；
只能在使用分区函数的列本身进行比较才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行；
mysql> explain select * from table_partition where ftime='2018-12-1';
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | table_partition | p_2019 | ref | ftime | ftime | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from table_partition where YEAR(ftime)='2018';
+----+-------------+-----------------+-------------------------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+-------------------------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | table_partition | p_2017,p_2018,p_2019,p_others | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
+----+-------------+-----------------+-------------------------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
不能根据表达式的值去过滤分区:
mysql> explain select * from table_partition where YEAR(ftime)='2018'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: table_partition
partitions: p_2017,p_2018,p_2019,p_others
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
合并表
一种早期的、简单的分区实现；将被淘汰的技术；
允许用户单独访问各个子表；分区表和优化器的结合更紧密；
视图
5.0版本之后引入；
视图本身是一个虚拟表，不存放任何数据，在使用SQL语句访问视图的时候，它返回的数据是MySQL从其他表生成的；
可更新视图
可以通过更新视图来更新视图涉及的相关表；
1.如果视图定义中包含了GROUP BY、UNION、聚合函数。以及其他特殊情况，就不能被更新了；
2.使用临时表算法实现的视图也无法被更新；
视图对性能的影响
在MySQL中某些情况下视图也可以帮助提升性能； 并且视图还可以和其他提升性能的方式叠加使用；
视图的限制
MySQL还不支持物化视图；
物化视图：将视图结果数据存放在一个可以查看的表中，并定期从原始表中刷新数据到这个表中；
MySQL也不支持在视图中创建索引；
外键约束
InnoDB是目前MySQL唯一支持外键的内置存储器；
如果只是使用外键做约束，通常在应用程序中实现该约束会更好；
外键会带来很大的额外消耗；
在MySQL内部存储代码
MySQL允许通过触发器、存储过程、函数的形式来存储代码；
优点：
1.在服务器内部执行，离数据最近，节省带宽和网络延迟；
2.代码重用；
3.简化代码的维护和版本更新；
4.提升安全，比如更细粒度的权限控制；
5.服务器端可以缓存存储过程的执行计划，对于反复调用的过程会大大降低消耗；
6.备份和维护都可以在服务器端完成；所以存储程序的维护工作会很简单；
7.在应用开发和数据库开发人员之间更好的分工；
缺点：
1.MySQL编写存储代码比其他数据库更难；
2.相对程序的代码，存储代码效率要差一点；存储代码可用的函数非常有限；
3.给应用程序代码的部署带来额外的复杂性；
4.安全隐患；非标准的加密功能放在存储程序时；
5.给数据库服务器增加额外的压力；
6.MySQL并没有什么选项可以控制存储程序的资源消耗；
7.调试MySQL存储过程比较困难；
存储过程和函数
触发器
事件
MySQL5.1引入的一种新的存储代码的方式；类似于Linux的定时任务，不过是完全在MySQL内部实现的；
在存储程序中保留注释
使用版本相关的注释；