工程应用中数据库性能优化经验小结

1、前言

    现阶段交付的算法产品，绝大多数涉及到数据库的使用。它承载的内容包括：用户权限管理、数据集信息、异步推论的结果、个性化配置等等。

    在OCR场景下，数据集体量通常较大（一个数据集几十万张图片），而数据库往往部署在客户共享数据库中（同时运行大量其他业务），甚至只能和算法镜像共享同一台服务器，因此在后台研发中尤其要关心数据库性能瓶颈。

    此篇文章分别从 sql执行过程、执行计划、索引数据结构、索引查询提速原理、聚焦索引、左前缀优化原则、自增主键索引 这些角度谈一谈我们对数据库优化的理解。

2.0 ORM场景下如何获得完整SQL语句

  1. 线上环境可以通过连接池进行慢SQL拦截，并发出告警通知

  2. 测试阶段，因为使用预编译语句或ORM框架，无法获取完整SQL时可以使用数据库日志方式获取

set global general_log=on;
show variables where Variable_name="general_log_file"；

2.1 SQL执行过程

    分析器：分析SQL，需要使用哪些表，使用哪些条件（知道要干什么）   

    优化器： 对各种执行过程做性能评估，挑选代价最小的执行过程，代价只是优化器认为的，不一定正确 （怎么样做最快）

    执行器：调用引擎接口，返回数据，引擎是插件式，类似编程时多态，在创建表时可以选择相应的存储 引擎

2.2 执行计划

    SQL前加explain关键词可以得到SQL的执行计划，根据执行计划可以判断执行过程是否符合预期

explain
SELECT
  db_dataset.uuid AS db_dataset_uuid,
  db_dataset.NAME AS db_dataset_name,
  db_dataset.updated_at AS db_dataset_updated_at,
  db_dataset.created_at AS db_dataset_created_at,
  db_dataset.volume_dir AS db_dataset_volume_dir,
  db_dataset.max_data_count AS db_dataset_max_data_count,
  db_dataset.description AS db_dataset_description
FROM
  db_dataset
  LEFT OUTER JOIN db_manifest ON db_manifest.dataset_id = db_dataset.id AND
  db_manifest.dataset_version = 'annotation_v0'
  LEFT OUTER JOIN db_ai_data ON db_manifest.id = db_ai_data.manifest_id AND
  db_ai_data.deleted = '0'
WHERE
  db_dataset.deleted = 0
GROUP BY
  db_dataset.id

    执行计划反馈列的解释：

   select_type详解：

    type详解：

查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：
system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery >
index_subquery > range > index > ALL

    system ：当表仅有一行记录时（系统表），数据量很少，往往不需要进行磁盘 IO，速度非常 快。

    const ：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量 （const）值。这类扫描效率极高，返回数据量少，速度非常快。

    eq_ref ：查询时命中主键 primary key 或者 unique key 索引， type 就是 eq_ref。

    ref ：区别于 eq_ref，ref 表示使用非唯一性索引，会找到很多个符合条件的行。

    ref_or_null ：这种连接类型类似于 ref，区别在于 MySQL 会额外搜索包含 NULL 值的行。

    index_merge ：使用了索引合并优化方法，一个查询使用了两个以上的索引。

EXPLAIN SELECT * FROM user_robot_relate WHERE id > 1 AND user_id = 2;

    unique_subquery ：替换下面的 IN 子查询，子查询返回不重复的集合。

value IN (SELECT primary_key FROM single_table WHERE some_expr)

    index_subquery ：区别于 unique_subquery，用于非唯一索引，可以返回重复值。

value IN (SELECT key_column FROM single_table WHERE some_expr)

    range ：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定 范围检索数据。在 where 语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。 从结果中看到只有对设置了索引的字段，做范围检索 type 才是 range。     

EXPLAIN SELECT * FROM user_robot_relate WHERE id BETWEEN 2 AND 3;

    index ：Index 与 ALL 其实都是读全表，区别在于 index 是遍历索引树读取，而 ALL 是从硬盘中 读取。

    ALL ：将遍历全表以找到匹配的行，性能最差。

    Extra ：不适合在其他列中显示的信息，Explain 中的很多额外的信息会在 Extra 字段显示。

    Using index：我们在相应的 select 操作中使用了覆盖索引，通俗一点讲就是查询的列被索引覆盖，使 用到覆盖索引查询速度会非常快，SQL 优化中理想的状态。

    Using where：查询时未找到可用的索引，进而通过 where 条件过滤获取所需数据，但要注意的是并不 是所有带 where 语句的查询都会显示 Using where。

    Using temporary：表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。

    Using filesort：表示无法利用索引完成的排序操作，也就是 ORDER BY 的字段没有索引，通常这样的 SQL 都是需要优化的。

    Using join buffer：在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区 来存储中间结果。

2.3 索引

    索引时帮助MySQL高效获取数据的排好序的数据结构

索引数据结构：

    二叉树

    红黑树

    HashTable

    B-Tree

一般不用二叉树的原因：有序数据将退化成链表，深度不可控，如下图所示

通常也不能用红黑树的原因：虽然压缩了深度，但深度还是不可控，海量数据查找复杂度极高

Hash表：仅支持IN查新，不支持RANGE查询。使用hash算法将内容进行hash处理 hash(aaaa) = 2 hash(bbbb) = 2 hash(cccc) = 4 

B+ 树：主流的索引结构

    查找过程：

    1. 读取根节点所有元素，因为是有序的，可以利用二分查找，高效查找到指定区间

    2. 根据指定区间文件地址找到二级节点，读取所有元素。

    3. 找到叶子节点中指定元素位置。 

2.4 索引查询提速原理

    以B+树索引为例，

    如果要查找数据项目29

    1、首先进入1号块，1号块数据加载如内存，发生一次I/O

    2、在内存中进行二分查找，发现29在17和35之前，于是锁定P2指针，将3号块数据加载到内存，又发生一次I/O

    3、同理在3号块中走P2指针锁定8号数据块，将8号数据块加载到内存，最后发生一次I/O

    4、遍历8号块的数据就能找到29号数据

    如果没有索引，最坏的情况是整个表格的数据块都需要加载到内存，然后遍历出结果，将产生大量的I/O开销和对整表数据的遍历。

2.5 聚焦索引

    聚焦索引尤其适合需要进行RANGE查找的列，这是因为他的叶子节点存放的是有序的数据行。在查询过程中可以根据WHERE的条件定位到两端叶子节点，然后将他们之间的整个链表结构取出。

2.6 左前缀优化原则

    工程应用中经常有一些核心表需要按照多种形式查询，如果为每一种查询方式都建一个索引会影响表插入和更新的性能。

    考虑到联合索引在创建时每个子列都是排好序的，比如数据表A上有一个联合缩影（a, b, c) , 那么查询where a = xxx ； where a = xxx and b = xxx都将命中缩影，因此可以利用这种特性按照业务需求设置少量联合索引覆盖多种查询需求。

    假设有表A, 有如下3种高频查询

select xx from A where a = xxx；
select xx from A where b = xxx；
select xx from A where a = xxx and b = xxx；

    最简单的办法是 分别给a b (a, b) 建索引，但这就过于啰嗦。按照左前缀原则，最合理的索引建法应该是 b 和 (a， b)。

2.7 自增主键索引

    1、InnoDB所有数据都是基于B+Tree存储的，如果没有主键mysql会在所有列中选择可能唯一的列用作索 引id，如果查找不到会默认增加rowid列。

    2、索引查找过程中会有大量数据比对的场景，如果使用uuid会逐位比对，效率会非常低，占用空间也会非 常大，占用过多ssd空间，存储费用增大。

    3、b+tree是有序树，自增索引数据可以一直向后插入性能高，如果使用非自增索引，可能导致插入过程中 带来树分裂及平衡问题，带来额外的性能损失。

3. 常规数据库优化顺序

    1、检查SQL，查看执行计划，是否命中索引？是否存在大量大表关联？查询的每个字段都是必须的？...

    2、加索引

    3、分区

    4、分表

    5、改表结构，减少查询种的关联，增加冗余字段

    6、加服务器，弹性主机加U加内存换SSD...