高性能mysql（第三版）笔记（三）

阳光穿透心脏的1/2处 2022-10-17 10:55 143阅读 0赞

### 高级特性 ###

#### mysql分区 PARTITION BY ####

> 分区表是一个独立的逻辑表, 但是底层由多个物理子表组成. 实现分区的代码实际上是对一组底层表的句柄对象的封装;

**使用场景**

1.  表非常大以至于无法全部都放在内存中, 或者只在表的最后部分有热点数据, 其他均是历史数据
2.  分区表的数据更容易维护
3.  分区表的数据可以在不同的物理设备上
4.  使用分区表来避免某些特殊的性能瓶颈. 如 InnoDB的单个索引的互斥访问

**限制**

1.  无法使用外键
2.  一个表最多1024个分区

**注意**

1.  分区列最好和索引列保持一致, 否则会导致查询无法进行分区过滤
2.  选择分区的成本可能很高,可以限制分区数量,100个左右的分区
3.  打开并锁住所有底层表的成本可能很高,可以试着批量操作
4.  维护分区的成本可能很高  
    **例子**

PARTITION BY COLUMNS(order_date)
    
    PARTITION SELECT * FROM sales_by_day where day between `2010-01-01` and `2020-12-31`

#### 视图 ####

**性能优化**

1.  将视图的定义`sql`直接包含进查询的`SQL`当中

-- 创建临时表 或者视图
    create temporary table tmp_oceania_123 as
    select * from country where continent = 'oceania';
    -- 查询
    select code, name, from  tmp_oceania_123 where name = 'Australia'
    
    -- 可以优化为以下SQL
    select code, name from country where continent = 'oceania' and name = 'australia'

1.  创建视图有两种算法:

*  临时表算法
 *  合并算法  
    尽可能使用合并算法 而不使用临时表算法, 如果视图中包含`group by , distinct , 任何聚合函数, union , 子查询等` 只要无法在原表记录和视图记录中建立一一映射的场景中, `MySQL` 都将使用**临时表算法**来实现视图

##### 1.可更新视图 #####

> 可更新视图(updatabale view) 是指可以通过更新这个视图来更新视图涉及的相关表

**注意**  
如果视图定义包含了 `group by, union, 聚合函数, 以及其他一些特殊情况` 就不能被更新了,另外所有使用临时表算法实现的视图都无法被更新.

##### 2.视图对性能的影响 #####

> 很多人认为视图不能提升性能. 实际上, 在 `MySQL` 中某些情况下,视图也可以帮助提升性能. 而且视图还可以和其他提升性能的方式叠加使用.

*  可以使用伪临时视图实现一些功能.

**视图劣势**

1.  临时表算法实现的视图无法建立索引(mysql5.6版本有所改进)
2.  临时表算法实现的视图, `MySQL` 以递归的方式执行这类视图
3.  视图背后的逻辑可能非常复杂; 即使一个简单的视图查询, `explain`也可能查询出几百行,因为可能有表关联着其他视图
4.  **视图中也无法使用索引**

##### 3.外键 #####

> `Innodb` 是目前MySQL中唯一支持外键的内置存储引擎;  
> 使用外键是需要成本的; **比如外键通常都要求每次在修改数据时都要在另外一张表中多执行一次查找操作, 虽然`Innodb`强制外键使用索引, 但还是无法消除这种约束检查的开销,而且外键也需要存储空间.**

#### 4.全文索引 ####

> `mysql` 在5.6.4以上 `Innodb`开始支持全文索引,支持 `char, varchar, text` 等; 但对中文支持性不太好,因为 分词使用的是空格; 到了`MySQL` 5.7版本, 有了中文分词插件`ngram` ,使得全文查找更全面;

--一般使用关键字 match aginst
    
    select title, match(title) aginst('aaa') as relevance
    from sakila.film_text
    where match(title) aginst('aaa');

**布尔搜索**

--一般使用关键字 match aginst
    
    select title, match(title) aginst('aaa') as relevance
    from sakila.film_text
    where match(title) aginst('+aaa' in boolean mode);

**全文索引的限制**

1.  `MySQL`全文索引中只有一种判断相关性的方法 : 词频;
2.  索引也不会记录索引词在字符串中的位置
3.  数据量大小也是一个问题. 全文索引只有全部在内存中的时候,性能才非常好. 如果内存无法装在全部索引,那么搜索速度可能非常慢
4.  全文索引会有更多的碎片  
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zlV0syex-1622622256101)(en-resource://database/2666:1)\]

**全文索引优化**

1.  提供一个好的停用词表
2.  为全文索引提供单独的键缓存,以免被其他索引挤出内存

#### 5.查询缓存 ####

> `mysql`查询缓存保存查询返回的完整结果, 当查询命中该缓存时,`mysql`会立刻返回结果,跳过了解析. 优化. 和 执行阶段;  
> 如果查询缓存会跟踪涉及的每一个表, 如果表发生变化, 则所有相关联的缓存失效

**注意**  
随着服务器越来越强大. 查询缓存被发现是一个影响服务器扩展性的因素. 它可能成为整个服务器资源竞争的单点,在多核服务器上还可能导致服务器僵死; 所以建议默认**关闭查询缓存**

**如果查询中包含一个不确定的函数,`MySQL`则不会检查查询缓存** 这个说法是不正确的, 因为检查查询缓存的时候,还没有解析`SQL`语句, 所以`MySQL`并不知道查询语句是否包含这类函数.`MySQL`只做一件事, 就是通过一个大小写不敏感的检查看看`SQL`语句是不是以`SEL`开头;  
准确的说法应该是 **如果查询语句中包含任何的不确定函数. 那么查询缓存中是不可能找到缓存结果的**

**重点**  
1.查询缓存是一个 **排他锁操作** 性能消耗可能不容小觑;  
2.事务的一些特性会限制查询缓存的使用  
3.如果查询缓存使用了很大量的内存, 缓存失效操作就可能成为一个非常严重的问题瓶颈, 因为这个操作是靠一个全局锁操作保护的,所有需要做该操作的查询都要等待这个锁. 而且无论是检测是否命中缓存,还是缓存失效检测都需要等待这个全局锁;

#### 6.XA事物 ####

> `两阶段提交XA`事物中需要有一个事务协调器来保证所有的事物参与者都完成了准备工作(第一阶段), 如果协调器收到所有的参与者都准备好的消息, 就会告诉所有的事物可以提交了(第二阶段) . `MySQL`在`XA`过程中扮演一个参与者的角色;

**XA事务会使`MySQL`性能严重下降, 因为破坏了`MySQL`内部的"批量提交"(一种通过单磁盘I/O操作完成多个事务提交的拘束), 使得`MySQL`不得不进行多次额外`fsync()`操作,即从内存强制把数据刷到磁盘中保存**