• 2022-08-09 mysql/stonedb-慢SQL-Q16分析

    摘要：

    分析慢SQL-Q16

    索引条件下推：

    ICP(Index Condition Pushdown) - 索引条件下推

    什么是ICP？

    不如换个问法，ICP索引条件下推的作用是什么？ 一句话总结：索引条件下推ICP就是尽可量利用二级索引筛除不符合where条件的记录，如此一来减少需要回表继续判断的次数

    With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine.The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

    MySQL官方示例 - 初次体验ICP

    示例如下，这个例子来自MySQL官方文档： Suppose：假设这个表有联合索引INDEX(zipcode, lastname, firstname)

    1. SELECT * FROM people
    2.   WHERE zipcode='95054'
    3.   AND lastname LIKE '%etrunia%'
    4.   AND address LIKE '%Main Street%';
    • 不用ICP，只使用最左匹配原则。那么只能使用联合索引的zipcode，回表记录不能有效去除。
    • 使用ICP，除了匹配zipcode的条件之外，额外匹配联合索引的lastname，看其是否符合where条件中的'%etrunia%'，然后进行回表。如此一来，使用联合索引就可以尽可量排除不符合where条件的记录。这就是ICP优化的真谛。 With Index Condition Pushdown, MySQL checks the lastname LIKE '%etrunia%' part before reading the full table row. This avoids reading full rows corresponding to index tuples that match the zipcode condition but not the lastname condition.

    为什么应该使用索引下推？
    首先联合索引idx_query(user_id, event_id, follower_id)，其次搜索条件为 user_id in (...) and follower_id = 26407612。完全可以在联合索引idx_query上使用ICP，通过匹配user_id和follower_id两者进行回表，符合条件的记录数相比只使用user_id进行过滤然后回表的记录数一定会少很多。

    但是根据explain的结果，Extra只有Using Where && key_len = 4（说明联合索引三个字段只用到了第一个user_id）该语句只是根据user_id进行回表，因为每个用户user_id有非常多的follower_id，回表的记录会非常多，并且这么多记录可能分布在聚促索引的多个页面，这就是随机I/O啊。一下子就将该查询语句变成慢查询。

    为什么没有使用？
    按照对ICP的理解，它就是尽量利用二级索引减少回表的记录数。在这个语句中，明明可以使用ICP，为什么没有使用呢？讲道理，它就应该使用ICP

    DDL：

    1. select
    2. 	p_brand,
    3. 	p_type,
    4. 	p_size,
    5. 	count(distinct ps_suppkey) as supplier_cnt
    6. from
    7. 	partsupp,
    8. 	part
    9. where
    10. 	p_partkey = ps_partkey
    11. 	and p_brand <> 'Brand#45'
    12. 	and p_type not like 'MEDIUM POLISHED%'
    13. 	and p_size in (49,
    14. 	14,
    15. 	23,
    16. 	45,
    17. 	19,
    18. 	3,
    19. 	36,
    20. 	9)
    21. 	and ps_suppkey not in (
    22. 	select
    23. 		s_suppkey
    24. 	from
    25. 		supplier
    26. 	where
    27. 		s_comment like '%Customer%Complaints%' )
    28. group by
    29. 	p_brand,
    30. 	p_type,
    31. 	p_size
    32. order by
    33. 	supplier_cnt desc,
    34. 	p_brand,
    35. 	p_type,
    36. 	p_size;

    1. mysql> desc partsupp;
    2. +---------------+---------------+------+-----+---------+-------+
    3. | Field         | Type          | Null | Key | Default | Extra |
    4. +---------------+---------------+------+-----+---------+-------+
    5. | ps_partkey    | int(11)       | NO   | PRI | NULL    |       |
    6. | ps_suppkey    | int(11)       | NO   | PRI | NULL    |       |
    7. | ps_availqty   | int(11)       | NO   |     | NULL    |       |
    8. | ps_supplycost | decimal(15,2) | NO   |     | NULL    |       |
    9. | ps_comment    | varchar(199)  | NO   |     | NULL    |       |
    10. +---------------+---------------+------+-----+---------+-------+
    11. 5 rows in set (0.01 sec)

    1. mysql> desc part;
    2. +---------------+---------------+------+-----+---------+-------+
    3. | Field         | Type          | Null | Key | Default | Extra |
    4. +---------------+---------------+------+-----+---------+-------+
    5. | p_partkey     | int(11)       | NO   | PRI | NULL    |       |
    6. | p_name        | varchar(55)   | NO   |     | NULL    |       |
    7. | p_mfgr        | char(25)      | NO   |     | NULL    |       |
    8. | p_brand       | char(10)      | NO   |     | NULL    |       |
    9. | p_type        | varchar(25)   | NO   |     | NULL    |       |
    10. | p_size        | int(11)       | NO   |     | NULL    |       |
    11. | p_container   | char(10)      | NO   |     | NULL    |       |
    12. | p_retailprice | decimal(15,2) | NO   |     | NULL    |       |
    13. | p_comment     | varchar(23)   | NO   |     | NULL    |       |
    14. +---------------+---------------+------+-----+---------+-------+
    15. 9 rows in set (0.00 sec)

    1. mysql> desc supplier;
    2. +-------------+---------------+------+-----+---------+-------+
    3. | Field       | Type          | Null | Key | Default | Extra |
    4. +-------------+---------------+------+-----+---------+-------+
    5. | s_suppkey   | int(11)       | NO   | PRI | NULL    |       |
    6. | s_name      | char(25)      | NO   |     | NULL    |       |
    7. | s_address   | varchar(40)   | NO   |     | NULL    |       |
    8. | s_nationkey | int(11)       | NO   |     | NULL    |       |
    9. | s_phone     | char(15)      | NO   |     | NULL    |       |
    10. | s_acctbal   | decimal(15,2) | NO   |     | NULL    |       |
    11. | s_comment   | varchar(101)  | NO   |     | NULL    |       |
    12. +-------------+---------------+------+-----+---------+-------+
    13. 7 rows in set (0.00 sec)

    分析:

    explain查询计划分析:

    1. mysql> explain select
    2.     -> p_brand,
    3.     -> p_type,
    4.     -> p_size,
    5.     -> count(distinct ps_suppkey) as supplier_cnt
    6.     -> from
    7.     -> partsupp,
    8.     -> part
    9.     -> where
    10.     -> p_partkey = ps_partkey
    11.     -> and p_brand <> 'Brand#45'
    12.     -> and p_type not like 'MEDIUM POLISHED%'
    13.     -> and p_size in (49,
    14.     -> 14,
    15.     -> 23,
    16.     -> 45,
    17.     -> 19,
    18.     -> 3,
    19.     -> 36,
    20.     -> 9)
    21.     -> and ps_suppkey not in (
    22.     -> select
    23.     -> s_suppkey
    24.     -> from
    25.     -> supplier
    26.     -> where
    27.     -> s_comment like '%Customer%Complaints%' )
    28.     -> group by
    29.     -> p_brand,
    30.     -> p_type,
    31.     -> p_size
    32.     -> order by
    33.     -> supplier_cnt desc,
    34.     -> p_brand,
    35.     -> p_type,
    36.     -> p_size\G
    37. ERROR 2006 (HY000): MySQL server has gone away
    38. No connection. Trying to reconnect...
    39. Connection id:    20
    40. Current database: tpch
    41.  
    42. *************************** 1. row ***************************
    43.            id: 1
    44.   select_type: PRIMARY
    45.         table: partsupp
    46.    partitions: NULL
    47.          type: ALL
    48. possible_keys: PRIMARY
    49.           key: NULL
    50.       key_len: NULL
    51.           ref: NULL
    52.          rows: 8000000
    53.      filtered: 100.00
    54.         Extra: Using where with pushed condition (not(<in_optimizer>(`tpch`.`partsupp`.`ps_suppkey`,`tpch`.`partsupp`.`ps_suppkey` in ( <materialize> (/* select#2 */ select `tpch`.`supplier`.`s_suppkey` from `tpch`.`supplier` where (`tpch`.`supplier`.`s_comment` like '%Customer%Complaints%') ), <primary_index_lookup>(`tpch`.`partsupp`.`ps_suppkey` in <temporary table> on <auto_key> where ((`tpch`.`partsupp`.`ps_suppkey` = `materialized-subquery`.`s_suppkey`)))))))(t0) Pckrows: 2, susp. 2 (0 empty 0 full). Conditions: 1; Using temporary; Using filesort
    55. *************************** 2. row ***************************
    56.            id: 1
    57.   select_type: PRIMARY
    58.         table: part
    59.    partitions: NULL
    60.          type: eq_ref
    61. possible_keys: PRIMARY
    62.           key: PRIMARY
    63.       key_len: 4
    64.           ref: tpch.partsupp.ps_partkey
    65.          rows: 1
    66.      filtered: 40.00
    67.         Extra: Using where with pushed condition ((`tpch`.`part`.`p_brand` <> 'Brand#45') and (not((`tpch`.`part`.`p_type` like 'MEDIUM POLISHED%'))) and (`tpch`.`part`.`p_size` in (49,14,23,45,19,3,36,9)))(t0) Pckrows: 31, susp. 31 (0 empty 0 full). Conditions: 3
    68. *************************** 3. row ***************************
    69.            id: 2
    70.   select_type: SUBQUERY
    71.         table: supplier
    72.    partitions: NULL
    73.          type: ALL
    74. possible_keys: PRIMARY
    75.           key: NULL
    76.       key_len: NULL
    77.           ref: NULL
    78.          rows: 100000
    79.      filtered: 11.11
    80.         Extra: Using where with pushed condition (`tpch`.`supplier`.`s_comment` like '%Customer%Complaints%')(t0) Pckrows: 2, susp. 2 (0 empty 0 full). Conditions: 1
    81. 3 rows in set, 1 warning (0.02 sec)

    条件上推:
     

    1.  
    2. 	Using
    3. where
    4. with pushed condition (not(<in_optimizer>(`tpch`.`partsupp`.`ps_suppkey`,
    5. `tpch`.`partsupp`.`ps_suppkey` in ( <materialize> (/* select#2 */
    6. select
    7. 	`tpch`.`supplier`.`s_suppkey`
    8. from
    9. 	`tpch`.`supplier`
    10. where
    11. 	(`tpch`.`supplier`.`s_comment` like '%Customer%Complaints%') ),
    12. <primary_index_lookup>(`tpch`.`partsupp`.`ps_suppkey` in <temporary
    13. table
    14. 	> on
    15. 	<auto_key>
    16. where
    17. 	((`tpch`.`partsupp`.`ps_suppkey` = `materialized-subquery`.`s_suppkey`)))))))(t0) Pckrows: 2,
    18. susp. 2 (0 empty 0 full). Conditions: 1;
    19. 	Using temporary;
    20. 	Using filesort

    核心函数:

    Tianmu::core::ColumnBinEncoder::EncoderText_UTF::Encode

    调用堆栈:

    1. (gdb) bt
    2. #0  my_strnxfrm_unicode (cs=0x487dc20 , dst=0x7f4f8166be20 "", dstlen=16, nweights=16, 
    3.     src=0x7f50256c3000 "Brand#13Brand#13Brand#42Brand#34Brand#32Brand#24Brand#11Brand#44Brand#43Brand#54Brand#25Brand#33Brand#55Brand#13Brand#15Brand#32Brand#43Brand#11Brand#23Brand#12Brand#33Brand#43Brand#35Brand#52Brand#55"..., srclen=8, flags=64) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/strings/ctype-utf8.c:5146
    4. #1  0x00000000030035e9 in Tianmu::common::strnxfrm (collation=..., src=0x7f4f8166be20 "", src_len=16, 
    5.     dest=0x7f50256c3000 "Brand#13Brand#13Brand#42Brand#34Brand#32Brand#24Brand#11Brand#44Brand#43Brand#54Brand#25Brand#33Brand#55Brand#13Brand#15Brand#32Brand#43Brand#11Brand#23Brand#12Brand#33Brand#43Brand#35Brand#52Brand#55"..., dest_len=8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/common/mysql_gate.cpp:45
    6. #2  0x00000000030273cd in Tianmu::core::ColumnBinEncoder::EncoderText_UTF::Encode (this=0x7f4f816c57f0, buf=0x7f4f8166be20 "", buf_sec=0x7f4f8166be6b "", vc=0x7f4f80a5a580, mit=..., 
    7.     update_stats=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/column_bin_encoder.cpp:941
    8. #3  0x0000000003023785 in Tianmu::core::ColumnBinEncoder::Encode (this=0x7f4f814e4930, buf=0x7f4f8166be20 "", mit=..., alternative_vc=0x0, update_stats=false)
    9.     at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/column_bin_encoder.cpp:169
    10. #4  0x000000000300a2bb in Tianmu::core::GroupTable::PutGroupingValue (this=0x7f72adf6e2e8, col=0, mit=...)
    11.     at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/group_table.h:85
    12. #5  0x000000000300a629 in Tianmu::core::GroupByWrapper::PutGroupingValue (this=0x7f72adf6e220, gr_a=0, mit=...)
    13.     at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/groupby_wrapper.h:82
    14. #6  0x0000000003007254 in Tianmu::core::AggregationAlgorithm::AggregatePackrow (this=0x7f72adf6e580, gbw=..., mit=0x7f72adf6dee0, cur_tuple=0)
    15.     at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/aggregation_algorithm.cpp:547
    16. #7  0x0000000003005b74 in Tianmu::core::AggregationAlgorithm::MultiDimensionalGroupByScan (this=0x7f72adf6e580, gbw=..., limit=@0x7f72adf6e208: 7422784, offset=@0x7f72adf6e608: 0, sender=0x0, 
    17.     limit_less_than_no_groups=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/aggregation_algorithm.cpp:280
    18. #8  0x00000000030053ca in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7f72adf6e580, just_distinct=false, limit=@0x7f72adf6e600: -1, offset=@0x7f72adf6e608: 0, sender=0x0)
    19.     at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/aggregation_algorithm.cpp:196
    20. #9  0x0000000002df1e3e in Tianmu::core::TempTable::Materialize (this=0x7f4f8165fd80, in_subq=false, sender=0x7f4f816b1cc0, lazy=false)
    21.     at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/temp_table.cpp:1972
    22. #10 0x0000000002d3a414 in Tianmu::core::Engine::Execute (this=0x6de5390, thd=0x7f4f800125f0, lex=0x7f4f80014918, result_output=0x7f4f80a3f3c0, unit_for_union=0x0)
    23.     at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/engine_execute.cpp:426
    24. #11 0x0000000002d395b6 in Tianmu::core::Engine::HandleSelect (this=0x6de5390, thd=0x7f4f800125f0, lex=0x7f4f80014918, result=@0x7f72adf6ed18: 0x7f4f80a3f3c0, setup_tables_done_option=0, 
    25.     res=@0x7f72adf6ed14: 0, optimize_after_tianmu=@0x7f72adf6ed0c: 1, tianmu_free_join=@0x7f72adf6ed10: 1, with_insert=0)
    26.     at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/engine_execute.cpp:232
    27. #12 0x0000000002e21e47 in Tianmu::dbhandler::TIANMU_HandleSelect (thd=0x7f4f800125f0, lex=0x7f4f80014918, result=@0x7f72adf6ed18: 0x7f4f80a3f3c0, setup_tables_done_option=0, res=@0x7f72adf6ed14: 0, 
    28.     optimize_after_tianmu=@0x7f72adf6ed0c: 1, tianmu_free_join=@0x7f72adf6ed10: 1, with_insert=0)
    29.     at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/handler/ha_rcengine.cpp:82
    30. #13 0x0000000002462f6a in execute_sqlcom_select (thd=0x7f4f800125f0, all_tables=0x7f4f8000f548) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/sql/sql_parse.cc:5182
    31. #14 0x000000000245c2ee in mysql_execute_command (thd=0x7f4f800125f0, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/sql/sql_parse.cc:2831
    32. #15 0x0000000002463f33 in mysql_parse (thd=0x7f4f800125f0, parser_state=0x7f72adf6feb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/sql/sql_parse.cc:5621
    33. #16 0x00000000024591cb in dispatch_command (thd=0x7f4f800125f0, com_data=0x7f72adf70650, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/sql/sql_parse.cc:1495
    34. #17 0x00000000024580f7 in do_command (thd=0x7f4f800125f0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/sql/sql_parse.cc:1034
    35. #18 0x000000000258accd in handle_connection (arg=0xcc94f80) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/sql/conn_handler/connection_handler_per_thread.cc:313
    36. #19 0x0000000002c71102 in pfs_spawn_thread (arg=0x7334f20) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/perfschema/pfs.cc:2197
    37. #20 0x00007f72fcf95ea5 in start_thread () from /lib64/libpthread.so.0
    38. #21 0x00007f72fb1cab0d in clone () from /lib64/libc.so.6

    火焰图:

    对比mysql/innodb:

    火焰图:

     

  • 相关阅读:
    开发者分享 | Ascend C算子开发及单算子调用
    基于微信小程序的语言课学习系统设计与实现(源码+lw+部署文档+讲解等)
    网站不被谷歌收录的常见原因及解决办法
    Day2讲课习题题解
    合并分支--将自己的分支合并到master分支
    【玩转Redhat Linux 8.0系列 | 实验—使用Bash shell执行命令】
    linux手动安装scapy2.5
    java毕业设计旅游网站设计mybatis+源码+调试部署+系统+数据库+lw
    【软件工程之美 - 专栏笔记】30 | 用好源代码管理工具，让你的协作更高效
    深入浅出Django的MTV架构
  • 原文地址：https://blog.csdn.net/adofsauron/article/details/126244352