1、找到慢查询SQL

1.1、找到数据库实例

F6_gears(订单、评价)

https://hdm.console.aliyun.com/#/dbMonitor/MySQL

1.2、找到慢日志样本

img

1.3、查看慢日志样本

img

1.4、查看日志详情

img

2、现有情况

2.1、数据量

1
2
3
4
select count(*) from p_user_relation
//7155611
select count(*) from p_user
//35218757

2.2、执行计划

img

2.3、索引

img

2.4、需求

查询带有手机号的公众号用户。

3、优化

3.1、方案一

3.1.1、调换p_user与p_user_relation的位置

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT
       p.id,
       p.user_id,
       p.nick_name,
       p.gender,
       p.wx_app_id,
       p.user_type,
       p.wx_open_id,
       p.wx_union_id,
       p.user_address_province,
       p.user_address_city,
       p.img_source,
       p.delete_flag,
       p.creation_time,
       p.modified_time,
       p.subscribe_time,
       p.union_user_id,
       p.group_id,
        u.user_id,
       u.user_tel,
       u.group_id,
       p.img_source AS IMG_SRC
  FROM p_user u 
left JOIN p_user_relation p ON u.user_id= p.user_id and p.user_type= 3 and  p.group_id= '10545724654284683189'  
   WHERE u.group_id= '10545724654284683189'
   and u.user_tel is not null

执行计划

img

执行结果

img

总结

问题：会返回存在手机号但是没有公众号用户的脏数据。

优点：速度较快。1000行数据耗时25ms

可以在代码中过滤脏数据。

3.2、方案二

3.2.1、首先分页获取带有手机号的userId列表，1000行耗时31ms。

SQL

1
2
3
4
5
6
7
SELECT
     u.user_id,
     u.user_tel,
     u.group_id
FROM p_user u 
 WHERE u.group_id= '10545724654284683189'
 and u.user_tel is not null

执行计划

img

执行结果

img

3.2.2、在通过userId列表查询p_user_relation。

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT p.id,
       p.user_id,
       p.nick_name,
       p.gender,
       p.wx_app_id,
       p.user_type,
       p.wx_open_id,
       p.wx_union_id,
       p.user_address_province,
       p.user_address_city,
       p.img_source,
       p.delete_flag,
       p.creation_time,
       p.modified_time,
       p.subscribe_time,
       p.union_user_id,
       p.group_id,
       p.img_source AS IMG_SRC
  FROM p_user_relation p
 WHERE p.user_type= 3
   AND p.group_id= '10545724654284683189'
   and p.user_id in(541716, 541720, 541723, 541725, 541728, 541732, 541737, 541738, 541739, 541740, 541744, 541752, 541755, 541765, 541774, 541788, 541792, 541799, 541802, 541807, 541811, 541834, 541835, 541856, 541859, 541864, 541883, 541888, 541898, 541901, 541906, 541908, 541913, 541916, 541927, 541928, 541939, 541941, 541942, 541943, 541950, 541968, 541976, 541978, 541984, 541985, 541989, 541991, 541999, 542000, 542006, 542009, 542012, 542033, 542054, 542058, 542071, 542074, 542077, 542078, 542095, 542096, 542098, 542099, 542111, 542119, 542136, 542149, 542150, 542154, 542158, 542159, 542160, 542174, 542178, 542180, 542184, 542189, 542198, 542210, 542218, 542230, 542234, 542239, 542264, 542272, 542275, 542286, 542303, 542314, 542316, 542317, 542333, 542334, 542346, 542371, 542377, 542387, 542394, 542424, 542428, 542429, 542445, 542468, 542475, 542477, 542481, 542483, 542485, 542493, 542502, 542533, 542543, 542547, 542551, 542558, 542573, 542576, 542578, 542579, 542583, 542588, 542593, 542599, 542600, 542607, 542610, 542613, 542628, 542632, 542637, 542638, 542639, 542651, 542654, 542662, 542669, 542670, 542685, 542686, 542703, 542718, 542721, 542742, 542743, 542761, 542767, 542776, 542783, 542785, 542786, 542787, 542794, 542813, 542815, 542819, 542820, 542821, 542828, 542836, 542842, 542843, 542849, 542860, 542864, 542871, 542875, 542878, 542879, 542881, 542882, 542886, 542894, 542898, 542901, 542904, 542905, 542909, 542913, 542915, 542917, 542939, 542942, 542946, 542947, 542955, 542961, 542963, 542968, 542976, 542981, 542987, 542997, 543007, 543011, 543017, 543019, 543041, 543047, 543059, 543073, 543080, 543083, 543095, 543096, 543099, 543100, 543105, 543108, 543133, 543134, 543152, 543157, 543164, 543167, 543174, 543178, 543179, 543183, 543185, 543187, 543188, 543190, 543191, 543194, 543201, 543202, 543218, 543226, 543242, 543246, 543249, 543250, 543256, 543258, 543263, 543284, 543285, 543293, 543295, 543298, 543299, 543301, 543307, 543313, 543319, 543321, 543328, 543333, 543337, 543339, 543341, 543349, 543354, 543357, 543365, 543377, 543400, 543414, 543421, 543427, 543430, 543431, 543434, 543443, 543446, 543449, 543452, 543460, 543484, 543489, 543501, 543502, 543505, 543517, 543519, 543527, 543533, 543541, 543558, 543567, 543573, 543579, 543586, 543596, 543609, 543611, 543614, 543616, 543618, 543619, 543623, 543632, 543646, 543653, 543658, 543660, 543664, 543672, 543677, 543682, 543683, 543685, 543709, 543710, 543716, 543720, 543727, 543733, 543736, 543747, 543762, 543767, 543772, 543779, 543783, 543784, 543788, 543796, 543797, 543798, 543804, 543805, 543824, 543828, 543830, 543831, 543837, 543838, 543855, 543862, 543870, 543881, 543898, 543900, 543906, 543907, 543941, 543949, 543965, 543974, 543978, 543982, 543984, 543992, 543996, 544023, 544024, 544027, 544031, 544047, 544048, 544055, 544067, 544068, 544092, 544095, 544106, 544121, 544124, 544145, 544149, 544150, 544154, 544159, 544163, 544173, 544176, 544208, 544214, 544226, 544228, 544233, 544242, 544244, 544253, 544266, 544269, 544276, 544287, 544288, 544289, 544293, 544294, 544312, 544336, 544347, 544353, 544369, 544389, 544392, 544408, 544425, 544439, 544442, 544446, 544448, 544449, 544459, 544461, 544465, 544469, 544478, 544486, 544517, 544521, 544522, 544535, 544546, 544550, 544558, 544562, 544563, 544568, 544580, 544603, 544619, 544621, 544623, 544630, 544643, 544646, 544650, 544656, 544663, 544664, 544667, 544668, 544680, 544681, 544687, 544707, 544708, 544751, 544770, 544773, 544777, 544780, 544781, 544789, 544798, 544836, 544838, 544842, 544845, 544850, 544860, 544861, 544869, 544878, 544897, 544912, 544913, 544921, 544929, 544950, 544954, 544959, 544967, 544981, 544982, 545000, 545020, 545021, 545024, 545036, 545040, 545042, 545056, 545063, 545092, 545096, 545107, 545118, 545140, 545141, 545143, 545149, 545153, 545158, 545181, 545185, 545186, 545197, 545200, 545207, 545211, 545215, 545220, 545233, 545240, 545244, 545248, 545250, 545266, 545267, 545269, 545273, 545286, 545295, 545306, 545308, 545310, 545323, 545331, 545349, 545373, 545378, 545381, 545384, 545393, 545406, 545413, 545420, 545436, 545438, 545455, 545460, 545465, 545466, 545474, 545478, 545483, 545490, 545498, 545506, 545523, 545533, 545540, 545542, 545550, 545557, 545568, 545578, 545579, 545587, 545612, 545637, 545655, 545660, 545676, 545680, 545686, 545688, 545689, 545690, 545696, 545705, 545707, 545711, 545727, 545730, 545731, 545736, 545751, 545765, 545771, 545772, 545793, 545802, 545806, 545809, 545828, 545838, 545839, 545840, 545842, 545847, 545849, 545851, 545852, 545865, 545885, 545894, 545895, 545898, 545902, 545912, 545945, 545948, 545957, 545962, 545974, 545981, 545985, 545986, 545989, 545996, 546002, 546004, 546036, 546046, 546062, 546065, 546068, 546080, 546081, 546091, 546094, 546103, 546114, 546123, 546126, 546127, 546133, 546151, 546176, 546193, 546194, 546201, 546216, 546234, 546236, 546270, 546271, 546274, 546285, 546304, 546305, 546330, 546337, 546341, 546346, 546360, 546369, 546371, 546372, 546375, 546383, 546386, 546388, 546392, 546394, 546397, 546405, 546415, 546418, 546436, 546450, 546451, 546493, 546494, 546500, 546501, 546514, 546518, 546521, 546526, 546527, 546529, 546549, 546553, 546563, 546565, 546566, 546591, 546596, 546637, 546638, 546640, 546657, 546673, 546679, 546682, 546683, 546694, 546699, 546727, 546729, 546737, 546760, 546774, 546778, 546783, 546809, 546817, 546827, 546832, 546834, 546838, 546839, 546859, 546864, 546876, 546879, 546885, 546900, 546902, 546931, 546935, 546941, 546945, 546953, 546963, 546969, 546985, 546993, 547000, 547019, 547023, 547025, 547032, 547048, 547055, 547056, 547061, 547086, 547099, 547140, 547151, 547155, 547161, 547203, 547221, 547238, 547274, 547280, 547281, 547292, 547303, 547351, 547383, 547385, 547397, 547399, 547402, 547426, 547429, 547435, 547457, 547479, 547500, 547510, 547518, 547522, 547530, 547541, 547550, 547554, 547555, 547562, 547571, 547573, 547577, 547589, 547604, 547636, 547639, 547643, 547664, 547685, 547686, 547693, 547700, 547710, 547711, 547727, 547731, 547764, 547766, 547779, 547782, 547790, 547796, 547812, 547813, 547820, 547830, 547837, 547854, 547891, 547901, 547907, 547913, 547944, 547952, 547956, 547960, 547969, 547985, 547987, 547991, 547993, 547994, 548004, 548012, 548015, 548016, 548021, 548025, 548030, 548033, 548042, 548045, 548049, 548058, 548063, 548067, 548073, 548074, 548076, 548078, 548082, 548086, 548092, 548093, 548103, 548107, 548118, 548124, 548137, 548145, 548146, 548162, 548165, 548166, 548175, 548186, 548198, 548201, 548204, 548205, 548208, 548212, 548218, 548228, 548233, 548236, 548241, 548263, 548271, 548273, 548274, 548287, 548289, 548303, 548307, 548308, 548312, 548319, 548323, 548345, 548349, 548351, 548357, 548366, 548375, 548378, 548390, 548419, 548436, 548438, 548440, 548448, 548472, 548483, 548489, 548515, 548528, 548529, 548553, 548561, 548563, 548588, 548603, 548608, 548610, 548614, 548622, 548625, 548637, 548638, 548640, 548644, 548648, 548653, 548658, 548661, 548665, 548689, 548691, 548704, 548706, 548711, 548729, 548730, 548743, 548750, 548754, 548757, 548770, 548771, 548775, 548779, 548792, 548796, 548802, 548841, 548847, 548852, 548854, 548855, 548858, 548865, 548881, 548887, 548888, 548893, 548895, 548897, 548911, 548918, 548919, 548924, 548929, 548943, 548955, 548978, 548980, 548988, 548990, 548993, 549004, 549011, 549025, 549026, 549037, 549039, 549059, 549071, 549072, 549077, 549102, 549103, 549108, 549109, 549113, 549117, 549132, 549147, 549149, 549156, 549164, 549170, 549185, 549186, 549188, 549189, 549190, 549204, 549207, 549229, 549234, 549252, 549266, 549267, 549270, 549283, 549296, 549299, 549311, 549334, 549352, 549374, 549392, 549423, 549435, 549454, 549469, 549473, 549475, 549479, 549480, 549500, 549501, 549503, 549507, 549518, 549520, 549538, 549542, 549546, 549547, 549548, 549553, 549563, 549572, 549574, 549576, 549579, 549628, 549629, 549634, 549644, 549666, 549673, 549674, 549678, 549680, 549681)

执行计划

img

执行结果

img

总结

问题：需要写两条sql，通过1000条userId只能查出少量的带有手机号的公众号用户。

优点：时间花费较少，31+15=46ms。

3.3、方案三

3.3.1、分页查询p_user_relation，获取有手机号的微信公众号粉丝的user_id

1
2
3
4
SELECT p.user_id
FROM p_user_relation p 
WHERE p.group_id = 10546172455174347299 and p.user_type = 3
LIMIT 71556, 100

返回的数据包含null值，耗时188ms。

执行计划

img

3.3.2、根据user_id批量获取手机号

1
2
3
SELECT u.USER_TEL
  FROM p_user u
 where u.user_id in(2740257, 2741582, 2741820, 2743365, 2715515, 2749578, 3648190, 2800499, 2790297, 2831369, 2787167, 2837895, 2759462, 2763681, 2788731, 2786291, 18007938, 2766690, 2788176, 2859280, 2768339)

返回21行，耗时5ms。

执行计划：

img

3.4、方案四

3.4.1、分页查询p_user_relation的微信粉丝id

1
2
3
4
SELECT p.id
FROM p_user_relation p 
WHERE p.group_id = 10546172455174347299 and p.user_type = 3
LIMIT 71556, 100

100行耗时21ms

执行计划：

img

3.4.2、关联p_user与p_user_relation通过p_user_relation的id列表过滤

1
2
3
4
5
6
7
SELECT u.USER_TEL
  FROM p_user u
  LEFT JOIN p_user_relation p ON u.user_id= p.user_id
   AND p.user_type= 3
   AND p.group_id= u.group_id
 WHERE p.id in(1341144, 1341591, 1342072, 1342369, 1343353, 1343755, 1343965, 1345000, 1345913, 1346139, 1346922, 1347059, 1347086, 1347927, 1348161, 1348454, 1349185, 1349490, 1350657, 1350729, 1350802, 1350803, 1350804, 1350805, 1350809, 1351295, 1351320, 1351346, 1351354, 1351387, 1351404, 1351405, 1351421, 1351445, 1351515, 1351532, 1351610, 1351719, 1351731, 1351837, 1351908, 1352021, 1352080, 1352082, 1352120, 1352172, 1352329, 1352378, 1352389, 1352433, 1352476, 1352568, 1352977, 1353024, 1353091, 1353172, 1353200, 1353728, 1353810, 1353875, 1353885, 1354456, 1354466, 1354611, 1354693, 1354725, 1354727, 1355091, 1355138, 1355294, 1355329, 1355615, 1355890, 1356554, 1356619, 1356652, 1356663, 1356851, 1356857, 1356861, 1356891, 1357110, 1357115, 1357122, 1357184, 1357313, 1357344, 1357475, 1357505, 1357641, 1357761, 1357767, 1357776, 1358005, 1358018, 1358138, 1358148, 1358367, 1358449, 1358463)
   AND u.user_tel IS NOT NULL;

过滤出21行，耗时15ms

执行计划：

img

4、20210629优化上线后执行情况

4.1、执行情况

方案一，20210629优化上线后，SQL执行情况:

img

img慢查询依然存在。

4.2、分析

明显区别：

  • 扫描行由原来的平均592k降到395k;
  • 执行次数由原来的188升到4638；
  • 平均执行时间由原来的1.043s升到1.399；

1）执行次数变多是因为sql的主表为p_user,数量比以前p_user_relation为主表时多；

img

2）执行时间变长是因为分页过深时，执行时间拉长。

img

从慢查询日志中可以看到从79400页开始到268100页结束，执行时间持续增加：

img

img

5、20210701优化上线后执行情况

5.1、执行情况

方案四：20210701优化上线后，慢sql解决。

img

5.2、分析

问题：为什么深度分页场景下，只查询主键id会比查询user_id快？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 例子1：
SELECT p.id
FROM p_user_relation p 
WHERE p.group_id = 10546172455174347299 and p.user_type = 3
LIMIT 71556, 100
--  首次查询耗时 耗时50ms
--  多次查询平均耗时 30ms


-- 例子2：
SELECT p.user_id
FROM p_user_relation p 
WHERE p.group_id = 10546172455174347299 and p.user_type = 3
LIMIT 71556, 100
-- 首次查询耗时 5496ms
-- 多次品骏耗时150ms

例子1执行计划：

img

例子2执行计划：

img

可以看到执行计划中的”Extra”不一样！

例子1中，Extra值为：Using index，使用了覆盖索引，所以速度会更快。

这里涉及到三个名词：主键、索引、覆盖索引。

索引知识点可参考：图解MySQL聚簇索引和非聚簇索（Innodb）

覆盖索引知识点：

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

理解方式一：

  • 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；
  • 当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

理解方式二：

  • 是非聚集复合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段，也即，索引包含了查询正在查找的数据）

6、总结

  • 平时应多关注DAS控制台里的慢查询日志 DAS控制台
  • 根据业务场景与sql执行频次分析，合理的创建索引、控制查询内容（让查询的内容尽量走到覆盖索引）。
  • 发生慢查询时，要多尝试，多问DBA
  • 平时多了解mysql相关知识，多积累。