MySQL从5.0和5.1版本开始引入了很多高级特性,包括分区、触发器等,这些新特性也许不会频繁用到,但对于某些场景下,会给我们更多DB层面优化的选择,所以,了解一下总是有益的。
分区表,通过在创建表时,使用partition by子句来定义每个分区存放的数据,以达到将数据按照一个比较粗的粒度分在不同的表中,这样,就可以方便的对数据进行分区处理。分区表,一般在下面场景中,可以体现其价值
对用户来说,分区表是一个独立的逻辑表,但是其底层是由多个物理子表组成。实现分区的代码实际是对一组底层表句柄对象的封装,对分区表的请求,都会通过句柄对象转化为存储引擎的接口调用,在存储引擎层面,分区表和普通表时没有区别的。
分区表在处理DML时,是可以通过分区特性进行分区粒度上的过滤的,其执行相关语句的逻辑如下
select查询:
当查询一个分区表的时候,分区层先打开幷锁住所有的底层表,优化器先般判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问数据
insert操作
当写入一条数据时,分区层先打开幷锁住所有的底层表,然后确定接收数据的分区,再将记录写入对应底层表
delete操作
删除一条记录时,分区层先打开幷锁住所有的底层表,然后确定数据的分区,在对应的底层表上进行删除操作
update操作
当更新一条记录时,分区层先打开幷锁住所有的底层表,确定需要更新的记录所在的区,到底层表取出数据幷更新,再判断更新后的数据所属的区,然后在新区进行写入操作幷删除旧的底层表数据
可以看到,在每一个操作前,分区层都会先打开幷锁住所有的底层表,但是这幷不是说分区表在处理过程中是锁全表的,如果存储引擎有自己实现的行级锁,如innodb,则在分层区会是释放对应的表锁。
MySQL支持多种分区表,应用场景最多的还是根据范围进行分区,分区表达式可以是列,也可以是包含列的表达式,但是有一个限制是,表达式返回的值要是一个确切的整数,且不能是常数。除了按范围,MySQL还支持键值、哈希和列表分区。
分区表其实可以理解为索引的最初形态,通过分区,我们可以以代价非常小的方式定位到需要的数据在哪一篇区域,在这片区域中,你可以做顺产扫描,可以建索引,可以将数据都缓存到内存中,因为分区不需要精确的定位到每条数据,也就无需额外的数据结构,这样代价是非常小的,在数据量非常巨大,使用索引也无法提升性能到可接受地步时,分区表就可以派上用场了,一般使用分区表,有如下两个策略:
全量扫描数据,不要任何索引
这种方式,需要在where条件中包含分区过滤条件,将数据扫描范围限制在少数分区中
索引数据,幷分离热点
如果数据有明显的热点,而且除了这部分数据,其他数据很少被访问到,那么可以将这些热点数据放置到一个区中,让这个分区的数据能够有机会都缓存在内存中,这样就能够使用索引,也能有效的使用缓存
分区最大的优点就是可以根据条件来过滤一些分区的数据,根据粗粒度的优势,让查询扫描更少的数据。所以,对于分区表的查询来说,最重要的一点就是在where条件中带上分区列,但有时候,分区条件也会失效,我们要优化的也就是分区条件失效的情况
select * from table where year(day) = 2010
MySQL只能使用分区函数的列本身进行比较时才能过滤分区,而不能够根据表达式的值去过滤分区,即使这个表达式就是分区函数也不行,这个和索引类似,可以进行如下优化
select * from table where day between 2010-01-01 and 2010-12-31
合并表和分区表相反,分区表是将一个大表拆分为多个小表,而合并表则是将多个表合并为一个表,这似乎并不能带来多大的用处,所以这个功能点可以忽略。
视图本身是一个虚拟表,不存放任何数据,在使用SQL语句访问视图时,它返回的是从其他表中生成的数据,视图和表在同一个命名空间中。MySQL实现视图有两个方法,一种是合并算法,一种是临时表算法。
可更新视图是指可以通过更新视图来更新视图相关表。但是如果视图中包含了group by、union、聚合函数以及一些特殊情况,就不能被更新了,更新视图的查询也可以是一个关联语句,但是关联被更新的列必须来自同一张表。在定义视图时,如果使用了check option子句,那么更新的列必须包含在定义视图列以内
在重构时,可以使用视图,使得在修改视图底层结构的时候,应用代码还可以继续不报错的运行,可以使用视图进行基于列的权限控制,却不需要在真正的系统上建立权限,因此没有额外的开销。
外键约束主要用来保证关联表的数据一致性,但是使用外键会带来一些成本,比如对一个表进行修改操作时,都需要去检查关联表,而且外键也会约束扩展性,所以虽好能将外键约束在程序中进行实现。
MySQL允许通过触发器、存储过程、存储函数的形式来存储代码,使用存储代码能带来一些好处,但同时也需要一些牺牲。
优点:
缺点
存储程序越小越简单越好,将复杂的逻辑交个上层应用去处理,这样代码更具有易读性,且更易维护,也会更灵活,但在重复执行一些小操作的情况下,存储过程可以节约大量的网络开销和解析成本。如插入一定数据数据到一张表中
触发器可以让你在执行insert,update,delete的时候执行一些特点的操作,可以知道在sql执行前或执行后触发,触发器本身没有返回,但是可以读取或改变触发SQL语句所影响的数据。触发器实现简单,同时功能也有限,所以在重度依赖触发器的情况下,需要注意以下几点
在触发器的使用中,对性能影响最大的就是其只基于行的触发
事件类似于定时任务,你可以通过事件,指定MySQL在某一段时间执行一段SQL代码或每隔一个时间间隔。通常将SQL封装到一个存储过程中,然后由事件使用call函数来调用
存储过程、存储函数、触发器、事件通常包含大量的重要代码,在代码中加上注释是有必要性的,但是MySQL客户端会自动过滤掉注释,一个可用的技巧就是使用版本相关的注释,为了使版本相关的代码不被执行,可用指定一个非常大的版本号。
MySQL在服务器端提供只读的、单向的游标,因为MySQL游标中指向的对象都是存储在临时表中,而不是实际查询到的数据,所有MySQL游标总是只读的。
当创建一个绑定变量的SQL时,客户端向服务器发送一个sql语句的原型,服务器端收到这个SQL语句框架后解析幷存储这个语句的执行计划,返回个客户端处理句柄,之后每次执行这类查询,客户端指定使用这个句柄。使用绑定变量有如下好处
存储过程只能使用SQL来编写,而UDF没有这个限制,你可以使用支持C语言调用约定变得任何编程语言来实现
字符集是一种字节到字符之间的映射,而校对规则是指一个字符集的排序方法。
全文索引用于满足基于相似度查询,通过关键字来进行查询过滤的场景
存储引擎的事务特性用来保证在存储引擎实现ACID,而分布式事务则让存储引擎级别的ACID可以扩展到数据库层面,甚至是扩展到多个数据库之间。
完全相同的查询在重复执行的时候,查询缓存可以立即返回结果,而无需执行整个查询流程,查询缓存可以减少很多重复查询的开销,但是查询保存在内存中,在使用查询缓存时,也需要考虑到查询缓存失效,内存碎片等带来的性能影响。
本文地址:https://blog.csdn.net/zhangyunfeihhhh/article/details/107238544
如对本文有疑问, 点击进行留言回复!!
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
网友评论