高性能MySQL第七章 读书笔记

第七章 MySQL的高级特性

• 分区操作时，可以只针对某个区进行操作，而且在底层文件系统中的表现，分区是多个表文件，可以高效地利用多个硬件设备。
• 如果分区字段中有主键或者唯一索引的列，那么所有的主键和唯一索引列都必须包含进来。
• 当操作分区表的时候，优化器会判断能否过滤部分分区。
• Mysql的分区支持范围，键值，哈希和列表分区。
• 当数据量超大的时候，B-Tree索引就无法起作用了，除非是索引覆盖查询，否则在回表查数据的时候，会产生大量的随机IO，导致超长的响应时间，而且维护索引的代价非常高。
• 分离热点能有效利用数据库缓存。
• NULL可能会使分区失效，因为在基于列函数进行分区时，NULL值会到第一个分区，每次根据该列函数去查找时，也总会去到第一个分区去查找数据。
• 分区列和索引列要尽量对应。
• 在选择分区时，成本可能很高，特别是针对范围和列表分区，因为每次都要计算和查找，特别是在存在大量分区的时候。
• MySQL只有使用分区函数的列本身进行比较才能分区，而不能根据表达式的值进行分区。
• 视图有合并表算法和临时表算法两种实现方案。

image-20200729230044885

• 可以用EXPLAIN来查看视图是用什么方式实现的，如果查询出来的select_type 是DERIVED，那么该视图是由临时表实现的。
• 只有合适的修改才能够更新视图涉及的相关表。如果视图涉及到聚合函数以及想修改视图以外的数据，那是不会成功的。
• 利用视图我们可以在重构schema的时候使用视图来让应用代码不报错地运行。
• 但是视图还不够成熟，存在着性能和可能有未知的bug。而且创建出来的视图没有注释，对开发不友好。
• 强烈推荐外键使用索引。
• 外键约束使得查询需要额外访问一些别的表，意味着额外的锁。

存储代码的优点：

1. 内部执行，离数据最近，在服务器上执行可以节省带宽和网络延迟。
2. 代码重用。
3. 简化代码的维护和版本更新。
4. 通过暴露一下限制操作提升安全。
5. 服务器会缓存存储过程的执行计划。
6. 代码集中。

缺点：

1. 不好调试。
2. 代码执行效率低，函数有限。
3. 会使应用程序代码更加复杂。
4. 对数据库增加额外的压力。
5. 无法控制存储过程的消耗。
6. 异常处理非常困难。

• 建议不用存储过程。
• 触发器可以让你在执行INSERT，UPDATE或者DELETE的时候执行一些特定操作。
• 触发器可以简化应用逻辑还可以提高性能（但是感觉还是少用好一点，因为代码逻辑应该尽可能地集中好管理。。个人看法）
• 每个表的每一个时间，最多只能定义一个触发器。触发器只能基于行触发，如果语句牵扯到大量的行，效率会很低。
• 触发器失败会使原来的sql也失败。
• MySQL的事件类似定时脚本。
• 使用绑定变量（prepared statement）可以大大提高客户端和服务器传输的效率。当创建一个绑定变量SQL之后，客户端可以向服务端发送一个SQL查询的原型。服务端会解析这个语句框架，存储这个SQL的部分执行计划，返回给客户端一个SQL语句处理句柄。利用这个句柄，客户端可以高效进行执行。因为

1. 服务端只需要解析一次SQL语句。
2. 某些优化器的工作只需要执行一次，因为它会缓存一部分的执行计划。
3. 只发送参数和句柄，可以减少网络开销。

• 绑定变量更加安全。
• 绑定变量的限制

1. 绑定变量是会话级别的，断开之后就没办法再使用了。
2. 如果每次执行sql之后都不再复用绑定变量了，效率还不如直接执行。
3. 如果总是忘记释放绑定变量资源，则服务端很容易泄露。

• 创建对象时的默认值会从表的默认值，数据库的默认值，服务器的默认值逐层继承。但是最终影响的是列的编码，只有当列没有指定编码时，才会需要默认值。
• 通讯的时候，可能使用不同的字符集，服务端假设客户端是按照chanracter_ser_client来传输数据和SQL语句的，收到客户端的SQL后，会将其转化为了character_set_connection,返回客户端的时候，会把其转化为charachter_set_result。
• 除非真的很特别的编码，否则所有的编码应该都是一致的。不同字符集之间相互的对比和转化都需要额外的资源。
• 虽然utf8是一种多字节编码，但是在mysql内部通常使用一个 定长空间来存储字符串，一个编码时utf8的char（10）需要30个字节，但是varchar在磁盘中没有这个困扰，但是在临时表中就会分配最大可能的长度。
• utf8虽然能让你世界都清净了，但是会带来额外的空间消耗。
• 查询缓存会追踪查询中涉及的每个表，这些表一旦发生变化，缓存立即失效。
• 判断是否命中缓存是根据查询本身，要查的数据库等会影响结果的信息进行哈希，任何变化都换导致缓存失效。
• 带有任何不确定的函数的查询，都不会对查询结果进行缓存。
• 查询缓存是一个加锁排他操作。
• 缓存设置过大，对myql维护缓存也是不小的消耗。
• 查询耗时长但是数据集小的缓存效果最好。
• 利用SHOW STATUS 中的Qcache_hits（命中缓存次数）和Com_select（正常查询次数）可以看到缓存的命中情况。

无法缓存的情况，会导致状态值Qcache_not_cached增加：

1. 查询语句存在不确定函数。
2. 查询结果太大。

• 如果缓存每次都被修改所清除，那么Com_select 和Qcache_inserts的值应该是相当的。

缓存可配置的参数：

1. query_cache_type，是否打开缓存
2. query_cache_size，缓存的总空间大小
3. query_cache_min_res_unit，缓存中分配内存块的最小单位。
4. query_cache_limit，能缓存的最大查询结果。
5. query_cache_wloack_invalidate，锁的细节，通常不用管。
6. 如果缓存没有带来什么好处，建议禁用它。

image-20200731173538255

• 批量写入时只需要做一次缓存失效，所以相比单条效果更好。