mysql 数据库服务自主优化能力(4种）

mysql 数据库服务自主优化能力(4种）

• 自主优化功能一：AHI（索引的索引）
• 自主优化功能二：CHANGE BUFFER
• 自主优化功能三：ICP （索引下推）
• 自主优化功能四：MRR

下边我们来一一介绍

1 自主优化功能一：AHI（索引的索引）

AHI全称（中文名称）为自适应的hash索引/散列索引，用于在内存中建立索引，快速锁定内存中的热点数据索引页位置；

正常情况下，所有数据都是存储在磁盘中的，如果想访问读取相应磁盘的数据信息，都是会将磁盘数据调取存放在内存中，即消耗IO；

对于数据库服务而言，想要读取数据信息，也是会从磁盘中读取存储页，在放入内存中被数据库服务进行访问，索引访问也是一样的；

但是当数据页大量的被存放在内存中后，从大量内存中的数据页找到想要的，也是比较困难的事情；

因此，可以对内存中经常被访问数据索引页建立一个hash索引，从而可以帮助数据库服务快速定位内存中想要找的索引数据页；

AHI功能配置信息:

mysql> show variables like 'innodb_adaptive_hash_index';
+--------------------------------------+-------+
| Variable_name                           | Value |
+--------------------------------------+-------+
| innodb_adaptive_hash_index  | ON    |
+--------------------------------------+-------+
1 row in set (0.00 sec)

2-自主优化功能二：CHANGE BUFFER

早期版本称为 insert buffer，只是对插入操作有作用，版本更新后(5.6)，可以对插入 修改 删除操作都有作用效果；

change buffer主要是针对辅助索引的缓冲区，属于内存结构上的应用；

changerbuffer应用原理：假设现在需要插入一行数据信息

① 插入一行数据信息到表中，将会实时立即更新聚簇索引信息，因为利用聚簇索引是用来获取数据页上详细原表数据信息的；

② 插入一行数据信息到表中，不会实时立即更新辅助索引信息，因为利用辅助索引是用来获取索引页上聚簇索引数据信息的；

如果此时实时更新了辅助索引的信息，有可能会导致出现数据页分裂，造成辅助索引树结构变化，形成索引树访问阻塞(锁机制)；

③ 为了避免辅助索引树结构变更，对数据库服务并发访问的影响，可以将插入的数据信息，暂时存储在缓冲区中；

当利用辅助索引检索数据时，可以将检索到数据页范围信息调取到内存中，与缓存区数据进行合并，自然可以检索到插入的数据

说明：在数据表中插入 修改 删除数据时，聚簇索引树会进行同步实时更新，辅助索引树会进行异步延时更新。

change_buffer功能配置信息:

mysql> show variables like '%change_buffer%';
+-----------------------------------------+-------+
| Variable_name                                | Value |
+-----------------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering              | all   |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)
--all：    默认值。开启buffer inserts、delete-marking operations、purges
--none： 不开启change buffer

idx(a,b,c)
where a=10 and b like '%x%' and c=z 

mysql> show variables like '%switch%';
mysql> set global optimizer_switch='index_condition_pushdown=off';
-- 实现测试练习完，需要恢复开启（操作可以省略）
​
# 测试练习
mysql> select * from t100w where k1='qj' and k2 like '%v%';
mysql> desc select * from t100w where k1='qj' and k2 like '%v%';
-- extra列显示using index condition信息，表示应用了索引下推
​
# 进行压测
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='baimei' --query="select * from t100w where k1='qj' and k2 like '%v%" engine=innodb --number-of-queries=20000 -uroot -p123456 -h192.168.30.101 -verbose

mysql > set optimizer_switch='mrr=on';
mysql > set global optimizer_switch='mrr_cost_based=off';
Query OK, 0 rows affected (0.06 sec)

优化器算法扩展：BNL/BKA（自行了解）

欢迎来撩 ： 汇总all