高性能mysql学习笔记--高级特性

高性能mysql
七：高级特性
1，分区表
分区表是一个独立的逻辑表，但是底层由多个无力字表组成，实现分区的代码实际上是对一组地层表的句柄对象的封装，对分区表的请求，都会通过句柄对象转化成对储存引擎的接口调用，所以分区对于sql层来说是一个完全封装底层实现的黑盒子，对应用完全是透明的，但是从底层的文件系统来看就很容易发现，每个分区表都有一个使用#分隔命名的表文件。
mysql实现分区表的方式：对底层表的封装，意味着索引也是按照分区的字表定义的，而没有全局索引。
mysql在初丧件表时使用partition by字句定义每个分区存放的数据，在执行查询时优化器会根据分区定义过滤那些没有我们需要数据的分区。
分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中，这样做可以将相关的数据存放在一起，删除起来也很方便。
使用场景：
表非常大，以至于无法全部放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
分区表的数据更容易维护，例如：想批量删除大量数据可以使用清除整个分区的方式，另外还可以对一个独立分区进行优化，检查，修复等操作。
分区表的数据可以分布在不同的无力设备上，从而高效的利用多个硬件设备。
可以使用分区表来避免某些特殊的瓶颈，例如innodb的单个索引的互斥访问，ext3文件系统的inode锁竞争等。
如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。
分区的限制：
一个表最多只能有1024个分区。
在mysql5.1中，分区表达式必须是整数，或者返回整数的表达式，在mysql5.5中，某些场景中可以直接使用列进行分区。
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
分区表中无法使用外溅约束。
分区表的原理
分区表由很多个相关的底层表来实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有底层表都必须使用相同存储引擎）,分区表的索引只是在各个底层表各自加上一个完全相同的索引，从存储角度来看，底层表和一个普通表没有任何不同，储存引擎也无须知道这是一个普通表还是一个分区表的一部分。
操作逻辑：
select查询
当查询一个分区表的时候，分区层先打开并锁住所有底层表，优化器先判断是逗可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。
insert操作
当写入一个记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应的底层表。
delete操作
当删除一条记录，分区层先打开并锁住所有底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。
update操作
当更新一条记录，分区先打开并锁住所有的底层表，mysql先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。
虽然每个操作都会“先打开并锁住所有底层表”，但这并不是说分区表在处理过程中是锁住全表，如果存储引擎能够实现行级锁，例如innodb,则会在分区层释放对应表锁。
分区表的类型
mysql支持多种分区表，一般最多的是根据范围来分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以是包含列的表达式。

partition分区字句中可以使用各种函数，但有一个要求，表达式返回的值要是一个确定的整数，且不能是一个常数，我们可以使用year()函数，也可以使用其他如to_days()，根据时间间隔进行分区。
mysql还支持键值，哈希和列表分区。
使用分区表
案例：一个超大的表，每次查询肯定是不好查的，而且在数据量超大的时候，btree索引就无法起作用了，
两个策略：
全量扫描数据，不要任何索引
可以使用简单的分区方式存放表，不要任何索引，根据分区的规则大致定位需要的数据位置，只要能够使用where条件，将需要的数据限制在少数分区中，则效率是很高的。
索引数据，并分离热点
如果数据有明显的“热点”，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，，让这个分区的数据能够有机会都缓存在内存中，这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存。
什么情况下会出问题
上面介绍的两种策略都是基于两个非常重要的假设：查询能够过滤掉很多额外的分区，分区本身并不会带来很多额外的代价，而事实证明在某些场景会有问题：
null值会使分区过滤无效

分区列和索引列不匹配
如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。
选择分区的成本可能很高
不同类型的分区，性能也有不同，尤其是范围分区，如果在写入大量数据的话，会因为每一行需要写入哪个分区而导致成本很高，所以一般分区数载100个左右，当然键分区和哈希分区则没有这样的问题。
打开并锁住所有底层表的成本可能很高
维护分区的成本可能很高
增和删很快，但是重组和修改成本很高
分区的限制：
所有分区都必须使用相同的存储引擎
分区函数中可以使用的函数和表达式也有一些限制
某些存储引擎不支持分区
对于myisam，不能再使用load,index,into,cache操作
对于myisam，使用分区表时需要打开更多的文件描述符，虽然看起来是一个表，其实是有很多独立的分区，
随着版本升级，分区表越来越完善
查询优化
通过分区来查询，优点是优化器可以根据分区函数来过滤一些分区，从而扫描更少的数据，但是很重要的一点是要在where条件中带入分区列，有时候看着多余也要带上，

2，视图
视图本身是一个虚拟表，不存放任何数据，在使用sql语句访问视图的时候，他返回的数据是mysql从其他表生成的。
视图的两种处理方法：合并算法和临时表法

如果视图中包含group by,distinct,任何聚合函数，union,子查询等，只要无法在原表记录和视图记录中建立一一映射的场景中，mysql丢将使用临时表算法来实现视图，
想知道mysql使用的是合并算法还是临时算法，可以用explain来检查

视图的实现算法是视图本身的属性，和作用在视图上的查询语句无关。可以为一个查询指定算法

2.1可更新视图
可更新视图是指可与你通过更新这个视图来更新视图设计的相关表。
例如：update oceania set population = population *1.1 where name = 'australia';
如果视图定义中包含了group by ,union,聚合函数以及其他一些特殊情况，就不能被更新了。更新视图的查询也可以是一个关联语句，但是有一个限制，被更新的列必须来自同一张表中，另外，所有使用临时表算法实现的视图都无法被更新。
上面定义视图时使用的check option字句，表示任何通过视图更新的行，都必须符合视图本身where条件定义，所以不能更新视图定义列以外的列，
2.2视图对性能的影响
在某些情况下视图可以帮助提升性能，而且可以和其他提升性能的方式叠加使用。
2.3视图的限制
mysql不支持物化视图，，也不支持在视图中创建索引，不过可以构建缓存表或者汇总表的办法来模拟物化视图和索引。
总结：在我看来，视图一般用于查询，隐藏表，防止表被改，和用于预先关联好表，做好复杂逻辑，这样查询sql就可以很简单，可更新视图用的场景很少，
3，外建的约束
innodb是目前mysql中唯一支持外建的内置存储引擎，所以如果需要外建支持那选择就不多了。
使用外建是有成本的，比如外键通常都要求每次在修改数据时都要在另外一张表中多执行一次查找操作，虽然innodb强制外键使用索引，但还是无法消除这种约束检查的开销。
提升性能场景：如果想确保两个相关表始终有一致的数据，那么使用外键比在程序中检查一致行的性能要高的多，而且外键在相关数据的删除和更新上，也比程序高效。不过外键的操作是逐步进行的，所以会比批量操作慢些。
外键约束使得查询需要额外访问一些别的表，这也意味着需要额外的锁。
有时可以使用触发器来代替外键，对于相关数据的同时更新外键更合适，但是如果外键只是作数值约束，那么触发器或者显式的限制取值会更好些。
总结：通常还是在程序中实现约束会更好，外键的额外消耗会比较大。
4，在mysql内部存储代码
mysql允许通过触发器，存储过程，函数的形式，定时任务来存储代码。
优点：
他在服务器内部执行，离数据最近，另外在服务器上执行还可以节省带宽和网络延迟。
这是一种代码重用，可以方便的统一业务规则，保证某些行为总是一致，也可以提供一定的安全性。
他可以简化代码的维护和版本更新。
他可以帮助提升安全，比如提供更细的权限控制。
服务器可以缓存存储过程的执行计划，这对于需要反复调用的过程，会大大降低消耗。
因为是在服务器部署的，所以备份，维护都可以在服务器端完成。
他可以在软件开发和数据库开发之间更好的分工。
缺点：
mysql本身没有提供好用的开发和调试工具。
比较程序来讲，存储代码效率稍微差点。
存储过程会给数据库增加额外的压力。
mysql并没有什么选项可以控制储存程序的资源消耗，所以在储存过程中的一个小错误，可能直接把服务器拖挂。
总结：mysql存储代码要慎重。一般不会用到
4.1存储过程和函数

4.2触发器
4.3事件
4.4游标
4.5绑定变量
4.9字符集和校对
每种字符集都可能有多种校对规则，并且都有一个默认的校对规则。

mysql如何比较两个字符的大小
如果比较两个字符的字符集不同，mysql会将其转成同一个字符集再进行比较。
选择字符集和校对规则
极简原则：尽可能的统一字符集
校对规则同茶馆需要考虑的一个问题是，是否以大小写敏感的方式比较字符串，或者以字符串编码的二进制来比较大小。
二进制和大小写敏感校对规则不同在于：二进制直接使用字符的字节进行比较，大小写敏感是在多字节字符集时有更复杂的比较规则。
字符集和校对规则如何影响查询
某些字符集和校对规则可能会需要更多的cpu操作，可能会消耗更多的内存和存储空间，甚至还会影响索引的正常使用，
只有排序查询要求的字符集与服务器数据的字符集相同的时候，才能使用索引进行排序，索引根据数据列的校对规则进行排序，

4.10全文索引
通过数值比较，范围过滤等基本可以完成大多数的需要的查询了，但是通过关键字的匹配来进行查询，就需要基于相似度的查询，全文索引就是为这种场景设计的
全文索引可以支持各种字符内容的搜索(char,varchar,text)，也支持自然语言搜索个布尔搜索

4.11分布式(XA)事务
xa事务中需要一个事务协调器来保证所有的事务参与者都完成了准备工作，如果协调器收到所有的参与者都准备好的消息，就会告诉所有的事务可以提交了
内部xa事务：
mysql本身的插件式架构导致在其内部需要使用xa事务，mysql中各个存储引擎是完全独立的，彼此不知道对方的存在，所以需要一个外部协调者，xa事务会带来巨大的性能下降。
外部xa事务：

4.12查询缓存
mysql查询缓存保存查询返回的完整结果，当查询命中该缓存，mysql会立刻返回结果跳过解析优化和执行阶段。

mysql如果判断缓存命中
缓存存放在一个引用表中，通过一个哈希值饮用，这个哈希值包含了如下因素：查询本身，当前要查询的数据库，客户端协议的版本等一些其他可能会影响返回结果的信息。当判断缓存是否命中时，先使用sql语句和客户端发送过来的其他原始信息，任何字符上的不同例如空格注释都会导致缓存不命中。
当查询语句中有一些不确定的数据时，则不会缓存，例如now()或者current_date()的查询都不会缓存，还有如果查询中包含任何用户自定义函数，存储函数，用户变量，临时表，mysql的系统表或者任何包含级别权限的表，都不会被缓存。
所以：如果查询语句中包含任何的不确定函数，那么在查询缓存中是不可能找到缓存结果的。所以当一个查询语句过来时，mysql还是会去检查缓存，只不过是找不到而已，而不是不去检查，因为还没到解析阶段，mysql还不知道这个查询语句有没有不确定函数。
缓存很多时候可以提升性能，但是主意：
打开缓存对于读写都存在一定的消耗，因为要先检查缓存，存入缓存也需要一定消耗。
总结：总的来说缓存好处还是很大的，建议使用。但是不建议缓存很大的数据。
查询缓存如何使用内存
查询缓存是完全存储在内存中的，除查询结果外，需要缓存的还有很多别的维护相关的数据，需要一些内存专门用来确定哪些内存目前是可用的，哪些是已经用掉的，哪些用来储存数据表和查询结果之前的映射，哪些用来存储查询字符串和查询结果。这些大概需要40k内存，除此之外，mysql用于查询缓存的内存被分为一个个数据块，数据块是变长的，
当服务器启动的时候，他先出实话查询缓存需要的内存，这个内存池初始是一个完整的空闲快，大小是你锁配置的查询缓存大小再减去用于维护元数据的数据结构所消耗的空间。当有查询结果需要缓存的时候，mysql先从大的空间快中申请一个数据块用于存储结果，这个数据块需要大雨参数query_cache_min_res_unit的配置，即使查询结果远远小于此，因为mysql无法为每一个查询结果精确分配大小。
因为需要先锁住空间快，然后找到合适大小的数据块，所以相对来说，分配内存是一个非常慢的操作。
mysql自己管理一大块内存，而不依赖操作系统的内存管理。
什么情况下查询缓存能发挥作用

判断缓存是否好处：命中和写入比率，即qcache_hits和qcache_inserts的比值，一般这个比值大雨3:1就是有效的，最好能达到10:1

如何配置和维护查询缓存
query_cache_type：是否打开查询缓存，可以设置成off,on或者demand,demand表示在查询语句中明确写明sql_cache的语句才放入查询缓存，这个变量可以是全局的。
query_cache_size：查询缓存使用的总内存空间，单位是字节，这个值必须是1024的整数倍。
query_cache_min_res_unit：在查询缓存中分配内存块时的最小单位。
query_cache_limit：mysql能够缓存的最大查询结果，超过就不缓存。
query_cache_wlock_invalidate：如果某个数据表被其他的链接锁住，是否冷然从查询缓存中返回结果。
减少碎片
没有什么办法能够完全避免碎片，但是选择合适的query_cache_min_res_unit可以帮你减少由碎片导致的内存空间浪费。可以通过内存实际消耗(query_cache_size-Qcache_free_memory)除以qcache_queries_in_cache计算单个查询的平均缓存大小，如果查询结果很不平均，那么可以通过参数query_cache_limit限制可以缓存的最大查询结果，借此大大减少大的查询结果的缓存，最终减少内存碎片的发生。还可以通过参数qcache_free_blocks来观察碎片，参数qcache_free_blocks反映查询缓存中空闲快的多少。可以使用命令flush query cache完成碎片整理，这个命令会将所有的查询缓存重新排序，并将所有的空闲空间都聚集到查询缓存的一块区域上，但是不会清空，清空缓存由命令reset query cache完成。flush query cache会访问所有的查询缓存，在这期间任何其他的链接都无法访问查询缓存，从而导致服务器僵死一段时间，使用这个命令的时候需要特别小心，另外，根据经验，建议保持查询缓存空间足够小，方便维护时将服务器僵死控制在非常短的时间内。
提高查询缓存的使用率
Innodb和查询缓存
通用查询缓存优化
用多个小表代替一个大表对查询缓存有好处
批量写入时只需要坐一次缓存失败，所以相比单挑写入效率更好。
因为缓存空间太大，在过期操作的时候可能会导致服务器僵死。一个简单的解决方法就是控制缓存空间的大小，或者直接禁用查询缓存。
无法在数据库或者表级别控制查询缓存，但是可以通过sql_cache和sql_no_cache来控制某个select语句是否需要进行缓存，还可以通过修改会话级别的变量query_cache_type来控制查询缓存。
对于写密集的应用来说，直接禁用查询缓存可能会提高系统性能。
因为对互斥信号量的竞争，有时直接关闭查询缓存对读密集型的应用也有好处。
如果不想所有的查询都进入查询缓存，但是又希望某些查询走查询缓存，那么可以将query_cache_type设置成demand，然后在希望缓存的查询中加上sql_cache。
查询缓存替代方案
mysql查询缓存工作的原则是：执行查询最快的方式就是不去执行，但是查询仍然需要发送到服务器端，服务器也需要做一点工作，如果对于某些查询完全不需要与服务器通信，这时客户端的缓存可以很大程度上帮你分担mysql压力，