AliSQL 20170716版本发布 Invisible Indexes 功能和 SELECT FROM UPDATE 语法

2022-01-21 07:46:27

Abstract

在传统的关系数据库中，想要在堆表或者索引组织表中快速的检索到目标数据，添加索引是一个常用的手段，但过多的索引不但增加空间的开销，
还会带来写入性能的衰减，如何降低在线删除索引的风险，Invisible Indexes 提供了一个风险可控的方法。

在面临一个常见的业务场景，比如更新某行记录，然后查询变更后的记录内容的时候，通常都是UPDATE + SELECT 两条语句来完成，
AliSQL 扩展了语法，提供SELECT...FROM UPDATE语句，在完成update变更的同时，返回整行记录内容，减少一次网络调用。

AliSQL REPO: https://github.com/alibaba/AliSQL
AliSQL Release Notes: https://github.com/alibaba/AliSQL/wiki/Changes-in-AliSQL-5.6.32-(2017-07-16)

1. Invisible Indexes

概要

AliSQL 为 index 增加了两个新的属性，visible/invisible，保存在FRM文件中，这两个属性决定了这个索引是否能够
被优化器使用，索引默认是visible，当变更为invisible的时候，无论是否使用了force index hint，这个索引不再会被优化器使用。
在引擎层面，这个索引属性是透明的，即引擎会继续维护索引记录变更。
注意区分disabled index属性。

语法和使用方法

新增的语法例如：

1. CREATE INDEX:
    
CREATE TABLE t ( a INT, b INT );
CREATE INDEX a_invisible ON t(a) INVISIBLE;
CREATE INDEX b_visible ON t(b) VISIBLE;

2. ALTER TABLE:

ALTER TABLE t ALTER INDEX a INVISIBLE;
ALTER TABLE t1 ALTER INDEX b VISIBLE;


3. SHOW CREATE TABLE:
CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a_invisible` (`a`) /*!50616 INVISIBLE */,
  KEY `b_visible` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1



4. SHOW INDEXES;
mysql> SHOW INDEXES FROM t;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| t     |          1 | a_invisible |            1 | a           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| t     |          1 | b_visible   |            1 | b           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

5. mysql> SELECT * FROM information_schema.statistics WHERE is_visible='NO';
+---------------+--------------+------------+------------+--------------+-------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME  | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | IS_VISIBLE |
+---------------+--------------+------------+------------+--------------+-------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+
| def           | test         | t          |          1 | test         | a_invisible |            1 | a           | A         |           0 |     NULL | NULL   | YES      | BTREE      |         |               | NO         |
+---------------+--------------+------------+------------+--------------+-------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+

场景使用

在DBA的日常运维中，为了加速SQL查询响应，随着业务的发展在表中积累了比较多的索引，而随着业务的变更，有部分索引可能已经不再需要，需要做删除处理。
在线删除索引，变成了一个风险极高的操作，如何降低删除索引的风险，主要评估两点：

需要统计信息展示索引的使用情况
当删除索引后，出现异常，如何快速的回滚

针对这两个点，AliSQL提供了较完善的方法进行评估：

1. 统计信息
AliSQL提供了一个内存统计表index_statistics，来统计每张表的索引使用统计信息，如下：

mysql> select * from t where b=2;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from information_schema.index_statistics;
+--------------+------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+------------+-----------+
| test         | t          | b_visible  |         3 |
+--------------+------------+------------+-----------+
1 row in set (0.00 sec)

这表明 t 表中的 b_visible 索引被拿来检索了3条记录，而t表中的其他index没有被使用过，基于这样的统计信息，
DBA可以评估一段时间，某些索引没有被使用过，就可以相对安全的进行删除。

2. 回滚
当对index进行删除的时候，可以分两步操作：

先invisible这个索引：

ALTER TABLE t ALTER INDEX a invisible;

完成后，这个索引就不会再被optimizer使用，观察一段使用，确认没有影响后，再进行安全删除:

ALTER TABLE T DROP INDEX a;

在真正的删除之前，如果出现异常，可以快速的回滚：

ALTER TABLE t ALTER INDEX a visible;

所以，通过index statistic 和 index invisible 功能，可以安全对索引进行在线删除。

2. SELECT FROM UPDATE

概要

为了优化在业务中使用 UPDATE + SELECT 两次调用的开销，AliSQL提供了 SELECT...FROM UPDATE 语法，
在一次SQL请求中，完成UPDATE变更和行记录返回。

语法和使用

CREATE TABLE t(a int, b int);
INSERT INTO t values(1, 1);
SELECT * FROM UPDATE t set a=a+1 where a=1;

mysql> SELECT * FROM UPDATE t set a=a+1 where a=1;
+------+------+
| a    | b    |
+------+------+
|    2 |    1 |
+------+------+
1 row in set (0.00 sec)

通过 SELECT...FROM UPDATE 语法，减少一次网络调用，在核心业务集群上，收益是非常可观的。

3. InnoDB Crash

概要

InnoDB在进行表结构变更的时候，如果是online操作，并且变更过程中失败，那么对于数据字典的dirty清理会延迟清理，但master thread在淘汰长时间未使用的Dictionary Object 的时候，因为对象未清理干净，导致实例crash。
详细的bug复现过程和修复方法，可以参考：http://mysql.taobao.org/monthly/2017/06/05/

MySQL官方的BUG跟踪：https://bugs.mysql.com/bug.php?id=86607
MariaDB的BUG跟踪：https://jira.mariadb.org/browse/MDEV-13051

4. Semisync优化

概要

Semisync的 ACK receiver 线程使用了 select() 系统库调用来监听slave线程，但由于 select() 方法存在多种限制，
所以，使用 poll() 替换原来的监听方法。

其他

除了AliSQL外，你还可以看看云数据库 MySQL 版

码农公寓