MYSQL之高性能的mysql(七)--MySQL高级特性
MySQL高级特性
分区表
对用户来说,分区表是一个独立的逻辑表,但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象(HandlerObject)的封装。
MySQL实现分区表的方式——对底层表的封装——意味着索引也是按照分区的子表定义的,而没有全局索引。
在下面的场景中,分区可以起到非常大的作用:
表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他均是历史数据。
分区表的数据更容易维护。例如,想批量删除大量数据可以使用清除整个分区的方式。另外,还可以对一个独立分区进行优化、检查、修复等操作。
分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备。
可以使用分区表来避免某些特殊的瓶颈,例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。
如果需要,还可以备份和恢复独立的分区,这在非常大的数据集的场景下效果非常好。
分区表本身也有一些限制,下面是其中比较重要的几点:
一个表最多只能有1024个分区。
在MySQL 5.1中,分区表达式必须是整数,或者是返回整数的表达式。在MySQL 5.5中,某些场景中可以直接使用列来进行分区。
如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来。
分区表中无法使用外键约束。
分区表的原理
SELECT查询
当查询一个分区表的时候,分区层先打开并锁住所有的底层表,优化器先判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据。
INSERT操作
当写入一条记录时,分区层先打开并锁住所有的底层表,然后确定哪个分区接收这条记录,再将记录写入对应底层表。
DELETE操作
当删除一条记录时,分区层先打开并锁住所有的底层表,然后确定数据对应的分区,最后对相应底层表进行删除操作。
UPDATE操作
当更新一条记录时,分区层先打开并锁住所有的底层表,MySQL先确定需要更新的记录在哪个分区,然后取出数据并更新,再判断更新后的数据应该放在哪个分区,最后对底层表进行写入操作,并对原数据所在的底层表进行删除操作。‘
分区表的类型
根据范围分区(例:2019年销售额,2020年销售额)
根据键值进行分区,来减少InnoDB的互斥量竞争。
使用数学模函数来进行分区,然后将数据轮询放入不同的分区。例如,可以对日期做模7的运算,或者更简单地使用返回周几的函数,如果只想保留最近几天的数据,这样分区很方便。
为了保证大数据量的可扩展性,一般有下面两个策略:全量扫描数据,不要任何索引。索引数据,并分离热点。
什么情况分区会出问题
NULL值会使分区过滤无效(5.5之后语法可解决)
分区列和索引列不匹配(应该避免建立和分区列不匹配的索引,除非查询中还同时包含了可以过滤分区的条件。)
选择分区的成本可能很高(对大多数系统来说,100个左右的分区是没有问题的。键分区和哈希分区,则没有这样的问题。)
打开并锁住所有底层表的成本可能很高
维护分区的成本可能很高
查询优化
分区最大的优点就是优化器可以根据分区函数来过滤一些分区。根据粗粒度索引的优势,通过分区过滤通常可以让查询扫描更少的数据(在某些场景下)。
存储过程
通常会希望存储程序越小、越简单越好。希望将更加复杂的处理逻辑交给上层的应用实现,通常这样会使代码更易读、易维护,也会更灵活。这样做也会让你拥有更多的计算资源,潜在的还会让你拥有更多的缓存资源。
触发器
触发器可以让你在执行INSERT、UPDATE 或者DELETE的时候,执行一些特定的操作。可以在MySQL中指定是在SQL语句执行前触发还是在执行后触发。触发器本身没有返回值,不过它们可以读取或者改变触发SQL语句所影响的数据。
需要注意以下几点:
对每一个表的每一个事件,最多只能定义一个触发器。
MySQL只支持“基于行的触发”——也就是说,触发器始终是针对一条记录的,而不是针对整个SQL语句的。如果变更的数据集非常大的话,效率会很低。
下面这些触发器本身的限制:
触发器可以掩盖服务器背后的工作,一个简单的SQL语句背后,因为触发器,可能包含了很多看不见的工作。例如,触发器可能会更新另一个相关表,那么这个触发器会让这条SQL影响的记录数翻一倍。
触发器的问题也很难排查,如果某个性能问题和触发器相关,会很难分析和定位。
触发器可能导致死锁和锁等待。如果触发器失败,那么原来的SQL语句也会失败。如果没有意识到这其中是触发器在搞鬼,那么很难理解服务器抛出的错误代码是什么意思。
事件
事件是MySQL 5.1引入的一种新的存储代码的方式。它类似于Linux的定时任务,不过是完全在MySQL内部实现的。你可以创建事件,指定MySQL在某个时候执行一段SQL代码,或者每隔一个时间间隔执行一段SQL代码。通常,我们会把复杂的SQL都封装到一个存储过程中,这样事件在执行的时候只需要做一个简单的CALL 调用。
在存储程序中保留注释
游标
MySQL在服务器端提供只读的、单向的游标,而且只能在存储过程或者更底层的客户端API中使用。因为MySQL游标中指向的对象都是存储在临时表中而不是实际查询到的数据,所以MySQL游标总是只读的。