7个工程应用中数据库性能优化经验分享

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

本文分享自华为云社区《工程应用中数据库性能优化经验小结》，作者： 叶工 。
1、前言

现阶段交付的算法产品，绝大多数涉及到数据库的使用。它承载的内容包括：用户权限管理、数据集信息、异步推论的结果、个性化配置等等。
在OCR场景下，数据集体量通常较大（一个数据集几十万张图片），而数据库往往部署在客户共享数据库中（同时运行大量其他业务），甚至只能和算法镜像共享同一台服务器，因此在后台研发中尤其要关心数据库性能瓶颈。
此篇文章分别从 sql执行过程、执行计划、索引数据结构、索引查询提速原理、聚焦索引、左前缀优化原则、自增主键索引这些角度谈一谈我们对数据库优化的理解。
2、ORM场景下如何获得完整SQL语句

1. 线上环境可以通过连接池进行慢SQL拦截，并发出告警通知
2. 测试阶段，因为使用预编译语句或ORM框架，无法获取完整SQL时可以使用数据库日志方式获取

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

复制代码

2.1 SQL执行过程

分析器：分析SQL，需要使用哪些表，使用哪些条件（知道要干什么）
优化器： 对各种执行过程做性能评估，挑选代价最小的执行过程，代价只是优化器认为的，不一定正确 （怎么样做最快）
执行器：调用引擎接口，返回数据，引擎是插件式，类似编程时多态，在创建表时可以选择相应的存储 引擎
2.2 执行计划

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

1. explain
2. SELECT
3. db_dataset.uuid AS db_dataset_uuid,
4.   db_dataset.NAME AS db_dataset_name,
5. db_dataset.updated_at AS db_dataset_updated_at,
6. db_dataset.created_at AS db_dataset_created_at,
7. db_dataset.volume_dir AS db_dataset_volume_dir,
8. db_dataset.max_data_count AS db_dataset_max_data_count,
9. db_dataset.description AS db_dataset_description
10. FROM
11. db_dataset
12.   LEFT OUTER JOIN db_manifest ON db_manifest.dataset_id = db_dataset.id AND
13. db_manifest.dataset_version = 'annotation_v0'
14.   LEFT OUTER JOIN db_ai_data ON db_manifest.id = db_ai_data.manifest_id AND
15. db_ai_data.deleted = '0'
16. WHERE
17. db_dataset.deleted = 0
18. GROUP BY
19.   db_dataset.id

复制代码

执行计划反馈列的解释：
select_type详解：
type详解：

1. 查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：
2. system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery >
3. 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 ：使用了索引合并优化方法，一个查询使用了两个以上的索引。

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

复制代码

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

1. value IN (SELECT primary_key FROM single_table WHERE some_expr)

复制代码

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

1. value IN (SELECT key_column FROM single_table WHERE some_expr)

复制代码

range ：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定 范围检索数据。在 where 语句中使用 bettween...and、、