后端接口性能优化分析-数据库优化（中）

后端接口性能优化分析-数据库优化（上）:https://developer.aliyun.com/article/1413677

索引列上有计算

当我们用主键做条件时，走索引了：

而当id列上面有计算，比如：

可以看到走了全表扫描

索引列上有函数

有时候我们在某条sql语句的查询条件中，需要使用函数，比如：截取某个字段的长度：

有没有发现，在使用该函数之后，该sql语句竟然走了全表扫描，索引失效了

字段类型不同

在sql语句中因为字段类型不同，而导致索引失效的问题，很容易遇到，可能是我们日常工作中最容易忽略的问题。

到底怎么回事呢？

我们看下表里的start_org_code字段，它是varchar字符类型的

在sql语句查询数据时，查询条件我们可以写成这样：

从上图中看到，该字段走了索引

但如果在写sql时，不小心把引号丢了：

咦，该sql语句居然变成全表扫描了，为什么索引失效了？

答：因为这个索引列是varchar类型，而传参的类型是int，mysql在比较两种不同类型的字段时会尝试把这两个转化为同一种类型，再进行比较。这样就可以理解为在字段上加了函数，根据上面分析，索引列加了函数会索引失效。

比较有意思的是，如果int类型的id字段，在查询时加了引号条件，却还可以走索引：

从图中看出该sql语句确实走了索引。int类型的参数，不管在查询时加没加引号，都能走索引。

答：MySQL发现如果是int类型字段作为查询条件时，它会自动将该字段的传参进行隐式转换，把字符串转换成int类型。

MySQL会把上面列子中的字符串12348，转换成数字12348，所以仍然能走索引。

事实上，索引列上对字段做任何操作都会导致索引失效，因为mysq认为任何计算或者函数都会改变索引的实际效果，如果继续使用索引可能会造成结果不准确。

like左边包含%

答：其实很好理解，索引就像字典中的目录。一般目录是按字母或者拼音从小到大，从左到右排序，是有顺序的。

我们在查目录时，通常会先从左边第一个字母进行匹对，如果相同，再匹对左边第二个字母，如果再相同匹对其他的字母，以此类推。

通过这种方式我们能快速锁定一个具体的目录，或者缩小目录的范围。

但如果你硬要跟目录的设计反着来，先从字典目录右边匹配第一个字母，这画面你可以自行脑补一下，你眼中可能只剩下绝望了，哈哈

列对比

假如我们现在有这样一个需求：过滤出表中某两列值相同的记录。例如：

索引失效了吧？惊不惊喜？

答：表里create_time和update_time都建了索引，单独查询某一字段时都会走索引。但如果把两个单独建了索引的列，用来做列对比时索引会失效。这其实和在索引列上加函数一个原理，MySQL认为索引无法满足需求。

or和in和exist和not in和not exist

该部分主要参考来自 京东云开发者团队

这几个有异曲同工之处，就放一起说了。这里就不像上面几种情况100%不走索引了，而是有时候会走索引，有时候不走索引。到底走不走？成本计算说了算。

成本计算

查询优化器是 MySQL 的核心子系统之一，成本计算又是查询优化器的核心逻辑。

全表扫描成本作为参照物，用于和表的其它访问方式的成本做对比。任何一种访问方式，只要成本超过了全表扫描成本，就不会被使用。

基于全表扫描成本的重要地位，要讲清楚 MySQL 的成本计算逻辑，从全表扫描成本计算开始是个不错的选择。

全表扫描成本计算

我们先来看一下Mysql源码里成本计算的定义：

class Cost_estimate {
 private:
  // cost of I/O operations
  double io_cost;
  // cost of CPU operations
  double cpu_cost;
  // cost of remote operations
  double import_cost;
  // memory used (bytes)
  double mem_cost;
  ......
}

从上面代码可以看到，MySQL 成本计算模型定义了四种成本：

• IO 成本：从磁盘或内存读取数据页的成本。
• CPU 成本：访问记录需要消耗的 CPU 成本。
• 导入成本：这一项一直没被使用，先忽略。
• 内存成本：这一项指的是占用内存字节数，计算 MRR（Multi Range Read）方式读取数据的成本时才会用到，也先忽略。

全表扫描的成本就只剩 IO 成本、CPU 成本这两项了

计算成本

我们先从整体计算公式开始，然后逐步拆解。

全表扫描成本 =io_cost+ 1.1 +cpu_cost+ 1

io_cost 后面的1.1是硬编码直接加到 IO 成本上的；cpu_cost 后面的1也是硬编码的，直接加到 CPU 成本上。代码里长这样：

int test_quick_select(...) {
  ......
  double scan_time =
    cost_model->row_evaluate_cost(static_cast<double>(records)) 
    + 1 /* cpu_cost 后面的 + 1 */; 
  Cost_estimate cost_est = table->file->table_scan_cost();
  // io_cost 后面的 + 1.1
  cost_est.add_io(1.1);
  ......
}

关于这两个硬编码的值，代码里没有注释为什么要加，不过它们是个固定值，不影响我们理解成本计算逻辑，先忽略它们。

io_cost =cluster_page_count*avg_single_page_cost。

cluster_page_count 是主键索引数据页数量，从表的统计信息中得到

avg_single_page_cost 是读取一个数据页的平均成本，通过计算得到，公式如下：

avg_single_page_cost =pages_in_memory_percent * 0.25 +pages_on_disk_percent * 1.0

pages_in_memory_percent 是主键索引已经加载到 Buffer Pool中的叶结点占所有叶结点的比例，用小数表示（取值范围 0.0 ~ 1.0），例如：80% 表示为 0.8。

pages_on_disk_percent 是主键索引在磁盘文件中的叶结点占所有叶结点的比例，通过1 - pages_in_memory_percent计算得到。

0.25是成本常数 memory_block_read_cost的默认值，表示从 Buffer Pool 中的一个数据页读取数据的成本。

1.0是成本常数io_block_read_cost的默认值，表示把磁盘文件中的一个数据页加载到 Buffer Pool 的成本，加上从 Buffer Pool 中的该数据页读取数据的成本。

cpu_cost = n_rows * 0.1。

n_rows 是表中记录的数量，从表的统计信息中得到，在统计信息小节会介绍。

0.1是成本常数row_evaluate_cost的默认值，表示访问一条记录的 CPU 成本。

有了上面这些公式，我们通过一个具体例子走一遍全表扫描成本计算的过程。

假设一个表有 15228 条记录，主键索引数据页的数量为 739，主键索引数据页已经全部加载到 Buffer Pool（pages_in_memory_percent = 1.0），下面我们开始计算过程：

• pages_on_disk_percent = 1 -pages_in_memory_percent(1.0) =0.0。
• avg_single_page_cost =pages_in_memory_percent(1.0) *0.25+pages_on_disk_percent(0.0) *1.0=0.25。
• io_cost =cluster_page_count(739) *avg_single_page_cost(0.25) =184.75。
• cpu_cost =n_rows(15228) * 0.1 =1522.8。
• 全表扫描成本 =io_cost(184.75) +1.1+cpu_cost(1522.8) +1=1709.55

这样，其实在涉及到有索引的查询，索引失效，很有可能是因为成本超过了全表扫描成本，就不会被使用。

索引设计不合理

我们的索引不是越多越好，需要合理设计。比如：

• 删除冗余和重复索引。
  
• 索引一般不能超过5个
  
• 索引不适合建在有大量重复数据的字段上、如性别字段
  
• 适当使用覆盖索引
  
• 如果需要使用force index强制走某个索引，那就需要思考你的索引设计是否真的合理了

索引总结

查询和更新SQL必须命中索引。查询SQL如果没命中索引，在访问量较大时，会出现大量慢查询，严重时会导致整个MySQL集群雪崩，影响到其他表、其他数据库。所以一定要严格审查SQL是否命中索引。可以使用explain命令查看索引使用情况。

在SQL更新场景，MySQL会在索引上加锁，如果没有命中索引会对全表加锁，全表的更新操作都会被阻塞住。所以更新SQL更要确保命中索引。

解释： 在SQL更新场景中，MySQL会在索引上加锁，这是为了保证数据的一致性和并发控制。当执行更新操作时，MySQL会先检查是否存在适用的索引来定位需要更新的数据行。如果存在适用的索引，MySQL会在该索引上加锁，只锁定需要更新的数据行，其他数据行不会被阻塞。这样可以提高并发性能，减少锁冲突。

然而，如果更新操作没有命中索引，即没有适用的索引可以定位需要更新的数据行，MySQL会执行全表扫描来查找需要更新的数据行。在这种情况下，MySQL会对整个表加锁，这会导致全表的更新操作被阻塞住，其他查询和更新操作也会受到影响。

因此，为了避免这种情况的发生，需要严格审查SQL是否命中索引。可以使用"explain"命令来查看SQL的执行计划，从而判断是否有使用索引。这样可以及早发现潜在的问题，并及时采取措施进行优化和调整。

除此之外，最好索引字段能够完全覆盖查询需要的字段。MySQL索引分主键索引和普通索引。普通索引命中后，往往需要再查询主键索引获取记录的全部字段。如果索引字段完全包含查询的字段，即索引覆盖查询，就无需再回查主键索引，可以有效提高查询性能。

SQL优化

SQL优化是一个大的章节，本质上是因为抛开索引外，就sql语句本身 就有很多优化的点。

join优化

一般来说，不建议使用子查询，可以把子查询改成join来优化。

是因为子查询导致无法评估子查询的成本，导致选择了不够高效的执行计划，这可能导致查询性能下降，特别是当子查询返回的结果集较大或者较复杂时。其次，在某些情况下，数据库优化器可能无法正确地利用索引来优化子查询的执行计划。这可能会导致查询性能下降，特别是当子查询涉及到复杂的条件或者无法直接使用索引的情况下。

当你需要从两个相关的表中检索数据时，可以使用 JOIN 或者子查询。以下是一个示例，分别展示了使用 JOIN 和子查询来实现相同的功能：

假设我们有两个表：orders（订单信息）和 customers（顾客信息），它们之间通过 customer_id 进行关联。

使用 JOIN 的示例：

sqlCopy CodeSELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

使用子查询的示例：

sqlCopy CodeSELECT order_id, 
    (SELECT customer_name FROM customers WHERE customer_id = orders.customer_id) AS customer_name
FROM orders;

在这个示例中，两种方法都可以用来获取订单信息以及对应的顾客名称。然而，通常情况下，使用 JOIN 会更有效率，因为数据库通常能够更好地优化和处理 JOIN 操作，而且语句也更易读和维护。

当然，并不是所有情况都适合使用 JOIN，有时候子查询是更合适的选择，特别是在需要处理复杂逻辑或者特定场景下。但总体来说，尽量优先考虑使用 JOIN 来进行表关联操作。

数据库有个规范约定就是：尽量不要有超过3个以上的表连接。为什么要这么建议呢? 我们来聊聊，join哪些方面可能导致慢查询吧。

MySQL中，join的执行算法，分别是：Index Nested-Loop Join和Block Nested-Loop Join。

• Index Nested-Loop Join：这个join算法，跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引。
• Block Nested-Loop Join：这种join算法，被驱动表上没有可用的索引,它会先把驱动表的数据读入线程内存join_buffer中，再扫描被驱动表，把被驱动表的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

join过多的问题：

一方面，过多的表连接，会大大增加SQL复杂度。另外一方面，如果可以使用被驱动表的索引那还好，并且使用小表来做驱动表，查询效率更佳。

如果被驱动表没有可用的索引，join是在join_buffer内存做的，如果匹配的数据量比较小或者join_buffer设置的比较大，速度也不会太慢。但是，如果join的数据量比较大时，mysql会采用在硬盘上创建临时表的方式进行多张表的关联匹配，这种显然效率就极低，本来磁盘的 IO 就不快，还要关联。

一般情况下，如果业务需要的话，关联2~3个表是可以接受的，但是关联的字段需要加索引哈。如果需要关联更多的表，建议从代码层面进行拆分，在业务层先查询一张表的数据，然后以关联字段作为条件查询关联表形成map，然后在业务层进行数据的拼装。

in元素过多

如果使用了in，即使后面的条件加了索引，还是要注意in后面的元素不要过多哈。in元素一般建议不要超过500个，如果超过了，建议分组，每次500一组进行。

反例：

select user_id,name from user where user_id in (1,2,3...1000000); 

如果我们对in的条件不做任何限制的话，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。

主要会造成以下三个问题：

1. 性能问题：当 IN 子句中包含大量元素时，数据库引擎需要逐个匹配每个元素，这可能会导致性能下降。数据库需要对每个元素进行比较，这将增加查询的执行时间。特别是在大型数据集上，这种性能下降可能会非常显著。
2. 内存消耗：IN 子句中包含的元素越多，数据库系统需要分配更多的内存来处理这些元素。如果内存不足，可能会导致查询失败或者影响其他正在执行的查询。
3. 查询优化问题：对于包含大量元素的 IN 子句，数据库优化器可能无法有效地选择最佳的查询执行计划。这可能导致查询性能不佳，甚至可能选择了低效的查询方式。

尤其有时候，我们是用的子查询，in后面的子查询，你都不知道数量有多少那种，更容易采坑（所以我把in元素过多抽出来作为一个小节）

select * from user where user_id in (select author_id from artilce where type = 1);

正例是，分批进行，每批500个：

select user_id,name from user where user_id in (1,2,3...500);

如果传参的ids太多，还可以做个参数校验什么的

if (userIds.size() > 500) {
    throw new Exception("单次查询的用户Id不能超过200");
}

order by

order by就一定会导致慢查询吗？不是这样的哈，因为order by平时用得多，并且数据量一上来，还是走文件排序的话，很容易有慢SQL的。

文件排序

我们平时经常需要用到order by ，主要就是用来给某些字段排序的。比如以下SQL:

select name,age,city from staff where city = '深圳' order by age limit 10;

它表示的意思就是：查询前10个，来自深圳员工的姓名、年龄、城市，并且按照年龄小到大排序。

查看explain执行计划的时候，可以看到Extra这一列，有一个Using filesort，它表示用到文件排序。

order by文件排序效率为什么较低

order by用到文件排序时，为什么查询效率会相对低呢？

order by排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data(系统参数)和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data (系统参数)这个值，就会走rowid排序，相反，则走全字段排序。

max_length_for_sort_data参数的值只在运行时确定，并且不允许手动更改。它的主要作用是限制排序操作所需的临时存储空间大小，以避免对系统资源的过度占用。在进行大规模排序操作时，如果超出了max_length_for_sort_data的限制，可能会导致排序失败或者性能下降。

rowid排序

rowid排序，一般需要回表去找满足条件的数据，所以效率会慢一点。以下这个SQL，使用rowid排序，执行过程是这样：

select name,age,city from staff where city = '深圳' order by age limit 10;

1. MySQL 为对应的线程初始化sort_buffer，放入需要排序的age字段，以及主键id；
2. 从索引树idx_city， 找到第一个满足 city='深圳’条件的主键id，也就是图中的id=9；
3. 到主键id索引树拿到id=9的这一行数据， 取age和主键id的值，存到sort_buffer；
4. 从索引树idx_city拿到下一个记录的主键id，即图中的id=13；
5. 重复步骤 3、4 直到city的值不等于深圳为止；
6. 前面5步已经查找到了所有city为深圳的数据，在sort_buffer中，将所有数据根据age进行排序；
7. 遍历排序结果，取前10行，并按照id的值回到原表中，取出city、name 和 age三个字段返回给客户端。

全字段排序

同样的SQL，如果是走全字段排序是这样的：

select name,age,city from staff where city = '深圳' order by age limit 10;

1. MySQL 为对应的线程初始化sort_buffer，放入需要查询的name、age、city字段；
2. 从索引树idx_city， 找到第一个满足 city='深圳’条件的主键 id，也就是图中的id=9；
3. 到主键id索引树拿到id=9的这一行数据， 取name、age、city三个字段的值，存到sort_buffer；
4. 从索引树idx_city 拿到下一个记录的主键id，即图中的id=13；
5. 重复步骤 3、4 直到city的值不等于深圳为止；
6. 前面5步已经查找到了所有city为深圳的数据，在sort_buffer中，将所有数据根据age进行排序；
7. 按照排序结果取前10行返回给客户端。

sort_buffer的大小是由一个参数控制的：sort_buffer_size。

• 如果要排序的数据小于sort_buffer_size，排序在sort_buffer内存中完成
• 如果要排序的数据大于sort_buffer_size，则借助磁盘文件来进行排序。

借助磁盘文件排序的话，效率就更慢一点。因为先把数据放入sort_buffer，当快要满时。会排一下序，然后把sort_buffer中的数据，放到临时磁盘文件，等到所有满足条件数据都查完排完，再用归并算法把磁盘的临时排好序的小文件，合并成一个有序的大文件。

如何优化order by的文件排序

order by使用文件排序，效率会低一点。我们怎么优化呢？

• 因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不会再用到文件排序啦。而索引数据本身是有序的，我们通过建立索引来优化order by语句。
• 我们还可以通过调整max_length_for_sort_data、sort_buffer_size等参数优化；

拿不到锁

有时候，我们查询一条很简单的SQL，但是却等待很长的时间，不见结果返回。一般这种时候就是表被锁住了，或者要查询的某一行或者几行被锁住了。我们只能慢慢等待锁被释放。

举一个生活的例子哈，你和别人合租了一间房子，这个房子只有一个卫生间的话。假设某一时刻，你们都想去卫生间，但是对方比你早了一点点。那么此时你只能等对方出来后才能进去。

delete + in子查询不走索引

一个生产慢SQL问题，当delete遇到in子查询时，即使有索引，也是不走索引的。而对应的select + in子查询，却可以走索引。

delete from account where name in (select name from old_account);

查看执行计划，发现不走索引：

但是如果把delete换成select，就会走索引。如下：

为什么select + in子查询会走索引，delete + in子查询却不会走索引呢？

我们执行以下SQL看看：

explain select * from account where name in (select name from old_account);
show WARNINGS; //可以查看优化后,最终执行的sql

结果如下：

select `test2`.`account`.`id` AS `id`,`test2`.`account`.`name` AS `name`,`test2`.`account`.`balance` AS `balance`,`test2`.`account`.`create_time` AS `create_time`,`test2`.`account`.`update_time` AS `update_time` from `test2`.`account` 
semi join (`test2`.`old_account`)
where (`test2`.`account`.`name` = `test2`.`old_account`.`name`)

可以发现，实际执行的时候，MySQL对select in子查询做了优化，把子查询改成join的方式，所以可以走索引。但是很遗憾，对于delete in子查询，MySQL却没有对它做这个优化。

group by

group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。日常开发中，我们使用得比较频繁。如果不注意，很容易产生慢SQL。

group by的执行流程

explain select city ,count(*) as num from staff group by city;

• Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表
• Extra 这个字段的Using filesort表示使用了文件排序

group by是怎么使用到临时表和排序了呢？我们来看下这个SQL的执行流程

select city ,count(*) as num from staff group by city;

创建内存临时表，表里有两个字段city和num；

全表扫描staff的记录，依次取出city = 'X'的记录。

• 判断临时表中是否有为 city='X'的行，没有就插入一个记录 (X,1);
• 如果临时表中有city='X'的行，就将X这一行的num值加 1；

遍历完成后，再根据字段city做排序，得到结果集返回给客户端。这个流程的执行图如下：

临时表的排序是怎样的呢？

就是把需要排序的字段，放到sort buffer，排完就返回。在这里注意一点哈，排序分全字段排序和rowid排序

• 如果是全字段排序，需要查询返回的字段，都放入sort buffer，根据排序字段排完，直接返回
• 如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。

group by可能会慢在哪里？

group by使用不当，很容易就会产生慢SQL 问题。因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。

• 如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是tmp_table_size），会把内存临时表转成磁盘临时表。
• 如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。

如何优化group by呢？

从哪些方向去优化呢？

• 方向1：既然它默认会排序，我们不给它排是不是就行啦。
• 方向2：既然临时表是影响group by性能的X因素，我们是不是可以不用临时表？

可以有这些优化方案：

group by 后面的字段加索引

如何保证group by后面的字段数值一开始就是有序的呢？当然就是加索引啦。

order by null 不用排序

select city ,count(*) as num from staff group by city order by null

尽量只使用内存临时表

如果group by需要统计的数据不多，我们可以尽量只使用内存临时表；因为如果group by 的过程因为数据放不下，导致用到磁盘临时表的话，是比较耗时的。因此可以适当调大tmp_table_size参数，来避免用到磁盘临时表。

使用SQL_BIG_RESULT优化

如果数据量实在太大怎么办呢？总不能无限调大tmp_table_size吧？但也不能眼睁睁看着数据先放到内存临时表，随着数据插入发现到达上限，再转成磁盘临时表吧？这样就有点不智能啦。

因此，如果预估数据量比较大，我们使用SQL_BIG_RESULT 这个提示直接用磁盘临时表。MySQl优化器发现，磁盘临时表是B+树存储，存储效率不如数组来得高。因此会直接用数组来存

select SQL_BIG_RESULT city ,count(*) as num from staff group by city;

执行计划的Extra字段可以看到，执行没有再使用临时表，而是只有排序

select

查询SQL尽量不要使用select *，而是select具体字段。只取需要的字段，节省资源，减少网络开销

select * 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询

避免使用or

假设现在需要查询userid为1或者年龄为18岁的用户，很容易有以下SQL

select * from user where userid = 1 or age = 18

主要是因为使用or可能会使索引失效，从而全表扫描

对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并

如果它一开始就走全表扫描，直接一遍扫描就完事。mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理。

后端接口性能优化分析-数据库优化（下）:https://developer.aliyun.com/article/1413684